Is there a way to use a string to pass sql query a table columnname???

  • Thread starter Thread starter jonny
  • Start date Start date
J

jonny

Hi I appreciate your help.

Depending on condition of a If statement I determine which column
table I want to pull data from...so...

If dropdown text is "" empty I want to use table column name
"ShopOrderFiller" in my sql query otherwise I want to use table column
name "ShopOrder".

For example:

Dim Sfiller as string


If dropdown.text = "" then
Sfiller = "ShopOrderFiller"

Else

Sfiller = "ShopOrder"

End if


SELECT * FROM myTable WHERE Sfiller = '" & dropdown2.text & "'

The problem here is that Sfiller is not an actual table column name so
is there a way I can do this?
 
Hi I appreciate your help.

Depending on condition of a If statement I determine which column
table I want to pull data from...so...

If dropdown text is "" empty I want to use table column name
"ShopOrderFiller" in my sql query otherwise I want to use table column
name "ShopOrder".

For example:

Dim Sfiller as string


If dropdown.text = "" then
Sfiller = "ShopOrderFiller"

Else

Sfiller = "ShopOrder"

End if


SELECT * FROM myTable WHERE Sfiller = '" & dropdown2.text & "'

The problem here is that Sfiller is not an actual table column name so
is there a way I can do this?
Click to expand...

I assume you are building a SQL statement in a string, like this:

str = "SELECT * FROM myTable WHERE Sfiller = '" & dropdown2.text & "'

Change it to:

str = "SELECT * FROM myTable WHERE " & Sfiller & " = '" &
dropdown2.text & "'
 
Jack Jackson said:
I assume you are building a SQL statement in a string, like this:

str = "SELECT * FROM myTable WHERE Sfiller = '" & dropdown2.text & "'

Change it to:

str = "SELECT * FROM myTable WHERE " & Sfiller & " = '" &
dropdown2.text & "'
Click to expand...

And, just to keep the conversation interesting, you really shouldn't be
building SQL statements from concatenated string values in the first place,
as they open the door to SQL injection attacks. Much better to use the
SqlCommand.Parameters collection to store the parameter data.

-Scott
 
I assume you are building a SQL statement in a string, like this:

str = "SELECT * FROM myTable WHERE Sfiller = '" & dropdown2.text & "'

Change it to:

str = "SELECT * FROM myTable WHERE " & Sfiller & " = '" &
dropdown2.text & "'
Click to expand...

Thank you Jack. I am trying to pull data from table column that
matches value in dropdown1. However if dropdown1 is empty then I will
like to ignore the where portion of the sql query is that possible?
You see, I have many dropdown controls and some will be empty and some
not so I will have many where conditions to look up value in dropdown
and match with column in table to pull data but if the value in
dropdown is empty it causes problem with reset of query...anyway I
just want to find a way to ingrore this WHERE if downdown control is
"" empty... Please help.
 
Scott said:
And, just to keep the conversation interesting, you really shouldn't be
building SQL statements from concatenated string values in the first place,
as they open the door to SQL injection attacks. Much better to use the
SqlCommand.Parameters collection to store the parameter data.
Click to expand...

THe problem with that is that it's not a value, it's a field name, so
it's not possible to use a parameter.
 
I assume you are building a SQL statement in a string, like this:

str = "SELECT * FROM myTable WHERE Sfiller = '" & dropdown2.text & "'

Change it to:

str = "SELECT * FROM myTable WHERE " & Sfiller & " = '" &
dropdown2.text & "'
Click to expand...

Thank you Jack. I am trying to pull data from table column that
matches value in dropdown1. However if dropdown1 is empty then I will
like to ignore the where portion of the sql query is that possible?
You see, I have many dropdown controls and some will be empty and some
not so I will have many where conditions to look up value in dropdown
and match with column in table to pull data but if the value in
dropdown is empty it causes problem with reset of query...anyway I
just want to find a way to ingrore this WHERE if downdown control is
"" empty... Please help.

Now, I'm confused. You say that you want to pull from a table that matches
a value in a dropdown, but if there is no value in the dropdown, you wish to
ignore the WHERE portion of the SQL statement.

The table reference isn't used in the WHERE portion of the SQL statement.

If you are trying to allow for the table to be chosen based on the dropdown,
then you will either know the table to query or you won't. If you don't,
then you can't perform any query, much less one that has a WHERE clause.

If you just want to have a WHERE when the dropdown has a value and none when
it doesn't, you would do this:

If dropdown.Text = "" Then
sqlString = "SELECT * FROM myTable"
Else
sqlString = "SELECT * FROM myTable WHERE " & Sfiller & " = '" &
dropdown2.text & "'
End If

But again, it would be better to use SQLParameters than concatenate the
dropdown value directly to the SQL statement.

-Scott
 
jonny said:
Hi I appreciate your help.

Depending on condition of a If statement I determine which column
table I want to pull data from...so...

If dropdown text is "" empty I want to use table column name
"ShopOrderFiller" in my sql query otherwise I want to use table column
name "ShopOrder".

For example:

Dim Sfiller as string


If dropdown.text = "" then
Sfiller = "ShopOrderFiller"

Else

Sfiller = "ShopOrder"

End if


SELECT * FROM myTable WHERE Sfiller = '" & dropdown2.text & "'

The problem here is that Sfiller is not an actual table column name so
is there a way I can do this?
Click to expand...

You could put the string in the query:

"SELECT * FROM myTable WHERE " & Sfiller & " = '" & dropdown2.text & "'"

