.executenonquery() error No value given for one or more required parameters

Question

Imports System.Data.OleDb

Public Class LoginForm Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\User\Desktop\thesis\YBIM.accdb" Dim conn As New OleDbConnection ' TODO: Insert code to perform custom authentication using the provided username and password ' (See http://go.microsoft.com/fwlink/?LinkId=35339).
' The custom principal can then be attached to the current thread's principal as follows: ' My.User.CurrentPrincipal = CustomPrincipal ' where CustomPrincipal is the IPrincipal implementation used to perform authentication. ' Subsequently, My.User will return identity information encapsulated in the CustomPrincipal object ' such as the username, display name, etc.

Private Sub LoginForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    conn.ConnectionString = connstring

    If conn.State = ConnectionState.Closed Then
        conn.Open()
        MsgBox("welcome")
    Else
        MsgBox("Cannot connect to database")
    End If
End Sub

Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
    Dim SqlQuery As String = ("SELECT * FROM tablelogin WHERE Username= @field1 AND Password=@field2")
    Dim SqlCommand As New OleDbCommand
    Dim Sqlrdr As OleDbDataReader

    With SqlCommand
        .CommandText = SqlQuery
        .Connection = conn
        .Parameters.AddWithValue("@field1", UsernameTextBox.Text)
        .Parameters.AddWithValue("@field2", PasswordTextBox.Text)
        .ExecuteNonQuery()
    End With

    Sqlrdr = SqlCommand.ExecuteReader()

    If (Sqlrdr.Read() = True) Then
        home.ShowDialog()
        Me.Hide()
    Else
        MsgBox("wong input")
    End If


End Sub

End Class

Tobberoth Tobberoth · Accepted Answer · 2013-11-18T07:37:23

There are two things of note in your code which you can remedy.

1* You are naming your parameters incorrectly. This:

    .Parameters.AddWithValue("@field1", UsernameTextBox.Text)
    .Parameters.AddWithValue("@field2", PasswordTextBox.Text)

Should be this:

.Parameters.AddWithValue("field1", UsernameTextBox.Text)
.Parameters.AddWithValue("field2", PasswordTextBox.Text)

2* You are executing the command twice. Remove .ExecuteNonQuery() from the With statement, and change:

Sqlrdr = SqlCommand.ExecuteReader()

to

Dim ret As Integer
ret = SqlCommand.ExecuteNonQuery()

And instead of using Sqlrdr.Read(), simply check if ret > 0 (ExecuteNonQuery returns the amount of rows affected by the command).

.executenonquery() error No value given for one or more required parameters

1 Answers