Need to avoid repeated data in a DataGrid

Hi there :)

I am developing a system for my uni course and I am stuck a little problem...

Basically its all about lecturers, students modules etc - A student has many modules, a module has manu students, a lecturer has many modules and a module has many lecturers.

I am trying to get a list of lecturers that run modules associated with a particular student. I am able to get a list of the appropriate lecturers, but some lecturers are repeated because they teach more than one module that the student is associated with.

How can I stop the repeats?

Heres my sql select code in my cs file:

string sqlDisplayLec = "SELECT * FROM student_module sm, lecturer_module lm, users u WHERE sm.user_id=" + myUserid + "" + " AND lm.module_id = sm.module_id " + " AND u.user_id = lm.user_id ";
SqlCommand sqlc2 = new SqlCommand(sqlDisplayLec,sqlConnection);
sqlConnection.Open();
lecturersDG.DataSource = sqlc2.ExecuteReader(CommandBehavior.CloseConnection);
lecturersDG.DataBind();

And here is a pic of my Data Model:
Data Model Screenshot

Any ideas? Many thanks :) !Its ok, sorted it now :)

Need table of words/glossary/etc.

Hi!

I have a client who wants to have a function to have a system create a validation system for users who register for a system. It would email them a registration code, which they want to be two or three random words strewn together.

So, I'm looking for a big table of words - random, glossary terms, etc. Does anyone have anything like this? (A flat file that I can import would be fine.)

Thanks!I have a dictionary of words in an access db,... by why would you do this, why not generate some random sequence based on something like the data/time they subscribed??|||I have a dictionary of words in an access db,... by why would you do this, why not generate some random sequence based on something like the data/time they subscribed??

Because that would be logical and clients don't operate on logic :)

(They're afraid that random words would be too difficult for the users of the system, most of which are senior citizens.)|||A smart person would suggest sending them a validation link in the email that they can click and autovalidates... but there you go. ;)

So do you want this db? I think it is about 4 megs but I can't be sure...|||This is completely out of my hands. I've made my arguments and I've been shot down. Other people have offered alternatives, they've shot them down too. So, this is what I have to go with.

If you could provide the table, that'd be great.

Thanks!|||how do you want it? http download from somewhere? I can upload it in the next few hours (once I get home).|||Either post it somewhere or email it to rflagg@.gmail.com

Thanks!|||should be in your gmail|||Thanks! That table is great (and very extensive). Much appreciated!|||...sounds like a "lazy hacker" looking for a quick and dirty dictionary attack...maybe wrong though ;)|||And I just helped him,.. great,... ah well, if that is the case then people should protect themselves against such attacks anyway...|||I'm in a similar situation. Could you possible send me a copy of the db ?

[My Email] (laasunde@.online.no)

Thank you.|||Any way you can prove to me that it's not for dictionary attacks?|||Don't know how I could prove that over the Internet.

Need the db for an assignment at uni..|||I promise you that's not what it's for. I'm a professional, I do ASP.NET and SQL development for a living as full-time work, as contracts on the side, and I maintain two websites that I wrote myself for two non-profit animal rescue groups dedicated to the saving and placement of homeless and stray animals. One of which is Animal Allies (http://www.animalallies.com) which services the Washington D.C. metro area (where I live). If you'd like more evidence of this, you can email me at tarkon@.animalallies.com and I'll respond.

Besides that, there's very little I can do to assure you this is for honest and legitimate purposes other than my personal guarantee (and the fact that I don't even know what a dictionary attack is).

And lastly, it's been my experience most hackers are Linux people and not SQL Server 2000 developers.|||heheh no worries Tarkon, I wasn't that concerned to be honest,... just messing with your head. ;)sql

Need suggestion or comments for n-Tier applications.

using System;namespace BaResearch.Data.msSql{interface ISqlDataObject { System.Data.SqlClient.SqlConnection Connection {get; }string ConnectionString {get;set; } }}

___________________________________

using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SqlClient;namespace BaResearch.Data.msSql{public class SqlDataObject : ISqlDataObject {private string _ConnectionString;public SqlConnection Connection {get {return new SqlConnection(_ConnectionString); } }public string ConnectionString {get {return _ConnectionString; }set { _ConnectionString =value; } } }}

__________________________________

using System;namespace BaResearch.Data{interface IBid {string Client {get;set; }string Contact {get;set; }string Sponsor {get;set; }string Priority {get;set; }string BidStatus {get;set; } }}

_____________________________________________

