Connecting Tech Pros Worldwide Help | Site Map
bytes > topic > visual basic > answers

data not being retrieved from Access database

Member
  
Join Date: Nov 2006
Posts: 72
#1: May 12 '07
Hi there vb genius... I have a problem, i'am currently doing a program that has a database on it. i'am using access database, my problem is the records on my database table doesnt appear on my form...

The scenarion is this.. my database table name is "SUBJECTS" and has a field name "SUBJECT CODES" and "ID" for my primary id.

heres my code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. If rs.State = adStateOpen Then rs.Close
  3.        rs.Open "Select * from [SUBJECTS] where 'PrimaryID Like' = 'ABA0101**'", cnn, adOpenKeyset, adLockOptimistic
  4.  
  5.     While rs.EOF <> True
  6.         For x = 0 To 7
  7.         Text3(x).Text = rs.Fields("SUBJECT CODES").Value
  8.         Next x
  9.      rs.MoveNext
  10.     Wend
  11.  
  12.  
the records must be display in the text box area, but it seems not to be working, i think i'am missing something in my code.. when i try to compile it, it doesnt give any error at all, so it means my database code is correct, i pressume.. but it doesnt do anything at all, it doesnt display all the records in my text boxes. Can someone help me out how can i display it in my text boxes. thank you very much.
Moderator
  
Join Date: Oct 2006
Location: Australia
Posts: 7,748
#2: May 12 '07

re: data not being retrieved from Access database

Hi Darrel.

Looks as though you misunderstood what I said about the Like operator. Is this supposed to return a bunch of records matching a wildcard value, or is "ABA0101**" actually the exact value which you expect to find in them? In other words, is this supposed to find records which start with "ABA0101"?

If it is supposed to match an exact value, then you don't use Like at all, just =.

If it is a wildcard, then you do need to use Like, but your syntax is wrong. Here is the syntax for the two options...

Expand|Select|Wrap|Line Numbers
  1. Your current code...
  2. Select * from [SUBJECTS] where 'PrimaryID Like' = 'ABA0101**'
  3.  
  4. Exact match...
  5. Select * from [SUBJECTS] where PrimaryID = 'ABA0101**'
  6.  
  7. Wilcard search...
  8. Select * from [SUBJECTS] where PrimaryID Like 'ABA0101*'
Member
  
Join Date: Nov 2006
Posts: 72
#3: May 15 '07

re: data not being retrieved from Access database

thank you fo rthe reply. but sad to say it doesnt wrong also,, here my code:

Expand|Select|Wrap|Line Numbers
  1. If rs.State = adStateOpen Then rs.Close
  2.        rs.Open "Select * from [SUBJECTS] where 'PrimaryID Like' = 'ABA0101*'", cnn, adOpenKeyset, adLockOptimistic
  3.  
  4.     While rs.EOF <> True
  5.         For x = 0 To 7
  6.         Text3(x).Text = rs.Fields.Item("SUBJECT CODES").Value
  7.  
  8.         Next x
  9.      rs.MoveNext
  10.     Wend
  11.  
yes and it is a wildcard choice, you code is this

Expand|Select|Wrap|Line Numbers
  1.  
  2. "Select * from [SUBJECTS] where PrimaryID Like = 'ABA0101*'", 
  3.  
  4.  
Ive noticed that you you disregard the single quote on the "PrimaryID Like" word and the equal sig, i have followed that but it gives me an error. but when i place a signle quote and equal sign on it. the error is gone but still it doenst display t he records..

i hope you can help me with this please... i'am being desperate.
Moderator
  
Join Date: Oct 2006
Location: Australia
Posts: 7,748
#4: May 15 '07

re: data not being retrieved from Access database

You can't use both the "=" and the "Like" operator. They are two different types of comparison. Use one or the other, depending on whether you want an exact match or a wildcard "fuzzy" match.

The syntax is...
WHERE FIELD = 'Value'
or
WHERE FIELD LIKE 'WildcardValue'
Member
  
Join Date: Nov 2006
Posts: 72
#5: May 15 '07

re: data not being retrieved from Access database

Thank you for that i was able to display the records on my text boxes but now my problem is it only display one record, an it is the last records of my field.

heres my code:

Expand|Select|Wrap|Line Numbers
  1. If rs.State = adStateOpen Then rs.Close
  2.        rs.Open "Select * from [SUBJECTS] WHERE 'PrimaryID LIKE  ABA0101*'", cnn, adOpenKeyset, adLockOptimistic
  3.  
  4.     While rs.EOF <> True
  5.         For x = 0 To 7
  6.         Text3(x).Text = rs.Fields.Item("SUBJECT CODES").Value
  7.         Next x
  8.      rs.MoveNext
  9.     Wend
  10.  
e.i. it only display HUM411T, in all my textboxes and thats not want i want... in my code it must display 8 diffenrent subject code but what i arrived is only one, 8 the same subjects code... and the record that is being displayed is not the one whose i my query HUM411T does not belong to the range of my query in my code now its different...

I hope you can give me some hints... i think am getting into it little by little than you for your patience.
Member
  
Join Date: Nov 2006
Posts: 72
#6: May 15 '07

re: data not being retrieved from Access database

Mr. Killer i have figure out whats wrong with my code:

to contribute heres my code

Expand|Select|Wrap|Line Numbers
  1.  
  2. If rs.State = adStateOpen Then rs.Close
  3.        rs.Open "Select * from [SUBJECTS] WHERE 'PrimaryID LIKE  ABA0101*'", cnn, adOpenKeyset, adLockOptimistic
  4.  
  5.   If rs.RecordCount > 0 Then
  6.         For x = 0 To 7
  7.         Text3(x).Text = rs.Fields.Item("SUBJECT CODES").Value
  8.         rs.MoveNext
  9.         Next x
  10.  
  11.  End If
  12.  
  13.  
i just place rs.MoveNext to my For Loop and uses .RecordCount to populate the records..

Again thank you so much for you help and patience i owe you a lot... Hail to you!!!
Moderator
  
Join Date: Oct 2006
Location: Australia
Posts: 7,748
#7: May 15 '07

re: data not being retrieved from Access database

It's good to see you progressing, but your SQL syntax is still wrong.

Here's your syntax, followed by the correction.

...[SUBJECTS] WHERE 'PrimaryID LIKE ABA0101*'" ...

...[SUBJECTS] WHERE PrimaryID LIKE 'ABA0101*'" ...
shidec's Avatar
Newbie
  
Join Date: May 2007
Posts: 27
#8: May 15 '07

re: data not being retrieved from Access database

Just a little explanation

"Select * from [SUBJECTS] WHERE 'PrimaryID LIKE ABA0101*'"
it will display all of your record


"Select * from [SUBJECTS] WHERE PrimaryID LIKE 'ABA0101*'"
it will display record that PrimaryID match the condition, that is 'ABA0101*'
Member
  
Join Date: Nov 2006
Posts: 72
#9: May 15 '07

re: data not being retrieved from Access database

Hi i still have problems:

1. the records that are display in my text boxes doesnt change.
2. the records in the text boxes are not being refresh.

Now my question is how will i able to refresh or clear the previous records that are been displayed on my text boxes.

its like this the first records that are in my primary id 'ABA0101*'", are 7 subject codes, and the other one which is 'ABA0102*'" have 9. But when i chose the 'ABA0102*'" records it just add up to the previous records that are been being display and view first. i hope you understand, coz am having difficulty in expalining the scenario..

to be more specific its like this:

'ABA0101*'" - has 7 records on it
'ABA0102*'" - has 9 records on it.

when i run it the records of ABA0101 is being displayed together with ABA0102 thats my problem... i want to eliminate that.

