unable to link tables and bind subcategory page to gridview

Question

I have a category table with 4 columns as and i have made Id as identity as well as Primarykey

ID Categoryname Cimage

Then i have a subcategory table as

ID1 Subcategoryname Simage Description Catid .

In category page

I have a Textbox (for entering name of category) and Image . I have binded it to gridview.
so my gridview displays Textbox data and image.

In my Subcategory page

I have a dropdownlist ,Textbox1 (for entering name of subcategory) , Image , Textbox2 (for entering description)

I populate the dropdownlist from category table(categoryname)
and the rest data should be saved into database and display in gridview..

I am stuck with subcategory page .My data enters into database but unable to display in gridview....

how should i relate 2 tables with id and catid .
and how to write the ado.net connections for subcategory page..

I have been trying for weeks but dint succeed...

can anyone help me out with detailed coding. please

Thanks in advance .

Iftikar Hussain · Answer

since Emp id is PK then you need to save image name with Emp id like below. before that you need to modify your SP to return inserted Emp Id like below ALTER PROCEDURE [dbo].[insert_employee] ( @cname varchar(50) @cimage varchar(50) ) AS Begin declare @Id int declare @TempImgName varchar(20) Insert into category values (@cname@cimage) SELECT @Id=IDENT_CURRENT('category') --setting the image name with the category id SET @TempImgName=CAST(@Id as VARCHAr)+'_'+@cimage --updating the image name UPDATE category SET cimage=@TempImgName WHERE id=@Id --selecting the file name SELECT @TempImgName end and in your code protected void btn_insert_Click(object sender EventArgs e) { var nm = txt_name.Text; string fileName = string.Empty; //string UniqueName = (DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString()); if (FileUpload1.PostedFile.ContentLength > 0) { fileName = Path.GetFileName(FileUpload1.PostedFile.FileName); SqlConnection con = new SqlConnection(strConnString); con.Open(); SqlCommand com = new SqlCommand(insert_employee con); com.CommandType = CommandType.StoredProcedure; com.Connection = con; com.Parameters.AddWithValue(@cname txt_name.Text); com.Parameters.AddWithValue(@cimage fileName); string imgFileName = Convert.ToString(com.ExecuteScalar()); com.Dispose(); //string path = Server.MapPath(images) + \; string path = Server.MapPath(images) + / + imgFileName; FileUpload1.SaveAs(path); bindgrid(); con.Close(); clear(); } } try the above code and check weather your first issue is resolved or not Regards Iftikar

antony victor · Answer

Dear sir thanks for the help. But can u help me . I am stuck with 2 things When ever i try to upload the same image twice in category page with same name it uploads but when i delete one of the image then the other image also doesn't show up . But the row still exists with other data.... Second issue in Subcategory table is that image uploads twice everytime i click the button. I have attached the category and subcategory page below. CATEGORY PAGE protected void Page_Load(object sender EventArgs e) { if (!IsPostBack) { bindgrid(); } } protected void btn_insert_Click(object sender EventArgs e) { //string UniqueName = (DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString()); if (FileUpload1.PostedFile.ContentLength > 0) { //string temp = Path.GetExtension(FileUpload1.FileName); //if (temp == .jpeg || temp == .JPEG || temp == .jpg || temp == .JPG || temp == .png || temp == .PNG || temp == .bmp || temp == .BMP || temp == .gif || temp == .GIF || temp == .wmf || temp == .WMF) { //string z; s1 = Path.GetFileName(FileUpload1.PostedFile.FileName); //string path = Server.MapPath(images) + \; string path = Server.MapPath(images) + / + s1; FileUpload1.SaveAs(path); //String fn = Guid.NewGuid().ToString() + FileUpload1.FileName.Substring(FileUpload1.FileName.LastIndexOf(.)); //if (path.EndsWith(\) == false) //path += \; //path += fn;//Concating file name with server path //FileUpload1.PostedFile.SaveAs(path);//FileUpload1.PostedFile property Holds client side path //string[] words = s1.Split('.'); //z = path + UniqueName + s1; //FileUpload1.SaveAs(z); //string urldet = ~\ +  images  + \ + UniqueName + s1; //s1 = urldet.Replace(\ /); } } SqlConnection con = new SqlConnection(strConnString); con.Open(); SqlCommand com = new SqlCommand(insert_employee con); com.CommandType = CommandType.StoredProcedure; com.Connection = con; com.Parameters.AddWithValue(@cname txt_name.Text); com.Parameters.AddWithValue(@cimage s1); com.ExecuteNonQuery(); com.Dispose(); bindgrid(); con.Close(); clear(); } private void clear() { txt_name.Text = ; } private void bindgrid() { SqlConnection con = new SqlConnection(strConnString); con.Open(); sqlda = new SqlDataAdapter(SELECT * FROM category  con); dt = new DataTable(); sqlda.Fill(dt); sqlda.Dispose(); GridView1.DataSource = dt; GridView1.DataBind(); con.Close(); } protected void GridView1_RowDeleting(object sender GridViewDeleteEventArgs e) { SqlConnection con = new SqlConnection(strConnString); con.Open(); try { id = Convert.ToInt32(((Label)(GridView1.Rows[e.RowIndex].FindControl(label2))).Text); SqlCommand com = new SqlCommand(delete_employee con); com.CommandType = CommandType.StoredProcedure; com.Connection = con; com.Parameters.Add(@id SqlDbType.Int).Value = id; SqlDataAdapter sqlda = new SqlDataAdapter(select * from category where id=@id con); sqlda.SelectCommand.Parameters.Add(@id SqlDbType.Int).Value = id; DataSet ds = new DataSet(); sqlda.Fill(ds); try { image = Convert.ToString(ds.Tables[0].Rows[0][cimage]); File.Delete(Server.MapPath(images) + \ + image); } catch (Exception) { } com.ExecuteNonQuery(); com.Dispose(); bindgrid(); } catch (Exception) { } } protected void GridView1_RowCancelingEdit(object sender GridViewCancelEditEventArgs e) { GridView1.EditIndex = -1; bindgrid(); } protected void GridView1_RowEditing(object sender GridViewEditEventArgs e) { GridView1.EditIndex = e.NewEditIndex; bindgrid(); } protected void GridView1_RowUpdating(object sender GridViewUpdateEventArgs e) { SqlConnection con = new SqlConnection(strConnString); con.Open(); id = Convert.ToInt32(((Label)(GridView1.Rows[e.RowIndex].FindControl(Label4))).Text); name = (((TextBox)(GridView1.Rows[e.RowIndex].FindControl(Label3))).Text); SqlCommand com = new SqlCommand(update_employee con); com.CommandType = CommandType.StoredProcedure; com.Connection = con; com.Parameters.Add(@id SqlDbType.Int).Value = id; com.Parameters.Add(@cname SqlDbType.VarChar 50).Value = name; com.ExecuteNonQuery(); com.Dispose(); con.Close(); GridView1.EditIndex = -1; bindgrid(); } protected void GridView1_SelectedIndexChanging(object sender GridViewSelectEventArgs e) { GridView1.PageIndex = e.NewSelectedIndex; bindgrid(); } SUBCATEGORY PAGE protected void Page_Load(object sender EventArgs e) { if (!IsPostBack) { bindgrid(); bindgrid1(); } } protected void btn_insert_Click(object sender EventArgs e) { if (FileUpload1.PostedFile.ContentLength > 0) { s1 = Path.GetFileName(FileUpload1.FileName); path = Server.MapPath(images) + / + s1; FileUpload1.SaveAs(path); } SqlConnection con = new SqlConnection(strConnString); con.Open(); SqlCommand com = new SqlCommand(insert_subcategory con); com.CommandType = CommandType.StoredProcedure; com.Connection = con; com.Parameters.AddWithValue(@sname TextBox1.Text); com.Parameters.AddWithValue(@simage s1); com.Parameters.AddWithValue(@desc TextBox2.Text); com.Parameters.AddWithValue(@cid DropDownList1.SelectedValue); com.ExecuteNonQuery(); com.Dispose(); bindgrid1(); con.Close(); clear(); } private void clear() { TextBox1.Text = ; TextBox2.Text = ; } private void bindgrid() { SqlConnection con = new SqlConnection(strConnString); SqlCommand com = new SqlCommand(Select * from category con); SqlDataAdapter da = new SqlDataAdapter(com); DataSet ds = new DataSet(); da.Fill(ds); DropDownList1.DataTextField = ds.Tables[0].Columns[cname].ToString(); DropDownList1.DataValueField = ds.Tables[0].Columns[id].ToString(); DropDownList1.DataSource = ds.Tables[0]; DropDownList1.DataBind(); } private void bindgrid1() { SqlConnection con = new SqlConnection(strConnString); con.Open(); dt = new DataTable(); SqlCommand com = new SqlCommand(select * from subcategory where cid=@cid con); com.Parameters.AddWithValue(@cid DropDownList1.SelectedValue); SqlDataAdapter sqlda = new SqlDataAdapter(com); sqlda.Fill(dt); if (con.State == ConnectionState.Open) sqlda.Fill(dt); sqlda.Dispose(); GridView2.DataSource = dt; GridView2.DataBind(); con.Close(); } protected void GridView2_RowDeleting(object sender GridViewDeleteEventArgs e) { SqlConnection con = new SqlConnection(strConnString); con.Open(); try { int id1= Convert.ToInt32(((Label)(GridView2.Rows[e.RowIndex].FindControl(label2))).Text); SqlCommand com = new SqlCommand(delete_subcategory con); com.CommandType = CommandType.StoredProcedure; com.Connection = con; com.Parameters.Add(@id1 SqlDbType.Int).Value = id1; SqlDataAdapter sqlda = new SqlDataAdapter(select * from subcategory where id1=@id1 con); sqlda.SelectCommand.Parameters.Add(@id1 SqlDbType.Int).Value = id1; DataSet ds = new DataSet(); sqlda.Fill(ds); try { simage = Convert.ToString(ds.Tables[0].Rows[0][image]); File.Delete(Server.MapPath(images) + \ + simage); } catch (Exception) { } com.ExecuteNonQuery(); com.Dispose(); bindgrid1(); } catch (Exception) { } } protected void GridView2_RowCancelingEdit(object sender GridViewCancelEditEventArgs e) { GridView2.EditIndex = -1; bindgrid1(); } protected void GridView2_RowEditing(object sender GridViewEditEventArgs e) { GridView2.EditIndex = e.NewEditIndex; bindgrid1(); } protected void GridView2_RowUpdating(object sender GridViewUpdateEventArgs e) { SqlConnection con = new SqlConnection(strConnString); con.Open(); id = Convert.ToInt32(((Label)(GridView2.Rows[e.RowIndex].FindControl(label5))).Text); sname = (((TextBox)(GridView2.Rows[e.RowIndex].FindControl(TextBox1))).Text); desc = (((TextBox)(GridView2.Rows[e.RowIndex].FindControl(TextBox2))).Text); SqlCommand com = new SqlCommand(update_subcategory con); com.CommandType = CommandType.StoredProcedure; com.Connection = con; //com.Parameters.Add(@id1SqlDbType.Int).Value = id1; com.Parameters.Add(@sname SqlDbType.VarChar 50).Value = sname; com.Parameters.Add(@desc SqlDbType.VarChar 250).Value = desc; //com.Parameters.Add(@id SqlDbType.Int).Value = id; com.ExecuteNonQuery(); com.Dispose(); con.Close(); GridView2.EditIndex = -1; bindgrid1(); } protected void GridView2_SelectedIndexChanging(object sender GridViewSelectEventArgs e) { GridView2.PageIndex = e.NewSelectedIndex; bindgrid1(); } Please suggest me.

Iftikar Hussain · Answer

Hi You can do like this DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(@Data Source=SQLEXPRESS;Initial Catalog=MyDb;Persist Security Info=True;User ID=MyUserId;Password=MyPassword)) { if (conn.State == ConnectionState.Closed) conn.Open(); string query = select ID1 SubcategorynameSimage DescriptionCatid where Catid=@Field; SqlCommand cmd = new SqlCommand(query conn); cmd.Parameters.AddWithValue(@Field dropdown1.SelectedItem); SqlDataAdapter dap = new SqlDataAdapter(cmd); dap.Fill(dt); if (conn.State == ConnectionState.Open) conn.Close(); conn.Dispose(); } gridview2.DataSource=dt; Regards Iftikar

