view raw
Paul Williams Paul Williams - 10 months ago 58 Question

Retrieving the Query used for a OleDBCommand

I'm currently using the following VB code to make a query against an Access Database, I would like to know is it possible to obtain what the

statement that is being run and send that output to the console.

Dim QuestionConnectionQuery = New OleDb.OleDbCommand("SELECT Questions.QuestionID FROM Questions WHERE Questions.QuestionDifficulty=[X] AND ( Questions.LastDateRevealed Is Null OR Questions.LastDateRevealed < DateAdd('d',-2,Date() ) AND Questions.LastUsedKey NOT LIKE ""[Y]"" );", QuestionConnection)
QuestionConnectionQuery.Parameters.AddWithValue("X", questionDifficulty.ToString)
QuestionConnectionQuery.Parameters.AddWithValue("Y", strDatabaseKey)

Right now when I try to use:
Console.WriteLine("Query: " & QuestionConnectionQuery.ToString)

I only get this:

Loop Question #1
Query: System.Data.OleDb.OleDbCommand


The short version comes down to this:


The QuestionConnectionQuery object is much more than just the text of your command. It's also the parameters, execution type, a timeout, and a number of other things. If you want the command text, ask for it:


But that's only the first issue here.

Right now, your parameters are not defined correctly, so this query will never succeed. OleDb uses ? as the parameter placeholder. Then the order in which you add the parameters to the collection has to match the order in which the placeholder shows in the query. The code in your question just has X and Y directly for parameter placeholders. You want to do this:

Dim QuestionConnectionQuery AS New OleDb.OleDbCommand("SELECT Questions.QuestionID FROM Questions WHERE Questions.QuestionDifficulty= ? AND ( Questions.LastDateRevealed Is Null OR Questions.LastDateRevealed < DateAdd('d',-2, Date() ) AND Questions.LastUsedKey NOT LIKE ? );", QuestionConnection)
QuestionConnectionQuery.Parameters.Add("?", OleDbType.Integer).Value  = questionDifficulty
QuestionConnectionQuery.Parameters.Add("?", OleDbType.VarChar, 20).Value =  strDatabaseKey

I had to guess at the type and lengths of your parameters. Adjust that to match the actual types and lengths of the columns in your database.

Once you have made these fixes, this next thing to understand is that the completed query never exists. The whole point of parameterized queries is parameter data is never substituted directly into the sql command text, not even by the database engine. This keeps user data separated from the command and prevents any possibility of sql injection attacks.

While I'm here, you may also want to examine the WHERE conditions in your query. The WHERE clause currently looks like this:


Whenever you see an AND next to an OR like that, within the same parenthetical section, I have to stop and ask if that's what is really intended, or whether you should instead close the parentheses before the final AND condition: