2 questions on ado.net ...

I'm finally writin my first .net project using ado.net. What I need to do is check whether any of the rows in my sql database table contain a username & password I pass to i.

First off, is this the right way of going about this, then also, none of the examples in my wrox books list the set dataset = nothing equivalent, I guess the GAC does this for you now, but wondered if it's considered bad to put the setting to nothing options in with .net?

VB Code:

strSQL = "SELECT UserId, UserPassword FROM Users Where UserId ='" & _
        strUserName & "' AND UserPassword='" & strUserPassword & "'"
 
objDAUsers = New SqlClient.SqlDataAdapter(strSQL, gstrDEVSQLSERVERCONN)
 
If Not (objDAUsers Is Nothing) Then
    objDAUsers.Fill(objDSUserDetails)
 
    If Not (objDSUserDetails Is Nothing) Then
 
        Select Case objDSUserDetails.Tables(0).Rows.Count
        Case 0
            LoginPassedValidation = False
        Case 1
            LoginPassedValidation = True
        End Select
 
    End If
End If

Thanks all!

Right, just looked at this a bit more & I'll answer my own question 1 incase I need to come back to it. :) For a password check, it looks as through a forward, read-only datareader object is more efficeient to use.

As for the second question, on the setting the connection & datareader/dataadapter objects to nothing, that one's still outstanding. ;)

If Not (objDSUserDetails Is Nothing) Then

put instead : if objDSUserDetails.Lenght > 0 then

posted at the same time lol!

Was just gonna put that! lol :D
Thanks for the suggestion there though!

As for setting things to nothing.
The short answer:
.NEt has a garbage collection thing it uses so you no longer need to set things to nothing, it pretty much is useless to. It seems wierd at first if you are used to vb6 but you get used to it.

The long answer:
http://www.vbforums.com/showthread.p...hreadid=211491

Also in regards to the database query, it would be quicker to check if the count is greater than 0 and use an ExecuteScalar to just get back the count into a variable.

VB Code:

strSQL = "SELECT Count(*) FROM Users Where UserId ='" & _
        strUserName & "' AND UserPassword='" & strUserPassword & "'"
Dim cmd As New SqlCommand(strSQL, cnn)
dim count as integer=cmd.ExecuteScalar()
Select Case count
Case 0
     LoginPassedValidation = False
Case 1
     LoginPassedValidation = True
End Select

thanks for that last post, I'd been setting things to nothing basically because either I didn't fully understand or didn't trust garbage collection - probably both!