data-types and parameterized query

[bmpsu]

I have the task to parameterize inline sql queries but ran into a bump using IN. I have had no problems until now. It seems the code wants to compare the data-type of memId to @idcheck.

Here is a snippet of code for example:

'Delete tbl_members WHERE memId IN (" & Request("idcheck") & ") 

Dim oCmd As SqlCommand
oCmd = New SqlCommand 
oCmd.CommandType = Data.CommandType.Text 
oCmd.Parameters.Add(New SqlParameter("@idcheck", Data.SqlDbType.Int, 4, Data.ParameterDirection.Input, False, 0, 0, Nothing, Data.DataRowVersion.Default, Request("idcheck"))) 
oCmd.CommandText = "Delete tbl_members WHERE memId IN (@idcheck)"
‘…

Request(“idcheck”) is a value passed from a form that represents checkbox values. The above sample works when this value is (ex. 70); but not when this value is (ex. 70,71)
“Conversion failed when converting the varchar value '70,71' to data type int.”

Anyone run into this to find a simple solution.

 

[ca8msm]

Yes, it will fail as "70,71" isn't an valid number. Instead, try passing it as a string (to a stored procedure) and either loop through the comma seperated values or build the SQL dynamically. Ask in the SQL Server forum if you get stuck.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[bmpsu]

Thank you for your response. The site I am working on has lots of inline queries and the option of stored procedures has been passed by. And since this may come up again, I was hoping to find a solution. I have gotten a little further this time.

Below is what I have as of now. It seems from my example above, the code below will delete 71 but not 70. Any thoughts?

Dim cmd As SqlCommand
Dim requestValues As String = Request("idcheck")
Dim sites As String() = Nothing
Dim count as integer = 0
Dim s As String
Dim strSQL As String 
Dim endChar AS Char() = " or "

strSQL = "DELETE tbl_memebers WHERE ("
cmd = New SqlCommand
cmd.CommandType = Data.CommandType.Text
	
sites = requestValues.Split(",")
    	
For x As Integer = 0 To count
  For Each s In sites
    cmd.Parameters.Clear()
    cmd.Parameters.Add(New SqlParameter("@idcheck", Data.SqlDbType.int, 4, Data.ParameterDirection.Input, False, 0, 0, Nothing, Data.DataRowVersion.Default, s))
    strSQL = strSQL & " memId = @idcheck or "
    count = count + 1
  Next s  
Next

Dim newstrSQL As String = strSQL.TrimEnd(endChar)		
cmd.CommandText = newstrSQL
'and so on...

 

[ca8msm]

If you aren't going to use a Stored Procedure (which I would recommend doing), then you could use your original method and build up a string (rather than an integer) with comma seperated values.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.