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]

 

[cjard]

Ugh. I wish this forum allowed me to click an edit button on your psot and at least see the source, even if I cannot save the changes. As it is, I'm going to ask you to re-post your code:

Press EDIT on your post
Press the button in the top right that looks like A/A
Delete the code you posted
Write the word [CODE]
Paste a fresh set of code into your post from your IDE
Write the word [/code]
Press post


That will ensure we see a nice, indented, readable copy of your code. It will help us not only with reading and understanding but also with pasting into our IDE too

 

[droose]

Access database update

Hi cjard,
Thanks much for the reply. I have edited my post as you suggested both with and without brackets surrounding the "code and /code" and the code looks the same to me either way. Is there some way I can email you a copy of the code?

Thanks again,
Dick Roose
dcroose@comcast.net

 

[cjard]

Try these instructions.. Follow them exactly. If you get stuck, please ask:


Press EDIT on your post
Press the button in the top right that looks like

, not

(the editor panel changes colour from a light blue to bright white)
Delete the code you posted
Write the word [CODE]
Paste a fresh set of code into your post from your IDE
Write the word [/code]
Press post

 

[droose]

update MS Access column from a variable #2

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][COLOR=#008000]' OLEDB Init[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strConnectionString [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/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][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] objConn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/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][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[/SIZE]
[SIZE=2]da = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.OleDb.OleDbDataAdapter(s, objConn)[/SIZE]
[SIZE=2]cb = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.OleDb.OleDbCommandBuilder(da)[/SIZE]
[SIZE=2]DSet = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet([/SIZE][SIZE=2][COLOR=#800000]"MS_Access_DataSet"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]da.Fill(DSet, [/SIZE][SIZE=2][COLOR=#800000]"MS_Access_DataSet"[/COLOR][/SIZE][SIZE=2])[/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[/SIZE]
[SIZE=2]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[/SIZE]
[SIZE=2]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[/SIZE]
[SIZE=2]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][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2]per = System.Math.Round(w / p, 2)[/SIZE]
[SIZE=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][COLOR=#0000ff]If[/COLOR][/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)[/SIZE]
[SIZE=2]cb = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.OleDb.OleDbCommandBuilder(da)[/SIZE]
[SIZE=2]DSet = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet([/SIZE][SIZE=2][COLOR=#800000]"MS_Access_DataSet"[/COLOR][/SIZE][SIZE=2])[/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()[/SIZE]
[SIZE=2]dr([/SIZE][SIZE=2][COLOR=#800000]"Cards"[/COLOR][/SIZE][SIZE=2]) = PDcards[/SIZE]
[SIZE=2]dr([/SIZE][SIZE=2][COLOR=#800000]"HandID"[/COLOR][/SIZE][SIZE=2]) = hid[/SIZE]
[SIZE=2]dr([/SIZE][SIZE=2][COLOR=#800000]"Suited"[/COLOR][/SIZE][SIZE=2]) = suited[/SIZE]
[SIZE=2]dr([/SIZE][SIZE=2][COLOR=#800000]"OddsOW"[/COLOR][/SIZE][SIZE=2]) = OOW[/SIZE]
[SIZE=2]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][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2]per = System.Math.Round(w / p, 2)[/SIZE]
[SIZE=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[/SIZE]
[SIZE=2]dr([/SIZE][SIZE=2][COLOR=#800000]"Won"[/COLOR][/SIZE][SIZE=2]) = 0[/SIZE]
[SIZE=2]dr([/SIZE][SIZE=2][COLOR=#800000]"Percent"[/COLOR][/SIZE][SIZE=2]) = 0[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/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][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][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2]Cleanup()[/SIZE]
[SIZE=2]objConn.Close()[/SIZE]

 

[droose]

I give up. I cannot seem to be able to get the page to do what you ask. I have instead attached a Notepad copy of the code to this reply.

 

[cjard]

odd..

ok. How come youre writing the data access code yourself? you can get the IDE to do this for you in VS2005. I really wouldnt put large amounts of code like that, in the catch block either.. Youre supposed to try code that might give errors, catch and handle those errors, then move on with the next bit..

Can you post your project thus far? I'll help with the data access code, and its a lot easier if I can have at least your database..

 

[droose]

Hi cjard,
Would you beleive I don't know any better. I am new to vb net, 70 years old and just too tired to learn all there is to learn about vn net. This is my last project to close a 44 year career as a computer system consultant. I am attaching the source code for the project, the sharp dll goes in the bin folder. You have no idea how much I appreciate your offer.
Thanks
Dick Roose

PS I am more comfortable writing all the data access code myself as I used to do in vb 6, that is probable the primary reason I did it the way I did.

 

[cjard]

Wow.. that's some long time programming!

I cant see the attachment?

 

[droose]

now let's try something different as it will not let me attach the whole shebangfor some unknown reason.

I have written a little test program which contains the original code from the program for the database part. i have wrapped the old code in a test routine which generates random numbers for all the variables I am working with a present. I expect the number of variables to grow considerably, probably to about 30 to 40.
Again thanks much for your help.
Dick Roose

 

[cjard]

Please also provide the database youre working with, even if it is empty. I need the schema - the code you have does things the looong hard way. I get the feeling I can solve this problem in about 5 minutes using .NET 2.0 methods of data access

 

[droose]

Here is the database.

 

[cjard]

I have completed the changes to your code. While there are many aspects I do not condone (such as copious use of shorts, necessitating extensive use of Convert.ToInt16) it compiles and runs.

There is a logical error in that the procedure does not appear to initialise the Won variable in all cases. The revised code will no doubt encounter a one or more errors relating to Won being null and attampting to use it as a division target when calculating the Percent. As I do not know what this code does, I am unwilling to change the logic, but the provided code should be sufficient to help you assess the .NET 2 way of data access

Additionally, I do not know whether the autonumber handling of the Autop field is correct. I prefer to find primary keys within the data, rather than defer to an accessory autonumber when designing my databases. If you encounter significant problems, feel free to return here

 

[droose]

Hi Jcards,
Thanks again for all your effort. Your code works as advertized except for a few little things:

1) When I try to transport your code into my project the "fillby" instruction for the data adapter gets lost and gives a compile error "not a member of HoldeMagic.ResultsDataSetTableAdapters.ResultsDataTable".

2) When I run your code the database gets overwitten with the new data. The old data is lost. Definately not the way I want it to work. I have made a workaround by coping the "\bin results" database to the folder that holds the vbproj file "Output\" and overwritting file that with the new data. Then the next time the program is run it starts by reading the old data that was copied over. I have no idea how this will work in production.

3) There does not seem to be anyway to add columns to the results database. No matter which database I add the column to, \bin\results, \output\results or both, it gets ignored by your program. How do I add columns?

Any suggestions?

Dick Roose

 

[cjard]

Hi Jcards,
Thanks again for all your effort. Your code works as advertized except for a few little things:

1) When I try to transport your code into my project the "fillby" instruction for the data adapter gets lost and gives a compile error "not a member of HoldeMagic.ResultsDataSetTableAdapters.ResultsDataTable".

Fill(ByXXX) is never a member of a DataTable; it is a member of a TableAdapter. A TableAdapter wraps up the functionality of a data adapter, database connectivity and pre-programmed SQL statements. The statements are written by you, in the designer. A command builder may be used behind the scenes to generate the I U D statements but you can fine tune them. The TableAdapter's purpose thus is to fill datatables from the database you originally conencted it to. Look again at my code, and you will see I call

resultsTA.FillByHandIdAndSuited

I do not call

resultsDT.Fill...


If your project has no tableadapters or your tableadapter has not had this query configured (SELECT * FROM Results WHERE HandID = ? AND suited = ?) then naturally, the member will not be available as a function call

2) When I run your code the database gets overwitten with the new data. The old data is lost. Definately not the way I want it to work. I have made a workaround by coping the "\bin results" database to the folder that holds the vbproj file "Output\" and overwritting file that with the new data. Then the next time the program is run it starts by reading the old data that was copied over. I have no idea how this will work in production.

I dont understand what youre saying here, please be more specific. The main problem I have is understanding "the database gets overwritten with the new data"
Does this mean that the database file is being overwritten? Or does it mean that the wrong records within the table, are being changed?

3) There does not seem to be anyway to add columns to the results database. No matter which database I add the column to, \bin\results, \output\results or both, it gets ignored by your program. How do I add columns?

The paths you list are not complete and the vagueness makes it rather difficult to ascertain to exactly what you are referring to. A project folder typically has 2 or 3 databases depending on how you configure it for building. Assuming we are making a project called myproj in solution folder c:\temp\mysolution you will find the following databases:

1 c:\temp\mysolution\myproj\myDB.mdb
2 c:\temp\mysolution\myproj\bin\debug\myDB.mdb
3 c:\temp\mysolution\myproj\bin\release\myDB.mdb

databases 2 and 3 come into being because the IDE copies database 1 into the given paths depending on whether youre doing a debug or a release build. In VB, pressing play performs a Debug build, whereas choosing Build Solution from the menu does a Release build

Because databases 2 and 3 can be overwritten by the IDE each time you press play (unless you tell the IDE not to perform the copy) they should never be considered your project's reference database. All work such as shifting tables and adding columns must be performed on database 1

This may be what you mean by your earlier complaint about data being overwritten. Click on the equivalent of database1 in your project and see the proeprties window; the Copy mode will be set to copy always. Set it to copy if newer. This way any changes you make to the file will be copied out when next starting the project, not each time. This is a cause of significant frustration to many users of access databases because they didnt read the warning shown by the IDE when they added the file. Please note, that in a production environment this issue is not an issue; it is the IDE that copies the file. Once a project is built, the IDE has nothing to do with it, and the project would use the database without copying it anywhere
​

It is not enough that you simply add the new info to database 1. The dataset is only current as of the time when it is first created from the database. You have two choices:
Delete the datatables and tableadapters from the dataset, refresh the database connection in Server Explorer and dra the table in again - it will be recreated from the specs on the database
-or-
Simply update the dataset much like you would update a database.. i.e. make the same changes in both. If you add a number column to database1.results table, then go to the dataset and do the same thing. Update the queries in the relevant tableadapter etc


I urge you to get your database structure right in the first place and not keep changing it all the time as it can become highly wearisome updating the dataset all the time. Note of course that its not strictly necessary to update the dataset; a datatable and tableadapter pair linked to a database DO NOT HAVE TO reference all the fields of the database. If there are only 5 out of 50 fields that you would ever want to show on screen, update in a random fashion and use as the basis for calculations then those 5 are the only ones that need representing in a datatable.
As an example, suppose you wanted to add an audit trail to all your tables to see who modified the record last and when. You would add the column to your database but you wouldnt need to add it to the datatable.. you would only need to change the UPDATE statement(s) of the table adapters to send a value into the new field

While this may seem a backwards step and thus datasets are hard work, I would remind you that even in classical programming where sql astatements are embedded in code, changing the database structure may require those to be rewritten too

Any suggestions?

Dick Roose

Run with it; programming in this way is likely as much of a departure for you as it was for me, who formerly used prepared statements peppered throughout his code. Now, I grimace at the thought of having to do data access any other way; its just so neat this way