Conversion failed when converting from a character string to uniqueidentifier. - PLEASE HE

I am trying to store a unique identifier that is text into a field in a SQL DB that is type uniqueidentifier and I get the follow error message.

Conversion failed when converting from a character string to uniqueidentifier.

My Code is shown below:

comSQL.Parameters.AddWithValue("@.PROPERTYID", Format(Request.QueryString("ID").ToString,"{0:########-####-####-####-############}"))

This has worked before but isnt' anymore. Any ideas?

jsmith3465:

comSQL.Parameters.AddWithValue("@.PROPERTYID", Format(Request.QueryString("ID").ToString,"{0:########-####-####-####-############}"))

have you tried as...

comSQL.Parameters.AddWithValue("@.PROPERTYID",New Guid(Format("werwerwerwerwerwerwerwerwerwerwe","{0:########-####-####-####-############}")))

|||

I tried adding the New Guid() and that did not solve the problem either. Anymore ideas? I am trying to convert Text to Unique Identifier for storage in SQL Server 2005.

Thanks for all of your help!

Ryan

Continue INSERT after key violation?

Hi there.
I have a simple table with a UNIQUE constraint on one field. I wish to
populate this table using a stored procedure that returns the equivalent
results set like so:
INSERT INTO MyTable EXECUTE p_GetMyResultsSet
My stored proc returns unique, distinct results each time it's called
(unique within each call!). The problem I'm having though is that, if the
stored proc returns any record that already exists in the table, the whole
statement quits with a 'constraint violation'.
I would like the statement to continue inserting the results - only leaving
out records which fail the criteria - is this possible?
e.g: 1. p_GetMyResultsSet returns one row with a key value of '1' - record
is inserted into the table OK.
2. p_GetMyResultsSet returns 4 rows with key values of 1, 2, 3, 4.
What happens now: none of the records are inserted because '1' already exist
s.
What I'd like to hapen: records 2, 3, 4 to be inserted.Can't you change the proc so that it only returns the rows that don't
exist? For example:
SELECT x, ...
FROM foo
WHERE NOT EXISTS
(SELECT *
FROM MyTable
WHERE x = foo.x) ;
If not, you could insert the results of the proc to a temp table and
then to MyTable using the same WHERE NOT EXISTS logic.
David Portas
SQL Server MVP
--|||Yes - I think I'll have to use the temp table approach. I'm restricted from
using the 'not exists' because the stored proc needs to be available for use
by other apps and processes- the "MyTable" population is just one process o
f
many using the proc to generate data...
Thanks for the help!
"David Portas" wrote:

> Can't you change the proc so that it only returns the rows that don't
> exist? For example:
> SELECT x, ...
> FROM foo
> WHERE NOT EXISTS
> (SELECT *
> FROM MyTable
> WHERE x = foo.x) ;
> If not, you could insert the results of the proc to a temp table and
> then to MyTable using the same WHERE NOT EXISTS logic.
> --
> David Portas
> SQL Server MVP
> --
>

constraints on views

Hi.
At first let me present the problem:
T(a int, b int, c bit)
I would like to see to it that (a,b) is unique where c = 1. The easiest way
to accomplish this would be creating a view like this:
create view V as select a,b from T where c = 1
And then putting a unique constraint on (a,b) in V.
I am open to other solutions as well, of course.
Thx,
Agoston
Create an indexed view with a unique index on (a,b).
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:570905D0-1D39-4CAF-995C-2DD91A2EC6B4@.microsoft.com...
> Create an indexed view with a unique index on (a,b).
Does this feature exists in SQL Server 7? Unfortunately I'm forced to work
with that, and I remember reading about this feature not being present in
SQL 7.
Thx,
Agoston

