vb.net dataset returning null value

Question

Hi I've googled for this...it's been 2 days...this null stuff really cracking my head! ...please someone tell me is there anything wrong with my code ????

here it is :-

Public Sub checkemail()
    sqlcheckemail = "select Email from WEBUSER where Email='" + TBEmail.Text + "'"
    Dim dscheckemail As New DataSet
    Dim sqlCnn As New SqlConnection
    'Dim MYNULL As String
    sqlCnn = New SqlConnection(connStr)
    sqlCmd = New SqlCommand(sqlcheckemail, sqlCnn)
    sqlCnn.Open()
    da.SelectCommand = sqlCmd
    da.Fill(dscheckemail)
    'MYNULL = CheckDBNull(dscheckemail)
    'If Not (dscheckemail.Tables.Count > 0) AndAlso (dscheckemail.Tables(0).Rows.Count > 0) Then
    'If Not IsDBNull(dscheckemail) Then
    'If Not (dscheckemail Is Nothing) Then
    'If MYNULL = "NULL" Then
    If Not dscheckemail Is Nothing Then
        LabelGender.Text = "There is something"
        'MsgBox("Unable to register because the E-mail address has already registered as a user, Please register using different Email address or contact administrator")
        'Response.Redirect("~/Rnewuser.aspx")
    Else
        'MsgBox("u can register")
        LabelGender.Text = "NULL"
    End If

    sqlCmd.Dispose()
    sqlCnn.Close()
End Sub

if u see the one that i've commented is the one i already googled at tested...so now if the email address exist in the db..everything works wonderful..but if it doesn't exist the code should process the one that does not satisfy the first if statement...but now i'm getting the result as "There is something" eventhough the email does not exist in the db....

PLEASE HELP ME!!!! I've done this null thingy before..but long time back and i didn't save the codes..now i'm doing it back for my own website..n it's cracking me

Solution 1

The dataset is not nothing thats why is entering the if, you should check the datatable inside.

'is not nothing, but is safer this way
If Not dscheckemail Is Nothing Then

    'Check there is a datatable inside the dataset and that it has rows
    If dscheckemail.Tables(0) Is Nothing OrElse dscheckemail.Tables(0).Rows.Count = 0 Then
        'is really empty
    Else
        'You have a dataTable with data.
    End If

End If

Solution 2

You are making life a little too difficult on yourself, your code is open to sql injection, and your resources are not guaranteed to be disposed. Fortunately, these are all easily correctable.

The following code uses the SQL Server EXISTS statement so that you can easily determine whether or not there are any records in the database with the requested email address. If there are, a 1 is returned, otherwise a 0 is returned, which means you don't have to test for nulls.

Since we know there is only going to be one value returned, we can change the execution of the command to use executescalar.

The code also uses a parameter in the command to prevent SQL injection.

And finally, we wrap our disposable objects in using statements to ensure they are properly disposed of.

Here is the modified code:

Public Sub checkemail()
    Using sqlCnn As New SqlConnection(connStr)
        Dim sqlcheckemail As String = "IF EXISTS(select 1 FROM WEBUSER WHERE Email=@Email) SELECT 1 ELSE SELECT 0"
        Using sqlCmd As New SqlCommand(sqlcheckemail, sqlCnn)
            sqlCmd.Parameters.AddWithValue("@Email", TBEMail.Text)
            sqlCnn.Open()

            If CBool(sqlCmd.ExecuteScalar) Then
                LabelGender.Text = "There is something"
                'MsgBox("Unable to register because the E-mail address has already registered as a user, Please register using different Email address or contact administrator")
                'Response.Redirect("~/Rnewuser.aspx")
            Else
                'MsgBox("u can register")
                LabelGender.Text = "NULL"
            End If
        End Using
        sqlCnn.Close()
    End Using
End Sub

Comments

[rathu]

thanks competent_tech...really appreciate it..tested it and it works great!!!.. never know there is function CBool..thanks so much :D