Problem with updating only one row in a SQL 2005 database

hi,

I have a problem with updating a row in my database. I don't know how to update and i need help.

I have a form where the users select a category and a sub category to enter a message.

I have two dropdown lists, one is for selecting the category and the other is for the sub-category.

In my database for the categories and the sub-categories, i have a variable (Quantity) where i keep the quantity for each table.

I just want to be able to increment this variable by 1 (for the selected category and sub-category) when a user enter a new message...

The insert procedure is working well and have no problem with it.

I use VWD 2005 Express with SQL 2005 express and i code in VB.

HELP!!!!!

Please post your table structure, some sample data and the update you are expecting to happen. Also the query you currently have would help.

|||

ok

Table Categories
------
CategoryID / int / Primary key
CategoryName / varchar(50)
Description / varchar (50)
Quantity / int

Table SubCategories
------
SubCategoryID / int / Primary key
CategoryID / int / Foreign key, linked to Table Categories-CategoryID
SubCategoryName / varchar(50)
Description / varchar (50)
Quantity / int

So when a user post a message, i want that the Quantity for table Categories and SubCategories increment by 1.

Portion of my code :
-------

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"
DataTextField="CategoryName" DataValueField="CategoryID">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:databaseDB %>"
SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories] ORDER BY [CategoryName]">
</asp:SqlDataSource>

<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2"
DataTextField="SousCategoryName" DataValueField="SubCategoryID" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:databaseDB %>"
SelectCommand="SELECT [SubCategoryID], [SubCategoryName] FROM [SousCategories] WHERE ([CategoryID] = @.CategoryID) ORDER BY [SubCategoryName]">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="CategoryID" PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

|||

With corrections!

----------

ok

Table Categories
------
CategoryID / int / Primary key
CategoryName / varchar(50)
Description / varchar (50)
Quantity / int

Table SubCategories
------
SubCategoryID / int / Primary key
CategoryID / int / Foreign key, linked to Table Categories-CategoryID
SubCategoryName / varchar(50)
Description / varchar (50)
Quantity / int

So when a user post a message, i want that the Quantity for table Categories and SubCategories increment by 1.

Portion of my code :
-------

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"
DataTextField="CategoryName" DataValueField="CategoryID">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:databaseDB %>"
SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories] ORDER BY [CategoryName]">
</asp:SqlDataSource>

<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2"
DataTextField="SubCategoryName" DataValueField="SubCategoryID" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:databaseDB %>"
SelectCommand="SELECT [SubCategoryID], [SubCategoryName] FROM [SubCategories] WHERE ([CategoryID] = @.CategoryID) ORDER BY [SubCategoryName]">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="CategoryID" PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

|||So any ideas?|||

Hi,

