Click here to Skip to main content
15,606,838 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have two tables in my database as following:

Suggestions Table: ID, Title, Description, StatusID... etc

SuggestionsStatus Table: ID, Status

I am using GridView to show the Suggestions and in the last column I put a DropDownList that for selecting the status for each Suggestion. Now, when I tried to update the Status of one of the submitted suggestions by selecting the status from the DropDownList, the value has been selected but it wasn't inserted to the Database (which means still I have NULL value in the StatusID column in the Suggestions Table) and I don't know why.

My ASP.NET code:
<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
                        AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="ID"
                        width="950px" CssClass="mGrid"
                        OnRowDataBound="GridView1_RowDataBound" >
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
            <HeaderStyle Font-Bold = "true" ForeColor="Black" Height="20px"/>
                <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False"
                    ReadOnly="True" SortExpression="ID" />
                <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
                <asp:BoundField DataField="Description" HeaderText="Description"
                    SortExpression="Description" />
                <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                <asp:BoundField DataField="Username" HeaderText="Username"
                    SortExpression="Username" />
                <asp:BoundField DataField="DivisionShortcut" HeaderText="Division"
                    SortExpression="DivisionShortcut" />
                <asp:TemplateField HeaderText="Status">
                        <asp:DropDownList ID="DropDownList" runat="server" DataSourceID="SqlDataSource2"
                                          Font-Bold="True" ForeColor="#006666" AppendDataBoundItems="false"
                                          DataTextField="Status" DataValueField="ID" AutoPostBack="true"
        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:testConnectionString %>"
            SelectCommand="SELECT     dbo.SafetySuggestionsLog.ID, dbo.SafetySuggestionsLog.Title, dbo.SafetySuggestionsLog.Description, dbo.employee.Name, dbo.SafetySuggestionsLog.Username,
FROM         dbo.employee INNER JOIN
                      dbo.SafetySuggestionsLog ON dbo.employee.Username = dbo.SafetySuggestionsLog.Username INNER JOIN
                      dbo.Divisions ON dbo.employee.DivisionCode = dbo.Divisions.SapCode"
                      FilterExpression="[DivisionShortcut] like '{0}%'">

                        <asp:ControlParameter ControlID="ddlDivision" Name="DivisionShortcut"
                                                 PropertyName="SelectedValue" Type="String" />

        <%--For the DropDownList--%>
        <asp:SqlDataSource ID="SqlDataSource2" runat="server"
        ConnectionString="<%$ ConnectionStrings:testConnectionString %>"
        SelectCommand="SELECT * FROM [SafetySuggestionsStatus]"></asp:SqlDataSource>

My Code-Behind:
protected void DropDownList_SelectedIndexChanged(object sender, EventArgs e)
    DropDownList ddl = (DropDownList)sender;
    int suggestionStatus = int.Parse(ddl.SelectedValue);

    //For inserting the status in the database
    string connString = "Data Source=localhost\\sqlexpress;Initial Catalog=psspdbTest;Integrated Security=True";
    string updateCommand = "UPDATE SafetySuggestionsLog  SET [StatusID] = @StatusID WHERE [ID] = '" + index + "'";
    using (SqlConnection conn = new SqlConnection(connString))
        using (SqlCommand cmd = new SqlCommand(updateCommand, conn))
            cmd.Parameters.AddWithValue("@StatusID", suggestionStatus);

** EDIT:**

I modified the DropDownList_SelectedIndexChanged() as shown above but it gave me the following error:

Cannot insert the value NULL into column 'Title', table 'psspdbTest.dbo.SafetySuggestionsLog'; column does not allow nulls. INSERT fails.
The statement has been terminated.

So how I can fix this problem to be able to update the status of one of the submitted Suggestions in the (GridView) in the database?**
Updated 27-Feb-12 23:15pm

1 solution

Check that your query is correct and you are inserting the values appropriately.
Share this answer
matrix388 28-Feb-12 2:41am    
Please see the updated question above.
Abhinav S 28-Feb-12 2:55am    
As the error suggests, you are saving null into the Title field. You can force "" to be inserted into title if this is in conjunction with your business scenario.
matrix388 28-Feb-12 2:57am    
I just want to update on column in the Suggestion table which is Status not the title, so how to do that?
Abhinav S 28-Feb-12 4:55am    
Use an Update query instead of an Insert query in this case.
matrix388 28-Feb-12 5:14am    
I used the following command:

string updateCommand = "UPDATE SafetySuggestionsLog SET [StatusID] = @StatusID WHERE [ID] = @ID";

And it will update the status for all suggestions, so how to do it just for specific suggestion?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900