Updating an Access DB Via a Dataset

[MaxinA]

Hey, I need someone to put me out of my misery here..

I am relatively new to VB.NET (old hand at VB6)

So, I have a project where I am taking large, bloated CSV's and importing them into an access database. I understand that filling the CSV data into a DataSet, and then updating the database with the Dataset is a pretty fast way to do it.. Great.. Except all the examples and reading I do about it still leave me with code that is not working(but not erroring either)... So here is where you can collectively tell me what I am doing wrong..

I Create my DataSet:

Dim Conn AsNew OleDb.OleDbConnection(mvarstrConnStr)
Conn.Open()
Dim da AsNew OleDb.OleDbDataAdapter("Select * FROM index_data", Conn)
Dim ds AsNew Data.DataSet("CSV")
Dim dsTbl As Data.DataTable
Dim cb AsNew OleDb.OleDbCommandBuilder(da)
Dim cmd As OleDb.OleDbCommand
cmd = cb.GetInsertCommand()

Then, I Create My DataSet Columns:
'Create DataSet Header
dsTbl = ds.Tables.Add("index_data")
dsTbl.Columns.Add("FileID", GetType(Long))
dsTbl.Columns.Add("Type", GetType(String))
dsTbl.Columns.Add("Count", GetType(Double))
dsTbl.Columns.Add("Size", GetType(Double))
For I = 0 To 120
dsTbl.Columns.Add("C" & I, GetType(Double))
Next
For I = 0 To 120
dsTbl.Columns.Add("M" & I, GetType(Double))
Next

Then I add My Data:

Dim DR As DataRow


For lngCntr = 1 To lngRows - 1

DR = dsTbl.NewRow()​

IfNot DR IsNothingThen​

DR(​

"FileID") = intID​

DR("Type") = Left(strArry(lngCntr, 0), 255)​

DR("Count") = strArry(lngCntr, 1)​

DR("Size") = strArry(lngCntr, 2)​

For lngAttr = 3 To lngCols - 1​

DR(CInt(lngAttr) + 1) = strArry(lngCntr, lngAttr)​

Next​

dsTbl.Rows.Add(DR)​

EndIf

​

Next


​

Then, I commit My changes and Update:

dsTbl.AcceptChanges()
lngRowsIns = da.Update(dsTbl)

Now the problem: No Error, No Records Inserted!

Any Ideas?

Thanks

Andrew

 

[cjard]

read the DNU link in my signature

 

[MaxinA]

cjard,

Thank you for your response, unfortunately, I dont believe that is what is happening in this case. My application creates Access MDB's (via ADOX) on the fly as "project files". I am also 100% positive that a connection is being made to the correct database. I am sure that the problem lies somewhere in how I am using the Dataset to update my database.

 

[cjard]

cjard,

My application creates Access MDB's (via ADOX) on the fly as "project files". I am also 100% positive that a connection is being made to the correct database. I am sure that the problem lies somewhere in how I am using the Dataset to update my database.

If there was an error in doing that you really would get an error message, but you can try MessageBox.Show()ing the return value from Update; it tells how many rows were updated. If it is 0, despite youre SURE that youre passing a dataset with modified rows that has NOT had AcceptChanges() called on it in between editing the rows and sending them to the db, then your update statement is broken. If the number of rows reported as changed is > 0 and youre not seeing it, then I can only suggest that your ADOX code is destroying the edited database, and overwriting it with a new blank one before you get to check the values

 

[MaxinA]

cjard

Well, what I have noted is that I am receiveing NO errors, and am getting zero updates from the Update statement. You mentioned that the update statement may be 'broken'. How could that be? Is there a way to code around such a case? As a side note, I am having the EXACT same issue trying this process via another project with ASP.NET. It just doesnt seem to work...

Andrew

 

[cjard]

Here is a broken update statement:

UPDATE table SET col = 'hello' WHERE 1 = 2


Whats the value of dataset.HasChanges just before you update?

 

[MaxinA]

The value is False.. I just dont know why.....

 

[cjard]

It is false because the dataset has had no modification since the last time that AcceptChanges() was called

Update() calls AcceptChanges implicitly

 

[cjard]

.... If it is 0, despite youre SURE that youre passing a dataset with modified rows that has NOT had AcceptChanges() called on it in between editing the rows and sending them to the db, then your update statement is broken....

Key point of note highlighted

 

[MaxinA]

Ok,

I changed the code to this:

MsgBox(ds.HasChanges())

lngRowsIns = da.Update(dsTbl.GetChanges())

The msgbox reports "True" and I am relying on that dsTbl's changes will be accepted (like you said) implicitly by the update stmt

Now, I get an Error:

"Syntax Error in INSERT INTO statement"

So, how does one fix that?

Thx.

 

[cjard]

I dont do data access this way; it's the "old" way, and you have less control of it than the modern way.

Given that your Insert was generated programmatically, I cannot say for sure what the problem is. Maybe one of your columns is called a reserved word.. Either way, you dont have any design time visibility of what insert is in use so controlling and debugging it will be hard

For the new way, read the DW2 link in my signature, section on creating a simple app

 

[MaxinA]

cjard

You hit the proverbial nail on the head with your last post... I used the column name "Count" which I changed to FCount and everything worked fine...

I can stop pounding my head on my desk now....


Thanks

Andrew

 

[cjard]

If you wanna see the designer break down and cry, call one of your columns "System"