SELECT statement

Sammy31
04-19-2004, 01:39 PM
I have the following code

If conn.Execute("SELECT * FROM [sheet1$] WHERE EmployeeNo = " & parsed("EmpNum") & " And DepartmentNo = " & parsed("Dept") & "").RecordCount > 0 Then

...code

basically what I want is, if the recordcount from the select statement is greater than 0 then that means the record exists then peform some other operation. But I get the error "Data type mismatch in criteria expression"

Any ideas to why?

MKoslof
04-19-2004, 01:54 PM
Looking into HAVING COUNT > 0...if you need to make sure you data set returns records, you want to use the HAVING COUNT method within your SQL Statement.

Sammy31
04-19-2004, 02:05 PM
Can you give me an example using HAVING COUNT.

Looking into HAVING COUNT > 0...if you need to make sure you data set returns records, you want to use the HAVING COUNT method within your SQL Statement.

MKoslof
04-19-2004, 02:11 PM
Well, first off lets understand the point of this. Typically having count is used to return a recordset that has no duplicates or some other condition. You should use a unique field, and do a group by. So, on your table, do you have a unique field? It would look something like this:



"SELECT my_ID, count(*) FROM myTable GROUP BY my_ID HAVING COUNT(*) > 0"

HardCode
04-19-2004, 02:13 PM
You are trying to return a set of records (RecordSet) to a Connection object, which you cannot do. Dim an ADODB.Recordset object and return the query results to that:


Dim RS As ADODB.RecordSet
Dim SQL As String

Set RS = New ADODB.RecordSet
SQL = "<your SQL here>"
RS.Open SQL, conn, adOpenStatic, adLockReadOnly

Debug.Print RS.RecordCount

If RS.RecordCount > 0 then
' blah blah blah
End If

MKoslof
04-19-2004, 02:15 PM
That is correct, but you CAN use the HAVING COUNT method within your SQL statement for certain conditions. The recordcount method is using to return the current recordcount of the query or command string opened. HAVING COUNT will restrict the query results.

HardCode
04-19-2004, 02:17 PM
I was tying the reply before all this Having business LOL :p

Sammy31
04-19-2004, 02:20 PM
Ok, let me explain what i'm trying to do.

If conn.Execute("SELECT * FROM [sheet1$] WHERE EmployeeNo = " & parsed("EmpNum") & " And DepartmentNo = " & parsed("Dept") & "").RecordCount > 0 Then
....code

I want to check if the record exist, if it does then i will insert an row. there might be a better way to check if the record exist in the conn connection.



You are trying to return a set of records (RecordSet) to a Connection object, which you cannot do. Dim an ADODB.Recordset object and return the query results to that:


Dim RS As ADODB.RecordSet
Dim SQL As String

Set RS = New ADODB.RecordSet
SQL = "<your SQL here>"
RS.Open SQL, conn, adOpenStatic, adLockReadOnly

Debug.Print RS.RecordCount

If RS.RecordCount > 0 then
' blah blah blah
End If

MKoslof
04-19-2004, 02:23 PM
OK, a simplier method:



Dim sSQL as string

'your sql statement goes here
sSQL = "SELECT COUNT(*) as total_Count FROM [Sheet1$] WHERE EmployeeNo...."

'assume rs a valid recordset object,and cn is a valid, opened connection
rs.Open sSQL, cn, adOpenkeyset, adLockOptimistic, adcmdText

If rs.fields("Total_Count").value > 0 Then
msgbox "this value already exists"
Exit sub
Else
'do something
End If



....code

Sammy31
04-19-2004, 02:36 PM
I tried your code. I have the following


ssql = "SELECT COUNT(*) as total_count FROM [sheet1$] WHERE EmployeeNo = " & parsed("EmpNum") & " And DepartmentNo = " & parsed("Dept") & ""
rec.Open ssql, conn, adOpenKeyset, adLockOptimistic, adCmdText



