Complex query (LIKE)

[cesarfrancisco]

Dim myConn As New OleDbConnection
myConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\..\DB.mdb;Jet OLEDB:Engine Type=5"
myConn.Open()
Dim myCMD As New OleDbCommand
[COLOR=blue]myCMD.CommandText = "Select * from Table1 where FN LIKE '" & FNTextBox.Text & "%'"[/COLOR]
myCMD.Connection = myConn
Dim myAdapter As New OleDbDataAdapter
myAdapter.SelectCommand = myCMD
Dim ds As New DataSet
ds.Clear()
myAdapter.Fill(ds, "Table1")
DataGridView1.DataSource = ds
DataGridView1.DataMember = "Table1"

How to save(update), delete the data displayed in the DataEntry form?

 

[cjard]

Have a read the DW2 link in my signature.

Dont ever (ever) think about writing an sql that accepts varying data by doing this:

"SELECT * FROM table WHERE col LIKE " & Textbox1.Text & "*"

 

[cesarfrancisco]

Thanks. I will going to read through it and will try to implement it in my "application" I was surprised by your warning (a newbie here). I am actually trying to mimic a certain feature of a commercial program. It has an Access database and it is used for store thousands of names and their data. The purpose of that query string is: when the exact spelling of a name is not known, a few letters or any other combination of that name to bring up a small batch of result. Of course, I do not know how this feature was implemented in the application. Mine is only an imitation of that feature.

"You wrote in an other post:

However, the other thing is, what you have there is a very bad way of coding.. Shoving all the db access code into a button click handler is NOT good for a variety of reasons, including encapsulation, maintainability, and readability of code, use of resources and database efficiency. Take a read of the DW2 link in my dignature.. it will help you build a Data Access Layer using the GUI, rather than typing this stuff manually.. the code gets done quicker, is 100% working, more robust (it does better error checking) and better encapsulated.. in turn it makes your code (that you write in your button click handlers) more readable and gives you more time to do proper logic without wasting time on the menial aspects such as the DAL.."

"do not shove.....into Button_click event. That is what I was intended to do. I used the TableAdapter.Fill method for queries before going over to OleDB, but I had to abandon them. I could not make a "variable" query from a text box.

I could not find anything about these problems in DW2.

 

[cjard]

erm

Couple of points..

1) you ARE allowed to press the ENTER/RETURN key here.. It will put words on a new line so they appear nicely spaced out etc
2)

is the QUOTE button. It produces a nice box round what someone else said:

To be or not to be...

Pressing QUOTE on the thread page rather than POST REPLY, will auto-quote what I wrote...

It will help my old, feeble eyes work out what part of that post there is what I wrote and what part is your response..

Cheers

 

[cesarfrancisco]

Thanks.
With the query above (disapproved by you

) I am wishing and trying to mimic
one feature of a commercial program's.
It also has an Access database used for the storage of THOUSANDS of names and their corresponding data.

The purpose of that (by you disapproved

) query string is:
A few letters or any other combination of letters (of a name)typed into the First Name or Last Name
TextBox will bring up a small batch of result in a DataGridView.

My program is a Form with one TabControl. This Control has two TabPages (1 and 2).

TabPage1 is a Data Entry Form.
It has ID, Attendance, First Name, Last Name, DOB, Phone, Note TextBoxes.

TabPage is a DataGridView Form.

If the result of the query is more than one, results show in DataGridView.
If query result is one. Data is displayed in DataEntry Form.

Next step: if the result is more than one:
user choose one of the names by clicking on the RowHeader of the desired row in DataGridView.

Data is "transferred" to DataEntry Form.
DataEntry Form is the "kitchen" where the data is queried, modified, added, deleted and saved
(by the -your disapproved

) Button events.






I have used the TableAdapter.Fill method
(an easier, more straightforward click, click, copy and paste,
chop, chop mate affair a la DW2 )
for queries before resorting to OleDB, but I had to abandon them.

I could not make a "variable" query from a text box.

 

[cjard]

I have used the TableAdapter.Fill method
(an easier, more straightforward click, click, copy and paste,
chop, chop mate affair a la DW2 )
for queries before resorting to OleDB, but I had to abandon them.

I could not make a "variable" query from a text box.

Perhaps you missed the DW2 section on creating a form to search data:

http://msdn2.microsoft.com/en-us/library/hbsty6z7(VS.80).aspx

 

[cesarfrancisco]

Actually, nobody can solve this problem.

SELECT * FROM Table1 WHERE LN LIKE " & LastNameTextbox1.Text & "%"

This is a query for Last Names, when the user do not remember the exact spelling of a name (out of thousands).

Write this query by ONE TableAdapter.Fill method.

USE only Last Name TextBox (No Dialog boxes, etc) for query entry.

 

[cjard]

Actually, nobody can solve this problem.

Perhaps you missed the DW2 section on creating a form to search data:

http://msdn2.microsoft.com/en-us/library/hbsty6z7(VS.80).aspx

 

[cesarfrancisco]

Post the answer, please.

 

[cjard]

What do you want me to do? Come to your place of work and show you personally? It's what I'd have to do to provide a more in-depth explanation than the Microsoft article.

If you want someone to do it for you, try rentacoder, getacoder, getafreelancer or some other site where you put your money where your keyboard is..

Read the article, because this will be my last answer in this thread

 

[cesarfrancisco]

I tell you, we are here on Earth to fart around, and don't let anybody tell you different.
Kurt Vonnegut 1922-2007

I did not mean to upset you. If I did, I am sorry.

 

[cjard]

I dont get upset, sorry if I came across that way; its actually (sometimes) quite the amusing highlight of my day..

Did you read the article?

 

[cesarfrancisco]

(Unfortunately Select * where LName Like LName ? is doesn't seem to giving a result like "Anything%")

These are not my lines but it talks about my problem.
How to Get a TableAdapter to Process Dynamic SQL SELECT Queries

The problem with what we’ve seen of the TableAdapter so far is that all this configuration and resulting code generation is strictly a design-time activity. What if you want to construct your SELECT query at run-time, based upon certain criteria entered by the user on your form?
Is there a way to do this and then pass the dynamic SQL to the TableAdapter? The short answer is “No” – unless you’re willing to hack the auto-generated code in the DataSet Designer .VB file.

 

[cjard]

(Unfortunately Select * where LName Like LName ? is doesn't seem to giving a result like "Anything%")

On a pure SQL syntax level, that command is totally and utterly broken..

SELECT * FROM table WHERE lastName LIKE ?



Then, into the parameter, you actually put the wildcard.. e.g.

MyTableAdapter.FillByLastName(myDataSet.MyDataTable, "Smith%")


Have another read of the article I keep pointing you to. I know it sounds like a broken record, but try and read it as though you have enthusiasm that it will solve your problem (because it will) rather than with resentment because I keep telling you something that you dont believe will work (trust me; it will work)

 

[cesarfrancisco]

It did work.

Private Sub Find_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Find.Click
    If txtboxFN.Text <> " " Then
        Try
            Me.Table1TableAdapter.FillBy(Me.AmDBDataSet.Table1, txtboxFN.Text + "%")
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    ElseIf txtboxLN.Text <> " " Then
        Try
            Me.Table1TableAdapter.FillBy(Me.AmDBDataSet.Table1, txtboxLN.Text + "%")
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    ElseIf txtboxID.Text <> " " Then
        Try
            Me.Table1TableAdapter.FillBy5(Me.AmDBDataSet.Table1, Integer.Parse(txtboxID.Text))
        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
     End If  
 
        Dim result As Integer = 0
        result = Table1BindingSource.Count
        MessageBox.Show(result)
        If result > 1 Then
            Me.TabControl1.SelectedTab = TabPage2
        End If
 
   End Sub