Adapter.Update

[jsurpless]

Hi all

I've successfully used the following code to populate a datatable from my MS Access database

strMSAccess_Connection_SQL = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabaseFilePath & ";Persist Security Info=True"
MSAccess_Connection = New OleDbConnection(strMSAccess_Connection_SQL)

MSAccess_Connection.Open()

strDatabaseTable_SQL = "SELECT * FROM [" & strDatabaseTable & "]"

If (strDatabaseTable_SQLWhere <> "") Then

  strDatabaseTable_SQL = strDatabaseTable_SQL & " " & strDatabaseTable_SQLWhere

End If

MSAccess_Database_Adapter = New OleDbDataAdapter(strDatabaseTable_SQL, MSAccess_Connection)
       MSAccess_Database_Adapter.Fill(MSAccess_DatabaseTable)

Now, I'd like to use a similar method to push changes back to the database, having done them to the datatable...

I was thinking I'd be able to do something like this but it doesn't seem to work...

strMSAccess_Connection_SQL = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabaseFilePath & ";Persist Security Info=True"
MSAccess_Connection = New OleDbConnection(strMSAccess_Connection_SQL)

MSAccess_Connection.Open()

strDatabaseTable_SQL = "SELECT * FROM [" & strDatabaseTable & "]"

If (strDatabaseTable_SQLWhere <> "") Then

  strDatabaseTable_SQL = strDatabaseTable_SQL & " " & strDatabaseTable_SQLWhere

End If

MSAccess_Database_Adapter = New OleDbDataAdapter(strDatabaseTable_SQL, MSAccess_Connection)

MSAccess_Database_Adapter.AcceptChangesDuringUpdate = True

MSAccess_Database_Adapter.Update(MSAccess_DatabaseTable)

Any thoughts?

 

[Tom]

Do you have a primary key column in that table?

 

[cjard]

all of which use their own database...

Do the databases have the same schema?

What is 'Me'?

The current object (instance) that the thread is running the code for. In C# it is "this".

Suppose you have 2 objects in memory. When ObjectA.MySub is running, Me refers to ObjectA itself. When ObjectA.MySub calls ObjectB.OtherSub, Me used in that other sub, will refer to ObjectB

Put another way:

Class Whatever

  Private myVariable as String = "Hello World"

  Private Sub MySub()
    'the following lines refer to the same thing
    Me.myVariable = "GoodBye World"
    myVariable = "GoodBye World"
  End Sub
End Class

Compare it with MyBase, which refers to the parent object

 

[cjard]

I was able to load up my datatable, make some changes to it and attempt to set the .UpdateCommand via

Do all the databases across the company have the same schema? Youre writing hard SQLs into your app here, so I don't really accept the line that "I can't use the wizard because all my databases are different across the company" - if they truly are different, then this query will fail in all but one database

If youre concerned that the wizard generates code that is tied to a particaulr copy of a db, you're not correct; we're trying to help with that but you're not helping us to help you because youre not answering questions

Now, these are all fields in my table but what does this all mean? Googling around, I've seen others with this sort of error...

It's not an error, it's an optimistically concurrent update query. It optimistically hopes that noone else edited the data at the same time as you. If this query updates 0 rows, then someone else did edit the data before you did, and you can take steps to resolve the situation. Optimistic concurrency does not use locks, which can be expensive, and in Access are poorly implemented

 

[jsurpless]

Do you have a primary key column in that table?

Yes, I have a column called 'Drawing_FileNameID' which is an autonumber field...

 

[jsurpless]

Do all the databases across the company have the same schema? Youre writing hard SQLs into your app here, so I don't really accept the line that "I can't use the wizard because all my databases are different across the company" - if they truly are different, then this query will fail in all but one database

I see how I am using hard-coded SQL statements but they're merely "SELECT * from strTable" - I'm also using my query statements to search through my datatable but these aren't hard-coded as I'm simply saying find the value in my database that matches this variable in this other variable field... all my databases are MS Access 2003 but the column names, etc can be completely different... I use a column-mapping XML file to specify my field names and how they relate to AutoCAD... similarly, the values of the tables and/or field names are specified by the user during run-time...

If youre concerned that the wizard generates code that is tied to a particaulr copy of a db, you're not correct; we're trying to help with that but you're not helping us to help you because youre not answering questions

OK, since I'm not correct about the generated code being tied to a specific DB, I'll take another look at that...

My apologies for "making things difficult" - not my intention at all...

Appreciate the help...

 

