check success of sql insert query

[Pouncer]

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

[Static]

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


those are strings....

so it needed the ' '

[Hack]

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

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.

cn.Execute str

[Pouncer]

thanks, its still throwing the error

[Hack]

thanks, its still throwing the error

1. Post what you currently have.

2. What error is it throwing?

[Static]

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

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.

cn.Execute str

oh yeah... execute lol


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

[brucevde]

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...

[Hack]

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.

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.

[Pouncer]

ok thansk guys i got it to work..

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

[Hack]

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.

[Pouncer]

nice one Hack.

[brucevde]

Another option

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