How to link to a relational database - visual basic

Question

I am creating a flashcard application where each user can create flashcards which will be specific to them. I was wondering how I can link each flashcard they create to their specific account.

Imports System.Data.OleDb

Public Class CreateFlashcards
  Dim pro As String
  Dim connstring As String
  Dim command As String
  Dim myconnection As OleDbConnection = New OleDbConnection

  Private Sub btnCreateFlashcard_Click(sender As Object, e As EventArgs) Handles btnCreateFlashcard.Click
    pro = "provider=microsoft.ACE.OLEDB.12.0;Data Source=flashcard login.accdb"
    connstring = pro
    myconnection.ConnectionString = connstring
    myconnection.Open()

    command = " insert into Flashcards ([Front],[Back]) values ('" & txtFront.Text & "','" & txtBack.Text & "')"
    Dim cmd As OleDbCommand = New OleDbCommand(command, myconnection)
    cmd.Parameters.Add(New OleDbParameter("username", CType(txtFront.Text, String)))
    cmd.Parameters.Add(New OleDbParameter("password", CType(txtBack.Text, String)))

    MsgBox("You have successfully added the flashcard into your deck!")

    Try
        cmd.ExecuteNonQuery()
        cmd.Dispose()
        myconnection.Close()
        txtFront.Clear()
        txtBack.Clear()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
  End Sub
End Class

This works in that it adds the data into the access database but it does not link to the users account. I have also linked the tables in access

The login table

The flashcard table

As you can see the loginID key does not link

`insert into Flashcards ([Front],[Back])`...you're only inserting values into those two fields. If you want to insert into the loginID field, then change your insert statement so it does that. — ADyson, Jul 22 '21 at 13:28
BTW your parameters are not being used at the moment, because you're just injecting the textbox data directly into the query. Don't do that - it leaves you vulnerable to SQL injection attacks and unexpected syntax errors. Instead, use placeholders in the SQL string, in the place where the parameter values should go. You can find plenty of examples online of the correct usage. — ADyson, Jul 22 '21 at 13:31
ah ok, thanks. How would I insert into the LoginID field as people can create accounts which means there will be lots of LoginIDs. — Idk, Jul 22 '21 at 13:40
OT you show "successfully added" *before* you actually do so — Hans Keﬆing, Jul 22 '21 at 13:41
When the user creates an account or logs in, you assign or get their loginID - remember and use that — Hans Keﬆing, Jul 22 '21 at 13:41
presumably once the user has logged in, your application stores the logged-in identity in a variable somewhere? Otherwise how do you keep track of who the current user is? So assuming you've got that, you can use it in your query. — ADyson, Jul 22 '21 at 13:48
Hi , I'm a bit confused - this is for a school project. my current application just checks if there username is in the database and if it matches to the password they are granted access to the application. How would I create the loginID from the access table to be a variable? — Idk, Jul 22 '21 at 14:33
`if it matches to the password they are granted access to the application`...ok well then at that point you need to store the username they used in the login form, so that you always know who's logged in. — ADyson, Jul 22 '21 at 14:46
Is this a web application, or a desktop forms application, or what? It isn't really clear. In a web application you'd store that in the Session (or more likely, ASP.NET would already store it for you), but in a desktop application you need to implement your own version - see https://stackoverflow.com/questions/7439706/persisting-logged-in-user-information, for example (it's written in C# but you get the idea, and you can use an online converter to automatically change it into VB.NET if you want.) — ADyson, Jul 22 '21 at 14:49

Mary · Answer 1 · 2021-07-24T19:34:27.767

Normally, the connection string for Access contains the the path to the database file for the Data Source attribute.

You will need to add a field to your Flashcard table to hold the UserID. This will tie the 2 tables together.

Database objects like connections and commands need to be disposed as well as closed. Using...End Using blocks handle this for you even if have an error. In this code you both the connection and the command are included in the same Using block.

You can pass the connection string directly to the constructor of the connection. Likewise, pass the sql command text and the connection to the constructor of the command.

When you are using parameters, which you should in Access and Sql Service, put the name of the parameter in the sql command text. It is not necessary to convert the Text property of a TextBox to a string. It is already a String.

I had to guess at the OleDbType for the parameters. Check your database. It is important to note that the order that parameters appear in the sql command text must match the order that they are added to the parameters collection. Access does not consider the name of the parameter, only the position.

I assume you can retrieve the user's ID when they log in to create flash cards. When the user logs in to use there flash cards you would do something like Select * From Flashcards Where LoginID = @UserID;.

Private Sub btnCreateFlashcard_Click(sender As Object, e As EventArgs) Handles btnCreateFlashcard.Click
    Try
        InsertFlashcard()
        MsgBox("You have successfully added the flashcard into your deck!")
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

Private Sub InsertFlashcard()
    Dim pro = "provider=microsoft.ACE.OLEDB.12.0;Data Source=Path to login.accdb"
    Dim Command = " insert into Flashcards (LoginID, [Front],[Back]) values (@Id, @Front, @Back);"

    Using myconnection As New OleDbConnection(pro),
            cmd As New OleDbCommand(Command, myconnection)
        cmd.Parameters.Add("@UserID", OleDbType.Integer).Value = ID
        cmd.Parameters.Add("@Front", OleDbType.VarChar).Value = txtFront.Text
        cmd.Parameters.Add("@Back", OleDbType.VarChar).Value = txtBack.Text
        myconnection.Open()
        cmd.ExecuteNonQuery()
    End Using
End Sub

EDIT

As per comment by ADyson I have added code to retrieve ID. Of course in your real application you would be salting and hashing the password. Passwords should never be stored as plain text.

Private ID As Integer

Private Sub btnLogIn_Click(sender As Object, e As EventArgs) Handles btnLogIn.Click
    Dim pro = "provider=microsoft.ACE.OLEDB.12.0;Data Source=Path to login.accdb"
    Using cn As New OleDbConnection(pro),
            cmd As New OleDbCommand("Select LoginID From login Where Username = @Username and Password = @Password;")
        cmd.Parameters.Add("@Username", OleDbType.VarChar).Value = txtUserName.Text
        cmd.Parameters.Add("@Password", OleDbType.VarChar).Value = txtPassword.Text
        cn.Open()
        Dim result = cmd.ExecuteScalar
        If Not result Is Nothing Then
            ID = CInt(result)
        Else
            MessageBox.Show("Invalid Login")
        End If
    End Using
End Sub

The table already contains a LoginID field, that's what OP wants to use. No new field needed. — ADyson, Jul 23 '21 at 06:51
`I assume you can retrieve the user's ID when they log in`...no, they can't. This was already discussed at length in the earlier comments. They need to implement that but didn't know how — ADyson, Jul 23 '21 at 06:53
@ADyson I apologize, I am having computer problems and didn't catch all comments. New part from Dell should arrive Monday. — Mary, Jul 24 '21 at 19:30

How to link to a relational database - visual basic

1 Answers1