Hi,
I am fairly new to vb.net and seem to be having some probelms doing an SQL statement. I am selecting data from more than one table from my Access database. A surname held on a database is typed and it is supposed to display results, related on the MemberID.
I get the error 'No value given for required parameters' at [da.Fill(ds, "RoyalYachtingAssociation2")]. The code I have is shown below. I do not think the SQL statement is finding a value. Any help would be much apperciated.
Public Class SearchInBDAOTRpt
Dim con As New OleDb.OleDbConnection
Dim userInput As String
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim commandString As String = "SELECT Owner.MemberID, Owner.FirstName, Owner.Surname, Owner.Address, Owner.PostCode, Owner.RYAMembershipNumber, Owner.PositionInClub, BoatDefectLog.BoatDefectLogID, BoatDefectLog.HullNumber, BoatDefectLog.Defect, BoatDefectLog.DateReported, BoatDefectLog.Repaired, BoatDefectLog.DateRepaired FROM Owner, BoatDefectLog " & _
"INNER JOIN ([Owner] INNER JOIN [BoatDefectLog] ON Owner.MemberID=BoatDefectLog.MemberID" & _
"ON MemberID.Owner = MemberID.BoatDefectLog) "
Dim MaxRows As Integer
Dim inc As Integer
Dim BDLID As New TextBox
Dim HullNumber As New TextBox
Dim Defect As New TextBox
Dim DateReported As New TextBox
Dim Repaired As New TextBox
Dim DateRepaired As New TextBox
Dim FirstName As New TextBox
Dim Surname As New TextBox
Dim Address As New TextBox
Dim PostCode As New TextBox
Dim RYAMembershipNumber As New TextBox
Dim PositionInClub As New TextBox
Dim MemberID As New TextBox
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\RoyalYachtingAssociation2.mdb"
con.Open()
Dim SQL As String = "SELECT [BoatDefectLogID], [HullNumber], [Defect], [DateReported], [Repaired], [DateRepaired], [FirstName], [Surname], [Address], [PostCode], [RYAMembershipNumber], [PositionInClub], [MemberID] " & _
" FROM Owner INNER JOIN BoatDefectLog ON Owner.MemberID = BoatDefectLog.MemberID WHERE Surname = '"
userInput = TextBox1.Text
SQL = String.Concat(SQL, userInput)
SQL = String.Concat(SQL, "'")
da = New OleDb.OleDbDataAdapter(SQL, con)
da.Fill(ds, "RoyalYachtingAssociation2")
Dim dataAdapter As New OleDb.OleDbDataAdapter(commandString, con)
dataAdapter.Fill(ds, "RoyalYachtingAssociation2")
FillControls()
End Sub
Private Sub FillControls()
BDLID.Text = ds.Tables("RoyalYachtingAssociation2").Rows(0).Item(0)
HullNumber.Text = ds.Tables("RoyalYachtingAssociation2").Rows(0).Item(1)
Defect.Text = ds.Tables("RoyalYachtingAssociation2").Rows(0).Item(2)
DateReported.Text = ds.Tables("RoyalYachtingAssociation2").Rows(0).Item(3)
Repaired.Text = ds.Tables("RoyalYachtingAssociation2").Rows(0).Item(4)
DateRepaired.Text = ds.Tables("RoyalYachtingAssociation2").Rows(0).Item(5)
FirstName.Text = ds.Tables("RoyalYachtingAssociation2").Rows(0).Item(6)
Surname.Text = ds.Tables("RoyalYachtingAssociation2").Rows(0).Item(7)
Address.Text = ds.Tables("RoyalYachtingAssociation2").Rows(0).Item(8)
PostCode.Text = ds.Tables("RoyalYachtingAssociation2").Rows(0).Item(9)
RYAMembershipNumber.Text = ds.Tables("RoyalYachtingAssociation2").Rows(0).Item(10)
PositionInClub.Text = ds.Tables("RoyalYachtingAssociation2").Rows(0).Item(11)
MemberID.Text = ds.Tables("RoyalYachtingAssociation2").Rows(0).Item(12)
con.Close()
MaxRows = ds.Tables("RoyalYachtingAssociation2").Rows.Count
inc = 0
BoatDefectsAndOwnsThem.Show()
End Sub
