[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-10-12
7
Medium Priority
?
946 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.
0
Comment
Question by:dmat1
  • 4
  • 2
7 Comments
 
LVL 10

Expert Comment

by:digitalZo
ID: 20063871
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>
0
 
LVL 10

Expert Comment

by:digitalZo
ID: 20063875
I think the source code one is going to throw an error. Something's missing. Anyway, try the code-behind code.

Hope it helps.
0
 
LVL 4

Expert Comment

by:NickWalt
ID: 20063984
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dmat1
ID: 20070420
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>
0
 
LVL 4

Expert Comment

by:NickWalt
ID: 20070672
Private conString As String = ConfigurationManager.ConnectionStrings("WebGISDB").ConnectionString
0
 
LVL 10

Expert Comment

by:digitalZo
ID: 20070759
The VB version:

Dim connStr As String = ConfigurationManager.ConnectionStrings("frogs4ConnectionString").ConnectionString
0
 
LVL 10

Accepted Solution

by:
digitalZo earned 2000 total points
ID: 20070762
Then reference it to SQLConnection:

Dim connStr As String = ConfigurationManager.ConnectionStrings("frogs4ConnectionString").ConnectionString

Dim sqconn as new SqlConnection(connStr)

Dim MyCommand As SqlCommand

MyCommand = New SqlCommand(UpdateCmd, sqconn)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month18 days, 6 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question