"Invalid character value for cast specification"

[Newsgroup_User]

"Invalid character value for cast specification."
MSSQL server 2000 - ASP


I get this when I try to connect to my Stored Procedure.

Through trial and error, I keep eliminating things that might cause this
error but I'm getting close to an empty box of things to look at.

I can post the Stored Procedure but given the error, I thought I'd ask
if there is something I should focus on first.

In a nutshell, the SProc pulls in variables from a form, checks if an
email exists, adds user if it doesn't fail and then returns some
variables if the transaction succeeds.

[Newsgroup_User]

Art wrote:
> "Invalid character value for cast specification."
> MSSQL server 2000 - ASP
>
>
> I get this when I try to connect to my Stored Procedure.
>
> Through trial and error, I keep eliminating things that might cause this
> error but I'm getting close to an empty box of things to look at.
>
> I can post the Stored Procedure but given the error, I thought I'd ask
> if there is something I should focus on first.
>
> In a nutshell, the SProc pulls in variables from a form, checks if an
> email exists, adds user if it doesn't fail and then returns some
> variables if the transaction succeeds.

Sounds like a datatype problem to me. Make sure your parameters are
using the correct values for your table columns.

Steve

[Newsgroup_User]

Dooza wrote:

> Sounds like a datatype problem to me. Make sure your parameters are
> using the correct values for your table columns.
>
> Steve

It's driving me nuts because they look right.

Do you have a list of correct datatypes? For a varchar type, I use:
200, 1, 255,

Do you know where I can find a list of all the different types?

[Newsgroup_User]

Is the @@IDENTITY the Primary Key?

[Newsgroup_User]

Art wrote:
> Is the @@IDENTITY the Primary Key?
I think it is the primary key; from what I can tell, I have set it up as
the "identity" in Enterprise Manager

[Newsgroup_User]

Art wrote:
> Art wrote:
>> Is the @@IDENTITY the Primary Key?
> I think it is the primary key; from what I can tell, I have set it up as
> the "identity" in Enterprise Manager

Being an identity doesn't make it a Primary Key, it just makes it
increment.

In Enterprise Manager right click on your table and click on Design
Table. Then, where you have your identity column, right click on the
black arrow and click on Set Primary Key.

Oh, here is a good link to keep:
http://msdn.microsoft.com/en-us/library/ms806221.aspx

Steve

[Newsgroup_User]

Dooza wrote:

>
> Being an identity doesn't make it a Primary Key, it just makes it
> increment.
>
> In Enterprise Manager right click on your table and click on Design
> Table. Then, where you have your identity column, right click on the
> black arrow and click on Set Primary Key.
>
> Oh, here is a good link to keep:
> http://msdn.microsoft.com/en-us/library/ms806221.aspx
>
> Steve

That's actually what I did. I just didn't realize that the @@IDENTITY
was the same as the "Identity" in Enterprise Manager.

I still can't get the SProc to work. Another week goes by...

By the way, if I do post a SProc, can what I post be used by hackers in
any way if they happen to watch these forums? I usually change the
variable names and table names but if that isn't needed, it would be
much easier to just post it.

If I do post it, would someone be able to look at it? I've had others
look at it but they didn't see any red flags.

[Newsgroup_User]

Art wrote:
> Dooza wrote:
>
>>
>> Being an identity doesn't make it a Primary Key, it just makes it
>> increment.
>>
>> In Enterprise Manager right click on your table and click on Design
>> Table. Then, where you have your identity column, right click on the
>> black arrow and click on Set Primary Key.
>>
>> Oh, here is a good link to keep:
>> http://msdn.microsoft.com/en-us/library/ms806221.aspx
>>
>> Steve
>
> That's actually what I did. I just didn't realize that the @@IDENTITY
> was the same as the "Identity" in Enterprise Manager.
>
> I still can't get the SProc to work. Another week goes by...
>
> By the way, if I do post a SProc, can what I post be used by hackers in
> any way if they happen to watch these forums? I usually change the
> variable names and table names but if that isn't needed, it would be
> much easier to just post it.

I am not an expert, but not giving out the URL of the server is a start,
and making sure the database user id has very limited access, and you
change table names and variable names, you should be fine posting it here.

> If I do post it, would someone be able to look at it? I've had others
> look at it but they didn't see any red flags.

