Writing dropdownlist value to a DB

[mellenburg]

I'm very new to .net and am using Visual Web Developer Express Edition.

I have created a form that allows me to enter a new record into a table. One of the feilds in the record is to be selected from a drop down list. I am able to get the list to populate correctly, but I can't get the value to write to the DB. I suspect I'm not binding the value, but I don't know how to bind the value.

Here's the code for a text box control, which works, and the dropdownlist control, which does not:

                <asp:TextBox 
                  ID="CategoryTextBox" 
                  Text='<%# Bind("Category") %>' 
                  RunAt="Server" />

                <asp:DropDownList 
                  ID="SubCategoryIDDropDownList"
                  DataSourceID="SubCategoriesSqlDataSource"
                  DataTextField="SubCategory"
                  DataValueField="SubCategoryID"
                  AutoPostBack=true
                  runat="server">

Here's the SqlDataSource control, which contains the INSERT statement:

<asp:SqlDataSource 
  ID="CategoriesDetailsSqlDataSource" 
  runat="server" 
  ConnectionString="<%$ ConnectionStrings:BudgetConnectionString1 %>"
  OnInserted="CategoriesDetailsSqlDataSource_OnInserted"
  ProviderName="<%$ ConnectionStrings:BudgetConnectionString1.ProviderName %>"
  SelectCommand="SELECT * FROM Categories WHERE CategoryID = @CategoryID"
  DeleteCommand="DELETE Categories WHERE CategoryID=@CategoryID"
  UpdateCommand="UPDATE Categories SET Category=@Category, SubCategoryID=@SubCategoryID WHERE CategoryID=@CategoryID"
  InsertCommand="INSERT INTO Categories (Category, SubCategoryID) VALUES (@Category, @SubCategoryID);">
  <SelectParameters>
    <asp:Parameter Name="CategoryID" Type="int32" DefaultValue="0" />
  </SelectParameters>
  <InsertParameters>
    <asp:Parameter Name="CategoryID" Direction="Output" Type="Int32" DefaultValue="0" />
  </InsertParameters>
</asp:SqlDataSource>

This is what I have in my code behind file:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class ManageTables : System.Web.UI.Page
{
  public void Page_Load(object sender, EventArgs e)
  {
  }

  public void CategoriesGridView_OnSelectedIndexChanged(Object sender, EventArgs e)
  {
    CategoriesDetailsSqlDataSource.SelectParameters["CategoryID"].DefaultValue =
    CategoriesGridView.SelectedValue.ToString();
    CategoriesFormView.DataBind();
  }

  public void SubCategoriesGridView_OnSelectedIndexChanged(Object sender, EventArgs e)
  {
    SubCategoriesDetailsSqlDataSource.SelectParameters["SubCategoryID"].DefaultValue =
    SubCategoriesGridView.SelectedValue.ToString();
    SubCategoriesFormView.DataBind();
  }

  public void CategoriesDetailsSqlDataSource_OnInserted(Object sender, SqlDataSourceStatusEventArgs e)
  {
    System.Data.Common.DbCommand command = e.Command;

    CategoriesDetailsSqlDataSource.SelectParameters["CategoryID"].DefaultValue = command.Parameters["@CategoryID"].Value.ToString();

    CategoriesGridView.DataBind();
    CategoriesFormView.DataBind();
  }

  public void SubCategoriesDetailsSqlDataSource_OnInserted(Object sender, SqlDataSourceStatusEventArgs e)
  {
    System.Data.Common.DbCommand command = e.Command;

    SubCategoriesDetailsSqlDataSource.SelectParameters["SubCategoryID"].DefaultValue = command.Parameters["@SubCategoryID"].Value.ToString();

    SubCategoriesGridView.DataBind();
    SubCategoriesFormView.DataBind();
  }

  public void CategoriesFormView_ItemDeleted(Object sender, FormViewDeletedEventArgs e)
  {
    CategoriesGridView.DataBind();
  }
    
  public void CategoriesFormView_ItemUpdated(Object sender, FormViewUpdatedEventArgs e)
  {
    CategoriesGridView.DataBind();
  }

  public void SubCategoriesFormView_ItemDeleted(Object sender, FormViewDeletedEventArgs e)
  {
      SubCategoriesGridView.DataBind();
  }

  public void SubCategoriesFormView_ItemUpdated(Object sender, FormViewUpdatedEventArgs e)
  {
      SubCategoriesGridView.DataBind();
  }
}

The error I'm getting is:

Must declare the scalar variable "@SubCategoryID".


What am I missing that's preventing this from working properly? Thank you,

Matt

 

[PsychoCoder]

Matt,

The DropDownList code you posted is using the DataSource SubCategoriesSqlDataSource but you posted the code for the CategoriesDetailsSqlDataSource DataSource. Could you post the code for the SubCategoriesSqlDataSource so we can see that?

Thanks

Senior Qik III, ASP.Net, VB.Net ,SQL Programmer

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
** Do NOT feed Code Gremlins after midnight **
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[mellenburg]

Here's the code for the SubCategoriesSqlDataSource, which supplies the information for the DropDownList.

<asp:SqlDataSource 
  ID="SubCategoriesSqlDataSource" 
  runat="server" 
  ConnectionString="<%$ ConnectionStrings:BudgetConnectionString1 %>"
  ProviderName="<%$ ConnectionStrings:BudgetConnectionString1.ProviderName %>"
  SelectCommand="SELECT * FROM SubCategories ORDER BY SortOrder">
</asp:SqlDataSource>