|
Hi,
I'm trying to use the SqlDataAdapter, but I need to know how many rows the SqlDataAdapter had. There is some way to know more easy that:
<br />
SqlDataAdapter datareader;<br />
int i = 0;<br />
while(dataReader.Read()){<br />
i++;<br />
}<br />
Thanks
----
hxxbin
|
|
|
|
|
I'm not sure there is way to get the count of rows on a sqlDataAdapter.
The sqlDataAdapter.Fill method fills a Dataset or a DataTable. Once the dataset or table is populated you can get the row count from the
Dataset.Tables(0).Rows.Count or DataTable.Rows.Count method.
Dim ds As Dataset
sqlDA.SelectCommand.Connection = cn
sqlDA.Fill(ds)
sqlDA.SelectCommand.Connection = Nothing
Return dataset
With the dataset or DataTable populated you can obtain the rowcount by doing the following:
ds.Tables(0).Rows.Count
dt.Rows.Count
Michael
|
|
|
|
|
I think you meant SqlDataReader (the rest of the code looks like that).
There's no way to find out in advance how many rows are going to be retrieved. The SqlDataReader won't allow you to rewind anyway.
If you need to store the data temporarily (maybe you need to do multiple passes over it?), consider a DataTable. If that has too much overhead for you, use some automatically-growing data structure such as an ArrayList - you can always use the ArrayList's CopyTo method to copy it to an array once you've retrieved all the data.
|
|
|
|
|
Yeah, I meant SqlDataReader, sorry. And I going change my code to SqlDataAdapter.
Thanks. 
----
hxxbin
|
|
|
|
|
Hello!
I'm in the process of porting a DAO application to ADO and I'm a bit stuck.
In DAO I can get the name of the database using CDaoDatabase::GetName().
How do I do this in ADO?
Anders Eriksson
Sonork 100.21825
|
|
|
|
|
Hi, I know this is mainly for SQL, but I am hoping someone can help. I have the following problem, which I need to find a solution for. I have a table with unique transaction ids, customer ids, payment dates and payment amounts. I need to find out what someones last pay date was. I cant seem to find out how to group by the customer ID, showing only their most recent pay date. That would get me to solve the problem. Just remember that each customer can have multiple transactions.
If you can help me, please do. I have no idea how to do this query. I have been trying, but have not been succesful.
|
|
|
|
|
Without knowing your tables fields etc:
Select * from table a
where a.date = (select max(b.date) from table b where a.cust_id = b.cust_id)
Hope this helps.
Grtz, Guus
|
|
|
|
|
Hi, just to clarify all the data is in one table. Would this still work?
|
|
|
|
|
Just a swag:
Fetch the recordset with:
SELECT * FROM tablename WHERE custID = customernumber ORDER BY transdate
then use MoveLast to find the last payment by the selected customer.
Heard in Bullhead City - "You haven't lost your girl -
you've just lost your turn..." [sigh] So true...
|
|
|
|
|
What's wrong with this?
SELECT MAX(PayDate)
FROM Table1
GROUP BY CustomerID Untested, but my reading of the Access (2003) help file indicates it should be supported. Access 2003 uses the same Jet 4.0 engine and query processor as Access 2000 and XP.
|
|
|
|
|
How do I view the contents of a .mdb file? I've searched all over, and the only program I found was shareware and just listed the field names, not their values. I'm brand-new to database programming, so please share even the most basic advice. If it helps, I'm trying to access Adobe Album 2's database file that stores the locations and tags of photos; the file ends in .psa, but I was told by a developer at Adobe that it's just a simple Access database (which it appears to be).
|
|
|
|
|
Can't you open it with Access? 
You can view the Bitmap fields by Paint from Access, otherwise simply write an application that read image fields and show each picture there. (You said you know the name of fileds)
Mazy
No sig. available now.
|
|
|
|
|
The problem is, I don't have Access (the Academic version of Office 2003 doesn't include Access), and I don't think the shareware app listed all the fields. The database file is only ~300kb for ~50 photos, so I think it just has filenames and metadata.
|
|
|
|
|
Use VS.NET. Server Explorer.
Free your mind...
|
|
|
|
|
Thanks, that works great 
|
|
|
|
|
Have you tried Microsofts free Access viewer?
|
|
|
|
|
That was the first thing I looked for, but I couldn't find one - I only found an Access snapshot viewer, which only opened *.snp files.
|
|
|
|
|
Well just try this one: I open my .mdb file with Excel and it works,at least you can see name of fields and table and value of non-image fields.Then with the name of them you can write your own application.
Mazy
No sig. available now.
|
|
|
|
|
I've suddenly started getting this error in an application -
ConnectionTransact (TransactNamedPipe()).
Connection broken.
State:01000,Native:6,Origin:[Microsoft][ODBC SQL Server Driver][Named Pipes]
State:08S01,Native:4,Origin:[Microsoft][ODBC SQL Server Driver][Named Pipes]
The odd thing is that it's not a named pipes connection - it's using tcp/ip. I'm using SQL Server 2000 with a 1-processor licence; the only references I can find to this error suggest the number of licences may be too small, but this shouldn't be the problem here. Anyone any ideas?
Keith Worden
|
|
|
|
|
Sure it's using TCP/IP?
About the only way to make sure is to configure the server only to accept TCP/IP.
If you want to check at the client end, run cliconfg.exe and remove Named Pipes from the Enabled Protocols By Order list. Alternatively you could just ensure that TCP/IP appears first.
|
|
|
|
|
TCP/IP or named pipes is actually a side issue. I've gone back to the previous version of the program and it's running fine, so it's something I've done but the sql error is meaningless to me!
|
|
|
|
|
OK, it looks like this is actually an error from the network API or from Windows, since SQL Server doesn't define either an error 6 or an error 4 in the sysmessages table. See the 'native error numbers' topic in Books Online.
Using the Error Lookup tool from Visual C++, Windows error code 6 is 'The handle is invalid', while error 4 is 'The system cannot open the file.'
Have you perhaps passed the wrong statement or connection handle?
|
|
|
|
|
I have data (Sample Test Results) stored in a table linked to a Test Name table. The data is a sub table of the Sample table. Sample => Sample Test Results is a one to many relationship respectively and Sample Test Results is a 1 to 1 relationship with the Test table. Problem: Customer would like to have the Test & Results listed by the Test Name table and have the columns represent the Sample table.
S1=Sample1
T1=TestName1
S1T1Result=Sample1 Test1 Results
Query results
S1 T1 S1T1Result
S1 T2 S1T2Result
S1 T3 S1T3Result
S1 T4 S1T4Result
S2 T1 S2T1Result
S2 T2 S2T2Result
S2 T3 S2T3Result
S2 T4 S2T4Result
Would like to display in a grid as such:
S1 S2
T1 S1T1Result S2T1Result
T2 S1T2Result S2T2Result
T3 S1T3Result S2T3Result
T4 S1T4Result S2T4Result
Tried the DataView.CopyTo(array,Index) then executing array.Reverse on the array but is only for a 1 dimensional array.
Is there a simple way to reverse a 2 dimensional array in .Net?
Doing a cursor creating dynamic SQL may be my best option at this point, but not the most efficient. Any help with this would be appreciated.
Any other suggestions would be even better.
Thanks,
Michael
|
|
|
|
|
Thanks
Michael
<br />
drop table #1<br />
go<br />
SELECT DISTINCT TOP 3 -- Rows returned Will be determined by user<br />
SC.SampleCondensateID, <br />
IsNull(SC.TripNumber, '') + ' ' + convert(CHAR(10), SC.ShipDate, 110) as [ColumnName], <br />
SC.ShipDate --+ char(13)<br />
INTO #1<br />
FROM tblSampleCondensate SC,<br />
tblSampleCondensateResult SCR,<br />
tblSamplesCondensateType CT<br />
WHERE SC.SampleCondensateID = SCR.SampleCondensateID <br />
AND SC.CondensateTypeID *= CT.CondensateTypeID <br />
AND (SC.Deleted = 0 or SC.Deleted IS NULL) <br />
<br />
<br />
<br />
<br />
DECLARE Properties CURSOR FAST_FORWARD FOR<br />
Select SampleCondensateID, [ColumnName] <br />
From #1<br />
Order by ShipDate DESC<br />
<br />
Declare @SQL varchar(8000),<br />
@InSQL varchar(200),<br />
@ID varchar(10)<br />
SELECT @SQL='', @InSQL='', @ID=''<br />
<br />
Declare @ColumnName varchar(75),<br />
@SampleCondensateID int<br />
<br />
Open Properties <br />
Fetch From Properties <br />
Into @SampleCondensateID, @ColumnName<br />
<br />
-- Loop through each row in #1 to create the columns for the final query<br />
While @@Fetch_Status = 0<br />
BEGIN<br />
-- Convert the Int value to a VarChar and trim extra spaces off<br />
SELECT @ID = RTrim(Convert(varchar(10), @SampleCondensateID))<br />
-- Build the In Clause<br />
SELECT @InSQL = @InSQL + @ID + + ','<br />
-- Build the Column with [ColumnName] being its name<br />
SELECT @SQL = @SQL + <br />
+ ' SUM(CASE WHEN SampleCondensateID = ' + @ID <br />
+ ' THEN Results ELSE 0 END) AS [' + @ColumnName + '], ' <br />
<br />
Fetch From Properties<br />
Into @SampleCondensateID, @ColumnName<br />
END<br />
<br />
-- Close & Deallocate Cursor<br />
Close Properties<br />
Deallocate Properties<br />
<br />
-- Trim Commas From SQL Variables<br />
SELECT @InSQL = SubString(@InSQL, 1, DataLength(@InSQL) - 1)<br />
SELECT @SQL = SubString(@SQL, 1, DataLength(@SQL) - 2)<br />
<br />
-- Build & Execute the Select query<br />
Exec('Select TestProperty, ' + @SQL<br />
+ 'From tblSamplesTestProperty tp, <br />
tblSampleCondensateResult cr<br />
WHERE tp.TestPropertyID *= cr.AmineID<br />
AND cr.SampleCondensateID in ('<br />
+ @InSQL<br />
+ ') GROUP BY tp.TestProperty Order By tp.TestProperty'<br />
)<br />
<br />
<br />
<br />
<br />
|
|
|
|
|
I have read this article on finding SQL servers on the network
(http://www.codeproject.com/cs/database/LocatingSql.asp)
Does anyone know how to find DB2 and Oracle Servers on the Network ?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
