escape character in oledb sql command

S

Stijn Vanpoucke

Hi,
I've made a program with an access database. In my sql insert command I need
to use escape characters to insert text strings but te problem is that I
want to use escape chars in my text strings themselves to.

strSQL = "INSERT INTO tblKlanten (Naam, Voornaam, Adres, Postnummer,
Telefoon, Fax, Gsm, Email, Gastvrouw, Matras, Lattenbodum, Waveflex,
Donsdeken, Btwnummer, Geboortedatum, Echtgenoot, Opmerkingen, Aankooplb)"

strSQL += " VALUES ('" & txtNaam.Text & "','" & txtVoornaam.Text & "','" &
txtAdres.Text & "', " & cboWoonplaats.SelectedValue & ", '" &
txtTelefoon.Text & "', '" & txtFax.Text & "', '" & txtGsm.Text & "','" &
txtMail.Text & "','" & chkGastvrouw.CheckState & "', '" &
chkMatras.CheckState & "', '" & chkLattenbodum.CheckState & "', '" &
chkWaveflex.CheckState & "', '" & chkDonsdeken.CheckState & "', '" &
txtBtw.Text & "', '" & txtGeboortedatum.Text & "', '" & txtEchtgenoot.Text &
"', '" & txtOpmerkingen.Text & "', '" & txtAankooplb.Text & "' )"

So like txtNaam.text could be 'test'a' and i must be something like "test'a"
but I need to use '

I hope someone understands my problem, and coulde give an answer.
 
H

Herfried K. Wagner [MVP]

* "Stijn Vanpoucke said:
I've made a program with an access database. In my sql insert command I need
to use escape characters to insert text strings but te problem is that I
want to use escape chars in my text strings themselves to.

strSQL = "INSERT INTO tblKlanten (Naam, Voornaam, Adres, Postnummer,
Telefoon, Fax, Gsm, Email, Gastvrouw, Matras, Lattenbodum, Waveflex,
Donsdeken, Btwnummer, Geboortedatum, Echtgenoot, Opmerkingen, Aankooplb)"

strSQL += " VALUES ('" & txtNaam.Text & "','" & txtVoornaam.Text & "','" &
txtAdres.Text & "', " & cboWoonplaats.SelectedValue & ", '" &
txtTelefoon.Text & "', '" & txtFax.Text & "', '" & txtGsm.Text & "','" &
txtMail.Text & "','" & chkGastvrouw.CheckState & "', '" &
chkMatras.CheckState & "', '" & chkLattenbodum.CheckState & "', '" &
chkWaveflex.CheckState & "', '" & chkDonsdeken.CheckState & "', '" &
txtBtw.Text & "', '" & txtGeboortedatum.Text & "', '" & txtEchtgenoot.Text &
"', '" & txtOpmerkingen.Text & "', '" & txtAankooplb.Text & "' )"

So like txtNaam.text could be 'test'a' and i must be something like "test'a"
but I need to use '
Click to expand...

Try "test''a" to search for "test'a".
 
A

Alex Papadimoulis

Stijin,

Sounds like a case for ... drumroll ... paramaters!

It's a bad practice to build a SQL string like that. You're ripe for
Injection attacks and all other unfun things. Try this instead ...

Dim myCon as New OleDbConnection( <connectionString> )
Dim myCmd as New OleDbCommand()
With myCmd
.Connection = myCon
.CommandText = "INSERT INTO myTbl (col1,col2,col3) VALUES (?,?,?)"
.Paramaters.Add( <ValueOfCol1> )
.Paramaters.Add( <ValueOfCol2> )
.Paramaters.Add( <ValueOfCol3> )
End With

myCon.Open()
myCmd.ExecuteNonQuery()
myCon.Close()
 
S

Stijn Vanpoucke

I've tried it this way with some less colls to test it.

----------------------------------------------------------------------------
---------------------------------------------------------

'sql commando

strSQL = "INSERT INTO tblKlanten (Naam, Voornaam, Adres) "

strSQL += " VALUES (@Naam, @Voornaam , @Adres)"



Dim cmdvoegtoe As New OleDb.OleDbCommand(strSQL, Objverbinding)

With cmdvoegtoe

..Connection = Objverbinding

..CommandText = strSQL

..Parameters.Add("@Naam", OleDb.OleDbType.VarChar, 40, txtNaam.Text)

..Parameters.Add("@Voornaam", OleDb.OleDbType.VarChar, 40, txtVoornaam.Text)

..Parameters.Add("@Adres", OleDb.OleDbType.VarChar, 40, txtAdres.Text)

End With

Objverbinding.Open()

cmdvoegtoe.ExecuteNonQuery()

Objverbinding.Close()

----------------------------------------------------------------------------
----------------------------------------------------------------------------
------------------

No I get:

Parapmeter @name does not have a standard value

What's my fault?
 
C

Cor Ligthert

Hi Stijn,

Can you try this.
\\\
strSQL = "INSERT INTO tblKlanten (Naam, Voornaam, Adres) "
strSQL += " VALUES (@Naam, @Voornaam , @Adres)"
Dim cmdvoegtoe As New OleDb.OleDbCommand(strSQL, Objverbinding)
cmdvoegtoe.Parameters.Add("@Naam", txtNaam.Text)
cmdvoegtoe.Parameters.Add("@Voornaam", txtVoornaam.Text)
cmdvoegtoe.Parameters.Add("@Adres", txtAdres.Text)
Objverbinding.Open()
cmdvoegtoe.ExecuteNonQuery()
Objverbinding.Close()
///
This should normally be enough, there is no @Name in it by the way is there
not a typo?

Cor
 
S

Stijn Vanpoucke

Hi,
I've tried it before and it wouldn't work but now with the short version of
the sql it does :blush:)

thx
 
Top