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 address,ownername all are NVARCHAR if you are not going to store other type of culture language (other than english) than declare as VARCHAR only and phonenumer,housenumber 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 Viswanath · 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(18,0), @Sex nvarchar(20), @Relation nvarchar(20), @Occupation nvarchar(20), @houseId numeric(18,0) AS insert into tbl_Person(houseId,personName, 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 Viswanath · 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 Viswanath · 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 Viswanath · 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 Viswanath · 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 Viswanath · 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 Viswanath · Answer

The code for HouseAdd 1) SQL SP ALTER PROCEDURE houseAdd @houseNumber numeric(18,0), @Address nvarchar(20), @phoneNumber numeric(18,0), @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 Viswanath · 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

DatagridView SAVE Funtion

Answers (18)