Encrypting the configuration file values stored in SQL server

Hi All,

I have the following requirement. I need to store the password for the connection manager in the configuration file. The sink for the configuration file is SQL Server. Though the password field appears as "******" the actual value is being taken as ""******" itself. If i update the SQL server table with the correct value, then the package starts working. But, the password is shown as clear text.

If i write logic to encrypt the password column in the configuration table, is there a way to tell the SSIS execute engine to decrypt the password before using the same for making the connection.

Is there a place holder, where i can write the decrypt code so that the decrypted password can be sent to the execution engine?

Thanks In Advance,

Madhu

I think the short answer to this is no, and no code hooks either.

I think though that there is also an argument, that says it would not be more secure than what you have now. If you encrypt the data, you need to then secure the key. So what will you do to secure the key? Why not use strong security to secure the password data instead of worrying about how to secure the key? I accept that the encryption adds an extra step, but I'm not convinced it will actually be any safer.

|||I'm not sure if it's a good idea, but couldn't he create a script task to decrypt the password and reset the connection manager's connectionstring property before the connection manager is used in the package?|||

Yes and no. Some connections are used before your script task could run, such as connections used for logging.

How would you secure the key used to decrypt the password? You need to secure the encryption/decryption key, so why not just secure the password to start with?

|||DarrenSQLIS is right the recommended way to do this is to store the password in the connection. SSIS will automatically encrypt these so that they are not stored in cleartext.|||Thanks for the thoughts Darren. As suggested by you, way to go is to store the password in SQL server and make sure that the access to the configuration table is only for administrators.|||

Denise, I think you are talking about the package level encryption, protection levels and such like. Nice though it is, it is not very useful, as I think you should "externalise" any kind of security information.

Using package encryption becomes unfeasible when you have to migrate packages between environments. Configurations solve that migration issue, but don't give you the encryption that is often seen as a requirement for some organisations. I'd argue that is should not be a big deal, secure the password so you don't have to worry about the key, but often it is an internal "standard" that must be complied with.

Still we have the choice of package encryption, which is better than not!

Encrypting Data in SQL 2000

