Updating Data from multiple tables

[manavsm]

I have passed values from one page to another using query
but it is not accepting the update i.e When i click on update button nothing happens

SqlConnection con = new SqlConnection("Data Source=SNSS1\\SQLEXPRESS;Initial Catalog=Employee;User ID=sa;Password=eLog!234");

        int empid = Convert.ToInt32(Request.QueryString["ID"].ToString());
        string strSQL = " UPDATE Tbl_Employee SET FirstName=@FirstName,LastName=@LastName,EmpID=@EmpID,PhoneNumber=@PhoneNumber,Salary=@Salary WHERE ID="+empid ;
        //string strSQL2 = " UPDATE Tbl_CountryStateCity SET CountryName=@FirstName,StateName=@StateName,CityName=@CityName WHERE ID="+ empid;
        SqlDataAdapter da = new SqlDataAdapter();
        da.UpdateCommand = new SqlCommand(strSQL, con);
        da.UpdateCommand.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text;
        da.UpdateCommand.Parameters.Add("@LastName", SqlDbType.VarChar).Value = TextBox2.Text;
        da.UpdateCommand.Parameters.Add("@EmpID", SqlDbType.VarChar).Value = TextBox3.Text;
        da.UpdateCommand.Parameters.Add("@PhoneNumber", SqlDbType.VarChar).Value = TextBox4.Text;
        da.UpdateCommand.Parameters.Add("@Salary", SqlDbType.VarChar).Value = TextBox5.Text;

[dnanetwork]

SqlConnection con = new SqlConnection("Your Connection String);
        SqlCommand comm = new SqlCommand();
        comm.CommandType = System.Data.CommandType.Text;
        comm.CommandText = "UPDATE Tbl_Employee SET FirstName=@FirstName,LastName=@LastName,EmpID=@EmpID,PhoneNumber=@PhoneNumber,Salary=@Salary WHERE ID=@ID";
        comm.Connection = con;

        comm.Parameters.AddWithValue("@FirstName", "Your TextBox Value");
        comm.Parameters.AddWithValue("@LastName", "Your TextBox Value");
        comm.Parameters.AddWithValue("@EmpID", "Your TextBox Value");
        comm.Parameters.AddWithValue("@PhoneNumber", "Your TextBox Value");
        comm.Parameters.AddWithValue("@Salary", "Your TextBox Value");
        comm.Parameters.AddWithValue("@ID", "Query String Value");
        // first check does querystring retirns something ..?
        // 
        con.Open();

        try
        {
            int result = comm.ExecuteNonQuery();
            if (result > 0)
                Response.Write("Success");
            else
                Response.Write("whoops you have a call from Jigsaw ");
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message.ToString());
        }

try this code..

and always check the null condition and also
handle exceptions..

hope that helps.

[manavsm]

I get the error as
Conversion failed when converting the varchar value 'FirstName' to data type int

n i never assigned it as int newhere...

n 1 more question if i place the below code

cmd.Parameters.AddWithValue("@LastName", textbox1.text);///here also nothing happens or shud something else come

[rohand]

Just make sure the datatype in database is Varchar not int. it may be possible by mistake you or someone has changed it.

I get the error as
Conversion failed when converting the varchar value 'FirstName' to data type int

n i never assigned it as int newhere...

n 1 more question if i place the below code

cmd.Parameters.AddWithValue("@LastName", textbox1.text);///here also nothing happens or shud something else come

[manavsm]

Thanks again...i solved it....i didnt put (!IsPostBack) in the page load

But one more problem now is to update the following procedure code

ALTER PROCEDURE [dbo].[EMP_SearchEmployee]
@FirstName varchar(50),
@LastName varchar(50),
@EmpID varchar(50),
@CountryName varchar(50),
@StateName varchar(50),
@CityName varchar(50)	

AS
BEGIN	

SELECT Tbl_Employee.ID,Tbl_Employee.FirstName,Tbl_Employee.LastName,Tbl_Employee.EmpID,Tbl_Employee.PhoneNumber,Tbl_Employee.Salary,Tab1.[Name] as CountryName,Tab2.[Name] as StateName,Tab3.[Name] as CityName
FROM Tbl_Employee
left JOIN Tbl_CountryStateCity Tab1 ON Tbl_Employee.CountryID=Tab1.ID
left JOIN Tbl_CountryStateCity Tab2 ON Tbl_Employee.StateID=Tab2.ID
left JOIN Tbl_CountryStateCity Tab3 ON Tbl_Employee.CityID=Tab3.ID
WHERE (Tbl_Employee.FirstName LIKE  '%'+@FirstName+'%') AND (Tbl_Employee.LastName LIKE '%'+@Lastname+'%') AND (Tbl_Employee.EmpID LIKE  '%'+@EmpID+'%') AND(Tab1.[Name] LIKE '%'+@CountryName+'%') AND (Tab2.[Name] LIKE '%'+@StateName+'%') AND (Tab3.[Name] LIKE '%'+@CityName+'%')



 
END

I just need the CountryStateTable to be updated...or if there is ne efficient way of updating instead of updating 1 by 1 den ur always welcome