Parameterized Query

[tegansnyder]

I'm having some issues creating a parametrized query in vb.net.

This doesn't work. I keep getting this error:

"ORA-01745: invalid host/bind variable name"

Code: Select all

Dim command As OracleCommand = OracleConnection.CreateCommand()
command.CommandText = "SELECT * FROM users WHERE uname = :user AND upass = :pass"
command.ParameterCheck = True
command.PassParametersByName = True
command.CommandType = CommandType.Text
command.Prepare()
command.Parameters.Add("user", "test")
command.Parameters.Add("pass", "test")

Using reader As OracleDataReader = command.ExecuteReader()
If reader.HasRows Then 'user exists
While reader.Read()

'do something here

End While
End If
End Using

Any help I would appreciate.

Thanks,

Tegan

[StanislavK]

The problem is that 'user' is a keyword and should be quoted to be used as a parameter name:

Code: Select all

command.CommandText = "SELECT * FROM users WHERE uname = :""user"" AND upass = :pass"

Also, you don't need to create parameters manually if ParameterCheck is enabled. The recommended way of assigning their values is

Code: Select all

command.Parameters.Item("""user""").Value = "test" 
command.Parameters.Item("pass").Value = "test"

Please tell us if this helps.

[tegansnyder]

I appreciate your help. Your suggestion makes sense.

I just renamed my parameter to username as to not conflict with the keyword.

final code:

Code: Select all

Dim command As OracleCommand = OracleConnection.CreateCommand()
command.CommandText = "SELECT * FROM USERS WHERE USERNAME = :username AND PASSWORD = :password"
command.ParameterCheck = True
command.PassParametersByName = True
command.CommandType = CommandType.Text
command.Prepare()
command.Parameters.Item("username").Value = "jdoe"
command.Parameters.Item("password").Value = "somepass"