What is wrong?

[Kevin Spencer]

Take a look at your Stored Procedure. It takes a wide variety of data types.
But in your code, you use an overloaded version of the Add() method of a
SqlParameterCollection that takes 2 parameters. The second parameter of this
method is of type "Object" because you need to pass the correct data type to
the Stored Procedure in order to match the data type of the parameter in the
Stored Procedure. However, you passed all strings in. What you need to do is
cast each value to the correct data type when using this overloaded method.
Example:

cmd.Parameters.Add("@CopiesEC", CType(txtCopiesEC.Text, Int32))

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
[url]http://www.takempis.com[/url]
Complex things are made up of
lots of simple things.

"Boris Bulit" <bulitb@bellsouth.net> wrote in message
news:%23ud9ZmFXDHA.1640@TK2MSFTNGP10.phx.gbl...

> I have the following code in vb.net but it doesn´t work:
> When cmd.ExecuteNonQuery() run this error comes:
> An unhandled exeption of type 'System.Data.SqlClient.SqlExeption occurred

in

> system.data.dll.
> Aditional information: System error.
>
>
> The code:
>
> Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles btnPrint.Click
> Dim cn As New SqlConnection("integrated security=SSPI;data
> source=(local);persist security info=False;initial

catalog=Arte")SqlParaameter

> Dim cmd As New SqlCommand
> cmd.CommandText = "UpdateEditions"
> cmd.CommandType = CommandType.StoredProcedure
> cmd.Connection = cn
> cmd.Parameters.Add("@Edition", txtEdition.Text)
> cmd.Parameters.Add("@Color", cboColor.SelectedItem)
> cmd.Parameters.Add("@EDate", cboDate.Value)
> cmd.Parameters.Add("@CopiesEC", txtCopiesEC.Text)
> cmd.Parameters.Add("@CopiesTB", txtCopiesTB.Text)
> cmd.Parameters.Add("@PagesEC", txtCopiesEC.Text)
> cmd.Parameters.Add("@PagesTB", txtCopiesTB.Text)
> cn.Open()
> cmd.ExecuteNonQuery()
> cmd.Dispose()
> cn.Close()
> Me.Close()
> End Sub
>
> and in sql server 2000 the followin Stored procedure:
>
> ALTER PROCEDURE UpdateEdition
> @Edition as smallint,
> @Color as char(10),
> @EDate as datetime,
> @CopiesEC as int,
> @CopiesTB as int,
> @PagesEC as smallint,
> @PagesTB as smallint
> AS
> If Exists (Select * From Editions WHERE Edition = @Edition)
> UPDATE Editions
> SET Color=@Color, EDate=@EDate, CopiesEC=@CopiesEC, CopiesTB=@CopiesTB,
> PagesEC=@PagesEC, PagesTB=@PagesTB
> WHERE Edition = @Edition
> ELSE
> INSERT INTO Editions (Edition, Color, EDate, CopiesEC, CopiesTB, PagesEC,
> PagesTB)
> VALUES (@Edition ,@Color, @EDate, @CopiesEC, @CopiesTB, @PagesEC,

@PagesTB)

> RETURN
>
>
>

[MS News \(MS LVP\)]

In addition your code

cmd.CommandText = "UpdateEditions" << remove the 's' at the end

ALTER PROCEDURE UpdateEdition

you have UpdateEditions and not UpdateEdition

Missing the 's' or with extra 's'


"Boris Bulit" <bulitb@bellsouth.net> wrote in message
news:%23ud9ZmFXDHA.1640@TK2MSFTNGP10.phx.gbl...

> I have the following code in vb.net but it doesn´t work:
> When cmd.ExecuteNonQuery() run this error comes:
> An unhandled exeption of type 'System.Data.SqlClient.SqlExeption occurred

in

> system.data.dll.
> Aditional information: System error.
>
>
> The code:
>
> Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles btnPrint.Click
> Dim cn As New SqlConnection("integrated security=SSPI;data
> source=(local);persist security info=False;initial catalog=Arte")
> Dim cmd As New SqlCommand
> cmd.CommandText = "UpdateEditions"
> cmd.CommandType = CommandType.StoredProcedure
> cmd.Connection = cn
> cmd.Parameters.Add("@Edition", txtEdition.Text)
> cmd.Parameters.Add("@Color", cboColor.SelectedItem)
> cmd.Parameters.Add("@EDate", cboDate.Value)
> cmd.Parameters.Add("@CopiesEC", txtCopiesEC.Text)
> cmd.Parameters.Add("@CopiesTB", txtCopiesTB.Text)
> cmd.Parameters.Add("@PagesEC", txtCopiesEC.Text)
> cmd.Parameters.Add("@PagesTB", txtCopiesTB.Text)
> cn.Open()
> cmd.ExecuteNonQuery()
> cmd.Dispose()
> cn.Close()
> Me.Close()
> End Sub
>
> and in sql server 2000 the followin Stored procedure:
>
> ALTER PROCEDURE UpdateEdition
> @Edition as smallint,
> @Color as char(10),
> @EDate as datetime,
> @CopiesEC as int,
> @CopiesTB as int,
> @PagesEC as smallint,
> @PagesTB as smallint
> AS
> If Exists (Select * From Editions WHERE Edition = @Edition)
> UPDATE Editions
> SET Color=@Color, EDate=@EDate, CopiesEC=@CopiesEC, CopiesTB=@CopiesTB,
> PagesEC=@PagesEC, PagesTB=@PagesTB
> WHERE Edition = @Edition
> ELSE
> INSERT INTO Editions (Edition, Color, EDate, CopiesEC, CopiesTB, PagesEC,
> PagesTB)
> VALUES (@Edition ,@Color, @EDate, @CopiesEC, @CopiesTB, @PagesEC,

@PagesTB)

> RETURN
>
>
>

[Jamie Orr]

When I try to add text, its just not doing anything, i put the text tool, then i type, ive tried changing the size and colour, whats wrong?

[Jodi Frye]

Jamie, sounds like you are working with a bitmap image. Go to toolbar>image>mode>change to RGB.

[Jodi Frye]

oops, scratch that...text can be added to a bitmap. So other guesses would be your preferences need to be deleted > hold down alt+ctrl+shift key immediatedly after clicking desktop icon to open elements...note pops up asking if you want to delete prefs say 'OK'. Another thing is that you are adding text to a hidden layer...if you have more than one layer.

[Niles Runeberg]

Can anyone spot an error in this query??!!

<CFQUERY NAME="GetEvents" DATASOURCE="#application.datasource#">
SELECT
EventCategory.EventCategory,EventPrivate,EVENT.Eve ntCatID,EventDate,EventDateEnd
,EventNoTime,EventDescr,EventID,EventName,EventDat e
FROM EVENT left join EventCategory on EVENT.EventCatID =
EventCategory.EventCatID
WHERE EVENT.Inactive = 0 AND EventPrivate = 0 AND
<cfif session.userid is "">
Event.TaskID = 0 and
<cfelse>
<cfif session.userTasks neq 0> (Event.TaskID IN (#session.userTasks#) or
(Event.TaskID = 0 )) and </cfif>
<cfif session.SrchTaskid neq 0> Event.TaskID = #session.SrchTaskid# and
</cfif>
</cfif>
{fn MONTH(EventDate)}={fn MONTH(#ThisDate#)} AND
{fn YEAR(EventDate)}={fn YEAR(#ThisDate#)}
<cfif CatIDs neq 0>
AND EVENT.EventCatID in (#PreserveSingleQuotes(CatIDs)#)
</cfif>
<cfif trim(session.usercats neq "")>
UNION
SELECT
EventCategory.EventCategory,EventPrivate,EVENT.Eve ntCatID,EventDate,EventDateEnd
,EventNoTime,EventDescr,EventID,EventName,EventDat e
FROM EVENT LEFT JOIN EventCategory on EVENT.EventCatID =
EventCategory.EventCatID
WHERE EVENT.Inactive = 0 AND EventPrivate = 1 AND
<cfif session.userid is "">
Event.TaskID = 0 and
<cfelse>
<cfif session.userTasks neq 0> ( Event.TaskID IN (#session.userTasks#) or
Event.TaskID = 0) and </cfif>
<cfif session.SrchTaskid neq 0> (Event.TaskID = #session.SrchTaskid#) and
</cfif>
</cfif>
{fn MONTH(EventDate)}={fn MONTH(#ThisDate#)} AND
{fn YEAR(EventDate)}={fn YEAR(#ThisDate#)}
<cfif session.usercats neq 0>
AND EVENT.EventCatID in (#PreserveSingleQuotes(session.usercats)#)
</cfif>
</cfif>
</CFQUERY>

[Kronin555]

What database are you using?
are Month() or Year() valid functions in your database?
What's the error that's getting thrown? that usually helps when trying to help
troubleshoot something...

{fn MONTH(EventDate)}={fn MONTH(#ThisDate#)} AND
{fn YEAR(EventDate)}={fn YEAR(#ThisDate#)}

[Niles Runeberg]

Oops... very true.

I am getting this error:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] Syntax error (missing operator) in query expression
'EVENT.Inactive = 0 AND EventPrivate = 0 AND (Event.TaskID IN () or
(Event.TaskID = 0 )) and month(EventDate)=month(#11/10/2005 12:11:41#) AND
Year(EventDate)=Year(#11/10/2005 12:11:41#)'.

I am using Microsoft Access and CF MX 6.1

Thanks ;)

[Kronin555]

is month() a function in Access? is year()? if not, that's your problem.

I don't use access, personally, so I can't tell you one way or another whether
those functions are valid. I know in PostgreSQL they aren't.

[Niles Runeberg]

hmmm...

I was thinking that...

What would be a suitable replacement?

[Kronin555]

{fn MONTH(EventDate)}={fn MONTH(#ThisDate#)} AND

to_char(eventdate,'mm') = #month(thisdate)#

This works in Postgresql. Not sure about Access...

[Niles Runeberg]

Wouldn't they both have to be to_char?

[Kronin555]

no, because one is a coldfusion variable (thisdate) and the other one is a
database field (eventdate)

You use a database function on the database field (to_char(eventdate,'mm'))
that converts it to a numeric value between 01 and 12 that represents the month.

You use a coldfusion function on the coldfusion variable (#month(thisdate)#)
that converts it to a numeric value between 1 and 12 that represents the month.

So what you end up sending to the database is this (after Coldfusion has
processed its stuff):

to_char(eventdate,'mm') = 2

And the database, with the to_char function, pulls the month value out of
eventdate, and you end up with something like this:

3 = 2 (assuming the month in eventdate is march, and the month in thisdate is
february)

[paross1]

MONTH() and YEAR() are a valid function in Access, but you don't need all of
the {fn... garbage, just call the function in the query. Try it without it.

SELECT
EventCategory.EventCategory,EventPrivate,EVENT.Eve ntCatID,EventDate,EventDateEnd
,EventNoTime,EventDescr,EventID,EventName,EventDat e
FROM EVENT left join EventCategory on EVENT.EventCatID =
EventCategory.EventCatID
WHERE EVENT.Inactive = 0 AND EventPrivate = 0 AND
<cfif session.userid is "">
Event.TaskID = 0 and
<cfelse>
<cfif session.userTasks neq 0> (Event.TaskID IN (#session.userTasks#) or
(Event.TaskID = 0 )) and </cfif>
<cfif session.SrchTaskid neq 0> Event.TaskID = #session.SrchTaskid# and </cfif>
</cfif>
MONTH(EventDate)=MONTH(#ThisDate#) AND
YEAR(EventDate)=YEAR(#ThisDate#)
<cfif CatIDs neq 0>
AND EVENT.EventCatID in (#PreserveSingleQuotes(CatIDs)#)
</cfif>
<cfif trim(session.usercats neq "")>
UNION
SELECT
EventCategory.EventCategory,EventPrivate,EVENT.Eve ntCatID,EventDate,EventDateEnd
,EventNoTime,EventDescr,EventID,EventName,EventDat e
FROM EVENT LEFT JOIN EventCategory on EVENT.EventCatID =
EventCategory.EventCatID
WHERE EVENT.Inactive = 0 AND EventPrivate = 1 AND
<cfif session.userid is "">
Event.TaskID = 0 and
<cfelse>
<cfif session.userTasks neq 0> ( Event.TaskID IN (#session.userTasks#) or
Event.TaskID = 0) and </cfif>
<cfif session.SrchTaskid neq 0> (Event.TaskID = #session.SrchTaskid#) and
</cfif>
</cfif>
MONTH(EventDate)=MONTH(#ThisDate#)} AND
YEAR(EventDate)=YEAR(#ThisDate#)
<cfif session.usercats neq 0>
AND EVENT.EventCatID in (#PreserveSingleQuotes(session.usercats)#)
</cfif>
</cfif>
</CFQUERY>

Phil

[Niles Runeberg]

There was an arrant } left over, and even after deleting that I still got the same error message.

hmmm...

[paross1]

Maybe something more like this....

MONTH(EventDate) = #MONTH(ThisDate)# AND
YEAR(EventDate) = #YEAR(ThisDate)#

--or--

MONTH(EventDate) =' #MONTH(ThisDate)#' AND
YEAR(EventDate) = '#YEAR(ThisDate)#'

Phil

[Niles Runeberg]

I am not so sure it is the date lines...

I commented portions of the code out and I no longer get the error...

<CFQUERY NAME="GetEvents" DATASOURCE="#application.datasource#">
SELECT
EventCategory.EventCategory,EventPrivate,EVENT.Eve ntCatID,EventDate,EventDateEnd
,EventNoTime,EventDescr,EventID,EventName,EventDat e
FROM EVENT LEFT JOIN EventCategory ON EVENT.EventCatID =
EventCategory.EventCatID
WHERE EVENT.Inactive = 0 AND EventPrivate = 0 AND
<!---<cfif session.userid is "">
Event.TaskID = 0 AND
<cfelse>
<cfif session.userTasks neq 0>
(Event.TaskID IN (#session.userTasks#) OR (Event.TaskID = 0 )) AND
</cfif>
<cfif session.SrchTaskid neq 0>
Event.TaskID = #session.SrchTaskid# AND
</cfif>
</cfif>--->
MONTH(EventDate) = MONTH(#ThisDate#) AND
YEAR(EventDate) = YEAR(#ThisDate#)
<!---<cfif CatIDs neq 0>
AND EVENT.EventCatID in (#PreserveSingleQuotes(CatIDs)#)
</cfif> --->

<cfif trim(session.usercats neq "")>
UNION
SELECT
EventCategory.EventCategory,EventPrivate,EVENT.Eve ntCatID,EventDate,EventDateEnd
,EventNoTime,EventDescr,EventID,EventName,EventDat e
FROM EVENT LEFT JOIN EventCategory ON EVENT.EventCatID =
EventCategory.EventCatID
WHERE EVENT.Inactive = 0 AND EventPrivate = 1 AND
<!---<cfif session.userid is "">
Event.TaskID = 0 AND
<cfelse>
<cfif session.userTasks neq 0> (Event.TaskID IN (#session.userTasks#) OR
Event.TaskID = 0) AND </cfif>
<cfif session.SrchTaskid neq 0> (Event.TaskID = #session.SrchTaskid#) AND
</cfif>
</cfif> --->
MONTH(EventDate) = MONTH(#ThisDate#) AND
YEAR(EventDate) = YEAR(#ThisDate#)
<!---<cfif session.usercats neq 0>
AND EVENT.EventCatID in (#PreserveSingleQuotes(session.usercats)#)
</cfif>--->
</cfif>
</CFQUERY>

I wonder if maybe my CFIF statements are not working properly...

Odd...

[paross1]

What is the data type of some of the database fields within the <CFIF> tags?
For instance, if Event.TaskID is actually character/char/varchar, etc., you
will need to use single quotes around the value that you are comparing to the
field in your query. Failure to do so will generate the error that you are
seeing.

Phil

[Niles Runeberg]

It is something in the commented code... I just have no idea what :(

<CFQUERY NAME="GetEvents" DATASOURCE="#application.datasource#">
SELECT
EventCategory.EventCategory,EventPrivate,EVENT.Eve ntCatID,EventDate,EventDateEnd
,EventNoTime,EventDescr,EventID,EventName,EventDat e
FROM EVENT LEFT JOIN EventCategory ON EVENT.EventCatID =
EventCategory.EventCatID
WHERE EVENT.Inactive = 0 AND EventPrivate = 0 AND
<!---<cfif session.userid is "">
Event.TaskID = 0 AND
<cfelse>
<cfif session.userTasks neq 0>
(Event.TaskID IN (#session.userTasks#) OR (Event.TaskID = 0 )) AND
</cfif>
<cfif session.SrchTaskid neq 0>
Event.TaskID = #session.SrchTaskid# AND
</cfif>
</cfif>--->
MONTH(EventDate) = MONTH(#ThisDate#) AND
YEAR(EventDate) = YEAR(#ThisDate#)
<cfif CatIDs neq 0>
AND EVENT.EventCatID in (#PreserveSingleQuotes(CatIDs)#)
</cfif>

<cfif trim(session.usercats neq "")>
UNION
SELECT
EventCategory.EventCategory,EventPrivate,EVENT.Eve ntCatID,EventDate,EventDateEnd
,EventNoTime,EventDescr,EventID,EventName,EventDat e
FROM EVENT LEFT JOIN EventCategory ON EVENT.EventCatID =
EventCategory.EventCatID
WHERE EVENT.Inactive = 0 AND EventPrivate = 1 AND
<!---<cfif session.userid is "">
Event.TaskID = 0 AND
<cfelse>
<cfif session.userTasks neq 0>
(Event.TaskID IN (#session.userTasks#) OR Event.TaskID = 0) AND
</cfif>
<cfif session.SrchTaskid neq 0>
(Event.TaskID = #session.SrchTaskid#) AND
</cfif>
</cfif> --->
MONTH(EventDate) = MONTH(#ThisDate#) AND
YEAR(EventDate) = YEAR(#ThisDate#)
<cfif session.usercats neq 0>
AND EVENT.EventCatID in (#PreserveSingleQuotes(session.usercats)#)
</cfif>
</cfif>
</CFQUERY>

[Kronin555]

What is SESSION.userTasks? is it a comma-delimited list?

If so, do this:
<cfif listlen(session.userTasks) gt 0>Event.TaskID IN (#session.userTasks#,0) and </cfif>