VB.NET Codebehind Code to Update SQL Server 2005 Using SQLDataSource Control?

Hi, I am a newbie in using ASP.NET 2.0 and ADO.NET.  I wrote a hangman game and want to record statistics at the end of each game.  I will create and update records in the database for each authenticated user as well as a record for the Anonymous, unauthenticated user.  After a win or loss has occurred, I want to programmatically use the SQLDataSource control to increment the statistics counters for the appropriate record in the database (note I don't want to show anything or get user input for this function).

I need a VB.NET codebehind example that will show me how I should set up the parameters and update the appropriate record in the database.  Below is my code.  What happens now is that the program chugs along happily (no errors), but the database record does not actually get updated.  I have done many searches on this forum and on the general Internet for programmatic examples of an update sequence of code.  If there is a tutorial for this online or a book, I'm happy to check it out.

Any help will be greatly appreciated.


CODE - Hangman.aspx.vb

1        Protected Sub UpdateStats()
2            Dim playeridString As String
3            Dim
gamesplayedInteger, gameswonInteger, _
4                easygamesplayedInteger, easygameswonInteger, _
5                mediumgamesplayedInteger, mediumgameswonInteger, _
6                hardgamesplayedInteger, hardgameswonInteger As Int32
8            ' determine whether player is named or anonymous
9            If User.Identity.IsAuthenticated Then
10               Profile.Item("hangmanplayeridString") = User.Identity.Name
11           Else
12               Profile.Item("hangmanplayeridString") = "Anonymous"
13           End If
15           playeridString = Profile.Item("hangmanplayeridString")
17           ' look up record in stats database
18           Dim hangmanstatsDataView As System.Data.DataView = CType(statsSqlDataSource.Select(DataSourceSelectArguments.Empty), System.Data.DataView)
20           gamesplayedInteger = 0
21           gameswonInteger = 0
22           easygamesplayedInteger = 0
23           easygameswonInteger = 0
24           mediumgamesplayedInteger = 0
25           mediumgameswonInteger = 0
26           hardgamesplayedInteger = 0
27           hardgameswonInteger = 0
29           If hangmanstatsDataView.Table.Rows.Count = 0 Then
31               '   then create record with 0 values
32               statsSqlDataSource.InsertParameters.Clear() ' don't really know what Clear does
33               statsSqlDataSource.InsertParameters("playerid").DefaultValue = playeridString
34               statsSqlDataSource.InsertParameters("GamesPlayed").DefaultValue = gamesplayedInteger
35               statsSqlDataSource.InsertParameters("GamesWon").DefaultValue = gameswonInteger
36               statsSqlDataSource.InsertParameters("EasyGamesPlayed").DefaultValue = easygamesplayedInteger
37               statsSqlDataSource.InsertParameters("EasyGamesWon").DefaultValue = easygameswonInteger
38               statsSqlDataSource.InsertParameters("MediumGamesPlayed").DefaultValue = mediumgamesplayedInteger
39               statsSqlDataSource.InsertParameters("MediumGamesWon").DefaultValue = mediumgameswonInteger
40               statsSqlDataSource.InsertParameters("HardGamesPlayed").DefaultValue = hardgamesplayedInteger
41               statsSqlDataSource.InsertParameters("HardGamesWon").DefaultValue = hardgameswonInteger
43               statsSqlDataSource.Insert()
44           End If
46           ' reread the record to get current values
47           hangmanstatsDataView = CType(statsSqlDataSource.Select(DataSourceSelectArguments.Empty), System.Data.DataView)
48           Dim hangmanstatsDataRow As System.Data.DataRow = hangmanstatsDataView.Table.Rows.Item(0)
50           ' set temp variables to database values
51           gamesplayedInteger = hangmanstatsDataRow("GamesPlayed")
52           gameswonInteger = hangmanstatsDataRow("GamesWon")
53           easygamesplayedInteger = hangmanstatsDataRow("EasyGamesPlayed")
54           easygameswonInteger = hangmanstatsDataRow("EasyGamesWon")
55           mediumgamesplayedInteger = hangmanstatsDataRow("MediumGamesPlayed")
56           mediumgameswonInteger = hangmanstatsDataRow("MediumGamesWon")
57           hardgamesplayedInteger = hangmanstatsDataRow("HardGamesPlayed")
58           hardgameswonInteger = hangmanstatsDataRow("HardGamesWon")
60           ' update stats record
61           'statsSqlDataSource.UpdateParameters.Clear()
62           'statsSqlDataSource.UpdateParameters("playerid").DefaultValue = playeridString
64           If Profile.Item("hangmanwinorloseString") = "win" Then
66               statsSqlDataSource.UpdateParameters("GamesPlayed").DefaultValue = gamesplayedInteger + 1
67               statsSqlDataSource.UpdateParameters("GamesWon").DefaultValue = gameswonInteger + 1
68               Select Case Profile.Item("hangmandifficultyInteger")
69                   Case 1
70                       statsSqlDataSource.UpdateParameters("EasyGamesPlayed").DefaultValue = easygamesplayedInteger + 1
71                       statsSqlDataSource.UpdateParameters("EasyGamesWon").DefaultValue = easygameswonInteger + 1
72                   Case 2
73                       statsSqlDataSource.UpdateParameters("MediumGamesPlayed").DefaultValue = mediumgamesplayedInteger + 1
74                       statsSqlDataSource.UpdateParameters("MediumGamesWon").DefaultValue = mediumgameswonInteger + 1
75                   Case 3
76                       statsSqlDataSource.UpdateParameters("HardGamesPlayed").DefaultValue = hardgamesplayedInteger + 1
77                       statsSqlDataSource.UpdateParameters("HardGamesWon").DefaultValue = hardgameswonInteger + 1
78               End Select
81           ElseIf
Profile.Item("hangmanwinorloseString") = "lose" Then
83               statsSqlDataSource.UpdateParameters("GamesPlayed").DefaultValue = gamesplayedInteger + 1
84               Select Case Profile.Item("hangmandifficultyInteger")
85                   Case 1
86                       statsSqlDataSource.UpdateParameters("EasyGamesPlayed").DefaultValue = easygamesplayedInteger + 1
87                   Case 2
88                       statsSqlDataSource.UpdateParameters("MediumGamesPlayed").DefaultValue = mediumgamesplayedInteger + 1
89                   Case 3
90                       statsSqlDataSource.UpdateParameters("HardGamesPlayed").DefaultValue = hardgamesplayedInteger + 1
91               End Select
92           End If

94           statsSqlDataSource.Update()
96       End Sub

CODE - Hangman.aspx

1            <asp:SqlDataSource ID="statsSqlDataSource" runat="server" ConflictDetection="overwritechanges"
2                ConnectionString="<%$ ConnectionStrings:lambanConnectionString %>" DeleteCommand="DELETE FROM [Hangman_Stats] WHERE [PlayerID] = @original_PlayerID AND [GamesPlayed] = @original_GamesPlayed AND [GamesWon] = @original_GamesWon AND [EasyGamesPlayed] = @original_EasyGamesPlayed AND [EasyGamesWon] = @original_EasyGamesWon AND [MediumGamesPlayed] = @original_MediumGamesPlayed AND [MediumGamesWon] = @original_MediumGamesWon AND [HardGamesPlayed] = @original_HardGamesPlayed AND [HardGamesWon] = @original_HardGamesWon"
3                InsertCommand="INSERT INTO [Hangman_Stats] ([PlayerID], [GamesPlayed], [GamesWon], [EasyGamesPlayed], [EasyGamesWon], [MediumGamesPlayed], [MediumGamesWon], [HardGamesPlayed], [HardGamesWon]) VALUES (@PlayerID, @GamesPlayed, @GamesWon, @EasyGamesPlayed, @EasyGamesWon, @MediumGamesPlayed, @MediumGamesWon, @HardGamesPlayed, @HardGamesWon)"
4                OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT PlayerID, GamesPlayed, GamesWon, EasyGamesPlayed, EasyGamesWon, MediumGamesPlayed, MediumGamesWon, HardGamesPlayed, HardGamesWon FROM Hangman_Stats WHERE (PlayerID = @playerid)"
5                UpdateCommand="UPDATE [Hangman_Stats] SET [GamesPlayed] = @GamesPlayed, [GamesWon] = @GamesWon, [EasyGamesPlayed] = @EasyGamesPlayed, [EasyGamesWon] = @EasyGamesWon, [MediumGamesPlayed] = @MediumGamesPlayed, [MediumGamesWon] = @MediumGamesWon, [HardGamesPlayed] = @HardGamesPlayed, [HardGamesWon] = @HardGamesWon WHERE [PlayerID] = @original_PlayerID AND [GamesPlayed] = @original_GamesPlayed AND [GamesWon] = @original_GamesWon AND [EasyGamesPlayed] = @original_EasyGamesPlayed AND [EasyGamesWon] = @original_EasyGamesWon AND [MediumGamesPlayed] = @original_MediumGamesPlayed AND [MediumGamesWon] = @original_MediumGamesWon AND [HardGamesPlayed] = @original_HardGamesPlayed AND [HardGamesWon] = @original_HardGamesWon">
6                <DeleteParameters>
7                    <asp:Parameter Name="original_PlayerID" Type="String" />
8                    <asp:Parameter Name="original_GamesPlayed" Type="Int32" />
9                    <asp:Parameter Name="original_GamesWon" Type="Int32" />
10                   <asp:Parameter Name="original_EasyGamesPlayed" Type="Int32" />
11                   <asp:Parameter Name="original_EasyGamesWon" Type="Int32" />
12                   <asp:Parameter Name="original_MediumGamesPlayed" Type="Int32" />
13                   <asp:Parameter Name="original_MediumGamesWon" Type="Int32" />
14                   <asp:Parameter Name="original_HardGamesPlayed" Type="Int32" />
15                   <asp:Parameter Name="original_HardGamesWon" Type="Int32" />
16               </DeleteParameters>
17               <UpdateParameters>
18                   <asp:Parameter Name="GamesPlayed" Type="Int32" />
19                   <asp:Parameter Name="GamesWon" Type="Int32" />
20                   <asp:Parameter Name="EasyGamesPlayed" Type="Int32" />
21                   <asp:Parameter Name="EasyGamesWon" Type="Int32" />
22                   <asp:Parameter Name="MediumGamesPlayed" Type="Int32" />
23                   <asp:Parameter Name="MediumGamesWon" Type="Int32" />
24                   <asp:Parameter Name="HardGamesPlayed" Type="Int32" />
25                   <asp:Parameter Name="HardGamesWon" Type="Int32" />
26                   <asp:Parameter Name="original_PlayerID" Type="String" />
27                   <asp:Parameter Name="original_GamesPlayed" Type="Int32" />
28                   <asp:Parameter Name="original_GamesWon" Type="Int32" />
29                   <asp:Parameter Name="original_EasyGamesPlayed" Type="Int32" />
30                   <asp:Parameter Name="original_EasyGamesWon" Type="Int32" />
31                   <asp:Parameter Name="original_MediumGamesPlayed" Type="Int32" />
32                   <asp:Parameter Name="original_MediumGamesWon" Type="Int32" />
33                   <asp:Parameter Name="original_HardGamesPlayed" Type="Int32" />
34                   <asp:Parameter Name="original_HardGamesWon" Type="Int32" />
35               </UpdateParameters>
36               <InsertParameters>
37                   <asp:Parameter Name="PlayerID" Type="String" />
38                   <asp:Parameter Name="GamesPlayed" Type="Int32" />
39                   <asp:Parameter Name="GamesWon" Type="Int32" />
40                   <asp:Parameter Name="EasyGamesPlayed" Type="Int32" />
41                   <asp:Parameter Name="EasyGamesWon" Type="Int32" />
42                   <asp:Parameter Name="MediumGamesPlayed" Type="Int32" />
43                   <asp:Parameter Name="MediumGamesWon" Type="Int32" />
44                   <asp:Parameter Name="HardGamesPlayed" Type="Int32" />
45                   <asp:Parameter Name="HardGamesWon" Type="Int32" />
46               </InsertParameters>
47               <SelectParameters>
48                   <asp:ProfileParameter Name="playerid" PropertyName="hangmanplayeridString" />
49               </SelectParameters>
50           </asp:SqlDataSource>
7/21/2007 2:11:56 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 49

Hi lambanlaa,

I guess I've understood what you want to impliment in your program. As to the data access issue

What happens now is that the program chugs along happily (no errors), but the database record does not actually get updated
There are servera ways you can follow:

1.Check if you have written the correct connection string and the connection string has connected to your database? An easy way to identify why your programm doesnot work is to comment your existed code and write some "sample"/test code, which will perform a data access to your database(if it does effect your database, then something wrong in your code, if not, you must have missed something in your connection string,etc). Anyway, I just don't think it's too much difficult to solve this issue.

2.Set a breakpoint on SqlDataSource.update() to check for the 2 local variables:  (i) updatestatement--make sure your update statements is executable (you can try it using query analyzer directly). 
                                                                                                                     (ii) Profile.Item("hangmanwinorloseString")--check if the value has been set either "win" or "lose". if not,your statistic won't be saved.

BTW, as to the resources on this area, first i would suggest you reading the msdn docments if you like, http://msdn2.microsoft.com/en-us/library/e80y5yhx(vs.80).aspx

and if you need further, check the book <essential ado.net > by Bob Beauchemin. Hope my suggestion helps



Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
7/26/2007 5:54:19 AM

Thank you, Bo, this gives me a few ideas for testing.


7/28/2007 7:21:23 PM

Similar Artilces:

Attach SQL DB to SQL Server 2005 using VB.Net
Is there any way to attach a SQL DB to SQL Server 2005 using VB.Net?  If so can you also set security? Can you rephrase your question? Attach SQL DB from which version of SQL server ? Look up books online for sp_attachdb and sp_detachdb.***********************Dinakar NethiLife is short. Enjoy it.*********************** I have a DB that was created in SQL Server 2005.  I have a website that displays data from systems all over the world.  Each system sends it's data to it's own DB.  The customer can then go to a website and see the data at real time.  What ...

SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)?  Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...

