DataAdapter.Fill Error During Data Save; error #-2147217904

T

TejasT

Member
Joined
May 26, 2006
Messages
5
Programming Experience
10+
I am fairly new to vb.net 2005. I am not sure why I am getting an OleDB Unhandled exception error # -2147217904 on a Adapter.fill(). It was working with only two parameters then when I added the rest of the needed parameters I started getting this error. This is updating a Access 03 database.

Could someone HELP me please!

VB.NET: 
[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] SaveToolStripButton_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] SaveToolStripButton.Click
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] connection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection([/SIZE][SIZE=2][COLOR=#800000]"Provider=Microsoft.Jet.OLEDB.4.0;"[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#800000]"Data Source=MABAK_2.MDB;"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]
[/SIZE][SIZE=2][COLOR=#008000]' Assumes that connection is a valid OleDbConnection object.
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]' Use the DataAdapter to fill and update the DataSet.
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] adapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter( _
[/SIZE][SIZE=2][COLOR=#800000]"SELECT VendorNumber, VendorActive,VendorName,VendorAddress1,VendorAddress2,VendorCity,VendorSt,"[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#800000]"VendorZip5,VendorZip4,VendorPhone,VendorPhoneExt,VendorCell,VendorFax,VendorContact,VendorEmail "[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#800000]"FROM Vendor ORDER BY VendorNumber"[/COLOR][/SIZE][SIZE=2], connection)[/SIZE]
[SIZE=2]
adapter.InsertCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand( _
[/SIZE][SIZE=2][COLOR=#800000]"INSERT INTO Vendor (VendorActive,VendorName,VendorAddress1,VendorAddress2,VendorCity,VendorSt,"[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#800000]"VendorZip5,VendorZip4,VendorPhone,VendorPhoneExt,VendorCell,VendorFax,VendorContact,VendorEmail)"[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#800000]"Values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"[/COLOR][/SIZE][SIZE=2], connection)[/SIZE]
[SIZE=2]
adapter.InsertCommand.CommandType = CommandType.Text
adapter.InsertCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@VendorActive"[/COLOR][/SIZE][SIZE=2], OleDbType.Boolean, 1, [/SIZE][SIZE=2][COLOR=#800000]"VendorActive"[/COLOR][/SIZE][SIZE=2])
adapter.InsertCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@VendorName"[/COLOR][/SIZE][SIZE=2], OleDbType.Char, 50, [/SIZE][SIZE=2][COLOR=#800000]"VendorName"[/COLOR][/SIZE][SIZE=2])
adapter.InsertCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@VendorAddress1"[/COLOR][/SIZE][SIZE=2], OleDbType.Char, 50, [/SIZE][SIZE=2][COLOR=#800000]"VendorAddress1"[/COLOR][/SIZE][SIZE=2])
adapter.InsertCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@VendorAddress2"[/COLOR][/SIZE][SIZE=2], OleDbType.Char, 50, [/SIZE][SIZE=2][COLOR=#800000]"VendorAddress2"[/COLOR][/SIZE][SIZE=2])
adapter.InsertCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@VendorCity"[/COLOR][/SIZE][SIZE=2], OleDbType.Char, 50, [/SIZE][SIZE=2][COLOR=#800000]"VendorCity"[/COLOR][/SIZE][SIZE=2])
adapter.InsertCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@VendorSt"[/COLOR][/SIZE][SIZE=2], OleDbType.Char, 2, [/SIZE][SIZE=2][COLOR=#800000]"VendorSt"[/COLOR][/SIZE][SIZE=2])
adapter.InsertCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@VendorZip5"[/COLOR][/SIZE][SIZE=2], OleDbType.Char, 5, [/SIZE][SIZE=2][COLOR=#800000]"VendorZip5"[/COLOR][/SIZE][SIZE=2])
adapter.InsertCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@VendorZip4"[/COLOR][/SIZE][SIZE=2], OleDbType.Char, 4, [/SIZE][SIZE=2][COLOR=#800000]"VendorZip4"[/COLOR][/SIZE][SIZE=2])
adapter.InsertCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@VendorPhone"[/COLOR][/SIZE][SIZE=2], OleDbType.Char, 14, [/SIZE][SIZE=2][COLOR=#800000]"VendorPhone"[/COLOR][/SIZE][SIZE=2])
adapter.InsertCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@VendorPhoneExt"[/COLOR][/SIZE][SIZE=2], OleDbType.Char, 5, [/SIZE][SIZE=2][COLOR=#800000]"VendorPhoneExt"[/COLOR][/SIZE][SIZE=2])
adapter.InsertCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@VendorCell"[/COLOR][/SIZE][SIZE=2], OleDbType.Char, 14, [/SIZE][SIZE=2][COLOR=#800000]"VendorCell"[/COLOR][/SIZE][SIZE=2])
adapter.InsertCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@VendorFax"[/COLOR][/SIZE][SIZE=2], OleDbType.Char, 14, [/SIZE][SIZE=2][COLOR=#800000]"VendorFax"[/COLOR][/SIZE][SIZE=2])
adapter.InsertCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@VendorContact"[/COLOR][/SIZE][SIZE=2], OleDbType.Char, 50, [/SIZE][SIZE=2][COLOR=#800000]"VendorContact"[/COLOR][/SIZE][SIZE=2])
adapter.InsertCommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"@VendorEmail"[/COLOR][/SIZE][SIZE=2], OleDbType.Char, 50, [/SIZE][SIZE=2][COLOR=#800000]"VendorEmail"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]
connection.Open()
 
[/SIZE][SIZE=2][COLOR=#008000]' Fill the DataSet. 
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] newVendor [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataSet = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet[/SIZE]
 
 
[SIZE=2]'THIS IS WHERE THE ERROR OCCURS ************************************
adapter.Fill(newVendor, [/SIZE][SIZE=2][COLOR=#800000]"Vendor"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]
VendorGridView.Cursor = Cursors.WaitCursor
 
[/SIZE][SIZE=2][COLOR=#008000]' Add a new row.
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] newRow [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRow = newVendor.Tables([/SIZE][SIZE=2][COLOR=#800000]"Vendor"[/COLOR][/SIZE][SIZE=2]).NewRow()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] gridrow [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataGridViewRow
[/SIZE][SIZE=2][COLOR=#008000]'Dim table_row As DataRow[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][SIZE=2] gridrow [/SIZE][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][SIZE=2] VendorGridView.Rows
[/SIZE][SIZE=2][COLOR=#008000]'table_row = gridtable.NewRow
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'table_row("datagridcollumn1") = gridrow.Cells("collumn1").Value
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'table_row("datagridcollumn2") = gridrow.Cells("collumn2").Value
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'gridtable.Rows.Add(table_row)
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'gridtable.WriteXml("test.xml")[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][SIZE=2]newRow([/SIZE][SIZE=2][COLOR=#800000]"VendorActive"[/COLOR][/SIZE][SIZE=2]) = gridrow.Cells([/SIZE][SIZE=2][COLOR=#800000]"VendorActive"[/COLOR][/SIZE][SIZE=2]).Value
newRow([/SIZE][SIZE=2][COLOR=#800000]"VendorName"[/COLOR][/SIZE][SIZE=2]) = gridrow.Cells([/SIZE][SIZE=2][COLOR=#800000]"VendorName"[/COLOR][/SIZE][SIZE=2]).Value
newRow([/SIZE][SIZE=2][COLOR=#800000]"VendorAddress1"[/COLOR][/SIZE][SIZE=2]) = gridrow.Cells([/SIZE][SIZE=2][COLOR=#800000]"VendorAddress1"[/COLOR][/SIZE][SIZE=2]).Value
newRow([/SIZE][SIZE=2][COLOR=#800000]"VendorAddress2"[/COLOR][/SIZE][SIZE=2]) = gridrow.Cells([/SIZE][SIZE=2][COLOR=#800000]"VendorAddress2"[/COLOR][/SIZE][SIZE=2]).Value
newRow([/SIZE][SIZE=2][COLOR=#800000]"VendorCity"[/COLOR][/SIZE][SIZE=2]) = gridrow.Cells([/SIZE][SIZE=2][COLOR=#800000]"VendorCity"[/COLOR][/SIZE][SIZE=2]).Value
newRow([/SIZE][SIZE=2][COLOR=#800000]"VendorSt"[/COLOR][/SIZE][SIZE=2]) = gridrow.Cells([/SIZE][SIZE=2][COLOR=#800000]"VendorSt"[/COLOR][/SIZE][SIZE=2]).Value
newRow([/SIZE][SIZE=2][COLOR=#800000]"VendorZip5"[/COLOR][/SIZE][SIZE=2]) = gridrow.Cells([/SIZE][SIZE=2][COLOR=#800000]"VendorZip5"[/COLOR][/SIZE][SIZE=2]).Value
newRow([/SIZE][SIZE=2][COLOR=#800000]"VendorZip4"[/COLOR][/SIZE][SIZE=2]) = gridrow.Cells([/SIZE][SIZE=2][COLOR=#800000]"VendorZip4"[/COLOR][/SIZE][SIZE=2]).Value
newRow([/SIZE][SIZE=2][COLOR=#800000]"VendorPhone"[/COLOR][/SIZE][SIZE=2]) = gridrow.Cells([/SIZE][SIZE=2][COLOR=#800000]"VendorPhone"[/COLOR][/SIZE][SIZE=2]).Value
newRow([/SIZE][SIZE=2][COLOR=#800000]"VendorPhoneExt"[/COLOR][/SIZE][SIZE=2]) = gridrow.Cells([/SIZE][SIZE=2][COLOR=#800000]"VendorPhoneExt"[/COLOR][/SIZE][SIZE=2]).Value
newRow([/SIZE][SIZE=2][COLOR=#800000]"VendorContact"[/COLOR][/SIZE][SIZE=2]) = gridrow.Cells([/SIZE][SIZE=2][COLOR=#800000]"VendorContact"[/COLOR][/SIZE][SIZE=2]).Value
newRow([/SIZE][SIZE=2][COLOR=#800000]"VendorCell"[/COLOR][/SIZE][SIZE=2]) = gridrow.Cells([/SIZE][SIZE=2][COLOR=#800000]"VendorCell"[/COLOR][/SIZE][SIZE=2]).Value
newRow([/SIZE][SIZE=2][COLOR=#800000]"VendorFax"[/COLOR][/SIZE][SIZE=2]) = gridrow.Cells([/SIZE][SIZE=2][COLOR=#800000]"VendorFax"[/COLOR][/SIZE][SIZE=2]).Value
newRow([/SIZE][SIZE=2][COLOR=#800000]"VendorEmail"[/COLOR][/SIZE][SIZE=2]) = gridrow.Cells([/SIZE][SIZE=2][COLOR=#800000]"VendorEmail"[/COLOR][/SIZE][SIZE=2]).Value
newVendor.Tables([/SIZE][SIZE=2][COLOR=#800000]"Vendor"[/COLOR][/SIZE][SIZE=2]).Rows.Add(newRow)[/SIZE]
[SIZE=2]
[/SIZE][SIZE=2][COLOR=#008000]' Include an event to fill in the Autonumber value.
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]AddHandler[/COLOR][/SIZE][SIZE=2] adapter.RowUpdated, _
[/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbRowUpdatedEventHandler([/SIZE][SIZE=2][COLOR=#0000ff]AddressOf[/COLOR][/SIZE][SIZE=2] OnRowUpdated)[/SIZE]
[SIZE=2]
[/SIZE][SIZE=2][COLOR=#008000]' Update the DataSet.
[/COLOR][/SIZE][SIZE=2]adapter.Update(newVendor, [/SIZE][SIZE=2][COLOR=#800000]"Vendor"[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#008000]'adapter.AcceptChangesDuringUpdate(newVendor, "Vendor")
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'adapter.ContinueUpdateOnError
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'newRow.AcceptChanges()[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]
[/COLOR][/SIZE][SIZE=2]newVendor.AcceptChanges()
[/SIZE][SIZE=2][COLOR=#0000ff]Next[/COLOR][/SIZE][SIZE=2] gridrow[/SIZE]
[SIZE=2]
connection.Close()
 
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].VendorTableAdapter1.Fill([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].MABAK_2DataSet.Vendor)
VendorGridView.Cursor = Cursors.Default
 
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[COLOR=black]
[/COLOR]
[/COLOR][/SIZE]
 
Last edited by a moderator:
Sort by date Sort by votes
Do this:

VB.NET: 
Try
    adapter.Fill(newVendor, "Vendor")
Catch exOLEDB As OLEDBException
    MessageBox.Show exOLEDB.ToString
End Try

THat will give you a more specific error. Once you have that, then it should be easier to fix it.

-tg
 
Upvote 0 Downvote
TG I did try the snippet you gave me; it did produce the error dialog as you stated.

My question is though; The only required field is the VendorID field which is a autoincrement field and is filled in the addnew(); So is it that field?

TejasT
 

Attachments

  • Doc7.doc
    46.5 KB · Views: 16
Upvote 0 Downvote
*scratches head*... hmmm.....

Let's try this.... Let's see if we can just get the dataset to fill. Don't worry about the insert part just yet.

See if this works:
VB.NET: 
PrivateSub SaveToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveToolStripButton.Click
Dim connection AsNew OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=MABAK_2.MDB;")

' Assumes that connection is a valid OleDbConnection object.
' Use the DataAdapter to fill and update the DataSet.
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter( _
"SELECT VendorNumber, VendorActive,VendorName,VendorAddress1,VendorAddre ss2,VendorCity,VendorSt," & _
"VendorZip5,VendorZip4,VendorPhone,VendorPhoneExt, VendorCell,VendorFax,VendorContact,VendorEmail " & _
"FROM Vendor ORDER BY VendorNumber", connection)

connection.Open()

' Fill the DataSet.
Dim newVendor As DataSet = New DataSet


Try
    adapter.Fill(newVendor, "Vendor")
Catch exOLEDB As OLEDBException
    MessageBox.Show exOLEDB.ToString
End Try

MessageBox.Show newVendor.Tables("Vendor").Rows.Count.ToString

connection.Close()

VendorGridView.Cursor = Cursors.Default

EndSub

Try that - I basicaly cut out all the stuff for the instering. Since you seemed to indicate that it wasn't even loading, let's try to get that first.

-tg
 
Upvote 0 Downvote
TG, I can only get the fill method to work with two columns in it; VendorNumber and VendorName. If there are any other columns in the fill.command.text it fails.

best regards,

TejasT
 
Upvote 0 Downvote
Interesting.... do those fields exist in the table? Is it possible that they are in a different table?

-tg
 
Upvote 0 Downvote
TG,

All of those columns exist in the Vendor table. When just those two columns are in the select command it will let the fill() happen; but the insert fails with "Field not member of adapter.insert()". I am wondering if somehow the adapter got screwed up and only put those in the class. I need to look at that.

TejasT
 
Upvote 0 Downvote

Share this page

Latest posts

Back
Top