6 Replies - 1127 Views - Last Post: 02 January 2012 - 04:03 PM Rate Topic: -----

#1 Jezza33  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 27-December 11

Syntax error in my INSERT INTO statement dont know why

Posted 27 December 2011 - 05:04 PM

hey guys I been trying to work out why ive been getting syntax error and i cant seem to find out why. ive been using vb for about a year now so im still learning!! please please help me

Dim DB_Name As String
Dim SQL_Str As String
Dim Conn As ADODB.Connection
Dim ctl As Control

'Get the data
DB_Name = App.Path
If Right$(DB_Name, 1) <> "" Then DB_Name = DB_Name & ""
DB_Name = DB_Name & "Users.mdb"
'Open a connection.
Set Conn = New ADODB.Connection

Conn.ConnectionString = "PROVIDER=Microsoft.JET.OLEDB.4.0;" & _
                   "DATA SOURCE=" & App.Path & "\Users.mdb;" & _
                   "PERSIST SECURITY INFO=FALSE;"

Conn.Open
'Compose the INSERT statement.
strSQL = "insert into Users (UserName, PassWord) values (" _
& "'" & txtname & "', " _
& "'" & txtSurname & "') "

'strSQL = App.Path
'Execute the statement.
Conn.Execute strSQL, objADOCon
'Close the connection.
Conn.Close

'Clear the TextBoxes.

For Each ctl In Controls
If TypeOf ctl Is TextBox Then
ctl.Text = ""
End If
Next ctl

MsgBox ("YEHAHAHAHAHAHAH")


Is This A Good Question/Topic? 0
  • +

Replies To: Syntax error in my INSERT INTO statement dont know why

#2 smohd  Icon User is offline

  • Critical Section
  • member icon


Reputation: 1752
  • View blog
  • Posts: 4,409
  • Joined: 14-March 10

Re: Syntax error in my INSERT INTO statement dont know why

Posted 27 December 2011 - 05:29 PM

Looking at this part:
strSQL = "insert into Users (UserName, PassWord) values (" _
& "'" & txtname & "', " _
& "'" & txtSurname & "') "

And I am guessing that txtname and txtSurname are textBoxes, and so you will need their Text properties to be passed there. So you will need txtSurname.Text and so.
I used Text because itt looks like vb.net because you used text at
ctl.Text = ""

This post has been edited by smohd: 27 December 2011 - 05:31 PM

Was This Post Helpful? 0
  • +
  • -

#3 Jezza33  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 27-December 11

Re: Syntax error in my INSERT INTO statement dont know why

Posted 28 December 2011 - 06:48 AM

thanks for replying smohd you could be right the code may be from vb net as i found it online and i try to alter it for vb6. its really hard finding codes just for vb6 whithout comming across vb NET. i have done what you requested but the program is still highlighting this bit line 25
Conn.Execute strSQL, objADOCon
so im not sure what does it want really

i will attach a screen shot of the error thats keeps pooping up, in the mean while im going to use mother google for some solution but i hope somebody here could relly help me out!

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#4 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: Syntax error in my INSERT INTO statement dont know why

Posted 01 January 2012 - 10:11 PM

View Postsmohd, on 28 December 2011 - 12:29 AM, said:

Looking at this part:
strSQL = "insert into Users (UserName, PassWord) values (" _
& "'" & txtname & "', " _
& "'" & txtSurname & "') "

And I am guessing that txtname and txtSurname are textBoxes, and so you will need their Text properties to be passed there. So you will need txtSurname.Text and so.
I used Text because itt looks like vb.net because you used text at
ctl.Text = ""

Text is the default property of the text box, so not specifying it will still work. Better practice is to specify it, however, as it makes the code easier to understand. Here we have a case in point. :)

jezza, I would suggest that you open the immediate window. when you get your error, go to the immediate window and type ? strsql. Look at that and make sure it's right.

It looks ok to me, so I would also suggest that you paste the code into SQL server directly and see if it runs from there. You may well have an error with your table names, field names, or whatever.
Was This Post Helpful? 0
  • +
  • -

