M
M. David Johnson
I cannot get my OleDbDataAdapter 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
OleDbDataAdapterArticles.Update
(DsInfoBase, "Articles")
' An unhandled exception of type
' System.Data.OleDb.OleDbException occurs in
' system.data.dll
Catch ex As Exception
MsgBox("Type = " & ex.GetType.ToString() &
CRLF & _
"Message = " & ex.Message, MsgBoxStyle.OKOnly, _
"Try-Catch Mechanism")
End Try
It reports: Type = System.Data.OleDb.OleDbException
Message = Syntax error in INSERT INTO
statement.
2. However, the INSERT INTO statement was automatically
generated by the IDE when the OleDbDataAdapterArticles 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.magTitleID
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.catCode
catCode2 Text 16 Default = " " Foreign Key
= Categories.catCode
catCode3 Text 16 Default = " " Foreign Key
= Categories.catCode
catCode4 Text 16 Default = " " Foreign Key
= Categories.catCode
catCode5 Text 16 Default = " " Foreign Key
= Categories.catCode
catCode6 Text 16 Default = " " Foreign Key
= Categories.catCode
catCode7 Text 16 Default = " " Foreign Key
= Categories.catCode
catCode8 Text 16 Default = " " Foreign Key
= Categories.catCode
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.Articles):
Dim rowCount As Integer
Dim xc As Integer
xc = 1
rowCount = DsInfoBase.Articles.Rows.Count()
MsgBox("Row Count = " & rowCount.ToString & CRLF _
& "artTitleID = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("artTitleID") & CRLF _
& "Title = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Title") & CRLF _
& "Author1 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Author1") & CRLF _
& "Author2 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Author2") & CRLF _
& "Author3 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Author3") & CRLF _
& "Type = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Type") & CRLF _
& "bkTitleID = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("bkTitleID") & CRLF _
& "magTitleID = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("magTitleID") & CRLF _
& "miscTitleID = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("miscTitleID") & CRLF _
& "Year = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Year") & CRLF _
& "Month = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Month") & CRLF _
& "Date = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Date") & CRLF _
& "PageStart = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("PageStart") & CRLF _
& "PageEnd = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("PageEnd") & CRLF _
& "Volume = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Volume") & CRLF _
& "Number = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Number") & CRLF _
& "Location = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Location") & CRLF _
& "Notes = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Notes") & CRLF _
& "catCode1 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode1") & CRLF _
& "catCode2 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode2") & CRLF _
& "catCode3 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode3") & CRLF _
& "catCode4 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode4") & CRLF _
& "catCode5 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode5") & CRLF _
& "catCode6 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode6") & CRLF _
& "catCode7 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode7") & CRLF _
& "catCode8 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode8") & CRLF, _
MsgBoxStyle.OKOnly, "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.HasChanges Then
HC = "Changes"
If DsInfoBase.HasErrors Then
HE = "Errors"
For Each TE In DsInfoBase.Tables
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).GetColumnError(CE) & CRLF
Next
RE(i).ClearErrors()
Next
End If
Next
End If
End If
ErrRec = HC & CRLF & HE & CRLF & ErrRec
MsgBox(ErrRec, MsgBoxStyle.OKOnly, "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
OleDbDataAdapter 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(Microsoft 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 OleDbConnection1 and OleDbDataAdapterArticles.
From the DataAdapter, I generated the DsInfoBase dataset.
8. I then dragged the two reference tables to the form,
thus creating OleDbDataAdapterMagazines and
OleDbDataAdapterCategories. 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.Articles.NewRow()
drN("Title") = HTitle
drN("Author1") = HAuthor1
drN("Author2") = HAuthor2
drN("Author3") = HAuthor3
drN("Type") = HType
drN("bkTitleID") = HBkTitleID
drN("magTitleID") = HMagTitleID
drN("miscTitleID") = 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.Articles.Rows.Add(drN)
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.public.dotnet.languages.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
(e-mail address removed)
File c:\work\word work\20030709dbProblem.rtf
Posted to microsoft.public.dotnet.languages.vb 2003/07/09
14:05 CDT
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
OleDbDataAdapterArticles.Update
(DsInfoBase, "Articles")
' An unhandled exception of type
' System.Data.OleDb.OleDbException occurs in
' system.data.dll
Catch ex As Exception
MsgBox("Type = " & ex.GetType.ToString() &
CRLF & _
"Message = " & ex.Message, MsgBoxStyle.OKOnly, _
"Try-Catch Mechanism")
End Try
It reports: Type = System.Data.OleDb.OleDbException
Message = Syntax error in INSERT INTO
statement.
2. However, the INSERT INTO statement was automatically
generated by the IDE when the OleDbDataAdapterArticles 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.magTitleID
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.catCode
catCode2 Text 16 Default = " " Foreign Key
= Categories.catCode
catCode3 Text 16 Default = " " Foreign Key
= Categories.catCode
catCode4 Text 16 Default = " " Foreign Key
= Categories.catCode
catCode5 Text 16 Default = " " Foreign Key
= Categories.catCode
catCode6 Text 16 Default = " " Foreign Key
= Categories.catCode
catCode7 Text 16 Default = " " Foreign Key
= Categories.catCode
catCode8 Text 16 Default = " " Foreign Key
= Categories.catCode
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.Articles):
Dim rowCount As Integer
Dim xc As Integer
xc = 1
rowCount = DsInfoBase.Articles.Rows.Count()
MsgBox("Row Count = " & rowCount.ToString & CRLF _
& "artTitleID = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("artTitleID") & CRLF _
& "Title = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Title") & CRLF _
& "Author1 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Author1") & CRLF _
& "Author2 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Author2") & CRLF _
& "Author3 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Author3") & CRLF _
& "Type = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Type") & CRLF _
& "bkTitleID = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("bkTitleID") & CRLF _
& "magTitleID = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("magTitleID") & CRLF _
& "miscTitleID = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("miscTitleID") & CRLF _
& "Year = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Year") & CRLF _
& "Month = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Month") & CRLF _
& "Date = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Date") & CRLF _
& "PageStart = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("PageStart") & CRLF _
& "PageEnd = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("PageEnd") & CRLF _
& "Volume = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Volume") & CRLF _
& "Number = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Number") & CRLF _
& "Location = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Location") & CRLF _
& "Notes = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("Notes") & CRLF _
& "catCode1 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode1") & CRLF _
& "catCode2 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode2") & CRLF _
& "catCode3 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode3") & CRLF _
& "catCode4 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode4") & CRLF _
& "catCode5 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode5") & CRLF _
& "catCode6 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode6") & CRLF _
& "catCode7 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode7") & CRLF _
& "catCode8 = " & DsInfoBase.Articles.Rows _
(rowCount - xc).Item("catCode8") & CRLF, _
MsgBoxStyle.OKOnly, "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.HasChanges Then
HC = "Changes"
If DsInfoBase.HasErrors Then
HE = "Errors"
For Each TE In DsInfoBase.Tables
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).GetColumnError(CE) & CRLF
Next
RE(i).ClearErrors()
Next
End If
Next
End If
End If
ErrRec = HC & CRLF & HE & CRLF & ErrRec
MsgBox(ErrRec, MsgBoxStyle.OKOnly, "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
OleDbDataAdapter 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(Microsoft 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 OleDbConnection1 and OleDbDataAdapterArticles.
From the DataAdapter, I generated the DsInfoBase dataset.
8. I then dragged the two reference tables to the form,
thus creating OleDbDataAdapterMagazines and
OleDbDataAdapterCategories. 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.Articles.NewRow()
drN("Title") = HTitle
drN("Author1") = HAuthor1
drN("Author2") = HAuthor2
drN("Author3") = HAuthor3
drN("Type") = HType
drN("bkTitleID") = HBkTitleID
drN("magTitleID") = HMagTitleID
drN("miscTitleID") = 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.Articles.Rows.Add(drN)
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.public.dotnet.languages.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
(e-mail address removed)
File c:\work\word work\20030709dbProblem.rtf
Posted to microsoft.public.dotnet.languages.vb 2003/07/09
14:05 CDT