Newbie Question accessing values in other data regions

Hi,
i have a simple report that has two data regions,
i want to use a calculated field in one data region that refers to a
field in the other region.
how do i achieve this?
sql server 2000 RS 2000
regardsThink of RS data regions as repeaters that expand to show all rows in the
underlying dataset. So, a data region cell is relative since its position is
known only during runtime. It is like trying to link two database tables
that don't have a common key. For this reason, you can only get an
aggregated value from another region, e.g. Sum(Fields!FieldName.Value).
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"cg" <colingresty@.yahoo.co.uk> wrote in message
news:1131975339.742358.216470@.z14g2000cwz.googlegroups.com...
> Hi,
> i have a simple report that has two data regions,
> i want to use a calculated field in one data region that refers to a
> field in the other region.
> how do i achieve this?
> sql server 2000 RS 2000
> regards
>|||thanks Teo,
that makes things a bit clearer.

Newbie Question

Can any one please provide a sample of how to read a field in SQL database
using VBS? I'm trying to learn how to do something more complex then this,
but this should get me started, hopfully.
Thanks in advance
Here's a link to a simple ASP example. For straight VBS just remove the
Server. bits for CreateObject e.g.
Set DataConn = Server.CreateObject("ADODB.Connection")
becomes
Set DataConn = CreateObject("ADODB.Connection")
http://www.asp101.com/samples/viewas...database%2Easp
You can ignore the top bit and just look at the database access code. Should
give you some ideas. Also look at
http://www.w3schools.com/ado/default.asp
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"C_G" <CG@.discussions.microsoft.com> wrote in message
news:21C90F48-B7DF-47C7-AA6C-A3311676CE79@.microsoft.com...
> Can any one please provide a sample of how to read a field in SQL database
> using VBS? I'm trying to learn how to do something more complex then
> this,
> but this should get me started, hopfully.
> Thanks in advance

Newbie question

Hi all,
I need an autonumber field in a table.
I set the following parameters for the field:
data type - int,
identity - yes (not for replication)
identity seed - 1
identity increment - 1
Is this the right thing to do?
TIA
CSharpHi,
You are right.
Thanks
Hri
MCDBA
"CSharp" <smitha@.asianetindia.com> wrote in message
news:OMXoyYuFEHA.3180@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I need an autonumber field in a table.
> I set the following parameters for the field:
> data type - int,
> identity - yes (not for replication)
> identity seed - 1
> identity increment - 1
> Is this the right thing to do?
> TIA
> CSharp
>

Newbie Query Problem

I want to use the same field in one table and return multiple columns for
different criteria. In other words...
First column
SUM(Sales.NetSales) as 'Total Sales').
Then I want a second column as
SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
Is this reasonable? I am sure that it is a simple thing that I am just
ignorant of.
Thanks.
ChuckChuck,
Try:
--rows
SELECT CategoryID, SUM(Sales) AS 'Total Sales'
FROM NetSales
GROUP BY CategoryID
--or
--columns
SELECT 'Category 1' = (SELECT SUM(Sales) AS 'Total Sales'FROM NetSales WHERE
CategoryID = 1),
'Category 2' = (SELECT SUM(Sales) AS 'Total Sales'FROM NetSales WHERE
CategoryID = 2)
HTH
Jerry
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:94FD5CDE-C7BE-43CF-857C-2A847675A1EF@.microsoft.com...
>I want to use the same field in one table and return multiple columns for
> different criteria. In other words...
> First column
> SUM(Sales.NetSales) as 'Total Sales').
> Then I want a second column as
> SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
> Is this reasonable? I am sure that it is a simple thing that I am just
> ignorant of.
> Thanks.
> Chuck|||SELECT
(SELECT SUM(NetSales) FROM Sales) as TotalSales,
(SELECT SUM(NetSales) FROM Sales WHERE categoryid = '02') as Category02
Chuck wrote:
> I want to use the same field in one table and return multiple columns for
> different criteria. In other words...
> First column
> SUM(Sales.NetSales) as 'Total Sales').
> Then I want a second column as
> SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
> Is this reasonable? I am sure that it is a simple thing that I am just
> ignorant of.
> Thanks.
> Chuck|||On Mon, 19 Sep 2005 11:58:06 -0700, Chuck wrote:

>I want to use the same field in one table and return multiple columns for
>different criteria. In other words...
>First column
>SUM(Sales.NetSales) as 'Total Sales').
>Then I want a second column as
>SUM(Sales.NetSales) as 'Category 02' where Sales.categoryid='02'.
>Is this reasonable? I am sure that it is a simple thing that I am just
>ignorant of.
>Thanks.
>Chuck
Hi Chuck,
Here's a way that requires only one pass over the table:
SELECT SUM(NetSales) AS 'Total Sales',
SUM(CASE WHEN categoryid = '02' THEN NetSales ELSE NULL END) AS
'Category 02'
FROM YourTable
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Newbie qn

