Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SP  ISSUE

Posted on 2006-04-17
4
Medium Priority
?
254 Views
Last Modified: 2012-06-21
I AM GETTIN THIS ERROR  Microsoft OLE DB Provider for SQL Server error '80040e07'

Invalid character value for cast specification.

/2004fallevents/updateschedule.asp, line 56


WHICH IN LINE 56 I HAVE   "call dbo.dboschedule(" + Replace(Recordset1__userid, "'", "''") + ",'" + Replace(Recordset1__vardatesss, "'", "''") + "'," + Replace(Recordset1__varevent, "'", "''") + "," + Replace(Recordset1__varsite, "'", "''") + "," + Replace(Recordset1__varfield, "'", "''") + "," + Replace(Recordset1__varteams, "'", "''") + ")}"


CREATE PROCEDURE dboschedule
@userid int,
@vardatesss  datetime,
@varevent int,
@varsite int,
@varfield int,
@varteams int
AS
SELECT *,newschedule.userid AS bestiduser,  newschedule.id AS bestid, carmelr.id AS Expr1, carmelr.refname AS Expr2, carmelr_1.id AS Expr3, carmelr_2.id AS Expr4
FROM newschedule LEFT OUTER JOIN                        events ON newschedule.EventType = events.newid LEFT OUTER JOIN                        leagues ON newschedule.League = leagues.leagueid LEFT OUTER JOIN                        eventsection ON newschedule.EevntSection = eventsection.sectionid LEFT OUTER JOIN                        sitename ON newschedule.Site = sitename.SiteID LEFT OUTER JOIN                        teams ON newschedule.CUSCTeam = teams.teamid LEFT OUTER JOIN                        carmelr ON newschedule.RefCenter = carmelr.id LEFT OUTER JOIN                        carmelr carmelr_1 ON newschedule.RefAR1 = carmelr_1.id LEFT OUTER JOIN                        carmelr carmelr_2 ON newschedule.RefAR2 = carmelr_2.id LEFT OUTER JOIN                        fielsd ON newschedule.Field = fielsd.idfields
WHERE (newschedule.EventDate = @vardatesss) AND (newschedule.EventType LIKE @varevent) AND                         (newschedule.Site LIKE @varsite) AND (newschedule.Field LIKE @varfield) AND (newschedule.CUSCTeam LIKE @varteams) and(newschedule.userid=@userid)
ORDER BY newschedule.EventDate, fielsd.FieldName
GO
0
Comment
Question by:gianitoo
  • 2
4 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 16469820
did you run it from the query analyzer? and get any errors.

also try this
"call dbo.dboschedule('" + Replace(Recordset1__userid, "'", "''") + "','" + Replace(Recordset1__vardatesss, "'", "''") + "','" + Replace(Recordset1__varevent, "'", "''") + "','" + Replace(Recordset1__varsite, "'", "''") + "','" + Replace(Recordset1__varfield, "'", "''") + "','" + Replace(Recordset1__varteams, "'", "''") + "')"
0
 
LVL 1

Author Comment

by:gianitoo
ID: 16469889
THIS QUERY  WORKS FINE FROM ENTERPRISE MANAGER

SELECT     *, newschedule.userid AS bestiduser, newschedule.id AS bestid, carmelr.id AS Expr1, carmelr.refname AS Expr2, carmelr_1.id AS Expr3,
                      carmelr_2.id AS Expr4
FROM         newschedule LEFT OUTER JOIN
                      events ON newschedule.EventType = events.newid LEFT OUTER JOIN
                      leagues ON newschedule.League = leagues.leagueid LEFT OUTER JOIN
                      eventsection ON newschedule.EevntSection = eventsection.sectionid LEFT OUTER JOIN
                      sitename ON newschedule.Site = sitename.SiteID LEFT OUTER JOIN
                      teams ON newschedule.CUSCTeam = teams.teamid LEFT OUTER JOIN
                      carmelr ON newschedule.RefCenter = carmelr.id LEFT OUTER JOIN
                      carmelr carmelr_1 ON newschedule.RefAR1 = carmelr_1.id LEFT OUTER JOIN
                      carmelr carmelr_2 ON newschedule.RefAR2 = carmelr_2.id LEFT OUTER JOIN
                      fielsd ON newschedule.Field = fielsd.idfields
WHERE     (newschedule.EventDate = CONVERT(DATETIME, '2006-03-08 00:00:00', 102)) AND (newschedule.EventType LIKE 1) AND (newschedule.Site LIKE 1)
                      AND (newschedule.Field LIKE 1) AND (newschedule.CUSCTeam LIKE 1) AND (newschedule.userid = 1)
ORDER BY newschedule.EventDate, fielsd.FieldName
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 2000 total points
ID: 16470328
debug this from your application by putting wath

"call dbo.dboschedule('" + Replace(Recordset1__userid, "'", "''") + "','" + Replace(Recordset1__vardatesss, "'", "''") + "','" + Replace(Recordset1__varevent, "'", "''") + "','" + Replace(Recordset1__varsite, "'", "''") + "','" + Replace(Recordset1__varfield, "'", "''") + "','" + Replace(Recordset1__varteams, "'", "''") + "')"

and see wht is the string created like print it on the screen because this one is created dynamically
print the stored procedure call before executing it and post it here.
0
 
LVL 11

Expert Comment

by:deroby
ID: 16470334
Ok, bit hard to explain, but worth investigating imho

=> you're using a couple of variables in the WHERE clause of the query (eg. @vardatesss)
These might be defined as a specific datatype (eg. datetime, or int, or whatever)

Now as you run your ASP, it seems to me that you try to pass a value from a recordset as a variable for the procedure, but by converting it to a string first. I wouldn't be too surprised if this conversion bailed out on some strange values

Another think I find rather confusing is that you do a LIKE on an int ??????

=> I'm not sure what you want to achieve here but 'WHERE field LIKE 1' actaully doesn't make sense. When comparing numeric values you should restrict to <, >, <=, >=, =, <> or !=

0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question