Iftikar Hussain · Answer

what error you are getting to get category name just join the table select A.id1A.snameA.simageA.descB.cname subcategory A inner join category B ON B.id=A.cid where A.cid=@cid Regards Iftikar

antony victor · Answer

No luck sir There is error when i write the code select id1snamesimagedesc subcategory where cid=@cid; it works fine when i type select * from subcategory where cid=@cid; But by using above query i cant get cname into gridview  which is in the category table.

Iftikar Hussain · Answer

you can use below query to return all subcategory for give category id select id1snamesiamge desc where cid=@cid Regards Iftikar

antony victor · Answer

My category Table id cname cimage id (11) int; cname varchar(50); cimage varchar(250) My subcategory Table id1 sname simage desc cid id1 (11) int ; sname varchar(50); siamge varchar(250); desc varchar(250); cid int sp for category table Insert category CREATE PROCEDURE [dbo].[insert_category] ( @cname varchar(50) @cimage varchar(250) ) AS Begin declare @Id int declare @TempImgName varchar(20) Insert into category values (@cname@cimage) SELECT @Id=IDENT_CURRENT('category') --setting the image name with the category id SET @TempImgName=CAST(@Id as VARCHAr)+'_'+@cimage --updating the image name UPDATE category SET cimage=@TempImgName WHERE id=@Id --selecting the file name SELECT @TempImgName end Delete category CREATE PROCEDURE [dbo].[delete_category] (@id int) AS Begin Delete from category where id=@id end update category CREATE PROCEDURE [dbo].[update_category] ( @id int @cname varchar(50) ) AS Begin Update category set cname=@cname where id=@id end sp for Subcategory table Insert subcategory CREATE PROCEDURE [dbo].[insert_subcategory] ( @sname varchar(50) @simage varchar(250) @desc varchar(250) @cid int ) AS Begin Insert into subcategory values (@sname@simage@desc@cid) end Delete Subcategory CREATE PROCEDURE [dbo].[delete_subcategory] (@id1 int) AS Begin Delete from subcategory where id1=@id1; end Update Subcategory CREATE PROCEDURE [dbo].[update_subcategory] ( @id1 int @sname varchar(50) @desc varchar(250) ) AS Begin Update subcategory set sname =@sname where id1=@id1; end