> --
> David Portas
> SQL Server MVP
> --
>
|||You are correct, indexed views are not available in SQL 7. Probably the best
thing you can do in SQL 7 is to create a trigger that enforces this for you.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:evpzFRF2EHA.3408@.tk2msftngp13.phx.gbl...
> Hi.
> At first let me present the problem:
> T(a int, b int, c bit)
> I would like to see to it that (a,b) is unique where c = 1. The easiest
way
> to accomplish this would be creating a view like this:
> create view V as select a,b from T where c = 1
> And then putting a unique constraint on (a,b) in V.
> I am open to other solutions as well, of course.
> Thx,
> Agoston
>
|||It is not clear from your message if you want to use regular view or
indexed view, but in any case you can only have constraint on indexed
view.
Another way that you can use is to work with stored procedure.
Instead of giving the users permissions to work directly with the
table, you can grant them execute permissions on a stored procedure
that inserts the data to the table (or rejects the data) according to
your criteria.
Another way is to use trigger or instead of trigger (instead of
trigger would be better).
My favorite of all of those is the stored procedure.
Adi
|||You could also consider a UNIQUE CONSTRAINT on the combination of a, b, and
c. What is the rule when c <> 1?
Sincerely,
Anthony Thomas

"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:evpzFRF2EHA.3408@.tk2msftngp13.phx.gbl...
Hi.
At first let me present the problem:
T(a int, b int, c bit)
I would like to see to it that (a,b) is unique where c = 1. The easiest way
to accomplish this would be creating a view like this:
create view V as select a,b from T where c = 1
And then putting a unique constraint on (a,b) in V.
I am open to other solutions as well, of course.
Thx,
Agoston

constraints on views

Hi.
At first let me present the problem:
T(a int, b int, c bit)
I would like to see to it that (a,b) is unique where c = 1. The easiest way
to accomplish this would be creating a view like this:
create view V as select a,b from T where c = 1
And then putting a unique constraint on (a,b) in V.
I am open to other solutions as well, of course.
Thx,
AgostonCreate an indexed view with a unique index on (a,b).
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:570905D0-1D39-4CAF-995C-2DD91A2EC6B4@.microsoft.com...
> Create an indexed view with a unique index on (a,b).
Does this feature exists in SQL Server 7? Unfortunately I'm forced to work
with that, and I remember reading about this feature not being present in
SQL 7.
Thx,
Agoston
> --
> David Portas
> SQL Server MVP
> --
>|||You are correct, indexed views are not available in SQL 7. Probably the best
thing you can do in SQL 7 is to create a trigger that enforces this for you.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:evpzFRF2EHA.3408@.tk2msftngp13.phx.gbl...
> Hi.
> At first let me present the problem:
> T(a int, b int, c bit)
> I would like to see to it that (a,b) is unique where c = 1. The easiest
way
> to accomplish this would be creating a view like this:
> create view V as select a,b from T where c = 1
> And then putting a unique constraint on (a,b) in V.
> I am open to other solutions as well, of course.
> Thx,
> Agoston
>|||It is not clear from your message if you want to use regular view or
indexed view, but in any case you can only have constraint on indexed
view.
Another way that you can use is to work with stored procedure.
Instead of giving the users permissions to work directly with the
table, you can grant them execute permissions on a stored procedure
that inserts the data to the table (or rejects the data) according to
your criteria.
Another way is to use trigger or instead of trigger (instead of
trigger would be better).
My favorite of all of those is the stored procedure.
Adi|||You could also consider a UNIQUE CONSTRAINT on the combination of a, b, and
c. What is the rule when c <> 1?
Sincerely,
Anthony Thomas
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:evpzFRF2EHA.3408@.tk2msftngp13.phx.gbl...
Hi.
At first let me present the problem:
T(a int, b int, c bit)
I would like to see to it that (a,b) is unique where c = 1. The easiest way
to accomplish this would be creating a view like this:
create view V as select a,b from T where c = 1
And then putting a unique constraint on (a,b) in V.
I am open to other solutions as well, of course.
Thx,
Agoston

constraints on views

Hi.
At first let me present the problem:
T(a int, b int, c bit)
I would like to see to it that (a,b) is unique where c = 1. The easiest way
to accomplish this would be creating a view like this:
create view V as select a,b from T where c = 1
And then putting a unique constraint on (a,b) in V.
I am open to other solutions as well, of course.
Thx,
AgostonCreate an indexed view with a unique index on (a,b).
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:570905D0-1D39-4CAF-995C-2DD91A2EC6B4@.microsoft.com...
> Create an indexed view with a unique index on (a,b).
Does this feature exists in SQL Server 7? Unfortunately I'm forced to work
with that, and I remember reading about this feature not being present in
SQL 7.
Thx,
Agoston

