CODEWITHSUNDEEP
mysqlsqlvb.netif-statementodbc
IMPhill
IMPhill

Reputation: 35

Vb.Net / MySql Query is Failing - not producing the expected result

I am trying to make my code much more efficient, and to remove the reliance on hard coded information.

SO - I want the script to check a name in a MySQL and if it exists, to update the record with that name - for instance "Phill". The code receives the name from an external source, and it is supposed to try and find that name in the MySQL database then update that record. However, it seems to fail and I cannot figure out why - it simply logs "unknown tag" like the system didn't find the MySQL record.

I've isolated the issue to the code below.

Sub myShowStringMethod(ByVal myString As String)
        'create todays log file 
        Dim filesys As New FileStream("c:\TagLog\Log-" + fstoday + ".txt", FileMode.Append, FileAccess.Write, FileShare.Write)
        Dim Numrow As String
        filesys.Close()
        'open todays log file for writing
        Dim sw As New StreamWriter("c:\TagLog\Log-" + fstoday + ".txt", True, Encoding.ASCII)
        ListStr = ""
        SQLString = ""
        'display text to our textbox
        TextBox2.AppendText(myString)
        'Add the last input to the listbox
        ListBox1.Items.Add(TextBox2.Text)
        ListStr = ListBox1.Items(ListBox1.Items.Count - 1)
        SQLString = "Select From TagTable where QuickName='" + ListStr + "';"
        Try
            Dim cn As OdbcConnection = New OdbcConnection("driver={MySQL ODBC 5.3 Unicode Driver};server=" + SqlConn + ";port=3306;database=tagging;uid=TagUser;pwd=tagging;")
            cn.Open()
            Dim cmd As New OdbcCommand(SQLString, cn)
            Numrow = cmd.ExecuteScalar.ToString
            If Numrow IsNot Nothing Then
                SQLString = "UPDATE tagtable SET State = NOT State, Time=Now() WHERE QuickName='" + ListStr + "';"
                sw.WriteLine(vbCr + Now() + " " + ListStr)
                Call UpdateSQL()
                cn.Close()
            Else
                cn.Close()
                sw.WriteLine(vbCr + Now() + " Unknown Tag")
            End If
        Catch ex As OdbcException
            sw.WriteLine(vbCr + Now() + ex.ToString)
        End Try**
        sw.Close()
    End Sub

The part I believe is failing is the IF block after "NumRow". Any ideas anyone?

Upvotes: 0

Views: 67

Answers (2)

bastos.sergio
bastos.sergio

Reputation: 6764

You're select is wrong...

Change these lines:

Dim Numrow As String
...
SQLString = "Select From TagTable where QuickName='" + ListStr + "';"
...
Numrow = cmd.ExecuteScalar.ToString
If Numrow IsNot Nothing Then
...
End If

to

Dim Numrow As Int32
...
SQLString = "Select COUNT(*) From TagTable where QuickName='" + ListStr + "';"
...
Numrow = Convert.ToInt32(cmd.ExecuteScalar)
If Numrow > 0 Then
...
End If

Upvotes: 1

Jonathan Houston
Jonathan Houston

Reputation: 171

You need to name at least one column to select from. So

SQLString = "Select From TagTable where QuickName='" + ListStr + "';"

needs to be something like

SQLString = "Select * From TagTable where QuickName='" + ListStr + "';"

or

SQLString = "Select QuickName From TagTable where QuickName='" + ListStr + "';"

Upvotes: 1

Related Questions