If you don't get much joy here, there is always the microsoft
newsgroups, there is an SQL one there that I use to get help.

Steve

[Newsgroup_User]

Dooza wrote:

>
> I am not an expert, but not giving out the URL of the server is a start,
> and making sure the database user id has very limited access, and you
> change table names and variable names, you should be fine posting it here.
>
>> If I do post it, would someone be able to look at it? I've had others
>> look at it but they didn't see any red flags.
>
> If you don't get much joy here, there is always the microsoft
> newsgroups, there is an SQL one there that I use to get help.
>
> Steve

Just so you know, you guys are a great help. I'm just frustrated that I
can't get past this.

Well, I had very small breakthrough and then got a new error that I
can't get past. I basically pared down the SProc until there was really
nothing left and then when I started adding lines back in, I got a new
error.

[Newsgroup_User]

Art wrote:
> Just so you know, you guys are a great help. I'm just frustrated that I
> can't get past this.
>
> Well, I had very small breakthrough and then got a new error that I
> can't get past. I basically pared down the SProc until there was really
> nothing left and then when I started adding lines back in, I got a new
> error.

Lets see what you have done and what the error message is.

Steve

[Newsgroup_User]

Dooza wrote:

> Lets see what you have done and what the error message is.
>
> Steve

On the page, I get no errors and it saves to both tables if the email
does not exist and doesn't save them if it does. OK, so that works fine.

However, in DW, I am not able to view the recordset; it now says
"Unspecified Error" when I click on the recordset in the Binding Window.

What I suspect is that it's doing all the "insert" stuff just fine but
when it gets to the "SELECT" bit on the end, it's not pulling that in
correctly.

When I place the code on the page manually (not using the binder window)
it SHOULD display the results but when I do this, I get the following error:

Item cannot be found in the collection corresponding to the requested
name or ordinal.

So, that item that I manually displayed, isn't being pulled into the page.

I think I'm really close

Below is the "edited" SProc. You may find some variable naming
incongruities because I might not have been as careful as I should have
when editing it for here.

I get the feeling that the SELECT statement just after the COMMIT
TRANSACTION is formatted wrong. Is it OK to just "plop" it on the end of
this SProc as I have done?

Thanks

@Name varchar (50),
@Last varchar (50),
@Pass varchar (20),
@EMail varchar (50),
@SpSessionID varchar (50),
@SpName varchar (100),
@CustID numeric (9)

As
DECLARE @err1 INT
DECLARE @err2 INT
--If the email is already then return some unique
-- error that we can check or.
IF EXISTS (
SELECT EMail
FROM Cust
WHERE EMail = @EMail)
BEGIN
RETURN -10101
--RETURN 1
END

BEGIN TRANSACTION
-- Email Appears to be unique so
-- Insert the users information
INSERT INTO Customer (Name, Last, Email, Pass)
VALUES (@Name, @Last, @EMail, @Pass)
SET @CustID = @@IDENTITY
SET @err1 = @@ERROR
--SET @err1 = 1
-- If the insert fails...
IF @err1 <> 0
BEGIN
-- ..specfiicaly check if the violated uniqueness
IF @err1 = 547
PRINT 'A constraint error has occurred.'
ELSE
PRINT 'An unspecified error has occurred.'

-- Rollback any changes we may have made
ROLLBACK TRANSACTION
-- Return a non-zero to show error
RETURN @err1
END

-- The insert of users info when well so
-- insert the shipping address
INSERT INTO ShipAdd (ShipN, SpSessionID)
VALUES (@ShipN, @SpSessionID)

SET @err2 = @@ERROR

-- If the insert fails///
IF @err2 <> 0
BEGIN
-- specifically check for violated uniqueness
IF @err2 = 547
PRINT 'A constraint error has occurred.'
ELSE
PRINT 'An unspecified error has occurred.'
-- Rollback the entire transaction (i.e. both
inserts)
ROLLBACK TRANSACTION
-- Return on-zero to show error
RETURN @err2
END

-- Both inserts when OK, so commit the changes
COMMIT TRANSACTION
-- Show we completed OK
--This is where
SELECT *
FROM Customer C
INNER JOIN ShipAdd SA ON C.CustID = SA.CustKey
WHERE SA.CustKey = @CustID
GO

[Newsgroup_User]

I have forwarded this to my home account and will take a look tonight, I
see what your doing, but I have never done it that way, so will see
what I can do.

