Operand type clash

[dw]

Hello all. We have a page that calls a SQL Server database using this
method,

cn.uspGetEventPerson Null, Null, rsEventPersons

where cn is our connection object, and uspGetEventPerson is the stored
procedure. When we call the database with nulls, we get the following error:

Microsoft OLE DB Provider for SQL Server
Operand type clash: text is incompatible with int

Both of the fields are integer fields, and the stored procedure. When we use
an ADO Command object and define the parameters, it works fine, even if
those two parameters are null. Why does the shortcut method break and the
command object work? Thanks in advance.

[Bob Barrows [MVP]]

dw wrote:

> Hello all. We have a page that calls a SQL Server database using this
> method,
>
> cn.uspGetEventPerson Null, Null, rsEventPersons
>
> where cn is our connection object, and uspGetEventPerson is the stored
> procedure. When we call the database with nulls, we get the following
> error:
>
> Microsoft OLE DB Provider for SQL Server
> Operand type clash: text is incompatible with int
>
> Both of the fields are integer fields, and the stored procedure.

It would help if you showed the portion of your CREATE PROCEDURE statement
in which the parameters are declared. Without that, we have to resort to
guesswork.

> When
> we use an ADO Command object and define the parameters, it works
> fine, even if those two parameters are null. Why does the shortcut
> method break and the command object work? Thanks in advance.

Without the explicit Command object, ADO is making a guess as to datatype,
and guessing wrong. Are these optional parameters? If so, you should be able
to call the procedure without specifying these arguments:

cn.uspGetEventPerson rsEventPersons

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[dw]

Thanks, Bob. Here's the procedure's CREATE portion,

CREATE PROCEDURE dbo.uspGetEventPerson
@prmEventID int = null,
@prmTerm varchar (6) = null
AS .....

The second parameter is a varchar, not an int as indicated earlier. These
two parameters are optional. Thanks for your help.


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eXI9%2392MEHA.3944@tk2msftngp13.phx.gbl...

> dw wrote:

> > Hello all. We have a page that calls a SQL Server database using this
> > method,
> >
> > cn.uspGetEventPerson Null, Null, rsEventPersons
> >
> > where cn is our connection object, and uspGetEventPerson is the stored
> > procedure. When we call the database with nulls, we get the following
> > error:
> >
> > Microsoft OLE DB Provider for SQL Server
> > Operand type clash: text is incompatible with int
> >
> > Both of the fields are integer fields, and the stored procedure.

>
> It would help if you showed the portion of your CREATE PROCEDURE statement
> in which the parameters are declared. Without that, we have to resort to
> guesswork.
>

> > When
> > we use an ADO Command object and define the parameters, it works
> > fine, even if those two parameters are null. Why does the shortcut
> > method break and the command object work? Thanks in advance.

>
> Without the explicit Command object, ADO is making a guess as to datatype,
> and guessing wrong. Are these optional parameters? If so, you should be

able

> to call the procedure without specifying these arguments:
>
> cn.uspGetEventPerson rsEventPersons
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[Bob Barrows [MVP]]

dw wrote:

> Thanks, Bob. Here's the procedure's CREATE portion,
>
> CREATE PROCEDURE dbo.uspGetEventPerson
> @prmEventID int = null,
> @prmTerm varchar (6) = null
> AS .....
>
> The second parameter is a varchar, not an int as indicated earlier.

That explains the error message.

> These two parameters are optional. Thanks for your help.
>

So does my suggestion work?

cn.uspGetEventPerson rsEventPersons

>
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:eXI9%2392MEHA.3944@tk2msftngp13.phx.gbl...

>> dw wrote:

>>> Hello all. We have a page that calls a SQL Server database using
>>> this method,
>>>
>>> cn.uspGetEventPerson Null, Null, rsEventPersons
>>>
>>> where cn is our connection object, and uspGetEventPerson is the
>>> stored procedure. When we call the database with nulls, we get the
>>> following error:
>>>
>>> Microsoft OLE DB Provider for SQL Server
>>> Operand type clash: text is incompatible with int
>>>
>>> Both of the fields are integer fields, and the stored procedure.

>>
>> It would help if you showed the portion of your CREATE PROCEDURE
>> statement in which the parameters are declared. Without that, we
>> have to resort to guesswork.
>>

>>> When
>>> we use an ADO Command object and define the parameters, it works
>>> fine, even if those two parameters are null. Why does the shortcut
>>> method break and the command object work? Thanks in advance.

>>
>> Without the explicit Command object, ADO is making a guess as to
>> datatype, and guessing wrong. Are these optional parameters? If so,
>> you should be able to call the procedure without specifying these
>> arguments:
>>
>> cn.uspGetEventPerson rsEventPersons
>>

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[dw]

Yes, it does. Works like a charm! Thanks, Bob.

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:Os%23fwP3MEHA.3348@TK2MSFTNGP09.phx.gbl...

> dw wrote:

> > Thanks, Bob. Here's the procedure's CREATE portion,
> >
> > CREATE PROCEDURE dbo.uspGetEventPerson
> > @prmEventID int = null,
> > @prmTerm varchar (6) = null
> > AS .....
> >
> > The second parameter is a varchar, not an int as indicated earlier.

>
> That explains the error message.
>

> > These two parameters are optional. Thanks for your help.
> >

> So does my suggestion work?
>
> cn.uspGetEventPerson rsEventPersons
>

> >
> > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> > news:eXI9%2392MEHA.3944@tk2msftngp13.phx.gbl...

> >> dw wrote:
> >>> Hello all. We have a page that calls a SQL Server database using
> >>> this method,
> >>>
> >>> cn.uspGetEventPerson Null, Null, rsEventPersons
> >>>
> >>> where cn is our connection object, and uspGetEventPerson is the
> >>> stored procedure. When we call the database with nulls, we get the
> >>> following error:
> >>>
> >>> Microsoft OLE DB Provider for SQL Server
> >>> Operand type clash: text is incompatible with int
> >>>
> >>> Both of the fields are integer fields, and the stored procedure.
> >>
> >> It would help if you showed the portion of your CREATE PROCEDURE
> >> statement in which the parameters are declared. Without that, we
> >> have to resort to guesswork.
> >>
> >>> When
> >>> we use an ADO Command object and define the parameters, it works
> >>> fine, even if those two parameters are null. Why does the shortcut
> >>> method break and the command object work? Thanks in advance.
> >>
> >> Without the explicit Command object, ADO is making a guess as to
> >> datatype, and guessing wrong. Are these optional parameters? If so,
> >> you should be able to call the procedure without specifying these
> >> arguments:
> >>
> >> cn.uspGetEventPerson rsEventPersons
> >>

>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>