SQLException was unhandled in .net

Question

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

what are the type of each field (schema of patientinfo)? What is the type of each variable that you are using in insert? — SMA, Nov 02 '14 at 08:59
[**SQL Injection alert**](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should ***NOT*** concatenate together your SQL statements like this - ***ALWAYS*** use **parametrized queries** instead to avoid SQL injection — marc_s, Nov 02 '14 at 09:01
The error just says that you seem to be trying to insert a `varchar` (string) into a column that's of type `binary`. So which of your columns is of type `binary`?? Make sure to insert a binary value into that column - if you have a string to insert, you need to **CONVERT** it to binary upon insert — marc_s, Nov 02 '14 at 09:03

score 2 · Answer 1 · edited Nov 02 '14 at 10:18

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.

SQLException was unhandled in .net

1 Answers1