Query MySQL DB and return all Rows/Values to richtextbox
Query MySQL DB and return all Rows/Values to richtextbox
Query MySQL DB and return all Rows/Values to richtextbox
Query MySQL DB and return all Rows/Values to richtextbox
Query MySQL DB and return all Rows/Values to richtextbox
Query MySQL DB and return all Rows/Values to richtextbox Query MySQL DB and return all Rows/Values to richtextbox Query MySQL DB and return all Rows/Values to richtextbox Query MySQL DB and return all Rows/Values to richtextbox Query MySQL DB and return all Rows/Values to richtextbox Query MySQL DB and return all Rows/Values to richtextbox Query MySQL DB and return all Rows/Values to richtextbox Query MySQL DB and return all Rows/Values to richtextbox
Query MySQL DB and return all Rows/Values to richtextbox Query MySQL DB and return all Rows/Values to richtextbox
Query MySQL DB and return all Rows/Values to richtextbox
Go Back  Xtreme Visual Basic Talk > > > Query MySQL DB and return all Rows/Values to richtextbox


Reply
 
Thread Tools Display Modes
  #1  
Old 02-09-2013, 01:31 AM
gr8_big_geek gr8_big_geek is offline
Newcomer
 
Join Date: Dec 2010
Posts: 2
Default Query MySQL DB and return all Rows/Values to richtextbox


Alright all,

I'm able to connect to my database just fine. I can enter things into MySQL DB without any issues from my VB 2010 form. Next, I'm trying to search for the data I had entered and return the results to a textbox or a richtextbox (prefered). So for instance I have the following setup on the DB side:
Database Name: db_name
Table: people
Column 1: first_name
Column 2: last_name
Column 3: DOB
Next, my code which does good in checking for a match (which is currently only searching by using the first name) is as follows:

HTML Code:
  Private Sub BtnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSearch.Click
        MysqlConn = New MySqlConnection()
        Dim iReturn As Boolean
        'This allows me to validate/connect to the database
        MysqlConn.ConnectionString = "server=IP_Addy_Here;user id=username;password=my_password;database=db_name"

        'this is the whole using section here
        Using sqlCommand As New MySqlCommand()
            'begin with for sqlCommand
            With sqlCommand
                .CommandText = "SELECT * FROM  `people` WHERE  `first_name` LIKE '" & TxtSearchFirstName.Text & "' LIMIT 0 , 30"
                .Connection = MysqlConn
                .CommandType = CommandType.Text
            End With
            'end with for sqlCommand

            'now try applying
            Try
                MysqlConn.Open()
                sqlCommand.ExecuteReader() 'this one works just fine when I use it
                'Dim reader = sqlCommand.ExecuteReader() 'not sure if I need this?
                iReturn = True
                MsgBox("Name was found!", vbInformation, "Success!")
                TxtReturnTest.Text = sqlCommand.CommandType.Text 'this isn't returning the last name
                'it simply returns, "SELECT * FROM  `people` WHERE  `first_name` LIKE 'john' LIMIT 0 , 30"
                RchBoxReturn.Text = sqlCommand.CommandText ' this has got me bothered
                'this only returns a number 1 
				'NOTE:
				' What I would like is  RchBoxReturn.Text = `first_name` & " " &`last_name` & " " &`DOB`

			Catch ex As MySqlException
                'if there's an issue print out the problem
                MsgBox(ex.Message.ToString)
                iReturn = False
            Finally
                'always close the db at the end
                MysqlConn.Close()
            End Try
            'end the try
        End Using
        'end the whole using section

    End Sub
Ideally, if I search the system for the first_name `john` I would like the system to return all matches. Cancantenating first_name + last_name + DOB with spaces between. This way if I search using first name, last name and then DOB it will limit more and more the possibility of matches.

How can I get this darn thing to search for a match then return the values into a richtextbox? I will keep working on it and seeing if I can figure it out but any assistance would be wonderful.

Cheers~
Reply With Quote
  #2  
Old 03-12-2013, 09:18 AM
torpkevuk's Avatar
torpkevuk torpkevuk is offline
Contributor
 
Join Date: Oct 2003
Location: Nebraska, USA
Posts: 640
Default

It seems like you need help with a couple of different things here.. how to return the values from the database query - honestly, a quick google will help with that though. https://www.google.com/search?num=30...ues+from+mysql


Also however, how to search using sql

the way your search is set up, you will only ever return values based on first name - you won't be searching last name or date of birth - though the sql you have will return all 3 values.

Instead of:

Code:
SELECT * FROM  `people` WHERE  `first_name` LIKE 'john' LIMIT 0 , 30
use
Code:
SELECT first_name, last_name, dob FROM people WHERE first_name LIKE 'john%'"
(put the limit back in there if you like).

SELECT * is just bad practice, it returns all columns from the table. Also, note the % after john - that is a wild card - so you could search for Jo% and you'd get John.. Joan.. Joanne.. etc.

Returning 'John Smith 1/1/1989' will not help you narrow down your search though - a better approach would be to have a text field for first name, one for last and one for date of birth, then search for all of them

Code:
SELECT first_name, last_name, dob FROM people WHERE
first_name LIKE 'john%' AND
last_name LIKE 'smith%' AND
dob = '1/1/1989'
you can put logic around each line being sent to MySQL so it doesn't include the line if its theres no value (for example, searching for John Smith but no date of birth - just skip the dob = part)

Or you could do it like this:

Code:
SELECT first_name, last_name, dob
FROM people
WHERE CONCAT(first_name, ' ', last_name, ' ', convert(dob, char(10))) LIKE 'John%'
Then you could search with a single phrase like 'John Br' and it'll return something like:

John Brown 1/1/2005
John Brown 1/1/1968

I'd separate the search terms out though to be honest.

This still isn't ideal - you really should look into using stored procedures within MySQL you can then send something like

Code:
EXEC people_getbyfirst_last_dob 'John', 'Smith', ''
And within the stored procedure, return the first_name, last_name and dob based on the parameters passed. I'd definitely recommend doing it this way - but get your code working first - it'll be better to master each part so its not all one big confusing mess at the same time.

Good luck

Last edited by torpkevuk; 03-12-2013 at 09:28 AM. Reason: tags
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Query MySQL DB and return all Rows/Values to richtextbox
Query MySQL DB and return all Rows/Values to richtextbox
Query MySQL DB and return all Rows/Values to richtextbox Query MySQL DB and return all Rows/Values to richtextbox
Query MySQL DB and return all Rows/Values to richtextbox
Query MySQL DB and return all Rows/Values to richtextbox
Query MySQL DB and return all Rows/Values to richtextbox Query MySQL DB and return all Rows/Values to richtextbox Query MySQL DB and return all Rows/Values to richtextbox Query MySQL DB and return all Rows/Values to richtextbox Query MySQL DB and return all Rows/Values to richtextbox Query MySQL DB and return all Rows/Values to richtextbox Query MySQL DB and return all Rows/Values to richtextbox
Query MySQL DB and return all Rows/Values to richtextbox
Query MySQL DB and return all Rows/Values to richtextbox
 
Query MySQL DB and return all Rows/Values to richtextbox
Query MySQL DB and return all Rows/Values to richtextbox
 
-->