Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 523
  • Last Modified:

ASP.NET Error: Must declare the scalar variable "@Farm".

Hello,

I am having some trouble with an ASP.NET error.  I was under the impression that the following line....

"Dim dbParam_farm As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter"

was when I declared the variable; but I was wrong I guess.  Any hints?

Thank You
Sub FarmList_SelectedIndexChanged(sender As Object, e As EventArgs)
    HouseList.DataSource = HouseSelect(FarmList.SelectedItem.Text)
    HouseList.DataBind()
End Sub
 
Function HouseSelect(ByVal farm As String) As System.Data.DataSet
    Dim connectionStringa As String = ConfigurationManager.ConnectionStrings("HubbardRD").ConnectionString
    Dim conna As New OleDb.OleDbConnection(connectionStringa)
    Dim strSQLa As String
    strSQLa = "SELECT [Location].[House], [Location].[Location ID] As Loc FROM [Location] WHERE ([Location].[Farm] = @Farm)"
    Dim cmda As New OleDb.OleDbCommand(strSQLa, conna)
    Dim dataAdapter As New OleDb.OleDbDataAdapter(cmda)
    Dim dt As New DataTable
 
    Dim dbParam_farm As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
    Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
    dbParam_farm.ParameterName = "@Farm"
    dbParam_farm.Value = farm
    dbParam_farm.DbType = System.Data.DbType.[String]
    dbCommand.Parameters.Add(dbParam_farm)
 
    conna.Open()
    dataAdapter.Fill(dt)
    conna.Close()
End Function

Open in new window

0
Navicerts
Asked:
Navicerts
  • 4
  • 2
  • 2
  • +1
1 Solution
 
DhaestCommented:
I think that you need to declare it without @

dbParam_farm.ParameterName = "Farm"
0
 
carlnorrbomCommented:
Hi,

Seems overly complicated, why not simplify things a bit:

    Function HouseSelect(ByVal farm As String) As System.Data.DataTable
        Dim conna As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("HubbardRD").ConnectionString)
        Dim cmda As New OleDb.OleDbCommand("SELECT [Location].[House], [Location].[Location ID] As Loc FROM [Location] WHERE ([Location].[Farm] = @Farm)", conna)
        Dim dataAdapter As New OleDb.OleDbDataAdapter(cmda)
        Dim dt As New DataTable
        dataAdapter.SelectCommand.Parameters.AddWithValue("@Farm", farm)
        conna.Open()
        dataAdapter.Fill(dt)
        conna.Close()
        Return dt
    End Function

/Carl.
0
 
NavicertsAuthor Commented:
Still getting the same error after changing that.
    Function HouseSelect(ByVal farm As String) As System.Data.DataSet
        Dim connectionStringa As String = ConfigurationManager.ConnectionStrings("HubbardRD").ConnectionString
        Dim conna As New OleDb.OleDbConnection(connectionStringa)
        Dim strSQLa As String
        strSQLa = "SELECT [Location].[House], [Location].[Location ID] As Loc FROM [Location] WHERE ([Location].[Farm] = @Farm)"
        Dim cmda As New OleDb.OleDbCommand(strSQLa, conna)
        Dim dataAdapter As New OleDb.OleDbDataAdapter(cmda)
        Dim dt As New DataTable
 
        Dim dbParam_farm As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
        Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
        dbParam_farm.ParameterName = "Farm"
        dbParam_farm.Value = farm
        dbParam_farm.DbType = System.Data.DbType.[String]
        dbCommand.Parameters.Add(dbParam_farm)
 
        conna.Open()
        dataAdapter.Fill(dt)
        conna.Close()
    End Function

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
NavicertsAuthor Commented:
@carlnorrbom

Nice and compact :)  Still getting the same error though.
0
 
sunithnairCommented:
Why dont you try this
        Dim sqlConnection As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("HubbardRD").ConnectionString)
        Dim sqlCommand As New System.Data.SqlClient.SqlCommand("SELECT [Location].[House], [Location].[Location ID] As Loc FROM [Location] WHERE ([Location].[Farm] = @Farm)", sqlConnection)
        Dim dataAdapter As New System.Data.SqlClient.SqlDataAdapter(sqlCommand)
        Dim dt As New DataTable
        sqlCommand.Parameters.AddWithValue("@Farm", farm)
        sqlConnection.Open()
        dataAdapter.Fill(dt)
        sqlConnection.Close()
        HouseSelect = dt

Open in new window

0
 
sunithnairCommented:
Including the function this
    Function HouseSelect(ByVal farm As String) As System.Data.DataTable
        Dim sqlConnection As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("HubbardRD").ConnectionString)
        Dim sqlCommand As New System.Data.SqlClient.SqlCommand("SELECT [Location].[House], [Location].[Location ID] As Loc FROM [Location] WHERE ([Location].[Farm] = @Farm)", sqlConnection)
        Dim dataAdapter As New System.Data.SqlClient.SqlDataAdapter(sqlCommand)
        Dim dt As New DataTable
        sqlCommand.Parameters.AddWithValue("@Farm", farm)
        sqlConnection.Open()
        dataAdapter.Fill(dt)
        sqlConnection.Close()
        HouseSelect = dt
    End Function

Open in new window

0
 
NavicertsAuthor Commented:
@sunithnair

That may have worked but now I am getting an error regarding my connection string... "Keyword not supported: 'provider'. "

I think my connection string is OK as I use the same string to successfully access data with out variables.  I guess I need to change my string for SqlClient instead of OleDb?  Working on that now to see if this solved my problem.
0
 
NavicertsAuthor Commented:
Ahh yes that worked.  I just added a new connection for SqlClient to my web.config file and the function you gave worked great.  Thank You!
0
 
carlnorrbomCommented:
Hi,

Well, i thought you needed to use OleDb, not SqlClient, otherwise I would have suggested that from get go.

/Carl.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now