Steve

[Newsgroup_User]

Dooza wrote:
> I have forwarded this to my home account and will take a look tonight, I
> see what your doing, but I have never done it that way, so will see
> what I can do.
>
> Steve
Thanks

[Newsgroup_User]

Dooza wrote:
> I have forwarded this to my home account and will take a look tonight, I
> see what your doing, but I have never done it that way, so will see
> what I can do.
>
> Steve

Also, if there's an error I need to be able to pull that variable out
and redirect to another page where they can try again.
If there's no error and it submits, I'd like to have them redirected to
a login page.

[Newsgroup_User]

Art wrote:
> Dooza wrote:
>> I have forwarded this to my home account and will take a look tonight,
>> I see what your doing, but I have never done it that way, so will see
>> what I can do.
>>
>> Steve
>
> Also, if there's an error I need to be able to pull that variable out
> and redirect to another page where they can try again.
> If there's no error and it submits, I'd like to have them redirected to
> a login page.
>
>
I can do the redirects, I just don't know how to pull the errors out

[Newsgroup_User]

So, I am now able to:

1. Check if user exists
2. Write to both tables if not
3. Redirect to a login page if the users registration succeeded
4. Redirect to Error page if registration failed.

HOWEVER... The only way I could get this to work was to create a NEW
recordset that did an INNER JOIN of the two tables (Customer ID on
Shipping ID based on the Session ID).

I would, ideally, want to pull it right out of the SProc that inserted
the items in the first place but being a Designer, not a Programmer,
this eludes me.

Is this a legitimate way to do this?

I'm not closing this but I guess I can now start to work through the
rest of the site and come back to my original method when I figure it
out. I think that's progress.

[Newsgroup_User]

Art wrote:
> So, I am now able to:
>
> 1. Check if user exists
> 2. Write to both tables if not
> 3. Redirect to a login page if the users registration succeeded
> 4. Redirect to Error page if registration failed.
>
> HOWEVER... The only way I could get this to work was to create a NEW
> recordset that did an INNER JOIN of the two tables (Customer ID on
> Shipping ID based on the Session ID).
>
> I would, ideally, want to pull it right out of the SProc that inserted
> the items in the first place but being a Designer, not a Programmer,
> this eludes me.
>
> Is this a legitimate way to do this?
>
> I'm not closing this but I guess I can now start to work through the
> rest of the site and come back to my original method when I figure it
> out. I think that's progress.

I had a good look at your code last night and I think your being too
complex, all that error checking as well as checking if the user already
exists seems like overkill to me.

I am going to attempt to create my own version over the next few days
and see what I can make it do.

Steve

[Newsgroup_User]

Dooza wrote:

> I am going to attempt to create my own version over the next few days
> and see what I can make it do.
>
> Steve

Wow! Thanks Hope you can get something out of this.

[Newsgroup_User]

Art wrote:
> Dooza wrote:
>
>> I am going to attempt to create my own version over the next few days
>> and see what I can make it do.
>>
>> Steve
>
> Wow! Thanks Hope you can get something out of this.

This might be foundation of my first tutorial on Stored
Procedures...that way everyone will get something from this.

Steve

[Newsgroup_User]

Dooza wrote:
> Art wrote:
>> Dooza wrote:
>>
>>> I am going to attempt to create my own version over the next few days
>>> and see what I can make it do.
>>>
>>> Steve
>>
>> Wow! Thanks Hope you can get something out of this.
>
> This might be foundation of my first tutorial on Stored
> Procedures...that way everyone will get something from this.
>
> Steve
To everyone,

Somebody REALLY needs to write a book on this stuff. I can't be the only
one out here that needs this sort of help.

I'm not a programmer but I've been doing web design for about 12 years.
Still, I keep pealing layers of this metaphorical onion (programming)
that never ends. Stored Procedures, although they've been around for
most of my Web Design Career, are still a gap in my training. I've
attempted to learn it a few times in the past 10+ years but until last
year, I always got hung up on something that I couldn't get past. This
time I have gotten pretty far with SProcs; I'm not totally satisfied
with my results because everything is just pieced together and I'm sure
there must be best practices that I'm not following. Again, I don't know
what I don't know. A book would be great

And if a book was authored, I think the best format would be in the
format that the OReilly books use (Cookbook).

However, how do you write a book based, in part, on a program
(Dreamweaver CS3) that has a flaw in the Stored Procedure interface? By
the time the book is finished, they may or may not have fixed the bug so
how do technical authors get through that?

Even with this flaw, I think a book that guided people through the
strategies and details of using SProcs with Dreamweaver would be very
helpful to a lot of us.

Steve, I REALLY welcome your intent to do a tutorial on this. Care to
take on the challenge of authoring a book? If you need ideas I have a few.

[Newsgroup_User]

Art wrote:
> Steve, I REALLY welcome your intent to do a tutorial on this. Care to
> take on the challenge of authoring a book? If you need ideas I have a few.

A book? I find it hard enough finishing websites for friends, let alone
a book, but maybe someone else here could? I dunno, this forum is pretty
quiet compared a few years ago.

I too am surprised with the lack of information about stored procedures
in general, not just how you use them with Dreamweaver.

The first thing I need to get sorted is how to by pass Dreamweaver and
write ASP for executing stored procedures. I need to show how to pass
parameters in to the stored procedure, and how to output them to display
on the page.

Some of my basic stored procedures that just accept inputs and then
return a recordset do work with Dreamweaver, so I will show some
examples of those.

I think a create user recordset is a good one to use as an example,
where it first checks if they exist, if they don't then insert, but if
they do, output a message saying they do exist.

Any other ideas?

Steve

[Newsgroup_User]

There are some books that have sections on store procs

The one I have is The Complete Reference SQL, Grpff & Wenberg,
Osborne/McGraw Hill

A google comes up with several including
-Writing Stored Procedures for Microsoft SQL Server (Sams Professional)
-Mysql Stored Procedure Programming, OReilly
-SQL Server 2000 Stored Procedure Programming

[Newsgroup_User]

Philo wrote:
> There are some books that have sections on store procs
>
> The one I have is The Complete Reference SQL, Grpff & Wenberg,
> Osborne/McGraw Hill
>
> A google comes up with several including
> -Writing Stored Procedures for Microsoft SQL Server (Sams Professional)
> -Mysql Stored Procedure Programming, OReilly
> -SQL Server 2000 Stored Procedure Programming
>

Good sources, I'm sure.

I've gotten a number of books over the years that deal with SProcs and
it seems that most show the technical side but fail to tie it all
together in a way that people like me can use.

So far I've adapted to the slow evolution of all this but I fear that
there's going to be a major paradigm shift that will leave me behind.

I'll keep plugging away at this stuff.

[Newsgroup_User]

Dooza wrote:
> Art wrote:
>> Steve, I REALLY welcome your intent to do a tutorial on this. Care to
>> take on the challenge of authoring a book? If you need ideas I have a
>> few.
>
> A book? I find it hard enough finishing websites for friends, let alone
> a book, but maybe someone else here could? I dunno, this forum is pretty
> quiet compared a few years ago.

🙂 I know what you mean. Yes, this forum is pretty quiet.
>
> I too am surprised with the lack of information about stored procedures
> in general, not just how you use them with Dreamweaver.
>
> The first thing I need to get sorted is how to by pass Dreamweaver and
> write ASP for executing stored procedures. I need to show how to pass
> parameters in to the stored procedure, and how to output them to display
> on the page.
>
> Some of my basic stored procedures that just accept inputs and then
> return a recordset do work with Dreamweaver, so I will show some
> examples of those.
>
> I think a create user recordset is a good one to use as an example,
> where it first checks if they exist, if they don't then insert, but if
> they do, output a message saying they do exist.
>
> Any other ideas?
>
> Steve

I've found some good tutorials here

http://www.webthang.co.uk/goto/tutorials/tutorials.asp?cat=2

[Newsgroup_User]

> I've gotten a number of books over the years that deal with SProcs
> and it seems that most show the technical side but fail to tie it all
> together in a way that people like me can use.

Excellent point. I have found a lot of the same. We need real word
examples from simple to moderately difficult with databases to work
with. I worked with Delphi for years and this was a complaint users
had but never really addressed by Borland.

>
> So far I've adapted to the slow evolution of all this but I fear that
> there's going to be a major paradigm shift that will leave me behind.
>

This is the way most of us learn. A little here, a little there, all
at once you have arrived. I don't expect a shift away from sp's
anytime soon, if fact I think they will become more common.



--