You have an error in your SQL syntax; check the manual that corresponds to your mariadb server version for the right syntax to use near 'product like 'a%'' at line

Question

1.00/5 (1 vote)

See more:

VB

Sub LoadProduct()
    If cboFilter.Text = String.Empty Then Return
    If txtSearch.Text = String.Empty Then Return
    Dim i As Integer = 0
    DataGridView2.Rows.Clear()
    cn.Open()
    cm = New MySqlCommand("SELECT * FROM tblProduct AS p INNER JOIN tblGeneric AS g ON p.ProductGeneric = GenericID INNER JOIN tblBrand AS b ON p.ProductBrand = BrandID INNER JOIN tblFromulation AS f ON p.ProductFromulation = FromulationID INNER JOIN tblClassification AS c ON p.ProductClassification = ClassificationID INNER JOIN tblType AS t ON p.ProductType = TypeID where " & cboFilter.Text & " like '" & txtSearch.Text & "%'", cn)
    dr = cm.ExecuteReader
    While dr.Read
        i += 1
        DataGridView2.Rows.Add(i, dr.Item("ProductID").ToString, dr.Item("GenericName").ToString & Space(2) & dr.Item("BrandName").ToString & Space(2) & dr.Item("FromulationName").ToString & Space(2) & dr.Item("ClassificationName").ToString & Space(2) & dr.Item("TypeName").ToString)
    End While
    dr.Close()
    cn.Close()
End Sub

What I have tried:

SQL

SELECT * FROM tblProduct AS p INNER JOIN tblGeneric AS g ON p.ProductGeneric = GenericID INNER JOIN tblBrand AS b ON p.ProductBrand = BrandID INNER JOIN tblFromulation AS f ON p.ProductFromulation = FromulationID INNER JOIN tblClassification AS c ON p.ProductClassification = ClassificationID INNER JOIN tblType AS t ON p.ProductType = TypeID

Posted 3-Jul-24 14:08pm

Raedr

Updated 3-Jul-24 17:00pm

Wendelius

v5

Add a Solution

2 solutions

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)

Wendelius · Accepted Answer · 2024-07-03T17:04:00

Solution 2

As said in the first solution parameterisation is the main issue but it looks like there are other issues as well like error handling, disposing of objects and so on.

Even though you have used MariaDB you could go through Properly executing database operations[^]. The examples are for SQL Server but the idea with MariaDB is exactly the same.

Posted 3-Jul-24 17:04pm

Wendelius

Comments

Raedr 3-Jul-24 23:57pm

Hello my friend, thank you for your reply
I did not face any actual problem in entering and displaying on DataCreditView, but the problem appears to me when I include the search engine. When I write in the file, the message appears and I need a solution.
To be honest, I say that I follow the explanation and do what it does, but it applies to the English language and I apply to the Arabic language. Do you think that the reason for the message appearing is from the Arabic language?

Wendelius 4-Jul-24 12:08pm

Most likely the lack of parameterisation is causing your problem since the text is concatenated to the string. When you use bind variables, such problems will disappear.

Pete O'Hanlon · Accepted Answer · 2024-07-03T15:23:00

There is so much going on in this question demonstrating how not to write a SQL query. The biggest, and most obvious issue is that your query is directly adding raw information directly from the interface. This leaves your code open to SQL Injection arracks[^].

Please parameterise your query. This will also take care of the problem with your query, because you aren't treating the search part of the text as a SQL string.