[RESOLVED] SQL INSERT error

[osemollie]

I am trying to INSERT new data into an MsACCESS 2003 DB. The field am trying to populate is comments field, with a field type of memo. When I type my comments with an apostrophe (') I get an error when submitting the info but without the apostrophe, it is Ok. How can I code to make sure that the apostrophe does not create an error?

[Al42]

Replacing each single tick with 2 tics ("escaping" the single quotes) should do it:

<your text variable here> = Replace(<your text variable here>, "'", "''")

[Static]

Access doesnt like '

how are u adding it? (Post code)

[Shuja Ali]

You should either replace the apostrophe with two apostrophes using Replace. However, I personally do not recommend that instead you should be using parametrized queries. Take a look at this thread expecially post #5
http://www.vbforums.com/showthread.p...ighlight=Query

[osemollie]

The code is:-

sSQL = sSQL = "INSERT INTO movieInfo (title, actor, producer, comment) VALUES ("
sSQL = sSQL & Val(txtTitle.Text) & ", "
sSQL = sSQL & "'" & txtactor.Text & "', "
sSQL = sSQL & "'" & txtproducer.Text & "', "
sSQL & "'" & txtComment.Text & "') "

Thanks

[Shuja Ali]

Did you check out the link that I posted above?

[Static]

try using " instead of '

sSQL = sSQL = "INSERT INTO movieInfo (title, actor, producer, comment) VALUES ("
sSQL = sSQL & Val(txtTitle.Text) & ", "
sSQL = sSQL & Chr(34) & txtactor.Text & Chr(34) & ", "
sSQL = sSQL & Chr(34) & txtproducer.Text & Chr(34) & ", "
sSQL = sSQL & Chr(34) & txtComment.Text & Chr(34) & ") "

[GaryMazzone]

Does the producer or actor name look this: O'Neil? If so then ye single qoute (') need to be replaced byu two single qoutes (''). The same goes for the comment text if Sam's is used then the single qoute again needs to be replaced by two single qoutes.

[techgnome]

Ugh....

Parameterized query folks.... Parameterized queries....

sSQL = "INSERT INTO movieInfo (title, actor, producer, comment) VALUES (?,?,?,?)
 
objCommand.CommandText = sSQL
objCommand.CommandType = adCommandText
objCommand.Parameters.Add objCommand.CreateParameter("title",advarchar,50, txtTitle.Text)
objCommand.Parameters.Add objCommand.CreateParameter("actor",advarchar,50, txtactor.Text)
objCommand.Parameters.Add objCommand.CreateParameter("producer",advarchar,50, txtproducer.Text)
objCommand.Parameters.Add objCommand.CreateParameter("comment",advarchar,50, txtComment.Text)
 
objCommand.Execute