Sql Server Script File(.sql) Execution by Vb.net Code
I have a problem. I m working on "light weight sql server"  project.   and i want to execute .sql file through vb.net code with the help of sqldmo library and sqlns namespace. but i donot know any method to directly execute the .sql file. i am successfully making the full script of select database of sql server.   please help me.... thanks.... Hi, You can use SQL-DMO object in your .net projects by referencing Sqldmo.dll  file which is in  C:\Program Files\Microsoft SQL Server\80\Tools\Binn  folder is default installation parameters are used. I...

Using VB.NET to export data from SQL Server 2005 to Excel
Hi There, I just want to know how to export data from SQL Server 2005 to an excel spread sheet, using VB.NET?ThanksSJB  Everytime I should export data from SQL to Excel with code, I simply create a ; seperated file (*.csv). So if you want to do the same you can simply use ADO.Net to query your database and get a result set from your tables. Walk trough the rows and get its value and create a textfile with the System.IO namespace. The file can look like: column1;column2;column3 value1;value2;value3 value1;value2;value3 The first row can if you want have the name of the column tha...

uploading a image in to the sql server 2005 database using vb.net 3.5
i am currently developing a web project using asp.net.i need to upload a image file to the database using asp.net.i am using linq to interactwith the database. can some one help me in writing these codes. {i need to know how to get the image size and i need the image content type to upload image) please let me know what are the codes i should use to get the image size and content type. i have used the following code but no use                           &n...

