How can I see the SQL sent to the database after the parameters have replaced their placeholders?

Question

The first MessageBox.Show() below simply shows me the exact same thing as const string SQL_GET_VENDOR_ITEMS, which seems fine to me, but I'm getting, "There was an error parsing the query. [Token line number, Token line offset,, Token in error,,]"

Is there a way to spy on the contents of the SQL after parameters have been added; it should then be something like: "SELECT ItemID, PackSize FROM VendorItems WHERE VendorID = 'TEST' AND VendorItemID = '852963'

Here's the pertinent code:

    const string SQL_GET_VENDOR_ITEMS = "SELECT ItemID, PackSize " + 
        "FROM VendorItems " +
         "WHERE VendorID = @VendorID AND VendorItemID = @VendorItemID";

    string retVal = string.Empty;
    checkConnection();
    SqlCeCommand vendorCMD = objCon.CreateCommand();
    try 
    {
        vendorCMD.CommandText = SQL_GET_VENDOR_ITEMS;
        vendorCMD.Parameters.Add("@VendorID", SqlDbType.NVarChar, 10).Value = VendorID; 
        vendorCMD.Parameters.Add("@VendorItemID", SqlDbType.NVarChar, 19).Value = VendorItemID;

        MessageBox.Show(string.Format("Made it up to vendorCMD.ExecuteReader() with sql {0}", vendorCMD.CommandText));

. . .

        vendorReader.Close();
    } 
    catch (SqlCeException sqlceex)
    {
        MessageBox.Show(string.Format("SqlCeException in GetValsForVendorAndItem == {0}", sqlceex.Message));//TODO: Remove
    }
    finally 
    {
        vendorCMD.Dispose();
    }
    return retVal;

. . .

Does sql-server-ce not support parameters or something? the entire point of parameters is that they **are not** replaced. The only reason to ever "replace[d] their placeholders" would be if the database didn't support parameters. Is there any chance that `VendorID` or `VendorItemID` is `null` ? — Marc Gravell, Apr 03 '13 at 21:17
What I mean is, the query string with the "@VendorID" needs to be replaced with some value, such as "TEST", as there is no "@VendorID" value in any of the rows. I just want to verify that the value I expect is being replaced there in my attempt to figure out what could possibly be hard to parse about the query - it seems pretty straightforward to me, and I don't know why the query parser is choking on it. — B. Clay Shannon-B. Crow Raven, Apr 03 '13 at 21:22
no, it doesn't need to be replaced (at least on most database engines); the parameters are sent separately to the command-text. Now it *could be* that CE does this differently, hence my question. Did you see the edit about `null` btw? — Marc Gravell, Apr 03 '13 at 21:24

score 4 · Accepted Answer · answered Apr 03 '13 at 21:25

4

but I can almost guarantee that won't work in my VS2003/.NET 1.0 world

ahhh... version - see MSDN:

The .NET Compact Framework data provider for SQL Server CE does not support named parameters for passing parameters to an SQL statement called by a SqlCeCommand when CommandType is set to Text. You must use the question mark (?) placeholder. For example: SELECT * FROM Customers WHERE CustomerID = ?

answered Apr 03 '13 at 21:25

Marc Gravell

1,026,079
266
2,566
2,900

1

Wholly crud! Thanks! This project is a sure cure for the sepia-drenched hues of moist-eyed nostalgia. – B. Clay Shannon-B. Crow Raven Apr 03 '13 at 21:31
Wholly crud! And I've been showing him how to use Parameters! Doh! – Apr 04 '13 at 02:17
1

Ha, ha! Welcome back to the 90's. – ctacke Apr 04 '13 at 13:23

score 1 · Answer 2 · edited May 23 '17 at 10:25

1

Since you are on CE, your options are limited, but there are some suggestions for how to peek into the database: Profiler for Sql CE

If you were on normal SQL Server, you might consider using SQL Profiler. You'd be able to see what is getting executed against the database.

edited May 23 '17 at 10:25

Community

1
1

answered Apr 03 '13 at 21:22

wilsjd

2,178
2
23
37

If I was living in the 21st century I would, but I can almost guarantee that won't work in my VS2003/.NET 1.0 world. – B. Clay Shannon-B. Crow Raven Apr 03 '13 at 21:23
@ClayShannon, this is on Database side not VS – rs. Apr 03 '13 at 21:24
Right, but I can't even access the database - I can't look in it, there's no way to do that (with outside tools, I mean, of course). Believe me, I've tried. – B. Clay Shannon-B. Crow Raven Apr 03 '13 at 21:29
He's using SQL CE and testing on a hand held. This isn't an option. – NotMe Apr 03 '13 at 21:30
Right, but that doesn't mean that he himself is on a hand held. "A mobile device is not necessary to develop a CE program" - http://en.wikipedia.org/wiki/Windows_CE. I assumed he was doing dev on a local machine. – wilsjd Apr 03 '13 at 22:34
He's had several questions recently surrounding development on a device. His comments to responses on those questions pretty much identified his limitations; guess I just happened to see most of them. – NotMe Apr 03 '13 at 23:36
Gotcha, that makes sense given that context. – wilsjd Apr 03 '13 at 23:42

How can I see the SQL sent to the database after the parameters have replaced their placeholders?

2 Answers2

Linked