Re: SELECT and NOT IN a dynamic sequence - Database Discussion Boards

Re: Any security check to stop Importing Data from SQL tables into EXCEL

Steven Campbell20-Sep-04 7:32

20-Sep-04 7:32

There are good security practises that you can use to make sure that sensitive data (passwords, credit card numbers) etc are either encrypted or hashed in the database, so that a query cannot be used to view the data.

See this article for an example of this technique.

my blog

Re: Any security check to stop Importing Data from SQL tables into EXCEL

BSRK20-Sep-04 19:16

BSRK

20-Sep-04 19:16

Dear Sir, Thank you very much for the answer and also a very good & useful article.
Though I am happy with the suggested method, I would also like to know whether "Is there any way to stop this Importing Data in to EXCEL"
Thanking You

Re: Any security check to stop Importing Data from SQL tables into EXCEL

Mike Dimmick21-Sep-04 4:53

Mike Dimmick

21-Sep-04 4:53

Yes. Make the users log in to SQL Server using an unprivileged login - e.g. one that is a member of the public role but not a member of dbo. This is a best practice in any case. Do your password checking in a stored procedure. Grant EXEC permission for public to the stored procedure, and deny SELECT permission to the table containing the usernames and passwords.

Don't ever give out your sa password. Make it a strong one.

SQL Server's security features are pretty powerful but you have to use them. Far too many applications use sa to connect to the database. Applications and users should always be limited to only seeing the data and performing the operations you specify.

Stability. What an interesting concept. -- Chris Maunder

SELECT and NOT IN a dynamic sequence

Vadim Tabakman19-Sep-04 15:27

Vadim Tabakman

19-Sep-04 15:27

Hi all,

I need to be able to do a query for those values that are not in a particular field in a recordset.

So lets say I have a table with a field (indexnum) which is a text string of a hex number. eg "00034F12". That number is incremented for each record that is added. Legacy code, so I don't know why it is done this way.

What i need to do is query for all indexnum's that don't occur given a list of indexnums. Indexnums will be between 00000001 and 0x000FFFFF. These are not hardcoded, so at the time this code will be run, it maybe between 0034829F and 00349000. So some how I need something like :
SELECT indexnum from tblData NOT IN ( SELECT ... [build up a temp table or something or a sequence of number from the starting and ending nummbers I give it] )

Can this be done somehow? I need it to work on DB's like Oracle and MS Access, and I don't think Access supports stored procedure (although I don't know much about these).

Thanx

If there's one thing I've learned, it's that life is one crushing defeat after another until you just wish Flanders was dead. - Homer Simpson

Re: SELECT and NOT IN a dynamic sequence

Mike Ellison19-Sep-04 19:38

Mike Ellison

19-Sep-04 19:38

What about a simple WHERE clause on indexnum?

select indexnum
  from tblData
 where not (indexnum between @lowerBound and @higherBound)

or maybe

select indexnum
  from tblData
 where not (indexnum >= @lowerBound and indexnum <= @higherBound)

Re: SELECT and NOT IN a dynamic sequence

Vadim Tabakman21-Sep-04 14:10

Vadim Tabakman

21-Sep-04 14:10

This is me being lazy and not trying it, but asking a question first Smile | :)

Given that indexnum is a text string, will this comparison work? For example :

"00000007"
"00000008"
"0000000C"

if we ran the sql query for "00000007" to "0000000C", would it return :
"00000009"
"0000000A"
"0000000B"
????

I didn't think the database would be so smart as to count in hex using text strings.

If there's one thing I've learned, it's that life is one crushing defeat after another until you just wish Flanders was dead. - Homer Simpson

Re: SELECT and NOT IN a dynamic sequence

Mike Ellison22-Sep-04 13:16

Mike Ellison

22-Sep-04 13:16

Well, if your being lazy means more work for me, then I one-up your laziness with some of my own Smile | :)

Try it out and see what you get.

I think most databases consider "8,9,A,B,C" an ascending text sort.

Re: SELECT and NOT IN a dynamic sequence

Vadim Tabakman22-Sep-04 15:11

Vadim Tabakman

22-Sep-04 15:11

Well touche. I respect your lazyness and bow down to it. I've been out-slothed. I will therefore try this out Smile | :)

hehe

Thanx Mike.

If there's one thing I've learned, it's that life is one crushing defeat after another until you just wish Flanders was dead. - Homer Simpson

