DatagridView SAVE Funtion

Question

Sir this is the result when putting break point.

Iftikar Hussain · Answer

modify the below code like this public int HouseAdd(houseInfo infoHouse) { int houseId; try { if (sqlCon.State == ConnectionState.Closed) { sqlCon.Open(); } SqlCommand sqlCmd = new SqlCommand(houseAdd sqlCon); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.AddWithValue(@houseNumber infoHouse.houseNumber); sqlCmd.Parameters.AddWithValue(@Address infoHouse.Address) ; sqlCmd.Parameters.AddWithValue(@phoneNumber infoHouse.phoneNumber); sqlCmd.Parameters.AddWithValue(@ownerName infoHouse.ownerName); sqlCmd.Parameters.AddWithValue(@Photo infoHouse.Photo); houseId = Convert.ToInt32(sqlCmd.ExecuteScalar()); if (houseId > 0) { Common.MessageAdd(); } } catch (Exception) { throw; } finally { sqlCon.Close(); } return houseId; } tell me some question why you have declared addressownername all are NVARCHAR if you are not going to store other type of culture language (other than english) than declare as VARCHAR only and phonenumerhousenumber numeric phonenumber housenumber can't be in decimal in real time so better declare as SMALINT if you thing housenumber can contain alphanumeric then declare as VARCHAR Regards Ifitkar

Iftikar Hussain · Answer

Please mark it answer so that it will help others. Regards Iftikar

Bineesh VP · Answer

Sir I changed the code in sqlSp sp class. Now finally I get the result. Thank you sir for having guided me about two days to get the output. here the modified code. 1) SQL Sp ALTER PROCEDURE personAdd @personName nvarchar(20) @Age numeric(180) @Sex nvarchar(20) @Relation nvarchar(20) @Occupation nvarchar(20) @houseId numeric(180) AS insert into tbl_Person(houseIdpersonName Age Sex Relation Occupation) values(@houseId@personName @Age @Sex @Relation @Occupation) SELECT @houseId as houseId 2) Sp class(pesonSP) public void PersonAdd(personInfo InfoPerson) { try { if (sqlCon.State == ConnectionState.Closed) { sqlCon.Open(); } SqlCommand sqlCmd = new SqlCommand(personAdd sqlCon); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.AddWithValue(@personName InfoPerson.personName); sqlCmd.Parameters.AddWithValue(@Age InfoPerson.Age); sqlCmd.Parameters.AddWithValue(@Sex InfoPerson.Sex); sqlCmd.Parameters.AddWithValue(@Relation InfoPerson.Relation); sqlCmd.Parameters.AddWithValue(@Occupation InfoPerson.Occupation); sqlCmd.Parameters.AddWithValue(@houseId SqlDbType.Decimal).Value = InfoPerson.houseId; int incount = sqlCmd.ExecuteNonQuery(); if (incount > 0) { Common.MessageAdd(); } else { MessageBox.Show(Already Exists); } } catch (Exception) { throw; } finally { sqlCon.Close(); } } Thank You.

Iftikar Hussain · Answer

try by modifying your code now and check the result Regards Iftikar

Bineesh VP · Answer

