7 Replies - 9227 Views - Last Post: 15 March 2011 - 10:52 AM Rate Topic: -----

#1 willvb  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 8
  • Joined: 14-March 11

insert data from vb6 form to access database

Posted 14 March 2011 - 08:24 AM

i have developed a vb6 code that inserts data from a form into an access database table, the program compiles without any errors but when i run it i get this error message

run-time error '-2147217900(8004e14)':
syntax error in string in query expression "2)'

db.Execute ("INSERT INTO infant VALUES('" & txtinfantid.Text & "'," & _
    txtfirstname.Text & ",'" & txtsurname.Text & "','" & DTP.Value & "','" & _
    txtweight.Text & "','" & txtbirthtype.Text & "'," & cmbsex.Text & ",' " & _
    cmbpatientno.Text & ")")

This post has been edited by AdamSpeight2008: 15 March 2011 - 11:57 AM


Is This A Good Question/Topic? 0
  • +

Replies To: insert data from vb6 form to access database

#2 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 300
  • View blog
  • Posts: 1,778
  • Joined: 26-March 09

Re: insert data from vb6 form to access database

Posted 14 March 2011 - 08:43 AM

Maybe you could assign your query to a variable, then do a debug.print on the string and verify that it looks like a valid sql query.
Was This Post Helpful? 0
  • +
  • -

#3 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: insert data from vb6 form to access database

Posted 14 March 2011 - 11:27 AM

Which it isn't. I see several things wrong with it, and I'll tell you one of them to prove my point. Your first name isn't enclosed in single quotes as it must be. There are others--you have another string that is missing quotes, and one string that has one on one side and not on the other--and you will follow maj's advice if you want the easiest way to find them.
Was This Post Helpful? 0
  • +
  • -

#4 willvb  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 8
  • Joined: 14-March 11

Re: insert data from vb6 form to access database

Posted 14 March 2011 - 10:53 PM

View Postmaj3091, on 14 March 2011 - 08:43 AM, said:

Maybe you could assign your query to a variable, then do a debug.print on the string and verify that it looks like a valid sql query.

i haven't used debug.print before can you show me the example

i have not used debug.print before

can you give me an example using my code
Was This Post Helpful? 0
  • +
  • -

#5 willvb  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 8
  • Joined: 14-March 11

Re: insert data from vb6 form to access database

Posted 14 March 2011 - 11:10 PM

View PostBobRodes, on 14 March 2011 - 11:27 AM, said:

Which it isn't. I see several things wrong with it, and I'll tell you one of them to prove my point. Your first name isn't enclosed in single quotes as it must be. There are others--you have another string that is missing quotes, and one string that has one on one side and not on the other--and you will follow maj's advice if you want the easiest way to find them.



Thanks BobRodes, i have just added the missing single quotes and it's now working,
that's wonderful
Was This Post Helpful? 1
  • +
  • -

#6 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: insert data from vb6 form to access database

Posted 15 March 2011 - 08:55 AM

Glad I could help. :) Did you figure out how to use Debug.Print then?
Was This Post Helpful? 0
  • +
  • -

#7 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 300
  • View blog
  • Posts: 1,778
  • Joined: 26-March 09

Re: insert data from vb6 form to access database

Posted 15 March 2011 - 09:10 AM

Glad you sorted it. Bob's good at spotting things like that.

In regards to the debug.print, it's one of a couple of ways of doing something to get a similar result.

dim strSQL as string
strSQL = "your SQL query goes here"
debug.print strSQL



When you run this, in the immediate window of the IDE, you will see the output from the debug.print.

Alternatively, if you step your code in the IDE, you can just type into the immediate window
print strSQL
, or you could open up the Locals window whilst stepping through, which will show you all the local variable values.

Hope that gives you some pointers for future.
Was This Post Helpful? 0
  • +
  • -

#8 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: insert data from vb6 form to access database

Posted 15 March 2011 - 10:52 AM

FYI, "? strSQL" works the same way as "print strSQL". You can use either one according to your preference.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1