SELECT trouble from VB.NET

Question

0.00/5 (No votes)

See more:

Good morning. I have a SELECT command that works perfectly if typed into console and does not work if issued trough a program. Before asking i tried several ways but nothing. I'm a newbie, so be patient.
Visual Studio community 2002
Vb .net
Mariadb 10.11.8-MariaDB-0
Server ubuntu0.24.04.1 Ubuntu 24.04
---
I have a Db with ONE table Seqf1 and a column Sequenza varchar(200) where i store some sequences. Sometimes i need to connect to it and take a look if the cIndicatorx string is present or not in the db. just that.

VB

cIndicatorx = "1234567"
cmd.CommandText = "SELECT Sequenza FROM Seqf1 Where Sequenza LIKE 'cIndicatorx%';"
cmd.CommandTimeout = 100000
Dim cStartQuery As String = Now
risultato = cmd.ExecuteNonQuery

cStrToFind = "1234567"
Using con As New MySqlConnection(myConnectionString)
    con.Open()
    Using com As New MySqlCommand("SELECT * FROM Seqf1 WHERE Sequenza LIKE @SeqToFind", con)
        com.CommandTimeout = 10000
        com.Parameters.AddWithValue("SeqToFind", cStrTofind + "%")
        risultato = com.ExecuteNonQuery()
    End Using
End Using

if i type commands manually, they are working and if in the db i have "12345678" the record is found. Trough program never find anything.

I believe that the command , when sent, is altered. Please help me.
Thanks in advance.
Tieko.

What I have tried:

I tried to rewrite the select several times but nothing works.

Posted 8-Jul-24 3:25am

Tieko

Updated 8-Jul-24 3:33am

v2

Add a Solution

Comments

Richard Deeming 8-Jul-24 9:47am

First of all: are you sure it's Visual Studio community 2002? As in the very first version of Visual Studio .NET? IIRC, the "community edition" wasn't available back then.

Secondly, your two code blocks are searching for different things. One is searching for values starting with "cIndicatorx", and the other is searching for values starting with "1234567".

And thirdly, why are you using ExecuteNonQuery with a SELECT query? That method is normally used for INSERT, UPDATE, or DELETE commands which do not return any records.

Tieko 8-Jul-24 10:15am

Hi Richard, it was VS 2022, a mistake. Thanks for signaling.
The first code block (one of the many i tried) is searching with the VARIABLE 'cIndicatorx' (this was my idea, if it is wrong tell me) and the second the same thing with another name.
i have a variable cIndicatorx (or something else) that contains a string, and i must find IF the string is present in the db.

Regarding the ExecuteNonQuery i trie all execute possibile but no one works. There is a special Execute for select so i can trace if found or not?

Note that my cIndicatorx string is shorter than the string in the db, so i used LIKE and the % after the last char.
Thanks in advance.

2 solutions

Solution 2

To add to what Dave has said the proper way to do it would be to separate the command and the variable via a parameterised query, but leave the "fixed" part of the query with the command string.
But ... that is a query: ExecuteNonQuery returns a single integer value, whereas your SELECT command returns every field in the matching rows.
Additionally, the parameter name needs to include the SQL variable indicator.
A better way to do that would be along these lines:

VB

cStrToFind = "1234567"
Dim dt As DataTable = New DataTable()
Using con As SqlConnection = New SqlConnection(strConnect)
    con.Open()
    Using da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Seqf1 WHERE Sequenza LIKE @SeqToFind + '%'", con)
        da.SelectCommand.Parameters.AddWithValue("@SeqToFind", cStrTofind)
        da.Fill(dt)
        myDataGridView.DataSource = dt
    End Using
End Using

Posted 8-Jul-24 5:11am

OriginalGriff

Comments

Tieko 8-Jul-24 12:05pm

