Saving apostraphy to sql table (resolved)
Gonna keep this simple. Got a textbox and a button. CLick on button, insert text into sql table. Now add a apostraphy into text and error occurs, obviously its the apostraphy in the sql syntax.
The .net cmdtext is
"Insert into testtable (testtext) values ( '" & txtTestTextbox.Text & "')"
So if the text is Hello I get a cmdtext of
"Insert into testtable (testtext) values ( 'Hello')"
Now with text of I Don't Know I get a cmdtext of
"Insert into testtable (testtext) values ( 'I Don't Know ')"
even I can see the problem but how do we work around this?
Re: Saving apostraphy to sql table
I had a similar problem to this the other day :)
Try replacing a single ' with ''
.Text.Replace("'", "''")
Note that I used this to filter with a dataview NOT with SQL commands so this might not be a solution :) (But i think it should :P)
Re: Saving apostraphy to sql table
Try using parameters instead.
VB Code:
strSQL = "Insert int testtable (testtext) Values (@TestData)"
cmdCommand.CommandText = strSQL
cmdCommand.Parameters.Add("@TestData",varchar,,Input, "I Don't Know Why")
cmdCommand.ExecuteNoQuery
OK, Now I'll admit, I did that from memory, so I'm not 100% sure the complete syntax of the VB code is right, but it should be close enough that you can look up the rest (or use intellisense) to clean it up proper.
Tg
EDIT: While The Duck's solution is A solution, it isn't concidered the ideal solution. Mainly because if you replace ' with " when inserting, then you need to replace " with ' when getting it back. But if it was a legit " and you change it to a ' then that becomes a problem.
Re: Saving apostraphy to sql table
using " is not ideal, as gnone mentioned, is "" is used this is bad.
I had the same issue and used ^&^ as the replacement, since the likeliness of this being input by a human is extremely improbable
Re: Saving apostraphy to sql table
I think The Duck was using double ticks, not a quote in his .Replace. In plain text, .Replace(single apostrophe, w/ double apostrophe). In this case, the conversion back is unnecessary. Though agreed on parameters being a better method.
Re: Saving apostraphy to sql table
Thanks for the input, it would seem parameters are the way to go so I will now learn them, master them, and royally screw them up.
Re: Saving apostraphy to sql table (My 2 cents)
Quote:
Originally Posted by Briantcva
I think The Duck was using double ticks, not a quote in his .Replace. In plain text, .Replace(single apostrophe, w/ double apostrophe). In this case, the conversion back is unnecessary. Though agreed on parameters being a better method.
Yeah, parameters inherently perform that replace.
Re: Saving apostraphy to sql table (resolved)
Hi,
I made a function that takes a string ..
Saves it into a variable ...
Check if the variable contains ' .. with instr
then if not .. return variable otherwise .. replace ' with '' then return variable.
...
now i'll just use apos(txtName.text) rather than txtName.text
Ciao
Re: Saving apostraphy to sql table (resolved)
Quote:
Originally Posted by Cranzy
Hi,
I made a function that takes a string ..
Saves it into a variable ...
Check if the variable contains ' .. with instr
then if not .. return variable otherwise .. replace ' with '' then return variable.
...
now i'll just use apos(txtName.text) rather than txtName.text
Ciao
I hope I may not sound rude but I think removing the single quote is not appropriate. What If the user type Master's Bedroom? Is it good when you replace it w/ Masters Bedroom? That's awful :sick:
The best option is use parameters save the correct data.
mar_zim
Re: Saving apostraphy to sql table (resolved)
Not having any luck with parameters yet. Are they to be used with Stored procedures only, I have not found an example that does not use a SP.
Re: Saving apostraphy to sql table (resolved)
It can be used with regular queries... their called "parameterized queries".... if you google that phrase, you will probably find what you are looking for.
Tg
sorry to be a little vague on this... I work exclusively with stored procs, but I have seen this done before with Access and with success too. So I know the theory is sounds, but not the nutz and bolts of it.
Re: Saving apostraphy to sql table (resolved)
Well I have been trying this and have this so far:
The bottom code works and I am replacing it with the top parameter and get this error:
An unhandled exception of type 'System.InvalidCastException' occurred in system.data.dll
Additional information: The SqlParameterCollection only accepts non-null SqlParameter type objects, not OleDbParameter objects.
VB Code:
Private Sub UiButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UiButton1.Click
Dim connectionString As String = "workstation id=""test-2098"";packet size=4096;integrated security=SSPI;data s" & _
"ource=""test-2B"";persist security info=False;initial catalog=Discipline"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim cmdtext As String = "Insert into RWInterview (IncidentID, Date, Time, Description) values ('" & globalIncidentID & "', '" & dtInterviewDate.Text & "', '" & dtInterviewTime.Text & "', ?)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.Connection = dbConnection
' dbCommand.Parameters.Add("Desc", txtInterviewDesc.Text)
dbCommand.Parameters.Add(New System.Data.OleDb.OleDbParameter("Desc", System.Data.OleDb.OleDbType.VarChar, 1000, txtInterviewDesc.Text))
dbCommand.CommandText = cmdtext
dbCommand.Connection = dbConnection
dbConnection.Open()
Try
dbCommand.ExecuteNonQuery()
MsgBox("Interview Successfully saved")
Finally
dbConnection.Close()
End Try
'Dim connectionString As String = "workstation id=""test-2098"";packet size=4096;integrated security=SSPI;data s" & _
' "ource=""test-2B"";persist security info=False;initial catalog=Discipline"
'Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
'Dim cmdtext As String = "Insert into RWInterview (IncidentID, Date, Time, Description) values ('" & globalIncidentID & "', '" & dtInterviewDate.Text & "', '" & dtInterviewTime.Text & "', '" & txtInterviewDesc.Text & "')"
'Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
'dbCommand.CommandText = cmdtext
'dbCommand.Connection = dbConnection
'dbConnection.Open()
'Try
' dbCommand.ExecuteNonQuery()
' MsgBox("Interview Successfully saved")
'Finally
' dbConnection.Close()
'End Try
End Sub
Re: Saving apostraphy to sql table (resolved)
Quote:
Originally Posted by mar_zim
I hope I may not sound rude but I think removing the single quote is not appropriate. What If the user type Master's Bedroom? Is it good when you replace it w/ Masters Bedroom? That's awful :sick:
The best option is use parameters save the correct data.
mar_zim
I agree. I have one better. I know a guy who consistently replaces apostrophes with the character next the 1 on the keyboard.
So instead of Master's bedroom, it would be Master`s bedroom. Just as bad in my opinion, but i don't think that's what Cranzy was doing.
Actually I missed that Duck did this above
Simplified version [Cranzy may call it apos()] of the function:
Code:
Public Shared Function SQLEncode(ByVal value As String) As String
Return Replace(value, "'", "''")
End Function
...
Dim cmdtext As String = & _
"Insert into RWInterview (IncidentID, [Date], [Time], [Description]) values (" & _
"'" & SQLEncode(globalIncidentID) & "', " & _
"'" & SQLEncode(dtInterviewDate.Text) & "', " & _
"'" & SQLEncode(dtInterviewTime.Text) & "'" & _
", ?)"
...
As for your error, it should be a SqlClient.SqlParameter, not OleDbClient.OleDbParameter
Re: Saving apostraphy to sql table (resolved)
Are you using SQL Server, or what? You've mixed your db types... some of your objects are SQLClient, while others are OleDB.... IF you are using SQL Server, then 1) You need to change OleDb.OleDbParameter to SQLClient.SQLParameter. 2) You really should learn how to write a stored procedure and use that method instead.
If you are NOT using SQL Server, then you shouldn't use the SQLCLient at all, and should use the OLEDBClient instead.
Tg
Re: Saving apostraphy to sql table (resolved)
Yes I am using SQL, I was just using the only code I could find that used parameters. I will change it to sqlclient. I have wrote SP's before but not with parameters. Does the use of all parameters in .net require an SP or can you create code with parameters without an SP? I am just trying to learn the proper way to code this.
Re: Saving apostraphy to sql table (resolved)
Quote:
Originally Posted by Beast777
Yes I am using SQL, I was just using the only code I could find that used parameters. I will change it to sqlclient. I have wrote SP's before but not with parameters. Does the use of all parameters in .net require an SP or can you create code with parameters without an SP? I am just trying to learn the proper way to code this.
Yes, you can write parameterized queries without using Stored Procedures
This is just off the top of my head:
Dim con As New SqlConnection
Dim cmd As New SqlCommand(con)
cmd.CommandText = "SELECT * FROM [foo] WHERE foo_id=@MyParam"
Dim prm As New SqlParameter("@MyParam", Integer, 4)
prm.Value = myValue
Dim da As New SqlAdapter(cmd)
Dim ds As New DataSet
da.Fill(ds)
Re: Saving apostraphy to sql table (resolved)
Quote:
Originally Posted by Beast777
Yes I am using SQL, I was just using the only code I could find that used parameters. I will change it to sqlclient. I have wrote SP's before but not with parameters. Does the use of all parameters in .net require an SP or can you create code with parameters without an SP? I am just trying to learn the proper way to code this.
the proper way is to create an SP and put your parameters there.
In the code you'll just pass a value to the parameter.
cm.parameters.add("@name",textbox2.text)
