Display From DB

[ViRi]

Hi

I have a Form with a Button and a few Textbox controls. This application will give access to users' given a correct UserName and PassWord is input.

Textbox Names as follows:
UserName 'Initially Blank
PassWord 'Initially Blank
DTIn 'Displays System Date
TTIn 'Displays the System Time
SessionID 'Ideally would display the current SessionID Number

The Application uses a Database to store all the required User and Session details. The Database is an Access 2003 DB called Log.

Log consists of 2 tables:
tblUsers-UName, PWord(Primary Key), RName
tblSession-SessionID(Primary Key AutoNumber), PWord, UName, SDate, TTin, TTout.

When the user inputs his details ie UserName and PassWord and clicks the Login Button the code checks the DB to validate the users. If the user is validated the users will be allowed into the secure area of the application. At the same time it will create a new record in the tblSession and store a new SessionID, PWord, UName, SDate and TTin. Here is the code that does this

Imports System.Data
Imports System.Data.OleDb
Imports System.IO

Public Class Form1

    

    ' Connection and Command variables for global access from any where within the current class:

    
    Dim myPath = Application.StartupPath & "\Log.mdb"

    Dim conn As New OleDbConnection( _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=""C:\Login\Log.mdb"";")

    Dim cmmd As OleDbCommand

   


    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        Dim strSQL = "SELECT UName,PWord FROM tblUsers WHERE UName = '" & _
            UserName.Text & " ' AND PWord = '" & PassWord.Text & "'"

        cmmd = New OleDbCommand(strSQL, conn)

        
        Try
            conn.Open()

        Catch ex As Exception
            MsgBox(ex.Message)

        End Try

        Dim dbread As OleDbDataReader = cmmd.ExecuteReader


        Try
            If dbread.Read = False Then
                My.Computer.Audio.Play("c:\Login\access_denied.wav")

                MessageBox.Show("Authentication Failed. Please Try Again")
                Me.PassWord.Text = ""
                Me.UserName.Text = ""
            Else
                My.Computer.Audio.Play("c:\Login\accessed.wav")

                MessageBox.Show("Login Successful")

                Dim sSQL = "INSERT INTO tblSession (PWord, UName,SDate, TTIn ) VALUES ('" & _
                Me.PassWord.Text & "','" & _
                Me.UserName.Text & "','" & _
                Me.DTIn.Text & "','" & _
                Me.TTIn.Text & "')"

                cmmd = New OleDbCommand(sSQL, conn)

                Dim theDataSet As New DataSet

                Try
                    cmmd.ExecuteNonQuery()

                Catch ex As Exception
                    System.Windows.Forms.MessageBox.Show(ex.Message)

                End Try


            End If

        Catch ex As Exception
            MsgBox(ex.Message)

        End Try

        If conn.State <> ConnectionState.Closed Then
            conn.Close()
        End If


    End Sub

 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'LogDataSet1.tblSession' table. You can move, or remove it, as needed.
        Me.TblSessionTableAdapter.Fill(Me.LogDataSet1.tblSession)
        My.Computer.Audio.Play("c:\Login\attention.wav")

        Me.DTIn.Text = System.DateTime.Now.ToShortDateString
        Me.TTIn.Text = System.DateTime.Now.ToShortTimeString
        Me.SessionIDTextBox.Text = ""
    End Sub

End Class

The problem is I need to populate Me.SessionID Textbox with the value of the newly created SessionId created when the users clicks the Login Button and I can't figure a way to do this.

ViRi

 

[alander]

u shldnt be coding ur connection string and stuff like that in .net framework 2.0, the tableadapters and binding source r nice to use in this case

 

[ViRi]

Could you direct me to links that show how to use the objects you mention for my example.


ViRi

 

[cjard]

For a bit of background about why we dont write SQLs like that in code, see the PQ link in my signature

Then for more info on what alander said, take a read of the DW2 link in my signature. Read the following sections in order:

Creating A Simple Data App (get a feel of how easy it all is)
Creating a Form To Search Data (understand how to search based on parameterized queries)
Saving Data (write new entries to your tables)


When youre done, you should be able to do your login screen in about 6 lines of code that are quite logical and readable

 

[ViRi]

Thanks for your help. I've carried your suggested exercises using my db now i have to figure out a way to put it all in one piece.

Thanks again
ViRi