I have a procedure in SQL Server 2

[ChrisQuick]

I have a procedure in SQL Server 2000 written to support on Online Complaint Tracking program.

The procedure runs OK in SQL Query Analyzer, but when used with ADO in ASP, produces the error:

[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.
-2147217833

I don't have any binary data in the referenced tables, and I don't believe any of the values being passed from the form exceed the column lengths so, I am kind of stumped here..

Any ideas or suggestions??

Here is the ASP code:

<%
Option Explicit
Response.Buffer = True
Response.Expires = 0
Response.ExpiresAbsolute = Now() - 1
Response.CacheControl = &quot;no-cache&quot;
' For documentation on the include files, see each individual file.
%>
<!-- #include virtual=&quot;common/writepretty.inc&quot;-->
<!-- #include virtual=&quot;common/adovbs.inc&quot;-->
<%

Dim strLastName
Dim strFirstName
Dim strHouseNo
Dim strStreetName
Dim strAptNo
Dim strAreaCode
Dim strPhonePrefix
Dim strPhoneSuffix
Dim strPhoneExt
Dim strEmailAccount
Dim strEmailDomainName
Dim selDomain
Dim strCheckAdd
Dim strCompHouseNo
Dim strCompStreetName
Dim strCompAptNo
Dim selCompType
Dim strDesc

Dim objADOConn, strConnectionString, objADORecs, objField
Dim booError
booError = &quot;FALSE&quot;

' READ THE INFORMATION PASSED FROM THE FORM
'*************************************************
strLastName = UCase(Request.Form(&quot;txtLastName&quot)
strFirstName = UCase(Request.Form(&quot;txtFirstName&quot)
strHouseNo = UCase(Request.Form(&quot;txtHouseNo&quot)
strStreetName = UCase(Request.Form(&quot;txtStreetName&quot)
strAptNo = UCase(Request.Form(&quot;txtAptNo&quot)
strAreaCode = UCase(Request.Form(&quot;txtAreaCode&quot)
strPhonePrefix = UCase(Request.Form(&quot;txtPhonePrefix&quot)
strPhoneSuffix = UCase(Request.Form(&quot;txtPhoneSuffix&quot)
strPhoneExt = UCase(Request.Form(&quot;txtPhoneExt&quot)
strEmailAccount = UCase(Request.Form(&quot;txtEmailAccount&quot)
strEmailDomainName = UCase(Request.Form(&quot;txtEmailDomainName&quot)
selDomain = UCase(Request.Form(&quot;selDomain&quot)
strCompHouseNo = UCase(Request.Form(&quot;txtCompHouseNo&quot)
strCompStreetName = UCase(Request.Form(&quot;txtCompStreetName&quot)
strCompAptNo = UCase(Request.Form(&quot;txtCompAptNo&quot)
selCompType = UCase(Request.Form(&quot;selCompType&quot)
strDesc = UCase(Replace(Request.Form(&quot;txtDesc&quot, vbCrLf, &quot;
&quot)

' CHECK THE INFORMATION PASSED FROM THE FORM
'*************************************************
If strLastName & &quot; &quot; = &quot; &quot; Then
WritePretty &quot;We need your last name.
Please hit the back button on your browser and try again.&quot;
Response.End
End If

If strFirstName & &quot; &quot; = &quot; &quot; Then
WritePretty &quot;We need your first name.
Please hit the back button on your browser and try again.&quot;
Response.End
End If

If strHouseNo & &quot; &quot; = &quot; &quot; Then
WritePretty &quot;We need your house number.
Please hit the back button on your browser and try again.&quot;
Response.End
End If

If strStreetName & &quot; &quot; = &quot; &quot; Then
WritePretty &quot;We need your street name.
Please hit the back button on your browser and try again.&quot;
Response.End
End If


If strAreaCode & &quot; &quot; = &quot; &quot; Then
WritePretty &quot;We need your area code.
Please hit the back button on your browser and try again.&quot;
Response.End
End If

If strPhonePrefix & &quot; &quot; = &quot; &quot; Then
WritePretty &quot;We need your phone number.
Please hit the back button on your browser and try again.&quot;
Response.End
End If

If strPhoneSuffix & &quot; &quot; = &quot; &quot; Then
WritePretty &quot;We need your phone number.
Please hit the back button on your browser and try again.&quot;
Response.End
End If

If strEmailAccount & &quot; &quot; = &quot; &quot; Then
WritePretty &quot;We need your email address.
Please hit the back button on your browser and try again.&quot;
Response.End
End If

If strEmailDomainName & &quot; &quot; = &quot; &quot; Then
WritePretty &quot;We need your email address.
Please hit the back button on your browser and try again.&quot;
Response.End
End If

If selDomain & &quot; &quot; = &quot; &quot; Then
WritePretty &quot;We need your email address.
Please hit the back button on your browser and try again.&quot;
Response.End
End If


If selCompType & &quot; &quot; = &quot; &quot; Then
WritePretty &quot;We need to know what type of complaint this is.
Please hit the back button on your browser and try again.&quot;
Response.End
End If

If strDesc & &quot; &quot; = &quot; &quot; Then
WritePretty &quot;We need a description of the problem.
Please hit the back button on your browser and try again.&quot;
Response.End
End If

strConnectionString = &quot;PROVIDER=MSDASQL; DRIVER={SQL Server}; SERVER=myserver; DATABASE=Complaint; UID=theUser;PWD=thePass;&quot;
Set objADOConn = Server.CreateObject(&quot;adodb.Connection&quot
Set objADORecs = Server.CreateObject(&quot;ADODB.Recordset&quot
objADOConn.Open strConnectionString
On Error Resume Next
objADOConn.AddNewComplaint strLastName, strFirstName, strHouseNo, strStreetName, strAptNo, strAreaCode, strPhonePrefix, strPhoneSuffix, strPhoneExt, strEmailAccount, strEmailDomainName, selDomain, strCompHouseNo, strCompStreetName, strCompAptNo, selCompType, strDesc, objADORecs

If Err.Number <> 0 Then
booError = &quot;True&quot;
End If

If booError = &quot;True&quot; Then
WritePretty &quot;There has been an error:

&quot; & Err.Description & &quot;
&quot; & Err.Number
Response.End
Else
For Each objField in objADORecs.Fields
Response.Write objField.Name & &quot;
&quot;
Next

Do While Not objADORecs.EOF
For Each objField in objADORecs.Fields
Response.Write objADORecs.Value & &quot;
&quot;
Next

'Advance the recordset
objADORecs.MoveNext
Loop
End If



This is the Stored Procedure:

CREATE PROCEDURE dbo.AddNewComplaint(

/* Contact Info */
@strLastName [varchar](50),
@strFirstName [varchar](50),
@intHouseNo [int],
@strStreetName [varchar](50),
@strAptNo [varchar](10) = null,
@intAreaCode [varchar](3),
@intPhonePrefix [varchar](3),
@intPhoneSuffix [varchar](4),
@intPhoneExt [varchar](4) = null,
@strEmailAccount [varchar](20),
@strEmailDomainName [varchar](20),
@strEmailDomainType [varchar](3),

/* Complaint Location Info */
@strCompHouseNo [varchar](6) = null,
@strCompStreetName[varchar](86) = null,
@strCompAptNo [varchar](6) = null,

/* Complaint Info */
@intCompType [int],
@strDesc [varchar](500),

/* Calculated variables */
@myDate [datetime] = null,
@mydow [varchar](10) = null,
@deadline [datetime] = null,
@ContactID [int] = null,
@ComplaintAssigned_Id [int] = null,
@ComplaintStatusType [INT] = null,
@ComplaintId [INT] = null,
@ComplaintConcatAddress [varchar](100) = null

)

AS

IF NOT EXISTS(SELECT 'True' FROM [Complaint].[dbo].[Contacts] WHERE [ContactEmailAddressName]= @strEmailAccount AND [ContactEmailAddressServer] = @strEmailDomainName)
BEGIN
--This means the record isn't in there already, let's go ahead and add it

INSERT INTO [Complaint].[dbo].[Contacts] (
[ContactFirstName],
[ContactlastName],
[ContactAddressHouseNum],
[ContactAddressRoadName],
[ContactAddressAptNumber],
[ContactAreaCode],
[ContactPhonePrefix],
[ContactPhoneNumber],
[ContactPhoneExt],
[ContactEmailAddressName],
[ContactEmailAddressServer],
[ContactEmailAddressDomain]
)

VALUES (
@strFirstName,
@strLastName,
@intHouseNo,
@strStreetName,
@strAptNo,
@intAreaCode,
@intPhonePrefix,
@intPhoneSuffix,
@intPhoneExt,
@strEmailAccount,
@strEmailDomainName,
@strEmailDomainType
)

END

SELECT @ContactID = (select ContactContact_Id from [Complaint].[dbo].[Contacts] WHERE [ContactEmailAddressName]= @strEmailAccount AND [ContactEmailAddressServer] = @strEmailDomainName)


SELECT @myDate = getdate()
SELECT @mydow = DATENAME(dw,@myDate)
SELECT @deadline = (
SELECT DATEADD(DAY,(SELECT
Case @mydow
WHEN 'Sunday' THEN 3
WHEN 'Monday' THEN 3
WHEN 'Tuesday' THEN 3
WHEN 'Wednesday' THEN 5
WHEN 'Thursday' THEN 5
WHEN 'Friday' THEN 5
WHEN 'Saturday' THEN 5
End
),@myDate))


IF EXISTS(SELECT 'True' FROM [Complaint].[dbo].[ComplaintTypes] WHERE ComplaintType_Id = @intCompType)
BEGIN
-- we know what type of complaint it is, so we know who to give it to. set the staus = autoassigned.
SELECT @ComplaintAssigned_Id = (SELECT ComplaintAssigned_Id FROM ComplaintTypes WHERE ComplaintType_Id = @intCompType)
SELECT @ComplaintStatusType = '2'
END
ELSE
BEGIN
-- we do not know who to give it to, so give it the admin user and set status = new
SELECT @ComplaintAssigned_Id = '1'
SELECT @ComplaintStatusType = '1'
END
INSERT INTO [Complaint].[dbo].[Complaints] (
[Contact_Id],
[ComplaintSubmittialMethod_Id],
[ComplaintType_Id],
[ComplaintStatusType_Id],
[ComplaintAssignedCityEmployee_Id],
[ComplaintDesc],
[ComplaintActionDueDate]
)
VALUES (
@ContactID,
'2',
@intCompType,
@ComplaintStatusType,
@ComplaintAssigned_Id ,
@strDesc,
@deadline
)
SELECT @ComplaintId = (SELECT SCOPE_IDENTITY())


SELECT @ComplaintConcatAddress = (SELECT cast(@strCompHouseNo AS varchar(5)) + ' ' + @strCompStreetName + ' ' + @strCompAptNo)
IF (LEN(@ComplaintConcatAddress) > 3)
BEGIN
INSERT INTO [Complaint].[dbo].[ComplaintLocations] (
[ComplaintLocationComplaint_Id],
[ComplaintLocationAddress]
)

VALUES (
@ComplaintId,
@ComplaintConcatAddress
)
END
ELSE
BEGIN
INSERT INTO [Complaint].[dbo].[ComplaintLocations] (
[ComplaintLocationComplaint_Id]
)

VALUES (
@ComplaintId
)
END

SELECT dbo.Complaints.Complaint_Id, dbo.CityEmployees.CityEmployeeEmailAdd
FROM dbo.Complaints INNER JOIN
dbo.CityEmployees ON dbo.Complaints.ComplaintAssignedCityEmployee_Id = dbo.CityEmployees.CityEmployee_Id
WHERE dbo.Complaints.Complaint_Id = @ComplaintId
GO
cquick@geotg.com
Geographic Information System (GIS), ASP, some Oracle

 

[link9]

Ok, well if SQL Server is returning the truncate error, then that is what's happening -- some string value is getting chopped off according to what you have passed it, and what you have declared -- so bump up your data lengths first off.

Then, try this on for using SPROCS in an ASP page... the ADO Command object:

Simplified, we'll say I have a sproc in my database called spDoSomething -- and that sproc has two input parameters, @val1, and @val2

It also has one OUTPUT parameter, @output, which I want to display the value for on the calling page.

Here's how I would use it:

<%
dim con, comObj
set con = server.createObject(&quot;ADODB.Connection&quot
set comObj = server.createObject(&quot;ADODB.Command&quot

con.open (&quot;DSN=myDSN;UID=uid;PWD=pwd&quot

comObj.commandText = &quot;spDoSomething&quot; 'tell it what sproc
comObj.commandType = 4 'adCmdStoredProcedure
comObj.activeConnection = con 'tell it what connection

dim val1, val2, output 'declare values to use

'get values
val1 = request.form(&quot;val1&quot
val2 = request.form(&quot;val2&quot

'assign the values to the sproc
comObj.parameters(&quot;@val1&quot.value = val1
comObj.parameters(&quot;@val2&quot.value = val2

'execute the sproc
comObj.execute

'get my output paramter
output = comObj.parameters(&quot;@output&quot.value

'clean up
set comObj = nothing
set con = nothing
%>

To be honest, I've never seen anyone use a sproc like you demonstrated above, but this is the way that I have read in books -- does that mean it's the only way? Never. But it does look to me to be alot cleaner.

Like I said, if the SQL Server is returning you a truncation error, then take it's word for it and start debugging on len(string) to figure out which one in particular is getting chopped -- hopefully that will solve your problem. But I would also check out:

http://www.devguru.com/Technologies/ado/quickref/command.html

for more information on the built in command object that is provided for us to use a sproc.

good luck!
Paul Prewett