However, constructing queries dynamically is a bad practice,
demonstrated by the fact that your query is already totally open to SQL
injections. You should use a parameterised query instead, but that gets
a little more complicated as you can't use a parameter to specify a
field name.

You can solve it like this:

"select * from myTable where (@Field = 1 and ShopOrderFiller = @Value)
or (@Field = 2 and ShopOrder = @Value)"

The @Field parameter decides which comparison to use, and the @Value
parameter contains the value from dropdown2.

Note: You should specify what fields you want to return from the query
instead of using "select *", that makes the code more robust.
 
Thank you Jack. I am trying to pull data from table column that
matches value in dropdown1. However if dropdown1 is empty then I will
like to ignore the where portion of the sql query is that possible?
You see, I have many dropdown controls and some will be empty and some
not so I will have many where conditions to look up value in dropdown
and match with column in table to pull data but if the value in
dropdown is empty it causes problem with reset of query...anyway I
just want to find a way to ingrore this WHERE if downdown control is
"" empty... Please help.


Now, I'm confused.  You say that you want to pull from a table that matches
a value in a dropdown, but if there is no value in the dropdown, you wishto
ignore the WHERE portion of the SQL statement.

The table reference isn't used in the WHERE portion of the SQL statement.

If you are trying to allow for the table to be chosen based on the dropdown,
then you will either know the table to query or you won't.  If you don't,
then you can't perform any query, much less one that has a WHERE clause.

If you just want to have a WHERE when the dropdown has a value and none when
it doesn't, you would do this:

If dropdown.Text = "" Then
    sqlString = "SELECT * FROM myTable"
Else
    sqlString = "SELECT * FROM myTable WHERE " & Sfiller & " = '"&
dropdown2.text & "'
End If

But again, it would be better to use SQLParameters than concatenate the
dropdown value directly to the SQL statement.

-Scott
Click to expand...

Yes Scott I know I can put the SQL query in a If else depending on the
condition but I am dealing with 10 dropdown controls and could be any
combination which adds up to and lot of if else...so many its not
practical. These dropdown controls are used as filters, for example
the user many what to filter the data by Partnumber and Shift or many
just by operator, etc. etc. etc.

So if I have a WHERE condition to filter the data if table column1 =
dropdown1 thats find but if dropdown1 is empty I want to skip the
WHERE condition because I do not want the result to return empty.
Again I could put every possibility into a If then statement like you
mentioned but its way to many combinations.
 
Yes Scott I know I can put the SQL query in a If else depending on the
condition but I am dealing with 10 dropdown controls and could be any
combination which adds up to and lot of if else...so many its not
practical. These dropdown controls are used as filters, for example
the user many what to filter the data by Partnumber and Shift or many
just by operator, etc. etc. etc.

So if I have a WHERE condition to filter the data if table column1 =
dropdown1 thats find but if dropdown1 is empty I want to skip the
WHERE condition because I do not want the result to return empty.
Again I could put every possibility into a If then statement like you
mentioned but its way to many combinations.
Click to expand...

You know, you could associate the filter condition with the item in the
dropdown and then put your dropdowns in a list...
Then you could do something like (this is air code):

Dim sqlQuery As String = "SELECT * FROM myTable"
Using command As SqlCommand = new SqlCommand (sqlQuery, new SqlConnection(connecitonString))

If Not String.IsNullOrEmpty (dropdown.Text) Then
sqlQuery &= " WHERE " ' could use a stringbuilder - but...
For Each dd As ComboBox In _dropDowns
dim itm As MyItem = dd.SelectedItem
If itm IsNot Nothing Then
sqlQuery &= " " & itm.Field & " = " & itm.ParamName
command.Parameters.Add (itm.ParamName, itm.Value)
End If
Next
End If

' exectue the query
End Using
 
jonny said:
Hi I appreciate your help.

Depending on condition of a If statement I determine which column
table I want to pull data from...so...

If dropdown text is "" empty I want to use table column name
"ShopOrderFiller" in my sql query otherwise I want to use table column
name "ShopOrder".

For example:

Dim Sfiller as string


If dropdown.text = "" then
Sfiller = "ShopOrderFiller"

Else

Sfiller = "ShopOrder"

End if


SELECT * FROM myTable WHERE Sfiller = '" & dropdown2.text & "'

The problem here is that Sfiller is not an actual table column name so
is there a way I can do this?
Click to expand...

Air-code:
-----------------
Dim sqlCmd as new sqlcommand
Dim sql as new stringbuilder()
Dim andNeeded as Boolean=false
Dim paramNum as integer=0

' remember to explicitly specify the columns instead of using *
sql.append("SELECT * FROM myTable")

for each dropdown in yourDropdowns

if dropdown has a value then
if andNeeded then
sql.append(" AND")
else
sql.append(" WHERE")
end if
sql.appendformat(" {0}=@param{1}", lookupFieldname(for that dropdown),
paramNum)
sql.Cmd.parameters.add(new sqlparameter("@param" & paramNum.ToString,
other appropriate bits here))
andNeeded=true
paramNum+=1
end if

next

sqlCmd.commandtext=sql.tostring & ";"
' now execute the query
-------------------

That's just the general idea: iterate over your dropdowns and add in clauses
as needed.

Probably you should code it with INNER JOINs (and UNIONs if that's
appropriate for some combinations) instead of ANDs. And you might want to
pop in the occasional vbCrLf and vbTab if you're going to read it, because
you don't want to be trying to read it like the queries my actual code
produces.

HTH,

Andrew
 
Back
Top