submitting a ' in a string to sql
-
i have a problem i have a vb6 program which at one point calls a stored procedure that accepts two strings as arguments but there are certain times when on of the strings contains a ' for example execute mystoredproc 'blahblah', 'jame's' this of course generates an error because it thinks that the letters jame is the whole string being submitted so how can i change this so it accepts it
-
i have a problem i have a vb6 program which at one point calls a stored procedure that accepts two strings as arguments but there are certain times when on of the strings contains a ' for example execute mystoredproc 'blahblah', 'jame's' this of course generates an error because it thinks that the letters jame is the whole string being submitted so how can i change this so it accepts it
-
DX, It sounds like your string in VB is reading the special character as the beginning of a comment. ie: ' Comments go here. I don't know of a workaround for this, I have had the same problem with ASP Pages doing data inserts into SQL Server. Tony
no its not vb that has the problem its doesnt think its a comment its when its submitted to the stored procedure that it gets the problem like the stored procedure would accept this execute mystoreproc 'blah','blah' no problem but when you have something like this execute mystoreproc 'blah's','blah' the stored proc will interpet it like this execute mystoreproc 'blah' s ,'blah' because sql uses the ' character to enclose strings that are inserted or otherwise used in queries
-
no its not vb that has the problem its doesnt think its a comment its when its submitted to the stored procedure that it gets the problem like the stored procedure would accept this execute mystoreproc 'blah','blah' no problem but when you have something like this execute mystoreproc 'blah's','blah' the stored proc will interpet it like this execute mystoreproc 'blah' s ,'blah' because sql uses the ' character to enclose strings that are inserted or otherwise used in queries
i found the solution if you want to use something like 'jake's bigtime' in an insert statement then you would have to simply put two apostrophies and sql will then put the one apostrophe character in the db so the query would look like this insert into test values('jake''s bigtime') and a this query select * from test would return jake's bigtime
-
i have a problem i have a vb6 program which at one point calls a stored procedure that accepts two strings as arguments but there are certain times when on of the strings contains a ' for example execute mystoredproc 'blahblah', 'jame's' this of course generates an error because it thinks that the letters jame is the whole string being submitted so how can i change this so it accepts it
-
i have a problem i have a vb6 program which at one point calls a stored procedure that accepts two strings as arguments but there are certain times when on of the strings contains a ' for example execute mystoredproc 'blahblah', 'jame's' this of course generates an error because it thinks that the letters jame is the whole string being submitted so how can i change this so it accepts it
You should use the parameters collection to submit your parameters to the stored proc.
SqlCommand cmd = new SqlCommand("mystoredproc"); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@param1", "blahblah"); //replace @param1 with the name of your parameter cmd.Parameters.Add("@param2", "jamie's"); //replace @param2 with the name of your parameter cmd.ExecuteNonQuery();Then the necessary convertions will be done automagically for you.