Hi all,
I need a boolean field like yes/no in MS Access.
Isn't there such a data type in SQL Server?
What should I do ?
TIA,
CSharpHi,
Go for BIT data type. Bit will store either 1 or 0.
Thanks
Hari
MCDBA
"CSharp" <smitha@.asianetindia.com> wrote in message
news:e4cL2YuFEHA.3180@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I need a boolean field like yes/no in MS Access.
> Isn't there such a data type in SQL Server?
> What should I do ?
> TIA,
> CSharp
>|||... but please be aware that the bit datatype is not a Boolean datatype. Bi
t is a numeric datatype with the
values 1, 0 and NULL. It is up to the user to determine whether 1 means true
or false.
A Boolean datatype would have the truth-values "true", "false" and "unknown"
(all three are possible outcome
of a comparison, for instance). But since all datatype would need to represe
nt NULL as well, you have a bit
complexity to determine the difference etc between "unknown" and NULL, and I
believe that this is one of the
reasons why ANSI SQL-92 didn't define a Boolean datatype. SQL:1999 did, howe
ver, and AFAIK they essentially
ignored the possible differences between "unknown" and NULL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Hari" <hari_prasad_k@.hotmail.com> wrote in message news:%23Y4GJfuFEHA.3912@.TK2MSFTNGP10.ph
x.gbl...
> Hi,
> Go for BIT data type. Bit will store either 1 or 0.
> Thanks
> Hari
> MCDBA
> "CSharp" <smitha@.asianetindia.com> wrote in message
> news:e4cL2YuFEHA.3180@.TK2MSFTNGP12.phx.gbl...
>|||Yep
Untill SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was
no support for NULL. But from SQL Server 7.0 onwards, bit datatype can hold
also NULL. (My two cents
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e#8kaEwFEHA.712@.tk2msftngp13.phx.gbl...
> ... but please be aware that the bit datatype is not a Boolean datatype.
Bit is a numeric datatype with the
> values 1, 0 and NULL. It is up to the user to determine whether 1 means
true or false.
> A Boolean datatype would have the truth-values "true", "false" and
"unknown" (all three are possible outcome
> of a comparison, for instance). But since all datatype would need to
represent NULL as well, you have a bit
> complexity to determine the difference etc between "unknown" and NULL, and
I believe that this is one of the
> reasons why ANSI SQL-92 didn't define a Boolean datatype. SQL:1999 did,
however, and AFAIK they essentially
> ignored the possible differences between "unknown" and NULL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23Y4GJfuFEHA.3912@.TK2MSFTNGP10.phx.gbl...
>sql

Newbie Needs Help Getting Date

Hello,
I have a field in a table that automatically saves the Date and Time.
My field looks like this: 10/10/2003 2:24:40 PM
I need to retrieve ONLY the date portion of this field. Is there a simple way to do this? Any help is greatly appreciated!
Thanks!SELECT CONVERT(varchar(26),GetDate(),1)

Look up CONVERT in BOL for more options...|||...but you'll need to cast is as a date value again if you want to use it like a date:

SELECT CAST(CONVERT(varchar(26),GetDate(),1) as DateTime)

...or my preference because it sorts correctly as varchar...
SELECT CAST(CONVERT(varchar(10),GetDate(),120) as DateTime)

Everybody has their own preference for this, but the general method of casting as a string and then back to datetime (if necessary) is standard.

blindman

P.S.: If you still need help getting a date, check out this link:
http://personals.yahoo.com/|||heh, nice P.S. -- this forum needs the occasional chuckle

by the way, style 120, which blindman mentioned, is the ISO standard format yyyy-mm-dd

is sorts nicely as a varchar because it goes from highest to lowest (well, i didn't say that right, but i hope you know what it means)

in addition, ISO format is always correctly interpreted by all databases when inserting dates

i cannot count how many times i've seen posts on various forums from people who have run into trouble trying to insert values like '04/03/2003' (march 4th or april 3rd?)|||Originally posted by r937
i cannot count how many times i've seen posts on various forums from people who have run into trouble trying to insert values like '04/03/2003' (march 4th or april 3rd?)

Funny, I'm trying to explain that to my fellow programmers right now ... They've got the most intricate functions to fix those problems and they all screw up when someone changes the regional settings on their servers :)|||Nice PS: However, my wife MAY be mad at me for looking at personals, or maybe she is tired of me and would like that!!! LOL

I may not have described my problem well enough or probably I am not understanding since I am new to all this. This is more of what I need...

I have these dates and times: Field Name is GetDate

10/10/2003 2:24:40 PM
10/10/2003 3:34:41 PM
10/20/2003 2:24:40 PM
10/20/2003 6:54:20 PM
10/21/2003 2:24:40 PM
10/21/2003 8:34:43 PM

I need to pull out the different dates and display only one date:

10/10/2003
10/20/2003
10/21/2003

Does that make sense??
Thanks again!!!|||Please tell me you didn't name your field "GetDate". :confused:

You actually are performing two operations, the first is to truncate datetime values (which we've shown you how to do) and the second is reducing the result set to a single row for each date. You can do this using either the SELECT DISTINCT syntax, or if you need to perform aggregate functions on other columns (sum, count, avg etc...) you can use the GROUP BY syntax.

Read up on the SELECT statement in Books Online. It is very powerful, has many options, and is 90%+ of the statements DBA's write.

blindman|||Thanks for the assistance...

I didn't name the field getDate: I misstyped... it is DateSubmitted...

Thinking dateSubmitted and getDate came out of fingers... hehehe

Thanks so much for everything!!!!

Newbie needing help with joining 2 fields

I have a sql 2005 dev ed running an application developed in vs.net 2005 C# I have several gridviews which have a field call first name and a field called lastname I need to put both the firstname and lastname in the same cell or colum row. I do not now how to join these. Can some one help me with a SQL query string that will do this for me.You could do SELECT FirstName + ' ' + LastName FROM YourTable|||SELECT FirstName + ' ' + LastName AS FullName
FROM Employees

Newbie need help on trigger.

Hello,
I need to write a trigger that removes repeated elements on a table.
I want to delete every row where the field "name" has beenrepeated.
Any hel in writing such a trigger would be so much higly appreciated.
Many thinks in advance
JBWhy do this via a trigger? Wouldn't it be easier to not insert duplicate
rows to begin with?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jensen bredal" <jensen.bredahl@.yahoo.com> wrote in message
news:O9xhu5PHFHA.3624@.tk2msftngp13.phx.gbl...
> Hello,
> I need to write a trigger that removes repeated elements on a table.
> I want to delete every row where the field "name" has beenrepeated.
> Any hel in writing such a trigger would be so much higly appreciated.
> Many thinks in advance
> JB
>|||While this is a very good question, it appears that we
need the other option.
We are doing something very unusual. This is a system
integration project and we are sharing this database with other systems.
I won''t go in furthere details and hope that make sens.
Many thanks
JB|||You could try an INSTEAD OF trigger:
CREATE TRIGGER TG_NoDupes
ON YourTable
FOR INSERT
AS
BEGIN
IF @.@.ROWCOUNT = 0
RETURN
INSERT YourTable
SELECT *
FROM INSERTED
WHERE NOT EXISTS
(SELECT *
FROM YourTable
WHERE YourTable.Name = INSERTED.Name)
END
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jensen bredal" <jensen.bredahl@.yahoo.com> wrote in message
news:%23SifbHQHFHA.1176@.TK2MSFTNGP12.phx.gbl...
> While this is a very good question, it appears that we
> need the other option.
> We are doing something very unusual. This is a system
> integration project and we are sharing this database with other systems.
> I won''t go in furthere details and hope that make sens.
> Many thanks
> JB
>|||Jensen,
Unless I'm really not understanding, what you are asking for is inherently
inconsistent. Either you want a PROCEDURE that will delete all instances
where name has been repeated, or you want a TRIGGER to PREVENT the insert of
such records in the first place. An insert TRIGGER, for eg, will fire and
run EVERY TIME a record is inserted. If you wrote it to delete all existing
duplicates, it would be re-running that code over and over- again
unnecessarily, on every insert, when the first run would have already delete
d
all existing duplicates.
What it sounds like yuou might actually need, (excuse me if I've
mistunderastood) is a procedure to run ONCE to delete all existing
duplicates, and a trigger, (actually a unique constraint on the name Column
would do it) that would prevent furthur inserts of duplicates in the future.
Anyway, if so, for the first step, to eliminate existing dupes, sasuming the
table has a Primary Key, called say "PKID", try this:
Delete T
From TableName T
Where Name In (Select Name From TableName
Group By Name
Having Count(*) > 1)
And PKID <> (Select Min(PKID)
From TableName
Where Name = T.Name)
"Jensen bredal" wrote:

> Hello,
> I need to write a trigger that removes repeated elements on a table.
> I want to delete every row where the field "name" has beenrepeated.
> Any hel in writing such a trigger would be so much higly appreciated.
> Many thinks in advance
> JB
>
>|||"Jensen bredal" <jensen.bredahl@.yahoo.com> wrote in message
news:O9xhu5PHFHA.3624@.tk2msftngp13.phx.gbl...
> Hello,
> I need to write a trigger that removes repeated elements on a table.
> I want to delete every row where the field "name" has beenrepeated.
> Any hel in writing such a trigger would be so much higly
appreciated.
> Many thinks in advance
> JB
>
Jensen bredal,
May I ask why you are operating a table without a Primary Key? (Yes,
I did read the other portion of the thread where the "We're doing
something unusual" answer was given, but I'm still curious).
From the description, it sounds like you want to keep out duplicate
records. This is the effect a Primary Key has, and it's a lot faster
than any trigger.
Sincerely,
Chris O.|||Well as i said this is not the every day scenario.
I may give you a full explaination if you have time to read my answer. Let
me know if you want that.
JB
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:MPednYwUXbNt_r_fRVn-sA@.comcast.com...
> "Jensen bredal" <jensen.bredahl@.yahoo.com> wrote in message
> news:O9xhu5PHFHA.3624@.tk2msftngp13.phx.gbl...
> appreciated.
> Jensen bredal,
> May I ask why you are operating a table without a Primary Key? (Yes,
> I did read the other portion of the thread where the "We're doing
> something unusual" answer was given, but I'm still curious).
> From the description, it sounds like you want to keep out duplicate
> records. This is the effect a Primary Key has, and it's a lot faster
> than any trigger.
>
> Sincerely,
> Chris O.
>

newbie need help

I need to know how to write a sql statement for VB to Microsoft Access for the following criteria.

TblEmployees only Field to be concerned with is EmployeeID -Its PK
TblEmpAttendane has these fields:
EntryID - Pk
EmployeeID - Fk
Date- A text form date validated through vb to avoid hassle
Value-A single digit value that can be alpha OR numeric

To get the Date range for the current month I use variables to store the First and last day of the month(in a CUSTOM calendar control).

I have been trying to use this Sql statement(that wont work):
SqlString = "SELECT * FROM TblEmpAttendance WHERE Date BETWEEN '" & FirstDay & "' AND '" & LastDay & "'"

I need to:
1.)using the current EmployeeID (from TblEmployees)
2.) find the same EmployeeID in TblEmpAttendance
3.)Get the date range for the current month and year(valid days currnt mo.)
-these date are supplied by the variables "FirstDay" AND "LastDay" as seen in above SqlString

4.) Find the Values associated with the dates and EmployeeIDAre you using Jet or MS-SQL (aka MSDE) as your database engine? They have differences in how they handle dates, and the example you gave would not fly very well in Jet.

