s6192221
asked on
Question on inserting values into Access database using SQL
Hi,
I wish to find out how can I insert a record into Microsoft Access using SQL query string? I've got a record of 3 columns in myTable namely: Name, Address and TelNumber. Name and Address in String and TelNumber in Integer. I'm using Microsoft DAO Object to access the database. I'm able to use the SELECT query string to extract the data but I don't know how to insert data into the database.
This is what I've tried:
rs = db.OpenRecordset("INSERT INTO myTable VALUES(Name,Address,TelNum
and of course it's not working :) I mean I believe there's something wrong with the parameters passed into the VALUES. Any help offered is very much appreciated!
I'm working on Microsoft Windows XP Pro, using Microsoft Access 2002 and Visual Basic 6.
Best Rgds,
Justin
I wish to find out how can I insert a record into Microsoft Access using SQL query string? I've got a record of 3 columns in myTable namely: Name, Address and TelNumber. Name and Address in String and TelNumber in Integer. I'm using Microsoft DAO Object to access the database. I'm able to use the SELECT query string to extract the data but I don't know how to insert data into the database.
This is what I've tried:
rs = db.OpenRecordset("INSERT INTO myTable VALUES(Name,Address,TelNum
and of course it's not working :) I mean I believe there's something wrong with the parameters passed into the VALUES. Any help offered is very much appreciated!
I'm working on Microsoft Windows XP Pro, using Microsoft Access 2002 and Visual Basic 6.
Best Rgds,
Justin
Set CONN = CreateObject("ADODB.Connec
CONN.ConnectionString = Your connection string
SQL = "INSERT INTO myTable VALUES(Name,Address,TelNum
CONN.Execute(SQL)
A few things, first, the database object has a method called Execute that is designed for executing "Action" queries that do not return recordsets. Next, you have the column names in your Values clause, you should really have a separate column list clause, then you need to put valid values in the Value clause. Let's say that you are getting all of this data off the screen, here is how your code might look:
Dim sSQL as String
sSQL = "INSERT INTO myTable (NAME, ADDRESS, TELNUMBER) VALUES ("
sSQL = sSQL & "'" & txtName.Text & "', "
sSQL = sSQL & "'" & txtAddress.Text & "', "
sSQL = sSQL & txtPhone.Text & ")"
db.Execute sSQL
Notice that the txtPhone.Text is not wrapped with single quotes. Single quotes are only used for text fields.
Dim sSQL as String
sSQL = "INSERT INTO myTable (NAME, ADDRESS, TELNUMBER) VALUES ("
sSQL = sSQL & "'" & txtName.Text & "', "
sSQL = sSQL & "'" & txtAddress.Text & "', "
sSQL = sSQL & txtPhone.Text & ")"
db.Execute sSQL
Notice that the txtPhone.Text is not wrapped with single quotes. Single quotes are only used for text fields.
A few things, first, the database object has a method called Execute that is designed for executing "Action" queries that do not return recordsets. Next, you have the column names in your Values clause, you should really have a separate column list clause, then you need to put valid values in the Value clause. Let's say that you are getting all of this data off the screen, here is how your code might look:
Dim sSQL as String
sSQL = "INSERT INTO myTable (NAME, ADDRESS, TELNUMBER) VALUES ("
sSQL = sSQL & "'" & txtName.Text & "', "
sSQL = sSQL & "'" & txtAddress.Text & "', "
sSQL = sSQL & txtPhone.Text & ")"
db.Execute sSQL
Notice that the txtPhone.Text is not wrapped with single quotes. Single quotes are only used for text fields.
Dim sSQL as String
sSQL = "INSERT INTO myTable (NAME, ADDRESS, TELNUMBER) VALUES ("
sSQL = sSQL & "'" & txtName.Text & "', "
sSQL = sSQL & "'" & txtAddress.Text & "', "
sSQL = sSQL & txtPhone.Text & ")"
db.Execute sSQL
Notice that the txtPhone.Text is not wrapped with single quotes. Single quotes are only used for text fields.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hyper8 , I'm using Microsoft DAO Object.
Thanks all I'll try it out :)
Thanks all I'll try it out :)
ASKER
Well done...... thanks.....!! I'm new to database :)
Glad you helped out :)
Glad you helped out :)
sorry for the multiple posts, EE did that, not me! Thanks!
rs = db.OpenRecordset("INSERT INTO myTable VALUES('" & Name & "','" & Address & "','" & TelNumber & "')")
You need to actually send the values rather than the names.