Recordset does not open with Table Variables

Hello!

I have made an stored procedure that receives 2 parameters and returns a resultset. The resultset is populated from a select made from a table variable declared on that procedure:

select * from @.MyTable

Now, the stored procedure works as expected when invoked on the Query Analizer; but when using a Visual Basic application that uses ADO 2.7, the Recordset object does not open.

What is wrong?

Thanks a lot in advance.Any error?|||Any error?|||Thanks for your reply.

The recordset object does not open; however, a runtime error does not occur.

The visual basic code is very straightforward and has been used with other kind of stored procedures:

Public Sub LoadData()
on error goto E:
Dim objConnection As ADODB.Connection
Dim objCommand As ADODB.Command
Dim objRecordset As ADODB.Recordset
Set objConnection = New ADODB.Connection
objConnection.CursorLocation = adUseClient
objConnection.ConnectionString = m_strConnectionString
objConnection.Open
Set objCommand = New ADODB.Command
Set objCommand.ActiveConnection = objConnection
objCommand.CommandType = adCmdStoredProc
objCommand.CommandText = "myStoredProcedure"
objCommand.Parameters("@.myParameter1").Value = m_varValue1
objCommand.Parameters("@.myParameter2").Value = m_varValue2

Set objRecordset = objCommand.Execute
If objCommand.Parameters(0).Value = 0 Then
do while not objRecordset.EOF
debug.print objRecordset!myField1
objRecordset.MoveNext
loop
End If
Exit sub
E:
MsgBox Err.Description

End Sub

I do not understand why the recordset is not opened. The only difference with other kind of stored procedures that I have used is that the SELECT statement is made from a Table variable:

select * from @.MyTable

The Query Analyzer returns values.

What could be wrong?|||When using Table variable or Temp tables, it is necesary to write "SET NOCOUNT ON" on the top of the stored procedure.

I read that in "PRB: Error Messaging Referencing #Temp Table with ADO-SQLOLEDB", a Microsoft Knowledge Base Article (235340).

http://support.microsoft.com/support/kb/articles/Q235/3/40.ASP

:-D

records with <NULL> aren't returned?

