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

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....

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

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

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

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

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

Hello rookie tiro

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

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

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

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

Hello again

It seems that your problem appears thereand I'll reply there

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

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

Hello again

It seems that your problem appears thereand I'll reply there

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