SQL Statement

[darrel]

Hello der people,, i just want to ask question. Is there a sql statement that uses compare... ei. Select * from [table name] where [field name] compare???

that can be implemented with visual basic 6 using adodb???


Thank you.

[Killer42]

As far as I know, if SQL can do it, you should be able to send it to the SQL database from VB.

[debasisdas]

Can u please specify what do u want to compare using SQL.

[Dököll]

Hello der people,, i just want to ask question. Is there a sql statement that uses compare... ei. Select * from [table name] where [field name] compare???

that can be implemented with visual basic 6 using adodb???


Thank you.

Just an idea darrel, things may have gotten sophisticate and I missed it, but I think VB may not know what access has, thus, comparing data from it against VB textboxes may no be that simple. Again, I have not researched this, someone has probably mastered it.

Long ago, I worked on a project for class and the need was to compare data in an Access database against my GUI. After banging my head for a while, and for the reason Access does/did not normally send over certain errors to VB when data not added, that sort of thing, I decided to simply write additional code to load the same data to an text file; mind you a .txt file can be compared with VB textboxes locally.

In turn, when the same data were being loaded to both the text file and my Access database at the same time, using one button, VB said no way: MsgBox "Duplicate entry, please try again", at the end of the code. Therefore, VB knew to disregard sending data anywhere, Unload Me, etc...

You may not have time to write additional code, but if you came up against a wall, I say go for it...

Hope this helps!

[darrel]

what i'am trying to comapre is, my database records made in access... it like this i have a table named RECORDS and has filed name "TimesStart", "TimeEnd", "ROOM", "DAYS", by the way i'am doing a scheduling system.

Now i have a GUI in vb6 thats consists of 4 combo boxes.
Combo1 is for the TimeStart
Combo2 is for the TimeEnd
Combo3 is for the ROOM
Combo4 is for the DAYS

Now, i have seperate table for TimeStart, TimeEnd, ROOM & DAYS. where they contain all the neccessary information. and its link to my GUI in vb

i uses this code to populate the datas in the tables of TimeStart, TimeEnd, ROOM and DAYS.by the all my combo boxes are in an array. and it works fine.

Combo1(x).additem  rs.fields("TimeStart").value
Combo2(x).additem  rs.fields("TimeEnd").value
Combo3(x).additem  rs.fields("ROOM").value
Combo4(x).additem  rs.fields("DAYS").value
 

Now what my system must do is to save, verify (if theres going to be a duplication), edit, and print.

I'am finished coding the saving part and its working now, i can save records in my database.

heres my code for it:

Dim rsShowRec As New ADODB.Recordset
Dim cnn As New ADODB.Connection
 
Set cnn = New ADODB.Connection
cnn.ConnectionString = _
 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & App.Path & "\dbTimeScheduling2.mdb;"
cnn.Open
 
For x = 0 To 15
On Error Resume Next
rsShowRec.Source = "Select * from RECORDS where [SUBJECT CODES] = ' " & txtCode(x).Text & " ' "
rsShowRec.Open , cnn, adOpenStatic, adLockOptimistic
 
    If Combo1(x).Text = "Select" Or Combo2(x).Text = "Select" Or Combo3(x).Text = "Select" Or Combo4(x).Text = "Select" Then
        MsgBox ("Cannot Save blank entries"), vbCritical
        rsShowRec.Close
        cnn.Close
        Exit Sub
    Else
 
    If rsShowRec.RecordCount > 0 Then
        Else
            rsShowRec.AddNew
                rsShowRec.Fields("SUBJECT CODES") = txtCode(x).Text
                rsShowRec.Fields("TimeStart") = Combo1(x).Text
                rsShowRec.Fields("TimeEnd") = (Combo2(x).Text)
                rsShowRec.Fields("ROOM") = Combo3(x).Text
                rsShowRec.Fields("DAYS") = Combo4(x).Text
            rsShowRec.Update
            rsShowRec.Requery
            Set rsShowRec = Nothing
    End If
    End If
    Next
 
    MsgBox ("Schedule has been save!!!")
End Sub
 

Now am into verification method, the coding part (the one am having trouble) the purpose of these verification method is to prevent duplication of schedule before saving its just like a test if there going to be a conflict.

and here my code for that method:

Private Sub cmdVerify_Click()
Dim rs As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim x As Integer
Dim strSQL As String
 
Set cnn = New ADODB.Connection
cnn.ConnectionString = _
 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & App.Path & "\dbTimeScheduling2.mdb;"
cnn.Open
 
For x = 0 To 15
On Error Resume Next
    rs.Source = "Select * from RECORDS where [TimeStart] = '" & Combo1(x).Text & "' and [TimeEnd] = '" & Combo2(x).Text & "' and [ROOM] = '" & Combo3(x).Text & "' and [DAYS] = '" & Combo1(x).Text & "'"
    rs.Open , cnn, adOpenKeyset, adLockOptimistic
 
If rs.RecordCount > 0 Then
    MsgBox ("Conflict on the previous schedule"), vbCritical
    Exit Sub
Else
 
    MsgBox ("You can now save the schedule"), vbOKOnly
    cmdSave.Enabled = True
End If
 
Next
 
End Sub
 

Now the scenario is like i have save a few records in the table RECORDS, with different value in TimeStart, TimeEnd, ROOM and DAYS. now i'am testing my verification method code but it seems its not working fine, because when created a schedule that has equal value store in my table RECORDS it must prompt me with an error "Conflict on the previous schedule" and if not it must prompt me "You can now save the schedule" and can save it... now the problem is eventhough ive inputted equal value in my GUI and try to verify if there an equal value in my database RECORDS table, my system prompt me that I CAN NOW SAVE THE SCHEDULE, evenethough it has an equal value in my database RECORDS. that i what am trying to figure out on how i can be able to compare the schedule that the user will be inputting from the schedule that are in my database already when they click my VERIFY button..

I hope you guys understand what i'am trying to say and do!!! i hope you can help me guys... by the its a proposed project in my work.. i'am working in a university that is not so automated in terms of there scheduling system, they are using manual system, thats why am trying to do an alternative to minimize the work and to avoid conflicts...

Thank you very much.

[darrel]

Guys good news i have figure out the answer to my problem, i can now compare records in my database... To contribute here's my code:

Private Sub cmdVerify_Click()
Dim rs As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim x As Integer
Dim strSQL As String
 
Set cnn = New ADODB.Connection
cnn.ConnectionString = _
 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & App.Path & "\dbTimeScheduling2.mdb;"
cnn.Open
 
For x = 0 To 15
On Error Resume Next
    rs.Source = "Select * from RECORDS where [TimeStart] = '" & Combo1(x).Text & "' and [TimeEnd] = '" & Combo4(x).Text & "' and [ROOM] = '" & Combo2(x).Text & "' and [DAYS] = '" & Combo3(x).Text & "'"
    rs.Open , cnn, adOpenKeyset, adLockOptimistic
 
If rs.RecordCount > 0 Then
    MsgBox ("There's a conflict in the schedule"), vbCritical
    rs.Close
    Exit Sub
 
Else
 
    MsgBox ("You can now save the schedule"), vbOKOnly
    cmdSave.Enabled = True
    Exit For
End If
 
Next
 
End Sub
 

Now i still have a few problems now what i want to do is after i prompt the user that theres a duplication of records with the schedule they are making, after that pop-up msg what i want to do is to highlight the schedule in my form to indicate that there's an conflict in that particular schedule,

Thank you very much for all those idea, and it helps me a lot!!! thank you very much!!! this forum rocks and helpful, i hope you continue supporting all newbies like me... hail to you all.

[Killer42]

...I think VB may not know what access has, thus, comparing data from it against VB textboxes may no be that simple.
...
additional code to load the same data to an text file; mind you a .txt file can be compared with VB textboxes locally.

I think you're trying to send darrel on a wild goose chase there, Dököll. If you can't trust your database system to faithfully record and return your data, then you might as well give up. That's exactly what the database is for, and you certainly don't need to store a redundant copy in a text file (though a backup of some sort is highly recommended, of course).

If a comparison between textboxes and database fields isn't working, it doesn't mean you can't compare the two. It just means the code is wrong.

[Dököll]

Guys good news i have figure out the answer to my problem, i can now compare records in my database...

Splendid darrel, good of you to remain persistant, furthermore, extremely nice of you to post your finding for all to see...

Good luck with this schedule!

[Dököll]

I think you're trying to send darrel on a wild goose chase there, Dököll. If you can't trust your database system to faithfully record and return your data, then you might as well give up. That's exactly what the database is for, and you certainly don't need to store a redundant copy in a text file (though a backup of some sort is highly recommended, of course).

If a comparison between textboxes and database fields isn't working, it doesn't mean you can't compare the two. It just means the code is wrong.

Bit of confusion in relaying the info, my database responded quite well, I searched, added, deleted, updated to it (Searching for data before submitting was my next option to darrel). What happened, and this is because I worked on this a very long time ago, is I could not quite pin down a compare method. So I went to work. Since I am familiar with text files, was able to found a solution; data was therefore neither recorded to the Text file nor the Access database...Nevetheless, I am rather glad our dear darrel did not have to do this as I cautioned it as a last step:-)

[darrel]

No problem with my friends! this forum helps me a lot by reading some old & new thread to get some ideas...

Hoping that youll continue guys with my future problem in my time scheduling system.