Ad:

ASP.NET Discussion Thread
Unsolved
Views: 445

Aug 5th, 2009

Data input via front end form

Expand Post »

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

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();
    }

Similar Threads

How to retrieve data from SQL backend on a .net form field wise in C++
'Input Past End of File' Error Persists even with 'EOF' Statements in Visual Basic 4 / 5 / 6

Haimanti

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

10 posts
since Jul 2009

Permalink

Aug 5th, 2009

Re: Data input via front end form

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.

bcasp

Reputation Points: 23

Solved Threads: 10

Light Poster

Offline

45 posts
since Apr 2008

Permalink

Aug 5th, 2009

Re: Data input via front end form

Not necessarily. You could do this:

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.

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Aug 6th, 2009

Re: Data input via front end form

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

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();
    }

Click to Expand / Collapse Quote 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.

Haimanti

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

10 posts
since Jul 2009

Permalink

Aug 6th, 2009

Re: Data input via front end form

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!

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in ASP.NET Forum Timeline: Gridview and Drop Down List

Next Thread in ASP.NET Forum Timeline: Duplication of records

DaniWeb Message

Cancel Changes

User Name
Password