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("@id1",SqlDbType.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, Subcategoryname,Simage, Description,Catid 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.id1,A.sname,A.simage,A.desc,B.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 id1,sname,simage,desc 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 id1,sname,siamge ,desc where cid=@cid Regards, Iftikar

antony victor · Answer

My category Table id cname cimage id (1,1) int; cname varchar(50); cimage varchar(250) My subcategory Table id1 sname simage desc cid id1 (1,1) 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 Category,S.cid,S.sname AS SubCategory,S.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 Category,S.Cid,S.sname AS SubCategory,S.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

unable to link tables and bind subcategory page to gridview

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (31)