locked
System.Data.OleDb.OleDbException: 'No value given for one or more required parameters.' RRS feed

  • Question

  • Private Sub btnEdit_Click(sender As Object, e As EventArgs) Handles btnEdit.Click
            Dim sql As String
            User.BookingDate = CDate(dgvBookings.CurrentRow.Cells(0).Value).Date
            User.Lesson = dgvBookings.CurrentRow.Cells(1).Value
            User.NumberOfDevices = dgvBookings.CurrentRow.Cells(2).Value
            sql = "SELECT BookingNo FROM tblBookings WHERE BookingDate = '" & CStr(User.BookingDate) & "' AND LessonNumber = '" & CStr(User.Lesson) & "' AND NumberOfDevices = '" & CStr(User.NumberOfDevices) & "' AND UserID = '" & CStr(User.UserID) & "';"
            ds = Con.SqlSelect(sql)
            User.BookingID = ds.Tables("Results").Rows(0).Item(0)
            sql = "SELECT DeviceID FROM tblDeviceBookings WHERE BookingNo = '" & CStr(User.BookingID) & "';"
            Con.Reset()
            ds = Con.SqlSelect(sql)
            sql = "SELECT DeviceTypeID FROM tblDevices WHERE DeviceID = '" & CStr(ds.Tables("Results").Rows(0).Item(0)) & "';"
            Con.Reset()
            ds = Con.SqlSelect(sql)
            sql = "SELECT DeviceTypeName FROM tblDeviceType WHERE DeviceTypeID = '" & CStr(ds.Tables("Results").Rows(0).Item(0)) & "';"
            Con.Reset()
            ds = Con.SqlSelect(sql)
            User.TypeOfDevice = CStr(ds.Tables("Results").Rows(0).Item(0))
            frmBookings.Show()
        End Sub

    I'm trying to create a section of my system where several different values, some from a datagridview and some from an access database, are stored in the global class User where they can then be taken to another form for editing. However, whenever I try to execute the first query in this sub (using the function below), I get the error in the title of this question. I have checked that all of the required values are there at the time of execution (using breakpoints) but it still gives me this error.

    Any help would be much appreciated.

    Function SqlSelect(ByVal sqlString As String) da = New OleDbDataAdapter(sqlString, Con) da.Fill(ds, "Results") Return ds End Function

    Example sqlString value (that causes the error):

    "SELECT BookingNo FROM tblBookings WHERE BookingDate = '20/03/2019' AND LessonNumber = '2' AND NumberOfDevices = '3' AND UserID = '3';"

    • Edited by Alafujah Saturday, March 2, 2019 3:19 PM
    Saturday, March 2, 2019 2:04 PM

Answers

  • Hello,

    Run the following, when an exception is thrown use the statement in MS-Access and work out the problem there followed by remedying the problem in code.

    Function SqlSelect(ByVal sqlString As String)
            Console.WriteLine(sqlString)
            da = New OleDbDataAdapter(sqlString, Con)
            da.Fill(ds, "Results")
            Return ds
    End Function



    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Alafujah Saturday, March 2, 2019 5:36 PM
    Saturday, March 2, 2019 3:44 PM

All replies

  • Determine with Debugger and show the value of sqlString parameter which causes the error.

    Saturday, March 2, 2019 3:14 PM
  • Hello,

    Run the following, when an exception is thrown use the statement in MS-Access and work out the problem there followed by remedying the problem in code.

    Function SqlSelect(ByVal sqlString As String)
            Console.WriteLine(sqlString)
            da = New OleDbDataAdapter(sqlString, Con)
            da.Fill(ds, "Results")
            Return ds
    End Function



    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Alafujah Saturday, March 2, 2019 5:36 PM
    Saturday, March 2, 2019 3:44 PM