problem in insert instruction

bilro

Member
Joined
Jun 9, 2004
Messages
9
Programming Experience
3-5
i have a table in MS Access 'users'
that table have these fields:
id (auto-increment)
login (text)
password (text)
type (number)

when i try to insert a new row into the table :

Dim sql As String
sql = "INSERT INTO users (login,password,type) VALUES ('" + Me.txt_name.Text + "','" + Me.txt_password.Text + "'," + Me.cmb_typo.SelectedValue + ")"

Try
Dim connection As OleDbConnection = New OleDbConnection(Me.str_con)
Dim cmd As New OleDbCommand(sql, connection)
Dim num As Integer
cmd.CommandType = CommandType.Text
connection.Open()
num = cmd.ExecuteNonQuery()
msgbox ("success")
connection.Close()
Catch ex As Exception
Me.msg.msg_info(ex.Message)
End Try

it gives me an sintax error in the INSERT INTO instruction

if i try:
sql = "INSERT INTO users (login,password,typo) VALUES ('1','1',1)"
it gives me the same error.

can anyone help me?
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
Try this:
sql = "INSERT INTO [users] (login,password,type) VALUES ('" + Me.txt_name.Text + "','" + Me.txt_password.Text + "'," + Me.cmb_typo.SelectedValue + ")"

Also what's you may need to add the aspnet user to the permissions of the DB file.

If that doesn't fix it let me know what the error is.

TPM
 

bilro

Member
Joined
Jun 9, 2004
Messages
9
Programming Experience
3-5
it still gives me the same error.
i´m using vb.net to insert the values into the table using Microsoft Jet 4.0 Provider
the user has full access to the database and in the access he can add the rows...
the problem is, and i don't know why, in the sql statement
even when i try
sql = "INSERT INTO users (login,password,type) VALUES ('1','1',1)"
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
What is the error?
 

bilro

Member
Joined
Jun 9, 2004
Messages
9
Programming Experience
3-5
the error is in [pt]
in [en] it would be something like:
syntax error in the INSERT INTO instruction...
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
And you tried :
sql = "INSERT INTO [users] (login,password,type) VALUES ('1','1',1)"
 

bilro

Member
Joined
Jun 9, 2004
Messages
9
Programming Experience
3-5
TPM said:
And you tried :
sql = "INSERT INTO [users] (login,password,type) VALUES ('1','1',1)"
yeah

and also:
sql = "INSERT INTO users ('login','password','type') VALUES ('1','1',1)
sql = "INSERT INTO [users] ('login','password','type') VALUES ('1','1',1)
sql = "INSERT INTO users (login,password,type) VALUES ('1','1',1)

but at the end i made it:

sql = "INSERT INTO [users] ([login],[password],[type]) VALUES ('1','1',1)

i don't know why because in the select instruction i don't need so many [ ]...
 
Last edited:

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
Hmm strange indeed, like you say you shouldn't need the other []. It could be because your using Type as a field.
 
Top Bottom