-PatP|||Im using jet to connect(through code) to my db.
Can u assist me in how to improve my table format..or whatever I would have to do in order to be able to do this correctly?

All that I did was set up a string format in vb to make sure that the correct amount of characters and the proper syntax was used:
eg... 00/00/0000
In my code I nvr explicity refer to them as a date. Only the user would think that it was a date!

Thanks-Greg S|||I'd try using:SqlString = "SELECT * FROM TblEmpAttendance WHERE #" _
& FirstDay & "# <= Date AND Date <= #" & LastDay & "#"This is only a swag, but I think that it should work.

-PatP|||Thanks I will try that and get back to you. Much appreciated!|||I have a lot more to do than I thought. I did do a date conversion function from within access so it is only displayed as dd/mm/yy format.
Then from vb I used:
SqlString = "SELECT * FROM TblEmpAttendance WHERE Date Bewteen '" & FirstDay & "' AND '" & LastDay & "'"

It seemd to work fine as I can refer to a field but for some reason when I refer to the recordcount property,I always get -1...Seems weird how I can read the info but not get the recordcount!!|||Check the BOL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprorecordcount.asp). If you use ADOpenStatic you won't have -1 as a recordcount.

-PatP|||I dont know what you mean by "BOL" but as I am using ADO as a connection it does support bookmarks(not that I believe I need on with the move.bof statement) If this is not what you were getting at could you be more specific?

