unable to link tables and bind subcategory page to gridview

antony victor

13y2.5k.NET

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 .

31 Replies

Know the answer? Post it — somebody with the same question will find it here.

Iftikar Hussain13y ago
Accepted 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
Iftikar Hussain13y ago
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 victor13y ago
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 Hussain13y ago
you can use below query to return all subcategory for give category id

select id1,sname,siamge ,desc where cid=@cid

Regards,
Iftikar
antony victor13y ago
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 Hussain13y ago
can you please share your query?

Regards,
Iftikar
antony victor13y ago
Dear Sir ,

I got an error saying

SqlException was unhandled By UserCode.
Incorrect syntax near the keyword 'desc'.
Sanjeeb Lenka13y ago
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 Hussain13y ago
provide me your category and subcategory table design and the sp for pulling all subcategory details.

Regards,
Iftikar
antony victor13y ago
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 Hussain13y ago
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 victor13y ago
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 Lenka13y ago
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 victor13y ago
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 victor13y ago
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 Hussain13y ago
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 victor13y ago
Sir , In physical path it saves only once , but in gridview ,it loads twice....
Iftikar Hussain13y ago
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 victor13y ago
sir no error in that line also... the flow goes correct.
Iftikar Hussain13y ago
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 victor13y ago
Sir , i have run the code but no error over here.. it is fired once.
Iftikar Hussain13y ago
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 victor13y ago
Dear Sir, It uploads twice in my Physical path as well as in gridview which i used to bind data.
Iftikar Hussain13y ago
is the image is getting uploaded twice in your physical path or record get inserted twice?

Regards,
Iftikar
antony victor13y ago
Thanks a ton, It worked .. Can u suggest any corrections for the second subcategory page
antony victor13y ago
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 Hussain13y ago
what sql query you have written for insert_employee

Regards,
Iftikar
antony victor13y ago
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 Hussain13y ago
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
antony victor13y ago
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 Hussain13y ago
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