using sql statment

[omerg84]

hi,
i want to use sql statment with vb the example:
i need to search any record the contains 'abc'
how am i doing it with like statment and vb?
omer

[SeanK]

What have you tried that doesn't work?

[GaryMazzone]

Like this:

First you need a connection to the database that is made and opened.
Second a recordset object declared and initiated.
Dim a string var as strSQL

Set the strSQL value like this:

strSQL = "Select [i][b]fieldname,fieldname,..... things being returned[/b][/i] From [i][b] tableName to be seached[/b][/i] Where [i][b] fieldname to seach [/b][/i] Like '%abc%'"

Now open the recordset and test if any records returned.

[omerg84]

i tried to search a word(abc for example) in the tables with the statment like
i didn't understand the vb basic code

[Hack]

What was the code that you had/have that you didn't understand?

Also, what database are you using? The characters supported by LIKE differ between Access and SQL Server.

[omerg84]

i didn't understood that code:
strSQL = "Select fieldname,fieldname,..... things being returned From tableName to be seached Where fieldname to seach Like '%abc%'"
i'm using access database
omer

[GaryMazzone]

Hack I use the same character with SQL Server and Access if I am doing the Connection from VB and it works on boths. If using Access exclusively (the code is written in Access) then the character makes a differance.

[Hack]

Well, we can't give you specific examples because we don't know what your field names are nor do we know the name of your database or their tables.

So, Gary's example, using Field1, Field2, etc and tablename was just using generics.

So, take what he did, plug in the real field names and table name and give it a shot.

[GaryMazzone]

Is the code written in VB or Access. Look in the thread: http://www.vbforums.com/showthread.php?t=349994 for a tutorial on using ADO with VB6 and databases.


Take a look here: http://sqlcourse.com/ for a SQL tutorial

[omerg84]

i'm searching word in hebrew התראה in field status the tables are all the tables in the database
(that contains status'for each table in the db there is status table )
how can i search the word with sql and vb?
omer

[omerg84]

and i tried the code that given to me'it didn't work(the vb basic code)

[GaryMazzone]

So what is the error or does it just not return anything?

[omerg84]

the error is: can't find object *התראה* the code is:
sqlstr="select * from "+editdata.database.tabledefs(i).name+ "where status like *התראה*
the table exist,i checked it

[GaryMazzone]

First the status after like need to be inside single qoutes (I'm assuming here that you are seaching on a text field.), second the * should be a % if you are passing this from VB and * is doing all this from inside Access.

Like this:

sqlstr="select * from " & editdata.database.tabledefs(i).name & " where status like '%התראה%'

[omerg84]

i wrote it exactly like that'the editor added " in the end of the line
and the error still exist
what could be the problem?
omer

[GaryMazzone]

Please post the exact sql that is being generated by the application. Set a break point and get the query of do a debug print to the immediate window and post the statement back here.

[omerg84]

the sql is:select * from " & editdata.database.tabledefs(i).name & " where status like '%התראה%'
the error is error number 3011
omer

[GaryMazzone]

I want to see the actual sql that is being sent to the application. Not the statement you wrote to build the SQL. Do you know how to get that actual statement to print out?

[omerg84]

i did it with debug.print sqlstr
and that is the result:
"select * from 0331345status where status like '%התראה%'"
explain:status is name of column in the table 0331345status
what can be wrong here?

[GaryMazzone]

I think it is having a problem translating the hebrew charaters. When I created a table with the same name an posted the characters you were seaching form I also got nothing when I removed the where condition and just retrieved the data I go ????? as the text in the field.

[omerg84]

so,how can i resolve the problem??
in the isreali forums they didn't answer...here it's faster
omer

[GaryMazzone]

Sorry I don't think I'll be able to help here then.