Stuck on with ODBC-Parameters

Question

_{Thursday, March 21, 2013 7:23 PM}

Nearly an Hour i'll try to teach this Command to run, but there is an Problem i'll maybe dont see.

First of all the Function fires the Query:

        public int ExecuteQueryInt(OdbcCommand sql,string db=null)
        {
            this.open(db);
            try
            {
                sql.Connection = this.con;                
                object res = sql.ExecuteScalar();
                ret = Convert.ToInt32(res);
            }
            catch (OdbcException ex)
            {
                throw new uInfo.MyODBCException(ex.Message, ex, sql);
            }
            finally
            {
                this.con.Close();
            }            return ret;
        }

The Codeblock constructing the Command:

            DB db = new DB();
            OdbcCommand sql = new OdbcCommand("INSERT INTO vLog (log_usr,log_msg,logType_val) VALUES (?,?,?)");
            sql.Parameters.Add("log_usr",OdbcType.VarChar,255).Value = mUser.user;
            sql.Parameters.Add("log_msg", OdbcType.Text).Value = msg;
            sql.Parameters.Add("logType_val", OdbcType.VarChar,100).Value = type;
            db.ExecuteQueryInt(sql);

This Trigger only checks and inserts the logType_val (Maybe you have an better Solution for solving that)

ALTER TRIGGER [dbo].[ins_vLog] ON [dbo].[vLog]
INSTEAD OF INSERT
AS
Declare 
@log_usr varchar(255),
@logType_val varchar(100),
@logType_id int

SELECT @log_usr = log_usr FROM inserted;
SELECT @logType_val = logType_val FROM inserted;

IF (SELECT logType_id FROM tblLogType WHERE logType_val=@logType_val) is null
BEGIN
    INSERT INTO tblLogType (logType_val) VALUES (@logType_val)
END
SELECT @logType_id=logType_id FROM tblLogType where logType_val=@logType_val

INSERT INTO tblLog(logType_id,log_usr,log_msg) SELECT @logType_id, @log_usr, log_msg FROM INSERTED

I get always this Error:

ERROR [07S01] [Microsoft] [ODBC SQL Server Driver] Invalid use of default parameters

If i copy the Query out of Visual Studio and replace all this Questionmarks with the Content of the 3 Parameters the Query work like a Charm. But why not out of Visual Studio? 

Best Regards and thanks for helping

DAniel

All replies (3)

_{Thursday, March 21, 2013 10:08 PM ✅Answered}

Based on the error message, I don't believe you have provided enough information to identify the cause of your problem.  

Does your trigger execute from within the database?  As in, if you write a SQL Script that calls

INSERT INTO vLog (log_usr,log_msg,logType_val)values(blah,blah,blah)

does your trigger execute to your satisfaction?

The most common reason for that message seems to be that you attempted to insert a null into a column that is non nullable. However, I can't tell.  Perhaps you should separate the creation of your command parameters from their assignments and check their values before you call ExecuteScalar()

_{Friday, March 22, 2013 3:49 AM ✅Answered}

I think you need @ in front of the parameter name

sql.Parameters.Add("@log_usr",OdbcType.VarChar,255).Value = mUser.user;
           

Paul Linton

_{Friday, March 22, 2013 4:51 AM ✅Answered}

Is there a particular reason that you are using ODBC instead of OleDb? OleDb is the preferred provider to use. Specifically with SQL Server, you should be using the classes from the System.Data.SqlClient namespace, such as SqlCommand, SqlConnection and SqlDataAdapter.

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com