Iftikar Hussain · Answer

can you please share your query? Regards Iftikar

antony victor · Answer

Dear Sir  I got an error saying SqlException was unhandled By UserCode. Incorrect syntax near the keyword 'desc'.

Sanjeeb Lenka · Answer

hi try this code: private void bindgrid1() { SqlConnection con = new SqlConnection(strConnString); con.Open(); DataSet ds = new DataSet(); string query = SELECT C.cname AS CategoryS.cidS.sname AS SubCategoryS.simage AS subcatimage S.desc AS description FROM subcategory S INNER JOIN Category c ON S.Cid=C.id where S.Cid=@cid; SqlCommand com = new SqlCommand(query con); com.Parameters.AddWithValue(@cid DropDownList1.SelectedValue); SqlDataAdapter sqlda = new SqlDataAdapter(com); sqlda.Fill(ds); if (con.State == ConnectionState.Closed) { con.Open(); } con.Close(); sqlda.Fill(ds); if (ds.Tables[0].Rows.Count == 0) { Label3.Visible = true; GridView2.Visible = false; Label3.Text = No images added yet; } else { Label3.Visible = false; GridView2.Visible = true; GridView2.DataSource = ds; GridView2.DataBind(); sqlda.Dispose(); con.Close(); } }

Iftikar Hussain · Answer

