error creating named calculation

I'm new to MS DM and am using the book "Data Mining with SQL Server 2005" by Wiley. On page 95 I try to follow the procedure for creating a named calculation, but I get this error: IErrorInfo.GetDescription failed with E_FAIL(0x80004005). Any ideas what's wrong?Can you provide some more details? Are you using Access or SQL as your source database?|||Jamie, Thanks for the help. I'm using the Access MovieClick DB that I downloaded from the Wiley web site. Also, I can create other named calculations just fine, such as adding two columns, or taking the log of one. It just seems to be the CASE examples that give me the error.|||You need to import the data into a SQL Server database using the instructions in the text before using the named calculations.|||Thanks! For those not wanting to convert the Access DB to SQL Server, you can use the Access code =IIF([Num Bedrooms] = 1, 'One',IIF([Num Bedrooms] <=3, 'Two or three',IIF([Num Bedrooms] >= 4, 'Four or more')))sql

Error Could establish connection to the database

Hi,

I encounter few errors when I try to create a login page using tool in Microsoft Visual Studio 2005, please refer to below :

'--------------------------

I create a Login screen from Toolbox.

When I try to configure the setting for Provider Configuration in the ASP.NET Website Administration Tool using Select a single provider for all site management data or Select a different provider for each feature (advanced) options, the error 'Could not establish a connection to the database' appear.

I have tried to run aspnet_regsql to configure the server and database and return to this tool to configure the Provider configuration but the same error 'Could not establish a connection to the database.' appear.

When I click the Security Configuration link in ASP.NET Configuration, the error as shown below appear :

'There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the below button to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem: An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact under the default settings SQL Server does not allow remote connections.(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)'

When I try to choose Data Store in the Security Configuration screen(Figure 4), the same error('Could not establish a connection to the database') appear.

'--------------------

I also have tried to configure the SQL Server Surface Area Configuration but the same error still appear….

Is there any other configuration I need to set before I can create this login page?

Any advices or help is much appreciate.

Thank you.

Regards,

Vicky

Hi Vickie,

Based on the errors, have you verified that you can manually connect to the data source with the connection string information you are using? You can use the information at this link to see if the 'role manager' feature is enabled,http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000013.asp. And, you can use this link to verify that your server will allow remote connections,http://support.microsoft.com/kb/265808/en-us. I hope this helps.

~des

error converting varchar to numeric

i have a huge stored procedure abt 500 lines..and i am calling this sp from an asp.net page...thn i got this error - error converting varchar to numeric - and am trying to debug...is there any way we can find out where the error is coming from...like aproxly which line number..etcor do i have to go through each line manually and see where i am doing the conversion...

thanksNever tried it myself but it should let you step through a sproc like you would your C#/VB.Net code behind.

Walkthrough: Debugging Hello World, a SQL Stored Procedure|||hi MMS

I was able to isolate the line tht was causing the problem..however i will go through the article to see if it will help with some info for future use.

thanks.

Error converting data type varchar to int?

