Different results with the same query?!?

  • Thread starter Thread starter trendum
  • Start date Start date
T

trendum

Hi all,
I want to check if a record exists in the DB.
I use two different ways to create the sql query.
One way is by using SqlCommand and SqlParameter.
The other way by building the query myself (without SqlParameter(s))
and using the SqlCommand to execute it.
In one I get false in the other i get true.
Can anyone help me?

////////////////////////////////////////////////
//Build the query using SqlParameters
////////////////////////////////////////////////
SqlCommand m_MessageSelectCommand = new SqlCommand();
m_MessageSelectCommand.CommandText =
"SELECT MessageNumber FROM Messages WHERE
CommunityNumber=@CommunityNumber and ForumParent=@ForumParent and
ForumNumber=@ForumNumber and WrittenBy=@WrittenBy and
MessageDate=@MessageDate";
m_MessageSelectCommand.Connection = m_Connection;
m_MessageSelectCommand.CommandTimeout = 0;
m_MessageSelectCommand.Parameters.Add(new
SqlParameter("@CommunityNumber", System.Data.SqlDbType.Int, 4,
"CommunityNumber"));
m_MessageSelectCommand.Parameters.Add(new SqlParameter("@ForumNumber",
System.Data.SqlDbType.Int, 4, "ForumNumber"));
m_MessageSelectCommand.Parameters.Add(new SqlParameter("@ForumParent",
System.Data.SqlDbType.Int, 4, "ForumParent"));
m_MessageSelectCommand.Parameters.Add(new SqlParameter("@WrittenBy",
System.Data.SqlDbType.NVarChar, 100, "WrittenBy"));
m_MessageSelectCommand.Parameters.Add(new SqlParameter("@MessageDate",
System.Data.SqlDbType.DateTime, 8, "MessageDate"));

// later in the code
m_MessageSelectCommand.Parameters["@CommunityNumber"].Value =
dlMessage.m_CommunityNumber;
m_MessageSelectCommand.Parameters["@ForumParent"].Value =
dlMessage.m_ForumNumber;
m_MessageSelectCommand.Parameters["@ForumNumber"].Value =
dlMessage.m_TopicNumber;
m_MessageSelectCommand.Parameters["@WrittenBy"].Value =
dlMessage.m_MessageAuthor;
m_MessageSelectCommand.Parameters["@MessageDate"].Value =
dlMessage.m_MessageDate;
SqlDataReader reader = m_MessageSelectCommand.ExecuteReader();
bool b = reader.HasRows;
// B IS FALSE!!!

