Error exporting report

Hello

I've developed an ASP.NET WebApplication (VS.NET 2002) with some Crystal Reports in it to report the data.

This reports are automatically exported into a pdf-File (using the Report.Export function)

To give the user the ability to change the language of the WebApp, all the texts are stored in a MS SQL 2000 Database.

I set the Text-Property of the TextObject in the Report with the following code:

Dim TextObjekt As CrystalDecisions.CrystalReports.Engine.TextObject
TextObjekt = Report.ReportDefinition.ReportObjects.Item("Text14")
TextObjekt.Text = Data.Tables(0).Rows(0).Item("TransText") & ":"

I thought this works fine, till I changed the Text in the database.

Examples:

1.) Text in the database = "Mobil 1"
--> The report works fine, the PDF is successfully created and the TextObject shows "Mobil 1:"

2.) Text in the database = "Hello Echo"
--> The Report works and the PDF is created, but the TextObject shows "Hello Ec"

3.) Text in the database = "PSA"
--> Report.Export crashes with the following error:
"Error #5 - Error in file C:\DOKUME~1\STEFAN\ASPNET\LOKALE~1\Temp\temp_c67a4fca-9bd3-4aa6-935b-0ad7a389f034.rpt:
Error found in Export-DLL : \n0\nCrystalDecisions.CrystalReports.Engine"

Has anyone an idea how I can solve this absolutly strange problem?

Thanks and best regards
GulliwahnDoes that PSA have any special charaters?
Do very Database on the Report and try againsql

Error creating control

Hello everybody,
I my asp.net application, when I select a CrystalReportViewer control and put it on my form, it says "Error creating control" and hence I am unable to use it.
Any ideas ??
Thankslooks like ur .NET is corrupted. Try with another language (VB.NET). If it the same u gotta reinstall .NET.

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 SQLExpress 2005 locally

Hello,
I'm a newbie here and was wondering anyone could help me with this.
I have a simple ASP.NET 2 web application running perfectly fine with
IIS and SQLServerExpress installed locally on my WinXP as a testing
server (I created the app in Visual Studio.NET 2005).
When deploying this app on the production server (running Win2000
Server, IIS and SQLServerExpress 2005), I'm getting the following
message when calling an aspx data-bound page from a browser:
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified)
On the production server, I have the SQLServerExpress installed locally
on C: drive and the database is located on D: drive (on the same
directory as the app).
Connection string used in the webconfig file:
connectionString=" Server=SERVERNAME\SQLEXPRESS;AttachDbFil
ename=D:\Inetpub\A
pp_Data\registration. mdf;Database=registration;Trusted_Connec
tion=yes;"
providerName="System.Data.SqlClient"
Does anyone know how to solve this issue? Any help would be greatly
appreciated.
Thanks,
HarryHi
Have you checked if you are allowing remote connections on your SQL Express
installation, by default these will be switched off.
Check the Remote Connections setting the the SQL Server 2005 Surface Aread
Configuration tool.
John
"hko" wrote:

> Hello,
> I'm a newbie here and was wondering anyone could help me with this.
> I have a simple ASP.NET 2 web application running perfectly fine with
> IIS and SQLServerExpress installed locally on my WinXP as a testing
> server (I created the app in Visual Studio.NET 2005).
> When deploying this app on the production server (running Win2000
> Server, IIS and SQLServerExpress 2005), I'm getting the following
> message when calling an aspx data-bound page from a browser:
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections. (provider: SQL Network Interfaces, error: 26 - Error
> Locating Server/Instance Specified)
> On the production server, I have the SQLServerExpress installed locally
> on C: drive and the database is located on D: drive (on the same
> directory as the app).
> Connection string used in the webconfig file:
> connectionString=" Server=SERVERNAME\SQLEXPRESS;AttachDbFil
ename=D:\Inetpub
\App_Data\registration. mdf;Database=registration;Trusted_Connec
tion=yes;"
> providerName="System.Data.SqlClient"
> Does anyone know how to solve this issue? Any help would be greatly
> appreciated.
> Thanks,
> Harry|||Hi John,
I did allow remote connections eventhough my database is on the same machine
as my web app, but no luck so far.
I used windows authentication for the database, and login to the machine
using admin account.
Another thing that I noticed is that when I go to SQL Server Configuration
Manager, under SQL Server 2005 Network Configuration, it says Protocols for
MSSQLSERVER, whereas on my dev machine it says SQLEXPRESS. When upgrading
from MSDE to SQLExpress i just chose the default instance name, but somehow
it's saying MSSQLSERVER.
I checked the databaseconnection through SQL Management Studio Express and
it works fine. Only when viewing the data-bound page on a browser that I get
the error message.
Do you think I should try to uninstall SQLServer Express and reinstall?
hko
"John Bell" wrote:
> Hi
> Have you checked if you are allowing remote connections on your SQL Expres
s
> installation, by default these will be switched off.
> Check the Remote Connections setting the the SQL Server 2005 Surface Aread
> Configuration tool.
> John
> "hko" wrote:
>|||Hi
If this is the default instance then change your connection string to only
be the servername.
If you want to change the installation from being the default instance you
will need to re-install.
John
"hko" wrote:
> Hi John,
> I did allow remote connections eventhough my database is on the same machi
ne
> as my web app, but no luck so far.
> I used windows authentication for the database, and login to the machine
> using admin account.
> Another thing that I noticed is that when I go to SQL Server Configuration
> Manager, under SQL Server 2005 Network Configuration, it says Protocols fo
r
> MSSQLSERVER, whereas on my dev machine it says SQLEXPRESS. When upgrading
> from MSDE to SQLExpress i just chose the default instance name, but someho
w
> it's saying MSSQLSERVER.
> I checked the databaseconnection through SQL Management Studio Express and
> it works fine. Only when viewing the data-bound page on a browser that I g
et
> the error message.
> Do you think I should try to uninstall SQLServer Express and reinstall?
> hko
> "John Bell" wrote:
>|||I only have one instance and that is the default instance, and this is my
connection string:
connectionString=" Server=HOMER\SQLEXPRESS;AttachDbFilename
=D:\Inetpub\App_Da
ta\registration. mdf;Database=registration;Trusted_Connec
tion=yes;"
providerName="System.Data.SqlClient"
Still something isn't right. Do you think I should probably re-install and
do fresh installation of SQL Express instead of upgrading (like what I did)?
"John Bell" wrote:
> Hi
> If this is the default instance then change your connection string to only
> be the servername.
> If you want to change the installation from being the default instance you
> will need to re-install.
> John
>
> "hko" wrote:
>|||Hi
Try the connection string:
connectionString=" Server=HOMER;AttachDbFilename=D:\Inetpub
\App_Data\registra
tion. mdf;Database=registration;Trusted_Connec
tion=yes;"
providerName="System.Data.SqlClient"
If you want a names instance then you would have to re-install and say that
you want a named instance and not the default instance.
John
"hko" wrote:
> I only have one instance and that is the default instance, and this is my
> connection string:
> connectionString=" Server=HOMER\SQLEXPRESS;AttachDbFilename
=D:\Inetpub\App_
Data\registration. mdf;Database=registration;Trusted_Connec
tion=yes;"
> providerName="System.Data.SqlClient"
> Still something isn't right. Do you think I should probably re-install and
> do fresh installation of SQL Express instead of upgrading (like what I did
)?
> "John Bell" wrote:
>|||Still not working. I tried different combination of connection strings but
kept getting the same error message.
I just want to use the default instance since there's only one database on
the machine.
I suspect there's a glitch during the upgrade process which causes SQL
server to locate the default instance. I was able to connect ok using
Management Studio Express.
This is the most frustrating thing ever to deal with...
"John Bell" wrote:
> Hi
> Try the connection string:
> connectionString=" Server=HOMER;AttachDbFilename=D:\Inetpub
\App_Data\regist
ration. mdf;Database=registration;Trusted_Connec
tion=yes;"
> providerName="System.Data.SqlClient"
> If you want a names instance then you would have to re-install and say tha
t
> you want a named instance and not the default instance.
> John
> "hko" wrote:
>|||Hi
A couple of suggestions:
Try using osql to try and connect, use both the server name and the IP
address and different user combinations.
Check that there is no blocking of the ports being used by a firewall (Try
telneting to the port!)
John
"hko" wrote:
> Still not working. I tried different combination of connection strings but
> kept getting the same error message.
> I just want to use the default instance since there's only one database on
> the machine.
> I suspect there's a glitch during the upgrade process which causes SQL
> server to locate the default instance. I was able to connect ok using
> Management Studio Express.
> This is the most frustrating thing ever to deal with...
> "John Bell" wrote:
>

Error connecting to SQL Server 2005 - ... does not allow remote connections?

