Images in SQL 2000 Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Images in SQL 2000 Server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-12 : 09:50:56

Grant writes "Hi Guys, i hope someone can help with this little problem. Im a Visual Basic 6.0 Programmer and ive built loads of systems with SQL 7.0 and 2000 as the database backend, i generally integrate crystal 9 as well to do the reporting. Anyway, i like using stored procedures in Visual basic 6.0 to send data to the SQL 2000 database and then use either the ADODC or RS! select transact SQL search to retrieve either single records or whole sets of data that match whatever criteria i choose to throw into the Query String. Anyway im wanting to learn how to prepare, fire and retrieve images (JPG / Bitmap) to and from SQL 2000 via VB Stored Procedures and the RS! / ADODC search methods you usually use to populate the datagrids etc. Can anyone please give me some examples in VB 6.0. I mean like detailed instructions here, ive already kinda figured out you need to use the common dialogue control to select your image initially. I then wanna be able to send it to sql 2000 via a stored procedure.

A brief example of the code im on about is below.

' ////////// VB PREPARATION STORED PROCEDURE CODE ////////

conn.ConnectionString = "Provider = SQLOLEDB.1; Persist Security Info = False; User Id = ITJTS; Password = support; Initial Catalog = PICTURETEST; Data Source = SQLSTOCK"

'OPEN A CONNECTION TO THE DATABASE
conn.Open

Set cmd = New ADODB.Command ' THIS IS AN INSERT START PROCEDURE
cmd.ActiveConnection = conn

'SET THE STAFF NAME
Set oParam = cmd.CreateParameter
oParam.Name = "staff_name"
oParam.Type = adChar
oParam.Size = 15
oParam.Direction = adParamInput
oParam.Value = Trim(staff_name)
cmd.Parameters.Append oParam

'SET THE STAFF SURNAME
Set oParam = cmd.CreateParameter
oParam.Name = "staff_surname"
oParam.Type = adChar
oParam.Size = 15
oParam.Direction = adParamInput
oParam.Value = Trim(staff_surname)
cmd.Parameters.Append oParam

'SET THE STAFF PICTURE - SEND AS A BINARY
Set oParam = cmd.CreateParameter
oParam.Name = "staff_picture"
oParam.Type = adBinary
oParam.Size = 50
oParam.Direction = adParamInput
oParam.Value = Trim(staff_picture)
cmd.Parameters.Append oParam

'SET THE STAFF SUBJECT
Set oParam = cmd.CreateParameter
oParam.Name = "staff_subject"
oParam.Type = adChar
oParam.Size = 15
oParam.Direction = adParamInput
oParam.Value = Trim(staff_subject)
cmd.Parameters.Append oParam

'SET THE STAFF GENDER
Set oParam = cmd.CreateParameter
oParam.Name = "staff_gender"
oParam.Type = adChar
oParam.Size = 10
oParam.Direction = adParamInput
oParam.Value = Trim(staff_gender)
cmd.Parameters.Append oParam

cmd.CommandText = "SP_add_staff_details"
cmd.CommandType = adCmdStoredProc

cmd.Execute

'/////CORESPONDING SQL 2000 STORED PROCEDURE - DATABASE SIDE ///

CREATE PROCEDURE SP_add_staff_details

@staff_name char(15), @staff_surname char(20), @staff_picture binary(50), @staff_subject char(15), @staff_gender char(10)
AS
begin transaction
insert staff_details

values (@staff_name, @staff_surname, @staff_picture, @staff_subject, @staff_gender)

if @@error=0

commit transaction

else rollback transaction

'///////////////////////////////////////////////////////////////

Then of course i wanna be able to retrieve the image along with a few other text / numeric details associated with the object in the picture via an RS! / ADODC search on the database in VB. Once ive got the image i then wanna be able to load it successfully into an image control on the main vb form and text / numeric data into text boxes etc.

Also, can anyone tell me what variable type to set the Picture container field in the SQL table to ? Is it image or Binary or something ?

Anyway, a detailed example would be appreciated, ive heard loads of people going on about doing this kinda thing in ASP and vb

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-06-12 : 11:08:01

http://www.sqlteam.com/item.asp?ItemID=986
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37980
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45564

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources