Parameterized Query

Parameterized Query

Postby tegansnyder » Wed 08 Dec 2010 15:16

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
tegansnyder
 
Posts: 3
Joined: Tue 30 Nov 2010 14:55

Postby StanislavK » Thu 09 Dec 2010 18:13

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.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Thanks

Postby tegansnyder » Thu 09 Dec 2010 19:15

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"
tegansnyder
 
Posts: 3
Joined: Tue 30 Nov 2010 14:55


Return to dotConnect for Oracle