Not able to fetch data from SQL server

Question

1.00/5 (1 vote)

See more:

This is my beginning. I have SQL server on my local machine. I an trying to display data in grid view using visual basic. I tried the codes by watching online tutorials from YouTube.
I have written following codes but it stuck somewhere.

What I have tried:

VB

Imports System.Data.SqlClient

Public Class Form1

    Dim connection As New SqlConnection("Data Source=DESKTOP-4FDNPTL\SQLEXPRESS;Initial Catalog=POS;Integrated Security=True")

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'POSDataSet1.OUTDOOR' table. You can move, or remove it, as needed.
        Me.OUTDOORTableAdapter.Fill(Me.POSDataSet1.OUTDOOR)

        FilterData("")

    End Sub

    Public Sub FilterData(ByVal valueToSerach As String)

        'SELECT * FROM DBO.OUTDOOR WHERE ITEM = 'HP209';'

        Dim searchquery As String = "SELECT * FROM DBO.OUTDOOR WHERE ITEM = 'HP209'" & valueToSerach & "%"

        Dim command As New SqlCommand(searchquery, connection)
        Dim adapter As New SqlDataAdapter(command)
        Dim table As New DataTable()

        adapter.Fill(table)

        DataGridView1.DataSource = table

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    End Sub
End Class

Posted 18-Sep-19 23:59pm

Member 14597659

Updated 19-Sep-19 0:51am

MadMyche

v2

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Answer 1 · 2019-09-19T00:51:00

I would suggest you find some other tutorials, as the code you have presented is ripe for an SQL Injection attack; which was identified over 20 years ago- before YouTube even existed.
Search this site and elsewhere to find out all you need to know and then some SQL Injection; it is seen here weekly and if you read through the other answers there is a plethora of links to information about this.

This line of code has a few problems:

VB

Dim searchquery As String = "SELECT * FROM DBO.OUTDOOR WHERE ITEM = 'HP209'" & valueToSerach & "%"

1. It is vulnerable as you are taking user input verbatim and concatenating it into an SQL statement. Crafted input from the end user could trick your software into revealing all of it's secrets, adding content to all of your tables, or deleting your entire database. Simumltaneously.

2. The WHERE clause in the statement utilizes the equals (=) operator, but it appears you want to do a search which would necessitate the LIKE operator.

3. The syntax of the SQL statement should be causing an exception to be raised- Have you reviewed the logs or run this in Debug mode?

Now for the better way to do this.

VB

Dim searchquery As String = "SELECT * FROM dbo.Outdoor WHERE Item LIKE 'HP209' + @ValueToSearch + '%'"
Dim command As New SqlCommand(searchquery, connection)
cmd.Parameters.AddWithValue("@ValueToSearch", valueToSerach)

1. Replaced the direct user input with a Paramater, and added a new line after command is create to assign the parameter value. If any nefarious input was typed in, when the parameter is added to the statement it will be encoded to not trick your server.

2. The WHERE clause has been changed to use the LIKE operator, so we can retrieve rows that match the HP209xxxx pattern (HP209, HP209abc, HP209xxaafff) will all be returned

Wouldn't you know it, but today's CodeProject News Items[^] has an article and item #6 on this list is SQL Injection- I would suggest reading through the article and related items such as #3 (input validation).
Revealed: The 25 most dangerous software bug types – mem corruption, so hot right now • The Register[^]

Not able to fetch data from SQL server

1 solution

Solution 1

Add your solution here

Preview 0

Not able to fetch data from SQL server

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us