OK WAIT..I added adOpenStatic to my statement!
now it seems to be working
But I had to put in in the form of:
AdoRecordset.Open SqlString, AdoConnection, adOpenStatic

instead of AdoRecordset.Open (SqlString, AdoConnection), adOpenStatic
like the link you posted...Im still not sure why the perverbial correct way wouldnt work but my way worked anyhow..
THANKS MUCH!|||adOpenStatic, adOpenDynamic, adOpenKeyset, and adOpenForwardOnly are qualifications of the cursor that is being open either on the server or client side (depends on the CursorLocation property). The default (adOpenForwardOnly) will have -1 for RecordCount property.

newbie ?

I have a table that has an id field and a description field... Unfortunately
the id field does contain dupicate id entries...
What I need to do is select each distinct id and then one of the many
description fields that are assigned to the id number
can anyone help with thisIf you wanna have the duplicates display:
Select IDField,DescriptionField from SomeTable
WHERE IDField in
(SELECT IDField
From SomeTable
HAVING COUNT(*) > 1
)
Having the distinct values displayed, you should change the Having clause
for HAVING COUNT(*) = 1
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"sqlnewbie67" <sqlnewbie67@.discussions.microsoft.com> schrieb im Newsbeitrag
news:908F1A7A-2F7A-422E-8E58-7FA9A5048184@.microsoft.com...
>I have a table that has an id field and a description field...
>Unfortunately
> the id field does contain dupicate id entries...
> What I need to do is select each distinct id and then one of the many
> description fields that are assigned to the id number
> can anyone help with this|||hello ,
SELECT DISTINCT id, Description from yourtablename
Let us know if it helps....
cheers,
siaj
"sqlnewbie67" wrote:

> I have a table that has an id field and a description field... Unfortunate
ly
> the id field does contain dupicate id entries...
> What I need to do is select each distinct id and then one of the many
> description fields that are assigned to the id number
> can anyone help with this|||Select id, min(Description)
From Table
Group By id
"sqlnewbie67" wrote:

> I have a table that has an id field and a description field... Unfortunate
ly
> the id field does contain dupicate id entries...
> What I need to do is select each distinct id and then one of the many
> description fields that are assigned to the id number
> can anyone help with this

Newbie - Store jpg file in Image Data Type Field

I assume that this is really simple to do once you know how to do it.
I have a very small table, only 5 rows and two columns. Column 0 is
an integer from 1 to 5, and column 1 is supposed to store an image
corresponding to each of the values in column 0. I have the five
different image files in a jpg format. I tried simply copying and
pasting these files into the table, but that didn't work. Can
somebody tell me how to save these jpg files in the table so that I
can use them in my VB.NET application?
Thanks,
RandySQL 2000?
"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182898391.523504.187750@.i38g2000prf.googlegroups.com...
>I assume that this is really simple to do once you know how to do it.
> I have a very small table, only 5 rows and two columns. Column 0 is
> an integer from 1 to 5, and column 1 is supposed to store an image
> corresponding to each of the values in column 0. I have the five
> different image files in a jpg format. I tried simply copying and
> pasting these files into the table, but that didn't work. Can
> somebody tell me how to save these jpg files in the table so that I
> can use them in my VB.NET application?
> Thanks,
> Randy
>|||Actually, SQL Server Management Studio Express|||In SQL Server 2005 you can use OPENROWSET with the SINGLE_BLOB option, like
this:
CREATE TABLE Foobar (
image_data VARBINARY(MAX));
INSERT INTO Foobar
(image_data)
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image.jpg',
SINGLE_BLOB)
AS ImageSource(image_data);
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks, but I don't know how to implement this. I'd like to actually
store the files within the datatable rather than referencing them from
a file location. Is that possible? Otherwise, can you explain this
to me in further detail?
Thanks again.
Randy|||That will store them in the database, it is insert script. You do it onces
they are there for access. If you want to do it from .NET code.. I don't
have .NET example, but here is example on how to do it in VB6, maybe you can
convert it.
Private Sub InsertFile()
On Error GoTo ErrorHandler
Dim Index As Long
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream
strSQL = "SELECT * FROM TableName"
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile FileNameToLoadWithFullPath
rs.AddNew
rs.Fields('FileData').Value = mstream.Read
rs.Update
rs.Close
End Sub
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Randy" wrote:

> Thanks, but I don't know how to implement this. I'd like to actually
> store the files within the datatable rather than referencing them from
> a file location. Is that possible? Otherwise, can you explain this
> to me in further detail?
> Thanks again.
> Randy
>|||You just need to run this as a query in SQL Server Management Studio. It
will actually store the image data into the table, the reference to the file
location is needed just to load the images.
Here is a more detailed example that may fit better your case. The code
below creates a table with keys and then updates the image column for each
key (based on your initial post I assume this is what you want to do).
CREATE TABLE Foobar (
keycol INTEGER,
image_data VARBINARY(MAX));
-- Insert the keys.
INSERT INTO Foobar (keycol)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image1.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 1;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image2.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 2;
-- Continue to load all images...
SELECT keycol, image_data
FROM Foobar;
DROP TABLE Foobar;
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks to both of you. I'm working with Plamen's query and got it to
execute successfully, however I'm still not all the way there. A
couple of questions:
1. I can see that the table was created in the Results pane, but I
don't know where this table is actually stored. Of course, the table
is of no use until I can make it part of my db, and I don't see it
listed among the tables in the db. How can I create this table so
that it is a permanent member of my db?
2. In the Results pane, I see a two column table. Column 1 is called
keycol and col 2 is called image_data, as created by the query. There
are five rows, each of which contains an integer value from 1 to 5 in
keycol, also as created by the query. However, the fields in
image_data are blank, at least as viewed through the Results pane.
I'm not sure if the images have actually loaded correctly. To
clarify, I changed the query language to include tha path names for
each of the 5 images that I am trying to import, so I don't think that
is part of the problem.
Thanks a lot for sticking with me on this. I'm sorry that I am so
clueless, but working direclty in SQL Server is completely new to me.
Randy|||Ignore that last reply. Apparently, I wasn't looking at refreshed
view of the db. The table is there, as are both columns. There was
no data in the table, so I added the key column values and just ran
the UPDATE part of the query, which seems to have populated the
image_data values. Now, i just have to figure out how to pull this
onto my VB form. When I look at the table data, it just says <Binary
Data> in each of the fields, so I'm not certain that I have everything
in place just yet. If I have trouble, I'll re-post.
Thanks for everybody's help!
Randy|||"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182979823.310482.88750@.e16g2000pri.googlegroups.com...
> Thanks to both of you. I'm working with Plamen's query and got it to
> execute successfully, however I'm still not all the way there. A
> couple of questions:
> 1. I can see that the table was created in the Results pane, but I
> don't know where this table is actually stored. Of course, the table
> is of no use until I can make it part of my db, and I don't see it
> listed among the tables in the db. How can I create this table so
> that it is a permanent member of my db?
If you just copied my sample query, then at the end of it there is a DROP
TABLE statement. You can comment it out or remove it and then run again to
keep the table. This is the line you need to comment out or remove:
--DROP TABLE Foobar;

