Embata Embata - 1 month ago 13
C# Question

I try to make Qr code application ( generated and then save it on DataBase) , but have some errors with my program code

I am getting this two errors


'SqlConnection' does not contain a definition for 'Parameters' and no extension
method 'Parameters' accepting a first argument of type 'SqlConnection' could be found (are you missing a using directive or an assembly reference?)


and


'SqlConnection' does not contain a definition for 'ExecuteNonQuery' and no extension method 'ExecuteNonQuery' accepting a first argument of type 'SqlConnection' could be found (are you missing a using directive or an assembly reference?)


Source Error:

SqlConnection con1;
con1 = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\User1\Documents\ESDB.mdf;Integrated Security=True;Connect Timeout=30");
con1.Open();
string qry = "Insert into QRCODES(Image) VALUES(@PIC)";
SqlConnection cmd = new SqlConnection(qry);
MemoryStream STREAM = new MemoryStream();
pictureBox1.Image.Save(STREAM,System.Drawing.Imaging.ImageFormat.Jpeg);
byte[] pic = STREAM.ToArray();
cmd.Parameters.AddWithValue("@PIC", pic);
cmd.ExecuteNonQuery();
cmd.Dispose();
con1.Close();


Now when i re-build my code with


SqlCommand cmd = new SqlCommand


I get new error ( warning ) and it say :


An unhandled exception of type 'System.NullReferenceException' occurred in WindowsFormsApplication1.exe
Additional information: Object reference not set to an instance of an object.

Answer

You are using two SqlConnection objects. It should be SqlCommand:

SqlConnection con1;
con1 = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\User1\Documents\ESDB.mdf;Integrated Security=True;Connect Timeout=30");
con1.Open();
string qry = "Insert into QRCODES(Image) VALUES(@PIC)";
SqlCommand cmd = new SqlCommand(qry);
^^^^^^^^^^           ^^^^^^^^^^
MemoryStream STREAM = new MemoryStream();
pictureBox1.Image.Save(STREAM, System.Drawing.Imaging.ImageFormat.Jpeg);
byte[] pic = STREAM.ToArray();
cmd.Parameters.AddWithValue("@PIC", pic);
cmd.ExecuteNonQuery();
cmd.Dispose();
con1.Close();

Also be careful with that code, because you're using commands and connections and you're not disposing them. It can lead you to memory leaks and several problems.

Maybe you should think in refactor to something like this:

using (SqlConnection con1 = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\User1\Documents\ESDB.mdf;Integrated Security=True;Connect Timeout=30"))
{
    con1.Open();
    string qry = "Insert into QRCODES(Image) VALUES(@PIC)";
    using (SqlCommand cmd = new SqlCommand(qry))
    {
        using (MemoryStream STREAM = new MemoryStream())
        {
            pictureBox1.Image.Save(STREAM, System.Drawing.Imaging.ImageFormat.Jpeg);
            byte[] pic = STREAM.ToArray();

            cmd.Parameters.AddWithValue("@PIC", pic);
            cmd.ExecuteNonQuery();
            con1.Close();
        }
    }
}

UPDATE:

Your problem, as the error says, is that the command and the connection are not associated. This should work:

using (SqlConnection con1 = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\User1\Documents\ESDB.mdf;Integrated Security=True;Connect Timeout=30"))
{
    con1.Open();
    string qry = "Insert into QRCODES(Image) VALUES(@PIC)";
    using (SqlCommand cmd = con1.CreateCommand()) //associate the connection to the command
    {
        cmd.CommandText = qry; //assign the command text to the command.
        using (var STREAM = new MemoryStream())
        {
            pictureBox1.Image.Save(STREAM, System.Drawing.Imaging.ImageFormat.Jpeg);
            byte[] pic = STREAM.ToArray();

            cmd.Parameters.AddWithValue("@PIC", pic);
            cmd.ExecuteNonQuery();
            con1.Close();
        }
    }
}
Comments