no records returned but should be

N

Newbie

Hi
I have a stored procedure in SQL that works perfectly if I run it through
SQL Query Analyser

However, if I call it from a form with the following code no records get
returned - even though there should be.

What am I doing wrong?

Variables are set in access on the OnClick event of btnOK as follows:
'these enter in declarations module
Public mYear As Variant
Public mMonth As Variant
Public mDateFrom As Date
Public mDateTo As Date
Public mStatus As Variant
Public pcfrom As Variant
Public mBranch As Variant

'this is behind the OnClick event
mDateFrom = datetimepick1.value
mDateTo = datetimepick2.value
mMonth = cbxMonth.value etc

All items seem to get a value.

I then call the stored procedure with the following
All appropriate DIMS are here. . .

Set cnMIS = New ADODB.Connection
Set cmdCoS = New ADODB.Command
Set rsRecords = New ADODB.Recordset

cnMIS.ConnectionString = "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=sa;pwd=mbl1175;Initial Catalog=MIS;Data Source=mycomp"
cnMIS.CursorLocation = adUseClient
cnMIS.Open

With cmdCoS


.ActiveConnection = cnMIS
.CommandText = "CostOfSales"
.CommandType = adCmdStoredProc
Set params = .Parameters
End With
params.Append cmdCoS.CreateParameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 0)
params.Append cmdCoS.CreateParameter("@DateTo", adDate, adParamInput)
params.Append cmdCoS.CreateParameter("@DateFrom", adDate, adParamInput)
'params.Append cmdCoS.CreateParameter("@Period", adChar, adParamInput, 10)
'params.Append cmdCoS.CreateParameter("@Month", adVarChar, adParamInput, 2)
'params.Append cmdCoS.CreateParameter("@Year", adVarChar, adParamInput, 4)
'params.Append cmdCoS.CreateParameter("@Branch", adChar, adParamInput, 2)
'params.Append cmdCoS.CreateParameter("@ProdClass", adChar, adParamInput, 4)

' Specify input parameter values
params("@DateTo") = mDateTo
params("@DateFrom") = mDateFrom
'params("@Period") = mStatus
'params("@Year") = mYear
'params("@Month") = mMonth
'params("@Branch") = mBranch
'params("@ProdClass") = pcfrom

Set rsRecords = cmdCoS.Execute
If rsRecords.RecordCount > 0 Then
MsgBox "Records"
Else
MsgBox "No Records"
End If

Here is an extract of the stored procedure

SELECT convert(char(10),T.InvoiceDate, 103) as InvDate,
T.Invoice,T.DetailLine, T.TrnYear,
T.TrnMonth,T.Branch, T.Customer, T.StockCode, T.ProductClass,
T.Warehouse, cast(T.QtyInvoiced as int) as Qty, T.SalesOrder,
T.NetSalesValue,
T.DocumentType, C.[Name]
FROM TrnDetail T INNER JOIN
Customer C ON T.Customer = C.Customer
WHERE (T.DocumentType = 'i' OR
T.DocumentType = 'C' OR
T.DocumentType = 'I') AND (T.Branch = coalesce(@Branch,
T.Branch)) AND (T.InvoiceDate >=coalesce(@DateFrom,T.InvoiceDate) AND
T.InvoiceDate <= coalesce(@DateTo,@DateFrom)) AND
(T.ProductClass like @ProdClass)
 
N

Newbie

More Info

If I send the query to SQL via a string = records are returned ok

what am I doing wrong?
eg
strSQL = "SELECT T.InvoiceDate, T.Invoice,T.DetailLine, T.TrnYear,"
strSQL = strSQL & " T.TrnMonth,T.Branch, T.Customer, T.StockCode,
T.ProductClass,"
strSQL = strSQL & " T.Warehouse, cast(T.QtyInvoiced as int) as Qty,
T.SalesOrder, T.NetSalesValue,"
strSQL = strSQL & " T.DocumentType , C.[Name]"
strSQL = strSQL & " FROM TrnDetail T INNER JOIN"
strSQL = strSQL & " Customer C ON T.Customer = C.Customer"
strSQL = strSQL & " WHERE (T.DocumentType = 'i' OR"
strSQL = strSQL & " T.DocumentType = 'C' OR T.DocumentType = 'I') AND"
strSQL = strSQL & " (T.Branch = 'A') AND"
strSQL = strSQL & " (T.InvoiceDate >= '" & Format(mDateFrom, "mm/dd/yyyy") &
"' AND"
strSQL = strSQL & " T.InvoiceDate <= '" & Format(mDateTo, "mm/dd/yyyy") &
"') AND"
strSQL = strSQL & " (T.ProductClass Like 'T%')"



Newbie said:
Hi
I have a stored procedure in SQL that works perfectly if I run it through
SQL Query Analyser

However, if I call it from a form with the following code no records get
returned - even though there should be.

What am I doing wrong?

Variables are set in access on the OnClick event of btnOK as follows:
'these enter in declarations module
Public mYear As Variant
Public mMonth As Variant
Public mDateFrom As Date
Public mDateTo As Date
Public mStatus As Variant
Public pcfrom As Variant
Public mBranch As Variant

