SQL Variable in Where Clause

how to pass variable in where clause in sql
dynamic sql variable in where clause
using variables in sql select statement
sql local variable in where clause
sql set variable from select
how to insert variable in sql query
set variable in where clause
sql variable in string
Dim varCity As String
varCity = Me.txtDestinationCity
Me.txtDestinationState.RowSource = "SELECT tPreTravelDestinationState FROM [TDestinationType] WHERE" & Me.txtDestinationCity & "= [TDestinationType].[tPreTravelDestinationCity]"

I am trying to select the states for the selected city. There is a drop down box with a list of cities. That box is titled txtDestinationCity.

It says I have an error in my FROM clause.

Thank you

You miss a space and some quotes. How about:

Me.txtDestinationState.RowSource = "SELECT tPreTravelDestinationState FROM [TDestinationType] WHERE '" & Me.txtDestinationCity & "' = [TDestinationType].[tPreTravelDestinationCity]"

Copy that next to your original to see the difference.

And for reasons SQL, PLEASE reverse the comparison. Always mention the column left and the value right:

Me.txtDestinationState.RowSource = "SELECT tPreTravelDestinationState FROM [TDestinationType] WHERE [TDestinationType].[tPreTravelDestinationCity] = '" & Me.txtDestinationCity & "'"

Since the quotes are annoying and easy to miss, i suggest defining a function like this:

Public Function q(ByVal s As String) As String
    q = "'" & s & "'"
End Function

and then write the SQL string like that:

Me.txtDestinationState.RowSource = "SELECT tPreTravelDestinationState FROM [TDestinationType] WHERE [TDestinationType].[tPreTravelDestinationCity] = " & q(Me.txtDestinationCity)

This makes sure you ALWAYS get both quotes at the right places and not get confused by double-single-double quote sequences.

If you care about SQL-injection (yes, look that up), please use the minimum

Public Function escapeSQL(sql As String) As String
    escapeSQL = Replace(sql, "'", "''")
End Function

and use it in all places where you concatenate user-input to SQL-clauses, like this:

Me.txtDestinationState.RowSource = "SELECT tPreTravelDestinationState FROM [TDestinationType] WHERE [TDestinationType].[tPreTravelDestinationCity] = " & q(escapeSQL(Me.txtDestinationCity))

Lastly, breakt it for readability. I doubt your editor shows 200 characters wide:

Me.txtDestinationState.RowSource = _
    "SELECT tPreTravelDestinationState " & _
    "FROM [TDestinationType] " & _
    "WHERE [TDestinationType].[tPreTravelDestinationCity] = " & q(escapeSQL(Me.txtDestinationCity))

Note the trailing spaces in each line! Without them, the concatenation will not work.

Using Variables in Where Clause - MSDN, SQL Server does not have macro-substitution. you will need dynamic sql of put that list inside a table (variable, temporary, permanent, or TVP). 3rd – Dynamic SQL. If you like dynamic SQL and use it on a regular basis then this one is nice and easy and certainly my favorite. If however dynamic SQL is not something that you feel comfortable with then you might just go with method #2.

It can be easier to troubleshoot your query construction if you set it to a variable (e.g., strSQL) first. Then you can put a breakpoint and see it right before it executes.

How to avoid using variables in WHERE clause, The values known to SQL Server at compile-time is a known value for @endDate and null for @startDate . Using a null in a between will give you� To assign a variable a value by using the SET statement, include the variable name and the value to assign to the variable. This is the preferred method of assigning a value to a variable. The following batch, for example, declares two variables, assigns values to them, and then uses them in the WHERE clause of a SELECT statement:

You need a space after WHERE. Change WHERE" to WHERE<space>"

Me.txtDestinationState.RowSource = "SELECT tPreTravelDestinationState FROM [TDestinationType] WHERE " & Me.txtDestinationCity & "= [TDestinationType].[tPreTravelDestinationCity]"

Using SQL Variables in Queries - MySQL Cookbook [Book], The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement. Below figure explain two types of variable available in MS SQL server. Type of Variables in SQL Server Local variable: A user declares the local variable. By default, a local variable starts with @. Every local variable scope has the restriction to the current batch or procedure within any given session. Global variable:

How do I use a variable in an IN clause?, I see this question in one form or another a lot. I've seen this or something like it probably half a dozen times in the last couple of weeks on the� Although being able to manipulate literals and other kinds of constants while dealing with a SQL database gives you great power, having variables is helpful, too. In many cases, you’d need to do much more work if you didn’t have variables. A variable, by the way, is a quantity that has a value that can …

Using variables in where clause – SQLServerCentral, Using variables in where clause – Learn more on the SQLServerCentral forums. SQL, like every other language take a while to get into. in case it's sqlserver 2000 where sp_executesql does not work create a temp table and store the data there. declare @UI varchar(50) set @UI = 'IDNumber' create table #result ( ClientExist bit ) -- temp table to store the data declare @SQL varchar(4000) set @SQL = 'insert into #result (ClientExist) select isnull((select top 1 clientid from Clients where '+ @UI +' = ''6001016119085'' or '+ @UI

This article explores the SQL variables using SET and Select SQL , What to choose when assigning values to SQL Server variables: SET vs Although both T-SQL statements fulfill the SQL variable value� You may also assign a value to the variable at the time of declaration. For instance, DECLARE @str_email VARCHAR(100) = ‘abc@test.com’; The next section shows you a few running examples of declaring, assigning and using the variables in SQL batch or procedures – so keep reading the rest of this tutorial.

Comments
  • If Me.txtDestinationCity is a string to test a column value against, you need to quote it
  • it is now asking me to enter parameter values for both the city i select and [TDestinationType].[tPreTravelDestinationCity]
  • you need to put quotes around your string but @Oliver already answered this