CODEWITHSUNDEEP
.netsql-server
Shrujan
Shrujan

Reputation: 11

SQLException was unhandled in .net

I'm getting this run time exception

SQLException was unhandled

and the detail is

Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query.

The error is at line 

cmd.ExecuteNonQuery();

Code:

SqlConnection con = new SqlConnection("Data Source=localhost\\SQLEXPRESS;Initial Catalog=sample;Integrated Security=True;");
con.Open();

SqlCommand cmd = new SqlCommand("INSERT INTO patientinfo (patientname, patientid, gender, dob , contactaddress, contactno, doa , referreddoctor, diagnosis, medication, ward) VALUES ('" + patientname + "','" + patientid + "','" + gender + "','" + dtpdob.Value.ToString("dd/MM/yyyy") + "','" + contactaddress + "','" + contactno + "','" + dtpdoa.Value.ToString("dd/MM/yyyy") + "','" + referreddoctor + "','" + diagnosis + "','" + medication + "','" + wardno + "')",con);

cmd.ExecuteNonQuery();

con.Close();

MessageBox.Show("Details Saved ! ", "PatientInformationSystem", MessageBoxButtons.OK, MessageBoxIcon.Information);
clearall();

Please help

Upvotes: 0

Views: 45

Answers (1)

marc_s
marc_s

Reputation: 754268

You need to learn the basics of proper ADO.NET programming:

  • use the using() { ... } blocks to ensure proper disposal of disposable items, like SqlConnection and SqlCommand
  • NEVER EVER concatenate together your SQL statements, but use parametrized queries instead to avoid SQL injection (still the #1 vulnerability on the web!)

So in brief, I would rewrite your code something like this:

// define your parametrized query
string insertStmt = @"INSERT INTO patientinfo(patientname, patientid, gender, dob, contactaddress, contactno, doa, referreddoctor, diagnosis, medication, ward) 
                      VALUES (@patientname, @patientid, @gender, @dob, @contactaddress, @contactno, @doa, @referreddoctor, @diagnosis, @medication, @ward);"

// define your connection string - typically, you'd read this from a config file
string connectionString = "Data Source=localhost\\SQLEXPRESS;Initial Catalog=sample;Integrated Security=True;";

// wrap connection and command in using() blocks
using (SqlConnection con = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(insertStmt, con))
{
   // now add and setup the parameter list
   cmd.Parameters.Add("@patientname", SqlDbType.VarChar, 100).Value = patientname;
   cmd.Parameters.Add("@patientid", SqlDbType.Int).Value = patientid;
   ..... and so forth, until *ALL* parameters have been added and defined

   // open connection, execute command, close connection
   con.Open();
   int rowsAffected = cmd.ExecuteNonQuery();
   con.Close();
}

When doing this, it should become very clear which parameter is of type binary, and it should be obvious which string you need to convert to binary first, before assigning it to the parameter value.

Upvotes: 2

Related Questions