Run-time error (-2147217900(80040e14)

[goobil]

Can some one please explane why this insert not working i am geting an error.Run-time error (-2147217900(80040e14)
Syntax error in insert into statement.

Private Sub cmdtest_Click()
Dim test As String
Dim oConn As New ADODB.Connection
Dim strSQL As String
Dim rsdb As New ADODB.Recordset
Dim xreg, xtitle, xfirst, xmiddle, xlast, xstreet, xcity, xzip, xcountry, xstate As String

xreg = txtReg.Text

Set oConn = CreateObject("ADODB.Connection")
Set rsdb = CreateObject("ADODB.RecordSet")
'Set rsd = CreateObject("Database")

oConn.Mode = adModeShareExclusive
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\holyfamily\data\holyfamily.mdb;"
test = txtbilly.Text
oConn.Execute "INSERT INTO personalinfo (Registration)VALUES("'&xreg&'")

 

[peekay]

Try this :

oConn.Execute "INSERT INTO personalinfo (Registration) VALUES (chr(39) & xreg & chr(39))"

PK Odendaal

http://www.odendaal.com

 

[goobil]

Sorry to say but i have tried that too.
Any other suggestion?

 

[HarleyQuinn]

Hi Goobil,

Instead of "INSERT INTO personalinfo (Registration)VALUES("'&xreg&'")"

Try this:

"INSERT INTO personalinfo (Registration)VALUES('"&xreg&"')"

i.e. swap the ' and " round in the parentheses.

That should work.

Thanks

Harleyquinn

 

[peekay]

Sorry - I left out "

oConn.Execute "INSERT INTO personalinfo (Registration) VALUES (chr(39) & xreg & chr(39))"

should be :

oConn.Execute "INSERT INTO personalinfo (Registration) VALUES (" & chr(39) & xreg & chr(39)& ")"

PK Odendaal

http://www.odendaal.com

 

[ArtieChoke]

One thing I noticed in your declarations:

Dim xreg, xtitle, xfirst, xmiddle, xlast, xstreet, xcity, xzip, xcountry, xstate As String

Are you aware all values except xstate are variant?

At any rate, I'll assume xreg is a text value. When inserting text into a field, you need to surround it with single quotes.

So your insert should look like:

"INSERT INTO personalinfo (Registration) VALUES ([red]'[/red]" & xreg & "[red]'[/red])"

which Harleyquinn also showed above.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[DrJavaJoe]

Next time do something like:

Debug.print "INSERT INTO personalinfo (Registration)VALUES("'&xreg&'")"

to see what the actual sql statement is that you are trying to execute, you'll noticed the error immediately.

Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[ArtieChoke]

That's why I usually set up sql statements in a variable so I can view them in the immediate window when they break!

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[goobil]

Hi all,
Thanks for the help. It is working know i don’t know what i will do with out you guys.

I will be posting as time goes by because i will be doing VB stuff full time from know on.
Once again thanks.

PS: One more question I have a textbox on the form and I want it to show the time when the form load how do I do that?

I have xtime = Now()
Do I need to used the onload form

 

[peekay]

You can use the on load event or form activate event and just set txtTime.text=now

PK Odendaal

http://www.odendaal.com

 

[goobil]

Thanks

 

[Vincentan]

Hi All,

I face the same problem where my record show for example ANNUAR BIN SA'AD

For the test include the ' sign it will generate an erro while insert into statement, how to handle this problem?

textbox1.text = "ANNUAR BIN SA'AD"
ConSql.Execute "INSERT INTO TABLES(NAME) VALUES('" & textbox1.text & "')"

Please help
Thanks
Vincent

 

[gmmastros]

try this.

ConSql.Execute "INSERT INTO TABLES(NAME) VALUES('" & Replace(textbox1.text, "'", "''") & "')"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Vincentan]

Hi George,

There still same without any changing! and I try to replace with * or space but it seems like not working too.

By the way where you found this code? Thanks

Vincent

 

[Vincentan]

Hi George,

Sorry for my mistake it actually works just I place a wrong part in my statement.

But any how is ther any possible way to insert this single quote ' into my sql statement and I can insert into my database? instead of I replace it to another quote?

Thanks
Vincent

 

[tsdragon]

You're not replacing it with another quote, you're DOUBLING it. SQL statements treat '' as a single ' within values, allowing you to embed apostrophes within values delimited by apostrophes.

VB does exactly the same thing with quotes. To embed a quote within a quoted string, code two of them.


Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[BobRodes]

As for Variant variables, the reason that this can be a problem is that they require more space to store values, as well as added overhead to figure out what kind of variables they are.

Bob

 

[Vincentan]

Thanks for your advise! tsdragon can you give me some example of what you means?

Vincent

 

[BobRodes]

Here's a sample that might help:

rs.Open "SELECT * FROM Customers WHERE cust_lname = 'O''BRIEN'"

Where rs is a valid ADODB.Recorset object. This will find the string O'BRIEN in the cust_lname field in SQL Server. The double quotes delimit the string in the VB context; the single quotes delimit the string in the SQL Server context, the doubled single quotes (get it? as in '', not ") mean a literal apostrophe to SQL Server.

Bob

 

[Vincentan]

IC, Thanks Bob and I understand now!