473,287 Members | 3,240 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

OleDbDataAdapter won't update database from dataset (Display at Width = 65)

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
da***********@irb.state.il.us

File c:\work\word work\20030709dbProblem.rtf
Posted to microsoft.public.dotnet.languages.vb 2003/07/09
14:05 CDT

Nov 19 '05 #1
0 5772

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: bob | last post by:
Hi.. I'm making a OleDb connection to a access database.. I can fill the dataset fine and view the data, but when I add a new row and try to update the db I get this error. Any help would be...
2
by: Son Ha | last post by:
I want to copy some record from a Access database to another Access DB. My code as follow but not working. The destAdapter.Update() return 0 record affected. Tell me what's wrong in my code? ...
1
by: Bennett Haselton | last post by:
Suppose I add a new row to a table in a dataset, and then I use an OleDbDataAdapter to add that new row to a SQL Server database using OleDbDataAdapter.Update(), as in the following code: ...
9
by: joun | last post by:
Hi all, i'm using this code to insert records into an Access table from asp.net, using a stored procedure, called qry_InsertData: PARAMETERS Long, Long, Text(20), Long, DateTime; INSERT...
5
by: joun | last post by:
As suggested by Cor Ligthert, i've created a simpler sample, with the same problem; this is the full source code, so everyone can try itself: Access database "dati.mdb": Tables: "myTable"...
1
by: Skiff | last post by:
I have wrote function that upgrades access database Public Function UpdateDataTable(ByRef po_dt As System.Data.DataTable) As Boolea Dim conn As New OleDbConnection(mstrConnectionString Dim...
1
by: RML | last post by:
Hi everyone, I am using VB.NET 2003 and an OleDBDataAdapter to update an Access table's DateTime field. The field's format is set to "General Date" (ie: 11/24/2004 8:00:00 AM). The problem is...
6
by: Marcel Hug | last post by:
Hi all ! I have a table in my database, which has 3 attributes. IDFailureControl, ControlDate and ControlVersion. In the following function I test, if the date of today allready exists. Then I...
2
by: explode | last post by:
I made nova oledbdataadapter select update insert and delete command and connection veza. dataset is Studenti1data, I made it by the new data source wizard,and made datagridview and bindingsource...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.