none
MySQL 5.1 SELECT WHERE Statment

    Question

  • Hi everybody;

    I have built a SQL database and table and am trying to query records by user input (textbox.text).

    I can query the entire database by the SELECT command as expected.  However, when I try to add in the WHERE clause to the SQL statement, it constantly says it can't find a record in the column specified that matches the user input.  To make sure I am processing the correct information, I have verified  the column name is typed correctly, I have tried querying by ID (auto number primary key field), I have tried querying by hard coded variable instead of user input into a control, I have copied and pasted all information including search variable into SQL SELECT syntax to ensure nothing had changed or was being change, I have tried using "" and '' and all kinds of variations, I have tried using @ to make the variable a parameter, I have tried + for the user input control.........I have tried EVERYTHING I can think of at this point........I'm at a loss at this point and have ZERO idea what else to try outside of the 246 GOOGLE searches I have done over the past 15 hours.  Here is 1 of the TONS of variations of the SQL SELECT WHERE statement I have tried....again, if I remove the WHERE clause the SELECT statement works great....so the issue seems to be from WHERE to the end of the statement:

     
    Imports System.Windows.Forms
    Imports MySql.Data.MySqlClient
    
    Public Class OfficeAddressPreview
    
        Dim ServerString As String = "Server=localhost;User Id=root;Password=*******;Database=siteaddresses"
        Dim SQLConnection As MySqlConnection = New MySqlConnection
        Dim Command As New MySqlCommand
        Dim output As New DataTable
    
        Private Sub OK_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK_Button.Click
            Me.DialogResult = System.Windows.Forms.DialogResult.OK
            Me.Close()
        End Sub
    
        Private Sub Cancel_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel_Button.Click
            Me.DialogResult = System.Windows.Forms.DialogResult.Cancel
            Me.Close()
        End Sub
    
        Private Sub OfficeAddressPreview_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    
            SQLConnection.ConnectionString = ServerString
            OfficeSiteAddressDGV.DataSource = Nothing
    
            Try
                Dim user_content As String
                user_content = OfficeERFApplication.AttentionToTextBox.Text
                Command = New MySqlCommand("SELECT * FROM site_addresses WHERE Tech_Name =" & Chr(34) & OfficeERFApplication.AttentionToTextBox.Text & Chr(34))
                Dim MySqlA As New MySqlDataAdapter(Command)
                MySqlA.SelectCommand.Connection = SQLConnection
                MySqlA.Fill(output)
    
                If output.Rows.Count = 0 Then
    
                    output = Nothing
                    MsgBox("No data available for the listed tech, please verify the tech's name is spelled as it is in Coventry's Active Directory service.  If you have spelled the name correctly, you can add the tech and corresponding site by clicking the Add button on this page.", 0, "Error: No Record Found")
    
                Else
    
                    OfficeSiteAddressDGV.DataSource = output
    
                    MsgBox("Please select the appropriate location from the following list.  If the correct office is not listed, click the Add button to create a new shipping address for this tech.")
    
                End If
    
            Catch ex As Exception
    
                MsgBox(ex.Message)
    
            End Try
    
        End Sub
    
    End Class
    

    One last time, There are 2 forms, Form1 (Parent (OfficeERFApplication)) and Form2 (Child Dialog Box(OfficeAddressPreview)).

    On OfficeERFApplication, the user fills in AttentionToTextBox with a person's name then hits the enter key.  Enter key opens Form2 (OfficeAddressPreview) Dialog Box and starts the connection to SQL database.  At this point, if I remove from WHERE on in the SELECT statement, the entire table is shown properly in the DGV, but as soon as I add any form of the WHERE clause to the SELECT statement, it queries nothing as a result, it says in the column I am searching, the variable I supply  is not found.  Sorry for how all over the place I am on this post but I've been up over 48 hours programming and the past 15 hours has been spent on this 1 single issue.....this one single line of code (and yes this is my first time working with SQL in this complex of a manner).....yes I am a very persistent person LOL. 

    And please, for all of you out there that quick scanned over this post and are now thinking "Well why don't you" or "that's stupid" or "why would you".....just please don't.......over 2 days up coding straight........not in the mood LOL.....I'm just simply looking for a working solution to what I have already....there's a reason I do everything I do the way I do it.....no offense.

    Thank you for all of your help and training ladies and gentleman.

    Sunday, July 14, 2013 12:39 AM

Answers

  • I'd suggest you try asking on a MySQL forum rather than MS SQL forum. While similar there are key differences between the two (as I found when I last dabbled in MySQL and couldn't get a query that works in MSSQL to work on it) so unless you get lucky and someone on here is also experienced with MySQL you're unlikely to get much help. I do know all MySQL statements have to end in a ; which yours doesn't, but for all I know that rule doesn't apply when scripting the query like you are.

    Well I figured out what is wrong but it's not the SQL statement.  I need to close this and create a new question under VB.NET.  The issue is, when the user inputs data into the textbox that I am using as the search variable and hits the enter key to query the server, the application is registering the original textbox.text value as the variable instead of what is currently in the box.  I switched the textbox.text property to a value that is in the database and it queries that variable every time now no matter I put into the textbox during execution.......well at least I can stop pulling my hair out looking for a problem that doesn't exist in my SQL statement lol.
    • Marked as answer by 51Patrick1 Sunday, July 14, 2013 4:11 PM
    Sunday, July 14, 2013 4:11 PM

All replies

  • I'd suggest you try asking on a MySQL forum rather than MS SQL forum. While similar there are key differences between the two (as I found when I last dabbled in MySQL and couldn't get a query that works in MSSQL to work on it) so unless you get lucky and someone on here is also experienced with MySQL you're unlikely to get much help. I do know all MySQL statements have to end in a ; which yours doesn't, but for all I know that rule doesn't apply when scripting the query like you are.
    Sunday, July 14, 2013 10:48 AM
  • I'd suggest you try asking on a MySQL forum rather than MS SQL forum. While similar there are key differences between the two (as I found when I last dabbled in MySQL and couldn't get a query that works in MSSQL to work on it) so unless you get lucky and someone on here is also experienced with MySQL you're unlikely to get much help. I do know all MySQL statements have to end in a ; which yours doesn't, but for all I know that rule doesn't apply when scripting the query like you are.

    Well I figured out what is wrong but it's not the SQL statement.  I need to close this and create a new question under VB.NET.  The issue is, when the user inputs data into the textbox that I am using as the search variable and hits the enter key to query the server, the application is registering the original textbox.text value as the variable instead of what is currently in the box.  I switched the textbox.text property to a value that is in the database and it queries that variable every time now no matter I put into the textbox during execution.......well at least I can stop pulling my hair out looking for a problem that doesn't exist in my SQL statement lol.
    • Marked as answer by 51Patrick1 Sunday, July 14, 2013 4:11 PM
    Sunday, July 14, 2013 4:11 PM