I have a table (TempTable) with one field (Field1)
and three records:
2
3
<NULL>
if I do " Select * from TempTable where Field1 <> '2' "
I only get returned :
3
why didn't the <NULL> record show up? Can anything be done to the
table properties without having to modify the query (by adding 'OR
Field1 IS NULL')
Thanks...No, Not as far as I know
SELECT * FROM TempTable
WHERE field1 <>1
OR field1IS NULL
or
SELECT * FROM TempTable
WHERE COALESCE(field1,0) <> 1
and take a look at these 2 queries
SELECT COUNT(*) FROM TempTable --3
SELECT count(field1) FROM TempTable --2
http://sqlservercode.blogspot.com/|||The reason for this is that both = null and <> null are undefined. SQL will
return false in both cases, as the convention for undefined.
For example, null = null is undefined as thus returns false.
There is no way that I know to change this behavior.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Eych" wrote:
> I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>|||The behavior you are seeing is the ANSI standard. It is recommended that
you use this, but if you need to turn it off , you can do so at the
connection level.
SET ANSI_NULLS OFF
--
Phil Lavene
"Eych" <eycheych@.hotmail.com> wrote in message
news:1137085275.394772.87080@.g47g2000cwa.googlegroups.com...
>I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>

records with <NULL> aren't returned?

I have a table (TempTable) with one field (Field1)
and three records:
2
3
<NULL>
if I do " Select * from TempTable where Field1 <> '2' "
I only get returned :
3
why didn't the <NULL> record show up? Can anything be done to the
table properties without having to modify the query (by adding 'OR
Field1 IS NULL')
Thanks...
No, Not as far as I know
SELECT * FROM TempTable
WHERE field1 <>1
OR field1IS NULL
or
SELECT * FROM TempTable
WHERE COALESCE(field1,0) <> 1
and take a look at these 2 queries
SELECT COUNT(*) FROM TempTable --3
SELECT count(field1) FROM TempTable --2
http://sqlservercode.blogspot.com/
|||The reason for this is that both = null and <> null are undefined. SQL will
return false in both cases, as the convention for undefined.
For example, null = null is undefined as thus returns false.
There is no way that I know to change this behavior.
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Eych" wrote:

> I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>
|||The behavior you are seeing is the ANSI standard. It is recommended that
you use this, but if you need to turn it off , you can do so at the
connection level.
SET ANSI_NULLS OFF
Phil Lavene
"Eych" <eycheych@.hotmail.com> wrote in message
news:1137085275.394772.87080@.g47g2000cwa.googlegro ups.com...
>I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>

records with <NULL> aren't returned?

I have a table (TempTable) with one field (Field1)
and three records:
2
3
<NULL>
if I do " Select * from TempTable where Field1 <> '2' "
I only get returned :
3
why didn't the <NULL> record show up? Can anything be done to the
table properties without having to modify the query (by adding 'OR
Field1 IS NULL')
Thanks...No, Not as far as I know
SELECT * FROM TempTable
WHERE field1 <>1
OR field1IS NULL
or
SELECT * FROM TempTable
WHERE COALESCE(field1,0) <> 1
and take a look at these 2 queries
SELECT COUNT(*) FROM TempTable --3
SELECT count(field1) FROM TempTable --2
http://sqlservercode.blogspot.com/|||The reason for this is that both = null and <> null are undefined. SQL will
return false in both cases, as the convention for undefined.
For example, null = null is undefined as thus returns false.
There is no way that I know to change this behavior.
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Eych" wrote:

> I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>|||The behavior you are seeing is the ANSI standard. It is recommended that
you use this, but if you need to turn it off , you can do so at the
connection level.
SET ANSI_NULLS OFF
Phil Lavene
"Eych" <eycheych@.hotmail.com> wrote in message
news:1137085275.394772.87080@.g47g2000cwa.googlegroups.com...
>I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>

Records number of a table

Dear Sir,

Instead of using SELECT count(*) From TABLE_NAME,

Is there any property of a Table that provide the total_records of this table?

Many Thanks

Xueliang

There's a quick way that returns a rowcount that is not necessarily 100% accurate, but that is good enough for most purposes, see below.

Chris

SELECT rows AS [RowCount]

FROM sysindexes

WHERE OBJECT_NAME(id) = '<Table Name>'

AND indid <= 1

|||

if u hv any mumeric fields ..u can chk out by diiference.. like auto id can sense this..or if product id p001 to p022 u can chk this too,

or the solutin by chris may work i hvnt tried..

|||

great job done sir..

sir, will it b common for any table?

will u plz explain syntax?

|||just wonder is there anything like row_counts_fuction(TABLE_NAME) ?

I think the SQL Server System should keep the total records number somewhere.

Thanks a lot

Xueliang|||

Chris Howarth rights. Statement in his answer doesn't count rows in table, but gets it from system table.

If you want function, you could create it:

create function row_counts_fuction(@.table_name varchar(50)) returns int

as

begin

declare @.result int

SELECT @.result = rows

FROM sysindexes

WHERE OBJECT_NAME(id) = @.table_name

AND indid <= 1

return @.result

end

Then:

select count(*) from Sales.SalesOrderDetail

--Returns 121317, works 0,21 sec

select dbo.row_counts_fuction('SalesOrderDetail')

--Returns 121317, works 0,00 sec

|||

>will u plz explain syntax?

Rather than me re-iterate what Microsoft say about sysindexes, let me point you towards the relevant topic in BOL - here you can see why the 'indid' column is included in the WHERE clause of the query.

http://msdn2.microsoft.com/en-us/library/ms190283.aspx

Chris

|||

If AUTO_CREATE_STATISTICS is not ON and not manually updated, then do I get Proper record

count from sysindexes ?

|||

To improve accuracy you should run DBCC UPDATEUSAGE before running the query.

See the following links for more info:

SQL Server 2000

http://msdn2.microsoft.com/en-gb/library/aa258283(sql.80).aspx

SQL Server 2005

http://msdn2.microsoft.com/en-us/library/ms188414.aspx

Apparently, according to MS, there should be no need to run the command in SQL Server 2005 as the statistics are maintained correctly.

Chris

Records NOT in OTHER table - SELECT statement

I am trying to compare 2 tables with similar data. One table has a field named [X Sent] and the other table has a field named [X Setup]. I was trying to extract all of the fields in Table 1 with field [X Sent] that are not yet in Table 2, i.e. field [X Setup]. I was trying some LEFT JOINS and other stuff but I can't seem to get the syntax correct. How can I get a query of only the fields that are not in Table 2, but are in Table 1?

Here are two of my attempts:

SELECT [X Sent], [X Date]
FROM [Table 1]
WHERE ([X Sent] NOT IN
(SELECT [Table 1].[X Sent]
FROM [Table 2] INNER JOIN
[Table 1] ON [Table 2].[X Setup]= [Table 1].[X Sent]))

I don't believe 'NOT IN' is proper syntax!

And my other attempt:

SELECT [Table 1].[X Sent]
FROM [Table 1] LEFT OUTER JOIN
[Table 2] ON [Table 2].[X Setup]= [Table 1].[X Sent]

Any help would greatly be appreciated.An outer join, checking for NULL in [Table 2] will do it.

SELECT [X Sent],[X Date] FROM
[Table 1] t1 LEFT OUTER JOIN [Table 2] t2 ON t1.[X Sent]=t2.[X Setup]
WHERE t2.[X Setup] IS NULL

|||Thanks once again Douglas Reilly!!!!

Greatly appreciative...|||After analyzing the data further, I have noticed that I can't only compare one field, but rather two from each table. I have been trying to work with the following statement, but I can't seem to get it to work. To start off with, I would like to capture all the fields where Field1 and Field2 from Table1 equals Field1 and Field2 of Table2.

SELECT [Field1], [Field2]
FROM Table1 t1 INNER JOIN
Table2 t2 ON t1.Field1 = t2.Field1 AND t1.Field2 = t2.Field2

Once I figure this part out, I will go ahead and search for all of these tuples not in the other table. Your assistance will greatly be appreciated.|||You should pick one of your candidate keys to be the primary key of Table1. Then you can do this:

select * from Table1
where Table1PrimaryKey not in
(select Table1PrimaryKey from Table1
inner join Table2 on Table1.Field1=Table2.Field1 and Table1.Field2=Table2.Field2)

If for some reason you really need the fields, you can use this:

select Table1.Field1, Table1.Field2
from Table1
inner join Table2 on Table1.Field1=Table2.Field1 and Table1.Field2=Table2.Field2

Records newer than 90 minutes (smalldatetime)

Hi,

I have a table in SQLServer2000 where e.g. the actual time is insert for
every record (smalldatetime). But how do I select every records newer
than 90 minutes?

/ChrisOn Mon, 08 Nov 2004 21:24:33 +0100, Chris wrote:

>Hi,
>I have a table in SQLServer2000 where e.g. the actual time is insert for
>every record (smalldatetime). But how do I select every records newer
>than 90 minutes?
>/Chris

Hi Chris,

SELECT Column1, Column2, ..., ColumnN
FROM MyTable
WHERE InsertionTime > DATEADD(minute, -90, CURRENT_TIMESTAMP)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Mon, 08 Nov 2004 21:24:33 +0100, Chris wrote:

> Hi,
> I have a table in SQLServer2000 where e.g. the actual time is insert for
> every record (smalldatetime). But how do I select every records newer
> than 90 minutes?
> /Chris

SELECT * FROM Tbl WHERE DateDiff(m,time_column,GetDate()) BETWEEN 0 AND 90

And you'd better have an index on your time_column.|||On Mon, 8 Nov 2004 16:45:45 -0500, Ross Presser wrote:

>On Mon, 08 Nov 2004 21:24:33 +0100, Chris wrote:
>> Hi,
>>
>> I have a table in SQLServer2000 where e.g. the actual time is insert for
>> every record (smalldatetime). But how do I select every records newer
>> than 90 minutes?
>>
>> /Chris
>SELECT * FROM Tbl WHERE DateDiff(m,time_column,GetDate()) BETWEEN 0 AND 90
>And you'd better have an index on your time_column.

Hi Ross,

As far as I know, that index won't be used in your query, since the
time_column is embedded in a function call. You'd have to have the
time_column all by itself at one side of a comparison operator (like in my
query) for the index to be useful.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in
news:kpuvo0l9uegi5iuute1h7rte13cp34dsh6@.4ax.com:

> On Mon, 8 Nov 2004 16:45:45 -0500, Ross Presser wrote:
>>On Mon, 08 Nov 2004 21:24:33 +0100, Chris wrote:
>>
>>> Hi,
>>>
>>> I have a table in SQLServer2000 where e.g. the actual time is insert
>>> for every record (smalldatetime). But how do I select every records
>>> newer than 90 minutes?
>>>
>>> /Chris
>>
>>SELECT * FROM Tbl WHERE DateDiff(m,time_column,GetDate()) BETWEEN 0
>>AND 90
>>
>>And you'd better have an index on your time_column.
> Hi Ross,
> As far as I know, that index won't be used in your query, since the
> time_column is embedded in a function call. You'd have to have the
> time_column all by itself at one side of a comparison operator (like
> in my query) for the index to be useful.
> Best, Hugo

Thank you for clarifying my fuzzy thinking, Hugo. Your query is much better
in that respect.

RecordCount with Datareader

I am very disappinted where Datareader have no RecordCount where I can get the total records it read. I guess I found a way:

sql = "SELECT *, ROW_NUMBER() OVER (ORDER BY id DESC) AS c FROM ACCOUNTS ORDER BY c DESC"
Dim command As SqlCommand = New SqlCommand(sql, New SqlConnection(_DBConnectionString))
command.Connection.Open()
_ReturnDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
command.Dispose()
_TotalRecord = _ReturnDataReader.GetInt64(_ReturnDataReader.GetOrdinal("c"))

Have SQL Server 2005 count for me...

Hi,

You can get the record count this way:

Dim

drAs SqlDataReader = AnyFunctionThatReturnsSqlDataReader()
Dim reccountAsInteger = 0
While dr.Read
reccount += 1
EndWhile

Good luck!

Wilmar, KSA (Mabuhay!)

RecordCount

I've got to count the rocrds of this query (SELECT DISTINCT lm.idlm, lm.descrizione FROM lm, grf, saf, cfc WHERE cfc.idlm = lm.idlm AND cfc.idsaf = saf.idsaf AND cfc.idgrf = grf.idgrf) but i can't use the function RecordCount of Visual Basic... how can I do?
Please Help ME!why can't you use the recordcount property? be more specific.|||in order to use recordcount, you have to use the proper (client-side)cursor. I dont remember what it is right off..
use an expensive one then work your way down ;)

recordcount

I've got to count the rocrds of this query (SELECT DISTINCT lm.idlm, lm.descrizione FROM lm, grf, saf, cfc WHERE cfc.idlm = lm.idlm AND cfc.idsaf = saf.idsaf AND cfc.idgrf = grf.idgrf) but i can't use the function RecordCount of Visual Basic... how can I do?
Please Help ME!Hello,

whats about

SELECT COUNT(DISTINCT lm.idlm, lm.descrizione)
FROM lm, grf, saf, cfc WHERE cfc.idlm = lm.idlm AND cfc.idsaf = saf.idsaf AND cfc.idgrf = grf.idgrf


?

Hope that helps ?

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

Record selection for latest dates (two fields)

Hi there.

I was wondering if anyone could help me here.

I need to select record details for the latest dates from two fields. The fields are:

Date1; and
Date2.

I tried to use the Maximum formula, but can not get it to work (i.e it wont select the latest date from each of the fields, and supress all other records):

{RCF03FormalAndUpcomingFormalReviews_RCF03.SttDat}= Maximum ({RCF03FormalAndUpcomingFormalReviews_RCF03.SttDat}) and
{RCF03FormalAndUpcomingFormalReviews_RCF03.QCF0612} = Maximum({RCF03FormalAndUpcomingFormalReviews_RCF03.QCF0612})

Any help would be great.

Cheers.

Mat.Not quite sure what you are asking, but if you want to select records for only the two most recent dates in the database, use a subroutine to select those dates, then "share" them with the main report.|||Try creating two formulas
@.maxrcf03 would be

Maximum ({RCF03FormalAndUpcomingFormalReviews_RCF03.SttDat})

@.MaxQCF would be

Maximum({RCF03FormalAndUpcomingFormalReviews_RCF03.QCF0612})

then use the formulas to return your maximum date for each of these fields.|||I tried that, but could not get it working.

Here is a sample of my dataset, where I need to return only information in row 1 (record 1):

Row 1. SPRING Cecil fred 31/05/2007 6/06/2007
Row 2. SPRING Cecil fred 1/06/2007 6/06/2007
Row 3. SPRING Cecil fred 18/06/2007 6/06/2007
Row 4. SPRING Cecil fred 15/06/2007 6/06/2007
Row 5. SPRING Cecil fred 19/06/2007 6/06/2007
Row 6. SPRING Cecil fred 25/06/2007 6/06/2007

The formulae overwrites the whole data range for all clients eg:

SPRING Cecil fred 31/05/2007 6/06/2007
SPRING Cecil fred 31/05/2007 6/06/2007
SPRING Cecil fred 31/05/2007 6/06/2007
SPRING Cecil fred 31/05/2007 6/06/2007
SPRING Cecil fred 31/05/2007 6/06/2007
SPRING Cecil fred 31/05/2007 6/06/2007
SPRING Cecil fred 31/05/2007 1/06/2007
WINTER Wayne 31/05/2007 4/06/2007
WINTER Wayne 31/05/2007 4/06/2007
WINTER Wayne 31/05/2007 4/06/2007

I'm not sure how to make the formulae record-specific i.e. so it only applies to Mr Spring, anmd not to Mr Winter.

Cheers and thanks for your help.

Mat.|||Group on the field that is SPRING, WINTER etc. I'll call it {table.id}

Add a GROUP selection formula of
{table.date1} = Maximum({table.date1}, {table.id})
and {table.date2} = Maximum({table.date2}, {table.id})

Note that this means that if a particular group does not have a record where *both* of the dates are the latest respective dates within the group then it will not show at all.

An alternative would be to group by {table.id}, sort descending on both date1 and date2 and then print in the group header (or sort ascending and print in the group footer).|||Thanks Jagan.

I'll give that a go tomorrow at work.

cheers.

Mat.