[Tom]

Wether to use auto-generated code is a matter of preference but I see no advantages in doing but which every method you pick, yes you can always re-assign the database connection at runtime. Similary I also work in an environement where each of our office locations has there own individual copy of company database.

The auto-generated sql does make some things simplier (such as not having to write it) but at the same time it may be overkill and doing more work then needed; for example your commandbuilder generated update sql statement, its comparing every single field, unsure of what fields may or may not change and likely it is only certain fields that are being updated.

My personal preference is keep full control of my data and therefore write my own sql statements directly in stored procedures. The stored procedures can then be connected to your data adapter/table adapter.

 

[Tom]

Yes, I have a column called 'Drawing_FileNameID' which is an autonumber field...

::shrugs:: I really cant guess to much more without knowing more details about the error and whats in the update statement along with the parameter values being passed at the time of the error.

However I would start by eliminating whether the update error is constant with all update calls or perhaps only certain records with different values.

 

[jsurpless]

::shrugs:: I really cant guess to much more without knowing more details about the error and whats in the update statement along with the parameter values being passed at the time of the error.

However I would start by eliminating whether the update error is constant with all update calls or perhaps only certain records with different values.

I tried changing a single field to "JUSTIN" and the error still occurs...

I'm giving a good deal of thought to going via your suggestion - to execute each and every update individual...

 

[jsurpless]

Been troubleshooting this - found a sample .sln that I tested and found to work

http://emmet-gray.com/Programs/ADONet_Test.zip

Pasted that code into my application and interacted with the same DB... still works... changed it to my DB while keeping the DIMs the same and all that... suddenly it doesn't work... as such, I'm assuming that it must be something to do with my DB...

Next step is to try a simplified version of my DB

 

[cjard]

OK, since I'm not correct about the generated code being tied to a specific DB, I'll take another look at that...

It's tied to a specific schema.. Youre now saying that your db will interact with many databases having wildly different schema


What I can't understand is that youre writing a front end to link to access databases, but a perfectly good front end already exists to do that; Microsoft Access.. I'm sure there will be other tools too, I just never looked for them, but if you must write your own query tool to work across hundreds of different databases, going the un-typed route (i.e. no TableAdapters) will be necessary

 

[jsurpless]

It's tied to a specific schema.. Youre now saying that your db will interact with many databases having wildly different schema


What I can't understand is that youre writing a front end to link to access databases, but a perfectly good front end already exists to do that; Microsoft Access.. I'm sure there will be other tools too, I just never looked for them, but if you must write your own query tool to work across hundreds of different databases, going the un-typed route (i.e. no TableAdapters) will be necessary

Are you asking why I'm writing an interface to MS Access? That's not what I'm doing... what I'm developing is a utility that reads information entered into AutoCAD Reference Block Attributes and transfers it to MS Access...

 

[cjard]

Now I'm lost


Are autocad files (your data source) some kind of standard database?
When you say "transferring to access" you mean a Jet database? (Access is a front end/ development environment for [typically] jet databases)
Is the destination database a fixed schema?

 

[jsurpless]

AutoCAD is a computer-aided drafting package that my company uses for documentation delivery to many customer... these .dwg files contain blocks of data but not within a database structure (i.e, table for ALL data)...

I'm reading the values of the blocks within each .dwg and locating the corresponding record in an MS Access table...

Pardon me, but what do you mean by 'fixed schema'? They are always MS Access tables, if that's what you mean...

 

[jsurpless]

It seems that my UPDATE syntax issue results from the fact that I have 'spaces' in the table names...

How can I get the .Update to use "[" & Table_Name & "]"?

Thanks!

*EDIT*

A quick Google search revealed the ability to "name" a datatable like

Dim myTable as New Datatable(strMyTableName)

Still doesn't seem to like it... I'm guessing I need some delimitting characters around my string because it's probably still just putting it in there with spaces...

 

[cjard]

It seems that my UPDATE syntax issue results from the fact that I have 'spaces' in the table names...

How can I get the .Update to use "[" & Table_Name & "]"?

By ensuring that your SQL looks like:

"UPDATE [table name] SET ..."

A quick Google search revealed the ability to "name" a datatable like

Dim myTable as New Datatable(strMyTableName)

Still doesn't seem to like it... I'm guessing I need some delimitting characters around my string because it's probably still just putting it in there with spaces...

DataTable is a client side storage container, nothing to do with a table in a database.. Much like an array of lines of strings is nothing to do with a text file on disk