Can i give auto increment to stateID and productID or should i allow nulls??? RSS

• • Reply
  

  rookie tiro

  Member

  86 Points

  130 Posts

  Can i give auto increment to stateID and productID or should i allow nulls???

  Jan 27, 2012 05:04 PM|LINK

  I have three tables Table(1):with CustomerID(PrimaryKEy), StateID(Foreign Key), ProductID(FK)., customername, state, city, zipcode. Table (2): StateID(primary Key), states Table(3): ProductID(Primary Key), products Now, i want to insert values into Table(1). I'm getting an error coz i have'nt allowed nulls for stateID and productID. Can i give auto increment to stateID and productID or should i allow nulls??? By the way Table(2) states field is only for four items to be populated in Dropdownlist. similarly Table(3) Products. Plz suggest me....
• • Reply
  

  AZMatt

  Star

  11024 Points

  1959 Posts

  Re: Can i give auto increment to stateID and productID or should i allow nulls???

  Jan 27, 2012 08:06 PM|LINK

  Ideally, you would want to know the StateID and ProductID at the time of your insert into the Customer table.  If you can't do this for some reason, you can leave them null.  I would not recommend auto-increment because then the foreign keys would not tie up correctly to the State or Product tables. 

  If you do allow null, and you want to write a query that joins all three tables, be sure to use a LEFT OUTER JOIN to make sure all of your Customer data is returned with null values for the State and Product data.

   

  Matt

  If this helps, please mark as Answer.
  • Edited by AZMatt on Jan 27, 2012 08:07 PM
