Parameter Query

T

tate

Well-known member
Joined
Nov 10, 2003
Messages
116
Im 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
 
Oledb doesnt support named parameters in the command text - you can name them as you set them.

try this:




Code: 
[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
 
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)
 
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

 
Back
Top