> --
> David Portas
> SQL Server MVP
> --
>|||You are correct, indexed views are not available in SQL 7. Probably the best
thing you can do in SQL 7 is to create a trigger that enforces this for you.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:evpzFRF2EHA.3408@.tk2msftngp13.phx.gbl...
> Hi.
> At first let me present the problem:
> T(a int, b int, c bit)
> I would like to see to it that (a,b) is unique where c = 1. The easiest
way
> to accomplish this would be creating a view like this:
> create view V as select a,b from T where c = 1
> And then putting a unique constraint on (a,b) in V.
> I am open to other solutions as well, of course.
> Thx,
> Agoston
>|||It is not clear from your message if you want to use regular view or
indexed view, but in any case you can only have constraint on indexed
view.
Another way that you can use is to work with stored procedure.
Instead of giving the users permissions to work directly with the
table, you can grant them execute permissions on a stored procedure
that inserts the data to the table (or rejects the data) according to
your criteria.
Another way is to use trigger or instead of trigger (instead of
trigger would be better).
My favorite of all of those is the stored procedure.
Adi|||You could also consider a UNIQUE CONSTRAINT on the combination of a, b, and
c. What is the rule when c <> 1?
Sincerely,
Anthony Thomas
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:evpzFRF2EHA.3408@.tk2msftngp13.phx.gbl...
Hi.
At first let me present the problem:
T(a int, b int, c bit)
I would like to see to it that (a,b) is unique where c = 1. The easiest way
to accomplish this would be creating a view like this:
create view V as select a,b from T where c = 1
And then putting a unique constraint on (a,b) in V.
I am open to other solutions as well, of course.
Thx,
Agoston

constraint expression for unique keys

if i have a table which defines a rule as "combination of two field
must be unique", how can I write this in a constraint expression
section?
i started learning more about ms sql side to handle all the necessary
rules in back-end instead of front-end.
also any good learning links, references, or book recommandations?
thanksan excerpt from BOL:

"C. Using UNIQUE constraints
UNIQUE constraints are used to enforce uniqueness on nonprimary key
columns. The following example enforces a restriction that the Name
column of the Product table must be unique.

Copy Code
Name nvarchar(100) NOT NULL
UNIQUE NONCLUSTERED

"|||can you explain what "NONCLUSTERED" is doing there? is that for
non-relation to a field in other table?

Alexander Kuznetsov wrote:
> an excerpt from BOL:
> "C. Using UNIQUE constraints
> UNIQUE constraints are used to enforce uniqueness on nonprimary key
> columns. The following example enforces a restriction that the Name
> column of the Product table must be unique.
> Copy Code
> Name nvarchar(100) NOT NULL
> UNIQUE NONCLUSTERED
> "|||SQL Server implicitly creates an index to implement a uinque
constraint. In this case NONCLUSTERED means the index will be
non-clustered.|||HandersonVA (handersonva@.hotmail.com) writes:
> if i have a table which defines a rule as "combination of two field
> must be unique", how can I write this in a constraint expression
> section?

CONSTRAINT u_tbl UNIQUE (col1, col2)

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> if i have a table which defines a rule as "combination of two field must be unique", how can I write this in a constraint expression section? <<

CONSTRAINT unique_location UNIQUE (x, y)
>> any good learning links, references, or book recommandations? <<

I recommend buying all of my books :)

Constraint Doubt

