Query Problems

Discussion in 'ASP .Net' started by Don Kim, Sep 7, 2004.

  1. Don Kim

    Don Kim Guest

    Hi,


    I'm trying to port the IGo Portal on the gotdotnet site from vb.net to c#.
    I'm having problems with the following:

    public DataSet GetAnnouncements(int moduleId) {

    // Create Instance of Connection and Command Object
    SqlConnection myConnection = new
    SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
    SqlDataAdapter myCommand = new SqlDataAdapter("SELECT ItemID,
    CreatedByUser, CreatedDate, Title, MoreLink, MobileMoreLink, ExpireDate,
    Description FROM Portal_Announcements WHERE ModuleID = ? AND ExpireDate >
    ?", myConnection);

    // Mark the Command
    myCommand.SelectCommand.CommandType = CommandType.Text;

    // Add Parameters
    SqlParameter parameterModuleId = new SqlParameter("@ModuleID",
    SqlDbType.Int, 4);
    parameterModuleId.Value = moduleId;
    myCommand.SelectCommand.Parameters.Add(parameterModuleId);

    // Add Parameters
    SqlParameter parameterExpireDate = new SqlParameter("@ExpireDate",
    SqlDbType.DateTime);
    parameterExpireDate.Value = DateTime.Now;
    myCommand.SelectCommand.Parameters.Add(parameterExpireDate);

    // Create and Fill the DataSet
    DataSet myDataSet = new DataSet();
    myCommand.Fill(myDataSet);

    // Return the DataSet
    return myDataSet;
    }



    I get this error: System.Data.SqlClient.SqlException: Line 1: Incorrect
    syntax near '?'.


    Does anyone have any ideas? Thanks.

    - Don Kim
     
    Don Kim, Sep 7, 2004
    #1
    1. Advertising

  2. "Don Kim" <> wrote in
    news:lXj%c.12648$:

    > Hi,
    >
    >
    > I'm trying to port the IGo Portal on the gotdotnet site from
    > vb.net to c#. I'm having problems with the following:
    >
    > public DataSet GetAnnouncements(int moduleId) {
    >
    > // Create Instance of Connection and Command Object
    > SqlConnection myConnection = new
    > SqlConnection(ConfigurationSettings.AppSettings["connectionString
    > "]);
    > SqlDataAdapter myCommand = new
    > SqlDataAdapter("SELECT ItemID,
    > CreatedByUser, CreatedDate, Title, MoreLink, MobileMoreLink,
    > ExpireDate, Description FROM Portal_Announcements WHERE ModuleID
    > = ? AND ExpireDate > ?", myConnection);
    >
    > // Mark the Command
    > myCommand.SelectCommand.CommandType =
    > CommandType.Text;
    >
    > // Add Parameters
    > SqlParameter parameterModuleId = new
    > SqlParameter("@ModuleID",
    > SqlDbType.Int, 4);
    > parameterModuleId.Value = moduleId;
    > myCommand.SelectCommand.Parameters.Add(parameterModul
    > eId);
    >
    > // Add Parameters
    > SqlParameter parameterExpireDate = new
    > SqlParameter("@ExpireDate",
    > SqlDbType.DateTime);
    > parameterExpireDate.Value = DateTime.Now;
    > myCommand.SelectCommand.Parameters.Add(parameterExpir
    > eDate);
    >
    > // Create and Fill the DataSet
    > DataSet myDataSet = new DataSet();
    > myCommand.Fill(myDataSet);
    >
    > // Return the DataSet
    > return myDataSet;
    > }
    >
    >
    >
    > I get this error: System.Data.SqlClient.SqlException: Line 1:
    > Incorrect syntax near '?'.
    >
    > Does anyone have any ideas? Thanks.


    Don,

    Parameters in SQL commands used in the System.Data.SqlClient
    namespace are referenced by name, not position. Therefore, you need
    to replace the "?" placeholders in the SELECT statement with the
    parameter name:

    SqlDataAdapter myCommand = new SqlDataAdapter(
    @"SELECT ItemID, CreatedByUser, CreatedDate, Title, MoreLink,
    MobileMoreLink, ExpireDate, Description FROM
    Portal_Announcements WHERE ModuleID = @ModuleID AND
    ExpireDate > @ExpireDate", myConnection);

    --
    Hope this helps.

    Chris.
    -------------
    C.R. Timmons Consulting, Inc.
    http://www.crtimmonsinc.com/
     
    Chris R. Timmons, Sep 7, 2004
    #2
    1. Advertising

  3. Don Kim

    Don Kim Guest

    > SqlDataAdapter myCommand = new SqlDataAdapter(
    > @"SELECT ItemID, CreatedByUser, CreatedDate, Title, MoreLink,
    > MobileMoreLink, ExpireDate, Description FROM
    > Portal_Announcements WHERE ModuleID = @ModuleID AND
    > ExpireDate > @ExpireDate", myConnection);



    Absolutely correct. Forgot that oledb provider is different from sql.
    Thanks.

    - Don Kim
     
    Don Kim, Sep 7, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Learner
    Replies:
    1
    Views:
    1,024
    Marina Levit [MVP]
    Jan 30, 2006
  2. Anonymous
    Replies:
    0
    Views:
    1,548
    Anonymous
    Oct 13, 2005
  3. David Gordon

    xpath query query

    David Gordon, May 18, 2005, in forum: XML
    Replies:
    2
    Views:
    831
    David Gordon
    May 18, 2005
  4. Eric Nelson
    Replies:
    5
    Views:
    1,577
    Alexey Smirnov
    Feb 4, 2009
  5. Jon F.

    CAML Query: Multiple Query Fields Issue

    Jon F., May 12, 2004, in forum: ASP .Net Web Services
    Replies:
    0
    Views:
    774
    Jon F.
    May 12, 2004
Loading...

Share This Page