OleDBException was Unhandled

[ashuag]

Getting error "OleDBException was Unhandled: No value given for one or more required parameters" while submitting the insert query through VB.Net.

Below is the sample code I am using:

    custid = txtcusid.Text
    custname = txtcustname.Text
    age = txtage.Text
    gender = cmbgender.SelectedItem.ToString
    phno = txtcontact.Text
    custadd = txtadd.Text

    comm.CommandText = "INSERT INTO Cust_Details(Cust_ID, CustName, Gender, Age, Phone, CustAddress) Values (" & custid & ", " & custname & "," & gender & "," & age & "," & phno & "," & custadd & ")"
    comm.ExecuteScalar()

Please help to resolve this...

[Pgmer]

INSERT INTO Cust_Details(Cust_ID, CustName, Gender, Age, Phone, CustAddress) Values "(" & custid & ", " & custname & "," & gender & "," & age & "," & phno & "," & custadd & ")"
Try this.

[Begginnerdev]

Is the cust ID field an auto number field?

[Reverend Jim]

Add single quotes around non-numeric fields and make sure that all of the variables you are using have valid values.

comm.CommandText = "INSERT INTO Cust_Details(Cust_ID, CustName, Gender, Age, Phone, CustAddress) Values (" & custid & ", '" & custname & "','" & gender & "'," & age & ",'" & phno & "','" & custadd & "')"

Assuming custname, gender, phno and custadd are char or varchar. It also wouldn't hurt to display the value of comm.CommandText just before you execute the query. It's easier to spot syntax errors in the actual query string. It is also possible that there is a database field in the given table that was not specified in the insert query. If that field is defined as NON NULL then it must be present on any insert query.

[ashuag]

Thanks alot for the help and now I am successfully able to submit the form and insert the record in DB after putting single quote to all non-numeric fields.
There is one more issue I am facing that on Successfull submission of form information into the DB, I am again refreshing the same form and calling function to generate CustID and display in the text box. But actually it is not working as intended. The ID which is getting generated again in the new form is the same one which was saved prviously. Giving you the code:

    Private Sub cmdbtnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdbtnadd.Click

        Dim custid As Integer
        Dim custname As String
        Dim gender As String
        Dim age As Integer
        Dim phno As Integer
        Dim custadd As String

        txtcustname.Focus()

        If txtcustname.Text = "" Or txtage.Text = "" Or txtadd.Text = "" Or txtcontact.Text = "" Then
            MsgBox("Please fill up all fields!", vbExclamation, "Customers Management System")


            If txtcustname.Text = "" Then
                txtcustname.Focus()
                Exit Sub
            End If

            If txtage.Text = "" Then
                txtage.Focus()
                Exit Sub
            End If

            If txtadd.Text = "" Then
                txtadd.Focus()
                Exit Sub
            End If

            If txtcontact.Text = "" Then
                txtcontact.Focus()
                Exit Sub
            End If

        End If

        'Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Ashutosh\My Documents\Test.mdb"
        'Con.Open()
        'comm.Connection = Con
        'comm.CommandType = CommandType.Text

        custid = txtcusid.Text
        custname = txtcustname.Text
        age = txtage.Text
        gender = cmbgender.SelectedItem.ToString
        phno = txtcontact.Text
        custadd = txtadd.Text

        comm.CommandText = "INSERT INTO Cust_Details(CustID, CustName, Gender, Age, Phone, CustAddress) Values (" & custid & ", '" & custname & "','" & gender & "'," & age & "," & phno & ",'" & custadd & "')"
        'Dim sql As String
        'sql = comm.CommandText
        'MsgBox("sql query is : ............. '" + sql + "'")
        comm.ExecuteScalar()
        cmdbtnadd.Enabled = False
        MsgBox("Information Successfully Saved!", vbInformation, "Customers Management System")
        Me.Refresh()
        cmdbtnadd.Enabled = True
        txtcustname.Text = ""
        txtage.Text = ""
        txtadd.Text = ""
        txtcontact.Text = ""
        cmbgender.SelectedText = ""
        'txtcusid.Text = ""
        txtcustname.Focus()
        Call GenerateID()


    End Sub

This is the place I am calling back this function again for generating ID after submission of form.

And this is the Generating ID code:

Private Function GenerateID() As String

    Dim value As String
    Dim ID As String

    value = "0000"

    Try

        If Con.State = ConnectionState.Open Then
            Con.Close()
        End If

        Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Ashutosh\My Documents\Test.mdb"
        Con.Open()
        comm.Connection = Con
        comm.CommandType = CommandType.Text

        comm.CommandText = "SELECT TOP 1 CustID FROM [Cust_Details] ORDER BY CustID desc"

        datardr = comm.ExecuteReader

        If datardr.HasRows Then
            datardr.Read()

            value = datardr.Item("CustID")


        End If
        value += 1



        ' Because incrementing a string with an integer removes 0's
        ' we need to replace them. If necessary.

        If value <= 9 Then              'Value is between 0 and 10
            value = "000" & value
        ElseIf value <= 99 Then         'Value is between 9 and 100
            value = "00" & value
        ElseIf value <= 999 Then        'Value is between 999 and 1000
            value = "0" & value
        End If

    Catch ex As Exception

        If Con.State = ConnectionState.Open Then

            Con.Close()
        End If

        value = "0000"

    End Try

    datardr.Close()

    Return value



End Function

I saw the problem and found that once control passes through End Function() statement, the application should not go to the line:

Call GenerateID()

written in ADD button click event.

Please help me where I am doing wrong

[Reverend Jim]

You can't do math on a string. Declare "value" as an integer and do the increment on that. To convert it to a string with zero padding do

txtCustid.Text = value.ToString("0000")