Visual Basic .NET Forums  
Click here to advertise with us

Go Back   Visual Basic .NET Forums > Database > Database General Discussion
update MS Access column from a variable update MS Access column from a variable
Register FAQ Social Groups Calendar Search Today's Posts Mark Forums Read

Database General Discussion General discussion on database related topics

VB.NET Forums Newsletter Signup:
Email address:


Reply
Page 1 of 3 1 23
 
LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old 09-08-2006, 8:12 PM
VB.NET Forum Newbie
.NET Framework: .NET 2.0 (VS 2005)
  
Join Date: Sep 2006
Posts: 10
Reputation: 45
droose is on a distinguished programming path ahead
Question update MS Access column from a variable
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

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

Last edited by droose; 09-20-2006 at 10:30 AM. Reason: Code Box
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #2 (permalink)  
Old 09-11-2006, 4:18 AM
cjard's Avatar
VB.NET Forum All-Mighty
.NET Framework: .NET 2.0 (VS 2005)
  
Join Date: Apr 2006
Age: 65
Posts: 6,442
Reputation: 807
cjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond repute
Default
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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #3 (permalink)  
Old 09-19-2006, 3:14 PM
VB.NET Forum Newbie
.NET Framework: .NET 2.0 (VS 2005)
  
Join Date: Sep 2006
Posts: 10
Reputation: 45
droose is on a distinguished programming path ahead
Default 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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #4 (permalink)  
Old 09-20-2006, 6:58 AM
cjard's Avatar
VB.NET Forum All-Mighty
.NET Framework: .NET 2.0 (VS 2005)
  
Join Date: Apr 2006
Age: 65
Posts: 6,442
Reputation: 807
cjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond repute
Default
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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #5 (permalink)  
Old 09-20-2006, 10:42 AM
VB.NET Forum Newbie
.NET Framework: .NET 2.0 (VS 2005)
  
Join Date: Sep 2006
Posts: 10
Reputation: 45
droose is on a distinguished programming path ahead
Default 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

Code:
' OLEDB Init
Dim strConnectionString AsString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\ThePokerEdgeNet\Result.mdb"
Dim objConn AsNew System.Data.OleDb.OleDbConnection(strConnectionString)
Dim cb As System.Data.OleDb.OleDbCommandBuilder
'Connect to provider (connectionstring above)
objConn.Open()
' Data Source > Data Adapter > Data Set (create DataSet)
Try
s = "SELECT * FROM Results WHERE HandID = " & hid & " and Suited = " & suited
da = New System.Data.OleDb.OleDbDataAdapter(s, objConn)
cb = New System.Data.OleDb.OleDbCommandBuilder(da)
DSet = New DataSet("MS_Access_DataSet")
da.Fill(DSet, "MS_Access_DataSet")
d = DSet.Tables("MS_Access_Dataset").Rows(g_lngCurrentRow).Item("Dealt") + 1
DSet.Tables("MS_Access_Dataset").Rows(g_lngCurrentRow).Item("Dealt") = d
If myBet <> 0 And WnLs <> 0 Then
p = DSet.Tables("MS_Access_Dataset").Rows(g_lngCurrentRow).Item("Played") + 1
DSet.Tables("MS_Access_Dataset").Rows(g_lngCurrentRow).Item("Played") = p
If WnLs > 0 Then
w = DSet.Tables("MS_Access_Dataset").Rows(g_lngCurrentRow).Item("Won") + 1
DSet.Tables("MS_Access_Dataset").Rows(g_lngCurrentRow).Item("Won") = w
EndIf
per = System.Math.Round(w / p, 2)
DSet.Tables("MS_Access_Dataset").Rows(g_lngCurrentRow).Item("Percent") = per
EndIf
' Update the dataset
da.Update(DSet, "MS_Access_DataSet")
Catch
s = "SELECT * FROM Results"
da = New System.Data.OleDb.OleDbDataAdapter(s, objConn)
cb = New System.Data.OleDb.OleDbCommandBuilder(da)
DSet = New DataSet("MS_Access_DataSet")
da.Fill(DSet, "MS_Access_DataSet")
Dim dr As System.Data.DataRow = DSet.Tables("MS_Access_Dataset").NewRow()
dr("Cards") = PDcards
dr("HandID") = hid
dr("Suited") = suited
dr("OddsOW") = OOW
dr("Dealt") = 1
If myBet <> 0 And WnLs <> 0 Then
dr("Played") = i
If WnLs > 0 Then
dr("Won") = 1
EndIf
per = System.Math.Round(w / p, 2)
dr("Percent") = per
Else
dr("Played") = 0
dr("Won") = 0
dr("Percent") = 0
EndIf
If istournament Then
dr("CWinLost") = WnLs
ElseIf PlayMoney Then
dr("PWinLost") = WnLs
Else
dr("$WinLost") = WnLs
EndIf
DSet.Tables("MS_Access_DataSet").Rows.Add(dr)
'da.Update(DSet, "MS_Access_DataSet")
EndTry
Cleanup()
objConn.Close()
Attached Files
File Type: txt AccessCode.txt (2.7 KB, 4 views)
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #6 (permalink)  
Old 09-20-2006, 7:04 PM
VB.NET Forum Newbie
.NET Framework: .NET 2.0 (VS 2005)
  
Join Date: Sep 2006
Posts: 10
Reputation: 45
droose is on a distinguished programming path ahead
Default update MS Access column from a variable
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.
Attached Files
File Type: txt AccessCode.txt (2.7 KB, 4 views)
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #7 (permalink)  
Old 09-21-2006, 7:40 AM
cjard's Avatar
VB.NET Forum All-Mighty
.NET Framework: .NET 2.0 (VS 2005)
  
Join Date: Apr 2006
Age: 65
Posts: 6,442
Reputation: 807
cjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond repute
Default
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..
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #8 (permalink)  
Old 09-22-2006, 5:36 PM
VB.NET Forum Newbie
.NET Framework: .NET 2.0 (VS 2005)
  
Join Date: Sep 2006
Posts: 10
Reputation: 45
droose is on a distinguished programming path ahead
Default update MS Access column from a variable
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.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #9 (permalink)  
Old 09-22-2006, 5:50 PM
cjard's Avatar
VB.NET Forum All-Mighty
.NET Framework: .NET 2.0 (VS 2005)
  
Join Date: Apr 2006
Age: 65
Posts: 6,442
Reputation: 807
cjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond reputecjard has a reputation beyond repute
Default
Wow.. that's some long time programming!

I cant see the attachment?
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #10 (permalink)  
Old 09-22-2006, 10:14 PM
VB.NET Forum Newbie
.NET Framework: .NET 2.0 (VS 2005)
  
Join Date: Sep 2006
Posts: 10
Reputation: 45
droose is on a distinguished programming path ahead
Default update MS Access column from a variable
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
Attached Files
File Type: zip Form1.zip (50.0 KB, 4 views)
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Reply
Page 1 of 3 1 23

Bookmarks

« Previous Thread | Next Thread »

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On




All times are GMT -4. The time now is 3:38 PM.
Visual Basic .NET Forums - Privacy Statement - Top

Powered by vBulletin® Version 3.8.5
Copyright ©2000 - 2010, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.3.2
© 2010 NC Software, Inc.


For advertising opportunities click here.