locked
Parameter for sql query RRS feed

  • Question

  • Hi,
    I am want to populate the datagridview with the register(s) wher the login name is the one enterd into the inputBos - vuser
    There suhoud be here a parameter which I dont know how to includ. 
    *********************
    
    Imports System.Data.SqlClient
    Public Class Form1
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim vuser As String = InputBox("Enter user name")
            Dim connectionString As String = "Data Source=DOVHP\SQLEXPRESS;Initial Catalog=mala2;Integrated Security=True;Pooling=False"
            ' Dim sql As String = "SELECT * FROM tbusuario where login like 'sousa'"
            Dim sql As String = "SELECT * FROM tbusuario where login like" & vuser
    
            Dim connection As New SqlConnection(connectionString)
            Dim dataadapter As New SqlDataAdapter(sql, connection)
            Dim ds As New DataSet()
    
            dataadapter.Fill(ds, "usuario_table")
            connection.Close()
            DataGridView1.DataSource = ds
            DataGridView1.DataMember = "usuario_table"
        End Sub
    End Class

    Apreciate Help

    Dov Kruman

    Friday, October 18, 2013 7:55 PM

Answers

  • Thanks a lot Armin.

    Works Perfect.

    One more question:

    In the case that I want to capture a Group of registers that start with the same first letters?

    I was trying the @login + "%" with no luck. can you help me with this

    Dov

    • Marked as answer by Dovk179 Friday, October 18, 2013 11:51 PM
    Friday, October 18, 2013 10:46 PM
  • ok it should be ..login like @login + '%'"

    Yes, but you should better change the parameter value, not the SQL statement:

        "...login like @login"

          Dim sql As String = "SELECT * FROM tbusuario where login LIKE @login"
    '...
    cmd.Parameters.AddWithValue("login", vuser & "%")


    Armin

    • Marked as answer by Dovk179 Monday, October 21, 2013 2:09 PM
    Saturday, October 19, 2013 12:53 AM

All replies

  • Hi Dov,

    if you want it to match exactly, use "=" otherwise use "like". The changed lines are:

          Dim sql As String = "SELECT * FROM tbusuario where login = @login"
          Dim connection As New SqlConnection(connectionString)
          Dim cmd As New SqlCommand(sql, connection)
          cmd.Parameters.AddWithValue("login", vuser)
          Dim dataadapter As New SqlDataAdapter(cmd)
    The rest remains unchanged.


    Armin

    Friday, October 18, 2013 8:57 PM
  • Thanks a lot Armin.

    Works Perfect.

    One more question:

    In the case that I want to capture a Group of registers that start with the same first letters?

    I was trying the @login + "%" with no luck. can you help me with this

    Dov

    • Marked as answer by Dovk179 Friday, October 18, 2013 11:51 PM
    Friday, October 18, 2013 10:46 PM
  • ok it should be ..login like @login + '%'"

    Dov

    Friday, October 18, 2013 11:53 PM
  • ok it should be ..login like @login + '%'"

    Yes, but you should better change the parameter value, not the SQL statement:

        "...login like @login"

          Dim sql As String = "SELECT * FROM tbusuario where login LIKE @login"
    '...
    cmd.Parameters.AddWithValue("login", vuser & "%")


    Armin

    • Marked as answer by Dovk179 Monday, October 21, 2013 2:09 PM
    Saturday, October 19, 2013 12:53 AM