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: 2148
  • Last Modified:

SqlDataSource: SqlExcepion upon databinding: Must declare the scalar variable...

I am programmatically creating a SqlDataSource as in the attached code.  However, when I run the code, where I call the DataBind I get a "SqlException: Must declare the scalar variable @prefix, Must declare the scalar variable @UserId"

However, in debug mode, I can see that the two parameters are actually populated in the SelectParameters collection.

What am I missing?
//The function that creates the datasource:
        public static SqlDataSource  CreateDataSource(string connectionName, MembershipUser user, string prefix)
        {
            string connectionString = WebConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
            string query = "SELECT Column1 As AnchorText, @prefix + Column2 As FullUrl FROM table1 where RoleId IN (select RoleId from vw_aspnet_UsersInRoles WHERE UserId = @UserId)";
            SqlDataSource source = new SqlDataSource(connectionString, query);
            source.DataSourceMode = SqlDataSourceMode.DataReader;
            source.SelectParameters.Add("@prefix", TypeCode.String, prefix);
            source.SelectParameters.Add("@UserId", TypeCode.String, user.ProviderUserKey.ToString());
            return source;
        }
 
//The .aspx file
    <asp:Repeater ID="ApplicationMenu" runat="server">
        <ItemTemplate>
            <a href="<%# ((System.Data.DataRowView)Container.DataItem)[FullUrl] %>"><%# ((System.Data.DataRowView)Container.DataItem)["AnchorText"] %></a>
        </ItemTemplate>
    </asp:Repeater>
 
//The Codebehind file.
    public partial class Menu : System.Web.UI.Page
    {
        protected SqlDataSource source;
        protected void Page_Load(object sender, EventArgs e)
        {
            source = DataSourceFactory.CreateDataSource("Connection", Membership.GetUser(), "http://someserver.com/");
            ApplicationMenu.DataSource = source;
            ApplicationMenu.DataBind(); // ****** Error Happens Here ******
        }
    }

Open in new window

0
davisnw
Asked:
davisnw
1 Solution
 
MikeMCSDCommented:
Not sure, but try changing this:
  "SELECT Column1 As AnchorText, @prefix + Column2 As FullUrl
to this:
  "SELECT Column1 As AnchorText, @prefix + Column2 As 'FullUrl'

I put single quotes around FullUrl
0
 
Jaime OlivaresSoftware ArchitectCommented:
try remove the @ symbols:
           source.SelectParameters.Add("prefix", TypeCode.String, prefix);
            source.SelectParameters.Add("UserId", TypeCode.String, user.ProviderUserKey.ToString());
 
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
//The function that creates the datasource:
        public static SqlDataSource  CreateDataSource(string connectionName, MembershipUser user, string prefix)
        {
            string connectionString = WebConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
            string query = "SELECT Column1 As AnchorText, ? + Column2 As FullUrl FROM table1 where RoleId IN (select RoleId from vw_aspnet_UsersInRoles WHERE UserId = ? )";
            SqlDataSource source = new SqlDataSource(connectionString, query);
            source.DataSourceMode = SqlDataSourceMode.DataReader;
            source.SelectParameters.Add("prefix", TypeCode.String, prefix);
            source.SelectParameters.Add("UserId", TypeCode.String, user.ProviderUserKey.ToString());
            return source;

Open in new window

0
 
davisnwAuthor Commented:
Removing the @ symbols fixed it!  Thanks!
0

Featured Post

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now