|
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
|
|
|
|
|
Not sure if this is the best way to execute a cross-tab query in SQL Server but it works. It is obviouly much faster than looping through each row/column to invert the dataset. If anyone else has any other ideas, feel free to post.
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 ?
|
|
|
|
|
Is there some class in MFC or some other library that would allow me to parse a .dbf file?
My company does not want to have to distribute a database management system with our software.
Kevin Shaffer
kshaff03@msn.com
|
|
|
|
|
Just use ADO
------------------
I'm naked under my clothes...
|
|
|
|
|
Can anyone tell me how to store an image from a c# windows form, to a MS SQL Server Database.
I've been struggling with this for some time now - please help me if you can.
Thanks in advice,
Peter
|
|
|
|
|
First, you must convert your image into a bit stream, then save it into a nimage field in your SQL Server.
1. Search about transforming your image into a stream.
2. Search about storing streams into SQL Server.
It's not that hard.
Free your mind...
|
|
|
|
|
I was going along these lines, but part of my problem is that the ways I have seen to do this involve reading the image from a file to put it into a stream, then saving it as an array of bytes. This can then be reversed at the other end.
I need to be able to create a byte-array of a System.Drawing.Image.Bitmap variable, to reduce the overhead of saving the file first. As for storing in the SQL server, I did consider using a web-service which re-constructed the stream before saving in the server - not sure if this is possible.
There is an 'image' data type in MS SQL, and I was hoping it was going to be as easy as doing an INSERT non-query directly from my c# application, but there seems to be serialization issues here because the System.Drawing.Image.Bitmap data tybe does not implement IConvertable (or something like that).
Thanks for your reply - if anyone can help me any further with this problem, I would be MOST grateful.
Thanks
peter
|
|
|
|
|
Take a look at Bitmap.Save method. You can save it to a memory stream. Then send that memory to the DB.
There is a formatter in the System.Web namespace, named LosFormatter (No documentation), that converts any byte array into a string. You can use it to serialize it into a string, then send it thru the webservice.
I recomend you to use a Store Procedure to store the Image in the DB instead of a direct INSERT.
Free your mind...
|
|
|
|
|
|
Thankyou all for your help - Im a good few steps closer to my solution.
Thanks again,
Peter
|
|
|
|