[RESOLVED]text box to database

Hello,
When writing a value in a textbox to the database, if there is a single quote as part of the textbox, an error is occuring. I mean if text1.text=vb's then , for writing it to database ,sql we would be something like this:
" INSERT INTO mytable (name) values ( '" & text1.text & "')".
This would evaluate to
"INSERT INTO mytable (name) values ('vb's')".
So , is there any workaround to include single quotes in the text values to be stored in the database? If yes, how?
Thank you.

Just replace those single quotes with two single quotes, you may use the ff. function...

VB Code:

Public Function CleanText(ByVal pString As String) As String
    CleanText = Replace$(pString, "'", "''")
End Function

Use it like...

VB Code:

INSERT INTO mytable (name) values ('" & CleanText(Variable) & "')".

If you use

VB Code:

rs!Name = Text1.Text

will add exactly, otherwise you have to replace single quote with double quotes and have to manage the conversion

Quote:

---

Originally Posted by ganeshmoorthy

If you use

VB Code:

rs!Name = Text1.Text

will add exactly, otherwise you have to replace single quote with double quotes and have to manage the conversion

---

Not double quote, two single quotes... ;)

The better way of doing this is to build a prepared query. Prepared queries are faster and secure compared to the queries written on the fly. You do not have to take care of special characters in prepared statements and they are compiled. Take a look at the Command object of ADODB library or else do a search in the Forum and you will find lots of examples.

Thank you for the reply. One more thing:
Is single quote the only thing that causes problems or are there other characters too that we have to check for?

Quote:

---

Originally Posted by srisa

Thank you for the reply. One more thing:
Is single quote the only thing that causes problems or are there other characters too that we have to check for?

---

As I said there might be other special characters too, but you cannot go on replacing each one of them. What you should do is to use Prepared Statements. Take a look at how this piece of code that shows how to write prepared statements

VB Code:

Dim cmdSQLInsert As ADODB.Command
Set cmdSQLInsert = New ADODB.Command
 
'Create the query
cmdSQLInsert.CommandText = "Insert Into Table1(ID, NAME, AGE) Values(?,?,?)"
cmdSQLInsert.CommandType = adCmdText
cmdSQLInsert.Prepared = True
 
'Create the parameters
'in this case we will create three parameters
'-----Param 1 (for Field ID)-------------
Dim gParam As ADODB.Parameter
Set gParam = New ADODB.Parameter
With gParam
    .Name = "ID"
    .Direction = adParamInput
    .Type = adChar
    .Size = 10
    .Value = "xxxxxxxxxx"
End With
cmdSQLInsert.Parameters.Append gParam
 
'-----Param 2 (for Field Name)-------------
Set gParam = Nothing
Set gParam = New ADODB.Parameter
With gParam
    .Name = "NAME"
    .Direction = adParamInput
    .Type = adVarChar
    .Size = 50
    .Value = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
End With
cmdSQLInsert.Parameters.Append gParam
 
'-----Param 3 (for Field AGE)-------------
Set gParam = Nothing
Set gParam = New ADODB.Parameter
With gParam
    .Name = "AGE"
    .Direction = adParamInput
    .Type = adChar
    .Size = 2
    .Value = "xx"
End With
cmdSQLInsert.Parameters.Append gParam
 
'Set the connection property of the command object
Set cmdSQLInsert.ActiveConnection = mySQLConnection
'pass the values that need to be inserted to specific parameters that we created above
cmdSQLInsert("ID") = txtID.Text
cmdSQLInsert("NAME") = txtName.Text
cmdSQLInsert("AGE") = txtAge.Text
 
'Execute the command
cmdSQLInsert.Execute

I Thank all of you.