I cannot get my OleDbDataAdapte r to update my database
table from my local dataset table. The Knowledge Base
doesn't seem to help - see item 10 below.
I have a Microsoft Access 2000 database which indexes
computer magazine articles for personal reference.
I am developing a Visual Basic.NET program whose sole
purpose is to enter new records into the database. No
updates to existing entries, no deletions, and no display
of existing records will be performed by this program;
only the entry of new records.
1. I use the following code to attempt the update (CRLF is
pre-coded - see item 9 below):
Try
OleDbDataAdapte rArticles.Updat e
(DsInfoBase, "Articles")
' An unhandled exception of type
' System.Data.Ole Db.OleDbExcepti on occurs in
' system.data.dll
Catch ex As Exception
MsgBox("Type = " & ex.GetType.ToSt ring() &
CRLF & _
"Message = " & ex.Message, MsgBoxStyle.OKO nly, _
"Try-Catch Mechanism")
End Try
It reports: Type = System.Data.Ole Db.OleDbExcepti on
Message = Syntax error in INSERT INTO
statement.
2. However, the INSERT INTO statement was automatically
generated by the IDE when the OleDbDataAdapte rArticles was
created. For your reference, that INSERT INTO statement is:
INSERT INTO Articles
(Author1, Author2, Author3, bkTitleID, catCode1, catCode2,
catCode3, catCode4, catCode5, catCode6, catCode7,
catCode8, [Date], Location, magTitleID, miscTitleID,
Month, Notes, [Number], PageEnd, PageStart, Title, Type,
Volume, Year)
VALUES
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
, ?, ?, ?, ?, ?)
and the data adapter's corresponding SELECT statement is:
SELECT artTitleID, Author1, Author2, Author3, bkTitleID,
catCode1, catCode2,
catCode3, catCode4, catCode5, catCode6, catCode7,
catCode8, [Date],
Location, magTitleID, miscTitleID, Month, Notes, [Number],
PageEnd,
PageStart, Title, Type, Volume, Year
FROM Articles
3. Also for your reference, the schema for the Articles
table (the only table being updated) is:
artTitleID AutoNumber Primary Key
Title Text 128 Default = "No
Title ???"
Author1 Text 32 Default = " "
Author2 Text 32 Default = " "
Author3 Text 32 Default = " "
Type Text 4 Default = "misc"
bkTitleID Long Integer Default = 0
magTitleID Long Integer Default = 0 Foreign
Key = Magazines.magTi tleID
miscTitleID Long Integer Default = 0
Year Long Integer Default = 2003
Month Long Integer Default = 18
Date Long Integer Default = 0
PageStart Text 4 Default = " "
PageEnd Text 4 Default = " "
Volume Text 4 Default = " "
Number Text 4 Default = " "
Location Text 128 Default = "MDJ
Library"
Notes Memo Default = " "
catCode1 Text 16 Default = " " Foreign Key
= Categories.catC ode
catCode2 Text 16 Default = " " Foreign Key
= Categories.catC ode
catCode3 Text 16 Default = " " Foreign Key
= Categories.catC ode
catCode4 Text 16 Default = " " Foreign Key
= Categories.catC ode
catCode5 Text 16 Default = " " Foreign Key
= Categories.catC ode
catCode6 Text 16 Default = " " Foreign Key
= Categories.catC ode
catCode7 Text 16 Default = " " Foreign Key
= Categories.catC ode
catCode8 Text 16 Default = " " Foreign Key
= Categories.catC ode
The schema for the referenced Magazines table is:
magTitleID Long Integer Primary Key
Title Text 64
PubName Text 32
PubCity Text 32
And the schema for the referenced Categories table is:
catCode Text 16 Primary Key
catTitle Text 255
supCatCode Text 8
4. Before attempting the above update, I use the following
code to display the data from the newly created row in the
local dataset's table (DsInfoBase.Art icles):
Dim rowCount As Integer
Dim xc As Integer
xc = 1
rowCount = DsInfoBase.Arti cles.Rows.Count ()
MsgBox("Row Count = " & rowCount.ToStri ng & CRLF _
& "artTitleID = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("artTi tleID") & CRLF _
& "Title = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("Title ") & CRLF _
& "Author1 = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("Autho r1") & CRLF _
& "Author2 = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("Autho r2") & CRLF _
& "Author3 = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("Autho r3") & CRLF _
& "Type = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("Type" ) & CRLF _
& "bkTitleID = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("bkTit leID") & CRLF _
& "magTitleID = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("magTi tleID") & CRLF _
& "miscTitleI D = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("miscT itleID") & CRLF _
& "Year = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("Year" ) & CRLF _
& "Month = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("Month ") & CRLF _
& "Date = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("Date" ) & CRLF _
& "PageStart = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("PageS tart") & CRLF _
& "PageEnd = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("PageE nd") & CRLF _
& "Volume = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("Volum e") & CRLF _
& "Number = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("Numbe r") & CRLF _
& "Location = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("Locat ion") & CRLF _
& "Notes = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("Notes ") & CRLF _
& "catCode1 = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("catCo de1") & CRLF _
& "catCode2 = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("catCo de2") & CRLF _
& "catCode3 = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("catCo de3") & CRLF _
& "catCode4 = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("catCo de4") & CRLF _
& "catCode5 = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("catCo de5") & CRLF _
& "catCode6 = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("catCo de6") & CRLF _
& "catCode7 = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("catCo de7") & CRLF _
& "catCode8 = " & DsInfoBase.Arti cles.Rows _
(rowCount - xc).Item("catCo de8") & CRLF, _
MsgBoxStyle.OKO nly, "Temporary Check")
An inspection of the information displayed in the msgBox
shows that everything seems okay.
5. To double-check, I then use the following code:
Dim i As Integer
Dim HC As String
Dim HE As String
Dim ErrRec As String
Dim TE As DataTable
Dim RE As DataRow()
Dim CE As DataColumn
HC = "No Changes"
HE = "No Errors"
ErrRec = ""
If DsInfoBase.HasC hanges Then
HC = "Changes"
If DsInfoBase.HasE rrors Then
HE = "Errors"
For Each TE In DsInfoBase.Tabl es
If TE.HasErrors Then
RE = TE.GetErrors
For i = 0 To RE.Length
For Each CE In TE.Columns
ErrRec = ErrRec &
CE.ColumnName & " " _
& RE(i).GetColumn Error(CE) & CRLF
Next
RE(i).ClearErro rs()
Next
End If
Next
End If
End If
ErrRec = HC & CRLF & HE & CRLF & ErrRec
MsgBox(ErrRec, MsgBoxStyle.OKO nly, "Record of
Errors")
It reports: Changes
No Errors
from which I conclude that the data has been successfully
entered into the local dataset's table but the
OleDbDataAdapte r is failing to update the database from
the dataset for some reason I can't figure out.
6. FYI, I am using Microsoft Access 2000 (9.0.3821 SR-1)
and Visual Basic.NET(Micro soft Development Environment
2002 Version 7.0.9466, Microsoft .NET Framework 1.0
Version 1.0.3705) running under Microsoft Windows 2000
Professional (5.0.2195, Service Pack 3, Build 2195) on a
Dell Optiplex GX400 (P4, 1.3 GHz, 512 MB RAM, 37.2 GB HD).
7. After designing the form layout, I went to Server
Explorer and dragged the Articles table to the form, thus
creating OleDbConnection 1 and OleDbDataAdapte rArticles.
From the DataAdapter, I generated the DsInfoBase dataset.
8. I then dragged the two reference tables to the form,
thus creating OleDbDataAdapte rMagazines and
OleDbDataAdapte rCategories. I added the corresponding
local tables to the DsInfoBase dataset. I created several
dataviews based on the two reference tables and bound them
to listboxes on the form. The form includes an "Add
Record" button to execute the addition of the new row to
the Articles table. The following code performs the
transfer of information from the form to the local
dataset's Articles table:
' Update the Local Articles Table
Dim drN As DataRow = DsInfoBase.Arti cles.NewRow()
drN("Title") = HTitle
drN("Author1") = HAuthor1
drN("Author2") = HAuthor2
drN("Author3") = HAuthor3
drN("Type") = HType
drN("bkTitleID" ) = HBkTitleID
drN("magTitleID ") = HMagTitleID
drN("miscTitleI D") = HMiscTitleID
drN("Year") = HYear
drN("Month") = HMonth
drN("Date") = HDate
drN("PageStart" ) = HPageStart
drN("PageEnd") = HPageEnd
drN("Volume") = HVolume
drN("Number") = HNumber
drN("Location") = HLocation
drN("Notes") = HNotes
drN("catCode1") = HCatCode1
drN("catCode2") = HCatCode2
drN("catCode3") = HCatCode3
drN("catCode4") = HCatCode4
drN("catCode5") = HCatCode5
drN("catCode6") = HCatCode6
drN("catCode7") = HCatCode7
drN("catCode8") = HCatCode8
DsInfoBase.Arti cles.Rows.Add(d rN)
9. CRLF = Chr(13) & Chr(10)
This is a personal idiosyncrasy - I prefer this to using
the constant vbCrLf because it's sometimes convenient when
generating certain files for Linux servers.
10. The Knowledge Base doesn't seem to have much on this.
I used various search terms including keyword searches on
kbADONET and kbVBNET. The few articles I found (301248,
308055, 316323, and 326602) indicate that I'm using the
proper update statement format. The
microsoft.publi c.dotnet.langua ges.vb and other newsgroups
don't seem to address this either (at least, I haven't
been able to find anything).
M. David Johnson
Director of Information Technology
Illinois Racing Board
da***********@i rb.state.il.us
File c:\work\word work\20030709db Problem.rtf
Posted to microsoft.publi c.dotnet.langua ges.vb 2003/07/09
14:05 CDT
