473,544 Members | 1,748 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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

Nov 19 '05 #1
0 5801

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

Similar topics

1
3724
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 appreciated.. Error: An unhandled exception of type "System.Data.OleDb.OleDbException' occurred in system.data.dll This error is from the following...
2
3806
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? public void CopyDataSet(int start, int end) { OleDbDataAdapter sourceAdapter = new OleDbDataAdapter("SELECT * FROM " + "WHERE ID > " + (start -...
1
5064
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: dsLocalDataSet.user_postRow newRow = dsLocalDataSet1.user_post.Newuser_postRow(); newRow.post_text = this.lblHiddenMessageStorage.Text;...
9
5264
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 INTO Table ( ID, Cod, CodArt, Q1, DataUscita ) VALUES (pID, pCod, pCod, pQ1, pDataUscita);
5
1920
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" Fields: fNumber Numeric fString VarChar(50)
1
1376
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 sTblName As String = po_dt.TableNam Dim dt As Data.DataTabl Dim da As New OleDbDataAdapter("SELECT * FROM " & sTblName, conn Dim custCB As...
1
4752
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 the field ends up with only a date, with no time component. In my project... I added an OleDBDataAdapter using the wizzard to my form. I...
6
14876
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 would like to write the new ControlDate or Version into the database. First i update the dataset, then i create a Insertcommand and call the...
2
2910
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 draging Table1 to Form2. The select command works fine, but when I change the data and call update command I get a syntax error: ...
0
7414
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7598
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7360
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7699
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5895
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5288
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4905
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3398
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1833
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.