CODEWITHSUNDEEP
c#.netodbcinformix
Sharpeye500
Sharpeye500

Reputation: 9073

Informix database via odbc connection - Issue with insert stored procedure

This stored proc. - SP_insertinfo inserts an entry into a table.

I am connecting via an ODBC DSN to an informix database.

This is my code, this one doesn't throw me an error or doesn't insert a record.

I am connected via sequeLink 3.10 32-bit driver, my application runs on 64-bit OS.

Trying to identify why the data is not getting inserted(when i put a breakpoint, get the parameterized statement into the actual DB, there it inserts for the same data, however it fails when run from the application code).

int rowsInserted = command.ExecuteNonQuery(); //This line is always returning -1 and data doesn't get inserted.

Any thoughts/idea will be very much helpful?

 private void InsertInfo()
        {
            try
            {

                using(var connection = new OdbcConnection("dsn=mydsn;UID=myusername;PWD=****;"))
                {

                    var command = connection.CreateCommand();
                    command.CommandType = CommandType.StoredProcedure;
                    command.Connection = connection;
                    command.CommandText = "execute procedure SP_insertinfo(?,?)";


                     command.Parameters.Clear();

                     //Insert parameter values


                     var paramId = new OdbcParameter("ID", OdbcType.Int) { Value = Convert.ToInt32(txtID.Text.Trim()) };
                     command.Parameters.Add(paramId);


                     var paramCountry = new OdbcParameter("Country", OdbcType.VarChar, 25) { Value = txtCountry.Text.Trim() };
                     command.Parameters.Add(paramCountry);


                    connection.Open();


                    int rowsInserted = command.ExecuteNonQuery(); //This line is always returning -1 and data doesn't get inserted.

                    if (rowsInserted > 0)
                    {
                        MessageBox.Show("Insert data saved.");
                    }
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.ToString()) ;
            }




        }

Upvotes: 0

Views: 1671

Answers (2)

Sharpeye500
Sharpeye500

Reputation: 9073

After a long research, i found out that, in order for ODBC dsn to work correctly with sequeLink, the drivers should match the version of operating system, i am using windows 7.0 64-bit, my dsn was 32-bit , i used a 64-bit dsn in order to work with 64-bit OS.

Upvotes: 0

Michał Niklas
Michał Niklas

Reputation: 54322

At first try to execute your procedure as simple statement, not as prepared statement. This will look like:

command.CommandText = "execute procedure SP_insertinfo(1, 'Poland')";
connection.Open();
int rowsInserted = command.ExecuteNonQuery();

This way you will see if it is problem with prepared statement.

Try to execute execute procedure SP_insertinfo(1, 'Poland') via dbaccess (Informix tool). This way you will see if it is ODBC issue.

If it do not work with dbaccess then you will have to debug SP_insertinfo. If it work, then problem is with ODBC. Then I suggest enabling ODBC trace in ODBC Manager and analyzing log it will produce.

Upvotes: 1

Related Questions