gianitoo
asked on
SP ISSUE
I AM GETTIN THIS ERROR Microsoft OLE DB Provider for SQL Server error '80040e07'
Invalid character value for cast specification.
/2004fallevents/updatesche
WHICH IN LINE 56 I HAVE "call dbo.dboschedule(" + Replace(Recordset1__userid
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=@us
ORDER BY newschedule.EventDate, fielsd.FieldName
GO
Invalid character value for cast specification.
/2004fallevents/updatesche
WHICH IN LINE 56 I HAVE "call dbo.dboschedule(" + Replace(Recordset1__userid
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=@us
ORDER BY newschedule.EventDate, fielsd.FieldName
GO
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 !=
=> 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 !=
also try this
"call dbo.dboschedule('" + Replace(Recordset1__userid