using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SqlClient;using System.Configuration;namespace BaResearch.Data.msSql{public class Bid : SqlDataObject, IBid {#region private member & variablesprivate int _BidID;private string _DateCreated;private string _CreatedBy;private string _Client;private string _Contact;private string _Sponsor;private string _Priority;private string _BidStatus;private SqlDataAdapter sqlda =new SqlDataAdapter();private SqlCommand _command;private SqlParameter[] _parameters = {new SqlParameter("@.client",SqlDbType.NVarChar),new SqlParameter("@.contact",SqlDbType.NVarChar),new SqlParameter("@.sponsor",SqlDbType.NVarChar),new SqlParameter("@.priority",SqlDbType.NVarChar),new SqlParameter("@.bidstatus",SqlDbType.NVarChar), };#endregion #region public member variables - stored procedures//private string sp_Create = "bids_sp_insert"; //private string sp_Update_ByID = "bids_sp_updatebyid"; //private string sp_Delete_ByID = "bids_sp_deletebyid"; //private string sp_Select_All = "bids_sp_selectall"; //private string sp_Select_ByID = "bids_sp_selectbyid"; //private string sp_Select_ByValue = "bids_sp_selectbyvalue";#endregion #region constructors and destructorspublic Bid() {if (this.ConnectionString ==null) {this.ConnectionString = ConfigurationManager.ConnectionStrings["SQL.ConnectionString"].ConnectionString; }else {new Bid(this.ConnectionString); } }public Bid(string ConnectionString) {this.ConnectionString = ConnectionString; }#endregion #region methods & events/// <summary> /// Attach parameters to an SqlCommand /// </summary> /// <param name="sqlcmd">SqlCommand</param>private void attachparameters(SqlCommand sqlcmd) { sqlcmd.Parameters.Clear();foreach (SqlParameter paramin _parameters) { sqlcmd.Parameters.Add(param); } }/// <summary> /// Assign value to the parameters /// </summary> /// <param name="sqlcmd">SqlCommand</param> /// <param name="value">Client object</param>private void assignparametervalues(SqlCommand sqlcmd, Bidvalue) {this.attachparameters(sqlcmd);// todo: assign parameter values here sqlcmd.Parameters[0].Value =value.Client; sqlcmd.Parameters[1].Value =value.Contact; sqlcmd.Parameters[2].Value =value.Sponsor; sqlcmd.Parameters[3].Value =value.Priority; sqlcmd.Parameters[4].Value =value.BidStatus; }public virtual void Create(Bidvalue) { _command =new SqlCommand(); _command.Connection =this.Connection; _command.CommandText = @."INSERT INTO bids (client, contact, sponsor, priority, bidstatus, createdby) VALUES (@.client, @.contact, @.sponsor, @.priority, @.bidstatus, @.createdby);"; _command.CommandType = CommandType.Text;try { sqlda.InsertCommand = _command;this.assignparametervalues(sqlda.InsertCommand,value); sqlda.InsertCommand.Parameters.AddWithValue("@.createdby",value.CreatedBy); sqlda.InsertCommand.Parameters.Add("@.bidid", SqlDbType.Int); sqlda.InsertCommand.Parameters["@.bidid"].Direction = ParameterDirection.ReturnValue; sqlda.InsertCommand.Connection.Open(); sqlda.InsertCommand.ExecuteNonQuery(); sqlda.InsertCommand.Connection.Close(); sqlda.InsertCommand.Connection.Dispose(); }catch (Exception e) {throw new Exception(e.Message); } }public virtual int Update(Bidvalue) {int result; _command =new SqlCommand(); _command.Connection =this.Connection; _command.CommandText = @."UPDATE bids SET client=@.client, contact=@.contact, sponsor=@.sponsor, priority=@.priority, bidstatus=@.bidstatus WHERE bidid=@.bidid"; _command.CommandType = CommandType.Text;try { sqlda.UpdateCommand = _command;using (SqlCommand _cmd = sqlda.UpdateCommand) {this.assignparametervalues(_cmd,value); _cmd.Parameters.AddWithValue("@.bidid",value.BidID); _cmd.Connection.Open(); result = _cmd.ExecuteNonQuery(); _cmd.Connection.Close(); _cmd.Connection.Dispose(); } }catch (Exception e) {throw new Exception(e.Message); }return result; }public virtual void Delete(Bidvalue) { _command =new SqlCommand(); _command.Connection =this.Connection; _command.CommandText = @."DELETE bids WHERE bidid=@.bidid;"; _command.CommandType = CommandType.Text;try { sqlda.DeleteCommand = _command;using (SqlCommand _cmd = sqlda.DeleteCommand) { _cmd.Parameters.AddWithValue("@.bidid",value.BidID); _cmd.Connection.Open(); _cmd.ExecuteNonQuery(); _cmd.Connection.Close(); _cmd.Connection.Dispose(); } }catch (Exception e) {throw new Exception(e.Message); } }public virtual DataSet List(string filter) { DataSet result =new DataSet(); _command =new SqlCommand(); _command.Connection =this.Connection; _command.CommandText = @."SELECT * FROM bids_view_listall WHERE (clientname like @.value) or (contactname like @.value) or (sponsor like @.value) ORDER BY datecreated DESC"; _command.CommandType = CommandType.Text;try {if (filter !=null) { filter = filter.Replace(" ","%"); } sqlda.SelectCommand = _command; sqlda.SelectCommand.Parameters.AddWithValue("@.value","%" + filter +"%"); sqlda.Fill(result); }catch (Exception e) {throw new Exception(e.Message); }return (DataSet)result; }#endregion #region IBid Memberspublic string Client {get {return _Client; }set { _Client =value; } }public string Contact {get {return _Contact; }set { _Contact =value; } }public string Sponsor {get {return _Sponsor; }set { _Sponsor =value; } }public string Priority {get {return _Priority; }set { _Priority =value; } }public string BidStatus {get {return _BidStatus; }set { _BidStatus =value; } }#endregion #region Propertiespublic int BidID {get {return _BidID; }set { _BidID =value; } }public string DateCreated {get {return _DateCreated; }set { _DateCreated =value; } }public string CreatedBy {get {return _CreatedBy; }set { _CreatedBy =value; } }#endregion }}

_____________________________________________

Am I on a right path? I will greatly appreciate in any comments or suggestions for a design. I am creating an n-tier application and i don't know if my design is right. I dont have the proper schooling for creating this kind of applications and I am still on layer of data access and business logic.

I tested it on a console application and web app, for now its working. But, how about if a change a different type of database? I studied some sample applications downloaded online and a little bit of reading from a book.

Although, I already completed a past project with a module (dll) for a web application and already have implemented it.
I am still confused on how to implement a data library that can be used in different type of applications and services (windows, web) and can change the type of database by changing some configuration and not change the code itself.

I there someone who can show me a pattern for creating a library that can be used to any type of applications. It will be a great help for me and I will greatly appreciate it.

|||

since no one replied, i have to search it for my self

http://www.dofactory.com/Patterns/Patterns.aspx.

I still didn't buy it, but, are these kind of books going to help me on what am i searching for?

|||

Sorry did not reply you sooner but here are some good samples, and the database agnostic stuff not that simple but check the Achitecture forum for references to Petshop a Java application converted to .NET by Microsoft. And good books I prefer Martin Fowler books but he is not a beginner's writer the best beginner's book is by Craig Larman because he is complete.

http://www.microsoft.com/belux/msdn/nl/community/columns/hyatt/ntier1.mspx

http://www.microsoft.com/belux/msdn/nl/community/columns/hyatt/ntier2.mspx

http://www.microsoft.com/belux/msdn/nl/community/columns/hyatt/ntier3.mspx

http://www.dotnetbips.com/articles/displayarticle.aspx?id=515

Free Objects development resources, Post again if you still have question. Hope this helps.

http://forums.asp.net/thread/1462028.aspx

|||

Thanks for your help.

|||

jd2001:

Thanks for your help.

I am glad I could help

Need SQL Server 2K sp3

NOT 3a. I have to rebuild a system that was using sp3 so I have to restore
the master database. The system is using sp3 and NOT 3a. I can't find sp3
anymore?
Never mind. I found the decompressed files on another server. It seems
strange for MS to have made this difficult to obtain for situations like
this, though.
"michelle" <michelle@.nospam.com> wrote in message
news:uMHak8SNFHA.3928@.TK2MSFTNGP09.phx.gbl...
> NOT 3a. I have to rebuild a system that was using sp3 so I have to restore
> the master database. The system is using sp3 and NOT 3a. I can't find sp3
> anymore?
>

Need SQL Server 2K sp3

NOT 3a. I have to rebuild a system that was using sp3 so I have to restore
the master database. The system is using sp3 and NOT 3a. I can't find sp3
anymore?Never mind. I found the decompressed files on another server. It seems
strange for MS to have made this difficult to obtain for situations like
this, though.
"michelle" <michelle@.nospam.com> wrote in message
news:uMHak8SNFHA.3928@.TK2MSFTNGP09.phx.gbl...
> NOT 3a. I have to rebuild a system that was using sp3 so I have to restore
> the master database. The system is using sp3 and NOT 3a. I can't find sp3
> anymore?
>

Need SQL Server 2K sp3

NOT 3a. I have to rebuild a system that was using sp3 so I have to restore
the master database. The system is using sp3 and NOT 3a. I can't find sp3
anymore?Never mind. I found the decompressed files on another server. It seems
strange for MS to have made this difficult to obtain for situations like
this, though.
"michelle" <michelle@.nospam.com> wrote in message
news:uMHak8SNFHA.3928@.TK2MSFTNGP09.phx.gbl...
> NOT 3a. I have to rebuild a system that was using sp3 so I have to restore
> the master database. The system is using sp3 and NOT 3a. I can't find sp3
> anymore?
>

Need some assistance

Hi,
I am importing some phone numbers from a lagacy system and I find some of
them having the following exeptions:
1. 2122121222
2. 212 212 2122
3. 212/212 2122
4. 212/2122122
5. 212.212 2122
6. 212.2122122
7. 212.212.2122
8. 212-212-2122
9. (212) 212-2122
10. 212-2122122
11. (212)-212-2122
12. TEXT
13. 212 2122122
14. 212 -212-2122
15. 212 212 21222
16. 212) 212-2122
17. 2122122122#212
18. 1212 212 2122
How can I format them to (212) 212-2122? I was thinking a function but can
someone assist?
ThanksHi Chris
The code I have supplied below works in a controlled test environment and
may work for you BUT use with extreme caution.
Test the code by using selects before trying the inserts to verify that you
are getting the results required.
You may also like to backup your table before attempting this.
---
drop table #temp
create table #temp(pnumber varchar(50))
insert into #temp values('2122121222')
insert into #temp values('212 212 2122')
insert into #temp values('212/212 2122')
insert into #temp values('212/2122122')
insert into #temp values('212.212 2122')
insert into #temp values('212.2122122')
insert into #temp values('212.212.2122')
insert into #temp values('212-212-2122')
insert into #temp values('(212) 212-2122')
insert into #temp values('212-2122122')
insert into #temp values('(212)-212-2122')
insert into #temp values('TEXT')
insert into #temp values('212 2122122')
insert into #temp values('212 -212-2122')
insert into #temp values('212 212 21222')
insert into #temp values('212) 212-2122')
insert into #temp values('17. 2122122122#212')
insert into #temp values('1212 212 2122')
-- remove know extraneous characters
update #temp
set pnumber = replace(pnumber, ' ', '')
update #temp
set pnumber = replace(pnumber, '.', '')
update #temp
set pnumber = replace(pnumber, '/', '')
update #temp
set pnumber = replace(pnumber, '-', '')
update #temp
set pnumber = replace(pnumber, '(', '')
update #temp
set pnumber = replace(pnumber, ')', '')
update #temp
set pnumber = replace(pnumber, '#', '')
-- set to null where number length is not 10 --
update #temp
set pnumber = null where len(pnumber) <> 10
-- add braces and dash
update #temp
set pnumber = '(' + pnumber
update #temp
set pnumber = left(pnumber, 4) + ') ' + right(pnumber, 7)
update #temp
set pnumber = left(pnumber, 9) + '-' + right(pnumber, 4)
select * from #temp
---
Regards
Peter Hamilton
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:FD7D08A6-B229-4A05-9FDE-DAFAAEEE2E4D@.microsoft.com...
> Hi,
> I am importing some phone numbers from a lagacy system and I find some of
> them having the following exeptions:
> 1. 2122121222
> 2. 212 212 2122
> 3. 212/212 2122
> 4. 212/2122122
> 5. 212.212 2122
> 6. 212.2122122
> 7. 212.212.2122
> 8. 212-212-2122
> 9. (212) 212-2122
> 10. 212-2122122
> 11. (212)-212-2122
> 12. TEXT
> 13. 212 2122122
> 14. 212 -212-2122
> 15. 212 212 21222
> 16. 212) 212-2122
> 17. 2122122122#212
> 18. 1212 212 2122
> How can I format them to (212) 212-2122? I was thinking a function but can
> someone assist?
> Thanks
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||What are you using to import them? Is this in a table? Or a spreadsheet?
Is this a one time thing. Why are all of your phone numbers the same
number! (kidding about the last one :)
If you want to do it in sql (the algorithm would be the same anywhere) just
strip out all of the characters you don't want, leaving you with only
numbers. Then simply put it back together. I would suggest you might want
to put the data in three columns, but certainly a check constraint is
needed. You ought ot get a list of valid area codes to validate against as
swell, if this data is important to you (if it is I would have expected it
would be a bit better taken care of, but you never know.
Note that I just toss off the (likely) extension information, and just using
the 10 characters.
drop table test
go
create table test
(
phoneNumber varchar(15)
)
insert into test
select '2122121222'
union all
select '212 212 2122'
union all
select '212/212 2122'
union all
select '212/2122122'
union all
select '212.212 2122'
union all
select '212.2122122'
union all
select '212.212.2122'
union all
select '212-212-2122'
union all
select '(212) 212-2122'
union all
select '212-2122122'
union all
select '(212)-212-2122'
union all
select 'TEXT'
union all
select '212 2122122'
union all
select '212 -212-2122'
union all
select '212 212 21222'
union all
select '212) 212-2122'
union all
select '2122122122#212'
union all
select '1212 212 2122'
select case when cleaned like '%[a-z]%' then 'INVALID DATA'
when cleaned like '1%' then '(' + substring(cleaned,2,3) + ')' +
substring(cleaned,5,3) + '-' + substring(cleaned,8,4)
else '(' + substring(cleaned,1,3) + ')' + substring(cleaned,4,3) + '-' +
substring(cleaned,7,4) end
--this bit strips out the offensive characters and spaces
from (select
replace(replace(replace(replace(replace(
replace(replace(phoneNumber,'
',''),'(',''),')',''),'-',''),'.',''),'/',''),'#','') as cleaned
from test) as numbers
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:FD7D08A6-B229-4A05-9FDE-DAFAAEEE2E4D@.microsoft.com...
> Hi,
> I am importing some phone numbers from a lagacy system and I find some of
> them having the following exeptions:
> 1. 2122121222
> 2. 212 212 2122
> 3. 212/212 2122
> 4. 212/2122122
> 5. 212.212 2122
> 6. 212.2122122
> 7. 212.212.2122
> 8. 212-212-2122
> 9. (212) 212-2122
> 10. 212-2122122
> 11. (212)-212-2122
> 12. TEXT
> 13. 212 2122122
> 14. 212 -212-2122
> 15. 212 212 21222
> 16. 212) 212-2122
> 17. 2122122122#212
> 18. 1212 212 2122
> How can I format them to (212) 212-2122? I was thinking a function but can
> someone assist?
> Thanks|||Probably better to validate this data externally and/or prior to acceptance
into the database. But here's one brute force method.
CREATE TABLE #t
(
phone VARCHAR(32)
);
SET NOCOUNT ON;
INSERT #t SELECT '2122121222';
INSERT #t SELECT '212 212 2122';
INSERT #t SELECT '212/212 2122';
INSERT #t SELECT '212/2122122';
INSERT #t SELECT '212.212 2122';
INSERT #t SELECT '212.2122122';
INSERT #t SELECT '212.212.2122';
INSERT #t SELECT '212-212-2122';
INSERT #t SELECT '(212) 212-2122';
INSERT #t SELECT '212-2122122';
INSERT #t SELECT '(212)-212-2122';
INSERT #t SELECT 'TEXT';
INSERT #t SELECT '212 2122122';
INSERT #t SELECT '212 -212-2122';
INSERT #t SELECT '212 212 21222';
INSERT #t SELECT '212) 212-2122';
INSERT #t SELECT '2122122122#212';
INSERT #t SELECT '1212 212 2122';
UPDATE #t
SET phone =
LTRIM(RTRIM(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(
phone,
-- you may want to add more characters here
' ', ''),
'(', ''),
')', ''),
'#', ''),
'.', ''),
'/', ''),
'-', '')
));
UPDATE #t
SET phone = '('+STUFF(STUFF(phone,4,0,') '),9,0,'-')
WHERE
-- make sure phone has 10 numerical digits
-- you can make this more precise, e.g.
-- disallow 0 in the first position
phone LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';
SELECT phone FROM #t ORDER BY phone;
DROP TABLE #t;
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:FD7D08A6-B229-4A05-9FDE-DAFAAEEE2E4D@.microsoft.com...
> Hi,
> I am importing some phone numbers from a lagacy system and I find some of
> them having the following exeptions:
> 1. 2122121222
> 2. 212 212 2122
> 3. 212/212 2122
> 4. 212/2122122
> 5. 212.212 2122
> 6. 212.2122122
> 7. 212.212.2122
> 8. 212-212-2122
> 9. (212) 212-2122
> 10. 212-2122122
> 11. (212)-212-2122
> 12. TEXT
> 13. 212 2122122
> 14. 212 -212-2122
> 15. 212 212 21222
> 16. 212) 212-2122
> 17. 2122122122#212
> 18. 1212 212 2122
> How can I format them to (212) 212-2122? I was thinking a function but can
> someone assist?
> Thanks|||For short term, create a scalar UDF like:
CREATE FUNCTION dbo.f ( @.s VARCHAR( 20 ) )
RETURNS VARCHAR( 20 ) AS BEGIN
WHILE PATINDEX( '%[^0-9]%', @.s ) > 0
SET @.s = REPLACE( @.s, SUBSTRING( @.s, PATINDEX( '%[^0-9]%', @.s ), 1 ),
'' )
RETURN CASE WHEN LEN( @.s ) = 10
THEN '(' + STUFF( STUFF( @.s, 4, 0, ') ' ), 9, 0, '-' )
ELSE 'BAD DATA'
END
END
Now try:
SELECT phone_col, dbo.f( phone_col )
FROM tbl ;
Anith|||I am using DTS to import. Thanks for the assistance. I'll try to create a
function that I can add exceptions to in future. I'll be importng this data
on a daily basis from a legacy system which has to validation in the fronten
d
so the users practically enters anything.
"Chris" wrote:

