no value given for one or more required parameters

Question

What's wrong in here, I always get some nasty errors even if the same code that I used earlier works. But when I apply it to other form it gives me the error above. here's my code:

Imports System.Data.OleDb
Public Class Updater2
    Public adminID As String
    Public adminName As String
    Public adminPass As String

    Private con As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;Jet OLEDB:Database Password=nrew123$%^;")
    Private com As OleDb.OleDbCommand

    Public Sub New()
        con.Open()
        com = New OleDb.OleDbCommand("Select * from admintable")
        com.Connection = con



    End Sub

    Public Sub updates()
        com.CommandText = "UPDATE admintable SET AdminName = '" & adminName & "', AdminPassS = '" & adminPass & "' WHERE ID = '" & adminID & "'"
        com.ExecuteNonQuery()

    End Sub
End Class

And here's the code in the button which tries to update the data:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        shikai.adminID = textbox1.text
        shikai.adminName = TextBox4.Text
        shikai.adminPass = TextBox3.Text






        shikai.updates()
        MsgBox("Successfully updated!")
    End Sub

what might be wrong here?

Schlieren

Looks like code ripe for SQL injection to me (though not DML since Jet/ACE can't execute multiple statements). You really ought to be using parameters. – Horizontal

I never thought sql injection could exist in client applications made through vb.net. thanks – Schlieren

With a Jet back end, the risk is low, as the only risk is revealing too much data (by the user trying to exploit your code putting in an expression that will evaluate to TRUE for all rows), but that can be a problem in and of itself, particularly with an UPDATE, which might end up changing data in all rows instead of just in the subset that is desired. – Horizontal

Answer 1

The usual reason for this error is a missing or misspelled value. It seems likely that adminName is Null or a zero-length string.

Parisparish

I got it sorry for the trouble it was just a simple typo error: AdminPassS = '" & adminPass must be AdminPass..but any way thanks for answering – Schlieren

Answer 2

A good trick for dealing with a no value given for one or more required parameters error when developing for an Access back end is to grab the content of the CommandText and paste it into a dummy query in Access itself. Then Access will offer you a popup identifying which field is causing the problem (usually a typo, as in your case).

Ninette

nice one, its really nice here, there are lots of good tricks I can get from monsters like you, thanks:) – Schlieren

Thanks - having to deal with legacy databases, I've forgotten all my Access tricks! – Iggy

Answer 3

When pasting the command text into access itself and Access pops up telling you which field is the problem, if there does not appear to be a type, try enclosing the field name in square brackets. [ ] It is possible that one of your columns may contain a keyword. This happened to me, which the column LL_ID - I had to change it to [LL_ID].

Mesentery

Answer 4

SELECT pt.person_name,pt.obile_no,pt.address_info FROM person_table pt LEFT JOIN company_table ct ON pt.com_id=ct.com_id WHERE pt.com_id=14

I used it for access database then this type of erro "no value given for one or more required parameters" happened.

I actually did typo error like pt.obile, but it will be pt.mobile. When i corrected this was working well.

Heroism

Answer 5

For my side, there is a field I have used in my query but not in my access db. After adding that field in ms access db, it works. May be you also need to check well if the field you have in query are the same as in your db

Infestation