Error: Must declare the scalar variable "@sqlPolicyNo".

[anandkasotia]

I'm getting an error on aSqlDataReader = aSqlCommand.ExecuteReader(): Must declare the scalar variable "@sqlPolicyNo".

Here's the code:

    Private Sub RetrievePolicyId(ByRef anAutoPolicy As AutoPolicy)

        Try
            If aSqlConn.State <> ConnectionState.Open Then
                aSqlConn.Open()
            End If

            aQuery = "Select PolicyId " & _
                     "From V4Policy " & _
                     "Where PolicyNo = @sqlPolicyNo " & _
                        "and SystemCd = @sqlSystemCd " & _
                        "and StateNo = @sqlStateNo " & _
                        "and LastActivityDt = @sqlLastActivityDt " & _
                     "Order By PolicyId"

            aSqlParameter = aSqlCommand.Parameters.Add("sqlPolicyNo", SqlDbType.Char, 20)
            aSqlParameter.Value = anAutoPolicy.PolicyNumber

            aSqlParameter = aSqlCommand.Parameters.Add("sqlSystemCd", SqlDbType.Char, 1)
            aSqlParameter.Value = anAutoPolicy.SystemCode

            aSqlParameter = aSqlCommand.Parameters.Add("sqlStateNo", SqlDbType.Int)
            aSqlParameter.Value = anAutoPolicy.StateNumber

            aSqlParameter = aSqlCommand.Parameters.Add("sqlLastActivityDt", SqlDbType.DateTime)
            aSqlParameter.Value = anAutoPolicy.LastActivityDate

            aSqlCommand = aSqlConn.CreateCommand()
            aSqlCommand.CommandText = aQuery
            aSqlDataReader = aSqlCommand.ExecuteReader()

            If aSqlDataReader.HasRows() Then
                While aSqlDataReader.Read
                    anAutoPolicy.PolicyId = aSqlDataReader.GetInt32(0)
                    Exit While
                End While
            Else
                anAutoPolicy.Results = "Policy not found in Table" & anAutoPolicy.PolicyNumber
            End If

            aSqlDataReader.Close()

        Catch ex As Exception
            MessageBox.Show(ex.Message & " Error in RetrievePolicyId(): " & anAutoPolicy.PolicyNumber, "Fatal Error!!!", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            'If aSqlConn.State = ConnectionState.Open Then
            '    aSqlConn.Close()
            'End If
        End Try

    End Sub

 

[kulrom]

i will test it later myself but for now please try to change this code

            aSqlParameter = aSqlCommand.Parameters.Add("sqlPolicyNo", SqlDbType.Char, 20)
            aSqlParameter.Value = anAutoPolicy.PolicyNumber

            aSqlParameter = aSqlCommand.Parameters.Add("sqlSystemCd", SqlDbType.Char, 1)
            aSqlParameter.Value = anAutoPolicy.SystemCode

            aSqlParameter = aSqlCommand.Parameters.Add("sqlStateNo", SqlDbType.Int)
            aSqlParameter.Value = anAutoPolicy.StateNumber

            aSqlParameter = aSqlCommand.Parameters.Add("sqlLastActivityDt", SqlDbType.DateTime)
            aSqlParameter.Value = anAutoPolicy.LastActivityDate

            aSqlCommand = aSqlConn.CreateCommand()
            aSqlCommand.CommandText = aQuery

to this

            aSqlCommand = aSqlConn.CreateCommand()
            aSqlCommand.CommandText = aQuery
            aSqlCommand.Parameters.Add("@sqlPolicyNo", SqlDbType.Char).Value = anAutoPolicy.PolicyNumber
            aSqlCommand.Parameters.Add("@sqlSystemCd", SqlDbType.Char).Value = anAutoPolicy.SystemCode
            aSqlCommand.Parameters.Add("@sqlStateNo", SqlDbType.Int).Value = anAutoPolicy.StateNumber
            aSqlCommand.Parameters.Add("@sqlLastActivityDt", SqlDbType.DateTime).Value = anAutoPolicy.LastActivityDate

 

[anandkasotia]

That worked.

Thank you so much.
That did it! I believe I needed these 2 statements before adding the parameters:

aSqlCommand = aSqlConn.CreateCommand()
aSqlCommand.CommandText = aQuery

I also like the abbreviated version of adding parameters.
Thanks again!