check success of sql insert query

VB Code:

If (cn.State = adStateOpen) Then
                    Dim Str As String
                    Str = "INSERT INTO " & SQLTable & "(Username, Password) VALUES(" & Text1.Text & "," & Text2.Text & ")"
                    adoRS.Open str, cn 
                End If

how can i tell if the insert was successful?
and does the inset syntax look correct to you guys? i havent tested it yet

edit**********
tested it and it throws an 'sql sntax' error :confused:

Str = "INSERT INTO " & SQLTable & "(Username, Password) VALUES('" & Text1.Text & "','" & Text2.Text & "')"


those are strings....

so it needed the ' '

Well, first off, you need to encapsulate string entries in single quotes

VB Code:

VALUES('" & Text1.Text & "','" & Text2.Text & "')"

Also, you OPEN a recordset that is created using a SELECT. For action queries, you use the Execute method of the connection object.

VB Code:

cn.Execute str

thanks, its still throwing the error :confused:

Quote:

---

Originally Posted by Pouncer

thanks, its still throwing the error :confused:

---

1. Post what you currently have.

2. What error is it throwing?

Quote:

---

Originally Posted by Hack

Well, first off, you need to encapsulate string entries in single quotes

VB Code:

VALUES('" & Text1.Text & "','" & Text2.Text & "')"

Also, you OPEN a recordset that is created using a SELECT. For action queries, you use the Execute method of the connection object.

VB Code:

cn.Execute str

---

oh yeah... execute ;) lol


Pouncer.. whats the error and what line is it stopping at?

Password is a keyword. Place square brackets around field names that are also keywords.

& "(Username, [Password]) VALUES(" &

Also, add a space between the table name and the list of columns

Str = "INSERT INTO " & SQLTable & " (Username

[Edit]
Sorry, I just ran a quick test. The space is not required...

Quote:

---

Originally Posted by brucevde

Password is a keyword. Place square brackets around field names that are also keywords.

---

I completely forgot about that. I always think in terms of PWD = whatever.....but, he is right....the word Password is a keyword. :thumb:

Quote:

---

Originally Posted by brucevde

Sorry, I just ran a quick test. The space is not required...

---

I have run into problems with VALUES clauses and spaces, or more accurately, the lack of a space, so I think it is always a good idea to put a space between the table name and the list of columns.

ok thansk guys i got it to work..

VB Code:

Str = "INSERT INTO testuser (Username, Password) VALUES('" & Text1.Text & "','" & Text2.Text & "')"
                    cn.Execute Str

but how can i make it msgbox or something after the execute was successful

VB Code:

Str = "SELECT COUNT("username") FROM " & SQLTable & " WHERE username = '" & Text1.Text & "',"
adoRS.Open str, cn
 
Msgbox adoRS.Fields.Item("username").Value

If the message box is blank, your INSERT failed.

nice one Hack. :wave:

Another option

VB Code:

Dim lngRowsAffected As Long 
 
Str = "INSERT INTO testuser (Username, Password) VALUES('" & Text1.Text & "','" & Text2.Text & "')"
cn.Execute Str, lngRowsAffected
 
If lngRowsAffected > 0 Then
   msgbox "Insert Successful"
End if