ASP.NET

ASP.NET RSS

Data input via front end form

Please support our ASP.NET advertiser: Intel Parallel Studio Home

•

Join Date: Jul 2009

Posts: 9

Reputation: Haimanti is an unknown quantity at this point

Solved Threads: 0

Haimanti

Offline

Newbie Poster

Data input via front end form

Aug 5th, 2009

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

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
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();
    }
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();
    }

•

Join Date: Apr 2008

Posts: 45

Reputation: bcasp is an unknown quantity at this point

Solved Threads: 10

bcasp

Offline

Light Poster

Re: Data input via front end form

Aug 5th, 2009

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.

•

Join Date: Feb 2009

Posts: 3,242

Reputation: sknake has much to be proud of

Solved Threads: 577

sknake

Offline

.NET Enthusiast

Re: Data input via front end form

Aug 5th, 2009

Not necessarily. You could do this:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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)
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)

Last edited by sknake; Aug 5th, 2009 at 2:42 pm.

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Jul 2009

Posts: 9

Reputation: Haimanti is an unknown quantity at this point

Solved Threads: 0

Haimanti

Offline

Newbie Poster

Re: Data input via front end form

Aug 6th, 2009

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

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
 
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();
    }
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();
    }

•

Originally Posted by 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.