• • Reply
  

  rookie tiro

  Member

  86 Points

  130 Posts

  Re: Can i give auto increment to stateID and productID or should i allow nulls???

  Jan 29, 2012 06:35 PM|LINK

  Problem with row_updating Event!!!!!!!!!!!!!!!!!

  protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { SqlConnection con = new SqlConnection("Data Source=STD-258E51EA446\\SQLEXPRESS;Initial

   

  Problem with row_updating Event
  protected void Page_Load(object sender, EventArgs e)
      {
          if (!IsPostBack)
          {
  
              SqlConnection con = new SqlConnection("Data Source=STD-258E51EA446\\SQLEXPRESS;Initial Catalog=Customers;Integrated Security=True");
              SqlCommand cmd = new SqlCommand("usp_state", con);
              con.Open();
              SqlDataAdapter da = new SqlDataAdapter(cmd);
              DataSet ds = new DataSet();
              da.Fill(ds);
              cmd.ExecuteScalar();
              ddlstate.DataTextField = "StateNames";
              ddlstate.DataValueField = "StateID";
              ddlstate.DataSource = ds;
              ddlstate.DataBind();
              ddlstate.Items.Insert(0, new ListItem("Select"));
              con.Close();
  
              SqlConnection con1 = new SqlConnection("Data Source=STD-258E51EA446\\SQLEXPRESS;Initial Catalog=Customers;Integrated Security=True");
              SqlCommand cmd1 = new SqlCommand("usp_products", con1);
              con1.Open();
              SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
              DataSet ds1 = new DataSet();
              da1.Fill(ds1);
              cmd1.ExecuteScalar();
              ddlproducts.DataTextField = "Products";
              ddlproducts.DataValueField = "ProductID";
              ddlproducts.DataSource = ds1;
              ddlproducts.DataBind();
              ddlproducts.Items.Insert(0, new ListItem("Select"));
              con1.Close();
              }
          bindgrid();
   }
  
      protected void btnsubmit_Click(object sender, EventArgs e)
      {
          SqlConnection con = new SqlConnection("Data Source=STD-258E51EA446\\SQLEXPRESS;Initial Catalog=Customers;Integrated Security=True");
          SqlCommand cmd = new SqlCommand("usp_insert", con);
          con.Open();
          SqlDataAdapter da = new SqlDataAdapter(cmd);
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.Parameters.AddWithValue("@CustomerName", txtcustname.Text);
          cmd.Parameters.AddWithValue("@Address", txtadd.Text);
          cmd.Parameters.AddWithValue("@City", txtcity.Text);
          cmd.Parameters.AddWithValue("@Zipcode", txtzip.Text);
          cmd.Parameters.AddWithValue("@RegistrationDate", txtregdate.Text);
          cmd.Parameters.AddWithValue("@Email", txtemail.Text);
          cmd.Parameters.Add("@StateID",SqlDbType.Int).Value = ddlstate.SelectedItem.Value;
          cmd.Parameters.Add("@ProductID",SqlDbType.Int).Value = ddlproducts.SelectedItem.Value;
          DataSet ds = new DataSet();
          da.Fill(ds);
          cmd.ExecuteNonQuery();
          GridView1.DataSource = ds;
          GridView1.DataBind();
          con.Close();
      }
  
      protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
      {
          GridView1.EditIndex = e.NewEditIndex;
          bindgrid();
      }
      public void bindgrid()
      {
          SqlConnection con = new SqlConnection("Data Source=STD-258E51EA446\\SQLEXPRESS;Initial Catalog=Customers;Integrated Security=True");
          SqlCommand cmd = new SqlCommand("usp_join", con);
          con.Open();
          SqlDataAdapter da = new SqlDataAdapter(cmd);
          DataSet ds = new DataSet();
          da.Fill(ds);
          cmd.ExecuteScalar();
          GridView1.DataSource = ds;
          GridView1.DataBind();
          con.Close();
      }
      protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
      {
          GridView1.EditIndex = -1;
          bindgrid();
      }
      protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
      {
          SqlConnection con = new SqlConnection("Data Source=STD-258E51EA446\\SQLEXPRESS;Initial Catalog=Customers;Integrated Security=True");
          SqlCommand cmd = new SqlCommand("usp_update", con);
          con.Open();
          SqlDataAdapter da = new SqlDataAdapter(cmd);
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.Parameters.AddWithValue("@CustomerID", SqlDbType.Int);
          cmd.Parameters.AddWithValue("@CustomerName", SqlDbType.VarChar);
          cmd.Parameters.AddWithValue("@Address", SqlDbType.VarChar);
          cmd.Parameters.AddWithValue("@City", SqlDbType.VarChar);
          cmd.Parameters.AddWithValue("@Zipcode", SqlDbType.VarChar);
          cmd.Parameters.AddWithValue("@RegistrationDate", SqlDbType.DateTime);
          cmd.Parameters.AddWithValue("@Email", SqlDbType.VarChar);
          DataSet ds = new DataSet();
          da.Fill(ds);
          cmd.ExecuteNonQuery();
          //GridView1.DataSource = ds;
          //GridView1.DataBind();
          GridView1.EditIndex = -1;
          bindgrid();
          con.Close();
          
      }
      protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
      {
          GridView1.PageIndex = e.NewPageIndex;
          bindgrid();
      }
  }
  
  "S.P"
  [usp_join]
  
  	SELECT Customer.CustomerName,Customer.Address, State.StateNames,Customer.City,Customer.Zipcode,Customer.Email,Customer.RegistrationDate,Product.Products
  	From Customer
  	Left outer join State
  	on Customer.StateID = State.StateID
  	Left outer join Product
  	on Customer.ProductID = Product.ProductID
  
  "S.P"
  [usp_insert]
  
  (@CustomerName varchar(50),@Address varchar(50), @City varchar(50), @Zipcode varchar(6),@RegistrationDate Datetime, @Email varchar(50),@StateID int,@ProductID int)
  AS
  insert into Customer
  (CustomerName
  ,Address
  , City
  , Zipcode
  ,RegistrationDate
  , Email
  ,StateID
  ,ProductID)
  values
  (@CustomerName
  ,@Address
  , @City
  , @Zipcode
  ,@RegistrationDate
  , @Email
  ,@StateID
  ,@ProductID)
  
  
  "S.P"
  [usp_update]
  
  (@CustomerID int,@CustomerName varchar(50),@Address Varchar(50),@City varchar(50),@Zipcode varchar(50),@Email Varchar(50),@RegistrationDate Datetime)
  AS
  update Customer set CustomerName=@CustomerName, Address=@Address, City=@city, Zipcode=@Zipcode, Email=@Email, RegistrationDate=@RegistrationDate where CustomerID=@CustomerID

   

   

  • Edited by rookie tiro on Jan 29, 2012 06:51 PM
  • Edited by rookie tiro on Jan 29, 2012 06:52 PM
  • Edited by decker dong - msft on Feb 02, 2012 01:18 AM
  • Edited by decker dong - msft on Feb 02, 2012 01:18 AM
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: Can i give auto increment to stateID and productID or should i allow nulls???

  Feb 02, 2012 01:18 AM|LINK

  Hello rookie tiro：）

  So you've found the issue and your problem gets solved？

• • Reply
  

  rookie tiro

  Member

  86 Points

  130 Posts

  Re: Can i give auto increment to stateID and productID or should i allow nulls???

  Feb 02, 2012 03:23 AM|LINK

  No that didnt solve me. I had done it my own way. I havent done them giving nulls.

• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: Can i give auto increment to stateID and productID or should i allow nulls???

  Feb 02, 2012 04:13 AM|LINK

  Hello again：）

  It seems that your problem appears there，and I'll reply there：

  http://forums.asp.net/p/1763680/4811730.aspx/1?Re+Problem+with+updating+

  • Marked as answer by Young Yang - MSFT on Feb 20, 2012 12:21 PM
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: Can i give auto increment to stateID and productID or should i allow nulls???

  Feb 02, 2012 04:13 AM|LINK

  Hello again：）

  It seems that your problem appears there，and I'll reply there：

  http://forums.asp.net/p/1763680/4811730.aspx/1?Re+Problem+with+updating+