So I have this asp 3.0 page. The page is taking form-submitted data and
saving it to a SQL Server 2000 database via stored procedure.
Just one problem - every time I execute the code, I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type
varchar to int.
/erf2005/forums/abstractcontactinfo1.asp, line 36
Now, this error is a new thing. This page worked fine until I added the
RegID parameter just a little while ago. Somehow that new parameter is the
cause of the error, but after 19 hours on the job, I'll be damned if I can
figure out why. I need fresher eyes. I also need to stop working so damned
much, but that is an issue for another day.
Below is the code form the asp page. Note that the 'line 36' referred to in
the error message is the oCmd.Execute line:
dim oCmd, oCmd4, oCmd2, oCmd5, oCmd6, oCmd7, oCmd8, oCmd9, oCmd10, oCmd11,
oCmd12, oCmd13, oCmd14
dim vRegID
vRegID = Request.Form("RegID")
vRegID = CInt(vRegID)
Set oCmd = GetStoredProcedure(getConnection(),"sp_addAbstract ")
oCmd.Parameters.append oCmd.CreateParameter("RegID", adInteger,
adParamInput,4,vRegID)
oCmd.Parameters.append oCmd.CreateParameter("AbstractTitle", adVarChar,
adParamInput,160,(Request.Form("AbstractTitle")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractText", adLongVarChar,
adParamInput,32000,(Request.Form("AbstractText")))
oCmd.Parameters.append oCmd.CreateParameter("WebAddress", adVarChar,
adParamInput,255,(Request.Form("WebAddress")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractType", adVarChar,
adParamInput,15,(Request.Form("AbstractType")))
oCmd.Parameters.append oCmd.CreateParameter("PresentChoice", adVarChar,
adParamInput,15,(Request.Form("PresentChoice")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice1", adVarChar,
adParamInput,7,(Request.Form("SessionChoice1")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice2", adVarChar,
adParamInput,7,(Request.Form("SessionChoice2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword1", adVarChar,
adParamInput,50,(Request.Form("Keyword1")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword2", adVarChar,
adParamInput,50,(Request.Form("Keyword2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword3", adVarChar,
adParamInput,50,(Request.Form("Keyword3")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword4", adVarChar,
adParamInput,50,(Request.Form("Keyword4")))
oCmd.Parameters.append oCmd.CreateParameter("StudentType", adVarChar,
adParamInput,15,(Request.Form("StudentType")))
oCmd.Parameters.append oCmd.CreateParameter("Judge", adVarChar,
adParamInput,3,(Request.Form("Judge")))
oCmd.Parameters.append oCmd.CreateParameter("Author1FirstInitial",
adVarChar, adParamInput,1,(Request.Form("Author1FirstInitial" )))
oCmd.Parameters.append oCmd.CreateParameter("Author1MI", adVarChar,
adParamInput,1,(Request.Form("Author1MI")))
oCmd.Parameters.append oCmd.CreateParameter("Author1LastName", adVarChar,
adParamInput,50,(Request.Form("Author1LastName")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Organization",
adVarChar, adParamInput,100,(Request.Form("Author1Organizatio n")))
oCmd.Parameters.append oCmd.CreateParameter("Author1City", adVarChar,
adParamInput,50,(Request.Form("Author1City")))
oCmd.Parameters.append oCmd.CreateParameter("Author1State", adVarChar,
adParamInput,25,(Request.Form("Author1State")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Country", adVarChar,
adParamInput,35,(Request.Form("Author1Country")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Email", adVarChar,
adParamInput,75,(Request.Form("Author1Email")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Presents", adVarChar,
adParamInput,3,(Request.Form("Author1Presents")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractID", adInteger,
adParamOutput,4)
oCmd.execute()
And here's the stored procedure:
CREATE Procedure sp_addAbstract
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
@.AbstractTitleVARCHAR(255)= NULL,
@.AbstractTextTEXT,
@.WebAddressVARCHAR(255)= NULL,
@.AbstractTypeVARCHAR(15)= NULL,
@.PresentChoiceVARCHAR(15)= NULL,
@.SessionChoice1VARCHAR(7)= NULL,
@.SessionChoice2VARCHAR(7)= NULL,
@.Keyword1VARCHAR(50)= NULL,
@.Keyword2VARCHAR(50)= NULL,
@.Keyword3VARCHAR(50)= NULL,
@.Keyword4VARCHAR(50)= NULL,
@.StudentType VARCHAR(15)= NULL,
@.Judge VARCHAR(3)= NULL,
@.Author1FirstInitialVARCHAR(1)= NULL,
@.Author1MIVARCHAR(1)= NULL,
@.Author1LastNameVARCHAR(50)= NULL,
@.Author1OrganizationVARCHAR(100)= NULL,
@.Author1CityVARCHAR(50)= NULL,
@.Author1StateVARCHAR(25)= NULL,
@.Author1CountryVARCHAR(35)= NULL,
@.Author1EmailVARCHAR(75)= NULL,
@.Author1PresentsVARCHAR(3)= NULL,
@.RegIDINT,
@.AbstractIDintoutput
as
insert dbo.tblAbstractInfo
(
AbstractTitle,
AbstractText,
WebAddress,
AbstractType,
PresentChoice,
SessionChoice1,
SessionChoice2,
Keyword1,
Keyword2,
Keyword3,
Keyword4,
Author1FirstInitial,
Author1MI,
Author1LastName,
Author1Organization,
Author1City,
Author1State,
Author1Country,
Author1Email,
Author1Presents,
StudentType,
Judge,
RegID
)
values
(
@.AbstractTitle,
@.AbstractText,
@.WebAddress,
@.AbstractType,
@.PresentChoice,
@.SessionChoice1,
@.SessionChoice2,
@.Keyword1,
@.Keyword2,
@.Keyword3,
@.Keyword4,
@.Author1FirstInitial,
@.Author1MI,
@.Author1LastName,
@.Author1Organization,
@.Author1City,
@.Author1State,
@.Author1Country,
@.Author1Email,
@.Author1Presents,
@.StudentType,
@.Judge,
@.RegID
)
set @.AbstractID = @.@.IDENTITY
if(@.@.error <> 0)
return 1
return(0)
GO
Before anyone asks, the underlying table does have a RegID field, and it is
indeed of datatype INT.
So what am I missing?
I'm not an ASP or ADO person, but perhaps ASP/ADO passes the parameters by position and not by name?
You would be able to see this using a Profiler trace. Or do a quick test "positioning" the regid
ASP/ADO parameter in the correct place according to the procedure definition for the parameter.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:C9A88262-AF9A-4270-A21A-C26427EB3529@.microsoft.com...
> So I have this asp 3.0 page. The page is taking form-submitted data and
> saving it to a SQL Server 2000 database via stored procedure.
> Just one problem - every time I execute the code, I get the following error:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type
> varchar to int.
> /erf2005/forums/abstractcontactinfo1.asp, line 36
>
> Now, this error is a new thing. This page worked fine until I added the
> RegID parameter just a little while ago. Somehow that new parameter is the
> cause of the error, but after 19 hours on the job, I'll be damned if I can
> figure out why. I need fresher eyes. I also need to stop working so damned
> much, but that is an issue for another day.
> Below is the code form the asp page. Note that the 'line 36' referred to in
> the error message is the oCmd.Execute line:
>
> dim oCmd, oCmd4, oCmd2, oCmd5, oCmd6, oCmd7, oCmd8, oCmd9, oCmd10, oCmd11,
> oCmd12, oCmd13, oCmd14
> dim vRegID
> vRegID = Request.Form("RegID")
> vRegID = CInt(vRegID)
> Set oCmd = GetStoredProcedure(getConnection(),"sp_addAbstract ")
> oCmd.Parameters.append oCmd.CreateParameter("RegID", adInteger,
> adParamInput,4,vRegID)
> oCmd.Parameters.append oCmd.CreateParameter("AbstractTitle", adVarChar,
> adParamInput,160,(Request.Form("AbstractTitle")))
> oCmd.Parameters.append oCmd.CreateParameter("AbstractText", adLongVarChar,
> adParamInput,32000,(Request.Form("AbstractText")))
> oCmd.Parameters.append oCmd.CreateParameter("WebAddress", adVarChar,
> adParamInput,255,(Request.Form("WebAddress")))
> oCmd.Parameters.append oCmd.CreateParameter("AbstractType", adVarChar,
> adParamInput,15,(Request.Form("AbstractType")))
> oCmd.Parameters.append oCmd.CreateParameter("PresentChoice", adVarChar,
> adParamInput,15,(Request.Form("PresentChoice")))
> oCmd.Parameters.append oCmd.CreateParameter("SessionChoice1", adVarChar,
> adParamInput,7,(Request.Form("SessionChoice1")))
> oCmd.Parameters.append oCmd.CreateParameter("SessionChoice2", adVarChar,
> adParamInput,7,(Request.Form("SessionChoice2")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword1", adVarChar,
> adParamInput,50,(Request.Form("Keyword1")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword2", adVarChar,
> adParamInput,50,(Request.Form("Keyword2")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword3", adVarChar,
> adParamInput,50,(Request.Form("Keyword3")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword4", adVarChar,
> adParamInput,50,(Request.Form("Keyword4")))
> oCmd.Parameters.append oCmd.CreateParameter("StudentType", adVarChar,
> adParamInput,15,(Request.Form("StudentType")))
> oCmd.Parameters.append oCmd.CreateParameter("Judge", adVarChar,
> adParamInput,3,(Request.Form("Judge")))
>
> oCmd.Parameters.append oCmd.CreateParameter("Author1FirstInitial",
> adVarChar, adParamInput,1,(Request.Form("Author1FirstInitial" )))
> oCmd.Parameters.append oCmd.CreateParameter("Author1MI", adVarChar,
> adParamInput,1,(Request.Form("Author1MI")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1LastName", adVarChar,
> adParamInput,50,(Request.Form("Author1LastName")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Organization",
> adVarChar, adParamInput,100,(Request.Form("Author1Organizatio n")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1City", adVarChar,
> adParamInput,50,(Request.Form("Author1City")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1State", adVarChar,
> adParamInput,25,(Request.Form("Author1State")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Country", adVarChar,
> adParamInput,35,(Request.Form("Author1Country")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Email", adVarChar,
> adParamInput,75,(Request.Form("Author1Email")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Presents", adVarChar,
> adParamInput,3,(Request.Form("Author1Presents")))
> oCmd.Parameters.append oCmd.CreateParameter("AbstractID", adInteger,
> adParamOutput,4)
> oCmd.execute()
>
> And here's the stored procedure:
>
> CREATE Procedure sp_addAbstract
> /*
> (
> @.parameter1 datatype = default value,
> @.parameter2 datatype OUTPUT
> )
> */
> @.AbstractTitle VARCHAR(255) = NULL,
> @.AbstractText TEXT,
> @.WebAddress VARCHAR(255) = NULL,
> @.AbstractType VARCHAR(15) = NULL,
> @.PresentChoice VARCHAR(15) = NULL,
> @.SessionChoice1 VARCHAR(7) = NULL,
> @.SessionChoice2 VARCHAR(7) = NULL,
> @.Keyword1 VARCHAR(50) = NULL,
> @.Keyword2 VARCHAR(50) = NULL,
> @.Keyword3 VARCHAR(50) = NULL,
> @.Keyword4 VARCHAR(50) = NULL,
> @.StudentType VARCHAR(15) = NULL,
> @.Judge VARCHAR(3) = NULL,
> @.Author1FirstInitial VARCHAR(1) = NULL,
> @.Author1MI VARCHAR(1) = NULL,
> @.Author1LastName VARCHAR(50) = NULL,
> @.Author1Organization VARCHAR(100) = NULL,
> @.Author1City VARCHAR(50) = NULL,
> @.Author1State VARCHAR(25) = NULL,
> @.Author1Country VARCHAR(35) = NULL,
> @.Author1Email VARCHAR(75) = NULL,
> @.Author1Presents VARCHAR(3) = NULL,
> @.RegID INT,
> @.AbstractID int output
> as
> insert dbo.tblAbstractInfo
> (
> AbstractTitle,
> AbstractText,
> WebAddress,
> AbstractType,
> PresentChoice,
> SessionChoice1,
> SessionChoice2,
> Keyword1,
> Keyword2,
> Keyword3,
> Keyword4,
> Author1FirstInitial,
> Author1MI,
> Author1LastName,
> Author1Organization,
> Author1City,
> Author1State,
> Author1Country,
> Author1Email,
> Author1Presents,
> StudentType,
> Judge,
> RegID
> )
> values
> (
> @.AbstractTitle,
> @.AbstractText,
> @.WebAddress,
> @.AbstractType,
> @.PresentChoice,
> @.SessionChoice1,
> @.SessionChoice2,
> @.Keyword1,
> @.Keyword2,
> @.Keyword3,
> @.Keyword4,
> @.Author1FirstInitial,
> @.Author1MI,
> @.Author1LastName,
> @.Author1Organization,
> @.Author1City,
> @.Author1State,
> @.Author1Country,
> @.Author1Email,
> @.Author1Presents,
> @.StudentType,
> @.Judge,
> @.RegID
> )
> set @.AbstractID = @.@.IDENTITY
>
> if(@.@.error <> 0)
> return 1
> return(0)
> GO
>
> Before anyone asks, the underlying table does have a RegID field, and it is
> indeed of datatype INT.
> So what am I missing?
|||You've misspecified the parameter names. In the procedure, you've indicated
names like @.RegID, @.AbstractTitle, etc.; however, in your ASP code when you
are creating and appendending to your parameters collections, you are
spcifying names like "RegID", "AbstractTitle," etc. These are mismatched;
so, ODBC has to convert to using positional parameters instead of named
parameters.
When you added the RegID to the collection, you did not add it in the same
order as the parameters are listed in the T-SQL stored procedure. This
causes SQL Server to attempt to store the passed "Author1Presents"
VARCHAR(3) value into the @.RegID INT value, which is incompatible.
Also, you are using ASP and ADO. Why are you using ODBC instead of OLEDB
directly? There are several drivers available, check that you are calling
the correct provider: SQLOLEDB or MSDASQL? SQLOLEDB would be the better
choice. If it is because you are using a DSN type configuration
persistance, consider switching to UDL, universal data links, instead, and
use the SQLOLEDB driver directly. Why have the extra API layer to slow you
down?
Sincerely,
Anthony Thomas

"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:C9A88262-AF9A-4270-A21A-C26427EB3529@.microsoft.com...
So I have this asp 3.0 page. The page is taking form-submitted data and
saving it to a SQL Server 2000 database via stored procedure.
Just one problem - every time I execute the code, I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type
varchar to int.
/erf2005/forums/abstractcontactinfo1.asp, line 36
Now, this error is a new thing. This page worked fine until I added the
RegID parameter just a little while ago. Somehow that new parameter is the
cause of the error, but after 19 hours on the job, I'll be damned if I can
figure out why. I need fresher eyes. I also need to stop working so damned
much, but that is an issue for another day.
Below is the code form the asp page. Note that the 'line 36' referred to in
the error message is the oCmd.Execute line:
dim oCmd, oCmd4, oCmd2, oCmd5, oCmd6, oCmd7, oCmd8, oCmd9, oCmd10, oCmd11,
oCmd12, oCmd13, oCmd14
dim vRegID
vRegID = Request.Form("RegID")
vRegID = CInt(vRegID)
Set oCmd = GetStoredProcedure(getConnection(),"sp_addAbstract ")
oCmd.Parameters.append oCmd.CreateParameter("RegID", adInteger,
adParamInput,4,vRegID)
oCmd.Parameters.append oCmd.CreateParameter("AbstractTitle", adVarChar,
adParamInput,160,(Request.Form("AbstractTitle")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractText", adLongVarChar,
adParamInput,32000,(Request.Form("AbstractText")))
oCmd.Parameters.append oCmd.CreateParameter("WebAddress", adVarChar,
adParamInput,255,(Request.Form("WebAddress")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractType", adVarChar,
adParamInput,15,(Request.Form("AbstractType")))
oCmd.Parameters.append oCmd.CreateParameter("PresentChoice", adVarChar,
adParamInput,15,(Request.Form("PresentChoice")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice1", adVarChar,
adParamInput,7,(Request.Form("SessionChoice1")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice2", adVarChar,
adParamInput,7,(Request.Form("SessionChoice2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword1", adVarChar,
adParamInput,50,(Request.Form("Keyword1")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword2", adVarChar,
adParamInput,50,(Request.Form("Keyword2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword3", adVarChar,
adParamInput,50,(Request.Form("Keyword3")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword4", adVarChar,
adParamInput,50,(Request.Form("Keyword4")))
oCmd.Parameters.append oCmd.CreateParameter("StudentType", adVarChar,
adParamInput,15,(Request.Form("StudentType")))
oCmd.Parameters.append oCmd.CreateParameter("Judge", adVarChar,
adParamInput,3,(Request.Form("Judge")))
oCmd.Parameters.append oCmd.CreateParameter("Author1FirstInitial",
adVarChar, adParamInput,1,(Request.Form("Author1FirstInitial" )))
oCmd.Parameters.append oCmd.CreateParameter("Author1MI", adVarChar,
adParamInput,1,(Request.Form("Author1MI")))
oCmd.Parameters.append oCmd.CreateParameter("Author1LastName", adVarChar,
adParamInput,50,(Request.Form("Author1LastName")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Organization",
adVarChar, adParamInput,100,(Request.Form("Author1Organizatio n")))
oCmd.Parameters.append oCmd.CreateParameter("Author1City", adVarChar,
adParamInput,50,(Request.Form("Author1City")))
oCmd.Parameters.append oCmd.CreateParameter("Author1State", adVarChar,
adParamInput,25,(Request.Form("Author1State")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Country", adVarChar,
adParamInput,35,(Request.Form("Author1Country")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Email", adVarChar,
adParamInput,75,(Request.Form("Author1Email")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Presents", adVarChar,
adParamInput,3,(Request.Form("Author1Presents")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractID", adInteger,
adParamOutput,4)
oCmd.execute()
And here's the stored procedure:
CREATE Procedure sp_addAbstract
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
@.AbstractTitle VARCHAR(255) = NULL,
@.AbstractText TEXT,
@.WebAddress VARCHAR(255) = NULL,
@.AbstractType VARCHAR(15) = NULL,
@.PresentChoice VARCHAR(15) = NULL,
@.SessionChoice1 VARCHAR(7) = NULL,
@.SessionChoice2 VARCHAR(7) = NULL,
@.Keyword1 VARCHAR(50) = NULL,
@.Keyword2 VARCHAR(50) = NULL,
@.Keyword3 VARCHAR(50) = NULL,
@.Keyword4 VARCHAR(50) = NULL,
@.StudentType VARCHAR(15) = NULL,
@.Judge VARCHAR(3) = NULL,
@.Author1FirstInitial VARCHAR(1) = NULL,
@.Author1MI VARCHAR(1) = NULL,
@.Author1LastName VARCHAR(50) = NULL,
@.Author1Organization VARCHAR(100) = NULL,
@.Author1City VARCHAR(50) = NULL,
@.Author1State VARCHAR(25) = NULL,
@.Author1Country VARCHAR(35) = NULL,
@.Author1Email VARCHAR(75) = NULL,
@.Author1Presents VARCHAR(3) = NULL,
@.RegID INT,
@.AbstractID int output
as
insert dbo.tblAbstractInfo
(
AbstractTitle,
AbstractText,
WebAddress,
AbstractType,
PresentChoice,
SessionChoice1,
SessionChoice2,
Keyword1,
Keyword2,
Keyword3,
Keyword4,
Author1FirstInitial,
Author1MI,
Author1LastName,
Author1Organization,
Author1City,
Author1State,
Author1Country,
Author1Email,
Author1Presents,
StudentType,
Judge,
RegID
)
values
(
@.AbstractTitle,
@.AbstractText,
@.WebAddress,
@.AbstractType,
@.PresentChoice,
@.SessionChoice1,
@.SessionChoice2,
@.Keyword1,
@.Keyword2,
@.Keyword3,
@.Keyword4,
@.Author1FirstInitial,
@.Author1MI,
@.Author1LastName,
@.Author1Organization,
@.Author1City,
@.Author1State,
@.Author1Country,
@.Author1Email,
@.Author1Presents,
@.StudentType,
@.Judge,
@.RegID
)
set @.AbstractID = @.@.IDENTITY
if(@.@.error <> 0)
return 1
return(0)
GO
Before anyone asks, the underlying table does have a RegID field, and it is
indeed of datatype INT.
So what am I missing?

Error converting data type varchar to int?

So I have this asp 3.0 page. The page is taking form-submitted data and
saving it to a SQL Server 2000 database via stored procedure.
Just one problem - every time I execute the code, I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type
varchar to int.
/erf2005/forums/abstractcontactinfo1.asp, line 36
Now, this error is a new thing. This page worked fine until I added the
RegID parameter just a little while ago. Somehow that new parameter is the
cause of the error, but after 19 hours on the job, I'll be damned if I can
figure out why. I need fresher eyes. I also need to stop working so damned
much, but that is an issue for another day.
Below is the code form the asp page. Note that the 'line 36' referred to in
the error message is the oCmd.Execute line:
dim oCmd, oCmd4, oCmd2, oCmd5, oCmd6, oCmd7, oCmd8, oCmd9, oCmd10, oCmd11,
oCmd12, oCmd13, oCmd14
dim vRegID
vRegID = Request.Form("RegID")
vRegID = CInt(vRegID)
Set oCmd = GetStoredProcedure(getConnection(),"sp_addAbstract")
oCmd.Parameters.append oCmd.CreateParameter("RegID", adInteger,
adParamInput,4,vRegID)
oCmd.Parameters.append oCmd.CreateParameter("AbstractTitle", adVarChar,
adParamInput,160,(Request.Form("AbstractTitle")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractText", adLongVarChar,
adParamInput,32000,(Request.Form("AbstractText")))
oCmd.Parameters.append oCmd.CreateParameter("WebAddress", adVarChar,
adParamInput,255,(Request.Form("WebAddress")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractType", adVarChar,
adParamInput,15,(Request.Form("AbstractType")))
oCmd.Parameters.append oCmd.CreateParameter("PresentChoice", adVarChar,
adParamInput,15,(Request.Form("PresentChoice")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice1", adVarChar,
adParamInput,7,(Request.Form("SessionChoice1")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice2", adVarChar,
adParamInput,7,(Request.Form("SessionChoice2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword1", adVarChar,
adParamInput,50,(Request.Form("Keyword1")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword2", adVarChar,
adParamInput,50,(Request.Form("Keyword2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword3", adVarChar,
adParamInput,50,(Request.Form("Keyword3")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword4", adVarChar,
adParamInput,50,(Request.Form("Keyword4")))
oCmd.Parameters.append oCmd.CreateParameter("StudentType", adVarChar,
adParamInput,15,(Request.Form("StudentType")))
oCmd.Parameters.append oCmd.CreateParameter("Judge", adVarChar,
adParamInput,3,(Request.Form("Judge")))
oCmd.Parameters.append oCmd.CreateParameter("Author1FirstInitial",
adVarChar, adParamInput,1,(Request.Form("Author1FirstInitial")))
oCmd.Parameters.append oCmd.CreateParameter("Author1MI", adVarChar,
adParamInput,1,(Request.Form("Author1MI")))
oCmd.Parameters.append oCmd.CreateParameter("Author1LastName", adVarChar,
adParamInput,50,(Request.Form("Author1LastName")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Organization",
adVarChar, adParamInput,100,(Request.Form("Author1Organization")))
oCmd.Parameters.append oCmd.CreateParameter("Author1City", adVarChar,
adParamInput,50,(Request.Form("Author1City")))
oCmd.Parameters.append oCmd.CreateParameter("Author1State", adVarChar,
adParamInput,25,(Request.Form("Author1State")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Country", adVarChar,
adParamInput,35,(Request.Form("Author1Country")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Email", adVarChar,
adParamInput,75,(Request.Form("Author1Email")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Presents", adVarChar,
adParamInput,3,(Request.Form("Author1Presents")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractID", adInteger,
adParamOutput,4)
oCmd.execute()
And here's the stored procedure:
CREATE Procedure sp_addAbstract
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
@.AbstractTitle VARCHAR(255) = NULL,
@.AbstractText TEXT,
@.WebAddress VARCHAR(255) = NULL,
@.AbstractType VARCHAR(15) = NULL,
@.PresentChoice VARCHAR(15) = NULL,
@.SessionChoice1 VARCHAR(7) = NULL,
@.SessionChoice2 VARCHAR(7) = NULL,
@.Keyword1 VARCHAR(50) = NULL,
@.Keyword2 VARCHAR(50) = NULL,
@.Keyword3 VARCHAR(50) = NULL,
@.Keyword4 VARCHAR(50) = NULL,
@.StudentType VARCHAR(15) = NULL,
@.Judge VARCHAR(3) = NULL,
@.Author1FirstInitial VARCHAR(1) = NULL,
@.Author1MI VARCHAR(1) = NULL,
@.Author1LastName VARCHAR(50) = NULL,
@.Author1Organization VARCHAR(100) = NULL,
@.Author1City VARCHAR(50) = NULL,
@.Author1State VARCHAR(25) = NULL,
@.Author1Country VARCHAR(35) = NULL,
@.Author1Email VARCHAR(75) = NULL,
@.Author1Presents VARCHAR(3) = NULL,
@.RegID INT,
@.AbstractID int output
as
insert dbo.tblAbstractInfo
(
AbstractTitle,
AbstractText,
WebAddress,
AbstractType,
PresentChoice,
SessionChoice1,
SessionChoice2,
Keyword1,
Keyword2,
Keyword3,
Keyword4,
Author1FirstInitial,
Author1MI,
Author1LastName,
Author1Organization,
Author1City,
Author1State,
Author1Country,
Author1Email,
Author1Presents,
StudentType,
Judge,
RegID
)
values
(
@.AbstractTitle,
@.AbstractText,
@.WebAddress,
@.AbstractType,
@.PresentChoice,
@.SessionChoice1,
@.SessionChoice2,
@.Keyword1,
@.Keyword2,
@.Keyword3,
@.Keyword4,
@.Author1FirstInitial,
@.Author1MI,
@.Author1LastName,
@.Author1Organization,
@.Author1City,
@.Author1State,
@.Author1Country,
@.Author1Email,
@.Author1Presents,
@.StudentType,
@.Judge,
@.RegID
)
set @.AbstractID = @.@.IDENTITY
if(@.@.error <> 0)
return 1
return(0)
GO
Before anyone asks, the underlying table does have a RegID field, and it is
indeed of datatype INT.
So what am I missing?I'm not an ASP or ADO person, but perhaps ASP/ADO passes the parameters by position and not by name?
You would be able to see this using a Profiler trace. Or do a quick test "positioning" the regid
ASP/ADO parameter in the correct place according to the procedure definition for the parameter.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:C9A88262-AF9A-4270-A21A-C26427EB3529@.microsoft.com...
> So I have this asp 3.0 page. The page is taking form-submitted data and
> saving it to a SQL Server 2000 database via stored procedure.
> Just one problem - every time I execute the code, I get the following error:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type
> varchar to int.
> /erf2005/forums/abstractcontactinfo1.asp, line 36
>
> Now, this error is a new thing. This page worked fine until I added the
> RegID parameter just a little while ago. Somehow that new parameter is the
> cause of the error, but after 19 hours on the job, I'll be damned if I can
> figure out why. I need fresher eyes. I also need to stop working so damned
> much, but that is an issue for another day.
> Below is the code form the asp page. Note that the 'line 36' referred to in
> the error message is the oCmd.Execute line:
>
> dim oCmd, oCmd4, oCmd2, oCmd5, oCmd6, oCmd7, oCmd8, oCmd9, oCmd10, oCmd11,
> oCmd12, oCmd13, oCmd14
> dim vRegID
> vRegID = Request.Form("RegID")
> vRegID = CInt(vRegID)
> Set oCmd = GetStoredProcedure(getConnection(),"sp_addAbstract")
> oCmd.Parameters.append oCmd.CreateParameter("RegID", adInteger,
> adParamInput,4,vRegID)
> oCmd.Parameters.append oCmd.CreateParameter("AbstractTitle", adVarChar,
> adParamInput,160,(Request.Form("AbstractTitle")))
> oCmd.Parameters.append oCmd.CreateParameter("AbstractText", adLongVarChar,
> adParamInput,32000,(Request.Form("AbstractText")))
> oCmd.Parameters.append oCmd.CreateParameter("WebAddress", adVarChar,
> adParamInput,255,(Request.Form("WebAddress")))
> oCmd.Parameters.append oCmd.CreateParameter("AbstractType", adVarChar,
> adParamInput,15,(Request.Form("AbstractType")))
> oCmd.Parameters.append oCmd.CreateParameter("PresentChoice", adVarChar,
> adParamInput,15,(Request.Form("PresentChoice")))
> oCmd.Parameters.append oCmd.CreateParameter("SessionChoice1", adVarChar,
> adParamInput,7,(Request.Form("SessionChoice1")))
> oCmd.Parameters.append oCmd.CreateParameter("SessionChoice2", adVarChar,
> adParamInput,7,(Request.Form("SessionChoice2")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword1", adVarChar,
> adParamInput,50,(Request.Form("Keyword1")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword2", adVarChar,
> adParamInput,50,(Request.Form("Keyword2")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword3", adVarChar,
> adParamInput,50,(Request.Form("Keyword3")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword4", adVarChar,
> adParamInput,50,(Request.Form("Keyword4")))
> oCmd.Parameters.append oCmd.CreateParameter("StudentType", adVarChar,
> adParamInput,15,(Request.Form("StudentType")))
> oCmd.Parameters.append oCmd.CreateParameter("Judge", adVarChar,
> adParamInput,3,(Request.Form("Judge")))
>
> oCmd.Parameters.append oCmd.CreateParameter("Author1FirstInitial",
> adVarChar, adParamInput,1,(Request.Form("Author1FirstInitial")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1MI", adVarChar,
> adParamInput,1,(Request.Form("Author1MI")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1LastName", adVarChar,
> adParamInput,50,(Request.Form("Author1LastName")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Organization",
> adVarChar, adParamInput,100,(Request.Form("Author1Organization")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1City", adVarChar,
> adParamInput,50,(Request.Form("Author1City")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1State", adVarChar,
> adParamInput,25,(Request.Form("Author1State")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Country", adVarChar,
> adParamInput,35,(Request.Form("Author1Country")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Email", adVarChar,
> adParamInput,75,(Request.Form("Author1Email")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Presents", adVarChar,
> adParamInput,3,(Request.Form("Author1Presents")))
> oCmd.Parameters.append oCmd.CreateParameter("AbstractID", adInteger,
> adParamOutput,4)
> oCmd.execute()
>
> And here's the stored procedure:
>
> CREATE Procedure sp_addAbstract
> /*
> (
> @.parameter1 datatype = default value,
> @.parameter2 datatype OUTPUT
> )
> */
> @.AbstractTitle VARCHAR(255) = NULL,
> @.AbstractText TEXT,
> @.WebAddress VARCHAR(255) = NULL,
> @.AbstractType VARCHAR(15) = NULL,
> @.PresentChoice VARCHAR(15) = NULL,
> @.SessionChoice1 VARCHAR(7) = NULL,
> @.SessionChoice2 VARCHAR(7) = NULL,
> @.Keyword1 VARCHAR(50) = NULL,
> @.Keyword2 VARCHAR(50) = NULL,
> @.Keyword3 VARCHAR(50) = NULL,
> @.Keyword4 VARCHAR(50) = NULL,
> @.StudentType VARCHAR(15) = NULL,
> @.Judge VARCHAR(3) = NULL,
> @.Author1FirstInitial VARCHAR(1) = NULL,
> @.Author1MI VARCHAR(1) = NULL,
> @.Author1LastName VARCHAR(50) = NULL,
> @.Author1Organization VARCHAR(100) = NULL,
> @.Author1City VARCHAR(50) = NULL,
> @.Author1State VARCHAR(25) = NULL,
> @.Author1Country VARCHAR(35) = NULL,
> @.Author1Email VARCHAR(75) = NULL,
> @.Author1Presents VARCHAR(3) = NULL,
> @.RegID INT,
> @.AbstractID int output
> as
> insert dbo.tblAbstractInfo
> (
> AbstractTitle,
> AbstractText,
> WebAddress,
> AbstractType,
> PresentChoice,
> SessionChoice1,
> SessionChoice2,
> Keyword1,
> Keyword2,
> Keyword3,
> Keyword4,
> Author1FirstInitial,
> Author1MI,
> Author1LastName,
> Author1Organization,
> Author1City,
> Author1State,
> Author1Country,
> Author1Email,
> Author1Presents,
> StudentType,
> Judge,
> RegID
> )
> values
> (
> @.AbstractTitle,
> @.AbstractText,
> @.WebAddress,
> @.AbstractType,
> @.PresentChoice,
> @.SessionChoice1,
> @.SessionChoice2,
> @.Keyword1,
> @.Keyword2,
> @.Keyword3,
> @.Keyword4,
> @.Author1FirstInitial,
> @.Author1MI,
> @.Author1LastName,
> @.Author1Organization,
> @.Author1City,
> @.Author1State,
> @.Author1Country,
> @.Author1Email,
> @.Author1Presents,
> @.StudentType,
> @.Judge,
> @.RegID
> )
> set @.AbstractID = @.@.IDENTITY
>
> if(@.@.error <> 0)
> return 1
> return(0)
> GO
>
> Before anyone asks, the underlying table does have a RegID field, and it is
> indeed of datatype INT.
> So what am I missing?|||You've misspecified the parameter names. In the procedure, you've indicated
names like @.RegID, @.AbstractTitle, etc.; however, in your ASP code when you
are creating and appendending to your parameters collections, you are
spcifying names like "RegID", "AbstractTitle," etc. These are mismatched;
so, ODBC has to convert to using positional parameters instead of named
parameters.
When you added the RegID to the collection, you did not add it in the same
order as the parameters are listed in the T-SQL stored procedure. This
causes SQL Server to attempt to store the passed "Author1Presents"
VARCHAR(3) value into the @.RegID INT value, which is incompatible.
Also, you are using ASP and ADO. Why are you using ODBC instead of OLEDB
directly? There are several drivers available, check that you are calling
the correct provider: SQLOLEDB or MSDASQL? SQLOLEDB would be the better
choice. If it is because you are using a DSN type configuration
persistance, consider switching to UDL, universal data links, instead, and
use the SQLOLEDB driver directly. Why have the extra API layer to slow you
down?
Sincerely,
Anthony Thomas
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:C9A88262-AF9A-4270-A21A-C26427EB3529@.microsoft.com...
So I have this asp 3.0 page. The page is taking form-submitted data and
saving it to a SQL Server 2000 database via stored procedure.
Just one problem - every time I execute the code, I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type
varchar to int.
/erf2005/forums/abstractcontactinfo1.asp, line 36
Now, this error is a new thing. This page worked fine until I added the
RegID parameter just a little while ago. Somehow that new parameter is the
cause of the error, but after 19 hours on the job, I'll be damned if I can
figure out why. I need fresher eyes. I also need to stop working so damned
much, but that is an issue for another day.
Below is the code form the asp page. Note that the 'line 36' referred to in
the error message is the oCmd.Execute line:
dim oCmd, oCmd4, oCmd2, oCmd5, oCmd6, oCmd7, oCmd8, oCmd9, oCmd10, oCmd11,
oCmd12, oCmd13, oCmd14
dim vRegID
vRegID = Request.Form("RegID")
vRegID = CInt(vRegID)
Set oCmd = GetStoredProcedure(getConnection(),"sp_addAbstract")
oCmd.Parameters.append oCmd.CreateParameter("RegID", adInteger,
adParamInput,4,vRegID)
oCmd.Parameters.append oCmd.CreateParameter("AbstractTitle", adVarChar,
adParamInput,160,(Request.Form("AbstractTitle")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractText", adLongVarChar,
adParamInput,32000,(Request.Form("AbstractText")))
oCmd.Parameters.append oCmd.CreateParameter("WebAddress", adVarChar,
adParamInput,255,(Request.Form("WebAddress")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractType", adVarChar,
adParamInput,15,(Request.Form("AbstractType")))
oCmd.Parameters.append oCmd.CreateParameter("PresentChoice", adVarChar,
adParamInput,15,(Request.Form("PresentChoice")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice1", adVarChar,
adParamInput,7,(Request.Form("SessionChoice1")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice2", adVarChar,
adParamInput,7,(Request.Form("SessionChoice2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword1", adVarChar,
adParamInput,50,(Request.Form("Keyword1")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword2", adVarChar,
adParamInput,50,(Request.Form("Keyword2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword3", adVarChar,
adParamInput,50,(Request.Form("Keyword3")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword4", adVarChar,
adParamInput,50,(Request.Form("Keyword4")))
oCmd.Parameters.append oCmd.CreateParameter("StudentType", adVarChar,
adParamInput,15,(Request.Form("StudentType")))
oCmd.Parameters.append oCmd.CreateParameter("Judge", adVarChar,
adParamInput,3,(Request.Form("Judge")))
oCmd.Parameters.append oCmd.CreateParameter("Author1FirstInitial",
adVarChar, adParamInput,1,(Request.Form("Author1FirstInitial")))
oCmd.Parameters.append oCmd.CreateParameter("Author1MI", adVarChar,
adParamInput,1,(Request.Form("Author1MI")))
oCmd.Parameters.append oCmd.CreateParameter("Author1LastName", adVarChar,
adParamInput,50,(Request.Form("Author1LastName")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Organization",
adVarChar, adParamInput,100,(Request.Form("Author1Organization")))
oCmd.Parameters.append oCmd.CreateParameter("Author1City", adVarChar,
adParamInput,50,(Request.Form("Author1City")))
oCmd.Parameters.append oCmd.CreateParameter("Author1State", adVarChar,
adParamInput,25,(Request.Form("Author1State")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Country", adVarChar,
adParamInput,35,(Request.Form("Author1Country")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Email", adVarChar,
adParamInput,75,(Request.Form("Author1Email")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Presents", adVarChar,
adParamInput,3,(Request.Form("Author1Presents")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractID", adInteger,
adParamOutput,4)
oCmd.execute()
And here's the stored procedure:
CREATE Procedure sp_addAbstract
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
@.AbstractTitle VARCHAR(255) = NULL,
@.AbstractText TEXT,
@.WebAddress VARCHAR(255) = NULL,
@.AbstractType VARCHAR(15) = NULL,
@.PresentChoice VARCHAR(15) = NULL,
@.SessionChoice1 VARCHAR(7) = NULL,
@.SessionChoice2 VARCHAR(7) = NULL,
@.Keyword1 VARCHAR(50) = NULL,
@.Keyword2 VARCHAR(50) = NULL,
@.Keyword3 VARCHAR(50) = NULL,
@.Keyword4 VARCHAR(50) = NULL,
@.StudentType VARCHAR(15) = NULL,
@.Judge VARCHAR(3) = NULL,
@.Author1FirstInitial VARCHAR(1) = NULL,
@.Author1MI VARCHAR(1) = NULL,
@.Author1LastName VARCHAR(50) = NULL,
@.Author1Organization VARCHAR(100) = NULL,
@.Author1City VARCHAR(50) = NULL,
@.Author1State VARCHAR(25) = NULL,
@.Author1Country VARCHAR(35) = NULL,
@.Author1Email VARCHAR(75) = NULL,
@.Author1Presents VARCHAR(3) = NULL,
@.RegID INT,
@.AbstractID int output
as
insert dbo.tblAbstractInfo
(
AbstractTitle,
AbstractText,
WebAddress,
AbstractType,
PresentChoice,
SessionChoice1,
SessionChoice2,
Keyword1,
Keyword2,
Keyword3,
Keyword4,
Author1FirstInitial,
Author1MI,
Author1LastName,
Author1Organization,
Author1City,
Author1State,
Author1Country,
Author1Email,
Author1Presents,
StudentType,
Judge,
RegID
)
values
(
@.AbstractTitle,
@.AbstractText,
@.WebAddress,
@.AbstractType,
@.PresentChoice,
@.SessionChoice1,
@.SessionChoice2,
@.Keyword1,
@.Keyword2,
@.Keyword3,
@.Keyword4,
@.Author1FirstInitial,
@.Author1MI,
@.Author1LastName,
@.Author1Organization,
@.Author1City,
@.Author1State,
@.Author1Country,
@.Author1Email,
@.Author1Presents,
@.StudentType,
@.Judge,
@.RegID
)
set @.AbstractID = @.@.IDENTITY
if(@.@.error <> 0)
return 1
return(0)
GO
Before anyone asks, the underlying table does have a RegID field, and it is
indeed of datatype INT.
So what am I missing?

Error converting data type varchar to int?

So I have this asp 3.0 page. The page is taking form-submitted data and
saving it to a SQL Server 2000 database via stored procedure.
Just one problem - every time I execute the code, I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting
data type
varchar to int.
/erf2005/forums/abstractcontactinfo1.asp, line 36
Now, this error is a new thing. This page worked fine until I added the
RegID parameter just a little while ago. Somehow that new parameter is the
cause of the error, but after 19 hours on the job, I'll be damned if I can
figure out why. I need fresher eyes. I also need to stop working so damned
much, but that is an issue for another day.
Below is the code form the asp page. Note that the 'line 36' referred to in
the error message is the oCmd.Execute line:
dim oCmd, oCmd4, oCmd2, oCmd5, oCmd6, oCmd7, oCmd8, oCmd9, oCmd10, oCmd11,
oCmd12, oCmd13, oCmd14
dim vRegID
vRegID = Request.Form("RegID")
vRegID = CInt(vRegID)
Set oCmd = GetStoredProcedure(getConnection(),"sp_addAbstract")
oCmd.Parameters.append oCmd.CreateParameter("RegID", adInteger,
adParamInput,4,vRegID)
oCmd.Parameters.append oCmd.CreateParameter("AbstractTitle", adVarChar,
adParamInput,160,(Request.Form("AbstractTitle")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractText", adLongVarChar,
adParamInput,32000,(Request.Form("AbstractText")))
oCmd.Parameters.append oCmd.CreateParameter("WebAddress", adVarChar,
adParamInput,255,(Request.Form("WebAddress")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractType", adVarChar,
adParamInput,15,(Request.Form("AbstractType")))
oCmd.Parameters.append oCmd.CreateParameter("PresentChoice", adVarChar,
adParamInput,15,(Request.Form("PresentChoice")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice1", adVarChar,
adParamInput,7,(Request.Form("SessionChoice1")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice2", adVarChar,
adParamInput,7,(Request.Form("SessionChoice2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword1", adVarChar,
adParamInput,50,(Request.Form("Keyword1")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword2", adVarChar,
adParamInput,50,(Request.Form("Keyword2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword3", adVarChar,
adParamInput,50,(Request.Form("Keyword3")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword4", adVarChar,
adParamInput,50,(Request.Form("Keyword4")))
oCmd.Parameters.append oCmd.CreateParameter("StudentType", adVarChar,
adParamInput,15,(Request.Form("StudentType")))
oCmd.Parameters.append oCmd.CreateParameter("Judge", adVarChar,
adParamInput,3,(Request.Form("Judge")))
oCmd.Parameters.append oCmd.CreateParameter("Author1FirstInitial",
adVarChar, adParamInput,1,(Request.Form("Author1FirstInitial")))
oCmd.Parameters.append oCmd.CreateParameter("Author1MI", adVarChar,
adParamInput,1,(Request.Form("Author1MI")))
oCmd.Parameters.append oCmd.CreateParameter("Author1LastName", adVarChar,
adParamInput,50,(Request.Form("Author1LastName")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Organization",
adVarChar, adParamInput,100,(Request.Form("Author1Organization")))
oCmd.Parameters.append oCmd.CreateParameter("Author1City", adVarChar,
adParamInput,50,(Request.Form("Author1City")))
oCmd.Parameters.append oCmd.CreateParameter("Author1State", adVarChar,
adParamInput,25,(Request.Form("Author1State")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Country", adVarChar,
adParamInput,35,(Request.Form("Author1Country")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Email", adVarChar,
adParamInput,75,(Request.Form("Author1Email")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Presents", adVarChar,
adParamInput,3,(Request.Form("Author1Presents")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractID", adInteger,
adParamOutput,4)
oCmd.execute()
And here's the stored procedure:
CREATE Procedure sp_addAbstract
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
@.AbstractTitle VARCHAR(255) = NULL,
@.AbstractText TEXT,
@.WebAddress VARCHAR(255) = NULL,
@.AbstractType VARCHAR(15) = NULL,
@.PresentChoice VARCHAR(15) = NULL,
@.SessionChoice1 VARCHAR(7) = NULL,
@.SessionChoice2 VARCHAR(7) = NULL,
@.Keyword1 VARCHAR(50) = NULL,
@.Keyword2 VARCHAR(50) = NULL,
@.Keyword3 VARCHAR(50) = NULL,
@.Keyword4 VARCHAR(50) = NULL,
@.StudentType VARCHAR(15) = NULL,
@.Judge VARCHAR(3) = NULL,
@.Author1FirstInitial VARCHAR(1) = NULL,
@.Author1MI VARCHAR(1) = NULL,
@.Author1LastName VARCHAR(50) = NULL,
@.Author1Organization VARCHAR(100)
= NULL,
@.Author1City VARCHAR(50) = NULL,
@.Author1State VARCHAR(25) = NULL,
@.Author1Country VARCHAR(35) = NULL,
@.Author1Email VARCHAR(75) = NULL,
@.Author1Presents VARCHAR(3) = NULL,
@.RegID INT,
@.AbstractID int output
as
insert dbo.tblAbstractInfo
(
AbstractTitle,
AbstractText,
WebAddress,
AbstractType,
PresentChoice,
SessionChoice1,
SessionChoice2,
Keyword1,
Keyword2,
Keyword3,
Keyword4,
Author1FirstInitial,
Author1MI,
Author1LastName,
Author1Organization,
Author1City,
Author1State,
Author1Country,
Author1Email,
Author1Presents,
StudentType,
Judge,
RegID
)
values
(
@.AbstractTitle,
@.AbstractText,
@.WebAddress,
@.AbstractType,
@.PresentChoice,
@.SessionChoice1,
@.SessionChoice2,
@.Keyword1,
@.Keyword2,
@.Keyword3,
@.Keyword4,
@.Author1FirstInitial,
@.Author1MI,
@.Author1LastName,
@.Author1Organization,
@.Author1City,
@.Author1State,
@.Author1Country,
@.Author1Email,
@.Author1Presents,
@.StudentType,
@.Judge,
@.RegID
)
set @.AbstractID = @.@.IDENTITY
if(@.@.error <> 0)
return 1
return(0)
GO
Before anyone asks, the underlying table does have a RegID field, and it is
indeed of datatype INT.
So what am I missing?I'm not an ASP or ADO person, but perhaps ASP/ADO passes the parameters by p
osition and not by name?
You would be able to see this using a Profiler trace. Or do a quick test "po
sitioning" the regid
ASP/ADO parameter in the correct place according to the procedure definition
for the parameter.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:C9A88262-AF9A-4270-A21A-C26427EB3529@.microsoft.com...
> So I have this asp 3.0 page. The page is taking form-submitted data and
> saving it to a SQL Server 2000 database via stored procedure.
> Just one problem - every time I execute the code, I get the following erro
r:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Error convertin
g data type
> varchar to int.
> /erf2005/forums/abstractcontactinfo1.asp, line 36
>
> Now, this error is a new thing. This page worked fine until I added the
> RegID parameter just a little while ago. Somehow that new parameter is the
> cause of the error, but after 19 hours on the job, I'll be damned if I can
> figure out why. I need fresher eyes. I also need to stop working so damned
> much, but that is an issue for another day.
> Below is the code form the asp page. Note that the 'line 36' referred to i
n
> the error message is the oCmd.Execute line:
>
> dim oCmd, oCmd4, oCmd2, oCmd5, oCmd6, oCmd7, oCmd8, oCmd9, oCmd10, oCmd11,
> oCmd12, oCmd13, oCmd14
> dim vRegID
> vRegID = Request.Form("RegID")
> vRegID = CInt(vRegID)
> Set oCmd = GetStoredProcedure(getConnection(),"sp_addAbstract")
> oCmd.Parameters.append oCmd.CreateParameter("RegID", adInteger,
> adParamInput,4,vRegID)
> oCmd.Parameters.append oCmd.CreateParameter("AbstractTitle", adVarChar,
> adParamInput,160,(Request.Form("AbstractTitle")))
> oCmd.Parameters.append oCmd.CreateParameter("AbstractText", adLongVarChar,
> adParamInput,32000,(Request.Form("AbstractText")))
> oCmd.Parameters.append oCmd.CreateParameter("WebAddress", adVarChar,
> adParamInput,255,(Request.Form("WebAddress")))
> oCmd.Parameters.append oCmd.CreateParameter("AbstractType", adVarChar,
> adParamInput,15,(Request.Form("AbstractType")))
> oCmd.Parameters.append oCmd.CreateParameter("PresentChoice", adVarChar,
> adParamInput,15,(Request.Form("PresentChoice")))
> oCmd.Parameters.append oCmd.CreateParameter("SessionChoice1", adVarChar,
> adParamInput,7,(Request.Form("SessionChoice1")))
> oCmd.Parameters.append oCmd.CreateParameter("SessionChoice2", adVarChar,
> adParamInput,7,(Request.Form("SessionChoice2")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword1", adVarChar,
> adParamInput,50,(Request.Form("Keyword1")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword2", adVarChar,
> adParamInput,50,(Request.Form("Keyword2")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword3", adVarChar,
> adParamInput,50,(Request.Form("Keyword3")))
> oCmd.Parameters.append oCmd.CreateParameter("Keyword4", adVarChar,
> adParamInput,50,(Request.Form("Keyword4")))
> oCmd.Parameters.append oCmd.CreateParameter("StudentType", adVarChar,
> adParamInput,15,(Request.Form("StudentType")))
> oCmd.Parameters.append oCmd.CreateParameter("Judge", adVarChar,
> adParamInput,3,(Request.Form("Judge")))
>
> oCmd.Parameters.append oCmd.CreateParameter("Author1FirstInitial",
> adVarChar, adParamInput,1,(Request.Form("Author1FirstInitial")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1MI", adVarChar,
> adParamInput,1,(Request.Form("Author1MI")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1LastName", adVarChar,
> adParamInput,50,(Request.Form("Author1LastName")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Organization",
> adVarChar, adParamInput,100,(Request.Form("Author1Organization")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1City", adVarChar,
> adParamInput,50,(Request.Form("Author1City")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1State", adVarChar,
> adParamInput,25,(Request.Form("Author1State")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Country", adVarChar,
> adParamInput,35,(Request.Form("Author1Country")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Email", adVarChar,
> adParamInput,75,(Request.Form("Author1Email")))
> oCmd.Parameters.append oCmd.CreateParameter("Author1Presents", adVarChar,
> adParamInput,3,(Request.Form("Author1Presents")))
> oCmd.Parameters.append oCmd.CreateParameter("AbstractID", adInteger,
> adParamOutput,4)
> oCmd.execute()
>
> And here's the stored procedure:
>
> CREATE Procedure sp_addAbstract
> /*
> (
> @.parameter1 datatype = default value,
> @.parameter2 datatype OUTPUT
> )
> */
> @.AbstractTitle VARCHAR(255) = NULL,
> @.AbstractText TEXT,
> @.WebAddress VARCHAR(255) = NULL,
> @.AbstractType VARCHAR(15) = NULL,
> @.PresentChoice VARCHAR(15) = NULL,
> @.SessionChoice1 VARCHAR(7) = NULL,
> @.SessionChoice2 VARCHAR(7) = NULL,
> @.Keyword1 VARCHAR(50) = NULL,
> @.Keyword2 VARCHAR(50) = NULL,
> @.Keyword3 VARCHAR(50) = NULL,
> @.Keyword4 VARCHAR(50) = NULL,
> @.StudentType VARCHAR(15) = NULL,
> @.Judge VARCHAR(3) = NULL,
> @.Author1FirstInitial VARCHAR(1) = NULL,
> @.Author1MI VARCHAR(1) = NULL,
> @.Author1LastName VARCHAR(50) = NULL,
> @.Author1Organization VARCHAR(100) = NULL,
> @.Author1City VARCHAR(50) = NULL,
> @.Author1State VARCHAR(25) = NULL,
> @.Author1Country VARCHAR(35) = NULL,
> @.Author1Email VARCHAR(75) = NULL,
> @.Author1Presents VARCHAR(3) = NULL,
> @.RegID INT,
> @.AbstractID int output
> as
> insert dbo.tblAbstractInfo
> (
> AbstractTitle,
> AbstractText,
> WebAddress,
> AbstractType,
> PresentChoice,
> SessionChoice1,
> SessionChoice2,
> Keyword1,
> Keyword2,
> Keyword3,
> Keyword4,
> Author1FirstInitial,
> Author1MI,
> Author1LastName,
> Author1Organization,
> Author1City,
> Author1State,
> Author1Country,
> Author1Email,
> Author1Presents,
> StudentType,
> Judge,
> RegID
> )
> values
> (
> @.AbstractTitle,
> @.AbstractText,
> @.WebAddress,
> @.AbstractType,
> @.PresentChoice,
> @.SessionChoice1,
> @.SessionChoice2,
> @.Keyword1,
> @.Keyword2,
> @.Keyword3,
> @.Keyword4,
> @.Author1FirstInitial,
> @.Author1MI,
> @.Author1LastName,
> @.Author1Organization,
> @.Author1City,
> @.Author1State,
> @.Author1Country,
> @.Author1Email,
> @.Author1Presents,
> @.StudentType,
> @.Judge,
> @.RegID
> )
> set @.AbstractID = @.@.IDENTITY
>
> if(@.@.error <> 0)
> return 1
> return(0)
> GO
>
> Before anyone asks, the underlying table does have a RegID field, and it i
s
> indeed of datatype INT.
> So what am I missing?|||You've misspecified the parameter names. In the procedure, you've indicated
names like @.RegID, @.AbstractTitle, etc.; however, in your ASP code when you
are creating and appendending to your parameters collections, you are
spcifying names like "RegID", "AbstractTitle," etc. These are mismatched;
so, ODBC has to convert to using positional parameters instead of named
parameters.
When you added the RegID to the collection, you did not add it in the same
order as the parameters are listed in the T-SQL stored procedure. This
causes SQL Server to attempt to store the passed "Author1Presents"
VARCHAR(3) value into the @.RegID INT value, which is incompatible.
Also, you are using ASP and ADO. Why are you using ODBC instead of OLEDB
directly? There are several drivers available, check that you are calling
the correct provider: SQLOLEDB or MSDASQL? SQLOLEDB would be the better
choice. If it is because you are using a DSN type configuration
persistance, consider switching to UDL, universal data links, instead, and
use the SQLOLEDB driver directly. Why have the extra API layer to slow you
down?
Sincerely,
Anthony Thomas
"Norrick" <Norrick@.discussions.microsoft.com> wrote in message
news:C9A88262-AF9A-4270-A21A-C26427EB3529@.microsoft.com...
So I have this asp 3.0 page. The page is taking form-submitted data and
saving it to a SQL Server 2000 database via stored procedure.
Just one problem - every time I execute the code, I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting
data type
varchar to int.
/erf2005/forums/abstractcontactinfo1.asp, line 36
Now, this error is a new thing. This page worked fine until I added the
RegID parameter just a little while ago. Somehow that new parameter is the
cause of the error, but after 19 hours on the job, I'll be damned if I can
figure out why. I need fresher eyes. I also need to stop working so damned
much, but that is an issue for another day.
Below is the code form the asp page. Note that the 'line 36' referred to in
the error message is the oCmd.Execute line:
dim oCmd, oCmd4, oCmd2, oCmd5, oCmd6, oCmd7, oCmd8, oCmd9, oCmd10, oCmd11,
oCmd12, oCmd13, oCmd14
dim vRegID
vRegID = Request.Form("RegID")
vRegID = CInt(vRegID)
Set oCmd = GetStoredProcedure(getConnection(),"sp_addAbstract")
oCmd.Parameters.append oCmd.CreateParameter("RegID", adInteger,
adParamInput,4,vRegID)
oCmd.Parameters.append oCmd.CreateParameter("AbstractTitle", adVarChar,
adParamInput,160,(Request.Form("AbstractTitle")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractText", adLongVarChar,
adParamInput,32000,(Request.Form("AbstractText")))
oCmd.Parameters.append oCmd.CreateParameter("WebAddress", adVarChar,
adParamInput,255,(Request.Form("WebAddress")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractType", adVarChar,
adParamInput,15,(Request.Form("AbstractType")))
oCmd.Parameters.append oCmd.CreateParameter("PresentChoice", adVarChar,
adParamInput,15,(Request.Form("PresentChoice")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice1", adVarChar,
adParamInput,7,(Request.Form("SessionChoice1")))
oCmd.Parameters.append oCmd.CreateParameter("SessionChoice2", adVarChar,
adParamInput,7,(Request.Form("SessionChoice2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword1", adVarChar,
adParamInput,50,(Request.Form("Keyword1")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword2", adVarChar,
adParamInput,50,(Request.Form("Keyword2")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword3", adVarChar,
adParamInput,50,(Request.Form("Keyword3")))
oCmd.Parameters.append oCmd.CreateParameter("Keyword4", adVarChar,
adParamInput,50,(Request.Form("Keyword4")))
oCmd.Parameters.append oCmd.CreateParameter("StudentType", adVarChar,
adParamInput,15,(Request.Form("StudentType")))
oCmd.Parameters.append oCmd.CreateParameter("Judge", adVarChar,
adParamInput,3,(Request.Form("Judge")))
oCmd.Parameters.append oCmd.CreateParameter("Author1FirstInitial",
adVarChar, adParamInput,1,(Request.Form("Author1FirstInitial")))
oCmd.Parameters.append oCmd.CreateParameter("Author1MI", adVarChar,
adParamInput,1,(Request.Form("Author1MI")))
oCmd.Parameters.append oCmd.CreateParameter("Author1LastName", adVarChar,
adParamInput,50,(Request.Form("Author1LastName")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Organization",
adVarChar, adParamInput,100,(Request.Form("Author1Organization")))
oCmd.Parameters.append oCmd.CreateParameter("Author1City", adVarChar,
adParamInput,50,(Request.Form("Author1City")))
oCmd.Parameters.append oCmd.CreateParameter("Author1State", adVarChar,
adParamInput,25,(Request.Form("Author1State")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Country", adVarChar,
adParamInput,35,(Request.Form("Author1Country")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Email", adVarChar,
adParamInput,75,(Request.Form("Author1Email")))
oCmd.Parameters.append oCmd.CreateParameter("Author1Presents", adVarChar,
adParamInput,3,(Request.Form("Author1Presents")))
oCmd.Parameters.append oCmd.CreateParameter("AbstractID", adInteger,
adParamOutput,4)
oCmd.execute()
And here's the stored procedure:
CREATE Procedure sp_addAbstract
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
@.AbstractTitle VARCHAR(255) = NULL,
@.AbstractText TEXT,
@.WebAddress VARCHAR(255) = NULL,
@.AbstractType VARCHAR(15) = NULL,
@.PresentChoice VARCHAR(15) = NULL,
@.SessionChoice1 VARCHAR(7) = NULL,
@.SessionChoice2 VARCHAR(7) = NULL,
@.Keyword1 VARCHAR(50) = NULL,
@.Keyword2 VARCHAR(50) = NULL,
@.Keyword3 VARCHAR(50) = NULL,
@.Keyword4 VARCHAR(50) = NULL,
@.StudentType VARCHAR(15) = NULL,
@.Judge VARCHAR(3) = NULL,
@.Author1FirstInitial VARCHAR(1) = NULL,
@.Author1MI VARCHAR(1) = NULL,
@.Author1LastName VARCHAR(50) = NULL,
@.Author1Organization VARCHAR(100) = NULL,
@.Author1City VARCHAR(50) = NULL,
@.Author1State VARCHAR(25) = NULL,
@.Author1Country VARCHAR(35) = NULL,
@.Author1Email VARCHAR(75) = NULL,
@.Author1Presents VARCHAR(3) = NULL,
@.RegID INT,
@.AbstractID int output
as
insert dbo.tblAbstractInfo
(
AbstractTitle,
AbstractText,
WebAddress,
AbstractType,
PresentChoice,
SessionChoice1,
SessionChoice2,
Keyword1,
Keyword2,
Keyword3,
Keyword4,
Author1FirstInitial,
Author1MI,
Author1LastName,
Author1Organization,
Author1City,
Author1State,
Author1Country,
Author1Email,
Author1Presents,
StudentType,
Judge,
RegID
)
values
(
@.AbstractTitle,
@.AbstractText,
@.WebAddress,
@.AbstractType,
@.PresentChoice,
@.SessionChoice1,
@.SessionChoice2,
@.Keyword1,
@.Keyword2,
@.Keyword3,
@.Keyword4,
@.Author1FirstInitial,
@.Author1MI,
@.Author1LastName,
@.Author1Organization,
@.Author1City,
@.Author1State,
@.Author1Country,
@.Author1Email,
@.Author1Presents,
@.StudentType,
@.Judge,
@.RegID
)
set @.AbstractID = @.@.IDENTITY
if(@.@.error <> 0)
return 1
return(0)
GO
Before anyone asks, the underlying table does have a RegID field, and it is
indeed of datatype INT.
So what am I missing?

Error converting data type varchar to float.

Hi,
I am receiving this error when trying to pass a value from ASP to SQL,
below is the SP and a snippet from the update code from the ASP page,
any ideas on how to rectifiy this. I have used a similar syntax in an
add SP and that works fine, the supp_rent_val2 and usr_rent_val2 are the
two values im passing in:
======SP========
CREATE PROCEDURE dbo.cnms_rentals_update
@.RENT_TYPE_SUPP FLOAT= NULL,
@.RENTAL_SUPP VARCHAR(1)=NULL,
@.RENT_TYPE VARCHAR(1)= NULL,
@.RENTAL FLOAT= NULL,
@.START_DATE DATETIME= NULL,
@.END_DATE DATETIME = NULL,
@.ROW_ID INT= NULL
AS
BEGIN
UPDATE RENTAL SET
RENT_TYPE_SUPP = convert(float,@.RENT_TYPE_SUPP),
RENTAL_SUPP = @.RENTAL_SUPP,
RENT_TYPE = @.RENT_TYPE,
RENTAL = convert(float,@.RENTAL),
START_DATE= @.START_DATE,
END_DATE = @.END_DATE
WHERE
row_id = @.ROW_ID
END
GO
==========ASP=========
szSQL="EXEC dbo.cnms_rentals_update"
if request("supp_rent_val2")<> "" then
szSQL = szSQL & ", @.RENT_TYPE_SUPP = " & request("supp_rent_val2")
end if
if request("supp_rent_per2")<> "" then
szSQL = szSQL & ", @.RENTAL_SUPP = '" & request("supp_rent_per2")& "'"
end if
if request("usr_rent_val2")<> "" then
szSQL = szSQL & ", @.RENTAL = " & request("usr_rent_val2")
end if
===================================
Thanks in advance
Peter
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
You didn't include the error. But, beyond that why are you using float?
Not that it might not be appropriate, but as an approximate data type I
would have a hard time recommending its use when it comes to monetary
transactions.
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:%23jI%23Cf6KEHA.2556@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> I am receiving this error when trying to pass a value from ASP to SQL,
> below is the SP and a snippet from the update code from the ASP page,
> any ideas on how to rectifiy this. I have used a similar syntax in an
> add SP and that works fine, the supp_rent_val2 and usr_rent_val2 are the
> two values im passing in:
>
> ======SP========
> CREATE PROCEDURE dbo.cnms_rentals_update
> @.RENT_TYPE_SUPP FLOAT= NULL,
> @.RENTAL_SUPP VARCHAR(1)=NULL,
> @.RENT_TYPE VARCHAR(1)= NULL,
> @.RENTAL FLOAT= NULL,
> @.START_DATE DATETIME= NULL,
> @.END_DATE DATETIME = NULL,
> @.ROW_ID INT= NULL
> AS
> BEGIN
>
> UPDATE RENTAL SET
> RENT_TYPE_SUPP = convert(float,@.RENT_TYPE_SUPP),
> RENTAL_SUPP = @.RENTAL_SUPP,
> RENT_TYPE = @.RENT_TYPE,
> RENTAL = convert(float,@.RENTAL),
> START_DATE= @.START_DATE,
> END_DATE = @.END_DATE
> WHERE
> row_id = @.ROW_ID
> END
> GO
> ==========ASP=========
> szSQL="EXEC dbo.cnms_rentals_update"
> if request("supp_rent_val2")<> "" then
> szSQL = szSQL & ", @.RENT_TYPE_SUPP = " & request("supp_rent_val2")
> end if
> if request("supp_rent_per2")<> "" then
> szSQL = szSQL & ", @.RENTAL_SUPP = '" & request("supp_rent_per2")& "'"
> end if
> if request("usr_rent_val2")<> "" then
> szSQL = szSQL & ", @.RENTAL = " & request("usr_rent_val2")
> end if
> ===================================
>
> Thanks in advance
> Peter
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Error converting data type varchar to float.

Hi,
I am receiving this error when trying to pass a value from ASP to SQL,
below is the SP and a snippet from the update code from the ASP page,
any ideas on how to rectifiy this. I have used a similar syntax in an
add SP and that works fine, the supp_rent_val2 and usr_rent_val2 are the
two values im passing in:
======SP========
CREATE PROCEDURE dbo.cnms_rentals_update
@.RENT_TYPE_SUPP FLOAT= NULL,
@.RENTAL_SUPP VARCHAR(1)=NULL,
@.RENT_TYPE VARCHAR(1)= NULL,
@.RENTAL FLOAT= NULL,
@.START_DATE DATETIME= NULL,
@.END_DATE DATETIME = NULL,
@.ROW_ID INT= NULL
AS
BEGIN
UPDATE RENTAL SET
RENT_TYPE_SUPP = convert(float,@.RENT_TYPE_SUPP),
RENTAL_SUPP = @.RENTAL_SUPP,
RENT_TYPE = @.RENT_TYPE,
RENTAL = convert(float,@.RENTAL),
START_DATE= @.START_DATE,
END_DATE = @.END_DATE
WHERE
row_id = @.ROW_ID
END
GO
==========ASP=========
szSQL="EXEC dbo.cnms_rentals_update"
if request("supp_rent_val2")<> "" then
szSQL = szSQL & ", @.RENT_TYPE_SUPP = " & request("supp_rent_val2")
end if
if request("supp_rent_per2")<> "" then
szSQL = szSQL & ", @.RENTAL_SUPP = '" & request("supp_rent_per2")& "'"
end if
if request("usr_rent_val2")<> "" then
szSQL = szSQL & ", @.RENTAL = " & request("usr_rent_val2")
end if
===================================
Thanks in advance
Peter
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!You didn't include the error. But, beyond that why are you using float?
Not that it might not be appropriate, but as an approximate data type I
would have a hard time recommending its use when it comes to monetary
transactions.
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:%23jI%23Cf6KEHA.2556@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> I am receiving this error when trying to pass a value from ASP to SQL,
> below is the SP and a snippet from the update code from the ASP page,
> any ideas on how to rectifiy this. I have used a similar syntax in an
> add SP and that works fine, the supp_rent_val2 and usr_rent_val2 are the
> two values im passing in:
>
> ======SP========
> CREATE PROCEDURE dbo.cnms_rentals_update
> @.RENT_TYPE_SUPP FLOAT= NULL,
> @.RENTAL_SUPP VARCHAR(1)=NULL,
> @.RENT_TYPE VARCHAR(1)= NULL,
> @.RENTAL FLOAT= NULL,
> @.START_DATE DATETIME= NULL,
> @.END_DATE DATETIME = NULL,
> @.ROW_ID INT= NULL
> AS
> BEGIN
>
> UPDATE RENTAL SET
> RENT_TYPE_SUPP = convert(float,@.RENT_TYPE_SUPP),
> RENTAL_SUPP = @.RENTAL_SUPP,
> RENT_TYPE = @.RENT_TYPE,
> RENTAL = convert(float,@.RENTAL),
> START_DATE= @.START_DATE,
> END_DATE = @.END_DATE
> WHERE
> row_id = @.ROW_ID
> END
> GO
> ==========ASP=========
> szSQL="EXEC dbo.cnms_rentals_update"
> if request("supp_rent_val2")<> "" then
> szSQL = szSQL & ", @.RENT_TYPE_SUPP = " & request("supp_rent_val2")
> end if
> if request("supp_rent_per2")<> "" then
> szSQL = szSQL & ", @.RENTAL_SUPP = '" & request("supp_rent_per2")& "'"
> end if
> if request("usr_rent_val2")<> "" then
> szSQL = szSQL & ", @.RENTAL = " & request("usr_rent_val2")
> end if
> ===================================
>
> Thanks in advance
> Peter
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Error converting data type varchar to float.

Hi,
I am receiving this error when trying to pass a value from ASP to SQL,
below is the SP and a snippet from the update code from the ASP page,
any ideas on how to rectifiy this. I have used a similar syntax in an
add SP and that works fine, the supp_rent_val2 and usr_rent_val2 are the
two values im passing in:
======SP======== CREATE PROCEDURE dbo.cnms_rentals_update
@.RENT_TYPE_SUPP FLOAT= NULL,
@.RENTAL_SUPP VARCHAR(1)=NULL,
@.RENT_TYPE VARCHAR(1)= NULL,
@.RENTAL FLOAT= NULL,
@.START_DATE DATETIME= NULL,
@.END_DATE DATETIME = NULL,
@.ROW_ID INT= NULL
AS
BEGIN
UPDATE RENTAL SET
RENT_TYPE_SUPP = convert(float,@.RENT_TYPE_SUPP),
RENTAL_SUPP = @.RENTAL_SUPP,
RENT_TYPE = @.RENT_TYPE,
RENTAL = convert(float,@.RENTAL),
START_DATE= @.START_DATE,
END_DATE = @.END_DATE
WHERE
row_id = @.ROW_ID
END
GO
==========ASP========= szSQL="EXEC dbo.cnms_rentals_update"
if request("supp_rent_val2")<> "" then
szSQL = szSQL & ", @.RENT_TYPE_SUPP = " & request("supp_rent_val2")
end if
if request("supp_rent_per2")<> "" then
szSQL = szSQL & ", @.RENTAL_SUPP = '" & request("supp_rent_per2")& "'"
end if
if request("usr_rent_val2")<> "" then
szSQL = szSQL & ", @.RENTAL = " & request("usr_rent_val2")
end if
===================================
Thanks in advance
Peter
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You didn't include the error. But, beyond that why are you using float?
Not that it might not be appropriate, but as an approximate data type I
would have a hard time recommending its use when it comes to monetary
transactions.
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:%23jI%23Cf6KEHA.2556@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> I am receiving this error when trying to pass a value from ASP to SQL,
> below is the SP and a snippet from the update code from the ASP page,
> any ideas on how to rectifiy this. I have used a similar syntax in an
> add SP and that works fine, the supp_rent_val2 and usr_rent_val2 are the
> two values im passing in:
>
> ======SP========> CREATE PROCEDURE dbo.cnms_rentals_update
> @.RENT_TYPE_SUPP FLOAT= NULL,
> @.RENTAL_SUPP VARCHAR(1)=NULL,
> @.RENT_TYPE VARCHAR(1)= NULL,
> @.RENTAL FLOAT= NULL,
> @.START_DATE DATETIME= NULL,
> @.END_DATE DATETIME = NULL,
> @.ROW_ID INT= NULL
> AS
> BEGIN
>
> UPDATE RENTAL SET
> RENT_TYPE_SUPP = convert(float,@.RENT_TYPE_SUPP),
> RENTAL_SUPP = @.RENTAL_SUPP,
> RENT_TYPE = @.RENT_TYPE,
> RENTAL = convert(float,@.RENTAL),
> START_DATE= @.START_DATE,
> END_DATE = @.END_DATE
> WHERE
> row_id = @.ROW_ID
> END
> GO
> ==========ASP=========> szSQL="EXEC dbo.cnms_rentals_update"
> if request("supp_rent_val2")<> "" then
> szSQL = szSQL & ", @.RENT_TYPE_SUPP = " & request("supp_rent_val2")
> end if
> if request("supp_rent_per2")<> "" then
> szSQL = szSQL & ", @.RENTAL_SUPP = '" & request("supp_rent_per2")& "'"
> end if
> if request("usr_rent_val2")<> "" then
> szSQL = szSQL & ", @.RENTAL = " & request("usr_rent_val2")
> end if
> ===================================>
> Thanks in advance
> Peter
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Error connecting to database?

Hi Again,
I have MSDE on ASP1.1 on a PC and have a logon page that uses a stored procedure to check my auth_user table, if logon and password exist then it returns (1) else returns (0).
I used objparams.return to pick up the returned value and either server.transfer to opening page or label text message 'login not valid'. This is insde a try and the catch is another label, 'cannot connect to database'.
Running on my local PC all is fine, transferred SP and table to hosting server, along with web.comfig set to the host servername, uid, password and database. On the host server all errors messages are returned. I have created a connection in my VS7 database manager to the remote host and the same happens after amending my connection string to run on the remote server.
It clearly is the connection string, I am using the <appsettings> add Key to connect as I thought it would mean no change of coding in my pages when moving from Dev PC to Server. I have checked the hosting server setup and used the server name associated with the database (mssql3.securedc.com) , checked uid, password and database settings. Is there something I have missed like permissions, specific security settings on the host server (I have to go through a third party as the hosting is done through a friend of my client)??
Many thanks
Earle
EJW--
Regarding this...

EJW wrote:

...It clearly is the connection string...

...you might want to checkhttp://www.ConnectionStrings.com to make sure you are using the right format.
Furthermore, usually in shared hosting environments, one cannot use "Integrated Security"; so, make sure about that.
Can you post the connection string here?
|||The string is
<add key="strConn" value="server=mssql3.securedc.com;uid=fleetwo_admin;password=XXXXXXXX;database=fleetwo_erfp" />
Should I use uid=sa ? I don't have a password for it.
I am unsure if the problem is in the connection or the deployment. I can find very litttle information on deployment, but basically I have put all my aspx file into my root, the dll files into bin. Should there be files elsewhere (client_ dir?).
I am building the project using my web.config file as that has paths for my PC, and this is different to the web.config file used on the remote server. Can this be an issue?
As a general question, not to you personally:- I made an assumption, probably wrong, that the web.config file was outside the build so that you could deploy on different servers. The .NET product is really useful, but I am finding getting really basic information hard to find, any tutorials on deployment around?
Many thanks
Earle

Error Code 207

I am getting the 207 error code and can't figure out the problem. I have a coldfusion page that is calling a stored procedure. The page allows users to sort the displayed columns and it is passing the column numbers into my stored procedure. I am using a case statement to decide which sort to use.

The error message is: Invalid column name 'SCHOOL'.
I've tried adding single and double quotes here and it worked before I added the case statment when I just type 'order by school'.

Below is some of my code:

SELECT P.NAME,
P.ASORG,
P.MAILSTOP,
P.OFCPHONE,
R.EMPLID,
SCHOOL = S.DESCR,
R.DEGREE_LEVEL,
R.DISCIPLINE,
TEAM = T.TEAM_NAME,
NAT_EVENT = NE.EVENT_NAME,
R.SCHOOL_CODE,
R.TEAM_CODE,
R.EVENT_CODE,
FROM HR_EXTRAS..RECRUITERS R,
HRWH..PS_SCHOOL_TBL S,
HRWH..PERSON P,
HR_Extras..UP_TEAMS T,
HR_Extras..UP_NATIONAL_EVENTS NE,
WHERE R.SCHOOL_CODE = S.SCHOOL_CODE
AND R.EMPLID = P.SNL_ID
AND T.TEAM_CODE = R.TEAM_CODE
AND NE.EVENT_CODE = R.EVENT_CODE
ORDER BY CASE WHEN @.SORT = 1 THEN P.NAME
WHEN @.SORT = 2 THEN P.ASORG
WHEN @.SORT = 3 THEN SCHOOL
WHEN @.SORT = 4 THEN T.TEAM_NAME
WHEN @.SORT = 5 THEN NE.EVENT_NAME
END

Can anyone offer suggestions?WHEN @.SORT = 3 THEN S.DESCR-PatP|||WHEN @.SORT = 3 THEN S.DESCR

That doesn't work because the SQL statement is actually part of a union query. The previous statements pull the exact same data but have

SCHOOL = ' NM-Not Assigned',

and

SCHOOL = ' CA-Not Assigned',

Sorry I didn't include that previously. The two values above are not stored in the PS_SCHOOL_TBL, thus the explicit call.|||deleted comment