Need help for adding record to database using vb.net

[aditya_amb]

I am trying to add a new record to data base using VB.net.
Here is my code:
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Dim objConnection As New OleDb.OleDbConnection
objConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\Documents and Settings\ambarda\My Documents\db1.mdb;"
Dim ds As New DataSet
Dim sql As String = "SELECT * FROM Sheet1"
Dim objDataAdapter As New OleDb.OleDbDataAdapter(sql, objConnection)
objDataAdapter.Fill(ds, "Runsheet Info")
Dim cb As New OleDb.OleDbCommandBuilder(objDataAdapter)
Dim dsNewRow As DataRow = ds.Tables("Runsheet Info").NewRow()
dsNewRow.Item(1) = "a"
dsNewRow.Item(2) = PORECNUM.Text
dsNewRow.Item(3) = Devlist.Text
dsNewRow.Item(4) = "04/01/08"
dsNewRow.Item(5) = SORECNUM.Text
dsNewRow.Item(6) = "17887"
dsNewRow.Item(7) = "7"
dsNewRow.Item(8) = "Michigan NA"
dsNewRow.Item(9) = "xcr23414"
dsNewRow.Item(10) = "6544234"
dsNewRow.Item(11) = "b"
dsNewRow.Item(12) = "c"
dsNewRow.Item(13) = "d"
ds.Tables("Runsheet Info").Rows.Add(dsNewRow)
objDataAdapter.Update(ds, "Runsheet Info")

But I get errror: Syntax error in INSERT INTO statement. at last command(objDataAdapter.Update(ds, "Runsheet Info")
Any help would be appreciated.
Thanks in Advance,
Aditya

[ptaylor965]

I would use a SQL INSERT INTO Statment instead of the objDataAdapter.Update

Have a look at http://www.taylorsnet.co.uk/SourceCodeDetail.aspx?SourceID=5
It shows how to use INSERTs and UPDATEs

The code is for SQL but works fine for MDB and ACCDB files (OleDb)

[baker4]

Hi
The website www.homeandlearn.co.uk has how to add a new record accurately and it works!
Takes you through step by step.

[aditya_amb]

I had constructed my code using home and learn but it did not work and sencountered INSERT IN to error problem as I explained earlier.
I do not know how to use SQL for database.I used following:
Dim sql2 As String

sql2 = " INSERT into Sheet1(DWNLDQPID,PO,Material,Date,SO,LI,QTY,Jurisdiction,Program,Type,UNCFD QTY,Label SIQPID,Sign off)VALUE('1',PORECNUM.TEXT,'3','4','5','6','7','8','9','10','11','12','13')"
But I do not know how to proceed further .
It doesn't add to database.
ALso if anybody could help me with dataadapter would be better. As I have to use that often in my Project.
Aditya

[aditya_amb]

When I debugged my code (posted earlier at first) for some reason, i think the values are not getting assigned.
for e.g.
dsNewRow.Item(2) = PORECNUM.Text
dsnewRow.Item(2) doesn't show the value stored in PORECNUM.Text.
May be that could be the problem..I dunno ..Any help...
I still get error: Syntax error in INSERT INTO statement in line
objDataAdapter.Update(ds, "Runsheet Info")

[cymapAndy]

Aditya,

I'd guess it's the field called Type giving you problems, as Type is possibly a reserved word in SQLServer. Putting it in square brackets should avoid this problem.

So, try:

sql2 = " INSERT into Sheet1(DWNLDQPID,PO,Material,Date,SO,LI,QTY,Jurisdiction,Program,[Type],UNCFD QTY,Label SIQPID,Sign off)VALUE('1',PORECNUM.TEXT,'3','4','5','6','7','8','9','10','11','12','13')"

cymapAndy.