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

 

[Chris R. Timmons]

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);

 

[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