ASP.NET VB Update SQL table in load complete in code
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(SQLDataSourc
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:frogs4Co
SelectCommand="SELECT [id], [Title], [MessageText], [Contributor], [MessageDate], [MessageTime],[ratingtotal
<SelectParameters>
<asp:querystringparameter DefaultValue="37" Name="ID"
QueryStringField="messagei
</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
Protected Sub viewmessage_LoadComplete(B
If Not IsPostBack Then
Dim MyCommand As SqlCommand
Dim UpdateCmd As String = "Update messages set messageviews=messageviews+
MyCommand = New SqlCommand(UpdateCmd, SqlDataSource1)
MyCommand.Parameters.Add(N
MyCommand.Parameters("@Id"
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.
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(SQLDataSourc
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:frogs4Co
SelectCommand="SELECT [id], [Title], [MessageText], [Contributor], [MessageDate], [MessageTime],[ratingtotal
<SelectParameters>
<asp:querystringparameter DefaultValue="37" Name="ID"
QueryStringField="messagei
</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
Protected Sub viewmessage_LoadComplete(B
If Not IsPostBack Then
Dim MyCommand As SqlCommand
Dim UpdateCmd As String = "Update messages set messageviews=messageviews+
MyCommand = New SqlCommand(UpdateCmd, SqlDataSource1)
MyCommand.Parameters.Add(N
MyCommand.Parameters("@Id"
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.
I think the source code one is going to throw an error. Something's missing. Anyway, try the code-behind code.
Hope it helps.
Hope it helps.
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
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
ASKER
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="frogs4ConnectionStri
providerName="System.Data.
</connectionStrings>
One last question to complete this section of my project. How do I use the connectionstring in my web config?
<connectionStrings>
<add name="frogs4ConnectionStri
providerName="System.Data.
</connectionStrings>
Private conString As String = ConfigurationManager.Conne
The VB version:
Dim connStr As String = ConfigurationManager.Conne
Dim connStr As String = ConfigurationManager.Conne
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:frogs4Co
SelectCommand="SELECT [id], [Title], [MessageText], [Contributor], [MessageDate], [MessageTime],[ratingtotal
UpdateCommand ="UPDATE [messages] set [messageviews]=@messagevie
<SelectParameters>
<asp:querystringparameter DefaultValue="37" Name="ID"
QueryStringField="messagei
</SelectParameters>
</asp:SqlDataSource>