How to Use a parameter within SQL in Vb 2010 (web developer)

Question

I am trying to work out SQL code in VB but I am having problems I have a simple database with the table admin with the columns UserName and Password.

I want to be able to read data from a text box and then input it into a SQL string… the SQL string works (I've tested it) and I can get it to output with a simple SELECT statement but I can't seem to get the SQL to read my Parameter.

Help?

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Call Password_Check(txtTestInput.Text)
End Sub

Public Sub Password_Check(ByVal Answer As String)

    Dim con As New SqlConnection
    Dim cmd As New SqlCommand
    Dim parameter As New SqlParameter("@Username", Answer)
    Try

        con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("Database1ConnectionString1").ConnectionString
        con.Open()
        cmd.Connection = con
        cmd.CommandText = " SELECT Password FROM Admin WHERE (UserName = @Username)"
        cmd.Parameters.Add(parameter)
        Dim lrd As SqlDataReader = cmd.ExecuteReader()

        While lrd.Read()
            Dim sothing As String

            sothing = lrd("Password").ToString
            If lrd("Password").ToString = txtPassword.Text Then
                lblTestData.Text = "passwordSuccess"
            ElseIf lrd("Password").ToString <> txtPassword.Text Then
                lblTestData.Text = "passwordFail...:("
            End If
        End While

    Catch ex As Exception
        lblTestData.Text = "Error while retrieving records on table..." & ex.Message
    Finally
        con.Close()
    End Try

End Sub

Does it enter the While lrd.Read() Loop, if so What is the value of lrd("Password").ToString — PatFromCanada, Nov 17 '12 at 17:34
You can't seem to get the SQL to read your Parameter, so how does it seem actually then? An exception thrown? — deerchao, Nov 17 '12 at 18:57

score 0 · Answer 1 · answered Nov 17 '12 at 19:18

0

Regarding to your Database system it is possible that it does not support parameter names. Have you tried ? Wat DB System you used?

cmd.CommandText = " SELECT Password FROM Admin WHERE (UserName = ?)"

answered Nov 17 '12 at 19:18

Stephan Ahlf

3,310
5
39
68

The OP has posted code with `Dim con As New SqlConnection` which strongly suggests they're using MS SQL Server, which does support named parameters. – Andrew Morton Nov 18 '12 at 17:25

David Lloyd Brookes · Accepted Answer · 2012-11-18T17:20:18.890

in your code above: --> Dim parameter As New SqlParameter("@Username", Answer)

Can I suggest two options:

Dim parameter As New SqlParameter("@Username", sqldbtype.nvarchar)

parameter.value = Answer

or

cmd.CommandText = string.format("SELECT Password FROM Admin WHERE (UserName = {0})", Answer)

Full Code:

Public Sub Password_Check(ByVal Answer As String)

    Dim con As New SqlConnection
    Dim cmd As New SqlCommand
    Dim parameter As New SqlParameter("@Username", SqlDbType.NVarChar)
    parameter.Value = Answer
    Try

        con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("Database1ConnectionString1").ConnectionString
        con.Open()
        cmd.Connection = con
        cmd.CommandText = "SELECT Password FROM Admin WHERE (UserName = @Username)"
        cmd.Parameters.Add(parameter)
        Dim lrd As SqlDataReader = cmd.ExecuteReader()

        While lrd.Read()
            Dim sothing As String

            sothing = lrd("Password").ToString
            If lrd("Password").ToString = txtPassword.Text Then
                lblTestData.Text = "passwordSuccess"
            ElseIf lrd("Password").ToString <> txtPassword.Text Then
                lblTestData.Text = "passwordFail...:("
            End If
        End While

    Catch ex As Exception
        lblTestData.Text = "Error while retrieving records on table..." & ex.Message
    Finally
        con.Close()
    End Try

End Sub

Using String.Format is a SQL injection vulnerability waiting to happen. SQL Parameters are the right approach. — Laurence, Nov 18 '12 at 14:29
Good point Laurence. in the string.format case it's advised you screen the string being passed to the cmd.commandtext. I've updated the code to use parameters. — David Lloyd Brookes, Nov 18 '12 at 17:22

How to Use a parameter within SQL in Vb 2010 (web developer)

2 Answers2