Problems inserting data into Access database

microsoft.public.dotnet.framework.adonet

30 Mar 2005 10:04 PM

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

31 Mar 2005 1:37 PM

Cowboy (Gregory A. Beamer) - MVP

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!
***************************

Show quote

"Roshawn Dawson" wrote:

> 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
>

31 Mar 2005 4:40 PM

J L

In your UpdateTable should the command type be StoredProcedure? Seems
to me it should be Text?

John

On Wed, 30 Mar 2005 16:04:16 -0600, Roshawn Dawson
<udr***@bellsouth.net> wrote:

Show quote

>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