Query Problems

[Don Kim]

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(parameterMo duleId);

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

// 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

[Chris R. Timmons]

"Don Kim" <in**@nospam.donkim.info> wrote in
news:lX*******************@newssvr21.news.prodigy. com:

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(parameterMo dul
eId);

// Add Parameters
SqlParameter parameterExpireDate = new
SqlParameter("@ExpireDate",
SqlDbType.DateTime);
parameterExpireDate.Value = DateTime.Now;
myCommand.SelectCommand.Parameters.Add(parameterEx pir
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/

[Don Kim]

> 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