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

Newbie - Changing ordered list

Hi,

I would like to programmatically move rows up or down an ordered list. I
have created a column to ORDER BY and filled with ascending integer values.
Swapping the column values with the row above or the row below changes the
row position OK but things start getting complicated when rows are deleted
or new rows are added (e.g. what column value to assign to the new row). Is
there a simple way of doing this?

ThanksJackT (turnbull.jack@.ntlworld.com) writes:
> I would like to programmatically move rows up or down an ordered list. I
> have created a column to ORDER BY and filled with ascending integer
> values. Swapping the column values with the row above or the row below
> changes the row position OK but things start getting complicated when
> rows are deleted or new rows are added (e.g. what column value to assign
> to the new row). Is there a simple way of doing this?

I am sorry, but you need to explain a lot more of what you are doing.
Are you moving rows in a table, or are they rows in a screen form?

Generally, for many types of question in this newsgroup it is a good
idea to include:

o CREATE TABLE statements for the involved tables.
o INSERT statements with sample data.
o The desired result with the sample data.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,

CREATE TABLE [CategoryList] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryIndex] [int] NULL ,
[CategoryName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('First',0)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Second',1)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Third',2)
INSERT INTO [CategoryList] (CategoryName, CategoryIndex)
VALUES ('Fourth',3)
GO
SELECT * FROM [CategoryList] ORDER BY CategoryIndex
GO

Gives Output

1 0 First
2 1 Second
3 2 Third
4 3 Fourth

UPDATE [CategoryList]
SET [CategoryIndex]=2 WHERE [CategoryID] = 2
UPDATE [CategoryList]
SET [CategoryIndex]=1 WHERE [CategoryID] = 3
SELECT * FROM dbo.CategoryList ORDER BY CategoryIndex
GO

Gives Output

1 0 First
3 1 Third
2 2 Second
4 4 Fourth

Is this the best method of setting up a list so you can swap the ordering
programmatically?

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns953BACDF25089Yazorman@.127.0.0.1...
> JackT (turnbull.jack@.ntlworld.com) writes:
> > I would like to programmatically move rows up or down an ordered list. I
> > have created a column to ORDER BY and filled with ascending integer
> > values. Swapping the column values with the row above or the row below
> > changes the row position OK but things start getting complicated when
> > rows are deleted or new rows are added (e.g. what column value to assign
> > to the new row). Is there a simple way of doing this?
> I am sorry, but you need to explain a lot more of what you are doing.
> Are you moving rows in a table, or are they rows in a screen form?
> Generally, for many types of question in this newsgroup it is a good
> idea to include:
> o CREATE TABLE statements for the involved tables.
> o INSERT statements with sample data.
> o The desired result with the sample data.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||JackT (turnbull.jack@.ntlworld.com) writes:
> UPDATE [CategoryList]
> SET [CategoryIndex]=2 WHERE [CategoryID] = 2
> UPDATE [CategoryList]
> SET [CategoryIndex]=1 WHERE [CategoryID] = 3
> SELECT * FROM dbo.CategoryList ORDER BY CategoryIndex
> GO
> Gives Output
> 1 0 First
> 3 1 Third
> 2 2 Second
> 4 4 Fourth
> Is this the best method of setting up a list so you can swap the ordering
> programmatically?

OK, so what you basically after is a sorter value who tells you in
which order to present the data?

I can't think of any radically different way of doing this, although
some varitions are possible, for instance using 100, 200 etc as values
initially. On the other hand, having a contiguous series, can actually
make it easier to maintain the list.

Insert a value at point n:

BEGIN TRANSACTION

UPDATE CategoryList SET CategoryIndex = CategoryIndex + 1
WHERE CategoryIndex >= @.Indexfornew

INSERT CategoryList(CategoryName, CategoryIndex)
VALUES (@.newname, @.Indexfornew)

COMMIT TRANSACTION

Delete an entry:

BEGIN TRANSACTION

SELECT @.indexforold = CategoryIndex FROM CategoryList
WHERE CategoryID = @.idtodelete

DELETE CategoryIndex FROM CategoryList WHERE CategoryID = @.idtodelete

UPDATE CategoryList SET CategoryIndex = CategoryIndex - 1
WHERE CategoryIndex > @.indexforold

COMMIT TRANSACTION

Move an entry to position @.n:

BEGIN TRANSACTION

SELECT @.currentindex = CategoryIndex FROM CategoryList
WHERE CategoryID = @.idtomove

UPDATE CategoryIndex SET CategoryIndex = 10000000
WHERE CategoryID = @.idtomove

IF @.n > @.currentindex
BEGIN
UPDATE CategoryList SET CategoryIndex = CategoryIndex - 1
WHERE CategoryIndex > @.currentindex AND CategoryIndex <= @.n
END
ELSE
BEGIN
UPDATE CategoryList SET CategoryIndex = CategoryIndex + 1
WHERE CategoryIndex < @.currentindex AND CategoryIndex >= @.n
END

UPDATE CategoryIndex SET CategoryIndex = 10000000
WHERE CategoryID = @.n

COMMIT TRANSACTION

Here I have assumed that you don't to have ties, and thus a UNIQUE
constraint on the index column is a good idea.

All the above is untested - you should have some fun too! :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,

This looks pretty much what I'm after. I'll try it out ASAP and tune as
required. Grateful thanks.

Jack