Problems inserting data into Access database

  • Thread starter Thread starter Roshawn Dawson
  • Start date Start date
R

Roshawn Dawson

Hi,

I'm trying to insert new rows into a table in an Access database.
Here's my code:

Dim cn As OleDbConnection
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
cn = New OleDbConnection(ConfigurationSettings.AppSettings("cnstr"))
Dim TStart, TEnd As Date
Dim duration As TimeSpan
TStart = Now()
Dim table as DataTable = GetData()
TEnd = Now()
duration = TEnd.Subtract(TStart)
Label1.Text = tbl.Rows.Count
Label2.Text = "Operation took " & Round(duration.TotalSeconds, 0) & "
seconds to execute."
End Sub

Private Function GetData() As DataTable
Dim AWS As New EEC.BookStore.Books()
Dim tbl As DataTable = MakeTable()
Dim books As EEC.BookResponse
Dim i As Integer
For i = 1 To 10 'would be to the max number of pages available
'Call the web service and format data as desired; this works
Call Me.AddToTable(books)'custom function that adds rows to the table
Next
cn.Open()
Call Me.SubmitInsertViaAdapter()
cn.Close()
AWS = Nothing
Return tbl
End Function

Private Function MakeTable() As DataTable
Dim tbl As New DataTable("Books")
Dim col As DataColumn
With tbl
col = .Columns.Add("ISBN", GetType(String))
col = .Columns.Add("Title", GetType(String))
col = .Columns.Add("Category", GetType(String))
col = .Columns.Add("Authors", GetType(String))
col = .Columns.Add("SImg", GetType(String))
col = .Columns.Add("MImg", GetType(String))
col = .Columns.Add("LImg", GetType(String))
col = .Columns.Add("Pub", GetType(String))
col = .Columns.Add("PubDate", GetType(String))
col = .Columns.Add("Binding", GetType(String))
col = .Columns.Add("NumItems", GetType(String))
col = .Columns.Add("NumPages", GetType(String))
col = .Columns.Add("EAN", GetType(String))
col = .Columns.Add("Keywords", GetType(String))
End With
Return tbl
End Function

Private Function UpdateTable() As OleDbCommand
Dim str As String
str = "INSERT INTO Books (ISBN, Title, Category, Authors, SImg, MImg,
LImg, Pub, PubDate, Binding, NumItems, NumPages, EAN, Keywords) VALUES
([?], [?], [?], [?], [?], [?], [?], [?], [?], [?], [?], [?], [?],[?])"
Dim cmd As New OleDbCommand(str, cn)
cmd.CommandType = CommandType.StoredProcedure
Dim col As DataColumn
For Each col In tbl.Columns
Dim param As New OleDbParameter(col.ColumnName, OleDbType.VarChar,
255, col.ColumnName)
cmd.Parameters.Add(param)
Next
Return cmd
End Function

Private Sub SubmitInsertViaAdapter()
Dim daBooks As New OleDbDataAdapter()
daBooks.ContinueUpdateOnError = True
'daBooks.SelectCommand = Nothing
'daBooks.UpdateCommand = Nothing
'daBooks.DeleteCommand = Nothing
daBooks.InsertCommand = UpdateTable()
Label3.Text = CStr(daBooks.Update(tbl))
End Sub

When all is done, there are rows in the datatable but none of them were
submitted to the database. What am I doing wrong? Please help, I have
only a few hairs left on my head!!

Thanks,
Roshawn
 
Two options:

1. Create a DataAdapter and call for an empty dataset (where 1 = 2). Add
rows to the DataSet (or rather the table that corresponds to the table you
wish to update). Add rows and call DataAdapter.Update()

2. If you want control, Create an update row and explicitly create each
parameter by name. If nothing else, this allows you to easily debug the
information. You can then have your loop call the UpdateRow() method once per
table row and pass all of the values as explicitly typed information (which
will bomb if the wrong type is sent for a column).

3. Move to Access queries, which act like stored procedures, instead of
using ???? for your parameters.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

Roshawn Dawson said:
Hi,

I'm trying to insert new rows into a table in an Access database.
Here's my code:

Dim cn As OleDbConnection
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
cn = New OleDbConnection(ConfigurationSettings.AppSettings("cnstr"))
Dim TStart, TEnd As Date
Dim duration As TimeSpan
TStart = Now()
Dim table as DataTable = GetData()
TEnd = Now()
duration = TEnd.Subtract(TStart)
Label1.Text = tbl.Rows.Count
Label2.Text = "Operation took " & Round(duration.TotalSeconds, 0) & "
seconds to execute."
End Sub

Private Function GetData() As DataTable
Dim AWS As New EEC.BookStore.Books()
Dim tbl As DataTable = MakeTable()
Dim books As EEC.BookResponse
Dim i As Integer
For i = 1 To 10 'would be to the max number of pages available
'Call the web service and format data as desired; this works
Call Me.AddToTable(books)'custom function that adds rows to the table
Next
cn.Open()
Call Me.SubmitInsertViaAdapter()
cn.Close()
AWS = Nothing
Return tbl
End Function