shopping cart in VB.NET, uses SQL server express edition 2005, anyone?
Hello i want to create a shopping cart which uses SQL server express 2005 as the database for my school project...Any good tutorials for this? Perhaps you can delve in to the source code of some of the open source offerings out there? Maybe DotShoppingCart or dashCommerce, both of which you can find links to on the Starter Kits and Community Projects page.Gavin HarrissPortfolio: www.gavinharriss.com Thank you very much for the suggestion! :)  my friend gave me a great shopping cart code which uses VB.net and SQL server express edition! Once again, thanks :)  ...

Using membership and roles in VB.NET 2005 and SQL 2005
Hi All, I am newbe and dont realy understand how to use roles and implement ASPNETDB. Here is the scenario: I have created a DB connection to my local network SQL 2005 development server and created the role database . When I add roles and users using Web Administration tool, the new database is created in a folder App_Data and all data is saved there instead on the actual SQL server.  The database connection string in the configuration file is correct as I am connected to the server but data is writen in a App_Data folder database instead on the server database. ...

This version of Microsoft Sql Server Management Studio can only be used to connect to Sql Server 2000 and Sql Server 2005 servers
When I connect SQL Server 2005 to a remote database i get the error: "This version of Microsoft Sql Server Management Studio can only be used to connect to Sql Server 2000 and Sql Server 2005 servers". Can I confirm it is because: the remote db is of SQL Server 2008? There is a soln mentioned in http://forums.asp.net/t/1324047.aspx Did anybody try this (installing 2008 express: http://www.microsoft.com/express/sql/download/)? I am just checking again, whether it will disturb my existing SQL Server installation, in any means...? Though it is mentioned that it will g...

