CODEWITHSUNDEEP
c#sql-server
Jomarlyn Tamargo
Jomarlyn Tamargo

Reputation: 17

Parameter Data type vs Database Data Type

About parameters SQL data type?

In my SQL Server database table I defined the data type there.

My question is in parameters and database data type they should be the same data type right?

Sample Enumber data type is INT in SQL Server and in parameter SqlDataType is also INT?

But when I run it say data type need to convert string to int32?

Is there something wrong in my data type?

Student

// Adding data to table Employee from database
cmd.CommandText = "Insert into Employee(Enumber,Inumber,Pnumber,Fname,Mname,Sname,Age,Bdate,Gender,Mstatus,Nationality,Eaddress,Cnumber,Picture) Values (@Enumber,@Inumber,@Pnumber,@Fname,@Mname,@Sname,@Age,@Bdate,@Gender,@Mstatus,@Nationality,@Eaddress,@Cnumber,@Picture)";

cmd.Parameters.Add("@Enumber",SqlDbType.Int).Value = txtEnumber.Text;
cmd.Parameters.Add("@Inumber",SqlDbType.Int).Value = txtInumber.Text;
cmd.Parameters.Add("@Pnumber",SqlDbType.VarChar,20).Value = txtPassport.Text;
cmd.Parameters.Add("@Fname",SqlDbType.VarChar,50).Value =  txtFname.Text;
cmd.Parameters.Add("@Mname",SqlDbType.VarChar,50).Value = txtMname.Text;
cmd.Parameters.Add("@Sname",SqlDbType.VarChar,50).Value = txtSname.Text;
cmd.Parameters.Add("@Age", SqlDbType.Int).Value = txtAge.Text;
cmd.Parameters.Add("@Bdate",SqlDbType.VarChar).Value = DtpBdate.Text;
cmd.Parameters.Add("@Gender",SqlDbType.VarChar,20).Value = cbGender.Text;
cmd.Parameters.Add("@Mstatus",SqlDbType.VarChar,20).Value = cbMstatus.Text;
cmd.Parameters.Add("@Nationality",SqlDbType.VarChar,30).Value = txtNationality.Text;
cmd.Parameters.Add("@Eaddress",SqlDbType.VarChar,50).Value =  txtEAddress.Text;
cmd.Parameters.Add("@Cnumber",SqlDbType.Int).Value = txtCnumber.Text;
cmd.Parameters.Add("@Picture",SqlDbType.VarChar).Value =  ImageToBase64(PbImage.Image, System.Drawing.Imaging.ImageFormat.Jpeg);

cmd.ExecuteNonQuery();

Upvotes: 0

Views: 76

Answers (1)

Steve
Steve

Reputation: 216243

This error is caused by one or more of your TextBox used to fill an Int parameter being empty. In this case the internal code that tries to convert your string value assigned to an Int parameter will fail. This happens only at the execution time not at the assignement because the Parameter.Value property is defined as object.

A possible workaround could be

cmd.CommandText = "Insert into Employee(Enumber,Inumber,Pnumber,.....)";
cmd.Parameters.Add("@Enumber",SqlDbType.Int).Value = 
     string.IsNullOrWhiteSpace(txtEnumber.Text) ? "0" : txtEnumber.Text ;

But this is not a good solution because your user could type anything in those textboxes. So you really should add a validation level before attempting the insert and add a real integer as value.

int enumber;
if(!Int32.TryParse(txtENumber.Text, out enumber))
{
   MessageBox.Show("Type a number");
   return;
}

cmd.Parameters.Add("@Enumber",SqlDbType.Int).Value = enumber;

This applies to other textboxes as well.

Upvotes: 3

Related Questions