'this is behind the OnClick event
mDateFrom = datetimepick1.value
mDateTo = datetimepick2.value
mMonth = cbxMonth.value etc

All items seem to get a value.

I then call the stored procedure with the following
All appropriate DIMS are here. . .

Set cnMIS = New ADODB.Connection
Set cmdCoS = New ADODB.Command
Set rsRecords = New ADODB.Recordset

cnMIS.ConnectionString = "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=sa;pwd=mbl1175;Initial Catalog=MIS;Data Source=mycomp"
cnMIS.CursorLocation = adUseClient
cnMIS.Open

With cmdCoS


.ActiveConnection = cnMIS
.CommandText = "CostOfSales"
.CommandType = adCmdStoredProc
Set params = .Parameters
End With
params.Append cmdCoS.CreateParameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 0)
params.Append cmdCoS.CreateParameter("@DateTo", adDate, adParamInput)
params.Append cmdCoS.CreateParameter("@DateFrom", adDate, adParamInput)
'params.Append cmdCoS.CreateParameter("@Period", adChar, adParamInput, 10)
'params.Append cmdCoS.CreateParameter("@Month", adVarChar, adParamInput, 2)
'params.Append cmdCoS.CreateParameter("@Year", adVarChar, adParamInput, 4)
'params.Append cmdCoS.CreateParameter("@Branch", adChar, adParamInput, 2)
'params.Append cmdCoS.CreateParameter("@ProdClass", adChar, adParamInput, 4)

' Specify input parameter values
params("@DateTo") = mDateTo
params("@DateFrom") = mDateFrom
'params("@Period") = mStatus
'params("@Year") = mYear
'params("@Month") = mMonth
'params("@Branch") = mBranch
'params("@ProdClass") = pcfrom

Set rsRecords = cmdCoS.Execute
If rsRecords.RecordCount > 0 Then
MsgBox "Records"
Else
MsgBox "No Records"
End If

Here is an extract of the stored procedure

SELECT convert(char(10),T.InvoiceDate, 103) as InvDate,
T.Invoice,T.DetailLine, T.TrnYear,
T.TrnMonth,T.Branch, T.Customer, T.StockCode, T.ProductClass,
T.Warehouse, cast(T.QtyInvoiced as int) as Qty, T.SalesOrder,
T.NetSalesValue,
T.DocumentType, C.[Name]
FROM TrnDetail T INNER JOIN
Customer C ON T.Customer = C.Customer
WHERE (T.DocumentType = 'i' OR
T.DocumentType = 'C' OR
T.DocumentType = 'I') AND (T.Branch = coalesce(@Branch,
T.Branch)) AND (T.InvoiceDate >=coalesce(@DateFrom,T.InvoiceDate) AND
T.InvoiceDate <= coalesce(@DateTo,@DateFrom)) AND
(T.ProductClass like @ProdClass)
Click to expand...
 
B

Ben Taylor

don't bother using commands, they're crap


Newbie said:
Hi
I have a stored procedure in SQL that works perfectly if I run it through
SQL Query Analyser

However, if I call it from a form with the following code no records get
returned - even though there should be.

What am I doing wrong?

Variables are set in access on the OnClick event of btnOK as follows:
'these enter in declarations module
Public mYear As Variant
Public mMonth As Variant
Public mDateFrom As Date
Public mDateTo As Date
Public mStatus As Variant
Public pcfrom As Variant
Public mBranch As Variant

'this is behind the OnClick event
mDateFrom = datetimepick1.value
mDateTo = datetimepick2.value
mMonth = cbxMonth.value etc

All items seem to get a value.

I then call the stored procedure with the following
All appropriate DIMS are here. . .

Set cnMIS = New ADODB.Connection
Set cmdCoS = New ADODB.Command
Set rsRecords = New ADODB.Recordset

cnMIS.ConnectionString = "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=sa;pwd=mbl1175;Initial Catalog=MIS;Data Source=mycomp"
cnMIS.CursorLocation = adUseClient
cnMIS.Open

With cmdCoS


.ActiveConnection = cnMIS
.CommandText = "CostOfSales"
.CommandType = adCmdStoredProc
Set params = .Parameters
End With
params.Append cmdCoS.CreateParameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 0)
params.Append cmdCoS.CreateParameter("@DateTo", adDate, adParamInput)
params.Append cmdCoS.CreateParameter("@DateFrom", adDate, adParamInput)
'params.Append cmdCoS.CreateParameter("@Period", adChar, adParamInput, 10)
'params.Append cmdCoS.CreateParameter("@Month", adVarChar, adParamInput, 2)
'params.Append cmdCoS.CreateParameter("@Year", adVarChar, adParamInput, 4)
'params.Append cmdCoS.CreateParameter("@Branch", adChar, adParamInput, 2)
'params.Append cmdCoS.CreateParameter("@ProdClass", adChar, adParamInput, 4)

' Specify input parameter values
params("@DateTo") = mDateTo
params("@DateFrom") = mDateFrom
'params("@Period") = mStatus
'params("@Year") = mYear
'params("@Month") = mMonth
'params("@Branch") = mBranch
'params("@ProdClass") = pcfrom

