C# SQL query not returning anything

Question

I have the public DataTable here and the code looks right, but its not returning anything, the OrderID is correct, the query itself is correct, its not returning anything...can anyone tell me why?

public DataTable get_OrderTransaction_Master_ByOrderID(Int64 orderID)
    {

        cn = new SqlConnection(objCommon.IpcConnectionString);
        cn.Open();

        string query = "select transactionID from dbo.OrderTransaction_Master where orderID = " + orderID;
        SqlCommand queryCommand = new SqlCommand(query, cn);
        SqlDataReader queryCommandReader = queryCommand.ExecuteReader();
        DataTable dataTable = new DataTable();
        dataTable.Load(queryCommandReader);
        cn.Close();
        return dataTable;

    }

so submitting the exact same query against the database directly (e.g. in SQL mgmt studio), gives results? — thekaveman
– thekaveman, Commented Oct 12, 2012 at 21:19
Have you tried your query using Sql Management Studio? Sometimes errors become obvious in that tool. — Steve
– Steve, Commented Oct 12, 2012 at 21:20

marc_s · Accepted Answer · 2012-10-12 21:58:22Z

2

Caveat:This is a guess based on incomplete information:

Try this: Change query string and add the line to add the parameter.

    string query = "select transactionID from dbo.OrderTransaction_Master where orderID = @OrderId";
    SqlCommand queryCommand = new SqlCommand(query, cn); 
    queryCommand.Parameters.AddWithValue("@OrderId", orderID);
    SqlDataReader queryCommandReader = queryCommand.ExecuteReader();

Explanation: Not only will this prevent SQL Injection, it will automatically assure that the OrderId is handled correctly.

You didn't specify what the data type is for the OrderId in the database. I'm guessing it may be non-numeric. (guid or varchar - I've seen databases that use nun-numeric IDs, so it's not inconceiveable.) If it's non-numeric you may be missing the quotes areound the value.

Example:

Where Id = 1

is NOT the same as

Where Id= '1'

Using a parameterized query will automagically fix this for you.

edited Oct 12, 2012 at 21:58

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Oct 12, 2012 at 21:23

David

73.7k19 gold badges134 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

C# SQL query not returning anything

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related