Just change the 50 to suit your needs (it's the length of the field in the database.

-tg

edit: I think I also forgot a parameter or two in the createparameter function, so don't copy paste it directly.... I'm doing this off of memory here.

[osemollie]

Guys, thanks for your views, I have tried the two types and they work.

Thanks a million.

[SeanK]

Ugh....

Parameterized query folks.... Parameterized queries....

sSQL = "INSERT INTO movieInfo (title, actor, producer, comment) VALUES (?,?,?,?)
 
objCommand.CommandText = sSQL
objCommand.CommandType = adCommandText
objCommand.Parameters.Add objCommand.CreateParameter("title",advarchar,50, txtTitle.Text)
objCommand.Parameters.Add objCommand.CreateParameter("actor",advarchar,50, txtactor.Text)
objCommand.Parameters.Add objCommand.CreateParameter("producer",advarchar,50, txtproducer.Text)
objCommand.Parameters.Add objCommand.CreateParameter("comment",advarchar,50, txtComment.Text)
 
objCommand.Execute

Just change the 50 to suit your needs (it's the length of the field in the database.

-tg

edit: I think I also forgot a parameter or two in the createparameter function, so don't copy paste it directly.... I'm doing this off of memory here.

Would the syntax for this, following this example, be this

sSQL = "INSERT INTO movieInfo (title, actor, producer, comment) VALUES ('" & txtTitle.Text & "', '" & txtActor.Text & "', '" & txtProducer.Text & "', '" & txtComment.Text & "')"
 
objCommand.CommandText = sSQL
objCommand.CommandType = adCommandText
objCommand.Parameters.Add objCommand.CreateParameter("title",advarchar,50, txtTitle.Text)
objCommand.Parameters.Add objCommand.CreateParameter("actor",advarchar,50, txtactor.Text)
objCommand.Parameters.Add objCommand.CreateParameter("producer",advarchar,50, txtproducer.Text)
objCommand.Parameters.Add objCommand.CreateParameter("comment",advarchar,50, txtComment.Text)
 
objCommand.Execute sSQL

Don't you need the sSQL after the .Execute?

[leinad31]

Its already assigned to the command object's CommandText property SeanK.

[techgnome]

No, and the sSQL is wrong anyways.... in your example, there isn't any placeholders for the data... you've concatenated them back in... this thread is the perfect example of why string concatenation is not recommended for SQL strings.... as evidence has shown problems arise when string delimeters show up in the data. By using parameterized queries, the problem goes away as ADO will take care of the translation for you. Also prevents the possibility of a SQL Injection attack....

-tg

[SeanK]

No, and the sSQL is wrong anyways.... in your example, there isn't any placeholders for the data... you've concatenated them back in... this thread is the perfect example of why string concatenation is not recommended for SQL strings.... as evidence has shown problems arise when string delimeters show up in the data. By using parameterized queries, the problem goes away as ADO will take care of the translation for you. Also prevents the possibility of a SQL Injection attack....

-tg

Ok..geez. I have slapped myself.

However, I don't understand, given all of this, how the VALUES clause would work. You posted

sSQL = "INSERT INTO movieInfo (title, actor, producer, comment) VALUES (?,?,?,?)
 
objCommand.CommandText = sSQL
objCommand.CommandType = adCommandText
objCommand.Parameters.Add objCommand.CreateParameter("title",advarchar,50, txtTitle.Text)
objCommand.Parameters.Add objCommand.CreateParameter("actor",advarchar,50, txtactor.Text)
objCommand.Parameters.Add objCommand.CreateParameter("producer",advarchar,50, txtproducer.Text)
objCommand.Parameters.Add objCommand.CreateParameter("comment",advarchar,50, txtComment.Text)
 
objCommand.Execute

Would I actually put question marks in the VALUES clause?

[leinad31]

Yes, and its interpreted as placeholders for parameters... the values of which are provided by the parameters collection of the command object.

objCommand.Parameters.Add objCommand.CreateParameter("title",advarchar,50, [b]txtTitle.Text[/b])
objCommand.Parameters.Add objCommand.CreateParameter("actor",advarchar,50, [b]txtactor.Text[/b])
objCommand.Parameters.Add objCommand.CreateParameter("producer",advarchar,50, [b]txtproducer.Text[/b])
objCommand.Parameters.Add objCommand.CreateParameter("comment",advarchar,50, [b]txtComment.Text[/b])

The replacement/update of the values is done by FIFO (first parameter object provides data for the first ?)

After all parameters have values then the query executes without an error.

[SeanK]

Yes, and its interpreted as placeholders for parameters... the values of which are provided by the parameters collection of the command object.

objCommand.Parameters.Add objCommand.CreateParameter("title",advarchar,50, [b]txtTitle.Text[/b])
objCommand.Parameters.Add objCommand.CreateParameter("actor",advarchar,50, [b]txtactor.Text[/b])
objCommand.Parameters.Add objCommand.CreateParameter("producer",advarchar,50, [b]txtproducer.Text[/b])
objCommand.Parameters.Add objCommand.CreateParameter("comment",advarchar,50, [b]txtComment.Text[/b])

The replacement/update of the values is done by FIFO (first parameter object provides data for the first ?)

After all parameters have values then the query executes without an error.

Ok...cool. I think I'm actually starting to get this (and like it )

So, I would use advarchar for text. What would I use to insert values into a int field?

Also, is this something that is confined to INSERTS or would I do the same thing with UPDATES?

[techgnome]

adinteger .... when you press the , to move from the parameter name to the data type, you *should* be getting intellisense that will give you a list of the ad datatypes....

you can do this on inserts, deletes, updates, and even selects....

-tg

[SeanK]

adinteger .... when you press the , to move from the parameter name to the data type, you *should* be getting intellisense that will give you a list of the ad datatypes....

you can do this on inserts, deletes, updates, and even selects....

-tg

Cool.

Is it possible for a thread to be closed twice by two different people?

Thanks techgnome and leinad31. I learned a lot from this.

And, thank you osemollie for asking the question in the first place.