Parameter Query

[tate]

I'm having a problem using a parameter as part of the SELECT statement. Can someone please help me to understand why I get the following error;

 

"The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not String objects"

 

Thanks in advance for the help.

 

*********** Here is the code *******************************

 

Dim daStudy As New OleDbDataAdapter("SELECT * FROM tblStudy WHERE StudyID = @StudyID", conSmallGrp)

Dim dsStudy As New DataSet

 

Me.ShowStudy(ddlStudy.SelectedItem.Value)

 

Private Sub ShowStudy(ByVal StudyID As Integer)

daStudy.SelectCommand.Parameters.Add("@StudyID")

daStudy.SelectCommand.Parameters("@StudyID").Value = StudyID

' Fill dataset that will be used to populate text boxes

daStudy.Fill(dsStudy, "tblStudy")

txtStudyID = dsStudy.Tables("tblStudy").Rows(0).Item("StudyID")

txtCategory = dsStudy.Tables("tblStudy").Rows(0).Item("CategoryID")

txtBookTitle = dsStudy.Tables("tblStudy").Rows(0).Item("Book")

txtAuthor = dsStudy.Tables("tblStudy").Rows(0).Item("Author")

txtISBN = dsStudy.Tables("tblStudy").Rows(0).Item("Isbn")

txtSummary = dsStudy.Tables("tblStudy").Rows(0).Item("Summary")

End Sub

[Joe Mamma]

Oledb doesnt support named parameters in the command text - you can name them as you set them.

 

try this:

 

 

 

 

[indent]Dim daStudy As New OleDbDataAdapter("SELECT * FROM tblStudy WHERE StudyID = ?", conSmallGrp) 


Dim dsStudy As New DataSet

Me.ShowStudy(ddlStudy.SelectedItem.Value)



[/indent]
Private Sub ShowStudy(ByVal StudyID As Integer)
[indent]daStudy.SelectCommand.Parameters.Add("StudyID", StudyID)


daStudy.Fill(dsStudy, "tblStudy")

txtStudyID = dsStudy.Tables("tblStudy").Rows(0).Item("StudyID")
txtCategory = dsStudy.Tables("tblStudy").Rows(0).Item("CategoryID")
txtBookTitle = dsStudy.Tables("tblStudy").Rows(0).Item("Book")
txtAuthor = dsStudy.Tables("tblStudy").Rows(0).Item("Author")
txtISBN = dsStudy.Tables("tblStudy").Rows(0).Item("Isbn")
txtSummary = dsStudy.Tables("tblStudy").Rows(0).Item("Summary")



[/indent]
End Sub 

[tate]

Thank you for your reply. However, after making the change you requested I now receive the following error;

 

"Specified cast is not valid"

 

Which looks to be in the positive direction but now it appears their is a problem with the MS Access data type for the index value. I thought the auto-number field would be an integer. By the way, this is the same error received if I use the original code presented with 1 change;

 

daStudy.SelectCommand.Parameters.Add("@StudyID")

 

changed to

 

daStudy.SelectCommand.Parameters.Add("@StudyID", OleDbType.Integer)

[cpopham]

The Autonumber field of access uses Long Integer as the data type I believe.

 

Chester

[tate]

I finally figured it out so the code is posted below for all to see how you can use parameterized queries in MS Access.

 

 

 

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

If Not IsPostBack Then

' Create data adapter and dataset for ddlStudy then fill

Dim daStudyList As New OleDbDataAdapter("SELECT StudyID, Study FROM tblStudy", conSmallGrp)

Dim dsStudyList As New DataSet

Try

' Open connection

conSmallGrp.Open()

' Fill dataset used to populate ddlStudy

daStudyList.Fill(dsStudyList, "tblStudyList")

' Populate ddlStudy

ddlStudy.DataSource = dsStudyList.Tables("tblStudyList")

ddlStudy.DataValueField = "StudyID"

ddlStudy.DataTextField = "Study"

ddlStudy.DataBind()

' If there is data fill text boxes

If dsStudyList.Tables("tblStudyList").Rows.Count > 0 Then

Me.ShowStudy(ddlStudy.SelectedItem.Value)

End If

Catch ex As Exception

lblTest.Text = ex.Message

Finally

conSmallGrp.Close()

End Try

End If

End Sub

 

Private Sub ShowStudy(ByVal StudyID As Integer)

' Create data adapter and dataset for text boxes then fill the dataset

Dim daStudy As New OleDbDataAdapter("SELECT * FROM tblStudy WHERE StudyID = @StudyID", conSmallGrp)

Dim dsStudy As New DataSet

daStudy.SelectCommand.Parameters.Add("@StudyID", OleDbType.Integer)

daStudy.SelectCommand.Parameters("@StudyID").Value = StudyID

' Fill dataset that will be used to populate text boxes

daStudy.Fill(dsStudy, "tblStudy")

txtStudyID.Text = CStr(dsStudy.Tables("tblStudy").Rows(0).Item("StudyID"))

txtCategory.Text = CStr(dsStudy.Tables("tblStudy").Rows(0).Item("CategoryID"))

txtBookTitle.Text = dsStudy.Tables("tblStudy").Rows(0).Item("Book")

txtAuthor.Text = dsStudy.Tables("tblStudy").Rows(0).Item("Author")

txtISBN.Text = dsStudy.Tables("tblStudy").Rows(0).Item("Isbn")

txtSummary.Text = dsStudy.Tables("tblStudy").Rows(0).Item("Summary")

End Sub