asked on

parameterized query with SQL

What I want is to retrieve only record at a time. To retrieve a data., enter a nameID and click on the button1. Then if a row is found with that nameID, the row is retreived and diplay data on the form(txtCity, txtState and txtZip)
.................

An unhandled exception of type 'System.IndexOutOfRangeException' occurred in system.data.dll

Additional information: An SqlParameter with ParameterName '@nameID' is not contained by this SqlParameterCollection.

................

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim iRecords As Integer

If Me.TextBox1.Text <> "" Then

SqlDataAdapter1.SelectCommand.Parameters("@nameID").Value _
= Me.TextBox1.Text

DataSet11.Clear()
iRecords = SqlDataAdapter1.Fill(DataSet11)

If iRecords = 0 Then
Me.SetEntryControls(False)
MessageBox.Show("Error", "Entry Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
Me.SetEntryControls(True)
Me.TextBox1.Focus()

End If

End If
End Sub

Private Sub SetEntryControls(ByVal bEditmode As Boolean)

Me.txtCity.Enabled = bEditmode
Me.txtState.Enabled = bEditmode
Me.txtZip.Enabled = bEditmode

End Sub

LordWabbit

You need to add the @nameID parameter, you are trying to reference it in your parameters collection before it's been added.
SqlDataAdapter1.SelectCommand.Parameters.Add("@nameID", Me.TextBox1.Text)

After that you can change it with

SqlDataAdapter1.SelectCommand.Parameters("@nameID").Value _
= Me.TextBox1.Text

if need be and continue using the same data adapter. Just remember that if you try to add @nameID again it will fail because it's already in the collection, so either modify it or alternatively you can clear the parameters collection and add again.

ASKER CERTIFIED SOLUTION

LordWabbit

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial