Data input via front end form

[Haimanti]

hi,

I'm trying to get daya into a new table via form. two fields in new table will pick up data based on look up tables.
pls guide as to how do i proceed . The code is flashing conversion of Data type error.
To avoid the datatype error, i 've already taken category_id and city_id fields in DB as varchar.

Thanks

protected void btnWinesAvailable_Click(object sender, EventArgs e)
    {   
        conn.Open();
        SqlCommand cmd1 = new SqlCommand("Insert into wines_available(name, address, city_id, category_id) values (@name, @address, @cityid, @categoryid)", conn);
        
       string a =("select city_id from city_master where city_name= '" + ddlCityname.SelectedItem + "'");
       string b= ("select category_id from category_master where category = '"+ ddlCategoryname.SelectedItem+ "'");
        
        cmd1.Parameters.Add(new SqlParameter("@name", txtOrgName.Text));
        cmd1.Parameters.Add(new SqlParameter("@address", txtAddress.Text));
        cmd1.Parameters.Add(new SqlParameter("@cityid", SqlDbType.Char,10)).Value = a;
        cmd1.Parameters.Add(new SqlParameter("@categoryid", SqlDbType.Char, 10)).Value = b;
        
      
        cmd1.ExecuteNonQuery();
        txtOrgName.Text = "";
        txtAddress.Text = "";
        //txtCityID1.Text = "";
        //txtCategoryID1.Text = "";
        conn.Close();
        conn.Dispose();
    }

[bcasp]

If what you're trying to do is put the results of the SELECT statements in a and b into the parameters, then it's not going to work like you think it will. Your best bet is to use a stored procedure. That way you'll be able to use variables in the stored proc to hold the results of the two select statements. Then you just plug them into your insert statement in the same stored procedure.
Otherwise, you'll have to make two DB calls from the code before the insert to retrieve the values and then use those values for your insert.

[sknake]

Not necessarily. You could do this:

Declare @CityId int, @CategoryId int
Set @CityId = (select city_id from city_master where city_name= 'abc')
Set @CategoryId = (select city_id from city_master where city_name='123')

Insert into wines_available(name, address, city_id, category_id) values (@name, @address, @cityid, @categoryid)

[Haimanti]

Thanks for the reply. I was getting error in the i/p parameters data type.

The use of DR and change in parameter worked.

I'll also try with stores precedure n variables as suggested

protected void btnWinesAvailable_Click(object sender, EventArgs e)
    {
        conn.Open();

        SqlCommand cmd1 = new SqlCommand("Insert into wines_available(name, address, city_id, category_id) values (@name, @address, @cityid, @categoryid)", conn);

        SqlCommand a = new SqlCommand("select city_id from city_master where city_name= '" + ddlCityname.SelectedItem + "'", conn);
        SqlCommand b = new SqlCommand("select category_id from category_master where category = '" + ddlCategoryname.SelectedItem + "'", conn);


        cmd1.Parameters.Add(new SqlParameter("@name", txtOrgName.Text));
        cmd1.Parameters.Add(new SqlParameter("@address", txtAddress.Text));

        SqlDataReader dr1;
        dr1 = a.ExecuteReader();
        while (dr1.Read())
        {
            cmd1.Parameters.Add(new SqlParameter("@cityid", SqlDbType.Char, dr1["city_id"].ToString().Trim().Length)).Value = dr1["city_id"].ToString().Trim();
        }
        dr1.Close();

        SqlDataReader dr2;
        dr2 = b.ExecuteReader();
        while (dr2.Read())
        {
            cmd1.Parameters.Add(new SqlParameter("@categoryid", SqlDbType.Char, dr2["category_id"].ToString().Trim().Length)).Value = dr2["category_id"].ToString().Trim();
        }

        dr2.Close();
        
        txtOrgName.Text = "";
        txtAddress.Text = "";
        //txtCityID1.Text = "";
        //txtCategoryID1.Text = "";

        cmd1.ExecuteNonQuery();
        conn.Close();
        conn.Dispose();
    }

If what you're trying to do is put the results of the SELECT statements in a and b into the parameters, then it's not going to work like you think it will. Your best bet is to use a stored procedure. That way you'll be able to use variables in the stored proc to hold the results of the two select statements. Then you just plug them into your insert statement in the same stored procedure. Otherwise, you'll have to make two DB calls from the code before the insert to retrieve the values and then use those values for your insert.

[sknake]

I'm glad you got it working

Please mark this thread as solved if you have found an answer to your question and good luck!