locked
Error in the Where clause RRS feed

  • Question

  • I am facing following error while developing the application...Please help......

    Code:

    <%

    @ Page Language="C#" %>

    <%

    @ Import Namespace="OSGeo.MapGuide" %>

     

     

    <%

     

     

    String sessionId = Request.Form.Get("SESSION");

     

    String id = Request.Form.Get("id");

     

    Int64 id1 = Convert.ToInt64(id);

    %>

    <!

     

    DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <

     

    html xmlns="http://www.w3.org/1999/xhtml" >

    <

     

    head runat="server">

     

    <title>Untitled Page</title>

    </

     

    head>

    <

     

    body>

     

    <form id="form1" runat="server">

     

    <div>

     

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DataStore8ConnectionString3 %>"

     

    SelectCommand="SELECT [name] FROM [Table_1] where [FeatId]= id1"></asp:SqlDataSource>

     

    &nbsp;</div>

     

    <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1"

     

    DataTextField="name" DataValueField="name">

     

    </asp:DropDownList>

     

    </form>

    </

     

    body>

    </

     

    html>




    Error:

    Invalid column name 'id1'.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Invalid column name 'id1'.

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

    Stack Trace:

    [SqlException (0x80131904): Invalid column name 'id1'.]
       System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826
       System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747
       System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
       System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
       System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
       System.Data.SqlClient.SqlDataReader.get_MetaData() +83
       System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
       System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
       System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
       System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
       System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
       System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
       System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
       System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +130
       System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +287
       System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +92
       System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1297
       System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +95
       System.Web.UI.WebControls.ListControl.PerformSelect() +34
       System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +73
       System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
       System.Web.UI.WebControls.BaseDataBoundControl.OnPreRender(EventArgs e) +22
       System.Web.UI.WebControls.ListControl.OnPreRender(EventArgs e) +18
       System.Web.UI.Control.PreRenderRecursiveInternal() +80
       System.Web.UI.Control.PreRenderRecursiveInternal() +171
       System.Web.UI.Control.PreRenderRecursiveInternal() +171
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +842
    


    Version Information: Microsoft .NET Framework Version:2.0.50727.3053; ASP.NET Version:2.0.50727
    Wednesday, April 15, 2009 11:05 AM

Answers

  • oh, yeah you have to add the paraemters first

    e.command.Parameters.AddWithValue("@Id1", Id1)


    Here the first argument is the name of the parameter and the second is the variable which you want to add.

    Ganesh Ranganathan
    [Please mark the post as answer if you find it helpful]
    • Marked as answer by Syeda Haider Thursday, April 16, 2009 5:59 AM
    Thursday, April 16, 2009 5:44 AM