What is the recommended way to store data in SQL Server 2000 encrypted so
not even DBA can see the values? I know SQL 2005 has encryption at the row
level, but what are my options in SQL 2000?
Thank you,
SteveVery limited.
If using a .NET applicaiton, there is a rich encryption library that could
be used at the application level to encrypt data before it is stored in the
server, and decrypt it after if is retreived.
And there are several third party 'Add-ins' to allow encryption via extended
procedures.
A very crude 'hiding (and generally ineffective) scheme would be to use one
of the checksum() functions and do a one-way process. You couldn't 'decrypt'
it but you could use a comparison to verify if an input value matches.
As far as I know, the application level method is the only way to keep a
determined DBA (or Admin) from being able to access the encrypted data AND
the means to decrypt it. (But then, the question is "Is that a wise
decision?, Are you feeling lucky? Go ahead, make my ..."
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Steven" <Lazans@.mskcc.org> wrote in message
news:eDdtTMrpGHA.4812@.TK2MSFTNGP04.phx.gbl...
> What is the recommended way to store data in SQL Server 2000 encrypted so
> not even DBA can see the values? I know SQL 2005 has encryption at the
> row level, but what are my options in SQL 2000?
> Thank you,
> Steve
>|||What about Microsoft's Crypto API?
Steve
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:e8qenSrpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> Very limited.
> If using a .NET applicaiton, there is a rich encryption library that could
> be used at the application level to encrypt data before it is stored in
> the server, and decrypt it after if is retreived.
> And there are several third party 'Add-ins' to allow encryption via
> extended procedures.
> A very crude 'hiding (and generally ineffective) scheme would be to use
> one of the checksum() functions and do a one-way process. You couldn't
> 'decrypt' it but you could use a comparison to verify if an input value
> matches.
> As far as I know, the application level method is the only way to keep a
> determined DBA (or Admin) from being able to access the encrypted data AND
> the means to decrypt it. (But then, the question is "Is that a wise
> decision?, Are you feeling lucky? Go ahead, make my ..."
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Steven" <Lazans@.mskcc.org> wrote in message
> news:eDdtTMrpGHA.4812@.TK2MSFTNGP04.phx.gbl...
>|||You would need to use some type of third party encryption.
Check the links under field level encryption in the
following FAQ:
http://www.sqlsecurity.com/FAQs/SQL...55/Default.aspx
-Sue
On Thu, 13 Jul 2006 15:32:26 -0400, "Steven"
<Lazans@.mskcc.org> wrote:

>What is the recommended way to store data in SQL Server 2000 encrypted so
>not even DBA can see the values? I know SQL 2005 has encryption at the row
>level, but what are my options in SQL 2000?
>Thank you,
>Steve
>|||Yes, that could be used. (I'm not sure if it's status is still considered
secure...)
You may wish to check the resourses at:
http://www.sqlsecurity.com/FAQs/SQL...55/Default.aspx
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Steven" <Lazans@.mskcc.org> wrote in message
news:ucwNjZrpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> What about Microsoft's Crypto API?
> Steve
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:e8qenSrpGHA.3324@.TK2MSFTNGP05.phx.gbl...
>|||Just to clarify: CryptoAPI is a set of encryption functions available in
Windows that expose standard cryptographic algorithms. .Net encryption
routines offer a higher level interface to CryptoAPI. The encryption in SQL
Server 2005 is also based on CryptoAPI. Any Windows application can use
these routines. For a list of the algorithms available through CryptoAPI,
you can take a look at:
http://msdn.microsoft.com/library/d...
y/alg_id.asp.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:O2IubprpGHA.4268@.TK2MSFTNGP04.phx.gbl...
> Yes, that could be used. (I'm not sure if it's status is still considered
> secure...)
> You may wish to check the resourses at:
> http://www.sqlsecurity.com/FAQs/SQL...55/Default.aspx
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Steven" <Lazans@.mskcc.org> wrote in message
> news:ucwNjZrpGHA.3324@.TK2MSFTNGP05.phx.gbl...
>|||Thanks Laurentin, for the clarification about CRYPTOAPI library.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Laurentiu Cristofor [MSFT]" <laur@.nospam.com> wrote in message
news:elpC%23vspGHA.4188@.TK2MSFTNGP04.phx.gbl...
> Just to clarify: CryptoAPI is a set of encryption functions available in
> Windows that expose standard cryptographic algorithms. .Net encryption
> routines offer a higher level interface to CryptoAPI. The encryption in
> SQL Server 2005 is also based on CryptoAPI. Any Windows application can
> use these routines. For a list of the algorithms available through
> CryptoAPI, you can take a look at:
> http://msdn.microsoft.com/library/d...ity/alg_id.asp.
> Thanks
> --
> Laurentiu Cristofor [MSFT]
> Software Design Engineer
> SQL Server Engine
> http://blogs.msdn.com/lcris/
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:O2IubprpGHA.4268@.TK2MSFTNGP04.phx.gbl...
>|||Here are some free XP's that perform column-level encryption using the
CryptoAPI:
http://www.sqlservercentral.com/col...oolkitpart1.asp
"Steven" <Lazans@.mskcc.org> wrote in message
news:ucwNjZrpGHA.3324@.TK2MSFTNGP05.phx.gbl...
> What about Microsoft's Crypto API?
> Steve
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:e8qenSrpGHA.3324@.TK2MSFTNGP05.phx.gbl...
>

Encrypting column values

I want to store a function in SQL Server database that when called during insertion, encrypts a value and then stores encrypted text in the column.

Is there any built in Encrypting and Hashing mechanism in SQL 2005.

There is very 'rich' encryption capabilities in SQL 2005.

You may wish to start by referring to Books Online, Topic: Cryptographic Functions

Encrypted values are different although the source is the same

Hi
I am in the process of evaluating the SQL 2005 data encryption. I have
noticed something very strange and am hoping that someone would be able to
clear it up for me.
I have a table that holds credit card numbers. It is possible that there
will be two or more rows with the same credit card number.
I have created my master key:
create master key encryption by password =
'***************************************
********'
GO
I have created my certificate:
create certificate cert_sk_admin with subject = 'Certificate for accessing
symmetric keys';
GO
I have created my symmetric key:
create symmetric key sk_CreditCard with algorithm = aes_128 encryption by
certificate cert_sk_admin;
GO
I have added a new column to the credit card table (lets call it CCNO_Enc.
The existing column is CCNO).
I then update the new column:
UPDATE CREDITCARD
SET CCNO_Enc = EncryptByKey(Key_GUID('sk_CreditCard'), CCNO)
GO
The Problem:
If I now query this table (select CCNO, CCNO_Enc FROM CREDITCARD WHERE CCNO
= '123456789'), I get two records back (there are two rows with this CCNO).
The problem is that the encrypted value of the two rows is different? How
can this be if the source value is the same. Our problem with this is that
searching on an encrypted column is very slow(when performing the
decryption). We would like to encrypt the search criteria and use that to do
a direct comparison on the encrypted field (without decrypting it in the
where clause)
The Questions:
1. Why are the encrypted values different?
2. Can this be changed?
Thanks
CraigCB wrote:
> Hi
> I am in the process of evaluating the SQL 2005 data encryption. I have
> noticed something very strange and am hoping that someone would be
> able to clear it up for me.
> I have a table that holds credit card numbers. It is possible that
> there will be two or more rows with the same credit card number.
> I have created my master key:
> create master key encryption by password =
> '***************************************
********'
> GO
> I have created my certificate:
> create certificate cert_sk_admin with subject = 'Certificate for
> accessing symmetric keys';
> GO
> I have created my symmetric key:
> create symmetric key sk_CreditCard with algorithm = aes_128
> encryption by certificate cert_sk_admin;
> GO
>
> I have added a new column to the credit card table (lets call it
> CCNO_Enc. The existing column is CCNO).
> I then update the new column:
> UPDATE CREDITCARD
> SET CCNO_Enc = EncryptByKey(Key_GUID('sk_CreditCard'), CCNO)
> GO
> The Problem:
> If I now query this table (select CCNO, CCNO_Enc FROM CREDITCARD
> WHERE CCNO = '123456789'), I get two records back (there are two rows
> with this CCNO). The problem is that the encrypted value of the two
> rows is different? How can this be if the source value is the same.
> Our problem with this is that searching on an encrypted column is
> very slow(when performing the decryption). We would like to encrypt
> the search criteria and use that to do a direct comparison on the
> encrypted field (without decrypting it in the where clause)
> The Questions:
> 1. Why are the encrypted values different?
> 2. Can this be changed?
> Thanks
> Craig
This has to do with AES (Rijndael) encryption and its use of something
called cipher-block chaining. That is, the same plain text can be
encrypted with the same key, producing different cipher text. This
thread might explain it in more detail than I can provide:
http://www.eggheadcafe.com/ng/micro...]www.imceda.com
www.quest.com|||The encryption is salted. There are several severe problems with encryption
if that is not done.
Therefore you cannot use encrypted values as index. You could use hash of
the clear text value as index, but that would also weaken you security
(again, if one knows a clear text value, it can find the corresponding
encrypted value in the database).
For a description of salting (or initialization vector) see
http://www.rsasecurity.com/rsalabs/node.asp?id=2171
HTH,
~ Remus
"CB" <craig.bryden@.derivco.com> wrote in message
news:%231gDknM9FHA.4076@.tk2msftngp13.phx.gbl...
> Hi
> I am in the process of evaluating the SQL 2005 data encryption. I have
> noticed something very strange and am hoping that someone would be able to
> clear it up for me.
> I have a table that holds credit card numbers. It is possible that there
> will be two or more rows with the same credit card number.
> I have created my master key:
> create master key encryption by password =
> '***************************************
********'
> GO
> I have created my certificate:
> create certificate cert_sk_admin with subject = 'Certificate for accessing
> symmetric keys';
> GO
> I have created my symmetric key:
> create symmetric key sk_CreditCard with algorithm = aes_128 encryption by
> certificate cert_sk_admin;
> GO
>
> I have added a new column to the credit card table (lets call it CCNO_Enc.
> The existing column is CCNO).
> I then update the new column:
> UPDATE CREDITCARD
> SET CCNO_Enc = EncryptByKey(Key_GUID('sk_CreditCard'), CCNO)
> GO
> The Problem:
> If I now query this table (select CCNO, CCNO_Enc FROM CREDITCARD WHERE
> CCNO = '123456789'), I get two records back (there are two rows with this
> CCNO). The problem is that the encrypted value of the two rows is
> different? How can this be if the source value is the same. Our problem
> with this is that searching on an encrypted column is very slow(when
> performing the decryption). We would like to encrypt the search criteria
> and use that to do a direct comparison on the encrypted field (without
> decrypting it in the where clause)
> The Questions:
> 1. Why are the encrypted values different?
> 2. Can this be changed?
> Thanks
> Craig
>

Encrypted value shown as '?' in a column of type varchar

Dear All,

I inserted a record in table on DB created on SQLServer 2005 and found out that the one of the column values is shown as '?' instead of showing the encrypted value that I sent with the insert statement.3

............................ Can anyone tell me how to get rid of this?

Thanks and regards,

Z Z.

How are you encrypting the data and how are you retrieving it?|||Thanks for your reply. Actually I'm using only one-way encryption and seen these '?' through SQL Server Management Studio by directly viewing the table contents.|||So what are you expecting to see returned if you are using one way encryption?|||

I was expecting to see encrypted value when I opened the database table directly from within the SQL Server Management Studio. Instead I found '?' only. Anyway, I am done with it and used two encryption mechanism. Thanks a lot.

Encrypt data in a Stored Procedure

I am trying to insert data in a table using a stored procedure, but somehow I cannot store the values passed by the stored procedure in the table.

Table has two fields FIRST_NAME, LAST_NAME with varbinary data type(I need to encrypt the data)

My stored procedure is as follows. Please let me know what i am doing wrong!

***************************************************************

ALTER PROCEDURE [dbo].[SP_InsertInfo]
-- Add the parameters for the stored procedure here

@.FIRST_NAME varBINARY(100)
,@.LAST_NAME varBINARY(100)

AS
OPEN SYMMETRIC KEY key DECRYPTION BY CERTIFICATE cert

BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here

Insert into [dbo].[INFO] (FIRST_NAME, LAST_NAME)
Values ( encryptbykey( key_guid('key'),'@.FIRST_NAME'),
encryptbykey( key_guid('key'),'@.LAST_NAME')
)
close SYMMETRIC KEY key

END

**********************************************
EXEC sp_InsertInfo 'larry', 'Smith'

when I run the SP, the data stored in the first_name, last_name fields are @.FIRST_NAME', @.LAST_NAME' instead of larry, smith respectively.

Thanks

Insert into [dbo].[INFO] (FIRST_NAME, LAST_NAME)
Values ( encryptbykey( key_guid('key'),'@.FIRST_NAME'),
encryptbykey( key_guid('key'),'@.LAST_NAME')
)

You have single quotes around the variable names. This leads to SQL Server to see them as characters and not variables.

Replace the above with this instead

Insert into [dbo].[INFO] (FIRST_NAME, LAST_NAME)
Values ( encryptbykey( key_guid('key'),@.FIRST_NAME),
encryptbykey( key_guid('key'),@.LAST_NAME)
)

No magic, I only remove ' around @.FIRST_NAME and @.LAST_NAME

|||Thanks Andreas for your reply|||

ks06,

did it help you? Please mark the reply as answer if that is the case.

Empty values in XML

Hi,
Trying to pass XML as parameter to stored procedure and get the result set.
I want to allow null values. Here is the problem: when I leave a value blank
(between the <Score> and </Score> tags) it will return 0 instead of <NULL>
... See code below.
What am I doing wrong here?
Thanks in advance!
Joost
CREATE PROCEDURE dbo.Test
AS
DECLARE @.i int
DECLARE @.ScoresXML nvarchar (4000)
SET @.ScoresXML = '<Scores><Score><ID>1</ID><Result></Result></Score></Scores>'
EXEC sp_xml_preparedocument @.i OUTPUT, @.ScoresXML
SELECT
ID,
Result
FROM
OPENXML (@.i, '/Scores/Score', 2)
WITH
(IDint,
Result float)
EXEC sp_xml_removedocument @.i
Hello JoostK,
SQL Server 2000 or SQL Server 2005?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||SQL 2000
Thanks,
Joost
"Kent Tegels" <ktegels@.develop.com> schreef in bericht
news:b87ad741c6fb8c81b916f5cc940@.news.microsoft.co m...
> Hello JoostK,
> SQL Server 2000 or SQL Server 2005?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||Hi Kent,
Using SQL2000
Thanks, Joost
"Kent Tegels" wrote:

> Hello JoostK,
> SQL Server 2000 or SQL Server 2005?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>
|||Hello JoostK,

> Hi,
> Trying to pass XML as parameter to stored procedure and get the result
> set. I want to allow null values. Here is the problem: when I leave a
> value blank (between the <Score> and </Score> tags) it will return 0
> instead of <NULL> ... See code below.
> What am I doing wrong here?
In your table, you're asking "what's the value of the Score element", which
isn't null because it does exist. What you want is "what's the value of
the text node of the element Score". To fix this, all you need do is write
your table as:
WITH
(IDint,
Result int 'text()')
And it should give you what you want.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Empty values in XML

Hi,
Trying to pass XML as parameter to stored procedure and get the result set.
I want to allow null values. Here is the problem: when I leave a value blank
(between the <Score> and </Score> tags) it will return 0 instead of <NULL>
... See code below.
What am I doing wrong here?
Thanks in advance!
Joost
CREATE PROCEDURE dbo.Test
AS
DECLARE @.i int
DECLARE @.ScoresXML nvarchar (4000)
SET @.ScoresXML = '<Scores><Score><ID>1</ID><Result></Result></Score></Scores
>'
EXEC sp_xml_preparedocument @.i OUTPUT, @.ScoresXML
SELECT
ID,
Result
FROM
OPENXML (@.i, '/Scores/Score', 2)
WITH
(ID int,
Result float)
EXEC sp_xml_removedocument @.iHello JoostK,
SQL Server 2000 or SQL Server 2005?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||SQL 2000
Thanks,
Joost
"Kent Tegels" <ktegels@.develop.com> schreef in bericht
news:b87ad741c6fb8c81b916f5cc940@.news.microsoft.com...
> Hello JoostK,
> SQL Server 2000 or SQL Server 2005?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||Hi Kent,
Using SQL2000
Thanks, Joost
"Kent Tegels" wrote:

> Hello JoostK,
> SQL Server 2000 or SQL Server 2005?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Hello JoostK,

> Hi,
> Trying to pass XML as parameter to stored procedure and get the result
> set. I want to allow null values. Here is the problem: when I leave a
> value blank (between the <Score> and </Score> tags) it will return 0
> instead of <NULL> ... See code below.
> What am I doing wrong here?
In your table, you're asking "what's the value of the Score element", which
isn't null because it does exist. What you want is "what's the value of
the text node of the element Score". To fix this, all you need do is write
your table as:
WITH
(ID int,
Result int 'text()')
And it should give you what you want.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

empty values in SQL sentence

i have insert/update SQL sentence, but sometimes there are empty values because there not required in the database so sometimes the sql sentence look this way:

INSERT INTO EquipmentAndPlace (EquipmentID,EquipmentEmdaNo,EquipmentPlace,EquipmentIDForRecognize, EquipmentRemarks,EquipmentLastChecked) VALUES ('3','','2','1','','12/1')

with empty values, but then it doent update in the dataBase-only if all the values appear-
what the solution of it?
ThanksCan you show us the whole part of your insert code. what kind of methods you used.

Alotaibi|||hi, u could build a dynamic SQL for this kind of problem. A stored procedure would be the best bet but let me know if u are using a sp or a SQL query inside your code. depending upon the values u pass to the stored proc, u could add the fields that need to be there in ur query. let me know if u want me to send the sp.|||for example in this code only if i have all the values its work-otherwise it doesnt update-the same thing happend if i want to insert new row data to dataBase.
shravan79 - im a beginner in ASP .net-i dint work yet with stored procedure , my senteces are SQL sentences...
as i said-this value are not requried in the database

 function UpdateDataStore(e as DataGridCommandEventArgs) _
 as boolean
 dim i,j as integer
 dim sDate1,sDone,sPeopleName,sResponse,sNextDate,sImidiate as string
 dim strText,sDate2,sAmlazot,sAct,sDesc as string
 dim blnGo as boolean = true
 dim lbGroupTemp,lbPlaceTemp,lbTypeTemp,lbLevelTemp as ListBox
 dim lbCostTemp,lbResponsableTemp,lbMainGroup As ListBox
 sDate1 = CType(e.Item.Cells(2).Controls(0), TextBox).Text
 sDone = CType(e.Item.Cells(3).Controls(1), checkBox).checked
 sPeopleName = CType(e.Item.Cells(4).Controls(0), TextBox).Text
 sResponse = CType(e.Item.Cells(5).Controls(0), TextBox).Text
 sNextDate = CType(e.Item.Cells(6).Controls(0), TextBox).Text
 sDate2 = CType(e.Item.Cells(9).Controls(0), TextBox).Text
 sImidiate = CType(e.Item.Cells(10).Controls(1), checkBox).checked
 sAmlazot = CType(e.Item.Cells(13).Controls(1), TextBox).Text
 sAct = CType(e.Item.Cells(14).Controls(1), TextBox).Text
 sDesc = CType(e.Item.Cells(19).Controls(1), TextBox).Text
 'get the list box info
 lbGroupTemp=e.Item.Cells(23).Controls(1)
 if lbGroupTemp.SelectedIndex = -1 then
 lbGroupTemp.SelectedIndex=0
 end if
 'get the list box info
 lbPlaceTemp=e.Item.Cells(21).Controls(1)
 if lbPlaceTemp.SelectedIndex = -1 then
 lbPlaceTemp.SelectedIndex=0
 end if
 'get the list box info
 lbTypeTemp=e.Item.Cells(18).Controls(1)
 if lbTypeTemp.SelectedIndex = -1 then
 lbTypeTemp.SelectedIndex=0
 end if
 'get the list box info
 lbLevelTemp=e.Item.Cells(16).Controls(1)
 if lbLevelTemp.SelectedIndex = -1 then
 lbLevelTemp.SelectedIndex=0
 end if
 'get the list box info
 lbCostTemp=e.Item.Cells(12).Controls(1)
 if lbCostTemp.SelectedIndex = -1 then
 lbCostTemp.SelectedIndex=0
 end if
 'get the list box info
 lbResponsableTemp=e.Item.Cells(8).Controls(1)
 if lbResponsableTemp.SelectedIndex = -1 then
 lbResponsableTemp.SelectedIndex=0
 end if
 'get the list box info
 lbMainGroup=e.Item.Cells(25).Controls(1)
 if lbMainGroup.SelectedIndex = -1 then
 lbMainGroup.SelectedIndex=0
 end if
 dim strSQL as string = "UPDATE SSFactory SET " & _
 "SSTypeID = '" & lbGroupTemp.selectedValue & "'," & _
 "PlaceId = '" & lbPlaceTemp.selectedValue & "'," & _
 "SSMaintypeID = '" & lbMainGroup.selectedValue & "'," & _
 "SSDesc = '" & sDesc & "'," & _
 "SSTypeID1 = '" & lbTypeTemp.selectedValue & "'," & _
 "SSShouldHandleID = '" & lbLevelTemp.selectedValue & "'," & _
 "SSHamlatzot = '" & sAmlazot & "'," & _
 "SSCostID = '" & lbCostTemp.selectedValue & "'," & _
 "SSDateToHandle = '" & sDate2 & "'," & _
 "SSResposible = '" & sResponse & "'," & _
 "SSFactoryAction = '" & sAct & "'," & _
 "SSPeopleName = '" & sPeopleName & "'," & _
 "SSDateWasDone = '" & sDate1 & "'," & _
 "SSDateShouldCheck = '" & sNextDate & "'" & _
 " WHERE SSFactoryID = " & dgData.DataKeys(e.Item.ItemIndex)
 response.write(strSQL)
 'exit function
 ExecuteStatement(strSQL)
 return blnGo
 end function

Thanks a lot!!!!|||please someone-i dont understand why if i have empty values its doesnt work...HELP!!

Empty <> IS NOT NULL

Hello,

I have a query that returns the appropriate values I need, however there is one field I'd like to add and utilize but my problem is I only want to use it if it contains data.

If I filter it with IS NOT NULL it returns all the records, including the empty records. The field is simply empty, and doesn't come back as NULL. If I filter it with =' ' , it shows all the records with the empty records only.

I need to do the opposite, be able to filter it only if it's not empty.

Any help would be appreciated.

What is your current query? ( And indicate the field you're talking about... including its type, I guess...)

>L<

|||

Figured it out myself.

For someone elses benefit I'll include what I had to do:

Filtered the desired field using LIKE '0%' .

The data the field actually contains is a yearcode-sequence code (eg. 07-555555).

Now the empty fields are gone and it only pulls the records if it has data.

I used this for a guide. http://msdn2.microsoft.com/en-us/library/ms179859.aspx

|||Thanks for replying, I figured it out.