> Hi,
> I am importing some phone numbers from a lagacy system and I find some of
> them having the following exeptions:
> 1. 2122121222
> 2. 212 212 2122
> 3. 212/212 2122
> 4. 212/2122122
> 5. 212.212 2122
> 6. 212.2122122
> 7. 212.212.2122
> 8. 212-212-2122
> 9. (212) 212-2122
> 10. 212-2122122
> 11. (212)-212-2122
> 12. TEXT
> 13. 212 2122122
> 14. 212 -212-2122
> 15. 212 212 21222
> 16. 212) 212-2122
> 17. 2122122122#212
> 18. 1212 212 2122
> How can I format them to (212) 212-2122? I was thinking a function but can
> someone assist?
> Thanks

Need random/unique "IDs" for key verification

I need to be able to create completely random and unique keys for a key verification system, which would require a user to enter a pre-defined key in order to activate their account, but I need to be able to create those keys on the fly.

This is going to be a key that will be mailed to them on paper, and unfortunately means it needs to be relatively short in order to prevent too much confusion while they are typing it in.

I like the newID() function in SQL, but the ID that it creates is a bit excessive to say the least for someone to have to type when registerring.

I use C#, so I wouldn't have much of a problem creating a small app to create x number of keys, which will sit in the DB until I need them, but I would rather not have to fill the DB with a million or so ID's which might never be used, and don't want to create too little that I have to track when I might need to add more, in case I start to run low on ID's.