setting RowStateFilter after updating (ADO.NET/C#)

Hesham Amin17-Sep-04 22:41

Hesham Amin

17-Sep-04 22:41

Hi I have a databound ListBox and I edit a row as follows :

DataRowView Row=((DataRowView)lst.SelectedItem);
Row.BeginEdit();
Row["name"]=frmEdit.txtName.Text;
Row.EndEdit();

After the EndEdit the edited item disappears from the list..
So I'm trying to set the RowStateFilter of the dataview (that the listbox is boud to) and tried several combinations of the DataViewRowState enum but I can't make all rows appear (unchanged/added/changed)

Any help ???

DataTable SELECT option

dbetting17-Sep-04 8:06

dbetting

17-Sep-04 8:06

I normally use Oracle and I haven't been able to figure out the appropriate escape characters to allow the SELECT option to work properly for a given situation.

Given the following:

string newValue = "Joe's Place";<br />
string searchValue = newValue.Replace("'",@"\'");<br />
DataRow[] dr = dataTable.Select(displayMember+"='"+searchValue+"'");

newValue is actually a parameter into the method but when it's contents include an apostrophe, I get the error such as Missing operand after 's' operator. The code worked for all situations until I hit an apostrophe so I added the conversion of the apostrophe to what I thought was the appropriate escape sequence {newValue.Replace()}. But it doesn't seem to matter what value is searched, I still get the error.

What should I have done? Are there any other characters I need to be aware of other than the apostrophe?

Thanks

Re: DataTable SELECT option

Michael Potter17-Sep-04 10:24

Michael Potter

17-Sep-04 10:24

Have you tried:

string searchValue = newValue.Replace("'","''");

Re: DataTable SELECT option

dbetting17-Sep-04 16:54

dbetting

17-Sep-04 16:54

Yes, this gave a result of Joe''s Place but I still get the same error back from the database. That's why I thought I must need an escape character rather than doubling the apostrophe.

Installing MSDE on XP Pro?

Dam.NET17-Sep-04 6:22

Dam.NET

17-Sep-04 6:22

I have just installed VC ++ .Net and as part of this installation I was told to download a standalone SQL engine as I am sure you all know! but I then installed this and it jumped through all the hoops and completed successfully it said! but I get nothing, no icons, no sys tray icon nothing and IIS keeps telling me its not loaded when I try demo's out? any ideas would be appreciated, thank you for tolerating such a simple question but I am stumped!

Thanks for your time!

Many thanks for your time and may the force be with you! (you'll need it!) Smile | :)

Re: Installing MSDE on XP Pro?

Orcrist17-Sep-04 7:15

Orcrist

17-Sep-04 7:15

MSDE does not come with a user interface to manage the program (thats part of the reason it's free) Although SQL Server 2005 Express (same as MSDE) will apparently include one.

As for the problem:
MSDE is not installed out of the box when you check it in VS. It loads the MSDE setup file into a VS sub directory and that is all. You probably still need to run the setup explicitly for MSDE. I would NOT recommend running the MSDE from Visual Studio as it is probably out of date and you will need to patch to fix the SASSER vulnerability (updating is a pain with MSDE). I would download the current version from Microsoft Downloads and install that version. Now be very careful on reading the instructions for setup. Very specifically the choosing of the Instance name. Most Visual Studio samples tend to look for the instance $NETSDK (I think, cant remember) so you will have trouble connecting out of the box if the instance name does not match.

Having said that I prefer to actually change the instance name but that means changing all the code samples that point to the NETSDK instance. Your call. I would also recommend that you carefully read the security.

I would encourage installation in THIS order:
1. Check your services to see if you may have MSDE already installed (many software packages do install it without telling you)
2. Unpack MSDE
3. Creating the INI file described in the readme with both the Instance name and SQL server administrator password (the SA password)
4. then run the setup
5. Open your Services under the Administrative Tools menu and locate the MSSQL${InstanceName} entry in the list of services and change the properties to load on System Startup (If not already setup that way).
6. Download TeraTrax's MSDE manager (www.teratrax.com) - no affiliation - They have a free trial and if you intend to use MSDE a lot I would just buy it (I did). You can also use Access XP or later (I use Access 2003) as the client for data viewing, table creation, and other miscellaneous stuff but I would not use it to initially setup the database nor for backup or restore (I use TeraTrax for that) - personal preference.

I use MSDE ++ALOT++ and it is great for small to medium projects and clients. No problems from my end.

Re: Installing MSDE on XP Pro?

Orcrist17-Sep-04 7:21

Orcrist

17-Sep-04 7:21

Oh yes.. I forgot.
7. Delete the INI file after installation is complete (it has your SA password afterall!)

Re: Installing MSDE on XP Pro?

Dam.NET19-Sep-04 8:57

Dam.NET

19-Sep-04 8:57

Hi Orcrist,

Thanks for the help I have now installed SQL MSDE and its working great (thanks) I have also downloaded TeraTrax frontend and thats great. But I am really in a mess, I cannot make sense of the passwording and username.

Everytime I attempt to access the owner is ODB and I donot know the password for this user. Also when I attempt to create a user I cannot seem to set a password I think I am totally lost with the SQL security system.

Any chance of a quick run through with respect to setting passwords and users in TeraTrax and the interfacing it to the SqlConnect command? The TeraTrax manual is poor for evaluation do you get a better one when you buy it?

Thanks for all your help!

Many thanks for your time and may the force be with you! (you'll need it!) Smile | :)

Re: Installing MSDE on XP Pro?

Orcrist19-Sep-04 10:42

Orcrist

19-Sep-04 10:42

If I had to guess (and I am), I would hedge a bet that the ODB user is a login created by another application for MSDE and that this login is for some other instance of SQL Server (MSDE). You should be able to just ignore it.

He are some steps to try. Hopefully it will get you rolling:

1. In bottom left taskbar tray for Windows 2000/XP double click the SQL service Manager Icon. In the server name field enter {MACHINE NAME}\{MSDE Instance Name}. So for example if your machine name is "CLOUD" and you MSDE instance was named "MSDEDB" you would enter:
CLOUD\MSDEDB

I am not sure on the following steps since it has been months since I last had to setup Teratrax for the database.
2. Start Teratrax
3. Under File select Add server
4. In the server name enter: {MACHINE NAME}\{MSDE Instance Name}
for example CLOUD\MSDEDB
If everything is setup okay then you should be able to connect to the database instance, add logins and perform other activities. If you are particularly weak with SQL server then I would suggest that you download SQL Server Books Online from Microsoft downloads.
As for you connection strings to the database you could use something like the following for a OLEDB connection:

"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ConsignIT;Data Source=CLOUD\MSDEDB"

That should do it. If you are still having trouble then perhaps reinstall MSDE again. The ini file should be something like the following:

[Options]
INSTANCENAME=MSDEDB
SAPWD=mypassword

Other than that I can imagigine what other problem you might be running into.

good luck and let me know how it went.

David

Re: Installing MSDE on XP Pro?

Dam.NET19-Sep-04 10:47

Dam.NET

19-Sep-04 10:47

Thanks David!!!

I have now got MSDE working and am currently battling with the SqlDataReader component and the security systems with SQL!!

Thanks for the info David!!!!!!!! very much appreciated!!!!!!

Many thanks for your time and may the force be with you! (you'll need it!) Smile | :)

datagrid with large record

caheo17-Sep-04 5:03

caheo

17-Sep-04 5:03

Hi all

I have a datagrid tied up to sql server. Also I have a very large record
need to page during runtime. I have looked up solutions to find out how to
increase the performance of my application when paging. However I'm not
satisfied with those solutions.
I would like to ask if there is an optimmize solution out there so that I can
do the following: paging, sorting, update, insert and delete.

Thanks for any instructions

Plugin to Enterpriser Manager

cbmdk16-Sep-04 23:21

cbmdk

16-Sep-04 23:21

Hey,

i know this havent anything to do with sql queries.

But I've heard there should be a plugin of some sort to Enterprise Manager, that lets you dbl click on a table and you will instantly get shown all rows, instead of the properties of the table which nobody actually cares about.

anybody know where to get this plugin or how to make it?

How do i insert a blank value?

xiaowenjie16-Sep-04 17:54

xiaowenjie

16-Sep-04 17:54

hi, im doing a add function. it allows user to enter numeric value into
Ms access database. I have 24 text boxes for the user to enter their data. however, if the user do not complete entering all the data into this 24 textbox, how do i insert a blank value into the database.

Re: How do i insert a blank value?

Mekong River17-Sep-04 6:32

Mekong River

17-Sep-04 6:32

You need to check the each text box before it add the record to the database. If your text is null then don't add to your text box. Here is an example:

if isnull(text1.value) = false then rst.field(0).value = text1.value
if isnull(text2.value) = false then rst.field(1).value = text2.value
if isnull(text3.value) = false then rst.field(2).value = text3.value
...
if isnull(text24.value) = false then rst.field(23).value = text24.value

Try to do it and let me know if you have more problem.

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

Re: How do i insert a blank value?

xiaowenjie17-Sep-04 18:59

xiaowenjie

17-Sep-04 18:59

THanks. But how do i convert those statement into c#?

Chris

Re: How do i insert a blank value?

Mekong River18-Sep-04 3:40

Mekong River

18-Sep-04 3:40

I'm sorry nuttynibbles1984, I never learn about C# programming language Frown | :(

I prefer in VB6 and VB.NET. May be you need to post this question in VB/VB.Net or C# forum. Wink | ;)

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

Locking Database

WinDotNet16-Sep-04 0:53

WinDotNet

16-Sep-04 0:53

Hi All,

I have .net webservice [with sybase backend], which Adds/Updates/Deletes the customer details. Considering the services is deployed on server where database also resides, if more than two clients requests[a multi-user environment] the same Update service, problems regarding the database locking arises.

I think record-level locking would be the better solution for handling datase locking issues related to Updates. for Inserts and Deletes actions using page loaking would be better.

Transaction obejct in .net have Isolation enums which can simulate these , odes anyone incrporated these.

Can Somebody help me out to solve these situation or any related links is appreciated.

Regards,
Vinay

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.