We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

convert Sqldatareader to string...

damixa
damixa asked
on
Medium Priority
850 Views
Last Modified: 2010-05-19
what I want to do is:
Label1.Text = Class1.connect("SELECT tekst FROM test_copy ORDER BY ID DESC")


but gets this error:
Error      10      Value of type 'System.Data.SqlClient.SqlDataReader' cannot be converted to 'String'.      C:\test2\Default.aspx.vb      38      23      http://localhost/test/


_________________________________
my class1.vb file

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient

Public Class Class1

    Public Shared Function connect(ByVal query As String) As SqlDataReader
        Dim connection As New SqlConnection("Server=server; Database=database; UID=admin; PWD=password;")
        connection.Open()
        Dim command As New SqlCommand(query, connection)
        Dim dataReader As SqlDataReader
        dataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
        Return dataReader
        connection.Close()
    End Function

End Class
Comment
Watch Question

Commented:
hi damixa

type tostirng at the end

Label1.Text = Class1.connect("SELECT tekst FROM test_copy ORDER BY ID DESC").toString();

see if it works
Commented:
You need to call one of the many Get[Type] methods on a DataReader to get the actual value using the column index (see http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader_methods(VS.80).aspx).

Label1.Text = Class1.connect("SELECT tekst FROM test_copy ORDER BY ID DESC").GetString(0)

or better yet, use the ExecuteScalar method instead of ExecuteReader. ExecuteScalar will simply return only the first column value of the first row of your query instead of a whole reader object. It returns the value as Object so you just need to add .ToString() at the end:

----------------------------------------------------------------------------
    Public Shared Function connect(ByVal query As String) As Object
        Dim connection As New SqlConnection("Server=server; Database=database; UID=admin; PWD=password;")
        connection.Open()
        Dim command As New SqlCommand(query, connection)
        Dim value As Object
        Return command.ExecuteScalar()
        connection.Close()
    End Function

Label1.Text = Class1.connect("SELECT tekst FROM test_copy ORDER BY ID DESC").ToString()
------------------------------------------------------------------------------

Of course if you want to be able to keep the function flexible for any type of query, just leave it as returning the DataReader, but remember to call the appropriate Get method for the type of column (GetInt32(0), GetBoolean(0), GetDateTime(0), etc.)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
Minor fix on that function I just posted. Forgot to return the value after calling the connection Close() method, otherwise function would exit leaving it open.

--------------------------------------------------------------------------------
  Public Shared Function connect(ByVal query As String) As Object
        Dim connection As New SqlConnection("Server=server; Database=database; UID=admin; PWD=password;")
        connection.Open()
        Dim command As New SqlCommand(query, connection)
        Dim value As Object
        value = command.ExecuteScalar()
        connection.Close()
        Return value
    End Function
----------------------------------------------------------------------------------

Author

Commented:
I don't receive an error, but the output is: System.Data.SqlClient.SqlDataReader

Author

Commented:
Label1.Text = Class1.connect("SELECT tekst FROM test_copy ORDER BY ID DESC").GetString(0)  doesn’t work

but

--------------------------------------------------------------------------------
  Public Shared Function connect(ByVal query As String) As Object
        Dim connection As New SqlConnection("Server=server; Database=database; UID=admin; PWD=password;")
        connection.Open()
        Dim command As New SqlCommand(query, connection)
        Dim value As Object
        value = command.ExecuteScalar()
        connection.Close()
        Return value
    End Function
----------------------------------------------------------------------------------
Label1.Text = Class1.connect("SELECT tekst FROM test_copy ORDER BY ID DESC").ToString()

WORKS!!!

thx m8 cheers

Author

Commented:
but if you easily can make :
Label1.Text = Class1.connect("SELECT tekst FROM test_copy ORDER BY ID DESC").GetString(0)
work I would like to see how
Top Expert 2006

Commented:
OK, in your initial question you have all sorts of problems.  1st of all, you are attempting to close the connection after the Return statement, which means the closing of the connection will never get called.  2nd, you're returning the SqlDataReader, but you're not executing the Read() method or accessing a specific column.

jplevyak is right on with the ExecuteScalar() method, IF you only need to return 1 value.  That method will attempt to read the 1st column of the 1st row's value and return it.  If you have multiple rows you need to return, then you'll need to stick with the ExecuteReader (or bind to a DataSet).  But it appears you're after a single value, so the ExecuteScalar() is what you need.

I'm going to assume the column, "tekst" is a String/Text/varchar field, so you could have code like this:

Public Shared Function connect(ByVal query As String) As String
  Dim connection As New SqlConnection("Server=server; Database=database; UID=admin; PWD=password;")
  Dim command As New SqlCommand(query, connection)
  Dim result As String = ""

  Try
    connection.Open()
    result = Convert.ToString(command.ExecuteScalar())
  Catch ex As Exception
    Throw ex
   Finally
     If connection.State = ConnectionState.Open Then connection.Close()
   End Try

   Return result
End Function

Then later in your code, you can bind to your Label with this:
Label1.Text = Class1.connect("SELECT tekst FROM test_copy ORDER BY ID DESC")

Good luck!
-- Jason
Top Expert 2006

Commented:
Ahh, sorry, guess I was a bit slow.. :)  Hope it's of some help nevertheless...

-- Jason

Commented:
What is the exact error you get when using:

Label1.Text = Class1.connect("SELECT tekst FROM test_copy ORDER BY ID DESC").GetString(0)

Author

Commented:
"Invalid attempt to read when no data is present."

Commented:
Scolja caught the problem, the missing call to the reader's Read() method. So if you are looking to use your original function see below:

-------------------------------------------------------------------------------------------------
  Public Shared Function connect(ByVal query As String) As SqlDataReader
        Dim connection As New SqlConnection("Server=server; Database=database; UID=admin; PWD=password;")
        connection.Open()
        Dim command As New SqlCommand(query, connection)
        Dim dataReader As SqlDataReader
        dataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
        dataReader.Read()  'added line
        Return dataReader
    End Function

Label1.Text = Class1.connect("SELECT tekst FROM test_copy ORDER BY ID DESC").GetString(0)
------------------------------------------------------------------------------------------------

The only problem with this is that the Class1.connect() method is going to open a Reader which you have no way to close unless you assign it to a variable first.

----------------------------------------------------
Dim reader As SqlDataReader = Class1.connect("SELECT tekst FROM test_copy ORDER BY ID DESC")
Label1.Text = reader.GetString(0)
reader.Close()
-----------------------------------------------------

The CommandBehavior.CloseConnection argument will automatically close the connection, but only when the reader is closed, which you have to remember to do on in the calling code. , which is why we suggest the ExecuteScalar method, because the function takes care of the closing.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.