> 2. In the Results pane, I see a two column table. Column 1 is called
> keycol and col 2 is called image_data, as created by the query. There
> are five rows, each of which contains an integer value from 1 to 5 in
> keycol, also as created by the query. However, the fields in
> image_data are blank, at least as viewed through the Results pane.
> I'm not sure if the images have actually loaded correctly. To
> clarify, I changed the query language to include tha path names for
> each of the 5 images that I am trying to import, so I don't think that
> is part of the problem.
>
You cannot see the image in the result pane, but rather the binary
representation. If you just right click the table and select Open Table, you
should see something like <Binary data> in the image_data column. If you run
the query and look in Result, you should see something like 0xFFD8... If you
see NULL, then the images were not uploaded successfully. I would suggest to
check the path for the files and if the file names are correct.
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Newbie - Store jpg file in Image Data Type Field

I assume that this is really simple to do once you know how to do it.
I have a very small table, only 5 rows and two columns. Column 0 is
an integer from 1 to 5, and column 1 is supposed to store an image
corresponding to each of the values in column 0. I have the five
different image files in a jpg format. I tried simply copying and
pasting these files into the table, but that didn't work. Can
somebody tell me how to save these jpg files in the table so that I
can use them in my VB.NET application?
Thanks,
Randy
SQL 2000?
"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182898391.523504.187750@.i38g2000prf.googlegr oups.com...
>I assume that this is really simple to do once you know how to do it.
> I have a very small table, only 5 rows and two columns. Column 0 is
> an integer from 1 to 5, and column 1 is supposed to store an image
> corresponding to each of the values in column 0. I have the five
> different image files in a jpg format. I tried simply copying and
> pasting these files into the table, but that didn't work. Can
> somebody tell me how to save these jpg files in the table so that I
> can use them in my VB.NET application?
> Thanks,
> Randy
>
|||Actually, SQL Server Management Studio Express
|||In SQL Server 2005 you can use OPENROWSET with the SINGLE_BLOB option, like
this:
CREATE TABLE Foobar (
image_data VARBINARY(MAX));
INSERT INTO Foobar
(image_data)
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image.jpg',
SINGLE_BLOB)
AS ImageSource(image_data);
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Thanks, but I don't know how to implement this. I'd like to actually
store the files within the datatable rather than referencing them from
a file location. Is that possible? Otherwise, can you explain this
to me in further detail?
Thanks again.
Randy
|||That will store them in the database, it is insert script. You do it onces
they are there for access. If you want to do it from .NET code.. I don't
have .NET example, but here is example on how to do it in VB6, maybe you can
convert it.
Private Sub InsertFile()
On Error GoTo ErrorHandler
Dim Index As Long
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream
strSQL = "SELECT * FROM TableName"
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile FileNameToLoadWithFullPath
rs.AddNew
rs.Fields('FileData').Value = mstream.Read
rs.Update
rs.Close
End Sub
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Randy" wrote:

> Thanks, but I don't know how to implement this. I'd like to actually
> store the files within the datatable rather than referencing them from
> a file location. Is that possible? Otherwise, can you explain this
> to me in further detail?
> Thanks again.
> Randy
>
|||You just need to run this as a query in SQL Server Management Studio. It
will actually store the image data into the table, the reference to the file
location is needed just to load the images.
Here is a more detailed example that may fit better your case. The code
below creates a table with keys and then updates the image column for each
key (based on your initial post I assume this is what you want to do).
CREATE TABLE Foobar (
keycol INTEGER,
image_data VARBINARY(MAX));
-- Insert the keys.
INSERT INTO Foobar (keycol)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image1.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 1;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image2.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 2;
-- Continue to load all images...
SELECT keycol, image_data
FROM Foobar;
DROP TABLE Foobar;
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Thanks to both of you. I'm working with Plamen's query and got it to
execute successfully, however I'm still not all the way there. A
couple of questions:
1. I can see that the table was created in the Results pane, but I
don't know where this table is actually stored. Of course, the table
is of no use until I can make it part of my db, and I don't see it
listed among the tables in the db. How can I create this table so
that it is a permanent member of my db?
2. In the Results pane, I see a two column table. Column 1 is called
keycol and col 2 is called image_data, as created by the query. There
are five rows, each of which contains an integer value from 1 to 5 in
keycol, also as created by the query. However, the fields in
image_data are blank, at least as viewed through the Results pane.
I'm not sure if the images have actually loaded correctly. To
clarify, I changed the query language to include tha path names for
each of the 5 images that I am trying to import, so I don't think that
is part of the problem.
Thanks a lot for sticking with me on this. I'm sorry that I am so
clueless, but working direclty in SQL Server is completely new to me.
Randy
|||Ignore that last reply. Apparently, I wasn't looking at refreshed
view of the db. The table is there, as are both columns. There was
no data in the table, so I added the key column values and just ran
the UPDATE part of the query, which seems to have populated the
image_data values. Now, i just have to figure out how to pull this
onto my VB form. When I look at the table data, it just says <Binary
Data> in each of the fields, so I'm not certain that I have everything
in place just yet. If I have trouble, I'll re-post.
Thanks for everybody's help!
Randy
|||"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182979823.310482.88750@.e16g2000pri.googlegro ups.com...
> Thanks to both of you. I'm working with Plamen's query and got it to
> execute successfully, however I'm still not all the way there. A
> couple of questions:
> 1. I can see that the table was created in the Results pane, but I
> don't know where this table is actually stored. Of course, the table
> is of no use until I can make it part of my db, and I don't see it
> listed among the tables in the db. How can I create this table so
> that it is a permanent member of my db?
If you just copied my sample query, then at the end of it there is a DROP
TABLE statement. You can comment it out or remove it and then run again to
keep the table. This is the line you need to comment out or remove:
--DROP TABLE Foobar;

> 2. In the Results pane, I see a two column table. Column 1 is called
> keycol and col 2 is called image_data, as created by the query. There
> are five rows, each of which contains an integer value from 1 to 5 in
> keycol, also as created by the query. However, the fields in
> image_data are blank, at least as viewed through the Results pane.
> I'm not sure if the images have actually loaded correctly. To
> clarify, I changed the query language to include tha path names for
> each of the 5 images that I am trying to import, so I don't think that
> is part of the problem.
>
You cannot see the image in the result pane, but rather the binary
representation. If you just right click the table and select Open Table, you
should see something like <Binary data> in the image_data column. If you run
the query and look in Result, you should see something like 0xFFD8... If you
see NULL, then the images were not uploaded successfully. I would suggest to
check the path for the files and if the file names are correct.
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Newbie - Store jpg file in Image Data Type Field

