Newbie insert question

[whateveragain]

I'm working with Visual Studio 2008, asp.net 3.5, MS Access 2007 on a Vista machine. I'm trying to insert data into an Access.mdb and am getting an error "Syntax error in INERT INTO statement." for the following code. Can anyone please tell me what is wrong? Thanks

Dim oleConn As New OleDb.OleDbConnection()
oleConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Joanne\Documents\Visual Studio 2008\WebSites\WebTesting\App_Data\whichdata.mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False"
Try
oleConn.Open()
Catch oleerr As Exception
MsgBox(oleerr.Message, "connection error!")
End Try

Dim olecomm As New OleDb.OleDbCommand()
olecomm.Connection = oleConn

olecomm.CommandText = "select * from whichdata"
Dim ds As New DataSet()
Dim da As New OleDb.OleDbDataAdapter()
da.SelectCommand = olecomm
Dim scb As New OleDb.OleDbCommandBuilder(da)
Try
da.Fill(ds, "whichdata")

Dim ndr = ds.Tables("whichdata").NewRow
ndr("sessionID") = Session.SessionID
ndr("mainmen") = "1"
ndr("whichdata1") = "1"
ndr("whichdata2") = "0"
ndr("whichdata3") = "0"

ds.Tables("whichdata").Rows.Add(ndr)
da.Update(ds, "whichdata")
Catch updex As Exception
MsgBox("Insert error")
MsgBox(updex.Message)
End Try

 

[jmeckley]

is the data you are entering actually strings? If they are numbers then pass numbers, (1 instead of "1").

other issues.
1. you cannot use MessageBox within a web application. If this works in development it's because the client and server are the same machine. deployed to production the server and client are different machines and this will blow up.

2. put the connection string in the web.config file and reference the connection string. do not hardcode the connection string in the appliation.

3. no point in having 2 try/catch blocks. if you cannot open the connection, you cannot execute the command builder.

4. you need to dispose of your connection when you're done with it.

5. no point in catching the exception, just let it bubble up. instead using the finally block to dispose of the connection. this ensures it is disposed of even if a connection is thrown

     Dim oleConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("name of key").ConnectionString)
        Try
            oleConn.Open()
            Dim olecomm As New OleDb.OleDbCommand()
            olecomm.Connection = oleConn

            olecomm.CommandText = "select * from whichdata"
            Dim ds As New DataSet()
            Dim da As New OleDb.OleDbDataAdapter()
            da.SelectCommand = olecomm
            Dim scb As New OleDb.OleDbCommandBuilder(da)
            da.Fill(ds, "whichdata")

            Dim ndr = ds.Tables("whichdata").NewRow
            ndr("sessionID") = Session.SessionID
            ndr("mainmen") = "1"
            ndr("whichdata1") = "1"
            ndr("whichdata2") = "0"
            ndr("whichdata3") = "0"

            ds.Tables("whichdata").Rows.Add(ndr)
            da.Update(ds, "whichdata")
        Finally
            oleConn.Dispose()
        End Try

you could also use the using keyword instead

     Using(Dim oleConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("name of key").ConnectionString))
        Try
            oleConn.Open()
            Dim olecomm As New OleDb.OleDbCommand()
            olecomm.Connection = oleConn

            olecomm.CommandText = "select * from whichdata"
            Dim ds As New DataSet()
            Dim da As New OleDb.OleDbDataAdapter()
            da.SelectCommand = olecomm
            Dim scb As New OleDb.OleDbCommandBuilder(da)
            da.Fill(ds, "whichdata")

            Dim ndr = ds.Tables("whichdata").NewRow
            ndr("sessionID") = Session.SessionID
            ndr("mainmen") = "1"
            ndr("whichdata1") = "1"
            ndr("whichdata2") = "0"
            ndr("whichdata3") = "0"

            ds.Tables("whichdata").Rows.Add(ndr)
            da.Update(ds, "whichdata")
End Using

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[whateveragain]

Thanks for the help, but it's still not working. The using statement doesn't work and the data type of the data doesn't seem to make a difference. It's connecting as I'm getting the 'Welcome' msg. See code below:

Dim oleConn As New OleDb.OleDbConnection()
Dim oleComm As New OleDb.OleDbCommand()
oleComm.Connection = oleConn
oleConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\users\joanne\documents\Visual Studio 2008\Websites\Webtesting\App_data\whichdata.mdb;User Id=admin;Password="

Try
oleConn.Open()
Catch sqlError As Exception
MsgBox(sqlError.Message, "Connection Error!")
End Try
If oleConn.State = 1 Then
MsgBox("Welcome to ADO.NET using sqlDB!") <- gives me this so it's connecting
End If

oleComm.CommandText = "select * from whichdata"
Dim ds As New DataSet()
Dim da As New OleDb.OleDbDataAdapter()
da.SelectCommand = olecomm
Dim scb As New OleDb.OleDbCommandBuilder(da)
Try
da.Fill(ds, "whichdata")

Dim ndr = ds.Tables("whichdata").NewRow
ndr("sessionID") = 1
ndr("mainmen") = 2
ndr("whichdata1") = 3
ndr("whichdata2") = 4
ndr("whichdata3") = 5

ds.Tables("whichdata").Rows.Add(ndr)
da.Update(ds, "whichdata")
Catch updex As Exception
MsgBox("Insert error")
MsgBox(updex.Message)
End Try

 

[jmeckley]

you knew it was connecting before so this doesn't tell you anything. the using statement wouldn't fix the error, it just manages the disposal of the connection for you. and you're still using messagebox. this will become a habit, and then a problem, if you depend on it for debugging.

for now remove all the try/catch and let the error bubble up. post the full stack trace when the error is thrown. this may give us more insight into the issue.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190

 

[kss444]

You are not inserting anything into your DB. You are only adding a row to your data table.
Not sure about the correct syntax but after you add a row to your DataTable you will have to to a .update or .commit.
If it were me, I would call an insert stored proc and pass my paramaters into it.

Ordinary Programmer

 

[whateveragain]

My code:
Dim oleConn As New OleDb.OleDbConnection()
Dim oleComm As New OleDb.OleDbCommand()
oleComm.Connection = oleConn
oleConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\users\joanne\documents\Visual Studio 2008\Websites\Webtesting\App_data\whichdata.mdb;User Id=admin;Password="
oleConn.Open()
If oleConn.State = 1 Then
' MsgBox("Welcome to ADO.NET using sqlDB!")
End If
oleComm.CommandText = "select * from whichdata"
Dim ds As New DataSet()
Dim da As New OleDb.OleDbDataAdapter()
da.SelectCommand = oleComm
Dim scb As New OleDb.OleDbCommandBuilder(da)
da.Fill(ds, "whichdata")
Dim ndr = ds.Tables("whichdata").NewRow
ndr("sessionID") = Session.SessionID()
ndr("mainmen") = 2
ndr("whichdata1") = 1
ndr("whichdata2") = 0
ndr("whichdata3") = 0
ds.Tables("whichdata").Rows.Add(ndr)
da.Update(ds, "whichdata")

ERROR MSG:
The compiler points to da.update(ds, "whichdata") and gives me an error message 'Syntax error in INSERT INTO statement.'

 

[jbenson001]

Where is you dataadapert's update and insert command? I don't see where you construct them or use a stored procedure