VB Win Form Issue Using SqlCommand

Question

I am currently creating a win form based tool in Visual Studio 2019 that reads data from a SQL database. I am having difficulty when pulling a field from the form I am working in into the sqlCommand query.

This is part of my VB script. Note that this works fine without the Surname like filter, it also works if i write it with a hard-coded surname, such as 'Adams'. I can also get the same logic to work using variables directly in SQL. The message pop-up displays 'Adams' as expected but nothing is returned in the data-grid.

Screen-shots below of the result of running the form as desired and when hard-coded to 'Adams'.

Thanks in advance for any help :)

Public Class Form4
Public Sub BtnFetchAdastraUser_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFetchAdastraUser.Click

    Dim connetionString As String = "Data Source=xxxxxx;Initial Catalog=xxxxxx;User ID=xxxxx;Password=xxxxx"
    Dim dt As New DataTable()

    Using connection As New SqlConnection(connetionString)
        Dim command As New SqlCommand(
                                        "select 
                                            [u].[UserRef],
                                            [u].[UserName],
                                            [u].[FullName]
                                        from dbo.[Users][u]
                                        where
                                            [Obsolete] = 0
                                                and [Surname] like '%" + Surname.Text + "%'", connection
                                      )
        command.Connection.Open()
        Dim sqlAdaptr As New SqlDataAdapter(command)
        Dim ds As New DataTable
        sqlAdaptr.Fill(ds)
        DataGridView1.DataSource = ds

        MsgBox(Surname.Text)

        command.Connection.Close()
    End Using
End Sub

End Class

Van Macintosh · Accepted Answer · 2020-03-17 10:44:22Z

0

I figured it out. I ran command.CommandText through my MsgBox and it highlighted the below issue with what I was passing through in the query!

I'd added text to the front of the field I was passing through.

answered Mar 17, 2020 at 10:44

Van Macintosh

35 bronze badges

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

Comments

Julie Xu-MSFT · Accepted Answer · 2020-03-18 05:59:31Z

0

It is recommended that you use parameters to write SQL query statements. It can be changed as follows:

    Using connection As New SqlConnection(connetionString)
        connection.Open()
        Dim command As New SqlCommand(
                                        "select 
                                        [u].[UserRef],
                                        [u].[UserName],
                                        [u].[FullName]
                                    from dbo.[Users][u]
                                    where
                                        [Obsolete] = 0
                                            and [Surname] like @Surname", connection
                                      )
        command.Parameters.Clear()
        command.Parameters.AddWithValue("@Surname", "%" & Surname.Text & "%")

        Dim sqlAdaptr As New SqlDataAdapter(command)
        Dim ds As New DataTable
        sqlAdaptr.Fill(ds)
        DataGridView1.DataSource = ds

        MsgBox(Surname.Text)
        MsgBox(command.CommandText)

        command.Connection.Close()
    End Using

answered Mar 18, 2020 at 5:59

Julie Xu-MSFT

3401 silver badge5 bronze badges

2 Comments

Kate Over a year ago

Using parameters: not just recommended but mandatory practice. All it takes to crash this code is a surname like O'connor. Besides, the input from a textbox should be sanitized, use the trim function to eliminate possible whitespace. When doing copy-paste, extra spaces, tabs, carriage returns sneak in frequently.

Julie Xu-MSFT Over a year ago

Yes, here, Surname.Text can be anything and it could break the query if it has ' in it.

Collectives™ on Stack Overflow

VB Win Form Issue Using SqlCommand

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related