Using VS.net 2005 and SQL Server 2005 for real projects?
We are working on the design of our new web site, which we initially targeted for ASP.NET 1.1.  We also planned on using SQL Server Reporting Services from SQL Server 2000.  However, I have been reading about ASP.NET 2.0, as well as vs.net 2005 and SQL Server 2005, including the fact that they are planned for release in Novemeber.  My questions is - should we consider using the beta products for our project, with the thought that they are relatively stable and production versions will be out in a few months?  I do see a number of attractive features in these new releases ...

SQL Server 2005 images with VB.NET 2005.. Help Please...
Hello to all, I really Hope you can help me. I need to build an App that has the feature to upload images to a database, as well as retrieve them. I am not an expert in ASP.NET.. here is what I got: I have a DB with a table named "Slide", in it there is a column named "image" it is image SQL Data type..I already have the GUI built, a text box for the file name, and a button that will browse the image.In the VB code i have: Imports Data.System.SQLClient' code... 'I know i have to "Split apart" the image into binary data.. how can I do ...

Sending SMS using vb.net or C#.net using vb.net or c#.net
Hi  My requirement is I hav one csv file with these fields id,mobilenum,messgae,status.intiallu staus is 0. once i read the all fileds and take that mobile number.using tat mobile number  i need to send sms .after sending sms i shuld change status as 1 How to send sms thru coding (please dont provide any links.if it is provide also please give working links becox i checked codeproject .i didnt get any nice link. and also provide the how to update the status field im csv file   Thank ssandhya   To send SMS, you need some third party SMS providers. if you consul...

I cannot stop Visual Studio.NET 2005 from looking for SQL Server 2005 instead of SQL 2000
I would like to know how to set SQL2000 as the default database server when running any webapplication. I have installed Visual Studio.NET and SQL Server 2000 locally in my laptop.  But anytime I want to run a webapplication I am getting the following error:System.Data.SqlClient.SqlException was unhandled by user code  Class=20  ErrorCode=-2146232060  LineNumber=0  Message="An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server doe...

VB.Net and SQL Server 2005
We are having a application in which VB 6.0 communicates with SQL Server 2000 .... This communication is very slow and becoming the bottle neck in the system ... If we move from VB 6.0 to VB.Net and SQL 2000 to SQL 2005 , would there be any performance gain , if yes how much gain we can expect... We need to evaulate the gain before writing the code base in VB.Net Thanks in Advance Regards Bobin Sondhi        Both technologies .NET and SQL 2005 are the successors of the previously stated technologies, so I think it is safe to ASSume that there wo...

Using Sql Server with VB.net
I am trying to create an asp.net application that will delete an item from inventory when the itemid is entered into a textbox and a button is pushed I only wnat it to delete the item if the QOH is 0 and then return the corresponding item name, and return an error if the item is still in stock. Could anyone help me on the stored procedure. The only table involved is the inventory table. "DELETE FROM <TableName> WHERE ItemID = @ID AND QOH=0" if records affected > 0, woop woop. else, errormessage="Who you talkin' bout willis?"; like my psuedo-code?Ka...

Web resources about - VB.NET Codebehind Code to Update SQL Server 2005 Using SQLDataSource Control? - asp.net.sql-datasource

Top Ranked Articles
Top Ranked Articles - Free source code and tutorials for Software developers and Architects.; Updated: 23 Feb 2013

Using The LINQDataSource
-*+There is a new datasource control available in Visual Studio that works in much the same way as the SQLDataSource and the ObjectDataSource. ...

Gabriel Mongeon
Archive - Gabriel Mongeon

M ore d otnet .NET Interview Questions, ASP.NET, C#, VB.NET, XML, SQL Check Out http://www.dotnetUncle.com √ .NET NOTES OOPS in .NET .NET ...

Architectural Overview of the Windows Presentation Foundation Beta 1 Release
Introduction Windows Presentation Foundation Beta 1 Windows Integration New Features WinFX SDK Exploring the New Features Introducing "Express" ...

About Mikesdotnetting
I started this site as somewhere to store answers to bits and pieces that I see asked frequently in the forums at www.asp.net . Rather than type ...

Using MiniProfiler with SqlDataSource ASP.NET WebForms Control
public class ProfiledSqlDataSource : SqlDataSource{ protected override DbProviderFactory GetDbProviderFactory() { // get the "base" DbProviderFactory ...

ASP.NET Pivot Table - RadPivotGrid for ASP.NET AJAX
... reports. RadPivotGrid allows easy customization of all data reports. Try now! Use any declarative data source control (AccessDataSource, SqlDataSource, ...

Sign on
Sign On • Join • Forums Home User Name * Password * Remember me next time ( I forgot my password ) SUBMIT YOUR WTF Content Random Article All ...

ComboBox Sample
ComboBox is an ASP.NET AJAX control that, like the AutoCompleteExtender , combines the flexibility of a TextBox with a list of options that users ...

Resources last updated: 1/15/2016 7:36:28 AM