This is the code I have. It works fine but returns only one row. As you can see in the SQL Statement I need 2 rows returned in the datagrid that I use in the form. While the procedure reads 2 rows it only displays one row. NameAddrmark is a constructor for the fields.
public NameAddrmark GetNameRespCommentData(string respid)
{
NameAddrmark cms = new NameAddrmark();
//var cms = new List<NameAddrmark>();
SqlConnection connection = new SqlConnection(GeneralData.getConnectionString());
string sql = "SELECT top 2 * FROM dbo.RESPONDENT_COMMENT WHERE respid = " + GeneralData.AddSqlQuotes(respid) + " and USRNME = " + GeneralData.AddSqlQuotes(UserInfo.UserName) + " order by COMMDATE ASC";
SqlCommand command = new SqlCommand(sql, connection);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult);
while(reader.Read())
//if (reader.Read())
{
cms.Id = respid;
cms.Date8 = reader["COMMDATE"].ToString();
cms.Usrnme = reader["USRNME"].ToString();
cms.Marktext = reader["COMMTEXT"].ToString();
}
//else
// cms = null;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return cms;
}
On my form I return cms to a list. Set the datasource of the datagrid to the list. Displays first row correctly but I need two rows displayed. Hope someone can help. Thanks.
You can return a List<NameAddrmark>
:
public List<NameAddrmark> GetNameRespCommentData(string respid)
{
List<NameAddrmark> cmsList = new List<NameAddrmark>();
SqlConnection connection = new SqlConnection("");
string sql = "SELECT top 2 * FROM dbo.RESPONDENT_COMMENT WHERE respid = " + GeneralData.AddSqlQuotes(respid) + " and USRNME = " + GeneralData.AddSqlQuotes(UserInfo.UserName) + " order by COMMDATE ASC";
SqlCommand command = new SqlCommand(sql, connection);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult);
while (reader.Read())
{
NameAddrmark cms = new NameAddrmark();
cms.Id = respid;
cms.Date8 = reader["COMMDATE"].ToString();
cms.Usrnme = reader["USRNME"].ToString();
cms.Marktext = reader["COMMTEXT"].ToString();
cmsList.Add(cms);
}
} catch (SqlException ex)
{
throw ex;
} finally
{
connection.Close();
}
return cmsList;
}
But you should really use parameters instead of string concatenation to prevent sql injection.