I want to integrate Textbox in my query. After hours of searching the internet, I came across the following code. Is there any better solution?

Question

'code

Dim conn as SqlConnection = new SqlConnection("SERVER=LOGIC\SERVERDB;DATABASE=sample;User=sa;Pwd=codename")
conn.Open()


Dim userId as String = txtUserId.Text
Dim sql as String = "SELECT name, password FROM users WHERE id=@userid"

Dim cmd as SqlCommand = new SqlCommand()
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommendText = sql
cmd.Parameters.AddWithValue("userid", userId);

Dim dr as SqlDataReader = cmd.ExecuteReader()

This looks like the best way to do it. Also, parameterised queries are always recommended when you are taking input from user for the query. — shree.pat18, Aug 07 '14 at 02:25
that should work; a better way *overall* would be to include the Password hash in the SELECT query. If you get nothing back, then one of the values is wrong...saves testing the PW value. — Ňɏssa Pøngjǣrdenlarp, Aug 07 '14 at 02:26
consider to use `Using` for your `SqlConnection`, its make sure they dispposed at the end. `Using conn As New SqlConnection("SERVER=LOGIC\SERVERDB;DATABASE=sample;User=sa;Pwd=codename")` — HengChin, Aug 07 '14 at 02:39

score 0 · Answer 1 · edited May 23 '17 at 12:20

Just a minor changes if your're happy to adopt:

SqlConnection implements IDisposable. Try wrap it with a Using block, and here is why.

Not sure if you use your name and password somewhere later. If its not used in the later part, why not just send in as param and SELECT Count, then ExecuteScalar?

If userId is used only once, I would reduce a line of code without declaring the variable

Using conn As New SqlConnection("SERVER=LOGIC\SERVERDB;DATABASE=sample;User=sa;Pwd=codename")
    conn.Open()

    Dim sql As String = "SELECT name, password FROM users WHERE id=@userid"

    Dim cmd As New SqlCommand
    cmd.Connection = conn
    cmd.CommandType = CommandType.Text
    cmd.CommandText = sql

    cmd.Parameters.AddWithValue("@userid", txtUserId.Text)

    Dim dr As SqlDataReader = cmd.ExecuteReader()            
End Using

I want to integrate Textbox in my query. After hours of searching the internet, I came across the following code. Is there any better solution?

1 Answers1