Hai, i'm new here, i would like to ask about SQL statements, i'm using VB6.0 and my systems if more on searching record, so the SQL statements is very important to search a record, so back to the question, i do this to search the "company name" - Private Sub cmdNameView_Click()
-
Dim strSQL As String
-
inputName = UCase(txtName.Text)
-
Set rs = New ADODB.Recordset
-
If inputName <> "" Then
-
strSQL = "SELECT * FROM SUPPLIER WHERE sName LIKE '*" & inputName & "*'"
-
MsgBox strSQL
-
rs.Open strSQL, conn, adOpenKeyset, adLockPessimistic, adCmdText
-
fillfields
-
End If
-
-
End Sub
And here the method of fillfields() - Public Sub fillfields()
-
-
NotEditable
-
frameSDetail.Visible = True
-
If Not (rs.BOF = True Or rs.EOF = True) Then
-
txtsName.Text = rs.Fields("sName")
-
txtsAddress.Text = rs!sAddress
-
txtPost_Code.Text = rs!Post_Code
-
txtCity.Text = rs!City
-
txtPhone.Text = rs!Phone
-
txtEmail.Text = rs!Email
-
txtFax.Text = rs!Fax
-
txtService.Text = rs!Service
-
Else
-
MsgBox "No Record Match", vbExclamation, "Cannot Move"
-
End If
-
Exit Sub
-
-
End Sub
I got the msg "No Record Match" when i try the LIKE statements in the Microsoft Office access same as my code above, it return correct output. then i thought it is because of the logic error in fillfields method, then i try change the LIKE statements to - SELECT * FROM SUPPLIER WHERE sName = '" & inputName & "'
it return a correct output. For your information sName field cannot be the same and i already create a code to validate it. Can anyone can help me to trace the logic error on it?
thanks in advance
6 7075 -
strSQL = "SELECT * FROM SUPPLIER WHERE sName LIKE '% & inputName & %'"
-
-
SQL syntax for LIKE uses % in most cases. Not *
I have always used astrik's but I don't think that is the problem. I would change adLockPessimistic to adLockOptimistic. - Private Sub cmdNameView_Click()
-
Dim strSQL As String
-
inputName = UCase(txtName.Text)
-
Set rs = New ADODB.Recordset
-
If inputName <> "" Then
-
strSQL = "SELECT * FROM SUPPLIER WHERE sName LIKE '*" & inputName & "*'"
-
MsgBox strSQL
-
rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic, adCmdText
-
fillfields
-
End If
-
-
End Sub
Let me know if that doesn't work.
Edward
I have always used astrik's but I don't think that is the problem. I would change adLockPessimistic to adLockOptimistic.
Edward
* Isn't a universal syntax for SQL like.
% as far as I know..is more universal across multiple database platforms. Why not to use * in SQL Like
I also do not think the problem is the locking method of opening the recordset.
That only relates to the contraints on the records of your database in terms of updating and inserting.
It has nothing to do with whether or not records will be returned in a SELECT.
* Isn't a universal syntax for SQL like.
% as far as I know..is more universal across multiple database platforms. Why not to use * in SQL Like
I also do not think the problem is the locking method of opening the recordset.
That only relates to the contraints on the records of your database in terms of updating and inserting.
It has nothing to do with whether or not records will be returned in a SELECT.
Yes!! It works!, thank u everyone, my problem solved, must use the "%" symbol to make it works - strSQL = "SELECT * FROM SUPPLIER WHERE sName LIKE '%" & inputName & "%'"
I think my problem solve, today, my Access 2003 and VB6.0 act weird. Here what i face today: - strSQL = "SELECT * FROM SUPPLIER WHERE sName LIKE '%" & inputByName & "%'"
i enter word "AM" and my VB return correct result. - strSQL = "SELECT * FROM SUPPLIER WHERE City LIKE '%" & inputByName & "%'"
i enter word "W" my program say Cannot find Record
i feel weird, then i try in Microsoft office 2003 - SELECT * FROM SUPPLIER WHERE sName LIKE '%AM%'
Microsoft office return no record found
then i try in Microsoft office 2003 - SELECT * FROM SUPPLIER WHERE sName LIKE '*AM*'
Microsoft office return correct result
then i try in VB6.0 - SELECT * FROM SUPPLIER WHERE sName LIKE '*AM*'
VB6.0 return cannot find record
Can anyone explain to me what happen
Hi,
For VB6 + ADO + Access Combination, you need to query with %:
SELECT * FROM SUPPLIER WHERE sName LIKE '%AM%'
Regards
Veena
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Bart Nessux |
last post by:
Should an if statement have a corresponding else statement? Or, is it OK
to have an if statement by itself. For completeness, it seems the two
should be together, but from experience I know that a...
|
by: Jaime Wyant |
last post by:
I know I've seen this somewhere, but can't seem to google it. Is
there a way to use an alternate statement separator, other than the
default ';'?
jw
|
by: Tom D |
last post by:
I'm rewriting a database interface that our company currently has.
Currently it's using the Pear::DB interface, but we found that that was
introducing a bit too much overhead. I'm rewriting the...
|
by: slogging_away |
last post by:
Hi - I'm running Python 2.4.2 (#67, Sep 28 2005, 12:41:11) on win32, and have a script that makes numerous checks
on text files, (configuration files), so discrepancies can be reported.
The script...
|
by: Fuzzyman |
last post by:
Hello all,
The following is a copy of a blog entry. It's asking a question about
future statements and the built in compile function. I'd appreciate any
pointers or comments about possible...
|
by: Neroku |
last post by:
Hello, i would like to know what the serious definition of statements
and expressions is:
i know an expression are evaluated to a value, i.e:
1 == 2
5+7
foo( 1,2)
and a statement is...
|
by: ojorus |
last post by:
Hi!
Some questions regarding the mysqli-extension (php5)
1) Prepared statements: If I understand things right, prepared statements
will give better performance if you make several similar...
|
by: Dmitri |
last post by:
Hello!
I have a developer that is playing around with some SQL statements
using VB.NET. He has a test table in a SQL 2000 database, and he has
about 2000 generated INSERT statements.
When the...
|
by: Gary Herron |
last post by:
Ohad Frand wrote:
There is no way you can consider 'elif', 'else', 'except', and 'from'
statements. However, as someone pointed out, the kwlist from the
keyword module is the closest thing we...
|
by: Ohad Frand |
last post by:
Hi
Thanks a lot for your reply
I think the main uses for it is to study the language and to see that I
didn't miss anything else or that something is changed from one version
to another.
The...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |