473,241 Members | 1,579 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

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 1910
Killer42
8,435 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,435 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,435 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,435 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

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

Similar topics

5
by: democratix | last post by:
Hi, I've only got a couple years experience developing for Access but have recently been experimenting with HTML/javascript for gui and client-side scripting, mysql for database and php for...
4
by: Susan Lam | last post by:
I am looking for some papers/information that compare relational databases such as oracle, mysql, sql server etc. I am particularly interested in their features such as locking mechanisms,...
3
by: muntyanu | last post by:
Hi all, I have datetime field in database. When I am getting records older than specific date that I passed in parameter I can not get records that differs only in Time part of the datetime...
37
by: spam.noam | last post by:
Hello, Guido has decided, in python-dev, that in Py3K the id-based order comparisons will be dropped. This means that, for example, "{} < " will raise a TypeError instead of the current...
2
by: Mark | last post by:
Hi, I don't have a code problem. I have an understanding problem. This is a made up situation that mimics my real situation but it is easier to understand. 1. I call a web method with an...
10
by: Jim Devenish | last post by:
I have a split front end/back end system. However I create a number of local tables to carry out certain operations. There is a tendency for the front end to bloat so I have set 'compact on...
5
by: Justin Fancy | last post by:
Hi everyone, I need some help. I'm placing text files into a created database using vb.Net. The problem is that, i need two seperate sql statements to add both files because they are in...
2
by: Justin Fancy | last post by:
Hi everyone, I need some help. I'm placing text files into a created database using vb.Net. The problem is that, i need two seperate sql statements to add both files because they are in...
5
by: Dave | last post by:
I need to filter an Access 2000 result set in ASP 30 using the ADO recordset.filter. I build the filter in pieces. The first clause of the filter is this... WHERE word LIKE 'S%' ... to...
1
by: AllBeagle | last post by:
Hello Everyone, I'm trying to build a web-based application for a client of mine and I keep finding holes in my design, so I could use some guidance if anyone has any to offer. Let me try to...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.