So How can i eliminate, refresh, clear or something like that to the records that are being populated or displayed in my textboxes when i want to view other records. Heres my code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 'SUBJECT CODE CONNECTION
  3.   If rs.State = 1 Then rs.Close
  4.   rs.Open "Select * from [SUBJECTS] WHERE 'PrimaryID LIKE  ABA0101**'", cnn, adOpenStatic, adLockOptimistic
  5.  
  6.   If rs.RecordCount < 0 Then
  7.             For x = 0 To 7
  8.         txtCode(x).Text = rs.Fields.Item("SUBJECT CODES").Value
  9.  
  10.         rs.MoveNext
  11.  
  12.         Next
  13.  
  14.         For x = 0 To 15
  15.         txtCode(x).Locked = True
  16.         txtSubjects(x).Locked = True
  17.         txtType(x).Locked = True
  18.         txtUnits(x).Locked = True
  19.         Next
  20.   End If
  21.  
  22. 'SUBJECT CODE CONNECTION
  23.  
  24. 'SUBJECT  CONNECTION
  25. If rs.State = adStateOpen Then rs.Close
  26.        rs.Open "Select * from [SUBJECTS] WHERE 'PrimaryID LIKE  ABA0101**'", cnn, adOpenKeyset, adLockOptimistic
  27.  
  28.   If rs.RecordCount > 0 Then
  29.  
  30.         For x = 0 To 7
  31.         txtSubjects(x).Text = rs.Fields.Item("SUBJECT").Value
  32.  
  33.         rs.MoveNext
  34.  
  35.         Next x
  36.  
  37.         For x = 0 To 15
  38.         txtCode(x).Locked = True
  39.         txtSubjects(x).Locked = True
  40.         txtType(x).Locked = True
  41.         txtUnits(x).Locked = True
  42.         Next x
  43.   End If
  44. 'SUBJECT CONNECTION
  45.  
  46. 'TYPE  CONNECTION
  47. If rs.State = adStateOpen Then rs.Close
  48.        rs.Open "Select * from [SUBJECTS] WHERE 'PrimaryID LIKE  ABA0101**'", cnn, adOpenKeyset, adLockOptimistic
  49.  
  50.   If rs.RecordCount > 0 Then
  51.  
  52.         For x = 0 To 7
  53.         txtType(x).Text = rs.Fields.Item("TYPE").Value
  54.  
  55.         rs.MoveNext
  56.  
  57.         Next x
  58.  
  59.         For x = 0 To 15
  60.         txtCode(x).Locked = True
  61.         txtSubjects(x).Locked = True
  62.         txtType(x).Locked = True
  63.         txtUnits(x).Locked = True
  64.         Next x
  65.   End If
  66. 'TYPE CONNECTION
  67.  
  68. 'UNITS  CONNECTION
  69. If rs.State = adStateOpen Then rs.Close
  70.        rs.Open "Select * from [SUBJECTS] WHERE 'PrimaryID LIKE  ABA0101**'", cnn, adOpenKeyset, adLockOptimistic
  71.  
  72.   If rs.RecordCount > 0 Then
  73.  
  74.         For x = 0 To 7
  75.         txtUnits(x).Text = rs.Fields.Item("UNITS").Value
  76.  
  77.         rs.MoveNext
  78.  
  79.         Next x
  80.  
  81.         For x = 0 To 15
  82.         txtCode(x).Locked = True
  83.         txtSubjects(x).Locked = True
  84.         txtType(x).Locked = True
  85.         txtUnits(x).Locked = True
  86.         Next x
  87.   End If
  88. 'UNITS CONNECTION
  89.  
  90.  
Thank you very much, hope you can help me, and i hope you can picture what am trying to say.
danp129's Avatar
Expert
  
Join Date: Jul 2006
Posts: 250
#10: May 15 '07

re: data not being retrieved from Access database

You need to fix your sql query before moving on. The query you are using most likely isn't filtering anything out. Read the last two posts from Killer42 and Shidec again.

It would be nice to know what exactly is in the "Subject Codes". If you open access and look at that field what do the first couple rows have in that field? Need to know exactly what it is, don't truncate it with a wild card when providing this sample.
Member
  
Join Date: Nov 2006
Posts: 72
#11: May 16 '07

re: data not being retrieved from Access database

Thank you for that advise, i have figure out what is wrong with my sql query. to contribute here's my final code:

Expand|Select|Wrap|Line Numbers
  1. If rs.State = adStateOpen Then rs.Close
  2.        rs.Open "Select * from [SUBJECTS] where ID like 'ABA0102%'", cnn, adOpenKeyset, adLockOptimistic
  3.   While rs.EOF <> True
  4.     For x = 0 To 15
  5.         txtCode(x).Text = rs.Fields("SUBJECT CODES").Value
  6.         On Error Resume Next
  7.         rs.MoveNext
  8.     Next x
  9.  
  10.   Wend
  11.  
The error is i need to enclose the condition with single quotes e.i 'ABA0102%', and place a % sign on the wildcard selection...

For the people who help and give some advise thank you very... hope you can help with my future programs. thank you very much, This site ROCKS! PEACE OUT!
Reply

« previous question | next question »

Similar Visual Basic 4 / 5 / 6 bytes

/bytes/development

/bytes/it

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,295 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved