0

I have some trouble to debugging my query in vb.net. I just wanna get full query with value inside it. I use parameters to add value in my query. This is my code:

'Select query
Dim stm As String = "SELECT *, FORMAT(NOW(),'DD-MM-YYYY HH:NN:SS') as waktu FROM [user] WHERE [username]=? AND [password]=? AND active=TRUE"
Dim cmd As OleDbCommand = New OleDbCommand(stm, db)

'Parameters
Using md5Hash As MD5 = MD5.Create()
    Dim pwd As String = GetMd5Hash(md5Hash, Me.tx_password.Text)
    cmd.Parameters.Add("p1", OleDbType.VarChar, 25).Value = Me.tx_username.Text
    cmd.Parameters.Add("p2", OleDbType.VarChar, 32).Value = pwd
End Using

'Execute Query
MsgBox(stm)
Dim reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.SingleRow)

With this code, I just get result like this:

SELECT *, FORMAT(NOW(),'DD-MM-YYYY HH:NN:SS') as waktu FROM [user]
WHERE [username]=? AND [password]=? AND active=TRUE

How to get result like this:

SELECT *, FORMAT(NOW(),'DD-MM-YYYY HH:NN:SS') as waktu FROM [user]
WHERE [username]='adminUser' AND [password]='adminPassword' AND active=TRUE
Improve this question
2
  • is it really intentional to have a comman after the asterisk??? select *, ??? Commented May 11, 2015 at 5:15
  • yes, because I want to get current time too in result Commented May 11, 2015 at 5:27

2 Answers 2

Reset to default
1

Parameters are not concatenated into the command, they are sent separately to the database. Otherwise there will be no difference between using a parameterized query and using a concatenated one. (see the answer to a similar question here.)
This means that in order to debug your queries you will have to work a little harder then if your sql was concatenated by the vb.net code.

If your database supports stored procedure I recommend you start using them instead of parameterized queries. You will probably gain performance, and it will be easier to debug.
If not, you can copy the query as is to the sql editor, and use one of the debugger options to get the values of the parameters and copy them one by one to the sql editor.

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

2 Comments

Okay, I get it. The reason that I use parameter is to prevent sql injection. Thanks for the answer.
that's a good reason. in fact, probably the best if not the only. stored procedures will give you that benefit along with other benefits such as encapsulation of your table structure, pre-compiled query plans, and so on.
1

Place this code below you have added the parameters and you'll have in debugSQL the SQL statement which will be executed 

Dim debugSQL As String = cmd.CommandText

For Each param As SqlParameter In cmd.Parameters
    debugSQL = debugSQL.Replace(debugSQL.ParameterName, debugSQL.Value.ToString())
Next
Improve this answer

1 Comment

This will answer the question, but it's not exactly the sql statement that will be executed. if it was, then a parameterized query would be the same as a concatenated one. a good way of testing this would be to have an varchar parameter with an apostrophe in it. try running the parameterized query and then try running the debugSQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.