Re-using ID's may be an option, but I would prefer to keep them intact for the life of the accounts.

If there is something that I can do to simulate the newID() function, but generate unique/random ID's which look more like this: A97-2C5-77D than this: A972C577-DFB0-064E-1189-0154C99310DAAC12 I would be very grateful to know about it.

Thanks!

You could use Membership.GeneratePassword Method

It allows control of length and complexity both of the generated password

http://msdn2.microsoft.com/en-us/library/system.web.security.membership.generatepassword.aspx

need lil fast help

my system has 2 db's - sql server 2000 & db2 @. separate locations. i have a select query which needs 2 pick up consolidated data from both the tables. also the schema on the db2 has minor changes when compared with the schema on sql server 2000.

while searching on microsoft i came across the technique of creating a linked server. would this be possible 2 implement in my scenario. also would in this case, be advised that i create another view in the db2 server which has changed the db2 schema to the sql server schema format??

please hurry..
regards,
sameerYou consolidating by UNION or by JOIN?

Either way, put as little logic into the passed linked server SQL command as possible, which means use a view on the db2 end.

What kind of performance do you need out of this, and how much traffic do you expect?|||Use linked server as stated above. Don't forget to enable multiprotocol in the server network utility so as to be on safe side.

Need help with VS_NEEDSNEWMETADATA error

