asked on How to pass mutiple string value from list box to SQL 2005
Hi,
I have question in concerns with passing multiple string value to a SQL stored procedure variable
So far my code will only allow me to delete one entry from a VB.NET listbox and I need it to be able to delete multiple items fromt he list box. Attached is my current stored procedure. Also I need just simple help in gathering my list values and put them into a string sperated by a comma. I figure I should be able to parse that in SQL and then delete the items; I just do not know how
I have question in concerns with passing multiple string value to a SQL stored procedure variable
So far my code will only allow me to delete one entry from a VB.NET listbox and I need it to be able to delete multiple items fromt he list box. Attached is my current stored procedure. Also I need just simple help in gathering my list values and put them into a string sperated by a comma. I figure I should be able to parse that in SQL and then delete the items; I just do not know how
Alter procedure [dbo].[Remove_AllUsersFromGroup]
@gname varchar(32)
as
delete from dbo.GroupNotification
where GroupName = @gname
.NET ProgrammingMicrosoft SQL Server 2005
Last Comment
Guy Hengel [angelIII / a3]
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I got this one to work. In VB.NET I created a hidden textbox and when a user selected more than one entry then it would add the useris and a a comma "," before it. when the user hit submit then it sent the string to SQL for parsing. I am not sure if this is the most proficient way, but it seems to work
Thank you all for your help
Thank you all for your help
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
ALTER procedure [dbo].[Remove_AllUsersFromGroup]
@UserID varchar(255)
as
declare @test varchar(255)
set @test = @UserID
select * from dbo.SecurityIncidents
where charindex(eventType,@UserID,0)>0
or you could create a CLR method similar to Angels' example.
Then you could do easy string.split() and return a table.
http://www.codeplex.com/MSFTEngProdSamples/Wiki/Print.aspx?title=SS2008!String%20Split%20Table-Valued%20Function%20(TVF)&version=6&action=Print
Then you could do easy string.split() and return a table.
http://www.codeplex.com/MSFTEngProdSamples/Wiki/Print.aspx?title=SS2008!String%20Split%20Table-Valued%20Function%20(TVF)&version=6&action=Print
> where charindex(eventType,@UserI
is a dangereous method.
@userid = 1
would match anythink like
1
10
11
12
13...
51
61...
you should keep the method in your .net to pass the string comma-delimited, but in the procedure use the method I suggested to ensure you get the 100% match...
is a dangereous method.
@userid = 1
would match anythink like
1
10
11
12
13...
51
61...
you should keep the method in your .net to pass the string comma-delimited, but in the procedure use the method I suggested to ensure you get the 100% match...
@gname varchar(MAX)
as
EXEC('delete from dbo.GroupNotification
where GroupName IN ('+@gname+')' )