Private Function MakeTable() As DataTable
Dim tbl As New DataTable("Books")
Dim col As DataColumn
With tbl
col = .Columns.Add("ISBN", GetType(String))
col = .Columns.Add("Title", GetType(String))
col = .Columns.Add("Category", GetType(String))
col = .Columns.Add("Authors", GetType(String))
col = .Columns.Add("SImg", GetType(String))
col = .Columns.Add("MImg", GetType(String))
col = .Columns.Add("LImg", GetType(String))
col = .Columns.Add("Pub", GetType(String))
col = .Columns.Add("PubDate", GetType(String))
col = .Columns.Add("Binding", GetType(String))
col = .Columns.Add("NumItems", GetType(String))
col = .Columns.Add("NumPages", GetType(String))
col = .Columns.Add("EAN", GetType(String))
col = .Columns.Add("Keywords", GetType(String))
End With
Return tbl
End Function

Private Function UpdateTable() As OleDbCommand
Dim str As String
str = "INSERT INTO Books (ISBN, Title, Category, Authors, SImg, MImg,
LImg, Pub, PubDate, Binding, NumItems, NumPages, EAN, Keywords) VALUES
([?], [?], [?], [?], [?], [?], [?], [?], [?], [?], [?], [?], [?],[?])"
Dim cmd As New OleDbCommand(str, cn)
cmd.CommandType = CommandType.StoredProcedure
Dim col As DataColumn
For Each col In tbl.Columns
Dim param As New OleDbParameter(col.ColumnName, OleDbType.VarChar,
255, col.ColumnName)
cmd.Parameters.Add(param)
Next
Return cmd
End Function

Private Sub SubmitInsertViaAdapter()
Dim daBooks As New OleDbDataAdapter()
daBooks.ContinueUpdateOnError = True
'daBooks.SelectCommand = Nothing
'daBooks.UpdateCommand = Nothing
'daBooks.DeleteCommand = Nothing
daBooks.InsertCommand = UpdateTable()
Label3.Text = CStr(daBooks.Update(tbl))
End Sub

When all is done, there are rows in the datatable but none of them were
submitted to the database. What am I doing wrong? Please help, I have
only a few hairs left on my head!!

Thanks,
Roshawn
Click to expand...
 
In your UpdateTable should the command type be StoredProcedure? Seems
to me it should be Text?

John

Hi,

I'm trying to insert new rows into a table in an Access database.
Here's my code:

Dim cn As OleDbConnection
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
cn = New OleDbConnection(ConfigurationSettings.AppSettings("cnstr"))
Dim TStart, TEnd As Date
Dim duration As TimeSpan
TStart = Now()
Dim table as DataTable = GetData()
TEnd = Now()
duration = TEnd.Subtract(TStart)
Label1.Text = tbl.Rows.Count
Label2.Text = "Operation took " & Round(duration.TotalSeconds, 0) & "
seconds to execute."
End Sub

Private Function GetData() As DataTable
Dim AWS As New EEC.BookStore.Books()
Dim tbl As DataTable = MakeTable()
Dim books As EEC.BookResponse
Dim i As Integer
For i = 1 To 10 'would be to the max number of pages available
'Call the web service and format data as desired; this works
Call Me.AddToTable(books)'custom function that adds rows to the table
Next
cn.Open()
Call Me.SubmitInsertViaAdapter()
cn.Close()
AWS = Nothing
Return tbl
End Function

Private Function MakeTable() As DataTable
Dim tbl As New DataTable("Books")
Dim col As DataColumn
With tbl
col = .Columns.Add("ISBN", GetType(String))
col = .Columns.Add("Title", GetType(String))
col = .Columns.Add("Category", GetType(String))
col = .Columns.Add("Authors", GetType(String))
col = .Columns.Add("SImg", GetType(String))
col = .Columns.Add("MImg", GetType(String))
col = .Columns.Add("LImg", GetType(String))
col = .Columns.Add("Pub", GetType(String))
col = .Columns.Add("PubDate", GetType(String))
col = .Columns.Add("Binding", GetType(String))
col = .Columns.Add("NumItems", GetType(String))
col = .Columns.Add("NumPages", GetType(String))
col = .Columns.Add("EAN", GetType(String))
col = .Columns.Add("Keywords", GetType(String))
End With
Return tbl
End Function

Private Function UpdateTable() As OleDbCommand
Dim str As String
str = "INSERT INTO Books (ISBN, Title, Category, Authors, SImg, MImg,
LImg, Pub, PubDate, Binding, NumItems, NumPages, EAN, Keywords) VALUES
([?], [?], [?], [?], [?], [?], [?], [?], [?], [?], [?], [?], [?],[?])"
Dim cmd As New OleDbCommand(str, cn)
cmd.CommandType = CommandType.StoredProcedure
Dim col As DataColumn
For Each col In tbl.Columns
Dim param As New OleDbParameter(col.ColumnName, OleDbType.VarChar,
255, col.ColumnName)
cmd.Parameters.Add(param)
Next
Return cmd
End Function

Private Sub SubmitInsertViaAdapter()
Dim daBooks As New OleDbDataAdapter()
daBooks.ContinueUpdateOnError = True
'daBooks.SelectCommand = Nothing
'daBooks.UpdateCommand = Nothing
'daBooks.DeleteCommand = Nothing
daBooks.InsertCommand = UpdateTable()
Label3.Text = CStr(daBooks.Update(tbl))
End Sub

When all is done, there are rows in the datatable but none of them were
submitted to the database. What am I doing wrong? Please help, I have
only a few hairs left on my head!!

Thanks,
Roshawn
Click to expand...
 
Back
Top