Marc Williams Marc Williams - 6 months ago 42
Vb.net Question

VB.NET - .Fill command - Unhandled Exception

I'm getting one of those oh so helpful error messages when trying to use .Fill

Public Class SQLSearch
Dim CON As New OleDb.OleDbConnection 'Database Connection object
Dim CONSTR As String 'Connection String
Dim dbProvider As String 'Database Provider
Dim dbSource As String 'Data Source
Dim Database As String 'DB Name
Dim strSEARCH As String 'Variable held by txtName.text
' Dim SQL As String 'SQL Query
Dim NAME1 As String
Dim STREET1 As String
Dim SUBURB1 As String
Dim PCODE1 As Integer
Dim HOMEPH1 As Integer
Dim MOBPH1 As Integer
Dim EMAIL1 As String

'public CONNECT As New System.Data.SqlClient.SqlConnection(“Integrated Security=SSPI;Initial Catalog=SMART2003.mdb;Data Source=(local);”)

Public Sub AccessCustDetails()
'Define The Provider
dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.15.0;"

'Define Database Name
Database = "\smart2003.mdb"

'Define Data Source
dbSource = "C:\Smart\SMART2003.mdb"

'Define Connection string - Provider + Data Source
CON.ConnectionString = "PROVIDER=Microsoft.ACE.OLEDB.15.0; data source = C:\Smart\SMART2003.mdb;"

'Open Database connection
CON.Open()

'Define Search Term
strSEARCH = frmnewcust.txtName.Text

'Define SQL string
'SQL = "Select * From tblcustdet Where customername = " & "'" & strSEARCH & "'"

MsgBox("1ok")
MsgBox("The value of strSEARCH is " & strSEARCH)

Dim CMD As New OleDb.OleDbCommand
Dim SQL As String = "SELECT * FROM tblCustDet WHERE CustomerName= ?"

If strSEARCH = "" Then
MsgBox("Please enter customer's name.", MsgBoxStyle.Exclamation, "Add New Customer")

Else
Using CON As New OleDb.OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.15.0; data source = C:\Smart\SMART2003.mdb;")
CMD = New OleDb.OleDbCommand(SQL, CON)
CON.Open()
'MsgBox("Connection to database is " & CON.State)
CMD.Parameters.AddWithValue("@CustomerName", strSEARCH)
Dim RESULTS = Convert.ToInt32(CMD.ExecuteScalar)
'If the Results number is greater than 0 then Customer already exists
If RESULTS > 0 Then
MsgBox("Customer name already exists.", MsgBoxStyle.Exclamation, "New Customer Record")

Else

MsgBox("2ok")

NAME1 = My.Forms.frmnewcust.txtName.Text
STREET1 = My.Forms.frmnewcust.txtStreet.Text
SUBURB1 = My.Forms.frmnewcust.txtSuburb.Text
PCODE1 = My.Forms.frmnewcust.txtPCode.Text
HOMEPH1 = My.Forms.frmnewcust.txtHomePh.Text
MOBPH1 = My.Forms.frmnewcust.txtMob.Text
EMAIL1 = My.Forms.frmnewcust.txtEmail.Text

Dim DS As New DataSet 'Dataset Object
Dim DA As OleDb.OleDbDataAdapter 'Data Adapter Object
Dim DSNEWROW As DataRow
Dim TABLE As String

TABLE = "tblCustDet"

DA = New OleDb.OleDbDataAdapter(SQL, CON)

DA.Fill(DS, TABLE)

Dim COMBUILD As New OleDb.OleDbCommandBuilder(DA)
DSNEWROW = DS.Tables(TABLE).NewRow

DS.Tables(TABLE).Rows.Add(DSNEWROW)

DSNEWROW.Item("CustomerName") = NAME1
DSNEWROW.Item("STREET") = STREET1
DSNEWROW.Item("suburb") = SUBURB1
DSNEWROW.Item("PostCode") = PCODE1
DSNEWROW.Item("LandlineNumber") = HOMEPH1
DSNEWROW.Item("MobileNumber") = MOBPH1
DSNEWROW.Item("EmailAddress") = EMAIL1

DA.Update(DS, TABLE)

MsgBox("Customer Added Successfuly.", MsgBoxStyle.OkOnly)

End If
End Using
End If
CON.Close()
MsgBox("3ok")
End Sub
End Class


At
DA.Fill(DS, TABLE)
I am getting:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: No value given for one or more required parameters.

The TABLE variable has the correct Table name in my DB.

Can someone please give me some hint at what i am missing?

Edited to show whole code

Answer

Here is your problem:

DA = New OleDb.OleDbDataAdapter(SQL, CON)
DA.Fill(DS, TABLE)

You create OleDbDataAdapter with sql query and connection. Your query
Dim SQL As String = "SELECT * FROM tblCustDet WHERE CustomerName= ?" use parameter. But when you execute query with OleDbDataAdapter parameter wasn't added.
In the first "search" execution parameter was provided and everything works fine

Using command As New OleDbCommand(SQL, CON)
    command.Parameters.AddWithValue("@CustomerName", strSEARCH)
    Using adapter As New OleDbDataAdapter(command)
         adapter.Fill(DS, TABLE)
    End Using
End Using