How do I add apostrof to the sql select

[Tony Johansson]

Hello!

If I write 'false' or 'true' instead of @complete in the sql below it works
fine.
I can fix this with string.format but first I try to fix this without using
it.
As you can see I use cmd.Parameters.AddWithValue("@complete", complete);
to get false ot true from the complete variable.
I have tried with '@complete' but that didn't work.

Can somebody give me the syntax for how to write this ?


public DataSet GetTicketDetail(ListItem owner, bool complete)
{
SqlCommand cmd;
if (owner.Text == "*") // All tickets that has an owner
{
string query = "SELECT Tickets.TicketID, Tickets.HeadLine,
Tickets.Description, " +
"Tickets.Priority, Tickets.CreatedTicket,
Users.Owner, Tickets.Complete, TicketType.Name " +
"FROM Tickets " +
"JOIN Users ON " +
"Users.UserID = Tickets.UserID " +
"JOIN TicketType ON " +
"TicketType.TicketTypeID = Tickets.TicketTypeID
" +
"where Tickets.UserID != @UserID and complete =
@complete'" +
"SELECT
TicketDetail.TicketDetailID,TicketDetail.TicketID, TicketDetail.Action, " +
"Users.Owner, TicketDetail.CreatedDate,
TicketDetail.UserID,TicketDetail.Status " +
"FROM [TicketDetail] " +
"JOIN Users ON " +
"Users.UserID = TicketDetail.UserID";

cmd = new SqlCommand(query);
cmd.Parameters.AddWithValue("@UserID",
GetIDForNonAssignedTickets());
cmd.Parameters.AddWithValue("@complete", complete);

}
}

//Tony

 

[alex]

Hello!

"TicketType.TicketTypeID = Tickets.TicketTypeID " +
"where Tickets.UserID != @UserID and complete = @complete'" +
"SELECT TicketDetail.TicketDetailID,TicketDetail.TicketID, TicketDetail.Action, " +
"Users.Owner, TicketDetail.CreatedDate, TicketDetail.UserID,TicketDetail.Status " +

//Tony

Replace the apostrophe after @complete, with a semicolon; do you really need the second select statement in the same query?

 

[Arne Vajhøj]

If I write 'false' or 'true' instead of @complete in the sql below it
works fine.
I can fix this with string.format but first I try to fix this without
using it.

Because String.Format to create SQL statements is bad.

Parameters is the right way.

As you can see I use cmd.Parameters.AddWithValue("@complete", complete);
to get false ot true from the complete variable.
I have tried with '@complete' but that didn't work.

Can somebody give me the syntax for how to write this ?


public DataSet GetTicketDetail(ListItem owner, bool complete)
{
SqlCommand cmd;
if (owner.Text == "*") // All tickets that has an owner
{
string query = "SELECT Tickets.TicketID,
Tickets.HeadLine, Tickets.Description, " +
"Tickets.Priority, Tickets.CreatedTicket,
Users.Owner, Tickets.Complete, TicketType.Name " +
"FROM Tickets " +
"JOIN Users ON " +
"Users.UserID = Tickets.UserID " +
"JOIN TicketType ON " +
"TicketType.TicketTypeID =
Tickets.TicketTypeID " +
"where Tickets.UserID != @UserID and
complete = @complete'" +

You should not put '' around booleans, so

and complete = @complete

with no single quotes should work.

"SELECT
TicketDetail.TicketDetailID,TicketDetail.TicketID, TicketDetail.Action, " +
"Users.Owner, TicketDetail.CreatedDate,
TicketDetail.UserID,TicketDetail.Status " +
"FROM [TicketDetail] " +
"JOIN Users ON " +
"Users.UserID = TicketDetail.UserID";

cmd = new SqlCommand(query);
cmd.Parameters.AddWithValue("@UserID",
GetIDForNonAssignedTickets());
cmd.Parameters.AddWithValue("@complete", complete);

}
}

I will strongly recommend adding parameters with type information.

That should help catch some errors.

Arne