You can create a button_click event in your code-behind page. The following code (c#) is just a sample which can achieve your purpose.

protected void Button1_Click(object sender, EventArgs e) {/// Get the CategoryName value from DropDownList1string CategoryName =this.DropDownList1.SelectedItem.Value;/// Get the CategoryName value from DropDownList2string SubCategoryName =this.DropDownList2.SelectedItem.Value;/// in this sample,we get the message and the username value from textboxesstring Message =this.TextBox1.Text;string User=this.TextBox2.Text;/// connstr is the name of connection string you set in Web.configstring conn = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString; SqlConnection myconn =new SqlConnection(conn);/// write the message which user submit into YourTablestring insert_command ="Insert into YourTable (CategoryName,SubCategoryName,Message,Users) values('"+CategoryName+"','"+SubCategoryName+"','"+Message+"','"+User+"')"; myconn.Open(); SqlCommand mycomm =new SqlCommand(insert_command, myconn); mycomm.ExecuteNonQuery(); mycomm.Dispose();/// sql for updating the Quantity number in the Categories tablestring up_cate ="update Categories set Quantity = (Quantity+1) where CategoryName = '" + CategoryName +"'";/// sql for updating the Quantity number in the SubCategories tablestring up_subcate ="update SubCategories set Quantity = (Quantity+1) where SubCategoryName ='" + SubCategoryName +"'"; SqlCommand mycomm_1 =new SqlCommand(up_cate, myconn); SqlCommand mycomm_2 =new SqlCommand(up_subcate, myconn); mycomm_1.ExecuteNonQuery(); mycomm_2.ExecuteNonQuery(); mycomm_1.Dispose(); mycomm_2.Dispose(); myconn.Close(); }

Hope it helps. Thanks.|||

Sorry i code in VB...

I tried that and it's not working (on a click event) :

Dim myConnection As String = System.Configuration.ConfigurationManager.ConnectionStrings("FouinerDB").ConnectionString
Dim myConnectionDB As New Data.SqlClient.SqlConnection(myConnection)
Dim myDA As New Data.SqlClient.SqlDataAdapter()

Dim CatID As Integer = ddlCategories.SelectedValue
Dim SubCatID As Integer = ddlSousCategories.SelectedValue

myDA.UpdateCommand = New Data.SqlClient.SqlCommand("UPDATE Categories SET Quantity=10 WHERE CategoryID = " & CatID.ToString(), myConnectionDB)

|||

(1) Please use parameterized queries to prevent SQL injeection attacks.

I would recommend you do it via stored procedure as follows:

Update CategoriesSET Quantity = Quantity + 1WHERE Categoryid = @.CategoryidUpdate SubCategoriesSET Quantity = Quantity + 1WHERE SubCategories = @.SubCategoriesand Categoryid = @.Categoryid

If you had to do it via adhoc T-SQL, you'd be making 2 trips once for each Update. Through a stored proc, you can do it all in one trip.

|||Ok, but how do you call the stored procedure in the vb code.

I dont know nothing about it... ;-(|||I tired that for stored procedure and when i try it (execute), it doesn't change anything in my database (i also tried to change @.Categoryid and @.SousCategoriesID by values that i know are valid ) :

ALTER PROCEDURE

dbo.UpdateQuantity/*(@.parameter1 int = 5,@.parameter2 datatype OUTPUT)*/

AS

/* SET NOCOUNT ON */RETURN

BEGIN

DECLARE@.CategoryidintDECLARE@.SousCategoriesIDint

UPDATE

CategoriesSETQuantity = Quantity + 1WHERECategoryID = @.Categoryid

UPDATE

SousCategoriesSETQuantity = Quantity + 1WHERESousCategoriesID = @.SousCategoriesID

END

|||I don't understand why aSelectCommand works in my page and not anUpdateCommand...

Works!!!
myDA.SelectCommand =

New Data.SqlClient.SqlCommand("SELECT * FROM Annonces WHERE SousCategoryID = " & SubCatID.ToString(), myConnectionDB)

Dont works!!!
myDA.UpdateCommand =New Data.SqlClient.SqlCommand("UPDATE Categories SET Quantity = 10 WHERE CategoryID = " & CatID.ToString(), myConnectionDB)
|||

Ok i finally got it!!!

It's theUpdateCommand.ExecuteNonQuery() that i missed. ;-)

I will also try with a Stored Procedure... See you soon!
-----------

Dim myConnection As String = System.Configuration.ConfigurationManager.ConnectionStrings("DatabaseDB").ConnectionString
Dim myConnectionDB As New Data.SqlClient.SqlConnection(myConnection)
myConnectionDB.Open()
Dim myDA1 As New Data.SqlClient.SqlDataAdapter()
Dim myDA2 As New Data.SqlClient.SqlDataAdapter()

Dim CatID As Integer = ddlCategories.SelectedValue
Dim SubCatID As Integer = ddlSousCategories.SelectedValue

myDA1.UpdateCommand = New Data.SqlClient.SqlCommand("UPDATE Categories SET Quantity = Quantity + 1 WHERE CategoryID = " & CatID.ToString(), myConnectionDB)
myDA2.UpdateCommand = New Data.SqlClient.SqlCommand("UPDATE SousCategories SET Quantity = Quantity + 1 WHERE SousCategoriesID = " & SubCatID.ToString(), myConnectionDB)
myDA1.UpdateCommand.ExecuteNonQuery()
myDA2.UpdateCommand.ExecuteNonQuery()
myConnectionDB.Close()

|||

It would be more like this: The parameters values would come from your VB application. For your update on SubCategories I also added the CategoryId= @.CategoryID condition because I assumed that the same subcategory could be present for multiple categories. So its important to also specify the Category level.

ALTER PROCEDURE dbo.UpdateQuantity ( @.Categoryidint , @.SousCategoriesIDinT)ASBeginSET NOCOUNT ON UPDATE CategoriesSET Quantity = Quantity + 1WHERE CategoryID = @.CategoryidUPDATE SousCategoriesSET Quantity = Quantity + 1WHERE SousCategoriesID = @.SousCategoriesIDAnd CategoryID = @.CategoryidSET NOCOUNT OFFEND

Your VB code could look like this:

Dim myCommandAs SqlCommandDim myParamAs SqlParametermyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="updateQuantity"myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.Add(New SqlParameter("@.Categoryid",SqlDbType.int))myCommand.Parameters("@.Categoryid").Value = 10myCommand.Parameters.Add(New SqlParameter("@.SousCategoriesID",SqlDbType.int))myCommand.Parameters("@.SousCategoriesID").Value = 1TryIf objCon.State = 0Then objCon.Open()mycommand.ExecuteNonQuery()Catch excAs ExceptionResponse.Write(exc)FinallyIf objCon.State = ConnectionState.OpenThen objCon.Close()End IfEnd Try

|||Hi,

In terms of process speed, wich one is the fastest?

Hard coding or Stored Procedure?|||

Whether you use T-SQL/stored proc the actual statements are executed at the DB. IF you had multiple T-SQL statements to be executed you are better off using stored procs as you can do it all in one trip. Also, if you have T-SQL all over your app it becomes a maintenance nightmare if your underlying schema changes in future. Stored procs provide a channelized way of accesssing the data. Also, for security reasons procs are a better choice. You'd have to give SELECT/UPDATE/DELETE rights to the userid you are using on the actual table if you use adhoc queries. If you do the same thing via procs, you just give the user permission to execute the proc. There are tons of other pros vs cons. You can also google (adhoc query vs stored proc) and see some good debate about this.