One for the experts - SQL and VB

[Lol Owen]

Hi, got a bit of a headscratcher. In attached folder is a database and VB6 project. The connection is made through ADO and works fine. I have a problem with finding if a record already exists in tblDetails. I've set up a little test routine under Command Button 2 that assigns values to intProduct and inttransaction that already exist in tblDetails. A piece of SQL checks if the record exists and if it does calls an update subroutine else calls an addnewitem subroutine. The problem I am having is converting a positive identification that the record exists into say an integer that can be used in a new routine, such as If-Then. I have tried Select Count(*) As Num from an SQl point of view executed against the current connection. Num is returned as 0 even though the record exists. I have tried instead creating a recordset based upon the tblDetails table and using a Do-While loop with nested If-Then statement. Still no joy. I tried the recordcount method for the recordset, this reported a value of -1 even though the cursor type, lock type is set I believe correctly. Doing a de-bug print of the recordset, both the SQL generated one and the conventional one shows that the records are there and can be retrieved. So what gives

Please have a look and see if you can figure this one out. It's probably a no-brainer

Thanks, Lol

 

[RoyVidar]

This is an Access VBA forum, I'd quess some of the members here have VB available, but not all. Using ADO is quite the same whether it is used from VB or VBA, so I'd suggest posting the code you're using, some table description... then we can see what we can do, else, try the VB forum.

 

[Lol Owen]

Sory Roy, my bad. Here is the database and a notepad document with the module code in it.

Cheers, Lol

 

[RoyVidar]

I'll have a look if you post the code, or you can wait and see if someone downloads.

 

[Lol Owen]

Here we go!
Option Explicit
Public cnABOF As ADODB.Connection ' set connection
Public strConnection As String ' declare connection string
Public rsListData As ADODB.Recordset
Public rsDetails As ADODB.Recordset
Public rsTransaction As ADODB.Recordset
Public SQL As String ' holds SQL statement to execute against connection
Public Num As Integer ' result from query to find existing items in transaction
Public intProduct As Integer ' holds Product ID from command buttons
Public intUser As Integer ' holds UserID from login page
Public intTransaction As Double ' holds transaction number


Public Sub OpenConnection()
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "C:\Documents and Settings\Lol\Desktop\Project18th March\Database\French.mdb;" ' text for connection
Set cnABOF = New ADODB.Connection ' set new ADODB connection
cnABOF.Open strConnection ' open connection
End Sub

Public Sub CloseConnection()
Set rsDetails = Nothing
Set rsListData = Nothing
Set rsTransaction = Nothing
Set cnABOF = Nothing ' close connection
End Sub

Public Sub AddNewItem() ' adds new item to transaction


SQL = "Insert Into tblDetails (TransactionNumber,ProductID,Quantity) Values (" & intTransaction & "," & intProduct & ",1);"
cnABOF.Execute SQL


End Sub

Public Sub ItemCheck()

SQL = "Select Count(*) As Num From tblDetails Where ProductID=" & intProduct & " AND TransactionNumber=" & intTransaction & ";" ' finds if product already exists in transaction
cnABOF.Execute SQL
Debug.Print Num
Select Case Num
Case Is = "1"
Call Update
Case Is = "0"
Call AddNewItem

End Select

End Sub

Public Sub Update()
SQL = "Update tblDetails Set Quantity=Quantity+1 Where ProductID=" & intProduct & "AND TransactionNumber=" & intTransaction & ";"
cnABOF.Execute SQL

Lol

 

[RoyVidar]

When using the .execute method, you can either just do an execute, which applies to action queries or you can assign the result to a recordset, which applies to dynamic sql (and some more)

dim rs as adodb.recordset
SQL = "Select Count(*) As Num From tblDetails Where ProductID=" & _
      intProduct & " AND TransactionNumber=" & intTransaction
set rs = cnABOF.Execute(SQL,,adcmdtext)
Debug.Print rs.fields("Num").value

 

[Lol Owen]

Thanks Roy will give it a try and let you know!

Cheers, Lol

 

[Lol Owen]

Roy, your'e the man! It worked nicely thankyou. Didn't appreciate that you had to assign the result to a recordset to get to the value.

Thanks, Lol