Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA excel - problem with having clause in sql with adodb.connection/recordset
Hi i need a help on the following error i am getting error in VBA excel when i am using having clause in the sql. i used adodb.connection and adodb.recordset. my problem is, i need to check duplicates in the oracle table. so i used the sql statement as follows select count(*) from table-1 having count(*) 1 group by field-1,field-2,field-3. and string the count in a variable. var-1 = rs.fields(0). when i execute that i am getting error as runtime error : 3021 and here is the description " Either BOF or EOF is True, or the current record has been deleted. Requested operation requires current record" here is my code strsql = "select name,product,product_number,begin_date,end_date from product_lisence_info" _ & " having count(*) 1 " _ & " group by name,product,product_number,begin_date,end_date " rs1.Open strsql, con1, adOpenKeyset dupli = rs1.Fields(0) rs1.close thanks Kiran -- ukp9999 ------------------------------------------------------------------------ ukp9999's Profile: http://www.excelforum.com/member.php...o&userid=28853 View this thread: http://www.excelforum.com/showthread...hreadid=486019 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA excel - problem with having clause in sql with adodb.connectio
Hi Kiran
The having clause should be at the end Select ... From ... Group By ... Having ... Try reorganising your query so that it is this way around. Not sure about your error though, souunds like there may be something else going on as well but that should be a start. Thanks Barry "ukp9999" wrote: Hi i need a help on the following error i am getting error in VBA excel when i am using having clause in the sql. i used adodb.connection and adodb.recordset. my problem is, i need to check duplicates in the oracle table. so i used the sql statement as follows select count(*) from table-1 having count(*) 1 group by field-1,field-2,field-3. and string the count in a variable. var-1 = rs.fields(0). when i execute that i am getting error as runtime error : 3021 and here is the description " Either BOF or EOF is True, or the current record has been deleted. Requested operation requires current record" here is my code strsql = "select name,product,product_number,begin_date,end_date from product_lisence_info" _ & " having count(*) 1 " _ & " group by name,product,product_number,begin_date,end_date " rs1.Open strsql, con1, adOpenKeyset dupli = rs1.Fields(0) rs1.close thanks Kiran -- ukp9999 ------------------------------------------------------------------------ ukp9999's Profile: http://www.excelforum.com/member.php...o&userid=28853 View this thread: http://www.excelforum.com/showthread...hreadid=486019 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA excel - problem with having clause in sql with adodb.connection/recordset
Barry, i tried but getting the same error. can we use have clause in adodb object? thanks Kira -- ukp999 ----------------------------------------------------------------------- ukp9999's Profile: http://www.excelforum.com/member.php...fo&userid=2885 View this thread: http://www.excelforum.com/showthread.php?threadid=48601 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA excel - problem with having clause in sql with adodb.conne
Hi Kiran
ADO doesn't care what the SQL string is that you send through to the database. It is just passing that to the back end and then returning the results to you. So yes you can use a having statement in your query as long as Oracle supports it. As Oracle supports standard SQL then you're OK on that front. I would guess that the error is telling you that no records are being returned. This is probably down to one of two things: 1. Your query is valid and there are no duplicates in the table. , or 2. Your query is invalid and hence returning no records. Are you sure there are duplicates in the database table? If there are dupes, can you test your query string against the database in some other way? I would suggest code something like the following (Note this is targetted at the Northwind database in Access): Sub test() Const CONN As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;Persist Security Info=False" Dim objRS As ADODB.Recordset Dim objConn As ADODB.Connection Dim strSQL As String Dim lngRecordCount As Long Set objConn = New ADODB.Connection Set objRS = New ADODB.Recordset 'open a connection explicitly objConn.Open CONN 'build the SQL string strSQL = "SELECT [Order Details].OrderID " & _ "FROM [Order Details] " & _ "GROUP BY [Order Details].OrderID " & _ "HAVING Count([Order Details].OrderID)1" 'open the recordset objRS.Open strSQL, CONN, adOpenKeyset 'check for errors If objConn.Errors.Count = 0 Then 'no errors 'check for returned records which have duplicate order ID's lngRecordCount = objRS.RecordCount If lngRecordCount 0 Then MsgBox lngRecordCount & " duplicates found!" Else MsgBox "No duplicate records found!" End If Else 'SQL Errors returned, check the errors collection 'Note: Should loop through the errors collection to pickup all errors MsgBox "There were errors." & vbLf & "Error 1: " & objConn.Errors(0).Description End If End Sub HTH Barry "ukp9999" wrote: Barry, i tried but getting the same error. can we use have clause in adodb object? thanks Kiran -- ukp9999 ------------------------------------------------------------------------ ukp9999's Profile: http://www.excelforum.com/member.php...o&userid=28853 View this thread: http://www.excelforum.com/showthread...hreadid=486019 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ADODB Connection Problem | Excel Programming | |||
problem using isnull() to check a value in ADODB.Recordset | Excel Programming | |||
share adodb connection in excel | Excel Programming | |||
ADODB Recordset problem | Excel Programming | |||
adodb.recordset with excel | Excel Programming |