January 7, 2009 at 12:28 pm
I have the following code in a select statement:
WHERE (Column1 IN (@myVar))
the @myVar is getting its value from selection may to a CheckBoxList object:
<asp:CheckBoxList ID="CheckBoxList1" runat="server"
DataSourceID="ObjectDataSource1" DataTextField="description"
DataValueField="@myVar" Width="677px"
I've confirmed that the CheckBoxListItem object produces values. I've concatenated quotes and commas around each value, but only the first value gets recognized by the SQL statement, alough when I use a Label object to show the ListItem object, I can see there are multiple values.
Here is the code that passes the values:
Dim LVL As Integer
Dim li As ListItem
Dim LabelT As New ListItem
Dim LabelV As New ListItem
For Each li In CheckBoxList1.Items
If (li.Selected <> 0) Then
' LabelV.Value += "'" + li.Value + "'" + ","
LabelV.Value += li.Value + ", "
LabelT.Text += li.Text
End If
Next
' LabelV.Value += "'"
LVL = LabelV.Value.Length
' LabelV.Value = LabelV.Value.Substring(0, (LVL - 1))
LabelV.Value = LabelV.Value.Substring(0, (LVL - 2))
Response.Redirect("DeviceLibDetails.aspx?SelectedValu=" + LabelV.Value + "&this=" + LabelT.Text)
End Sub
Then the above goes to the called .aspx page in the following:
<asp:QueryStringParameter Name="@myVar" QueryStringField="SelectedValu"
The reason I left in the comments fields is to show how I've attempted to add in the single quotes required by SQL.
So, for example, I can produce the ListItem which appears EXACTLY as the following:
'mc ','rel '
and when I enter this into the
WHERE (IN (@myVar))
I get nothing,
But when I enter the 'mc ','rel ' into the SQL statement in SQL 2005, I can get the results. So how I pass this object to SQL 2005 using ASP?
Forgive me if this not the appropriate place to post this, but I'm guess I'm missing something in handling the incoming values to the SQL statement.
And, by the way, when I choose only 1 ListItem, the WHERE (IN (@myVar)) works, but it does so when there are no single quotes ( as in ') around the one value.
So, I appreciate any insight or resolution or some path which may guide me in the right way
January 7, 2009 at 1:42 pm
The issue is not with the .net code. SQL itself does not allow you to use a variable as part of an in clause. So you really have a couple solutions.
1. Use dynamic sql. So you build the sql statemant with the variable and then call sp_executesql. This way the command is actually typed out as 'item1', 'item2', etc versus @var.
2. Use a table valued function to return the variable to a temp table or table variable. Then use the in clause with a SELECT * FROM Table
January 8, 2009 at 10:26 am
Hi
You can't use IN clause with any variables.. to do this you need to construct dynamic SQL..
Thanks -- Vj
January 9, 2009 at 12:45 pm
Just to clarify, do I write dynamic SQL to call sp_executesql ? And by 'dynamic SQL' are you referring to a stored procedure?
If possible, what would sample code look like?....I only ask if you know of any resource where an example of a dynamic SQL would look like varibles being passed into from any programming language......hopefully I can take it from there....
..thank you for your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply