Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Dim EmailID As String = ""
Dim CounterID As Integer = 0
mysqlconn.ConnectionString = "server=localhost;user=root;database=Somedatabase;port=3306;password=password"
Dim createSql As String = ""
Try
    mysqlconn.Open()
    createSql = "SELECT COUNT(`Links`) FROM Fresh_links WHERE COUNT='" & CounterID & "'"
    Dim cmd As New MySqlCommand(createSql, mysqlconn)
    Dim rs As Integer = cmd.ExecuteScalar().ToString()
    mysqlconn.Close()
Catch ex As Exception
    MsgBox(ex.ToString())
Finally
    mysqlconn.Dispose()
End Try


What I have tried:

how to i get data from Fresh_links table where counterID = 1
get me the data in string
how to do so ?
Posted
Updated 5-Jan-21 11:19am

Um. Did you look at your code?
VB
Dim rs As Integer = cmd.ExecuteScalar().ToString()

So you retrieve a value from the DB (the number of matching items), convert that to a string - which is what you say you want but I doubt it - and then store that string in a integer.

Why?

If you want an integer, just cast the ExecuteScalar return value as an Integer, don't convert it to a string first.
If you want it as a string, don't store it in an Integer variable.

And if that isn't what you want at all, then you need to start by working out what you do want and modifying your SELECT query to return that information. But we can't tell you how to do that as we don't have access to your DB or any idea what you do what to fetch!

but while we're on teh subject of your SELECT query ... Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
 
Share this answer
 
Comments
Member 10974007 5-Jan-21 6:38am    
i am a new coder with my sql am unable to get data at first row
can you code few line for me
it table contains colums id(int) and emaild(vartext)
OriginalGriff 5-Jan-21 6:43am    
So what have you tried?
(Other than copy'n'paste random code and hoping it will work, obviously.)

Have you looked at a DataReader or DataAdapter yet?
Richard MacCutchan 5-Jan-21 6:39am    
Didn't you know, you must call ToString on all return values, in .NET?
OriginalGriff 5-Jan-21 6:42am    
Especially strings:

dim myString As String = "Hello World!".ToString


Unless you copy'n'paste without understanding, when it's:

dim myString As String = "Hello World!".ToString.ToString


:D
Member 10974007 5-Jan-21 7:12am    
Dim myString As String = "Hello World!"
:D is perfect code
am good with vb.net
and new to mysql
First of all, please start with reading MySql documentation: MySQL :: MySQL Connector/NET Developer Guide :: 4.1 Creating a Connector/NET Connection String[^]

You should use parameterized query instead of concatenated string:
VB.NET
Dim rs As Integer = 0
Dim ConnString = "server=localhost;user=root;database=Somedatabase;port=3306;password=password"
Dim createSql As String = "SELECT COUNT(`Links`) FROM Fresh_links WHERE COUNT=?cnt"
Try
    Using MySqlConnection connection = New MySqlConnection(ConnString)
		connection.Open()
    	Using  MySqlCommand cmd As New MySqlCommand(createSql, connection)
			cmd.Parameters.Add(New MySqlParameter("cnt", 5))  
    		rs = cmd.ExecuteScalar()
			MsgBox(rs.ToString())
		End Using
    End Using
Catch ex As Exception
    MsgBox(ex.ToString())
End Try


Sample code is here: VB.NET & MySql.Data.MySqlClient - Parameter Query (MySqlParameter) | ShotDev.Com[^]
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900