ODBCcommand executescalar returns nothing

Migrating a OLEDB SQL Server data connected asp.net web application to ODBC (Driver 13 for SQL Server).

The first ODBCcommand in the application runs a stored procedure with ExecuteScalar command. The command executes but returns Nothing. The same code in the old OLEDB application succesfully returns data from the stored procedure. Here is the stored procedure and the  (vb.net) code:

####################################################
PROCEDURE [dbo].[spGetConfigValue]
      @ItemName varchar(max) = '',
      @ConfigID numeric=0
      
AS
      Select ItemValue from tblConfiguration
      Where ItemName = @ItemName
      and ConfigID = @ConfigID
      RETURN




 Dim cmd As Odbc.OdbcCommand
            Dim objTemp As Object = Nothing
            objTemp = GetCacheItem("Config" & strName)
            If objTemp Is Nothing Then
                cmd = GetCommand("spGetConfigValue")
                cmd.Parameters("@ItemName").Value = strName
                cmd.Parameters("@ConfigID").Value = GetWebConfigValue("ConfigID")
                objTemp = cmd.ExecuteScalar()

                If objTemp Is Nothing Or IsDBNull(objTemp) Then
                    Return ""
                Else
                    SetCacheItem("Config" & strName, objTemp)
                End If
            End If

            Return objTemp
#########################################################

Any help would be gratefully received.
Al230762Asked:
Who is Participating?
 
Al230762Connect With a Mentor Author Commented:
So it seems that the correct method and syntax for calling ODBC stored procedures from a web application is as follows:

cmd = GetCommand("{call spGetConfigValue (?,?)}")
                cmd.Parameters.AddWithValue("@ItemName", strName)
                cmd.Parameters.AddWithValue("@ConfigID", GetWebConfigValue("ConfigID"))

The GetCommand function previously (with OLEDB) received simply the name of the SP as its CommandText and then collated SP details like connection and parameter requirements but now with ODBC we need to pass a more complex string which includes a "call" command and a placeholder ("?") for each parameter.

Thank you for the responses received.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Unless you can share with us the stored procedure code, it will be hard to help you.
0
 
Al230762Author Commented:
Vitor:  here is the stored procedure which should have been visible in my question:

PROCEDURE [dbo].[spGetConfigValue]
      @ItemName varchar(max) = '',
      @ConfigID numeric=0
     
AS
      Select ItemValue from tblConfiguration
      Where ItemName = @ItemName
      and ConfigID = @ConfigID
      RETURN
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
So what do you want to return?
The SP has a RETURN but no value to be returned.
0
 
Al230762Author Commented:
It is a select query which returns an ItemValue . The SP and vb.net code works fine in the previous OLEDB version.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try to store the itemvalue in a variable and the return that variable from the stored procedure.
0
 
Al230762Author Commented:
Vitor: when I hard-code the parameter value into the SP like so:

ALTER PROCEDURE [dbo].[spGetConfigValue]
      @ItemName varchar(max) = 'ThemeName',
      @ConfigID numeric=0

I do receive data when the command is executed from the application code.

So for some reason my parameter values in the vb code are not being passed to the SP, even though I can see that "strName" quite clearly has the value "ThemeName" when I debug:

 cmd.Parameters("@ItemName").Value = strName
 cmd.Parameters("@ConfigID").Value = GetWebConfigValue("ConfigID")

Is there something wrong with this syntax for an odbc command?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Nothing that I can see from here but you told that you're using a new OLEDB version, right?
Check for the compatibility data types in this new OLEDB version.
0
 
Al230762Author Commented:
Vitor: no, we have switched from OLEDB to ODBC (Driver 13 for SQL Server). We have cross-checked data types between OLEDB and ODBC and cannot see any variations that would apply in this case.

I thought there might be an issue with the varchar(max) parameter so I changed that to 1) @ItemName varchar = '' and then 2) @ItemName varchar (30) = ''

... but still no luck.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Can you check if the numeric parameter is taken over correctly? E.g. use a SP which does nothing else than return the numeric input value?
0
 
Al230762Author Commented:
Qlemo - yes, so if the only paramater value I add at runtime is:
  cmd.Parameters("@ConfigID").Value = GetWebConfigValue("ConfigID"):

and I change the SP as follows:

      @ItemName varchar (max) = '',
      @ConfigID numeric=0      
AS
      Select ItemValue from tblConfiguration
      Where ConfigID = @ConfigID and ItemName = 'ThemeName'

... the command runs fine. So as you suggest, the problem appears to be not with the numeric input value but with the varchar(max) value.
0
 
Al230762Author Commented:
Apologies, in the previous example it was a coincidence that the query worked because the @ConfigID parameter value we were trying to pass to the SP was "0" - - the same as the default value in the SP. When I change the default value in the SP to:  @ConfigID numeric= -1 the query does not work.

So it appears that neither of the parameter values is being passed to the SP from the application code! Are we sure this isn't a syntax issue with ODBC (Driver 13 for SQL Server) ?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
That confirms what I have found today when researching: ODBC does only allow for "anonymous" positional parameters, not named ones. It seems not to have changed with ODBC 13, which is regrettable to a huge amount - a step back, as ODBC is the proclaimed future path to go with MS SQL ...
0
 
Al230762Author Commented:
Qlemo: thank you for confirming this. It appears to be a security measure to prevent injection attacks but as you say, it's certainly not a step forward.
0
 
Al230762Author Commented:
Although grateful for the contributions of others I feel they did not help me in finding the solution which was eventually achieved through a combination of online research and trial-and-error.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.