CODEWITHSUNDEEP
sqlsql-servervb.net
Nightwolf
Nightwolf

Reputation: 951

Why does this parameterized sql query not return results?

I am taking over a VB project and with my limited VB skills I cannot get the following to parameterized query to return results:

Dim strSQLUser As String = "Select Name, CompanyID from Users where UserName = @UserName"
dbCommand = New SqlCommand(strSQLUser, dbConn)
dbCommand.Parameters.AddWithValue("@UserName", User)
dr = dbCommand.ExecuteReader

However this is the original code that does work:

Dim strSQLUser As String = "Select Name, CompanyID from Users where UserName ='" & User & "'"
dbCommand = New SqlCommand(strSQLUser, dbConn)
dr = dbCommand.ExecuteReader

As you can see the original code was vulnerable to sql injection and needs to be fixed.

Extra - Here is the line that does the reading:

While dr.Read
  DbUser = dr.GetValue(0).ToString
  DbCompany = dr.GetValue(1).ToString
End While

Upvotes: 1

Views: 82

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

Try this:

Dim strSQLUser As String = "Select Name, CompanyID from Users where UserName =@UserName"
dbCommand = New SqlCommand(strSQLUser, dbConn)
dbCommand.Parameters.AddWithValue("@UserName", User.Text)
dr = dbCommand.ExecuteReader

Also the better approach is provide the value as:

dbCommand.Parameters.Add("@UserName", SqlDbType.VarChar).Value = User.Text

Assuming User to be the varchar ie., text type.

Upvotes: 2

haraman
haraman

Reputation: 2752

When using parameters you do not specify the quotes around parameters '. All the parameters are automatically converted to their respective column types such as date, nvarchar etc. So no more quotes.

Dim strSQLUser As String = "Select Name, CompanyID from Users where UserName =@UserName"

Upvotes: 1

Related Questions