Saving apostraphy to sql table (resolved)

[Beast777]

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?

[The_Duck]

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)

[techgnome]

Try using parameters instead.

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.

[Phill64]

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

[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.

[Beast777]

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.

[WALDO]

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.

[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

[mar_zim]

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
The best option is use parameters save the correct data.

mar_zim

[Beast777]

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.

[techgnome]

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.

[Beast777]

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.

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

[WALDO]

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
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:

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

[techgnome]

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

[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.

[WALDO]

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)

[mar_zim]

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)