provide me your category and subcategory table design and the sp for pulling all subcategory details. Regards Iftikar

antony victor · Answer

Dear Sir  I m able to retreive all subcategory details but now a small correction In my subcategory Gridview  i also want to display cname(categoryname) which i pulled in ddl from category table... coz i dont have a cname(categoryname) in my subcategory table .

Iftikar Hussain · Answer

that is correct in category table id is PK where in subcategory table cid (FK - refereed to Category table id). You should able to retrieve all subcategory by cid Regards Iftikar

antony victor · Answer

Dear Sir In my gridview i am cascading data(cname) into ddl from previous category table. following is the below code private void bindgrid1() { SqlConnection con = new SqlConnection(strConnString); con.Open(); DataSet ds = new DataSet(); SqlCommand com = new SqlCommand(select * from subcategory where cid=@cid con); com.Parameters.AddWithValue(@cid DropDownList1.SelectedValue); SqlDataAdapter sqlda = new SqlDataAdapter(com); sqlda.Fill(ds); if (con.State == ConnectionState.Closed) { con.Open(); } con.Close(); sqlda.Fill(ds); if (ds.Tables[0].Rows.Count == 0) { Label3.Visible = true; GridView2.Visible = false; Label3.Text = No images added yet; } else { Label3.Visible = false; GridView2.Visible = true; GridView2.DataSource = ds; GridView2.DataBind(); sqlda.Dispose(); con.Close(); } } Design of my table is category id cname cimage subcategory id1 sname simage desc cid. Here what i have done is i am storing id value into ddl from category table and also storing the same into cid of subcategory table so that i can relate two tables. and one more thing is  i dont have cid common in both the tables. could you guide me please sir.

