Database Discussion Boards

Re: Storing Compressed Image Into SQL Server

Mark Churchill16-Mar-08 1:57

16-Mar-08 1:57

Just out of interest, why are you using Gzip? Is it a requirement that the images are stored in a lossless format?

Have you considered gif or png? Both of these are designed to compress images...

Mark Churchill
Director
Dunn & Churchill
Free Download:
Diamond Binding: The simple, powerful, reliable, and effective data layer toolkit for Visual Studio.

insert user checkboxlist selections in table

C. L. Phillip14-Mar-08 4:26

C. L. Phillip

14-Mar-08 4:26

I have a single asp.net page with a checkboxlist to allow multiple selections for a field. On the database side i have a "main table" that is used to store log data the user types in. I have a table that is bound to the checklistbox with the selection options. finally i have a table to store the associated selections, especially it stores foreign keys for the selection options id, and the associated log id.

So my problem is .... how do i properly construct the insert so that I can insert the log into the "main table" and also the various checkboxlist selections into the other table with a single insert ?

Re: insert user checkboxlist selections in table

Mark J. Miller14-Mar-08 6:28

Mark J. Miller

14-Mar-08 6:28

Assuming the only insert you need to make is to your selected options table (Log is already created, no edits required to the options table because they are simply for displaying available options), you can try something like this:

http://www.codeproject.com/KB/database/TableValuedFnsAsArrays.aspx[^]

Create a TVF which uses either the T-SQL or CLR method described in the article and pass the selected options and the log id to a stored procedure which uses it like this:

<br />
CREATE PROCEDURE dbo.AddSelectedOptions<br />
@LogId INT,<br />
@options VARCHAR(??)<br />
AS<br />
<br />
INSERT INTO selectedOptions(LogId, OptionId)<br />
SELECT @LogId, Data FROM dbo.function_string_to_table(@options)<br />
<br />
GO<br />

NOTE: "Data" would be the optionId as it is returned from the function defined in the article.

Mark's blog: developMENTALmadness.blogspot.com

Re: insert user checkboxlist selections in table

C. L. Phillip14-Mar-08 9:36

C. L. Phillip

14-Mar-08 9:36

oh great. thank you so much!!

Please help me move these records to another table...

Support12314-Mar-08 3:10

Support123

14-Mar-08 3:10

hi all,

I am trying to copy the records from one table to another like this...

INSERT INTO [Claims].[dbo].[DocumentData]<br />
           ([DocumentId]<br />
           ,[DocumentData]<br />
           ,[DocumentName]<br />
           ,[ContentType]<br />
           ,[LastUser]<br />
           ,[LastDate])<br />
     VALUES<br />
(SELECT DocumentQueueId, DocumentData, DocumentName, ContentType, AssignedTo, GetDate() FROM DocumentQueue WHERE (AssignedTo = 'nbad\SlabbertH'))

yet i get an error:

Msg 156, Level 15, State 1, Line 14<br />
Incorrect syntax near the keyword 'SELECT'.<br />
Msg 102, Level 15, State 1, Line 14<br />
Incorrect syntax near ')'.

what is the correct syntax for copying the records from one table to another?

After i copied the records to the other table i will truncate the previouse table....

"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison

Re: Please help me move these records to another table...

soni uma14-Mar-08 3:20

soni uma

14-Mar-08 3:20

Remove 'Values' for select statement and also one ')' at the end of select statement

like

insert into Table()
(select from table1)

Cool | :cool:

Re: Please help me move these records to another table...

Support12314-Mar-08 3:23

Support123

14-Mar-08 3:23

thank you... if this works... it would be a great start to a great weekend Smile | :)

"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison

Re: Please help me move these records to another table...

soni uma14-Mar-08 3:26

soni uma

14-Mar-08 3:26

It's my pleasure to help you.

Cool | :cool:

Re: Please help me move these records to another table...

Support12314-Mar-08 3:25

Support123

14-Mar-08 3:25

Thank you,

That did the trick... Smile | :)

"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison

Fetching records based on Boolean operators

sumit703414-Mar-08 0:41

sumit7034

14-Mar-08 0:41

hi i want to fetch records from table where values in the column can be

(((joan AND smith) OR (sally AND rogers) OR ((pete OR peter) AND miller)) AND ((barbeque OR bbq OR bb-q) OR sauce OR chicken OR chikn OR flavor))

Re: Fetching records based on Boolean operators

GDMFSOB14-Mar-08 0:48

GDMFSOB

14-Mar-08 0:48

firstly what DB are you using MySql Oracle Access or MS SQL

Re: Fetching records based on Boolean operators

sumit703414-Mar-08 0:55

sumit7034

14-Mar-08 0:55

I M using MS SQL

Re: Fetching records based on Boolean operators

GDMFSOB14-Mar-08 1:07

GDMFSOB

14-Mar-08 1:07

I think this will help.

SELECT Col1, ColToSearch
FROM table
WHERE CONTAINS (ColToSearch, '"quick easy" or "Help"')

you must have full text index setup on the table field you are searching

Table change Watch In SQL 2000

GDMFSOB14-Mar-08 0:39

GDMFSOB

