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" 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
>
Similar Threads
