Hello so I have a problem with my simple program that I'm practicing. I'm trying to insert data from vb input box to MS Access database, there are 5 columns in employeeInfo table but the other one is AutoNumber so I didn't include it in the code. The employeeDB has only 3 columns ID, username, pword but I didn't include ID since it's AutoNumber. When I hit the button to add data it will throw an error No value given for one or more required parameters @ database_reader = cmd_personal.ExecuteReader even if I did input all the input box that has connection to the database.

Private Sub signUp_btn_Click(sender As Object, e As EventArgs) Handles signUp_btn.Click
 Dim usernameInput As String = inputBoxUsername.Text
    Dim inputPword As String = inputBoxPword.Text
    Dim input_FirstName As String = FirstName_Box.Text
    Dim input_MidName As String = MidName_Box.Text
    Dim input_LastName As String = LastName_Box.Text
    Dim input_ContactNum As String = ContactNumber_Box.Text


    dbConnection.Open()
    Dim str_personal As String
    Dim str_acctInfo As String
    str_personal = "INSERT INTO employeeInfo([FirstName], [MiddleName], [LastName], [PhoneNumber]) Values (?, ?, ?, ?)"
    str_acctInfo = "INSERT INTO employeeDB([username], [password]) Values (?, ?)"
    Dim cmd_personal As OleDbCommand = New OleDbCommand(str_personal, dbConnection)
    Dim cmd_acctInfo As OleDbCommand = New OleDbCommand(str_acctInfo, dbConnection)
    database_reader = cmd_personal.ExecuteReader
    database_reader = cmd_acctInfo.ExecuteReader
    database_reader.Read()

    ' Check If Input box has values 
    If usernameInput = "" Then
        MessageBox.Show("Please Insert Username.")
        inputBoxUsername.Clear()
    ElseIf inputPword = "" Then
        MessageBox.Show("Please Insert Password.")
    ElseIf input_FirstName = "" Then
        MessageBox.Show("Please insert First Name.")
    ElseIf input_MidName = "" Then
        MessageBox.Show("Please insert Middle Name.")
    ElseIf input_LastName = "" Then
        MessageBox.Show("Please insert Last Name.")
    ElseIf input_ContactNum = "" Then
        MessageBox.Show("Please insert Phone Number.")
    End If



    ' Insert into employeeInfo DB
    cmd_personal.Parameters.Add(New OleDbParameter("FirstName", CType(input_FirstName, String)))
    cmd_personal.Parameters.Add(New OleDbParameter("MiddleName", CType(input_MidName, String)))
    cmd_personal.Parameters.Add(New OleDbParameter("LastName", CType(input_LastName, String)))
    cmd_personal.Parameters.Add(New OleDbParameter("PhoneNumber", CType(input_ContactNum, String)))

    ' Insert into employeeDB acct DB
    cmd_acctInfo.Parameters.Add(New OleDbParameter("username", CType(usernameInput, String)))
    cmd_acctInfo.Parameters.Add(New OleDbParameter("password", CType(inputPword, String)))

    MessageBox.Show("Success! User has been created.")
    dbConnection.Close()

I don't need advance solution just a simple one. Thanks!

(I already connect it to the Access Database I just didn't include the code at this post.)

CodePudding user response：

If your SQL contains N ?, then you cannot call cmd_personal.ExecuteReader before you call cmd_personal.Parameters.Add N times to create the parameters (and give them a value)

If your intention is to call the command repeatedly, you can adopt a pattern of:

• create command with ? placeholders
• add parameters without values, or with representative dummy values if you're using "AddWithValue"/its equivalent (using the paramname,paramvalue constructor, as your code does, is equivalent to calling AddWithValue)
• start a loop
  • set values
  • execute command

In this case it looks like your code is merely in the wrong order

CodePudding user response：

Ok a few things. There is really no use to write a bunch of separate statements to test if a text box has a value. Like FoxPro, Access, dabase, or just about any system?

They have the ability to "validate" each text box.

So, move that code out. For each control that you want say as required?

So for say FirstName_Box?

Then in the events of the property sheet, use this event:

So, we double click in that Validating event, and we can write this:

Private Sub FirstName_Box_Validating(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles FirstName_Box.Validating

    If FirstName_Box.Text = "" Then
        MsgBox("First name is required")
        e.Cancel = True
    End If

End Sub

So, now if I click any button on the form, the text box will validate, and also pop up a nice message.

So, now we can dump, and assume our button code will not run until each control and its validation event is ok. It not only saves some code, but what happens if we have 2 or 4 buttons on the form, and now we going to write code to check all those text boxes each time? (nope!!!).

Ok, so now we can assume in our code that any required text box has a value. And if not, then the button click for any button code will not run for us - nice and simple, but more important nice and CLEAN. Since I can now know where to look for any code for a SINGLE control that has some criteria for input.

It also means that you have less code to work with, and deal with at one point in time.

This is much like the answer to this question:

How do you eat a elephant?

Answer: One bite at a time!!!

Ok, now that we have that all fixed up, we can clean up our code to insert.

I actually in a lot of cases suggest using a Datatable, since then we don't have to mess with parameters, but MORE important, it gives us the ability to EASY check if the information or "user" in this case already exists. I mean, you don't want to add the same user two times? Right?

So, now we can clean this up and ALSO check for if the user already exists.

Say somthing like this (warning: air code).

    Using conn As New OleDbConnection(My.Settings.AccessDB)

        conn.Open()

        Dim strSQL As String =
            "SELECT * FROM employeeDB WHERE UserName = @User"
        Using cmdSQL As New OleDbCommand(strSQL, conn)

            Dim da As New OleDbDataAdapter(cmdSQL)
            Dim daU As New OleDbCommandBuilder(da)

            cmdSQL.Parameters.Add("@User", OleDbType.VarWChar).Value = inputBoxUserName.Text

            Dim rstAcctInfo As New DataTable

            rstAcctInfo.Load(cmdSQL.ExecuteReader)

            If rstAcctInfo.Rows.Count > 0 Then
                MsgBox("This user already exists")
                return   ' bail out and exit
            End If
            ' if we get here, user does not exist, so add this user

            Dim OneRow As DataRow = rstAcctInfo.NewRow

            OneRow("username") = inputBoxUsername.Text
            OneRow("password") = inputBoxPword.Text
            
            rstAcctInfo.Rows.Add(OneRow)

            da.Update(rstAcctInfo)  ' add this new user

            ' now add the data to personal
            cmdSQL.Parameters.Clear()
            cmdSQL.CommandText =
                "INSERT INTO employeeInfo([FirstName], [MiddleName], [LastName], [PhoneNumber]) Values " &
                "(@FN, @MN,@LN , @Phone)"

            With cmdSQL.Parameters
                .Add("@FN", OleDbType.VarWChar).Value = FirstName_Box.Text
                .Add("@MN", OleDbType.VarWChar).Value = MidName_Box.Text
                .Add("@LN", OleDbType.VarWChar).Value = LastName_Box.Text
                .Add("@Phone", OleDbType.VarWChar).Value = ContactNumber_Box.Text
            End With

            cmdSQL.ExecuteNonQuery()

        End Using
    End Using