how to get data from sql server in asp.net

How to get data from sql server in asp.net


To retrieve data/record from SQL database in asp.net steps

1. Create a table and insert value in Sql Database

Create table ItemDetails
(
ItemId int NOT NULL,
Name Varchar(30)NOT NULL,
Quantity int NOT NULL,
Price float NOT NULL,
Category varchar(30)NOT NULL,
Country varchar(20)NOT NULL,
Remarks Varchar(250)NULL
)



Insert into ItemDetails Values('1','Dell Desktop Computer','10','540','Computer','Japan',NULL)
Insert into ItemDetails Values('2','Dell Server','5','500','Computer','USA',NULL)
insert into ItemDetails Values('3','Acer Desktop Computer','50','200','Computer','Korea',NULL)
Insert into ItemDetails Values('4','Acer Laptop','20','250','Computer','Korea',NULL)
Insert into ItemDetails Values('5','Dell Laptop','300','150','Computer','USA',NULL)
Insert into ItemDetails Values('6','Apple Mac Book','40','600','Computer','USA',NULL)
insert into ItemDetails Values('7','Samsung Laptop','20','200','Computer','USA',NULL)
Insert into ItemDetails Values('8','MSI Desktop Computer','40','200','Computer','China',NULL)
Insert into ItemDetails Values('9','MSI Laptop','15','250','Computer','China',NULL)
Insert into ItemDetails Values('10','Toshiba','5','200','Computer','USA',NULL)

2. Create ASP.Net From like bellow:

[Note:
ItemCode to txtItemCode
ItemName to txtName
Category to txtCategory
Quantity to txtQty Price to txtPrice
Country to txtCountry
Desc to txtDesc
]

3. Type following code inside the button event:

   

protected void btnFind_Click(object sender, EventArgs e)


        {


            try


            {


                SqlConnection con;


                con = new SqlConnection("Server=Kool-PC;Database=CenterDB;user=sa;password=a;trusted_connection=yes");


                con.Open();


                SqlCommand cmd;


                cmd = new SqlCommand("Select ItemId,Name,Quantity,Price,Category,Country, Remarks From ItemDetails Where ItemId=@ItemCode", con);


                cmd.Parameters.Add("@ItemCode", SqlDbType.VarChar, 20).Value = txtItemCode.Text.Trim();


                SqlDataReader dr = cmd.ExecuteReader();


                if(dr.HasRows)


                {


                    dr.Read();


                    txtName.Text = dr["Name"].ToString();


                    txtCategory.Text=dr["Category"].ToString();


                    txtQty.Text=dr["Quantity"].ToString();


                    txtPrice.Text=dr["Price"].ToString();


                    txtCountry.Text=dr["Country"].ToString();


                    txtDesc.Text = dr["Remarks"].ToString();                 



                }


                else


                {


                    Page.ClientScript.RegisterStartupScript(Page.GetType(), "Message Box", "<script language = 'javascript'>alert('ItemDoes not exist, choose correct ItemCode')</script>");


                }


            }


            catch(Exception ex)


            {


                lblerror.Text = ex.Message;


            }





        }