asked on

Running SQL Store Procedure with Parameters

I have a SQL Store Procedure called from Visual Studio 2005 vb code behind.
In the store procedure I want to delete a serie of record which meet the following condition:

DELETE FROM [DATABASE].[dbo].[TABLE]
WHERE [Sequence] = @sequence AND [Product] IN(@selctedlist)

Product field is an Int field and @selctedlist is a string character. i.e '1, 2, 3, 4, 5'
when I run the store procedure is returning me an error.
If I run all from vb code behind I will have no error:
i.e = Command = "DELETE FROM [DATABASE].[dbo].[TABLE]
WHERE [Sequence] = " & sequence & " AND [Product] IN(" & selctedlist & ")"
But I really want to run it from the Store Procedure. Can anybody help ?

nogovoia

product is a string field? if so pass the stored procedure '1','2','3','4','5'

NeGueSa

ASKER

No Produc is an Integer Field and @selctedlist is a parameter string of this type '1, 2, 3, 4, 5'

nogovoia

post the exception you receive

linuxfreakus

Well if you quoted the whole string and the field is a number it won't work. take out the quotes. if the column is a string then it would not give an error, but you would have to quote each item individually to get them all recognized in the SQL statement.

IN('1, 2, 3, 4, 5') will not work if Product is a number
IN(1, 2, 3, 4, 5) will work if Product is a number

NeGueSa

ASKER

Well this value comes from a hiden label and is stored as :

"1, 2, 3, 4, 5"

linuxfreakus

if the quotes are not passed to the stored proc, then you must have a problem with the way you call the stored proc, please post your code where you call the stored proc.

NeGueSa

ASKER

Dim SqlCon As New System.Data.SqlClient.SqlConnection()
Dim SqlCom As New System.Data.SqlClient.SqlCommand()

SqlCon.ConnectionString = "data source=SERVERNAME;initial catalog=DATABASE;password=ttt;persist security in" & _
"fo=True;user id=uuuu;workstation id=MACHINE;packet size=4096"

SqlCon.Open()

SqlCom.CommandText = "dbo.[DELETEPROD]"
SqlCom.CommandType = System.Data.CommandType.StoredProcedure
SqlCom.Connection = SqlCon

SqlCom.Parameters.Add(New System.Data.SqlClient.SqlParameter("@selctedlist", System.Data.SqlDbType.VarChar, 800)).Value = me.list.text
SqlCom.Parameters.Add(New System.Data.SqlClient.SqlParameter("@sequence", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, me.sequence.text))

Try
SqlCom.ExecuteNonQuery()
success = True
Catch exc As Exception
success = False
End Try

SqlCom = Nothing
SqlCon.Close()

dqmq

>Well this value comes from a hiden label and is stored as :
"1, 2, 3, 4, 5"

Then, in VB, you need to convert the above, so your parameter looks like this:

"(Select 1 Union select 2, union select 3 union select 4, union select 5)" to make your procedure work.

ASKER CERTIFIED SOLUTION

dqmq

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

linuxfreakus

oh, lol, no wonder, i had just assumed that he was using dynamic SQL, never thought to ask...