SQL anywhere query error: Not enough values for ho

问题:

I am building a query using ODBC command object in .Net with multiple parameters being passed in. When executing the query against SQL Anywhere, I get the following error. (The same code works against SQL Server).

[System.Data.Odbc.OdbcException] = {"ERROR [07002] [Sybase][ODBC Driver][SQL Anywhere]Not enough values for host variables"}

The command object has the same number of parameters added as the place holders ('?') in the query. Following is a simple query and C# code that fails the test.

C# code to populate the host variables

String queryText= @"DECLARE @loanuseraddress varchar(40), @loanid decimal Set @loanid = ? Set @loanuseraddress = ? select * from loan_assignments where loan_id = @loanid"

        OdbcConnection connection = new OdbcConnection(request.ConnectionString);
        OdbcCommand command;    

        command = new OdbcCommand(queryText, connection);

        OdbcParameter param1 = new OdbcParameter("@loanid", OdbcType.Decimal);
        param1.Value = request.Loan.LoanNumber;
        command.Parameters.Add(param1);

        OdbcParameter param2 = new OdbcParameter("@loanuseremployer", dbcType.VarChar);
        param2.Value = appraisalCompanyUpdate.LoanUserEmployer;
        if (param2.Value == null)
            param2.Value = DBNull.Value;
        command.Parameters.Add(param2);


        connection.Open();  
        OdbcDataReader rows = command.ExecuteReader();

回答1:

I fixed this by checking for nulls. When you try to pass a null parameter to Sybase, that's the error you get (at least for me). Have a feeling LoanId is null at some point.

Edit After doing a little more research, I think you can also get this error when you try multiple insert / deletes / updates through the Sybase ODBC Connection in .Net. I don't think this is supported and MSDN seems to say it's vendor specific.

回答2:

"Insufficient host variables" can also mean something else but it's applicable to the OP: one of the other causes could be that you have a set of declared variables different from the set your SQL statement is using.

E.g. this could be a typo, or you could have copied in SQL from Visual Studio that was used to fill a dataset table using parameters (like :parm) but in doing so you forgot to declare it (as @parm) in your stored proc or begin/end block.