#5 guyfromri  Icon User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 805
  • Joined: 16-September 09

Re: Syntax error in my INSERT INTO statement dont know why

Posted 02 January 2012 - 02:20 AM

Ok -- my 2 cents :)

1. When you have an issue like this, start simple -- take the query and run it in a query module directly in the mdb. Open a new query, at the top left select SQL then paste it there with some hard values for the username and surname. That will give you the exact error not just a basic SQL error.

2. It appears that you have more going on than you need to in your code -- you don't need to define extra double quotes to get the single quote in there -- that just creates more room for error.

3. If it were me, I would start by running step 1. If you can't for some reason then you could move on to writing two separate queries -- one with just username and one with just password. One is going to throw the error and that helps narrow down the problem.

4. Also, unless they're global variables, I don't see txtname or txtSurName listed anywhere in the sub before you call them. I suppose the guess would be that they're textboxes but if so, shouldn't you be checking the values before you go inserting them to the table? Open data like that can cause huge errors down the road. Where are you assigning the values? If the DB has a no null for one of those fields and the strings are null, that would create an error as well. If they are in fact textboxes then you have to be more specific textSurName.text or textSurName.value.

5. Lastly -- you have SQL_Str defined as a variable but then you're using strSQL the rest of the sub -- are there more variables somewhere else?

The shortened version of your code
strSQL = "INSERT INTO Users(UserName, Password) VALUES('" & txtname.text & "', '" & txtSurName.text & "')"



Also just because I'm incredibly bored, here's a cleaned up version of your code -- as long as the connections are good, this should do the trick
Dim DB_Name As String
Dim strSQL As String
Dim Conn As New ADODB.Connection
Dim ctl As Control

'Open a connection.
With Conn
    .ConnectionString = "PROVIDER=Microsoft.JET.OLEDB.4.0;" & _
                   "DATA SOURCE=" & App.Path & "\Users.mdb;" & _
                   "PERSIST SECURITY INFO=FALSE;"
    .Open
End With

'Compose the INSERT statement.
strSQL = "INSERT INTO Users(UserName, Password) VALUES('" & txtname.Text & "', '" & txtSurName.Text & "')"

'Execute the statement.
Conn.Execute strSQL, objADOCon

'Close the connection.
Conn.Close
Set Conn = ""

'Clear the TextBoxes.
For Each ctl In Controls
    If TypeOf ctl Is TextBox Then ctl.Text = ""
Next ctl

MsgBox ("YEHAHAHAHAHAHAH")


Hope that helps :)

This post has been edited by guyfromri: 02 January 2012 - 02:38 AM

Was This Post Helpful? 0
  • +
  • -

#6 Jezza33  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 27-December 11

Re: Syntax error in my INSERT INTO statement dont know why

Posted 02 January 2012 - 02:09 PM

hey guys thanks id figure another way in which i can do the same, by ussing data controls and data grids but thanks i reallly appreciate your feeds back. currently im working on a college individual project ao and anytime soon i might be back on here begging for you experts help:L im fairly new to all these programming ive been doing using vb6 for like a 9months. but i must admit encounting this problem i learned alot on the wayy to find a solution or another wayy around it!! Thanks guys i really appriciate it i learned so much on this website is well so keep it going yall doing a great job just please next time if you see another post of mine please dont ignore It need you guys :L and lool thanks D.I.C addict for cleannign up my code its amazing what boredom can do!! Thanks guys much appriciate it!!
Was This Post Helpful? 0
  • +
  • -

#7 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: Syntax error in my INSERT INTO statement dont know why

Posted 02 January 2012 - 04:03 PM

Well, you were going about it the professional way and now you have changed to the amateur way. Glad you got it working, but you'll want to figure out your first code before you go much further. Try changing "password" to "pword" and see what develops. If it works, read this.

This post has been edited by BobRodes: 02 January 2012 - 04:04 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1