All replies

  • Is id1 a variable?

    If so, then shouldnt it be enclosed in quotes?
    Ganesh Ranganathan
    [Please mark the post as answer if you find it helpful]
    Wednesday, April 15, 2009 11:20 AM
  • Yes id1 is variable but If i encolse within single quotes then get the following error:

    Incorrect syntax near 'id1'.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'id1'.

    If I use

    SelectCommand

     

    ="SELECT [name] FROM [Table_1] WHERE ([FeatId] = " + id1>


    then following error occurs:
    Error 5 Literal expressions like '<%$ ConnectionStrings:DataStore8ConnectionString4 %>' are not allowed. Use <asp:Literal runat="server" Text="<%$ ConnectionStrings:DataStore8ConnectionString4%>" /> instead. c:\inetpub\wwwroot\development\Owner.aspx 23 

    Please help......
    Wednesday, April 15, 2009 11:45 AM
  • You should use a parameter to define the variable

    SELECT [name] FROM [Table_1] WHERE ([FeatId] = @id1

    You can write code for On Selecting event of the datasource and set the parameter from EventArgs object

    protected void SqlDataSource1_Selecting(object sender,
    SqlDataSourceSelectingEventArgs e)
    {
    //You can access parameter collection by e.Command.Parameters
    }



    Ganesh Ranganathan
    [Please mark the post as answer if you find it helpful]
    Wednesday, April 15, 2009 12:02 PM
  • By the way, if the parameter is being passed from a control, you can also use the asp:Control Parameter or the asp:QueryString Parameter to define its value.
    Ganesh Ranganathan
    [Please mark the post as answer if you find it helpful]
    Wednesday, April 15, 2009 12:04 PM
  • By the way, if the parameter is being passed from a control, you can also use the asp:Control Parameter or the asp:QueryString Parameter to define its value.
    Ganesh Ranganathan
    [Please mark the post as answer if you find it helpful]

    Thanks for the help but what i need to write in
    protected void SqlDataSource1_Selecting(object sender,
    SqlDataSourceSelectingEventArgs e)
    {
    //You can access parameter collection by e.Command.Parameters
    }
    

    I am not able to understand how to use the variable.....The parameter is not being passed from the control or Query String. it is being passed from a form and then stored in id1 variable.....

    I wiil be really grateful to you ffor help in this context...
    Wednesday, April 15, 2009 12:12 PM
  • Try this code:-
    protected void SqlDataSource1_Selecting(object sender,
    SqlDataSourceSelectingEventArgs e)
    {
    e.Command.Parameters["@ID1"].Value = Id1;
    }
    

    Ganesh Ranganathan
    [Please mark the post as answer if you find it helpful]
    Wednesday, April 15, 2009 12:40 PM
  • Try this code:-
    protected void SqlDataSource1_Selecting(object sender,
    
    SqlDataSourceSelectingEventArgs e)
    
    {
    
    e.Command.Parameters["@ID1"].Value = Id1;
    
    }
    
    
    
    

    Ganesh Ranganathan
    [Please mark the post as answer if you find it helpful]
    I tried the above code and got the following error:


    System.IndexOutOfRangeException was unhandled by user code
      Message="An SqlParameter with ParameterName '@ID1' is not contained by this SqlParameterCollection."
      Source="System.Data"
      StackTrace:
           at System.Data.SqlClient.SqlParameterCollection.GetParameter(String parameterName)
           at System.Data.Common.DbParameterCollection.get_Item(String parameterName)
           at ASP.owner_aspx.SqlDataSource1_Selecting1(Object sender, SqlDataSourceSelectingEventArgs e) in c:\Inetpub\wwwroot\development\Owner.aspx:line 30
           at System.Web.UI.WebControls.SqlDataSourceView.OnSelecting(SqlDataSourceSelectingEventArgs e)
           at System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments)
           at System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e)
           at System.Web.UI.WebControls.ListControl.PerformSelect()
           at System.Web.UI.WebControls.BaseDataBoundControl.DataBind()
           at System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound()
           at System.Web.UI.WebControls.BaseDataBoundControl.OnPreRender(EventArgs e)
           at System.Web.UI.WebControls.ListControl.OnPreRender(EventArgs e)
           at System.Web.UI.Control.PreRenderRecursiveInternal()
           at System.Web.UI.Control.PreRenderRecursiveInternal()
           at System.Web.UI.Control.PreRenderRecursiveInternal()
           at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)



    Can you please send me some link on using the different parameters?
    Thanks for giving me your precious time.......
    Thursday, April 16, 2009 5:03 AM
  • oh, yeah you have to add the paraemters first

    e.command.Parameters.AddWithValue("@Id1", Id1)


    Here the first argument is the name of the parameter and the second is the variable which you want to add.

    Ganesh Ranganathan
    [Please mark the post as answer if you find it helpful]
    • Marked as answer by Syeda Haider Thursday, April 16, 2009 5:59 AM
    Thursday, April 16, 2009 5:44 AM
  • oh, yeah you have to add the paraemters first

    e.command.Parameters.AddWithValue("@Id1", Id1)


    Here the first argument is the name of the parameter and the second is the variable which you want to add.

    Ganesh Ranganathan
    [Please mark the post as answer if you find it helpful]

    Thanx alot it solved my problem.......
    Thursday, April 16, 2009 6:00 AM
  • Ur Welcome... :)
    Ganesh Ranganathan
    [Please mark the post as answer if you find it helpful]
    Thursday, April 16, 2009 6:04 AM