Hi. In a table called Groups with fields GroupID (PK), Name, how can I set a Constraint to make Name filed Unique to avoid:
GroupID | Name
==========
1 | Admin
2 | Admin
Check out ALTER TABLE in BooksOnLine and look at the CONSTRAINT section.
Here is an example directly from BOL:
CREATE TABLE doc_exc ( column_a INT)
GO
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE
Andrew J. Kelly SQL MVP
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:1DD9B537-20E5-4010-A469-03C4259F2C3D@.microsoft.com...
> Hi. In a table called Groups with fields GroupID (PK), Name, how can I set
a Constraint to make Name filed Unique to avoid:
> GroupID | Name
> ==========
> 1 | Admin
> 2 | Admin
|||Thanks Andrew:
But suppose that I already have the 'doc_exc' Table created, but still haven′t add a constraint to column_b ... I can find a way to add the constraint, unless I delete the table and run the script again?
KenA.
"Andrew J. Kelly" wrote:

> Check out ALTER TABLE in BooksOnLine and look at the CONSTRAINT section.
> Here is an example directly from BOL:
> CREATE TABLE doc_exc ( column_a INT)
> GO
> ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
> CONSTRAINT exb_unique UNIQUE
>
> --
> Andrew J. Kelly SQL MVP
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:1DD9B537-20E5-4010-A469-03C4259F2C3D@.microsoft.com...
> a Constraint to make Name filed Unique to avoid:
>
>
|||In that case, just run the Alter table command alone.
John
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:B982E393-F58D-4224-A883-32E8B10AAAB7@.microsoft.com...
> Thanks Andrew:
> But suppose that I already have the 'doc_exc' Table created, but still
havent add a constraint to column_b ... I can find a way to add the
constraint, unless I delete the table and run the script again?[vbcol=seagreen]
> KenA.
> "Andrew J. Kelly" wrote:
set[vbcol=seagreen]

Constraint Doubt

Hi. In a table called Groups with fields GroupID (PK), Name, how can I set a Constraint to make Name filed Unique to avoid:
GroupID | Name
==========
1 | Admin
2 | Admin
Check out ALTER TABLE in BooksOnLine and look at the CONSTRAINT section.
Here is an example directly from BOL:
CREATE TABLE doc_exc ( column_a INT)
GO
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE
Andrew J. Kelly SQL MVP
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:1DD9B537-20E5-4010-A469-03C4259F2C3D@.microsoft.com...
> Hi. In a table called Groups with fields GroupID (PK), Name, how can I set
a Constraint to make Name filed Unique to avoid:
> GroupID | Name
> ==========
> 1 | Admin
> 2 | Admin
|||Thanks Andrew:
But suppose that I already have the 'doc_exc' Table created, but still haven′t add a constraint to column_b ... I can find a way to add the constraint, unless I delete the table and run the script again?
KenA.
"Andrew J. Kelly" wrote:

> Check out ALTER TABLE in BooksOnLine and look at the CONSTRAINT section.
> Here is an example directly from BOL:
> CREATE TABLE doc_exc ( column_a INT)
> GO
> ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
> CONSTRAINT exb_unique UNIQUE
>
> --
> Andrew J. Kelly SQL MVP
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:1DD9B537-20E5-4010-A469-03C4259F2C3D@.microsoft.com...
> a Constraint to make Name filed Unique to avoid:
>
>
|||In that case, just run the Alter table command alone.
John
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:B982E393-F58D-4224-A883-32E8B10AAAB7@.microsoft.com...
> Thanks Andrew:
> But suppose that I already have the 'doc_exc' Table created, but still
havent add a constraint to column_b ... I can find a way to add the
constraint, unless I delete the table and run the script again?[vbcol=seagreen]
> KenA.
> "Andrew J. Kelly" wrote:
set[vbcol=seagreen]

Constraint Doubt

Hi. In a table called Groups with fields GroupID (PK), Name, how can I set a
Constraint to make Name filed Unique to avoid:
GroupID | Name
==========
1 | Admin
2 | AdminCheck out ALTER TABLE in BooksOnLine and look at the CONSTRAINT section.
Here is an example directly from BOL:
CREATE TABLE doc_exc ( column_a INT)
GO
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE
Andrew J. Kelly SQL MVP
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:1DD9B537-20E5-4010-A469-03C4259F2C3D@.microsoft.com...
> Hi. In a table called Groups with fields GroupID (PK), Name, how can I set
a Constraint to make Name filed Unique to avoid:
> GroupID | Name
> ==========
> 1 | Admin
> 2 | Admin|||Thanks Andrew:
But suppose that I already have the 'doc_exc' Table created, but still haven
′t add a constraint to column_b ... I can find a way to add the constraint,
unless I delete the table and run the script again?
KenA.
"Andrew J. Kelly" wrote:

