update MS Access column from a variable

[droose]

I need to update individual columns in selected rows of an access database in VB Express (net). The code I am using is listed below. It compiles but does not updata the data base. I have been able to find examples of everything about MS Access except, "How to update a record column from a variable rather than a data bound form element". Any help would be appreciated.

Thanks,
Dick Roose

[SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' OLEDB Init
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strConnectionString [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\ThePokerEdgeNet\Result.mdb"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] objConn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.OleDb.OleDbConnection(strConnectionString)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cb [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Data.OleDb.OleDbCommandBuilder
[/SIZE][SIZE=2][COLOR=#008000]'Connect to provider (connectionstring above)
[/COLOR][/SIZE][SIZE=2]objConn.Open()
[/SIZE][SIZE=2][COLOR=#008000]' Data Source > Data Adapter > Data Set (create DataSet)
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]s = [/SIZE][SIZE=2][COLOR=#800000]"SELECT * FROM Results WHERE HandID = "[/COLOR][/SIZE][SIZE=2] & hid & [/SIZE][SIZE=2][COLOR=#800000]" and Suited = "[/COLOR][/SIZE][SIZE=2] & suited
da = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.OleDb.OleDbDataAdapter(s, objConn)
cb = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.OleDb.OleDbCommandBuilder(da)
DSet = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet([/SIZE][SIZE=2][COLOR=#800000]"MS_Access_DataSet"[/COLOR][/SIZE][SIZE=2])
da.Fill(DSet, [/SIZE][SIZE=2][COLOR=#800000]"MS_Access_DataSet"[/COLOR][/SIZE][SIZE=2])
d = DSet.Tables([/SIZE][SIZE=2][COLOR=#800000]"MS_Access_Dataset"[/COLOR][/SIZE][SIZE=2]).Rows(g_lngCurrentRow).Item([/SIZE][SIZE=2][COLOR=#800000]"Dealt"[/COLOR][/SIZE][SIZE=2]) + 1
DSet.Tables([/SIZE][SIZE=2][COLOR=#800000]"MS_Access_Dataset"[/COLOR][/SIZE][SIZE=2]).Rows(g_lngCurrentRow).Item([/SIZE][SIZE=2][COLOR=#800000]"Dealt"[/COLOR][/SIZE][SIZE=2]) = d
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] myBet <> 0 [/SIZE][SIZE=2][COLOR=#0000ff]And[/COLOR][/SIZE][SIZE=2] WnLs <> 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]p = DSet.Tables([/SIZE][SIZE=2][COLOR=#800000]"MS_Access_Dataset"[/COLOR][/SIZE][SIZE=2]).Rows(g_lngCurrentRow).Item([/SIZE][SIZE=2][COLOR=#800000]"Played"[/COLOR][/SIZE][SIZE=2]) + 1
DSet.Tables([/SIZE][SIZE=2][COLOR=#800000]"MS_Access_Dataset"[/COLOR][/SIZE][SIZE=2]).Rows(g_lngCurrentRow).Item([/SIZE][SIZE=2][COLOR=#800000]"Played"[/COLOR][/SIZE][SIZE=2]) = p
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] WnLs > 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]w = DSet.Tables([/SIZE][SIZE=2][COLOR=#800000]"MS_Access_Dataset"[/COLOR][/SIZE][SIZE=2]).Rows(g_lngCurrentRow).Item([/SIZE][SIZE=2][COLOR=#800000]"Won"[/COLOR][/SIZE][SIZE=2]) + 1
DSet.Tables([/SIZE][SIZE=2][COLOR=#800000]"MS_Access_Dataset"[/COLOR][/SIZE][SIZE=2]).Rows(g_lngCurrentRow).Item([/SIZE][SIZE=2][COLOR=#800000]"Won"[/COLOR][/SIZE][SIZE=2]) = w
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2]per = System.Math.Round(w / p, 2)
DSet.Tables([/SIZE][SIZE=2][COLOR=#800000]"MS_Access_Dataset"[/COLOR][/SIZE][SIZE=2]).Rows(g_lngCurrentRow).Item([/SIZE][SIZE=2][COLOR=#800000]"Percent"[/COLOR][/SIZE][SIZE=2]) = per
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]' Update the dataset
[/COLOR][/SIZE][SIZE=2]da.Update(DSet, [/SIZE][SIZE=2][COLOR=#800000]"MS_Access_DataSet"[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#0000ff]Catch
[/COLOR][/SIZE][SIZE=2]s = [/SIZE][SIZE=2][COLOR=#800000]"SELECT * FROM Results"
[/COLOR][/SIZE][SIZE=2]da = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.OleDb.OleDbDataAdapter(s, objConn)
cb = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.OleDb.OleDbCommandBuilder(da)
DSet = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet([/SIZE][SIZE=2][COLOR=#800000]"MS_Access_DataSet"[/COLOR][/SIZE][SIZE=2])
da.Fill(DSet, [/SIZE][SIZE=2][COLOR=#800000]"MS_Access_DataSet"[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dr [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Data.DataRow = DSet.Tables([/SIZE][SIZE=2][COLOR=#800000]"MS_Access_Dataset"[/COLOR][/SIZE][SIZE=2]).NewRow()
dr([/SIZE][SIZE=2][COLOR=#800000]"Cards"[/COLOR][/SIZE][SIZE=2]) = PDcards
dr([/SIZE][SIZE=2][COLOR=#800000]"HandID"[/COLOR][/SIZE][SIZE=2]) = hid
dr([/SIZE][SIZE=2][COLOR=#800000]"Suited"[/COLOR][/SIZE][SIZE=2]) = suited
dr([/SIZE][SIZE=2][COLOR=#800000]"OddsOW"[/COLOR][/SIZE][SIZE=2]) = OOW
dr([/SIZE][SIZE=2][COLOR=#800000]"Dealt"[/COLOR][/SIZE][SIZE=2]) = 1
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] myBet <> 0 [/SIZE][SIZE=2][COLOR=#0000ff]And[/COLOR][/SIZE][SIZE=2] WnLs <> 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]dr([/SIZE][SIZE=2][COLOR=#800000]"Played"[/COLOR][/SIZE][SIZE=2]) = i
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] WnLs > 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]dr([/SIZE][SIZE=2][COLOR=#800000]"Won"[/COLOR][/SIZE][SIZE=2]) = 1
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2]per = System.Math.Round(w / p, 2)
dr([/SIZE][SIZE=2][COLOR=#800000]"Percent"[/COLOR][/SIZE][SIZE=2]) = per
[/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2]dr([/SIZE][SIZE=2][COLOR=#800000]"Played"[/COLOR][/SIZE][SIZE=2]) = 0
dr([/SIZE][SIZE=2][COLOR=#800000]"Won"[/COLOR][/SIZE][SIZE=2]) = 0
dr([/SIZE][SIZE=2][COLOR=#800000]"Percent"[/COLOR][/SIZE][SIZE=2]) = 0
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] istournament [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]dr([/SIZE][SIZE=2][COLOR=#800000]"CWinLost"[/COLOR][/SIZE][SIZE=2]) = WnLs
[/SIZE][SIZE=2][COLOR=#0000ff]ElseIf[/COLOR][/SIZE][SIZE=2] PlayMoney [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]dr([/SIZE][SIZE=2][COLOR=#800000]"PWinLost"[/COLOR][/SIZE][SIZE=2]) = WnLs
[/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2]dr([/SIZE][SIZE=2][COLOR=#800000]"$WinLost"[/COLOR][/SIZE][SIZE=2]) = WnLs
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2]DSet.Tables([/SIZE][SIZE=2][COLOR=#800000]"MS_Access_DataSet"[/COLOR][/SIZE][SIZE=2]).Rows.Add(dr)
[/SIZE][SIZE=2][COLOR=#008000]'da.Update(DSet, "MS_Access_DataSet")
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]Cleanup()
objConn.Close()

[/SIZE]

 

[droose]

Hi cjard,

It seems to me that we are talking about two systems that differ in significant ways. For instance, when I run your code on my system, what you call database 1 never gets updated in any way except if I programically copy database 2 back into database 1. Only database 2 gets updated and that update gets lost the next time the code is run. The update just goes into the bit bucket.

Yesterday when showed "data sources and I right clicked on the results db, I got the option to "Edit Data set with designer". Today I get the option in the copy of your code I pasted into my project, but I do not get the option in you code. This is where I could see the two instructions "fill" and "fillby" in your code but the fillby instruction was missing from the pasted code database. Now it is missing from your code too because I deleted the datasource and re added it to the code. Now I can see the new column but because of the missing "fillby" I cannot run either code.

In my forty some years of programming I have worked with just about every compiller ever written at one time or another. I can tell you one thing about vb.net. It sucks. It is the most illogical, error prone, poorly documented, ill conceved, slow and difficult to work with peice of crap I have ever run across. I haven't said this many times in my life, but I give up! I will write my data to a file and write a vb6 program to convert it to a database I can work with.

I really appreciate your efforts and wish there was some additional way I could show it. For now, my thanks is all I have.

Dick Roose

 

[cjard]

Database 1 is not supposed to be updated; it is as much a part of the source code as anything else. Just as you use the Visual Studio IDE to update the code content of a *.vb file, you must use Microsoft Access to update database 1.

As noted, the collection of files you see in the solution explorer window; Form1.vb, ResultsDataSet.xsd etc etc go to make up a program that is compiled an runs. Resources like DLLs and databases are rarely compiled into the exe; they are copied to the output directory along with the EXE.
The EXE will thus be configured to read and write that database..

Also as I said, the IDE is set up to copy Database 1 out every single time you perform a build. This is a sensible idea because who knows what your program code might do if it has a bug; the program code might accidentally destroy all your carefuly prepared test data. If it does this on a copy of the database, noone cares.
It does have the side effect of (for file based databases that are a member of the project only) copying Database 1 (the project original database) over the top of any changes you have made to another database entirely.

The IDE is behaving normally, and as you(i, we) have instructed it to do. You can change the instruction, so that any changes wont be lost but it is not the IDEs job to copy Databse 2 back over Database 1 to 'preserve your changes'

I dont know how else to explain it other than this: When you write code, it needs building into an exe. During a build, VS deletes any previous built EXEs and then copies the latest version to the \bin\Debug or \bin\Release folders. It also copies any supporting files according to parameters the user sets. Your original database is being copied over the updated one as per instruction and the two become separate databases at that point. At no time does your program interact with the database you see in the Solution Explorer window. It interacts with its own copy, in its own directory, and that is a different directory to the solution directory

Every time you press the Play button or request a Build from the Build menu, a Build is performed. The database is copied during the Build


What happens in a production environment? Well NO BUILDS TAKE PLACE because building is a development process designed to produce an output that the users run. The program doesnt copy the database anywhere, it doesnt overwrite any other databases, it just runs


-
Perhaps it would help if I explain that VB2005 is NOT like VB6. When you press play, an EXE is built, that EXE is run and then the debugger hooks into the process. The Build must take place first for the debugger to have something to hook into. In VB6 you could press play 1000 times and no EXE would ever be made. Make EXE was only performed when you chose Make Exe from the file menu. In debug mode the code you wrote and stepped through WOULD change your original database. This meant if you wanted to avoid releasing a database that was full of hello world and other test junk, you had to keep a clean copy somewhere. In VB2005 the database you see in the Solution Explorer IS your clean copy


-

VB2005 is confusing, it was even for me; a university trained OO programmer who previously used VB6 extensively. I was able to quickly pick it up, but I wish I'd had some orientation like this picture I made for you, when it came to the data access part:

 

[cjard]

(wide picture)

 

[cjard]

your VS2005 window may not be laid out like this; it's how I work because I have a 1400x1200 screen - i can cope with some clutter

All the windows on display here are available to you, just do bear in mind that they can sometimes look similar enough to be confusing, especially when they are two tabs in the same container

 

[cjard]

Incidentally, when coding VB2K5 data access I found these pages to be valuable:

http://msdn2.microsoft.com/en-us/library/fxsa23t6.aspx


I ran into many problems that had me baffled for weeks; i'd be glad to tell anyone what I learned in realising the solution, so here's hoping you stick with it - it makes things a lot easier and does do a reasonable job of confining all data access to a few classes - encapsulata sensible!

 

[droose]

Hi cjard,

I have been having a most frustrating time with vb net and data access. If it isn't one thing its another.

After much toying I got you code to run in my application. Then I tried to add more columns and have not had any success since. No matter what I do, going back to the original code I get the same error message: .

"Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

I have traced the code and a valid update command (Copied directly from you code) causes the catch to be thrown and the error message.

I have researched the error message on the internet and it appears this is a catchall error message for anything Microsoft didn't want to deal with in writtine the compiler.

Any suggestions?

Dick Roose

 

[cjard]

"Update requires a valid UpdateCommand when passed DataRow collection with modified rows."

Sorry for the slow reply, I didnt notice your post and I'm fairly sure the forum didnt email me to tell me you'd posted..

I spoke about this error every recently.. let me see if I can pull the post out of a search.. If, afte reading that post, you still cannot solve the issue, then post a project (make sure you choose CLEAN SOLUTION from the build menu first) and I'll take a look

 

[cjard]

If you read this thread in its entirety, then you will hopefully gain an enhanced understanding of ADO.NET in VS2005:

http://www.vbdotnetforums.com/showthread.php?t=13892