Hello:

I need some help figuring out the true source of the following error:

"

Executed as user: EPSILON\SYSTEM. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:53:09 PM Error: 2007-09-14 12:53:09.59 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-09-14 12:53:10.50 Code: 0xC020837F Source: Data Flow Task Source - icsp [1] Description: The data type of "output column "user2" (138)" does not match the data type "System.String" of the source column "user2". End Error Error: 2007-09-14 12:53:10.50 Code: 0xC004706B Source: Data Flow Task DTS.Pipeline Description: "component "Source - icsp" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA". The package execution fa... The step failed."

Reading the error it appears that the issue is with a datatype mismatch with field "user2". It is coming in as a unicode string and being sent into a varchar field. But so are a whole bunch of other userX fields as well. So why is my package having an issue with this specific field. Moreoever the package was running fine until a few days ago and runs successfully in BIDS!

I first figured that the source has changed, as there was some work being performed on the source ERP system. The package had failed a month ago and when I updated the metadata I thought it fixed the problem.

I appreciate your assistance in helping me resolve this issue!

Don't ignore the password issue. Maybe because it can't use the password, it can't determine that the metadata has changed, or something like that. In BIDS, maybe it doesn't have the password problem?

|||Did you move the package from one computer to another by any chance? What is the ProtectionLevel of the package set to? I recommend setting the ProtectionLevel to "DontSaveSensitive" and seeing if it resolves the problem.

