Syntax error in OLEDB Insert statement with parameters

[fbartolom]

Hullo,

I have a quite simple piece of code that returns a syntax error in the query execution: Have you got any idea about what might be wrong?

         public void SQLCommandWithParam(string name, byte[] image)
            {
            OleDbCommand sqlCommand1 = new OleDbCommand();
            System.Data.OleDb.OleDbConnection myOleConnection = new System.Data.OleDb.OleDbConnection();
            myOleConnection.ConnectionString=connectionString();
            myOleConnection.Open();
            sqlCommand1.Connection = myOleConnection;
            try
                {
                if (sqlCommand1.Parameters.Count ==0 )
                    {
                    sqlCommand1.CommandText = "INSERT INTO Image (Name, Immagine) " + 
                           "VALUES(@Name, @Picture);";
                    sqlCommand1.Parameters.Add("@Name",
                             System.Data.OleDb.OleDbType.VarChar, 200);
                    sqlCommand1.Parameters.Add("@Picture",
                             System.Data.OleDb.OleDbType.Binary);
                    }
                sqlCommand1.Parameters["@Name"].Value = name;
                sqlCommand1.Parameters["@Picture"].Value = image;
                sqlCommand1.Connection = myOleConnection;
                int iresult=sqlCommand1.ExecuteNonQuery();
                MessageBox.Show(Convert.ToString(iresult));
                }
            catch(Exception ex)
            {
            MessageBox.Show(ex.Message);
            }
 }

[tlhintoq]

TIP: When you are writing your question, there is a button on the tool bar that wraps the [code] tags around your copy/pasted code. It helps a bunch. Its the button with a '#' on it. More on tags. They're cool. Check'em out.

[fbartolom]

I immediately follow your suggestion in posting another try, this time with an ODBCDrvriver: here the error appears at connection time and reports a reference to an unassigned object. This is the new code:

         
public void SQLCommandWithParam(string name, byte[] image)
         {
            OdbcConnection conn = new OdbcConnection();
            conn.ConnectionString = @"Dsn=.\DataBase\BRP.mdb";
            conn.Open();
            string sqlQuery = "INSERT INTO Image ([Name], [Picture]) " +
                           "VALUES(@Name, @Picture);";
            OdbcCommand dbcommand = new OdbcCommand(sqlQuery, conn);
            dbcommand.Parameters.Add("@Name", OdbcType.VarChar,200).Value = name;
            dbcommand.Parameters.Add("@Picture", OdbcType.VarBinary).Value = image;

            try
            {
                int count = dbcommand.ExecuteNonQuery();
            }
            catch (OdbcException ex)
            {
                MessageBox.Show(ex.Message);
            }
          }

[tlhintoq]

reports a reference to an unassigned object

"How do I fix a 'object reference not set to an instance of an object' error?

The line your code stopped on while debugging holds all your answers.
One of the variables/objects was created but not initialized. For example:

string TempString;// Created but not initialized so this is still null
//versus
string TempString = string.empty;

Debug your project again. This time, when it breaks on a line look at the Locals pallet to see which variable/object is null. You can also hover your mouse over each variable and the hothelp will shows its value. One of them will be null.

[fbartolom]

So far I can get myself on my own feet..., after all I am in programming since my graduation 20 years ago, albeit not in the .Net environment and with gaps.

The problem is that the error is returned _inside_ the opening of the connection where the debugger has no way to enter.
In the previous case it was _inside_ the query execution, exactly in the same situation.

[fbartolom]

In the following the revised message: please delete the present one if possible, I am not able to do it myself.

[fbartolom]

In brief the only involved variable is conn, the connection, which has all values assigned but the Server Version and Base that cannot be otherwise because the connection is closed: what is not suprising given what is applied to the conn variable is the open operation. This is the expanded pallette.
Of course that might all boil down to the connection string I got from various internet sites: another one I found is the following, returning the same error: at the bottom the pallette for this other configuration:

Alternative connection string:

            OdbcConnection con = new OdbcConnection(@"{Microsoft Access Driver (*.mdb)};DSN=.\DataBase\BRP.mdb;Uid=Admin;Pwd=;");

First pallette:

-		conn	{System.Data.Odbc.OdbcConnection}	System.Data.Odbc.OdbcConnection
+		base	{System.Data.Odbc.OdbcConnection}	System.Data.Common.DbConnection {System.Data.Odbc.OdbcConnection}
		ConnectionString	"Dsn=.\\DataBase\\BRP.mdb"	string
		ConnectionTimeout	15	int
		Database	""	string
		DataSource	""	string
		Driver	""	string
-		ServerVersion	'conn.ServerVersion' ha generato un'eccezione di tipo 'System.InvalidOperationException'	string {System.InvalidOperationException}
-		base	{"Operazione non valida. La connessione è chiusa."}	System.SystemException {System.InvalidOperationException}
-		base	{"Operazione non valida. La connessione è chiusa."}	System.Exception {System.InvalidOperationException}
+		Data	{System.Collections.ListDictionaryInternal}	System.Collections.IDictionary {System.Collections.ListDictionaryInternal}
		HelpLink	null	string
+		InnerException	null	System.Exception
		Message	"Operazione non valida. La connessione è chiusa."	string
		Source	"System.Data"	string
		StackTrace	"   in System.Data.ProviderBase.DbConnectionClosed.get_ServerVersion()\r\n   in System.Data.Odbc.OdbcConnection.get_ServerVersion()"	string
+		TargetSite	{System.String get_ServerVersion()}	System.Reflection.MethodBase {System.Reflection.RuntimeMethodInfo}
+		Membri statici		
+		Membri non pubblici

Second pallette:

-		con	{System.Data.Odbc.OdbcConnection}	System.Data.Odbc.OdbcConnection
-		base	{System.Data.Odbc.OdbcConnection}	System.Data.Common.DbConnection {System.Data.Odbc.OdbcConnection}
+		base	{System.Data.Odbc.OdbcConnection}	System.ComponentModel.Component {System.Data.Odbc.OdbcConnection}
		ConnectionString	"{Microsoft Access Driver (*.mdb)};DSN=.\\DataBase\\BRP.mdb;Uid=Admin;Pwd=;"	string
		ConnectionTimeout	15	int
		Database	""	string
		DataSource	""	string
-		ServerVersion	'((System.Data.Common.DbConnection)(con)).ServerVersion' ha generato un'eccezione di tipo 'System.InvalidOperationException'	string {System.InvalidOperationException}
+		base	{"Operazione non valida. La connessione è chiusa."}	System.SystemException {System.InvalidOperationException}
		State	Closed	System.Data.ConnectionState
+		Membri non pubblici		
		ConnectionString	"{Microsoft Access Driver (*.mdb)};DSN=.\\DataBase\\BRP.mdb;Uid=Admin;Pwd=;"	string