ExecuteScalar returns nothing when there is something

  • Thread starter Thread starter zdrakec
  • Start date Start date
Z

zdrakec

Hello all:

Please consider the following snippet:

Dim cn As New OdbcConnection
Dim cm As New OdbcCommand
Dim result As Object
cn.ConnectionString = AValidConnectionString
cn.Open()
cm.Connection = cn
cm.CommandType = CommandType.Text
cm.CommandText = AValidQuery
cm.Parameters.Add(AValidODBCParameterObject)
result = cm.ExecuteScalar

Although the query is a straightforward select that will
return at most one row and one column, and the query, when run in Query
Analyzer, in fact returns this value, ExecuteScalar is returning
Nothing.

The query is simple: "SELECT lastname FROM users WHERE userkey = ?"
The parameter is equally simple: name = "@userkey",value = 25 (for
example).
The only anomaly I can find is when I inspect the parameter, it
declares itself to have a DbType of String, and ODBCDbType of NVarChar,
when it should be Int32 and Int, respectively. It retains this setting
even when I explicitly set it.

Any ideas?

Thanks much,
zdrakec
 
Before you execute, peer in to the commandtext. It should show you what will
be passed to the odbc connection.

You can explicitly cast the paramater value like this
oParam = new OdbcParamater("@CategoryName", OdbcType.VarChar, 80)
oParam.Value = "toasters"

Since you are using unnamed paramaters. Just before you execute, check to
see how many paramaters you have in the collection. Maybe yours is
overlooked. Try changing your query to this

'Select LastName from users where userkey = @userkey'

or

..paramaters.clear, .paramaters.add, .executescalar

If all this fails, do these steps

Dim obj as object
obj = new object
.....
obj.dispose()

where obj is each of your odbc objects (Connection and Command)



if that still doesn't work, try executing without a paramater and just
concatinating the sql string to see if you get the desired result.
 
Hello AMDIRT:

Actually, I did indeed peek at the command text, and I also ran it
without a parameter, that is, as "SELECT lastname FROM users WHERE
userkey = 25". All with the same result.
I'll try assigning the type in the constructor, and see how that does
me.
I'm also verifying that I at least have SP3 for SQL Server installed,
in case that makes any difference.

Thanks,
zdrakec
 
You have the where clause of 'userkey=?' in your query, but you are adding a
parameter named '@userkey'?

For SQL server, you need to have named parameters, not placeholder
parameters.

Your query needs to be something like "SELECT lastname FROM users WHERE
userkey=@userkey".

Also, why not use the SqlClient namespace since you are going to SQL server?

Lastly, you should use the profiler tool to see exactly what is being sent
to SQL server. This will include the query and values of all parameters.
 
Hello Marina:

Given my preferences, I would be using SQLClient, and named parameters
in the query string, but alas, I must use a connection string that is
already created and is stored in our database. As it happens, it is an
ODBC connection string; it doesn't work when I use SQLClient.
Similarly, when I use a named parameter with my ODBC connection, if
fails; when I use the placeholder ?, it works admirably... except in
this instance. I give the parameter a name for the sake of
completeness. This format for my query, "SELECT fields FROM tables
WHERE fields = ?", works quite well in this scenario.
Which profiler tool are you referring to? I have been using my
immediate window to verify to contents of my command text and parameter
object...

I could, were I not using ExecuteScalar elsewhere to good effect,
create a DataAdapter to fill a table, then get table.rows(0).item(0),
but I'd like to figure out why this is occuring with ExecuteScalar.

Thanks,
zdrakec
 
Thanks for your responses, it turns out that the problem was a bit
different, and I should have realized it quicker. The database
specified in my connection string is not the one I need to query, so I
needed to do:

connectionobject.ChangeDatabase(correctDatabaseName)

before doing my ExecuteScalar.

Thanks again,
zdrakec
 
I have recreated the environment that you eluded to, I was unable to
replicate your problem. Let me know where we differ:

Created a system DSN on my local machine named ggg
Specified all the defaults with the following variations
Database=MyDatabase
Security = SQL
UserID & Password

Dim myConnection As Odbc.OdbcConnection
Dim myCommand As Odbc.OdbcCommand
Dim MyData As Object
Dim MyConnectionString as string = "DSN=ggg;uid=username;pwd=password;"

myConnection = New Odbc.OdbcConnection(MyConnectionString)
myConnection.Open()

myCommand = New Odbc.OdbcCommand
myCommand.CommandType = CommandType.Text
myCommand.Connection = myConnection
myCommand.CommandText = "Select BinderNumber from Applications where
ApplicationID = ?"
myCommand.Parameters.Add(New Odbc.OdbcParameter("@ApplicationID", 15646))

MyData = myCommand.ExecuteScalar

If Not MyData Is Nothing Then
MsgBox(CType(MyData, String)) --> 'BA0500102"
End If

myConnection.Close()

myCommand.Dispose()
myConnection.Dispose()
 
Since you are using multiple databases, shouldn't you consider implemeting
fully qualified names?

dbname.dbowner.dbtablename

Perhaps it would save on frustration as long as your server is correct.
 
Back
Top