////////////////////////////////////////////////
//The other way - just build the query by myself
////////////////////////////////////////////////
System.Text.StringBuilder sql = new System.Text.StringBuilder(256);
sql.Append("SELECT MessageNumber FROM Messages WHERE
CommunityNumber=");
sql.Append(dlMessage.m_CommunityNumber);
sql.Append(" and ForumParent=");
sql.Append(dlMessage.m_ForumNumber);
sql.Append(" and ForumNumber=");
sql.Append(dlMessage.m_TopicNumber);
sql.Append(" and WrittenBy='");
sql.Append(dlMessage.m_MessageAuthor);
sql.Append("' and MessageDate='");
sql.Append(dlMessage.m_MessageDate);
sql.Append("'");
SqlCommand c = new SqlCommand(sql.ToString(), m_Connection);
SqlDataReader reader = c.ExecuteReader();
bool b = reader.HasRows;
// I get that b is true.
This happens durring the same program execution
How can this be???
 
Check the resulting strings from each after you add all of the information
(just prior to running the command). By finding differences in the resulting
SQL statement, you will be able to determine where the code is different and
figure out how to fix it. That is my suggestion.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

trendum said:
Hi all,
I want to check if a record exists in the DB.
I use two different ways to create the sql query.
One way is by using SqlCommand and SqlParameter.
The other way by building the query myself (without SqlParameter(s))
and using the SqlCommand to execute it.
In one I get false in the other i get true.
Can anyone help me?

////////////////////////////////////////////////
//Build the query using SqlParameters
////////////////////////////////////////////////
SqlCommand m_MessageSelectCommand = new SqlCommand();
m_MessageSelectCommand.CommandText =
"SELECT MessageNumber FROM Messages WHERE
CommunityNumber=@CommunityNumber and ForumParent=@ForumParent and
ForumNumber=@ForumNumber and WrittenBy=@WrittenBy and
MessageDate=@MessageDate";
m_MessageSelectCommand.Connection = m_Connection;
m_MessageSelectCommand.CommandTimeout = 0;
m_MessageSelectCommand.Parameters.Add(new
SqlParameter("@CommunityNumber", System.Data.SqlDbType.Int, 4,
"CommunityNumber"));
m_MessageSelectCommand.Parameters.Add(new SqlParameter("@ForumNumber",
System.Data.SqlDbType.Int, 4, "ForumNumber"));
m_MessageSelectCommand.Parameters.Add(new SqlParameter("@ForumParent",
System.Data.SqlDbType.Int, 4, "ForumParent"));
m_MessageSelectCommand.Parameters.Add(new SqlParameter("@WrittenBy",
System.Data.SqlDbType.NVarChar, 100, "WrittenBy"));
m_MessageSelectCommand.Parameters.Add(new SqlParameter("@MessageDate",
System.Data.SqlDbType.DateTime, 8, "MessageDate"));

// later in the code
m_MessageSelectCommand.Parameters["@CommunityNumber"].Value =
dlMessage.m_CommunityNumber;
m_MessageSelectCommand.Parameters["@ForumParent"].Value =
dlMessage.m_ForumNumber;
m_MessageSelectCommand.Parameters["@ForumNumber"].Value =
dlMessage.m_TopicNumber;
m_MessageSelectCommand.Parameters["@WrittenBy"].Value =
dlMessage.m_MessageAuthor;
m_MessageSelectCommand.Parameters["@MessageDate"].Value =
dlMessage.m_MessageDate;
SqlDataReader reader = m_MessageSelectCommand.ExecuteReader();
bool b = reader.HasRows;
// B IS FALSE!!!

////////////////////////////////////////////////
//The other way - just build the query by myself
////////////////////////////////////////////////
System.Text.StringBuilder sql = new System.Text.StringBuilder(256);
sql.Append("SELECT MessageNumber FROM Messages WHERE
CommunityNumber=");
sql.Append(dlMessage.m_CommunityNumber);
sql.Append(" and ForumParent=");
sql.Append(dlMessage.m_ForumNumber);
sql.Append(" and ForumNumber=");
sql.Append(dlMessage.m_TopicNumber);
sql.Append(" and WrittenBy='");
sql.Append(dlMessage.m_MessageAuthor);
sql.Append("' and MessageDate='");
sql.Append(dlMessage.m_MessageDate);
sql.Append("'");
SqlCommand c = new SqlCommand(sql.ToString(), m_Connection);
SqlDataReader reader = c.ExecuteReader();
bool b = reader.HasRows;
// I get that b is true.
This happens durring the same program execution
How can this be???
Click to expand...
 
trendum,

I notice that when you build the query yourself without using parameters
that you surround MessageDate with single-quote characters.

Is the single-quote character the correct delimiter for date values in SQL
Server?

Perhaps you need to surround date values with another delimiter, such as the
pound-sign character.

Kerry Moorman


trendum said:
Hi all,
I want to check if a record exists in the DB.
I use two different ways to create the sql query.
One way is by using SqlCommand and SqlParameter.
The other way by building the query myself (without SqlParameter(s))
and using the SqlCommand to execute it.
In one I get false in the other i get true.
Can anyone help me?

////////////////////////////////////////////////
//Build the query using SqlParameters
////////////////////////////////////////////////
SqlCommand m_MessageSelectCommand = new SqlCommand();
m_MessageSelectCommand.CommandText =
"SELECT MessageNumber FROM Messages WHERE
CommunityNumber=@CommunityNumber and ForumParent=@ForumParent and
ForumNumber=@ForumNumber and WrittenBy=@WrittenBy and
MessageDate=@MessageDate";
m_MessageSelectCommand.Connection = m_Connection;
m_MessageSelectCommand.CommandTimeout = 0;
m_MessageSelectCommand.Parameters.Add(new
SqlParameter("@CommunityNumber", System.Data.SqlDbType.Int, 4,
"CommunityNumber"));
m_MessageSelectCommand.Parameters.Add(new SqlParameter("@ForumNumber",
System.Data.SqlDbType.Int, 4, "ForumNumber"));
m_MessageSelectCommand.Parameters.Add(new SqlParameter("@ForumParent",
System.Data.SqlDbType.Int, 4, "ForumParent"));
m_MessageSelectCommand.Parameters.Add(new SqlParameter("@WrittenBy",
System.Data.SqlDbType.NVarChar, 100, "WrittenBy"));
m_MessageSelectCommand.Parameters.Add(new SqlParameter("@MessageDate",
System.Data.SqlDbType.DateTime, 8, "MessageDate"));

// later in the code
m_MessageSelectCommand.Parameters["@CommunityNumber"].Value =
dlMessage.m_CommunityNumber;
m_MessageSelectCommand.Parameters["@ForumParent"].Value =
dlMessage.m_ForumNumber;
m_MessageSelectCommand.Parameters["@ForumNumber"].Value =
dlMessage.m_TopicNumber;
m_MessageSelectCommand.Parameters["@WrittenBy"].Value =
dlMessage.m_MessageAuthor;
m_MessageSelectCommand.Parameters["@MessageDate"].Value =
dlMessage.m_MessageDate;
SqlDataReader reader = m_MessageSelectCommand.ExecuteReader();
bool b = reader.HasRows;
// B IS FALSE!!!

////////////////////////////////////////////////
//The other way - just build the query by myself
////////////////////////////////////////////////
System.Text.StringBuilder sql = new System.Text.StringBuilder(256);
sql.Append("SELECT MessageNumber FROM Messages WHERE
CommunityNumber=");
sql.Append(dlMessage.m_CommunityNumber);
sql.Append(" and ForumParent=");
sql.Append(dlMessage.m_ForumNumber);
sql.Append(" and ForumNumber=");
sql.Append(dlMessage.m_TopicNumber);
sql.Append(" and WrittenBy='");
sql.Append(dlMessage.m_MessageAuthor);
sql.Append("' and MessageDate='");
sql.Append(dlMessage.m_MessageDate);
sql.Append("'");
SqlCommand c = new SqlCommand(sql.ToString(), m_Connection);
SqlDataReader reader = c.ExecuteReader();
bool b = reader.HasRows;
// I get that b is true.
This happens durring the same program execution
How can this be???
Click to expand...
 
The resutling string of the StringBuilder is ok since it runs well on
the Sql Analayzer and it finds the record.
How do i check the sql query in the SqlCommand object? When i look in
the debugger i get the string BEFORE the parameters replacements.
 
Hi Kerry,
The single-quote character works fine in the Sql Analyzer and the query
also works fine with the SqlCommand so i guess its ok.
 
Back
Top