Thank you. I don't need to catch field of matching rows. I just need to know if in the DB there is a string that contains my search string. cStrToFind was here as example. In the reality i have a code that build the search string, go to the db to see if there is or not. So i must put it into a variable , i cannot type it becaus the string change everytime. is there a way to know (true/false/number of matching rows)?

OriginalGriff 8-Jul-24 12:32pm

Use the SQL COUNT function: SELECT COUNT(Id) FROM MyTable WHERE ...
You can then call ExecuteScalar to return a single value which is the number of matching rows.

Tieko 8-Jul-24 12:54pm

Thank you again. I will try. But it seems that the problem is how i send commands. so , knowing the right command is not enough. look at this version : it seems to be ok, but does not find the string (that is present in the form "12345678" into Db). Huge timout is present due to a db of 120 millions of records.

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)

Dave Kreskowiak · Accepted Answer · 2024-07-08T04:13:00

Solution 1

On top of the issues Richard raised in his comment, your first example SQL command doesn't do what you think it does:

SQL

SELECT Sequenza FROM Seqf1 Where Sequenza LIKE 'cIndicatorx%';

That command searches for any records where the Sequenza field start with literally the string cIndicatorx. It does NOT search for what is in the cIndicatorx variable. Variable replacements in strings in VB.NET does not work the way you seem to think it does. Also, using direct string replacement in a query like you're trying to do is a very insecure way of doing it and can lead to the destruction of your database with carefully crafted input.

Your second example actually does it the correct way, by using a parameter to pass the value of the variable to the SQL command. Though, your example still isn't production quality code, it's a step in the right direction on the path to learning how to do it.

But, in both examples, you called ExecuteNonQuery, which does not return any records, like Richard said, so your code completely ignored the records being returned from the SELECT queries, if any.

Posted 8-Jul-24 4:13am

Dave Kreskowiak

Comments

Tieko 8-Jul-24 13:22pm

After lot of tries i think that Dave has found the point. I don't do correct variables substitution. The code is correct, db is opened, command (scalar) are executed , but values returned are wrong also if present in db. So i think that due to not correct variable sobstitution, the query is not correct. Can you help me Dave about? where i can look?

Dave Kreskowiak 8-Jul-24 14:00pm

We know nothing on the structure of your table, like what type the Sequenza column is, so it's very difficult to suggest anything.

Tieko 8-Jul-24 14:05pm

I wrote it. The database is composed of one column named "Sequenza" varchar(200).
During program execution i obtain a string (more smaller than the string into db) that i must compare using LIKE to discover if there is a db row that starts with the same text and finish (is not important. so i was using like cIndicatorex% .

Example : cIndicatorex="12345"
scan the db and if you find "1234567890" returns true or found 1 or something so i know it was found. just that.
Thanks in advance Dave.

Dave Kreskowiak 8-Jul-24 14:17pm

Then this should work:

cIndicatorx = "1234567"
Using conn As New MySqlConnection(myConnectionString)
    Using comm As New MySqlCommand("SELECT COUNT(Sequenza) FROM SeqFam1_20_60 WHERE Sequenza LIKE @SeqToFind", conn)
        comm.Parameters.AddWithValue("SeqToFind", cIndicatorx & "%")
        conn.Open()
        Dim count As Integer = comm.ExecuteScalar()
        conn.Close()
        Return (count > 0)
    End Using
End Using

The advantage of the above code is this code is more debuggable by not combining multiple statements into a single statement, giving you the opportunity to see variable contents at every line of code when run under the debugger. For example, now you can see exactly how many records were found by the database, and don't need conversions, making the code simpler.

Dave Kreskowiak 8-Jul-24 14:21pm

You also don't need the command timeout as the default is 30 seconds. If the database can't come up with a count of matching records out of 120,000,000 inside 30 seconds, you have other problems with the database, like no indexing.

Tieko 8-Jul-24 14:40pm

& instead of "+". This was the error. Now i have 5 different codes working. Thanks Dave.
(now i go to build index) - Thank you very much.