14-Mar-08 0:39

Hi, Is there anyway to have SQL Server 2000 run a function on a database if a specific table is updated or a row added?

my problem is this I am having to work with two seperate databses for 2 sepreate programs, I need data from the first program in my second program,
but if anybody adds a new row or record to my table in database 1, I want to update the table in my second database using an sql server auto function, and the server must run a change on db2 if the table in db 1 is changed.

I hope this made sense, the main reason for doing this I do not have access to the source code for the first program so this must happen on the DB side.

Re: Table change Watch In SQL 2000

sumit703414-Mar-08 1:01

sumit7034

14-Mar-08 1:01

i dont know exactly but i think it can be done with the help of Triggers

Re: Table change Watch In SQL 2000

GDMFSOB14-Mar-08 1:08

GDMFSOB

14-Mar-08 1:08

thanks man, I been searching the whole day for SQL watch, not trigger lol thanks man.

How to call Unmanaged Code from SQL CLR.

ivix4u14-Mar-08 0:18

ivix4u

14-Mar-08 0:18

I have written a C# dll which makes a call to some unmanaged code.
When i tried to register this dll on SQL server 2005 i got the following error.

Msg 6215, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because method 'AddNumbers' on type 'Project1._Class1' in safe assembly 'Interop.Project1' has invalid attribute 0x1003.

How do i register such a dll.

Re: How to call Unmanaged Code from SQL CLR.

Mark J. Miller14-Mar-08 6:20

Mark J. Miller

14-Mar-08 6:20

You need to register the assembly as unsafe (project properties) and you'll need to configure SQL Server to allow unsafe assemblies (database must be marked as TRUSTWORTHY and you must have UNSAFE ASSEMBLY permissions). Everything you need to know should be in this article: http://www.codeproject.com/KB/database/SQLCLRIntegrationSecurity.aspx[^]

Mark's blog: developMENTALmadness.blogspot.com

Problem with Cursor

Vimalsoft(Pty) Ltd13-Mar-08 22:19

Vimalsoft(Pty) Ltd

13-Mar-08 22:19

Good Morning All

i have the Following Cursor that Displays data , like this

<br />
SET NOCOUNT ON<br />
Declare @Property_ID int ,<br />
@Lis_key Varchar(12),<br />
@Effective_DAte Datetime,<br />
@Func_key Varchar(12),<br />
@Property_ID2 int ,<br />
@Lis_key2 Varchar(12),<br />
@Effective_DAte2 Datetime,<br />
@Func_key2 Varchar(12)<br />
<br />
Declare Find_Val  Cursor for <br />
select p.Property_ID, p.Lis_key,v.Effective_Date,<br />
p.Func_key from Valuation v<br />
Inner Join Property p <br />
on p.Property_ID = v.Property_ID<br />
where V.Effective_date ='2002/07/01'<br />
And v.Val_Status_ID  = 2<br />
And v.Archive_Date is NUll<br />
And p.Archive_Date is Null<br />
<br />
Open Cursor Find_Val<br />
<br />
FETCH NEXT FROM Find_Val INTO<br />
@Property_ID,<br />
@Lis_key,<br />
@Effective_DAte,<br />
@Func_key<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
SELECT @Property_ID,@Lis_key , @Effective_Date , @Func_key<br />
<br />
--Declare<br />
DECLARE Second_cursor CURSOR FOR <br />
select V.Property_ID, P.Lis_key,v.Effective_Date,<br />
p.Func_key from Valuation v, Property p<br />
where v.Property_ID = @Property_ID<br />
And EFFECTIVE_DATE = '2008/07/01'<br />
<br />
OPEN Second_cursor<br />
<br />
FETCH NEXT FROM Second_cursor INTO<br />
<br />
@Property_ID2,<br />
<br />
@Lis_key2,<br />
<br />
@Effective_DAte2,<br />
<br />
@Func_key2<br />
<br />
IF @@FETCH_STATUS <> 0 <br />
<br />
WHILE @@FETCH_STATUS = 0<br />
<br />
BEGIN<br />
<br />
SELECT @Property_ID2,<br />
<br />
@Lis_key2,<br />
<br />
@Effective_DAte2,<br />
<br />
@Func_key2<br />
<br />
<br />
FETCH NEXT FROM product_cursor INTO <br />
<br />
@Property_ID2,<br />
<br />
@Lis_key2,<br />
<br />
@Effective_DAte2,<br />
<br />
@Func_key2<br />
<br />
END<br />
<br />
CLOSE Second_cursor<br />
<br />
DEALLOCATE Second_cursor<br />
<br />
FETCH NEXT FROM Find_Val into<br />
@Property_ID,<br />
<br />
@Lis_key,<br />
<br />
@Effective_DAte,<br />
<br />
@Func_key<br />
<br />
End <br />
<br />
CLOSE Find_Val<br />
<br />
DEALLOCATE Find_Val<br />

And when i run this Cursor i get the Following Error

Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'Cursor'.

And this Points to this line

Open Cursor Find_Val

Thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Re: Problem with Cursor

pmarfleet13-Mar-08 22:31

pmarfleet

13-Mar-08 22:31