parameterized query with SQL

Posted on 2006-03-30
Last Modified: 2010-04-23
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

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

            iRecords = SqlDataAdapter1.Fill(DataSet11)

            If  iRecords = 0 Then
                MessageBox.Show("Error", "Entry Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

            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
Question by:VBdotnet2005
    LVL 11

    Expert Comment

    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.
    LVL 11

    Accepted Solution

    oh yeah forgot to say that if you want only one record in the table you can add TOP 1 to your select statement.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Suggested Solutions

    If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now