• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 553
  • Last Modified:

convert Sqldatareader to string...

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
0
damixa
Asked:
damixa
  • 4
  • 4
  • 2
  • +1
1 Solution
 
spdudeCommented:
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
0
 
jplevyakCommented:
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.)
0
 
jplevyakCommented:
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
----------------------------------------------------------------------------------
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
damixaAuthor Commented:
I don't receive an error, but the output is: System.Data.SqlClient.SqlDataReader
0
 
damixaAuthor 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
0
 
damixaAuthor 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
0
 
Jason ScolaroCommented:
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
0
 
Jason ScolaroCommented:
Ahh, sorry, guess I was a bit slow.. :)  Hope it's of some help nevertheless...

-- Jason
0
 
jplevyakCommented:
What is the exact error you get when using:

Label1.Text = Class1.connect("SELECT tekst FROM test_copy ORDER BY ID DESC").GetString(0)
0
 
damixaAuthor Commented:
"Invalid attempt to read when no data is present."
0
 
jplevyakCommented:
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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now