I have also had problems in the past where somehow the metadata gets messed up and the easiest thing to do is recreate the package.|||

Thank you for your response gentlemen!

I have checked the connection string for teh SQL Agent and it has the correct ID and password. I have not changed anything in the job. In BIDS, when I enter the password and run the job it runs fine.

|||As Danny asked, what's the ProtectionLevel of the package?|||EncryptSensitiveWithUserKey|||

Using EncryptSensitiveWithUserKey is the cause of the error. This is a link to an article that explains the problem and more importantly, the solution:

http://support.microsoft.com/kb/918760

|||

You mention SQL Agent, so does this error happen when you schedule the package, but works for you on your desktop?

If so the problem is the ProtectionLevel. The EncryptSensitiveWithUserKey value means just that, it uses the user key, your key as you built the package. If your SQL Server Agent service was set to run under you account, then the error would go away. that would also be a stupid thing to do, so change to using DontSaveSensitive is my advice. If you have passwords, then supply them through Configurations.

Some links with more information -

http://support.microsoft.com/kb/904800

http://support.microsoft.com/kb/918760

http://technet.microsoft.com/en-us/library/ms141682.aspx

|||

I have to admit, I have really looked at the Protection Level closely. The issue is that the package for running fine in an Agent, as well are other packages with the same type of Protection Level. Why did it work all this time and is not working now - is the question that puzzles me?

