[RESOLVED] Datareader doubt

[LuxCoder]

This code is not giving the msgbox value! Is the GetValue method incorrect?

 Dim myDBConnection As SqlClient.SqlConnection = Nothing
        Dim myDBCommand As SqlClient.SqlCommand = Nothing
        Dim myDBReader As SqlClient.SqlDataReader = Nothing
        Try
            myDBConnection = New SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=D:\ProjectMW\MachineWale\MachineWale\DatabaseMW.mdf;Integrated Security=True;User Instance=True")
            myDBConnection.Open()
            myDBCommand = New SqlClient.SqlCommand("SELECT MAX(ClientID) FROM ClientProfile", myDBConnection)
            myDBReader = myDBCommand.ExecuteReader(CommandBehavior.CloseConnection)
            If myDBReader.HasRows Then
                myDBReader.Read()
                MsgBox(myDBReader.GetValue("ClientID") + 1)
                'Me.TextBox2.Text = myDBReader.Item("ClientID".ToUpper) + 1
            Else
                Me.TextBox2.Text = "1000"
            End If
        Catch ex As Exception
            MsgBox(" Error in Connection! ")
            Exit Try
            Exit Sub
        Finally
            'CLEAN UP DB OBJECTS
            If myDBReader IsNot Nothing Then myDBReader.Close()
            If myDBCommand IsNot Nothing Then myDBCommand.Dispose()
            If myDBConnection IsNot Nothing Then myDBConnection.Close()
        End Try

[jmcilhinney]

Have you read the documentation for the GetValue method? Obviously not, because the documentation would have told you that the parameter is type Integer, not String. If you want to get the value by column name then you need to use the Item property. Also, if the ClientID column contains Integers then, if you do get the value by index rather than name, you should call GetInt32.

Off topic a bit, let's look at how you can improve the way you construct your code. Mainly, you should be employing Using blocks to create and destroy your disposable objects. That way you could do away with that Finally block:

Try
    Using connection As New SqlConnection("...")
        connection.Open()
 
        Using command As New SqlCommand("...", connection)
            Using reader As SqlDataReader = command.ExecuteReader()
                '...
            End Using
        End Using
    End Using
Catch ex As Exception
    '...
End Try

In the Catch block you really MUST actually look at the exception to see what went wrong. How many different things could go wrong in that code and you are just saying "Error in Connection!" for all of them. In many cases the connection could be fine.

[LuxCoder]

I tried using item as ClientID is stored as integer in the DB. But still i am not getting it's value.

Try
            Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=D:\ProjectMW\MachineWale\MachineWale\DatabaseMW.mdf;Integrated Security=True;User Instance=True")
                connection.Open()
                Using command As New SqlCommand("SELECT MAX(ClientID) FROM ClientProfile", connection)
                    Using reader As SqlDataReader = command.ExecuteReader()
                        If reader.HasRows Then
                            reader.Read()
                            MsgBox((reader.Item("ClientID")) + 1)
                        Else
                            Me.TextBox2.Text = "1000"
                        End If
                    End Using
                End Using
            End Using
        Catch ex As Exception
            MsgBox(ex.ToString, MsgBoxStyle.Exclamation)
        End Try

[jmcilhinney]

I didn't actually look at your SQL code before. Your result set has no ClientID column so you can't get a value from it. You are getting the ClientID column; you're getting the MAX of the ClientID column. The results of expressions don't inherently use the names of the columns they contain because they could contain any number of columns. If you want the value to be named ClientID then you have to specify that with an alias:

SELECT MAX(ClientID) AS ClientID FROM ClientProfile

I would suggest "MaxClientID" though, to be more accurate.

That said, if you're only retrieving a single value then you shouldn't be using a DataReader at all. Check out my thread of ADO.NET code examples, which you can get to from the Database FAQ link or Codebank link in my signature, and look for the ExecuteScalar example.

[LuxCoder]

I used msgbox(reader(1) + 1) and it worked. I still want to have a look at ExecuteScalar. Tried to find but could not. Can u gimme it's link?

[Shaggy Hiker]

ExecuteScalar is similar to ExecuteReader, except that it just returns a single value. In your case, I think the value is an integer (though it could be a double, I suppose), so you would do something like this (using the SQL statement and command object that you have already created in your current code):

msgbox(command.ExecuteScalar.ToString)

Actually, I'm a little unclear on that. Normally, I'd assign the return value to a variable like this:

myInt = CInt(command.ExecuteScalar)

whether or not you can use .ToString as I show, or need to use CStr(command.ExecuteScalar), is something you will find out really fast.

ExecuteScalar returns the value in the first column of the first row of the set of rows returned by the SQL SELECT statement.

[jmcilhinney]

I used msgbox(reader(1) + 1) and it worked. I still want to have a look at ExecuteScalar. Tried to find but could not. Can u gimme it's link?

The thread is called "Retrieving and Saving Data in Databases".