Compare data from SQL select to variables on form 1

[cdck]

I have a simple form for login to an application which needs to pull a a user's information from a database based on variables set on the form and verify that the data matches. I've worked extensively in both VBA and VBscript in the past, and am having trouble making the transition to Visual Basic. I also appear to be using the wrong search terms to find solutions to my problems.

Below is my code. There are two points where Visual Studio is identifying errors for me. Can anyone help me to figure out the correct way to code this?
[tt]
Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click

Dim UserName As String, Password As String
UserName = Me.txtUsername.Text
Password = Me.txtPassword.Text


Dim constr As String
constr = My.Settings.M2MAydinOtherConnectionString

Dim SQLselect As String
SQLselect = "SELECT Password FROM vwUserDBaccess WHERE UserName = " & UserName & " AND DBid = 46"

Dim ds As DataSet
Dim da As System.Data.SqlClient.SqlDataAdapter
Dim con As System.Data.SqlClient.SqlConnection

con = New System.Data.SqlClient.SqlConnection(constr)
da = New System.Data.SqlClient.SqlDataAdapter(SQLselect, con)
ds = New System.Data.DataSet()

da.Fill(ds)

If IsNull(ds) = True Then
MsgBox("The username is not found in the system. Please try again or contact IT for assistance.", MsgBoxStyle.OkOnly, "Login Failed.")
ElseIf Password <> ds(Password) Then
MsgBox("The password does not match the one in the system for User " & UserName & ". Please contact IT for assistance.")
Else
QCmaster.MdiParent = QualityControl
QCmaster.Show()
Me.Close()
End If
End Sub
[/tt]

I have underlined the errors above.

In the first case, I am attempting to determine if there is actually no record at all matching the criteria. Visual Studio sees IsNull as a variable which should be declared - obviously the IsNull function is not a part of Visual Basic, but I cannot find a comparable process.

In the second case, I am attempting to verify that the record pulled has a password matching what the user entered. Visual Studio informs me that the "Class 'System.Data.DataSet' cannot be indexed because it has no default property." I have no idea how to perform logic on data pulled through the SQL Select statement.

Cheryl dc Kern

 

[ZmrAbdulla]

First of all,
a dataset is similar to a database that resides in the memory.
a datatable is similar to a table in a database.

Dim str As String = "myPassword"

If you want to check if a string is empty you can do the following

If str.Length.ToString.Trim = 0 Then

        End If

or

  If str.Trim = "" Then

        End If

or if the data from a database then use

If IsDBNull(str) Then

        End If

to check a value from field(item) of a datatable inside a dataset use something like

Dim ds As New DataSet
        ds.Tables.Add("tbl")
        If str = ds.Tables("tbl").Rows(0).Item(0) Then

        End If

Dig MSDN for more...

http://msdn.microsoft.com/en-us/library/system.data.dataset.aspx

http://msdn.microsoft.com/en-us/library/system.data.datatable.aspx

By the way to retrieve a value from db, best way to use "ExecuteScalar" method.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(v=vs.71).aspx

Zameer Abdulla

 

[TysonLPrice]

Couple of things you should check into:

1. Parameterized SQL.
2. SQL injection attacks.

Use this as your password:

'';drop table vwUserDBaccess --

I know the name of the table from your code but the point is you are opening up a big hole to your databases.

 

[TysonLPrice]

Use this as your password:'';drop table vwUserDBaccess --

I meant UserName

 

[cdck]

FIrst, I should apologize, the IsNull effort above came after I attempted "If ds.EOF AND ds.BOF Then"; I was pretty sure before I tried IsNull that it wouldn't work, because a non-existent dataset is not the same as a null string.

ZmrAbdulla: Thanks for giving me a good path to work down. Like I mentioned above, a big part of the struggle is that the terms you can use to successfully search for VBscript and VBA solutions don't work when searching for Visual Basic

TysonLPrice: Thank you for giving me that "Parameterized SQL" term to search for. It looks a lot easier than the long series of replace functions I've had to use in the past to clean up user-input variables before using them in SQL - I hadn't even started building them yet. This application is not a web application, and all comparisons are being built against views rather than tables, but I do still intend to protect against injection.

Thank you both, I am digging into these materials now.

Cheryl dc Kern