Storing ID from SQL result

Question

Im running the following code to tell if a user excists on a database - standard stuff. Obviously once the code is run a boolean true or false will be returned if there is a result. If a result is found i want to store the ID of the said result. Can anyone tell me how'd id go about doing this?

code:

 Username = txtUserName.Text
    Password = txtPassword.Text
    dbConnInfo = "PROVIDER=Microsoft.Jet.OLEDB.4.0; Data Source = C:\Users\Dave\Documents\techs.mdb"
    con.ConnectionString = dbConnInfo
    con.Open()
    Sql = "SELECT * FROM techs WHERE userName =  '" & Username & "' AND '" & Password & "'"
    LoginCommand = New OleDb.OleDbCommand(Sql, con)
    CheckResults = LoginCommand.ExecuteReader
    RowsFound = CheckResults.HasRows
    con.Close()
    If RowsFound = True Then

        MsgBox("Details found")

        TechScreen.Show()
    Else
        MsgBox("Incorrect details")
    End If

Answer 1

There are a lot of problems with the code snippet you posted. Hopefully, I can help you correct these problems.

In order to load the ID of the result you'll want to use SqlCommand.ExecuteScalar() as this is optimized to pull back one result from Sql.

As to what is wrong with your code, you're wide open to Sql Injection attacks and you should be using Parametrized Queries as shown in my sample below.

Public Function AddProductCategory( _
  ByVal newName As String, ByVal connString As String) As Integer 
    Dim newProdID As Int32 = 0
    Dim sql As String = _
     "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); " _
       & "SELECT CAST(scope_identity() AS int);" 

Using conn As New SqlConnection(connString)
    Dim cmd As New SqlCommand(sql, conn)
    cmd.Parameters.Add("@Name", SqlDbType.VarChar)
    cmd.Parameters("@Name").Value = newName
    Try
        conn.Open()
        newProdID = Convert.ToInt32(cmd.ExecuteScalar())
    Catch ex As Exception
        Console.WriteLine(ex.Message)
    End Try 
End Using 

Return newProdID
End Function

Source: MSDN