here is the Info class codes:- 1) houseInfo class houseInfo { public decimal houseId { get; set; } public int houseNumber { get; set; } public string Address { get; set; } public int phoneNumber { get; set; } public string ownerName { get; set; } public byte[] Photo { get; set; } 2) personInfo class personInfo { public decimal houseId { get; set; } public string personName { get; set; } public int Age { get; set; } public string Sex { get; set; } public string Relation { get; set; } public string Occupation { get; set; }

Iftikar Hussain · Answer

yes before that share me the complete code for adding house info and then person info Regards Iftikar

Bineesh VP · Answer

Sir I modified the code you gave me right now. again I getting the error i posted recently. Can I modify the PersonAdd(Sql SP SP class ) same as HouseAdd to get output?

Iftikar Hussain · Answer

yesterday you got the same error then I have modified your code again today you are getting public int HouseAdd(houseInfo infoHouse) { int houseId; try { if (sqlCon.State == ConnectionState.Closed) { sqlCon.Open(); } SqlCommand sqlCmd = new SqlCommand(houseAdd sqlCon); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.AddWithValue(@houseNumber infoHouse.houseNumber); sqlCmd.Parameters.AddWithValue(@Address infoHouse.Address) ; sqlCmd.Parameters.AddWithValue(@phoneNumber infoHouse.phoneNumber); sqlCmd.Parameters.AddWithValue(@ownerName infoHouse.ownerName); sqlCmd.Parameters.AddWithValue(@Photo infoHouse.Photo); houseId = Convert.ToInt32(sqlCmd.ExecuteScalar()); if (houseId > 0) { Common.MessageAdd(); } } catch (Exception) { throw; } finally { sqlCon.Close(); } return houseId; } Regards Iftikar

Bineesh VP · Answer

Sir this is my current error. I know that houseId is not passing to the tbl_Person in the PersonAdd function. how can it possible?

Iftikar Hussain · Answer

are you able to save the owner name now? Regards Iftikar

Bineesh VP · Answer

SP class function  personAdd:- public void PersonAdd(personInfo InfoPerson) { try { if (sqlCon.State == ConnectionState.Closed) { sqlCon.Open(); } SqlCommand sqlCmd = new SqlCommand(personAdd sqlCon); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.AddWithValue(@personName InfoPerson.personName); sqlCmd.Parameters.AddWithValue(@Age InfoPerson.Age); sqlCmd.Parameters.AddWithValue(@Sex InfoPerson.Sex); sqlCmd.Parameters.AddWithValue(@Relation InfoPerson.Relation); sqlCmd.Parameters.AddWithValue(@Occupation InfoPerson.Occupation); int incount = sqlCmd.ExecuteNonQuery(); if (incount > 0) { Common.MessageAdd(); } } catch (Exception) { throw; } finally { sqlCon.Close(); } }

Muralidharan Deenathayalan · Answer

http://www.codeproject.com/Articles/21371/SQL-Server-Profiler-Step-by-Step

Bineesh VP · Answer

could you give me the code to (Use sql profiler to hook what values sent during person add that will tell you sp parameters and its values. )

Muralidharan Deenathayalan · Answer

Use sql profiler to hook what values sent during person add that will tell you sp parameters and its values. Please post sp for person add and sql profiler trace values also.

Bineesh VP · Answer

The code for HouseAdd 1) SQL SP ALTER PROCEDURE houseAdd @houseNumber numeric(180) @Address nvarchar(20) @phoneNumber numeric(180) @ownerName nvarchar(20) @Photo image AS DECLARE @houseId int insert into tbl_House( houseNumber Address phoneNumber ownerName Photo) values( @houseNumber @Address @phoneNumber@ownerName@Photo) SET @houseId = @@identity SELECT @houseId as houseId 2)SP class public int HouseAdd(houseInfo infoHouse) { int houseId; try { if (sqlCon.State == ConnectionState.Closed) { sqlCon.Open(); } SqlCommand sqlCmd = new SqlCommand(houseAdd sqlCon); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.Add(@houseNumber SqlDbType.Decimal).Value = infoHouse.houseNumber; sqlCmd.Parameters.Add(@Address SqlDbType.NVarChar).Value = infoHouse.Address; sqlCmd.Parameters.Add(@phoneNumber SqlDbType.Decimal).Value = infoHouse.phoneNumber; sqlCmd.Parameters.Add(@ownerName SqlDbType.NVarChar).Value = infoHouse.ownerName; sqlCmd.Parameters.Add(@Photo SqlDbType.Image).Value = infoHouse.Photo; houseId = Convert.ToInt32(sqlCmd.ExecuteScalar()); if (houseId > 0) { Common.MessageAdd(); } } catch (Exception) { throw; } finally { sqlCon.Close(); } return houseId; } 3) btnSave_Click private void btnSave_Click(object sender EventArgs e) { try { personInfo infoPerson = new personInfo(); houseInfo infoHouse = new houseInfo(); personSP SpOwner = new personSP(); houseSP spHouse = new houseSP(); infoHouse.houseNumber = decimal.Parse(txtHouseNumber.Text); infoHouse.Address = txtAddress.Text; infoHouse.ownerName = txtowner.Text; infoHouse.phoneNumber = decimal.Parse(txtFonNo.Text); infoHouse.Photo = FamilyPhoto; decimal houseId = spHouse.HouseAdd(infoHouse); if (dgvFamilyReg.RowCount > 0) { for (int i = 0; i { string col1 = dgvFamilyReg.Rows[i].Cells[txpersonName].Value.ToString(); string col2 = dgvFamilyReg.Rows[i].Cells[txAge].Value.ToString(); string col3 = dgvFamilyReg.Rows[i].Cells[cmbSex].Value.ToString(); string col4 = dgvFamilyReg.Rows[i].Cells[cmbRelation].Value.ToString(); string col5 = dgvFamilyReg.Rows[i].Cells[txOccupation].Value.ToString(); infoPerson.houseId = houseId; infoPerson.personName = col1; infoPerson.Age = decimal.Parse(col2); infoPerson.Sex = col3; infoPerson.Relation = col4; infoPerson.Occupation = col5; SpOwner.PersonAdd(infoPerson); } } } catch (Exception ex) { MessageBox.Show(ex.Message); } }

Iftikar Hussain · Answer

share me the code for adding house details Regards Iftikar

Bineesh VP · Answer

Sir the function HouseAdd is going well. Both HouseAdd and PersonAdd is worked when I remove fk relationship from tbl_Person( but not worthy) . Then in tbl_Person houseId is Zero and all other data are inserted successfully. the database table of tbl_House is:- Also one thing no value being added to the column ownerName.

Iftikar Hussain · Answer

Hi Can you check your database what data has been inserted for the house? Regards Iftikar