I have an ASP.NET 2.0 Website that in the connection string does not connect
to the local SQL Server but tries to connect to another SQL Server 2005
Server in another State. When I run the application from Visual Studio 2005
it connects just fine. When I published the website to the Live Server I
receive the below error:
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
Any ideas?
Peace in Christ
Marco Napoli
http://www.ourlovingmother.orgCheck this url for troubleshooting:
http://blogs.msdn.com/sql_protocols.../22/506607.aspx
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Marco Napoli" <marco@.avantitecnospam.com> wrote in message
news:#14p4JoaGHA.5004@.TK2MSFTNGP02.phx.gbl...
> I have an ASP.NET 2.0 Website that in the connection string does not
connect
> to the local SQL Server but tries to connect to another SQL Server 2005
> Server in another State. When I run the application from Visual Studio
2005
> it connects just fine. When I published the website to the Live Server I
> receive the below error:
> An error has occurred while establishing a connection to the server. When
> connecting to SQL Server 2005, this failure may be caused by the fact that
> under the default settings SQL Server does not allow remote connections.
> Any ideas?
> --
> Peace in Christ
> Marco Napoli
> http://www.ourlovingmother.org
>
>|||Thank you.
Marco
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:euqdnWInVNe2O8zZRVnyjg@.bt.com...
> Check this url for troubleshooting:
> http://blogs.msdn.com/sql_protocols.../22/506607.aspx
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Marco Napoli" <marco@.avantitecnospam.com> wrote in message
> news:#14p4JoaGHA.5004@.TK2MSFTNGP02.phx.gbl...
> connect
> 2005
>

Error connecting to sql server

I have a perplexing problem. I've developed an ASP application for our internal business use. I'm getting ready to launch, but every once in a while the application can't connect to the database. The error I get is:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

We (IT and I) turned off the remote connections and it still happens. I'm afraid of launching without understanding what causes this and gettign a fix. To get it working again, we restart IIS and it works fine. I used VWD 2005 Express and it's running on SQL Server 2005 Express.

Thanks for any help you may have.
Scott J.

Don't let the bit about "does not allow remote connections" send you down the wrong path - this ridiculous error message is posted regardless of the actual problem (one of Microsoft's worst error messages).

The part to concentrate on is the final part of the error message, which differs depending on the actual error - in your case error: 40 - Could not open a connection to SQL Server. Are you sure that the SQL Express instance is actually running when you get this problem (is it periodically stopping?). Check it via the services applet - restarting IIS may simply be restarting the Express instance.

error connecting to sql db

I have an asp.net project on my laptop 'supposed to be' connecting to a SQL DB on the same machine. But I keep getting this error:

System.Data.SqlClient.SqlException: Cannot open database requested in login 'ASPState'. Login fails. Login failed for user 'xyz'.

It's driving me nuts. Any help would be appreciated.

txs
sonnersYou have to go to Sql Server Manager, go to the database, click Add New User and add the user that needs permission, then give that user owner permission|||I did all this. My SQL knowledge isn't so bad. I've been using the main SQL login account as it's my local machine. This account works for other ASP sites but not for .Net ASP site.|||What does your connection string look like? You may need to add <identity impersonate="true"/> to your web.config file.

Terri

Error connecting to remote SQL 2000 Server

I'm experiencing a problem connecting to a SQL 2000 server through my ASP code. My connection string is as follows:

<addname="TheConnectionString"connectionString="driver={Sql Server};provider=MSDASQL;server=10.0.1.42;database=dbname;uid=*********;pwd=*********"providerName="System.Data.Odbc" />

The problem doesn't occur when I run my ASP code from my workstation using VS.NET's builtin webserver. It makes the connections and executes the CRUD commands successfully. However, when I publish my site to the webserver (which resides on 10.0.1.16) it fails out with the following error:

System.Data.Odbc.OdbcException: ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).

I've verified that the webserver can talk to the DB server by connecting to the remote DB server through SQL Enterprise Manager running locally on the webserver. If I try to do this with a DSN I get the same results. I get the same error from any other webserver on the internal network. The difference between my workstation and the internal network is that I'm using a VPN to connect to our internal network while the webservers are physically connected to it. Firewalling isn't the issue in this case because the webservers and DB server are on a trusted network. I've seen other ways of connecting to the DB server including using Named Pipes (which I would rather not do because I don't want to setup a named pipe on the production db server).

I'm relatively new to ASP.NET 2.0, so the above connection string is an adaptation of some old ASP code. If anybody has any suggestions on a better way to construct this connection string, please let me know. I've been racking my brains trying to get this to work outside of the devel env.

Try with this:

<add name="TheConnectionString" connectionString="Server=10.0.1.42;Database=dbname;User ID=*********;Password=*********;"/>

|||

johnladda:

System.Data.Odbc.OdbcException: ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).