> Check out ALTER TABLE in BooksOnLine and look at the CONSTRAINT section.
> Here is an example directly from BOL:
> CREATE TABLE doc_exc ( column_a INT)
> GO
> ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
> CONSTRAINT exb_unique UNIQUE
>
> --
> Andrew J. Kelly SQL MVP
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:1DD9B537-20E5-4010-A469-03C4259F2C3D@.microsoft.com...
> a Constraint to make Name filed Unique to avoid:
>
>|||In that case, just run the Alter table command alone.
John
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:B982E393-F58D-4224-A883-32E8B10AAAB7@.microsoft.com...
> Thanks Andrew:
> But suppose that I already have the 'doc_exc' Table created, but still
havent add a constraint to column_b ... I can find a way to add the
constraint, unless I delete the table and run the script again?[vbcol=seagreen]
> KenA.
> "Andrew J. Kelly" wrote:
>
set[vbcol=seagreen]

Constraint Doubt

Hi. In a table called Groups with fields GroupID (PK), Name, how can I set a Constraint to make Name filed Unique to avoid:
GroupID | Name
========== 1 | Admin
2 | AdminCheck out ALTER TABLE in BooksOnLine and look at the CONSTRAINT section.
Here is an example directly from BOL:
CREATE TABLE doc_exc ( column_a INT)
GO
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE
Andrew J. Kelly SQL MVP
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:1DD9B537-20E5-4010-A469-03C4259F2C3D@.microsoft.com...
> Hi. In a table called Groups with fields GroupID (PK), Name, how can I set
a Constraint to make Name filed Unique to avoid:
> GroupID | Name
> ==========> 1 | Admin
> 2 | Admin|||Thanks Andrew:
But suppose that I already have the 'doc_exc' Table created, but still haven´t add a constraint to column_b ... I can find a way to add the constraint, unless I delete the table and run the script again?
KenA.
"Andrew J. Kelly" wrote:
> Check out ALTER TABLE in BooksOnLine and look at the CONSTRAINT section.
> Here is an example directly from BOL:
> CREATE TABLE doc_exc ( column_a INT)
> GO
> ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
> CONSTRAINT exb_unique UNIQUE
>
> --
> Andrew J. Kelly SQL MVP
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:1DD9B537-20E5-4010-A469-03C4259F2C3D@.microsoft.com...
> > Hi. In a table called Groups with fields GroupID (PK), Name, how can I set
> a Constraint to make Name filed Unique to avoid:
> >
> > GroupID | Name
> > ==========> > 1 | Admin
> > 2 | Admin
>
>|||In that case, just run the Alter table command alone.
John
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:B982E393-F58D-4224-A883-32E8B10AAAB7@.microsoft.com...
> Thanks Andrew:
> But suppose that I already have the 'doc_exc' Table created, but still
haven´t add a constraint to column_b ... I can find a way to add the
constraint, unless I delete the table and run the script again?
> KenA.
> "Andrew J. Kelly" wrote:
> > Check out ALTER TABLE in BooksOnLine and look at the CONSTRAINT section.
> > Here is an example directly from BOL:
> >
> > CREATE TABLE doc_exc ( column_a INT)
> > GO
> > ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
> > CONSTRAINT exb_unique UNIQUE
> >
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "KenA" <KenA@.discussions.microsoft.com> wrote in message
> > news:1DD9B537-20E5-4010-A469-03C4259F2C3D@.microsoft.com...
> > > Hi. In a table called Groups with fields GroupID (PK), Name, how can I
set
> > a Constraint to make Name filed Unique to avoid:
> > >
> > > GroupID | Name
> > > ==========> > > 1 | Admin
> > > 2 | Admin
> >
> >
> >