If rec.Fields("total_count").Value > 0 Then
conn.Execute "INSERT INTO [sheet1$] (EmployeeNo, DepartmentNo,JobNo, Method, TaxFreq, CheckCount, REGULAR) VALUES('" & parsed("EmpNum") & "', '" & parsed("Dept") & "', '" & "9079" & "', '" & "DIR DEP" & "', '" & "WEEKLY" & "', '" & "2" & "', '" & FormatNumber(parsed("Amount") / 100, 2, vbFalse, vbFalse, vbFalse) & "')", affected
End If

but i get a "Object variable or with block varialbe not set"

OK, a simplier method:



Dim sSQL as string

'your sql statement goes here
sSQL = "SELECT COUNT(*) as total_Count FROM [Sheet1$] WHERE EmployeeNo...."

'assume rs a valid recordset object,and cn is a valid, opened connection
rs.Open sSQL, cn, adOpenkeyset, adLockOptimistic, adcmdText

If rs.fields("Total_Count").value > 0 Then
msgbox "this value already exists"
Exit sub
Else
'do something
End If



....code

MKoslof
04-19-2004, 02:45 PM
Are all of your objects declared properly?



Dim rs as ADODB.recordset
Dim cn as ADODB.connection

Set cn = New ADODB.connection
Set rs = New ADODB.recordset



Are your "parsed" values being returned correctly? If you run this code in debug mode, do these variables have values?

Sammy31
04-19-2004, 03:54 PM
yep everything is declared properly

Are all of your objects declared properly?



Dim rs as ADODB.recordset
Dim cn as ADODB.connection

Set cn = New ADODB.connection
Set rs = New ADODB.recordset



Are your "parsed" values being returned correctly? If you run this code in debug mode, do these variables have values?

MKoslof
04-19-2004, 03:59 PM
I believe the error is in your conn.execute command. But first:

1) I believe (though not sure) you want to run this execute command if the value IS zero. If this record already exists, you don't fire your execute command (> 0). If total_Count is = 0, then run it.

One step at a time...does this work:



Dim sSQL As String

'your sql statement goes here
sSQL = "SELECT COUNT(*) as total_Count FROM [Sheet1$] WHERE EmployeeNo...."

'assume rs a valid recordset object,and cn is a valid, opened connection
rs.Open sSQL, cn, adOpenkeyset, adLockOptimistic, adcmdText

If rs.fields("Total_Count").value > 0 Then
msgbox "this value already exists"
Else
msgbox "no values present"
End If

'just back out for now

Exit Sub



Does this piece work?

Sammy31
04-19-2004, 04:23 PM
Well, if the value > 0 the that means the record exist so I want to run the execute method. I tried your code, but it stills gives me the same error


I believe the error is in your conn.execute command. But first:

1) I believe (though not sure) you want to run this execute command if the value IS zero. If this record already exists, you don't fire your execute command (> 0). If total_Count is = 0, then run it.

One step at a time...does this work:



Dim sSQL As String

'your sql statement goes here
sSQL = "SELECT COUNT(*) as total_Count FROM [Sheet1$] WHERE EmployeeNo...."

'assume rs a valid recordset object,and cn is a valid, opened connection
rs.Open sSQL, cn, adOpenkeyset, adLockOptimistic, adcmdText

If rs.fields("Total_Count").value > 0 Then
msgbox "this value already exists"
Else
msgbox "no values present"
End If

'just back out for now

Exit Sub



Does this piece work?

MKoslof
04-19-2004, 04:25 PM
If you run the code in debug mode, where does it die? Does it die on the recordset open line? Or after? I assume you can do a SELECT COUNT() on an Excel spreadsheet.

Sammy31
04-19-2004, 04:28 PM
it dies at that line

rec.Open ssql, conn, adOpenKeyset, adLockOptimistic

MKoslof
04-19-2004, 07:46 PM
Ok, I just ran a test. This produced a "total_Count" of 8, which is correct. See the code below: connection to Excel and returning the recordcount of the query:



Public Sub excelTest()
Dim cn As ADODB.Connection
Dim strQuery As String
Dim rs As ADODB.Recordset


Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset


With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\MyWorkbook.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With

strQuery = "SELECT COUNT(*) as total_Count FROM [Sheet1$]"

rs.Open strQuery, cn, adOpenStatic, adLockOptimistic, adCmdText

MsgBox rs.Fields("total_Count").Value

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum