Hello
I have question about SqlDataReader.

In my sql table when ResetNo is INT then this code doesn't work, even when I first get the value as integer then change to string. But this works fine when I set in sql table ResetNo as nvarchar, and of course I just get it as string and I don't need to change from integer to string.

Why does this not work when I have ResetNo as INT?????
I really want to keep ResetNo as INT and not Nvarchar.


This is the code when I keep ResetNo as INT and not nvarchar.
Why doesn't it work???
Thank you.

Public Shared Function GetResetNo(ByVal sEmail As String) As String
        Dim myConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("C onnectionString"))


        Dim sSql As String = "SELECT ResetNo FROM OCVBUG_Member WHERE Email = @Email"
        Dim selectCmd As New SqlCommand(sSql, myConn)

        selectCmd.CommandType = CommandType.Text
        selectCmd.Connection = myConn

        selectCmd.Parameters.Add("@Email", SqlDbType.NVarChar, 100)
        selectCmd.Parameters("@Email").Value = Replace(sEmail, "'", "''")
        Dim iResetNo As Integer
        Dim sResetNo As String

        Try

            Dim da As SqlDataReader

            myConn.Open()
            da = selectCmd.ExecuteReader(CommandBehavior.SingleRow)
            da.Read()
            iResetNo = da.Item("ResetNo")
            sResetNo = Cstr(iResetNo)
            myConn.Close()

        Catch ex As Exception
            sResetNo = "Error"


        Finally
            If myConn.State = ConnectionState.Open Then
                myConn.Close()
            End If
        End Try

        Return sResetNo

    End Function

Hey,

Try:

da.GetInt32("ResetNo")

Brian

Hello bmains,
iResetNo = da.GetInt32("ResetNo")
doens't work

What can be the problem?????

Hey,

Well then, what is the actual error that you are getting? From what I see, everything is fine. Also let me know what line of code the error is from.

Brian

You probably need a 'do while' loop in front of the da.Read()

Try

            Dim da As SqlDataReader

            myConn.Open()
            da = selectCmd.ExecuteReader(CommandBehavior.SingleRow)
            da.Read()
            iResetNo = da.GetInt32("ResetNo")
            sResetNo = CStr(iResetNo)
                        myConn.Close()

        Catch ex As Exception
            sResetNo = "Error"


        Finally
            If myConn.State = ConnectionState.Open Then
                myConn.Close()
            End If
        End Try

        response.write("sResetNo")
---------------------------------------------------------------------
stu9820
Are you sure I need to do a do while loop in front of the da.Read()
 because I am only getting one row: da = selectCmd.ExecuteReader(CommandBehavior.SingleRow) .

When I run this the exception gets thrown.

Hey,

You probably don't need to loop through. Also, change response.write("sResetNo") to Response.Write(sResetNo) to find the response. Are you getting the text Error, or are you actually getting an exception? If you are getting an exception, it's because o something outside the try catch block.

Brian