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