I will review the links that you have provided and figure out how to build Configurations.

PS: The package does not run outside of an agent in Mgmt. Studio but runs in BIDS after I supply the password in the DataReaderSrc.

Thanks again!

|||

Many of us have had to struggle with the ProtectionLevel setting during deployment. I do not think Microsoft documented it very well. It finally clicks after beating your head against the wall for awhile and visiting forums.

Need help with VS_NEEDSNEWMETADATA error

Hello:

I need some help figuring out the true source of the following error:

"

Executed as user: EPSILON\SYSTEM. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:53:09 PM Error: 2007-09-14 12:53:09.59 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-09-14 12:53:10.50 Code: 0xC020837F Source: Data Flow Task Source - icsp [1] Description: The data type of "output column "user2" (138)" does not match the data type "System.String" of the source column "user2". End Error Error: 2007-09-14 12:53:10.50 Code: 0xC004706B Source: Data Flow Task DTS.Pipeline Description: "component "Source - icsp" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA". The package execution fa... The step failed."

Reading the error it appears that the issue is with a datatype mismatch with field "user2". It is coming in as a unicode string and being sent into a varchar field. But so are a whole bunch of other userX fields as well. So why is my package having an issue with this specific field. Moreoever the package was running fine until a few days ago and runs successfully in BIDS!

I first figured that the source has changed, as there was some work being performed on the source ERP system. The package had failed a month ago and when I updated the metadata I thought it fixed the problem.

I appreciate your assistance in helping me resolve this issue!

Don't ignore the password issue. Maybe because it can't use the password, it can't determine that the metadata has changed, or something like that. In BIDS, maybe it doesn't have the password problem?

|||Did you move the package from one computer to another by any chance? What is the ProtectionLevel of the package set to? I recommend setting the ProtectionLevel to "DontSaveSensitive" and seeing if it resolves the problem.

I have also had problems in the past where somehow the metadata gets messed up and the easiest thing to do is recreate the package.|||

Thank you for your response gentlemen!

I have checked the connection string for teh SQL Agent and it has the correct ID and password. I have not changed anything in the job. In BIDS, when I enter the password and run the job it runs fine.

|||As Danny asked, what's the ProtectionLevel of the package?|||EncryptSensitiveWithUserKey|||

