Data Type Error

[dr223]

Hallo,

I have the following piece of Code in my application shown below,

Try
Dim Oracle_num As New System.Data.SqlClient.SqlCommand(("SELECT Oracle_no FROM gprdsql.TblOracleNos WHERE Prac_no='" & _
Me.TxtPracNo.Text & "' and Prac_eid = '" & _
Me.TxtPracEid.Text & "' and Pay_method = '" & _
Me.CmbPayMethod.Text & "' "), conn)

Using reader As System.Data.SqlClient.SqlDataReader = Oracle_num.ExecuteReader()

While reader.Read()

Dim Oracle_no As String = FixNull(reader.GetValue(0))

TxtOracleNo.Text = Oracle_no

End While

End Using

Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information, "GCPM")
End Try



When I run the application and try to save when the TxtOracleNo textbox is null (no value) then the following error is prompted,
"Error converting data type varchar to numeric" when you ok it the data is saved, regardless. In the database oracle_no is set as numeric data type.

The error is the red highlighted code. So I tried the following;

Dim Oracle_no As Integer = FixNullInteger(reader.GetValue(0))

This doesnt work and the application fails to run, highlighting the error on the above code. Also note, the error is only prompted if you are saving when the TxtOracleNo has no value and save button clicked but if it is populated by a value it saves ok without any error prompted.


Any ideas!!!
Thank you

[DataMiser]

I am surprised the code runs. You have a dim statement inside a loop. I've never tried this in .net but I know in vb6 this will result in a duplicate declaration error.

That said you say the error occurs when you try to save the data but the code you posted is what you would use to read the data rather than save it.

Where is the save code? That is likely where the real problem is.

[Scott.Macmaster]

I agree. Your post is a little confusing. The code you are showing is loading data. Unless when you say save you are talking about putting the value from the database into the text box.

If that block of code is having a problem it's probably in your FixNull() and FixNullInteger() function. I'm assuming you wrote them since I never seen them before.

I've written similar functions. This may help,

    <Extension()> Public Function ReadField(ByVal reader As SqlDataReader, ByVal fieldName As String, ByVal defaultValue As String) As String

        Dim value As String = defaultValue
        If reader(fieldName) Is DBNull.Value = False Then
            value = CStr(reader(fieldName)).Trim()
        End If

        Return value
    End Function

Of course, I have several of these for different data types.

I prefer to use field names over field index espacially since it's so easy to change the field order in your select statement.

By the way, in VB.NET you can declare variable pretty much anyway. Also, if you don't need a variable outside of a loop it's considered best practice to declare the variable in the loop.

[DataMiser]

By the way, in VB.NET you can declare variable pretty much anyway. Also, if you don't need a variable outside of a loop it's considered best practice to declare the variable in the loop.

Good to know. I will try it next time I am working on a project and see what happens. I guess .net ignores the repeated declarations within the loop and destroyes the var when the loop is complete?

[Scott.Macmaster]

A loop creates a level of scope. I'm guessing internally VB.NET moves all the declarations before the loop. They would of course go out of scope when the loop is done. They'll be destroyed whenever the garbage collector gets around to it. However, the compiler will give you an error if you try to reference the variable outside of the loop.