Please Help

i'd like to get info on storing photos to Sql Server 2000 using VB 6.0. i searched many vb sites, but all have code samples in vb.Net.

can any 1 give code samples if possible?

one more thing..

my application uses mssql server running in an IBM server machine ,i hav plans to give one machine as a back up server. i wud like to have the data updated on both machines simultaneously..
i.e,
either the data must go to both the Machines (SErver & Back Up Server) or it should not go to both.

how can i implement this?

i do welcome your views/ideas in this context.

thanks in advanceSure.

Don't.

Store the filepaths of the images.|||I'd have to agree with Brett about storing only the path to a file but if you really must store the image in a DB try this...

Include a reference to "Microsoft ActiveX Data Objects 2.5 Library" (or later)

Using the Northwind DB as an example, open your connection and add the following

Dim rs as New ADODB.Recordset
Dim sql as String
Dim mStream as Stream

sql = "SELECT EmployeeID, Photo FROM Employees Where EmployeeID=1"
rs.Open sql, adoConn, adOpenKeyset, adLockPessimistic

' Load the image into the DB field

Set mStream = New ADODB.Stream
mStream.Type = adTypeBinary
mStream.Open
mStream.LoadFromFile "c:\MyNewPhoto.jpg" ' <-******* Your filename here
rs.Fields("Photo").Value = mStream.Read

rs.Close
Set rs = Nothing

That replaces the existing image with a new one. To dump the existing image to a file you can use...

Dim rs as New ADODB.Recordset
Dim sql as String
Dim mStream as Stream

sql = "SELECT * FROM Employees Where EmployeeID=1"
rs.Open sql, adoConn, adOpenKeyset, adLockPessimistic

Set mStream = New ADODB.Stream
mStream.Type = adTypeBinary
mStream.Open
mStream.Write rs.Fields("Photo").Value
mStream.SaveToFile "c:\MyImage.jpg", adSaveCreateOverWrite ' <-******* Your filename here

rs.Close
Set rs = Nothing

The major problem I've found with storing images in the DB is ease of editing. A file can be loaded into Paint or whatever but a DB image must first be dumped to disk and then sent back after editing.|||thank you very much...

Pivot misunderstood ?

Hello, i've been looking for some samples on the pivot function, but somewhere i'm making a mistake and can't find where.

This is my statement

SELECT MOV_UI, [1] AS SUNDAY, [2] AS MONDAY, [3] AS THUESDAY, [4] AS WEDNESDAY, [5] AS THURSDAY, AS FRIDAY, [7] AS SATURDAY

FROM XX.UGENT_DAILYSALES_OF_MOVIE_FIRST_WEEK

PIVOT (SUM(TICKETS)

FOR DAYOFWEEK IN ([1], [2], [3], [4], [5], , [7])) AS PVT

ORDER BY MOV_UI

I though to receive a line per mov_ui with the 7 sums, but .. i'm getting a line for each day ....

MOV_UI SUNDAY MONDAY THUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY
29 NULL NULL NULL 73 NULL NULL NULL
29 NULL NULL NULL NULL 72 NULL NULL
29 NULL NULL NULL NULL NULL 129 NULL
29 NULL NULL NULL NULL NULL NULL 138
29 104 NULL NULL NULL NULL NULL NULL
29 NULL 68 NULL NULL NULL NULL NULL
29 NULL NULL 58 NULL NULL NULL NULL

What am i missing .... ?

Hmm, this works for me:

CREATE TABLE test
(
movie_ui int,
dayOfWeek int,
tickets int
)
INSERT INTO test
SELECT 29,1,20
UNION ALL
SELECT 29,2,38
UNION ALL
SELECT 30,1,20
UNION ALL
SELECT 30,2,44
GO
SELECT movie_ui, [1] AS SUNDAY, [2] AS MONDAY
FROM test PIVOT (SUM(TICKETS) FOR DAYOFWEEK IN ([1], [2])) AS PVT
ORDER BY movie_ui

movie_ui SUNDAY MONDAY
-- -- --
29 20 38
30 20 44

|||

Thx for the answer, i've found the problem ...

this was my syntax

SELECT MOV_UI, [1] AS SUNDAY, [2] AS MONDAY, [3] AS THUESDAY, [4] AS WEDNESDAY, [5] AS THURSDAY, AS FRIDAY, [7] AS SATURDAY

FROM XX.UGENT_DAILYSALES_OF_MOVIE_FIRST_WEEK

PIVOT (SUM(TICKETS)

FOR DAYOFWEEK IN ([1], [2], [3], [4], [5], , [7])) AS PVT

ORDER BY MOV_UI

Problem was that from the view xx.ugent.. there where other columns returned that where not used in the pivot, but they cause this effect, so adding them in the select mov_ui, ... and the order by helpen the problem, or chaning the 'from xx.ugent' to a

from (select mov_ui, dayofweek, tickets from xx_ugent_dailysales_of_movie_first_Week) p' solved the problem too.

Kind Regards