Using EncryptSensitiveWithUserKey is the cause of the error. This is a link to an article that explains the problem and more importantly, the solution:

http://support.microsoft.com/kb/918760

|||

You mention SQL Agent, so does this error happen when you schedule the package, but works for you on your desktop?

If so the problem is the ProtectionLevel. The EncryptSensitiveWithUserKey value means just that, it uses the user key, your key as you built the package. If your SQL Server Agent service was set to run under you account, then the error would go away. that would also be a stupid thing to do, so change to using DontSaveSensitive is my advice. If you have passwords, then supply them through Configurations.

Some links with more information -

http://support.microsoft.com/kb/904800

http://support.microsoft.com/kb/918760

http://technet.microsoft.com/en-us/library/ms141682.aspx

|||

I have to admit, I have really looked at the Protection Level closely. The issue is that the package for running fine in an Agent, as well are other packages with the same type of Protection Level. Why did it work all this time and is not working now - is the question that puzzles me?

I will review the links that you have provided and figure out how to build Configurations.

PS: The package does not run outside of an agent in Mgmt. Studio but runs in BIDS after I supply the password in the DataReaderSrc.

Thanks again!

|||

Many of us have had to struggle with the ProtectionLevel setting during deployment. I do not think Microsoft documented it very well. It finally clicks after beating your head against the wall for awhile and visiting forums.

Need help with VS_NEEDSNEWMETADATA error

Hello:

I need some help figuring out the true source of the following error:

"

Executed as user: EPSILON\SYSTEM. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:53:09 PM Error: 2007-09-14 12:53:09.59 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-09-14 12:53:10.50 Code: 0xC020837F Source: Data Flow Task Source - icsp [1] Description: The data type of "output column "user2" (138)" does not match the data type "System.String" of the source column "user2". End Error Error: 2007-09-14 12:53:10.50 Code: 0xC004706B Source: Data Flow Task DTS.Pipeline Description: "component "Source - icsp" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA". The package execution fa... The step failed."

Reading the error it appears that the issue is with a datatype mismatch with field "user2". It is coming in as a unicode string and being sent into a varchar field. But so are a whole bunch of other userX fields as well. So why is my package having an issue with this specific field. Moreoever the package was running fine until a few days ago and runs successfully in BIDS!

I first figured that the source has changed, as there was some work being performed on the source ERP system. The package had failed a month ago and when I updated the metadata I thought it fixed the problem.

I appreciate your assistance in helping me resolve this issue!

Don't ignore the password issue. Maybe because it can't use the password, it can't determine that the metadata has changed, or something like that. In BIDS, maybe it doesn't have the password problem?

|||Did you move the package from one computer to another by any chance? What is the ProtectionLevel of the package set to? I recommend setting the ProtectionLevel to "DontSaveSensitive" and seeing if it resolves the problem.

I have also had problems in the past where somehow the metadata gets messed up and the easiest thing to do is recreate the package.|||

Thank you for your response gentlemen!

I have checked the connection string for teh SQL Agent and it has the correct ID and password. I have not changed anything in the job. In BIDS, when I enter the password and run the job it runs fine.

|||As Danny asked, what's the ProtectionLevel of the package?|||EncryptSensitiveWithUserKey|||

Using EncryptSensitiveWithUserKey is the cause of the error. This is a link to an article that explains the problem and more importantly, the solution:

http://support.microsoft.com/kb/918760

|||

You mention SQL Agent, so does this error happen when you schedule the package, but works for you on your desktop?

If so the problem is the ProtectionLevel. The EncryptSensitiveWithUserKey value means just that, it uses the user key, your key as you built the package. If your SQL Server Agent service was set to run under you account, then the error would go away. that would also be a stupid thing to do, so change to using DontSaveSensitive is my advice. If you have passwords, then supply them through Configurations.

Some links with more information -

http://support.microsoft.com/kb/904800

http://support.microsoft.com/kb/918760

http://technet.microsoft.com/en-us/library/ms141682.aspx

|||

I have to admit, I have really looked at the Protection Level closely. The issue is that the package for running fine in an Agent, as well are other packages with the same type of Protection Level. Why did it work all this time and is not working now - is the question that puzzles me?

I will review the links that you have provided and figure out how to build Configurations.

PS: The package does not run outside of an agent in Mgmt. Studio but runs in BIDS after I supply the password in the DataReaderSrc.

Thanks again!

|||

Many of us have had to struggle with the ProtectionLevel setting during deployment. I do not think Microsoft documented it very well. It finally clicks after beating your head against the wall for awhile and visiting forums.