[RESOLVED] DA.Fill causing errors.

[snayler0]

Gday all.

So, i can't work out why this code is giving me this OleDbException: "No value given for one or more required parameters"

Public Function makeLabels()
        Dim DA As OleDb.OleDbDataAdapter
        Dim DS As DataSet = New DataSet("DS")
        Dim SQL As String
        Dim xigsm, xserial, eol As String
        Dim eolDate As Date
        Dim i As Integer = 0

        dbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cString
        dbConnection.Open()

        While i < Macs.Count
            If Macs(i).getMacType = "2" Or Macs(i).getMacType = "4" Then
                Try
                    SQL = "SELECT * FROM SMSAssetViewer1 WHERE Barcode = IGSM" & Macs(i).getIGSM
                    DA = New OleDb.OleDbDataAdapter(SQL, dbConnection)
                    DA.Fill(DS, "stuff")
                    xigsm = DS.Tables("stuff").Rows(0).Item("Barcode")
                    xserial = DS.Tables("stuff").Rows(0).Item("SerialNo")
                    eol = DS.Tables("stuff").Rows(0).Item("ClientAcceptedInstallDate")
                    eolDate = CDate(eol)

                    eolDate = DateAdd(DateInterval.Year, 3, eolDate)

                    MessageBox.Show("igsm: " & xigsm & " ||| serial: " & xserial & " ||| eol: " & eolDate.ToString)
                Catch

                End Try

            End If
            i = i + 1
        End While

        dbConnection.Close()
        Return 0
    End Function

Any ideas/Pointers?

[Shaggy Hiker]

What datatype is Barcode? It pretty much has to be some kind of string, in which case you will need to wrap that argument, including the IGSM part, in single quotes.

[Jenner]

I suspect your problem is right here:

WHERE Barcode = IGSM" & Macs(i).getIGSM

What exactly is the results of Macs(i).getIGSM? If it's not proper SQL then it'll give you that problem.

You should use a Parameter in your SQL. It fixes multitudes of problems you can have with that code and will probably fix this.

Second, while your While loop works, it would be more common to make a For Each loop.

Third, a DataReader would be more effective for what you're doing. It's lighter and faster than a DataAdapter. If only one parameter of your SQL is changing with each loop, it's even faster.

Fourth tip, learn to use String.Format() . It's really awesome for easily formatting strings. See my use of it in the MessageBox line.

Another tip, when using an If...Then, use "AndAlso" and "OrElse" as opposed to "And" and "Or". They're more efficient.

Also, if you're not really returning anything from your Function, why make it a Function? Make it a Sub.

Last, The "Using" blocks are your friends for accessing databases. When the routine exits the Using block, everything contained within is cleaned up nicely.

Rewrite of your code is below:

    Public Sub makeLabels()
        Using DbConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cString)
            DbConnection.Open()

            Dim SQL As String = "SELECT * FROM SMSAssetViewer1 WHERE Barcode = @IGSM;"
            Dim CMD As New OleDb.OleDbCommand(SQL, DbConnection)
            CMD.Parameters.Add("@IGSM", OleDb.OleDbType.VarChar, 128)
            Dim READER As OleDb.OleDbDataReader

            For Each mac In Macs
                If mac.getMacType = "2" OrElse mac.getMacType = "4" Then
                    Try
                        CMD.Parameters("@IGSM").Value = mac.getIGSM
                        READER = CMD.ExecuteReader
                        If READER.Read Then
                            Dim xigsm As String = READER.Item("Barcode").ToString
                            Dim xserial As String = READER.Item("SerialNo").ToString
                            Dim eolDate As Date = Convert.ToDateTime(READER.Item("ClientAcceptedInstallDate"))
                            eolDate = DateAdd(DateInterval.Year, 3, eolDate)

                            MessageBox.Show(String.Format("igsm: {0} ||| serial: {1} ||| eol: {2}", xigsm, xserial, eolDate.ToString))
                        End If
                        READER.Close()
                    Catch ex As Exception
                        MessageBox.Show(ex.Message)
                    End Try
                End If
            Next
            CMD.Dispose()
            DbConnection.Close()
        End Using
    End Sub