I assume that this is really simple to do once you know how to do it.
I have a very small table, only 5 rows and two columns. Column 0 is
an integer from 1 to 5, and column 1 is supposed to store an image
corresponding to each of the values in column 0. I have the five
different image files in a jpg format. I tried simply copying and
pasting these files into the table, but that didn't work. Can
somebody tell me how to save these jpg files in the table so that I
can use them in my VB.NET application?
Thanks,
RandySQL 2000?
"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182898391.523504.187750@.i38g2000prf.googlegroups.com...
>I assume that this is really simple to do once you know how to do it.
> I have a very small table, only 5 rows and two columns. Column 0 is
> an integer from 1 to 5, and column 1 is supposed to store an image
> corresponding to each of the values in column 0. I have the five
> different image files in a jpg format. I tried simply copying and
> pasting these files into the table, but that didn't work. Can
> somebody tell me how to save these jpg files in the table so that I
> can use them in my VB.NET application?
> Thanks,
> Randy
>|||Actually, SQL Server Management Studio Express|||In SQL Server 2005 you can use OPENROWSET with the SINGLE_BLOB option, like
this:
CREATE TABLE Foobar (
image_data VARBINARY(MAX));
INSERT INTO Foobar
(image_data)
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image.jpg',
SINGLE_BLOB)
AS ImageSource(image_data);
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks, but I don't know how to implement this. I'd like to actually
store the files within the datatable rather than referencing them from
a file location. Is that possible? Otherwise, can you explain this
to me in further detail?
Thanks again.
Randy|||That will store them in the database, it is insert script. You do it onces
they are there for access. If you want to do it from .NET code.. I don't
have .NET example, but here is example on how to do it in VB6, maybe you can
convert it.
Private Sub InsertFile()
On Error GoTo ErrorHandler
Dim Index As Long
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream
strSQL = "SELECT * FROM TableName"
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile FileNameToLoadWithFullPath
rs.AddNew
rs.Fields('FileData').Value = mstream.Read
rs.Update
rs.Close
End Sub
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Randy" wrote:
> Thanks, but I don't know how to implement this. I'd like to actually
> store the files within the datatable rather than referencing them from
> a file location. Is that possible? Otherwise, can you explain this
> to me in further detail?
> Thanks again.
> Randy
>|||You just need to run this as a query in SQL Server Management Studio. It
will actually store the image data into the table, the reference to the file
location is needed just to load the images.
Here is a more detailed example that may fit better your case. The code
below creates a table with keys and then updates the image column for each
key (based on your initial post I assume this is what you want to do).
CREATE TABLE Foobar (
keycol INTEGER,
image_data VARBINARY(MAX));
-- Insert the keys.
INSERT INTO Foobar (keycol)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image1.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 1;
UPDATE Foobar
SET image_data = (
SELECT image_data
FROM OPENROWSET(
BULK N'C:\image2.jpg',
SINGLE_BLOB)
AS ImageSource(image_data))
WHERE keycol = 2;
-- Continue to load all images...
SELECT keycol, image_data
FROM Foobar;
DROP TABLE Foobar;
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Thanks to both of you. I'm working with Plamen's query and got it to
execute successfully, however I'm still not all the way there. A
couple of questions:
1. I can see that the table was created in the Results pane, but I
don't know where this table is actually stored. Of course, the table
is of no use until I can make it part of my db, and I don't see it
listed among the tables in the db. How can I create this table so
that it is a permanent member of my db?
2. In the Results pane, I see a two column table. Column 1 is called
keycol and col 2 is called image_data, as created by the query. There
are five rows, each of which contains an integer value from 1 to 5 in
keycol, also as created by the query. However, the fields in
image_data are blank, at least as viewed through the Results pane.
I'm not sure if the images have actually loaded correctly. To
clarify, I changed the query language to include tha path names for
each of the 5 images that I am trying to import, so I don't think that
is part of the problem.
Thanks a lot for sticking with me on this. I'm sorry that I am so
clueless, but working direclty in SQL Server is completely new to me.
Randy|||Ignore that last reply. Apparently, I wasn't looking at refreshed
view of the db. The table is there, as are both columns. There was
no data in the table, so I added the key column values and just ran
the UPDATE part of the query, which seems to have populated the
image_data values. Now, i just have to figure out how to pull this
onto my VB form. When I look at the table data, it just says <Binary
Data> in each of the fields, so I'm not certain that I have everything
in place just yet. If I have trouble, I'll re-post.
Thanks for everybody's help!
Randy|||"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182979823.310482.88750@.e16g2000pri.googlegroups.com...
> Thanks to both of you. I'm working with Plamen's query and got it to
> execute successfully, however I'm still not all the way there. A
> couple of questions:
> 1. I can see that the table was created in the Results pane, but I
> don't know where this table is actually stored. Of course, the table
> is of no use until I can make it part of my db, and I don't see it
> listed among the tables in the db. How can I create this table so
> that it is a permanent member of my db?
If you just copied my sample query, then at the end of it there is a DROP
TABLE statement. You can comment it out or remove it and then run again to
keep the table. This is the line you need to comment out or remove:
--DROP TABLE Foobar;
> 2. In the Results pane, I see a two column table. Column 1 is called
> keycol and col 2 is called image_data, as created by the query. There
> are five rows, each of which contains an integer value from 1 to 5 in
> keycol, also as created by the query. However, the fields in
> image_data are blank, at least as viewed through the Results pane.
> I'm not sure if the images have actually loaded correctly. To
> clarify, I changed the query language to include tha path names for
> each of the 5 images that I am trying to import, so I don't think that
> is part of the problem.
>
You cannot see the image in the result pane, but rather the binary
representation. If you just right click the table and select Open Table, you
should see something like <Binary data> in the image_data column. If you run
the query and look in Result, you should see something like 0xFFD8... If you
see NULL, then the images were not uploaded successfully. I would suggest to
check the path for the files and if the file names are correct.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||VB.NET you should be able to grab the binary data from the database, store
it in a Byte() array and create a Graphics object from it. I'm doing
something similar right now in VB 2005 with dynamically generated images
being passed from SQL Server to a client-side VB app where the binary
content is converted to a bitmap and displayed on a form. Just be sure to
properly dispose of your Graphics objects, etc., when you're done with them.
"Randy" <spam.eastland@.gmail.com> wrote in message
news:1182982110.182760.129310@.e16g2000pri.googlegroups.com...
> Ignore that last reply. Apparently, I wasn't looking at refreshed
> view of the db. The table is there, as are both columns. There was
> no data in the table, so I added the key column values and just ran
> the UPDATE part of the query, which seems to have populated the
> image_data values. Now, i just have to figure out how to pull this
> onto my VB form. When I look at the table data, it just says <Binary
> Data> in each of the fields, so I'm not certain that I have everything
> in place just yet. If I have trouble, I'll re-post.
> Thanks for everybody's help!
> Randy
>|||I need to do the exact same thing but in SQL Server 2000.
I have been tinkering with BULK INSERT and OPENROWSET but have not been able
to get anything to load the image data.
Actually in my case, the image data can be a Word document, Excel
spreadsheet, PDF file, text file, etc.
Thank you in advance for any information that you can provide.
Joe|||Hi Joe,
The BULK rowset provider functionality to load BLOBs is only available since
SQL Server 2005.
See the following example by Erland Sommarskog on how this can be done is
SQL Server 2000:
http://www.sommarskog.se/blobload.txt
HTH,
Plamen Ratchev
http://www.SQLStudio.com