We help IT Professionals succeed at work.

ASP.NET VB Update SQL table in load complete in code

963 Views
Last Modified: 2013-12-20
Hello Everyone

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)">
        <SelectParameters>
            <asp:querystringparameter DefaultValue="37" Name="ID"
                QueryStringField="messageid" Type="Int32" />
        </SelectParameters>
    </asp:SqlDataSource>

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")


        MyCommand.Connection.Open()

        Try
            MyCommand.ExecuteNonQuery()
            '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"
            Else
                'Message.InnerHtml = "ERROR: Could not add record, please ensure the fields are correctly filled out"
            End If
            'Message.Style("color") = "red"

        End Try

        MyCommand.Connection.Close()
    End If
    End Sub

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.
Comment
Watch Question

Using code-behind that's the incorrect way of initiating a connection.

Give this a try:

Dim sqconn as new SqlConnection("[your connection string name]")

Dim MyCommand As SqlCommand

MyCommand = New SqlCommand(UpdateCmd, sqconn)

You can also define the Update Command dynamically from the Source code:

    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConnectionString="<%$ ConnectionStrings:frogs4ConnectionString %>"
       
        SelectCommand="SELECT [id], [Title], [MessageText], [Contributor], [MessageDate], [MessageTime],[ratingtotal], [ratedby]  FROM [Messageview] WHERE ([ID] = @ID)">

UpdateCommand ="UPDATE [messages] set [messageviews]=@messageviews+1 where (ID=@ID)"

        <SelectParameters>
       <asp:querystringparameter DefaultValue="37" Name="ID"
                QueryStringField="messageid" Type="Int32" />
        </SelectParameters>
    </asp:SqlDataSource>
I think the source code one is going to throw an error. Something's missing. Anyway, try the code-behind code.

Hope it helps.

Commented:
Hi,

Am i right in saying you are loading a page with a MessageID in the Query String and you want to update the record and then display the record.

I'm fairly new to ASP.net also and using VS2005 with VB. I spent a huge amount of time in the beginning trying to work out the best way to talk to SQL Server databases. I found out that there seems to be 3 ways to talk:
1) Single Page Datasource for direct connection to the database, very visual queries etc.
2) Datasets
3) Object datasource

No 3 is the only one that I found to satisfy all of my needs. This basically involves writing a series of classes to insert, update, delete and select the data rows.

I came to the conclusion that this was very tedious as a simple table structure required between 500 and 1000 lines of code. Doing this by hand was very time consuming. I have tried various tools for doing the same thing, but not really happy with them. I therefore wrote a program to generate the code given the table strucure of my choice. This seems to work well and I use it in all projects even really small ones.

If you send me your email address I will give you a copy of it if you like. I don't really know what to do with it to be honest. I've only really used it for my own use.

The advantage of the code is you can use it really easily with the form controls with parameters if you like or you can use the same class in vb code.

3 classes are created - Database Class (DAL), Record Layout Class and the Business Layer Logic (BLL). This immediately gives you n-tier architechture which is good practice.

Hope this helps

Nick

Author

Commented:
Thanks digitalZo

One last question to complete this section of my project. How do I use the connectionstring in my web config?

      <connectionStrings>
 
    <add name="frogs4ConnectionString" connectionString="Data Source=***.***.***.***;Initial Catalog=frogs4;Persist Security Info=True;User ID=***;Password=******"
 providerName="System.Data.SqlClient" />
 </connectionStrings>

Commented:
Private conString As String = ConfigurationManager.ConnectionStrings("WebGISDB").ConnectionString
The VB version:

Dim connStr As String = ConfigurationManager.ConnectionStrings("frogs4ConnectionString").ConnectionString
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.