Set rsRecords = cmdCoS.Execute
If rsRecords.RecordCount > 0 Then
MsgBox "Records"
Else
MsgBox "No Records"
End If

Here is an extract of the stored procedure

SELECT convert(char(10),T.InvoiceDate, 103) as InvDate,
T.Invoice,T.DetailLine, T.TrnYear,
T.TrnMonth,T.Branch, T.Customer, T.StockCode, T.ProductClass,
T.Warehouse, cast(T.QtyInvoiced as int) as Qty, T.SalesOrder,
T.NetSalesValue,
T.DocumentType, C.[Name]
FROM TrnDetail T INNER JOIN
Customer C ON T.Customer = C.Customer
WHERE (T.DocumentType = 'i' OR
T.DocumentType = 'C' OR
T.DocumentType = 'I') AND (T.Branch = coalesce(@Branch,
T.Branch)) AND (T.InvoiceDate >=coalesce(@DateFrom,T.InvoiceDate) AND
T.InvoiceDate <= coalesce(@DateTo,@DateFrom)) AND
(T.ProductClass like @ProdClass)
Click to expand...
 
E

Eduardo A. Morcillo [MS MVP]

don't bother using commands, they're crap

Why? How do you call a SP that returns a value (one with an OUTPUT
parameter)? How you do call a parameterized SQL sentence?
 
A

Al Reid

Perhaps it's just a result of providing a simplified example, but your SP snippet includes @Branch but there is no Parameter
appended to the Command object for Branch.

Also, is rsRecords.EOF=True or are you relying on the RecordCount property. The RecordCount property can not be relied on under all
circumstances.

--
Al Reid

"It ain't what you don't know that gets you into trouble. It's what you know
for sure that just ain't so." --- Mark Twain

Newbie said:
Hi
I have a stored procedure in SQL that works perfectly if I run it through
SQL Query Analyser

However, if I call it from a form with the following code no records get
returned - even though there should be.

What am I doing wrong?

Variables are set in access on the OnClick event of btnOK as follows:
'these enter in declarations module
Public mYear As Variant
Public mMonth As Variant
Public mDateFrom As Date
Public mDateTo As Date
Public mStatus As Variant
Public pcfrom As Variant
Public mBranch As Variant

'this is behind the OnClick event
mDateFrom = datetimepick1.value
mDateTo = datetimepick2.value
mMonth = cbxMonth.value etc

All items seem to get a value.

I then call the stored procedure with the following
All appropriate DIMS are here. . .

Set cnMIS = New ADODB.Connection
Set cmdCoS = New ADODB.Command
Set rsRecords = New ADODB.Recordset

cnMIS.ConnectionString = "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=sa;pwd=mbl1175;Initial Catalog=MIS;Data Source=mycomp"
cnMIS.CursorLocation = adUseClient
cnMIS.Open

With cmdCoS


.ActiveConnection = cnMIS
.CommandText = "CostOfSales"
.CommandType = adCmdStoredProc
Set params = .Parameters
End With
params.Append cmdCoS.CreateParameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 0)
params.Append cmdCoS.CreateParameter("@DateTo", adDate, adParamInput)
params.Append cmdCoS.CreateParameter("@DateFrom", adDate, adParamInput)
'params.Append cmdCoS.CreateParameter("@Period", adChar, adParamInput, 10)
'params.Append cmdCoS.CreateParameter("@Month", adVarChar, adParamInput, 2)
'params.Append cmdCoS.CreateParameter("@Year", adVarChar, adParamInput, 4)
'params.Append cmdCoS.CreateParameter("@Branch", adChar, adParamInput, 2)
'params.Append cmdCoS.CreateParameter("@ProdClass", adChar, adParamInput, 4)

' Specify input parameter values
params("@DateTo") = mDateTo
params("@DateFrom") = mDateFrom
'params("@Period") = mStatus
'params("@Year") = mYear
'params("@Month") = mMonth
'params("@Branch") = mBranch
'params("@ProdClass") = pcfrom

Set rsRecords = cmdCoS.Execute
If rsRecords.RecordCount > 0 Then
MsgBox "Records"
Else
MsgBox "No Records"
End If

Here is an extract of the stored procedure

SELECT convert(char(10),T.InvoiceDate, 103) as InvDate,
T.Invoice,T.DetailLine, T.TrnYear,
T.TrnMonth,T.Branch, T.Customer, T.StockCode, T.ProductClass,
T.Warehouse, cast(T.QtyInvoiced as int) as Qty, T.SalesOrder,
T.NetSalesValue,
T.DocumentType, C.[Name]
FROM TrnDetail T INNER JOIN
Customer C ON T.Customer = C.Customer
WHERE (T.DocumentType = 'i' OR
T.DocumentType = 'C' OR
T.DocumentType = 'I') AND (T.Branch = coalesce(@Branch,
T.Branch)) AND (T.InvoiceDate >=coalesce(@DateFrom,T.InvoiceDate) AND
T.InvoiceDate <= coalesce(@DateTo,@DateFrom)) AND
(T.ProductClass like @ProdClass)
Click to expand...
 
Top