VB6 and Access DB- Search and Replace

[starscrea2]

Hi,

I am trying to create a tool that will find data typed into one textbox and replace with data in another textbox as the user types, according to what is in different columns in a database. For example:

Column 1 has English words: "Hello", "Heat", etc
Column 2 has Spanish words "Hola", "Calor", etc

I have a MDB file with the English words in Column 1 alphabetized, and their Spanish corresponding words are in Column 2, on the same row, for example

Col1 Col2
Heat| Calor
Hello| Hola
Hot | Caliente
Is | Es
It | Es
Really| Muy

What I want to do is the following:

The user types "Heat" in text1, and the word "Calor" appears in text2. Or the user types "It Is Really Hot" in text1, and the words "Es Es Muy Caliente" appears in text2.

I'm using the ADO control to connect to the database using VB6.

From what I see, I need to

a)write a find command so the control queries the database on column(1) for the word typed.
b) move the query one column over, on the same row to get the Spanish word.
c) replace the English word with the Spanish word.
d) once the word has been replaced, move on to the next word being typed, ignoring the word that has been replaced as part of the query, so the database only queries for the word being typed, and not the entire phrase in text1, ie if I have replaced "Hot" with "Caliente" and then I type "Es", the database only queries for "Es" and not the string "Caliente Es".


Any help would be greatly appreciated. Thanks!

[akhileshbc]

For reference:

1. http://www.vbforums.com/showthread.php?t=337051#ado
2. http://www.w3schools.com/sql/sql_select.asp
3. http://www.sql-tutorial.net/SQL-LIKE.asp

[starscrea2]

Thank you, I will check those links out.

[starscrea2]

Hi,
I have run into an issue here, any ideas? My database name is span, my database table is customers and the column with the spanish words is called FirstName. FlexGrid is bound to the Adodc1 control to display the results.

I am trying the following code and am getting an error 2147217900 syntax error in FROM clause.


Private Sub cmd1_Click()
Dim dselect As String
dselect = Text1.Text



Adodc1.RecordSource = "Select * from [customers] Where [FirstName] LIKE '" & dselect & "%'"
Adodc1.Refresh

FlexGrid1.AllowUserResizing = flexResizeBoth
End Sub

Can anyone see the error? Thanks!

[starscrea2]

Without using SQL, I got the control to (sort of) work with the following code

   
      Private Sub Command1_Click()
   
          Dim target_name As String, sResult As String
   
          Dim r As Integer, c As Integer
   
         
  
          target_name = Text1.Text
   
          If Len(target_name) = 0 Then Exit Sub
   
         
   
          With MSFlexGrid1
   
              .Redraw = False
  
              .FillStyle = flexFillRepeat
  
              .Col = 1: .Row = 1:
  
              .ColSel = .Cols - 1
  
              .RowSel = .Rows - 1

              .CellBackColor = vbWhite
  
              .Col = 0: .Row = 0
 
             
  
              .RowHeight(-1) = .RowHeight(0)
  
              For r = 1 To .Rows - 1
 
                  For c = 1 To .Cols - 1

                      If .TextMatrix(r, c) Like target_name & "*" Then Exit For
  
                  Next c
  
                  If c = .Cols Then .RowHeight(r) = 0

              Next r

              .Redraw = True
            .SetFocus
            
             
              
         

 
              
         
.Refresh
Set MSFlexGrid1.DataSource = Adodc1




Text2.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row + 1, 3)

 

End With

  
  End Sub

The problem is that the program is accessing the data from column 3 before it was queried. For example

The first word in my list was
Incitar | Incite

I queried for the word "Incauto" (seize)

The result Incauto comes up fine, with all the columns showing, for just that word.

text 2 fills up with "incite", which makes me think I didn't properly kill the data from memory when the query executed. Does that make sense? How do I tell the record to look for the search results, not the original database? I can attach the project if anyone is interested in looking at it. Thanks!

[dee-u]

You can attach it so interested members will be able to test it out.

[starscrea2]

Here is the project. It uses the MSHFlexGrid, sorry I said MSFlexGrid originally.

[dee-u]

It looks like you are looking in all the columns, I thought you only had 2 columns for the English and Spanish?

[jcis]

You'll find some big problems if you want this to translate word by word, simple cases like possesive case need to invert the word to make a good translation, like:

"Jhon's house" -> "La casa de Jhon"

Even most common translations would fail for this same problem:

"Electromagnetic waves" -> "Ondas Electromagnéticas"

1 word translation would be easier to do, else you'll need some extra habilities (or dlls) to make it work fine. Also, In the words in your db the words in spanish are not right, it should be..

Is | Es / Está
It | Esto / Esta / Este
Really| Realmente

(/) separates different possible translations depending on the context it's used.

"It Is Really Hot" could be translated as:

1) "Esto está muy caliente" (something, like an object that's really hot)
2) "Hace mucho calor" (weather is hot, temperature is high)

[starscrea2]

It's a daunting task, I agree, but anything is possible with enough code, right?

Ideally, I'd like to scan the database as you type for strings like "hace calor" and be able to differentiate that meaning of hacer from the meaning in the string "hace mucho tiempo". Maybe set up certain keywords and tell the program to always look two words forward or two words back if it encounters those words, trap any word combinations that change the meaning of the word into something different, then plug them in to the textbox. And then there's the conjugations, the grammar rules for subjunctive, all that. There's a lot to do.

[starscrea2]

It looks like you are looking in all the columns, I thought you only had 2 columns for the English and Spanish?

I was trying to scan only the first 2 columns of the database. The database has columns for Spanish/French, Spanish/Portuguese, etc further along. However, I created a new "dummy" database with only two columns and a few definitions and words to test out, and now it doesn't even fill in the data from the "unrefreshed" grid. Here is a new project with db1.mdb replacing span.mdb. It only has the two columns, one for Spanish and one for English.

Thanks for any help!