Reading Single Value from a SQL DB

[Jim in Arizona]

I'm having trouble pulling a single value from my SQL DB and filling a
string type with it.

Something like:

Dim strConnection As String =
"server=localhost;uid=sa;pwd=password;database=tickets"
Dim objConnection As New SqlConnection(strConnection)
Dim strSQLSubject As String
Dim objCommand As SqlCommand
Dim objReader As SqlDataReader
Dim test As String

strSQLSubject = "SELECT subject FROM TTickets WHERE PK = " & txtID.text
objConnection.Open()
objCommand = New SqlCommand(strSQLSubject, objConnection)
objCommand = New SqlCommand(strSQLSubject, objConnection)
objReader = objCommand.ExecuteReader

test = objReader.Read

Response.Write(test)

objConnection.Close()

This is probably the long way of doing it, I'm sure. It doesn't work
anyway. Its been a while since I've worked with this stuff and if you
don't use it, you lose it.

I'm actually trying to send an email with a single value out of the DB
wtihin the subject line of the email. I got the emailing part down now
(thanks paul), I just can't get this DB part right.

Thanks,
Jim

 

[sloan]

You want to look at the ExecuteScalar method:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatasqlclientsqlcommandclassexecutescalartopic.asp

Its a cheaper operation than ExecuteReader, when you know you only want 1
value.

 

[Guest]

Try usiing ExecuteScalar, which returns a single item of type Object. You can
then cast this to the type you need.

objReader.Read simply sets the pointer at the first row of the Reader, you
would still need to use getInt, getString or whatever else you need to read
the column value.

Hope this helps.

Peter

 

[Jeff]

You might instead use the ExecuteScalar() method of your command object.
It's designed specifically to return a single value.

something like:
test = objCommand.ExecuteScalar().ToString();
or
test = Convert.ToString(objCommand.ExecuteScalar());

-HTH

 

[Jim in Arizona]

You want to look at the ExecuteScalar method:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatasqlclientsqlcommandclassexecutescalartopic.asp

Its a cheaper operation than ExecuteReader, when you know you only want 1
value.

Thanks. Your link didn't work but I went to
http://msdn.microsoft.com/library/default.asp and did a search for
ExecuteScalar and found what I needed (probably where you intended me to
go).

My resulting code:

Dim strConnection As String =
"server=localhost;uid=sa;pwd=password;database=tickets"
Dim objConnection As New SqlConnection(strConnection)
Dim strResult As String
Dim strSQL As String = "SELECT field2 FROM TTickets WHERE PK = " &
txtID.Text
Dim objCommand As New SqlCommand(strSQL, objConnection)
objCommand.Connection.Open()
strResult = objCommand.ExecuteScalar().ToString
objConnection.Close()
Response.Write(strResult)

Thanks everyone for the quick responses.

Jim