468,306 Members | 1,212 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,306 developers. It's quick & easy.

Database Comparison

72
Hello vb masters, am having difficulty in comparing database through my visual basic program, i'am using access database and sql statement, i'am doing a time scheduling system, so one of the thing that my system must do is to trap the error of same schedule being save by the user .
heres my code for it:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdVerify_Click()
  2. Dim rs As New ADODB.Recordset
  3. Dim cnn As New ADODB.Connection
  4. Dim x As Integer
  5.  
  6. Set cnn = New ADODB.Connection
  7. cnn.ConnectionString = _
  8.  "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  9.           "Data Source=" & App.Path & "\dbTimeScheduling2.mdb;"
  10. cnn.Open
  11.  
  12. If Combo1(x).Text = "Select" Or Combo2(x).Text = "Select" Or Combo3(x).Text = "Select" Or Combo4(x).Text = "Select" Then
  13.         MsgBox ("Cannot Save blank entries"), vbCritical
  14.         Exit Sub
  15. End If
  16.  
  17.     Set rs = New ADODB.Recordset
  18.     Set rs.ActiveConnection = cnn
  19.  
  20.     For x = 0 To 15
  21. On Error Resume Next
  22. rs.Source = "Select * from RECORDS where [SUBJECT CODES] = ' " & txtCode(x).Text & " ' "
  23. rs.Open , cnn, adOpenStatic, adLockOptimistic
  24.  
  25.     If rs.Fields("TimeStart").Value = Format(Combo1(x).Text, "Medium Time") And rs.Fields("TimeEnd").Value = Format(Combo4(x).Text, "Medium Time") And rs.Fields("ROOM").Value = Combo2(x).Text And rs.Fields("DAYS").Value = Combo3(x).Text Then
  26.         MsgBox ("An error occured"), vbCritical
  27.         rs.Close
  28.         cnn.Close
  29.         Exit Sub
  30.     End If
  31.     Next
  32.  
  33.         For z = 0 To 15
  34.         If Not rs.Fields("TimeStart") = Format(Combo1(z).Text, "Medium Time") And rs.Fields("TimeEnd").Value = Format(Combo4(z).Text, "Medium Time") And rs.Fields("ROOM").Value = Combo2(z).Text And rs.Fields("DAYS").Value = Combo3(z).Text Then
  35.  
  36.         MsgBox ("Perfect"), vbOKOnly
  37.         cmdSave.Enabled = True
  38.  
  39.         End If
  40.         Next
  41.  
  42.  
  43.  
  44.  
  45.  
  46. End Sub
  47.  
now having difficulty in comparing the previous records in my table records that consists of TimeStart, TimeEnd, ROOM and DAYS field name.Both TimeStart and TimeEnd are in Date/Time format ("Medium Time") in Access...

i think i have some problems in this area:

Expand|Select|Wrap|Line Numbers
  1.   If rs.Fields("TimeStart").Value = Format(Combo1(x).Text, "Medium Time") And rs.Fields("TimeEnd").Value = Format(Combo4(x).Text, "Medium Time") And rs.Fields("ROOM").Value = Combo2(x).Text And rs.Fields("DAYS").Value = Combo3(x).Text Then
  2.         MsgBox ("An error occured"), vbCritical
  3.         rs.Close
  4.         cnn.Close
  5.         Exit Sub
  6.     End If
  7.     Next
  8.  
coz though my RECORD TABLE has some data on it, and the user inputed data to save in my RECORD TABLE, and its very equivalent to the records that are saved in my RECORD TABLE, so if the schedule is already in the RECORD table, my system must prompt the user that ("An error Occured") to prevent conflict or duplication of schedule.. Please can someone help me out with my problem, it been 1 week and still i cannot moveforward because of this..

Thank you very much...
May 29 '07 #1
7 1751
Killer42
8,434 Expert 8TB
Access doesn't store date/time fields in any particular fomat. It just stores the actual date/time information (as a number of some sort). Formatting is done when it displays the fields. In this case, you will be simply retrieving a date/time value, so you should be comparing it to another date/time value, not a formatted string.

In other words, the comparison which you have written as

If rs.Fields("TimeStart").Value = Format(Combo1(x).Text, "Medium Time")

is likely to be more like...

If rs.Fields("TimeStart").Value = CDate(Combo1(x).Text)
May 29 '07 #2
darrel
72
Thank you for that information. Now may question, is my code in testing if there an equal records in my the database are correct. Becuase it seems that it doesnt do anything at all, it like it doesnt check if there equal records in my table (RECORDS). PLease do check if my comparison statement are correct or if i'am missing some sort of code or details.

Thank you very much....
Here's my comparison statement code:

Expand|Select|Wrap|Line Numbers
  1. If rs.Fields("TimeStart").Value = Format(Combo1(x).Text, "Medium Time") And rs.Fields("TimeEnd").Value = Format(Combo4(x).Text, "Medium Time") And rs.Fields("ROOM").Value = Combo2(x).Text And rs.Fields("DAYS").Value = Combo3(x).Text Then
  2.         MsgBox ("An error occured"), vbCritical
  3.         rs.Close
  4.         cnn.Close
  5.         Exit Sub
  6.     End If
  7.     Next
  8.  
May 30 '07 #3
Killer42
8,434 Expert 8TB
I just answered that, in my last post. Check the bold parts toward the end.
May 30 '07 #4
darrel
72
Ive already tried it, but same thing... it always prompt that "An error occured" indicating that the schedule i have been trying to save it al ready in my database records, though the schedule that am trying to save is actually not in my database records. i hope u understand what am try to say, its like this is i have inputted in my TimeStart 7:00am and TimeEnd 8:00, Room 302 and Days Monday,,, if the schedule am trying to save is already in my database records my system must prompt me, "An error occured" if not "There no error", hope u you can help me with this.

I think my code is mess up and wrong thats why am getting the output or msg which "An error occured' though theres no error at all.
May 30 '07 #5
Killer42
8,434 Expert 8TB
If you have a comparison which isn't working, stop the execution at that point (with a breakpoint) and examine the values on either side of the comparison. That will tell you what's going on.

For instance, check the values of rs.Fields("TimeStart").Value and Combo1(x).Text to see whether they really do match.

Also, could you post the version that you say you tried? The one you've posted looks unlikely to work due to the date-format thing I mentioned.
May 30 '07 #6
darrel
72
How can i check the values of my field in my database and combo box to if they are really match...
I'am using VB 6 and Microsoft access 2000, and my .mdb i'am using in my program is also in 2000 format? thank you! lookinf forward for your reply.
May 30 '07 #7
Killer42
8,434 Expert 8TB
You really should read the documentation or look up a tutorial on the web. The debugging facilities built into VB6 are excellent. You can interrupt the code at any point, examine the values of variables and properties, change them, "step through" the code one statement at a time, and I forget what else.

To begin with, just move the cursor to the line which does the big IF test, and press the F9 key to put a breakpoint there. (You can also toggle breakpointts on and off by clicking in the grey area to the left of the code).

Run the program. When it hits that line, it should stop and display it. If you simply hover your mousepointer over a variable or property, a popup tooltip should show the current value. You can also do a number of things such as typing Print variable in the immediate window (press Ctrl-G to get there).
May 30 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by democratix | last post: by
4 posts views Thread by Susan Lam | last post: by
37 posts views Thread by spam.noam | last post: by
2 posts views Thread by Mark | last post: by
10 posts views Thread by Jim Devenish | last post: by
5 posts views Thread by Justin Fancy | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by Teichintx | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.