1

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
Improve this question
1
  • 1
    I suppose that the variable User is a string right? Commented Oct 16, 2015 at 9:49

2 Answers 2

Reset to default
2

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.

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

5 Comments

Thanks for answer, seems like I had a mistake with =@ having a space, since haraman did give the correct answer first I will mark his as correct.
@Nightwolf:- Ok no problem, also as a reminder do check the correct way of providing parameters which I have added in my answer. Yours way is not recommended. And yes the space between = and @ doesnt matter.
I was tried SqlDbType.VarChar but did not know you don't have to give the length and thought this could be a reason for query failure. I will be using your suggestion.
That space between = and @ actually prevented the query from returning results. Don't know why.
@Nightwolf:- That sounds really strange to me.
1

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"
Improve this answer

6 Comments

I thought so just before I posted the question, and tried it out, without any results.
I have added the updated version of the question since the problem was still not resolved, even though you did point out a mistake.
It seems that for some strange reason you must have no spaces between =@ symbol
It does not matter whether you use space or not be it like " =@", "=@" or " = @" all should work
As a precaution you should not use keywords like User as variables, control names, literals
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.