David Rudlin
asked on
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("spGetConfigVal ue")
cmd.Parameters("@ItemName" ).Value = strName
cmd.Parameters("@ConfigID" ).Value = GetWebConfigValue("ConfigI D")
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.
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("spGetConfigVal
cmd.Parameters("@ItemName"
cmd.Parameters("@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.
Unless you can share with us the stored procedure code, it will be hard to help you.
ASKER
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
PROCEDURE [dbo].[spGetConfigValue]
@ItemName varchar(max) = '',
@ConfigID numeric=0
AS
Select ItemValue from tblConfiguration
Where ItemName = @ItemName
and ConfigID = @ConfigID
RETURN
So what do you want to return?
The SP has a RETURN but no value to be returned.
The SP has a RETURN but no value to be returned.
ASKER
It is a select query which returns an ItemValue . The SP and vb.net code works fine in the previous OLEDB version.
Try to store the itemvalue in a variable and the return that variable from the stored procedure.
ASKER
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("ConfigI D")
Is there something wrong with this syntax for an odbc command?
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"
cmd.Parameters("@ConfigID"
Is there something wrong with this syntax for an odbc command?
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.
Check for the compatibility data types in this new OLEDB version.
ASKER
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.
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.
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?
ASKER
Qlemo - yes, so if the only paramater value I add at runtime is:
cmd.Parameters("@ConfigID" ).Value = GetWebConfigValue("ConfigI D"):
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.
cmd.Parameters("@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.
ASKER
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) ?
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) ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ...
ASKER
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.
ASKER
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.