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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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[^]