ASP.NET VB Update SQL table in load complete in code
Posted on 2007-10-12
I a, just beginning to learn ASP.NET. I have been coding ASP by hand for close to 10 years now and know it quite well but when it comes to .NET I am having a very hard time figuring out something that seems so simple to me.
I am building a forum. I have the page that list categories and then lists sub categories. And then lists the number of views that a message has received. I have it so that you can click on the message and the message comes up in a different aspx page. I have this working as well. On the page I have a "rate it" option. I have not implemented this but I am sure it will involve the ispostback property. If it isnt a post back though I want to be able to increment the view count on the message.
On my form I have DB Fromview that is listing everything I want. I also have an SQLDataSource(SQLDataSource1). This is all within a Master Page. I have this behaving properly as well.
What I want to do is add the loadcomplete function that will update my message table based on the messae id that is passed in the query string with the number of message views. I am trying to do this in the code behind page.
Here is the code in my aspx page for the datasource:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:frogs4ConnectionString %>"
SelectCommand="SELECT [id], [Title], [MessageText], [Contributor], [MessageDate], [MessageTime],[ratingtotal], [ratedby] FROM [Messageview] WHERE ([ID] = @ID)">
<asp:querystringparameter DefaultValue="37" Name="ID"
QueryStringField="messageid" Type="Int32" />
My Question is how do I update the table. What do I import. What is inherited? Do I need a new Datasource / dataconnection or can I use the existing one? Everything I have found online so far seems to be circling around what I am looking for or leaving out something.
The code below seems close but I get an error on SqlDataSource1(blue underline)..."Value of type 'system.web.ui.webcontrols.sqldatasource' cannot be converted to 'system.data.sqlclient.sqlconnection'".
Protected Sub viewmessage_LoadComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadComplete
If Not IsPostBack Then
Dim MyCommand As SqlCommand
Dim UpdateCmd As String = "Update messages set messageviews=messageviews+1 where (ID=@ID)"
MyCommand = New SqlCommand(UpdateCmd, SqlDataSource1)
MyCommand.Parameters.Add(New SqlParameter("@Id", SqlDbType.NVarChar, 11))
MyCommand.Parameters("@Id").Value = Request.QueryString("MessageID")
'Message.InnerHtml = "<b>Record Added</b><br>" & InsertCmd.ToString()
Catch Exp As SqlException
If Exp.Number = 2627 Then
'Message.InnerHtml = "ERROR: A record already exists with the same primary key"
'Message.InnerHtml = "ERROR: Could not add record, please ensure the fields are correctly filled out"
'Message.Style("color") = "red"
One last thing I should mention is I am on an XP Pro system I have a VPN connection to my SQL serverand I am developing using Visual Web Developer 2008 express edition.