First, this error is a generic one so your actual problem may range from incorrect credentials to a network problem.

Second, I'm not too sure why you've used ODBC library to connect to SQL while you've the SQL library.

johnladda:

I'm relatively new to ASP.NET 2.0, so the above connection string is an adaptation of some old ASP code. If anybody has any suggestions on a better way to construct this connection string, please let me know.

Third, you can use "Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx" to connect to SQL Server. You can referwww.connectionstrings.com . This site provides a number of connection string examples for almost every database.

Hope this will help.

|||

This site is fantastic. It's exactly what I needed. I was wondering if the "Data Source" property of the connection string works similar to the "Server" property in that you can specify the port number too? If not, how would I go about specifying the port number in the connection string. I only just recently found out that the person who implimented the database server put it on an alternate port.

|||

johnladda:

I was wondering if the "Data Source" property of the connection string works similar to the "Server" property in that you can specify the port number too? If not, how would I go about specifying the port number in the connection string. I only just recently found out that the person who implimented the database server put it on an alternate port.

No, you've mistaken the Data Source property of the connection string. It is no way similar to any server control. In fact, you can user Data Source or Server property in your connection string. Below are some of the alternatives you can use in your connection string.

Data Source = Server
Initial Catalog = Database
User Id = uId
Password = pwd

The default port is 1433 for SQL Server TCP/IP connection. You can specify any other port in your connection string as below:

"Data Source=xxx,portNumber;Initial Catalog=xxx;User ID=xxx;Password=xxx". That is, after either the IP or the name of the server put a comma and then provide the port number.

Error connecting to DB

Hi everyone,

I am learning how to use .NET and MSDE. I am working out of the book "ASP.NET Unleashed" on the Job Site (Chap 30)

I have used osql to run the .sql script, but now I am having problems connecting to the DB; I get "SQL Server does not exist or access denied"

The web.config file is:

<configuration>
<appSettings>
<add key="constring"
value="Server=localhost;UID=AspNETJobsUser;PWD=secret;Database=AspNetJobs" />
</appSettings>
<system.web>
<authentication mode="Forms" >
<forms name=".ASPNETJOBS"
loginUrl="/aspnetjobs/site/password/login.aspx" />
</authentication>
</system.web>
</configuration
My computer is running in Windows Authentication Mode.

I have tried replacing the UID and PWD to my sa account settings, but I still can not connect.
The books says I need to manually add a login that has access to the AspNETJobsDatabase, but I assume that would be the sa login or do I need to create another all together? If so, can anyone suggest an article to lead me in that direction.

Any suggestions would be helpful and greatly appreciated.

ThanksHere's an article that'll tell you how to add a login to an MSDE instance.

Regards,

Xander|||Data Source=IpAddyorServerName;Initial Catalog=DbName;User ID=UserName;Password=BlankpasswordSucks|||[SqlException: Impossibile eseguire l'accesso per l'utente 'sa'. Motivo: l'utente non è associato a una connessione SQL Server trusted.]

Why?
Bye Bye!|||I changed my code in the Web.config file to:

<configuration>
<appSettings>
<add key="constring"
value="Data Source=SHAYLA1;Initial Catalog=AspNetJobs;User ID=AspNETJobsUser;Password=secret" />
</appSettings>
<system.web>
<authentication mode="Forms" >
<forms name=".ASPNETJOBS"
loginUrl="/aspnetjobs/site/password/login.aspx" />
</authentication>
</system.web>
</configuration
and now I get the error:
Login failed for user 'AspNETJobsUser'. Reason: Not associated with a trusted SQL Server connection.

I followed the article on how to create a login, and I got the message of "user AspNETJobsUser already exists"

When I change the User to sa and password to "my password," I still get the same error

Still missing something?|||just an update for anybody out there that might be facing the same problem. I changed the setting to Mixed Mode from Windows authentication following the direction of the article at

http://support.microsoft.com/?kbid=322336#2

Once I restarted my computer, the app came up fine.

My final web.config files looks like so:

<configuration>
<appSettings>
<add key="constring"
value="Server=(local);Database=AspNetJobs;UID=AspNETJobsUser;PWD=secret" />
</appSettings>
<system.web>
<authentication mode="Forms" >
<forms name=".ASPNETJOBS"
loginUrl="/aspnetjobs/site/password/login.aspx" />
</authentication>
</system.web>
</configuration
Thank you to those who responded to my original post.