Sanjeeb Lenka · Answer

HI You want to show the Category Name in Subcategory gridview. or You want to show Category id. if you want Category name then try this query and bind it to grid: SELECT C.Cname AS CategoryS.CidS.sname AS SubCategoryS.simage AS subcatimage S.desc AS description FROM subcategory S INNER JOIN Category c ON S.Cid=C.Cid any problem reply

antony victor · Answer

Dear sir  I have a problem In my subcategory table  i have columns id1 sname simage desc cid But i want to display in gridview as category subcategory subcatimage description ... The category column  i populated it from category table onto dropdownlist in subcategorypage and that showed up in dropdownlist in subcategory page and i am able to delete rows via cid. The problem now is how do i display the category column in subcategory gridview ?

antony victor · Answer

sir  i am trying to figure out the error .. I ll keep u posted... I am really very thankful for the help  u provided..

Iftikar Hussain · Answer

may be earlier you have inserted twice in subcategory table. Please check this query for your category id. how many rows it is returing. select * from subcategory where cid=@cid Regards Iftikar

antony victor · Answer

Sir  In physical path it saves only once  but in gridview it loads twice....

Iftikar Hussain · Answer

try to create a new folder inside image say (catimage) and upload there..then you can identify weather it is uploading twice or not. Regards Iftikar

antony victor · Answer

sir no error in that line also... the flow goes correct.

Iftikar Hussain · Answer

then it should upload only one image and insert one record protected void btn_insert_Click(object sender EventArgs e) { if (FileUpload1.PostedFile.ContentLength > 0) { s1 = Path.GetFileName(FileUpload1.FileName); path = Server.MapPath(images) + / + s1; FileUpload1.SaveAs(path); } SqlConnection con = new SqlConnection(strConnString); con.Open(); SqlCommand com = new SqlCommand(insert_subcategory con); com.CommandType = CommandType.StoredProcedure; com.Connection = con; com.Parameters.AddWithValue(@sname TextBox1.Text); com.Parameters.AddWithValue(@simage s1); com.Parameters.AddWithValue(@desc TextBox2.Text); com.Parameters.AddWithValue(@cid DropDownList1.SelectedValue); com.ExecuteNonQuery(); com.Dispose(); bindgrid1(); con.Close(); clear(); } put the break point in the highlighted line Regards Iftikar

antony victor · Answer

Sir  i have run the code but no error over here.. it is fired once.

Iftikar Hussain · Answer

can you put break point on btn_insert_Click event and check how many times it fired when you click on the button. Regards Iftikar

antony victor · Answer

Dear Sir It uploads twice in my Physical path as well as in gridview which i used to bind data.

Iftikar Hussain · Answer

is the image is getting uploaded twice in your physical path or record get inserted twice? Regards Iftikar

antony victor · Answer

Thanks a ton It worked .. Can u suggest any corrections for the second subcategory page

antony victor · Answer

Create PROCEDURE [dbo].[insert_employee] ( @cname varchar(50) @cimage varchar(50) ) AS Begin Insert into category values (@cname@cimage) end This is the query in sp written..

Iftikar Hussain · Answer

what sql query you have written for insert_employee Regards Iftikar

antony victor · Answer

Sir Actually i have customised the code by taking from one of the site where they were implementing the employee details. I have kept the sp name same but changed the sp code in database. SqlCommand com = new SqlCommand(insert_employee con); SqlCommand com = new SqlCommand(delete_employee con); SqlCommand com = new SqlCommand(update_employee con); so i have changed it to SqlCommand com = new SqlCommand(insert_category con); Here i am implementing a Customer Management System where in if i try to upload the same image twice it uploads but when i try to delete any one of that  the other image link doesnt show up  but data still exists in database.

Iftikar Hussain · Answer

for the first issue are you uploading same image for different employee id? How each image is linked with employee? or is image is liked with each category id? Can you explain me? Regards Iftikar