NULL Parameters in TADOQuery

Thread View: borland.public.cppbuilder.database.ado

4 messages

[T] Back to Threads [V] Tree View [G] Group Articles

4 total messages Started by Ruben Lopez Mon, 20 Nov 2006 16:05

#419

Author: Ruben Lopez
Date: Mon, 20 Nov 2006 16:05

39 lines
2004 bytes

Hi to all!

I need send a sqlquery using the TADOQuery. I use the next metod.

//****************************************************
   TADOQuery *q=new TADOQuery(NULL);
   q->Connection = this->ADOConnection1;//Conected OK
   q->SQL->Clear();
   q->SQL->Add("INSERT INTO Table Values(:Filed_Id,:Name1,:Value1)");
   q->Parameters->Clear();
   q->Parameters->ParseSQL(q->SQL->Text,true);
   q->Parameters->Items[0] = 1;
   q->Parameters->Items[1] = NULL; //I test q->Parameters->Items[1] = "";
   q->Parameters->Items[2] = NULL;
   try
   {
     q->ExecSQL();
   }
   catch(Exception *e)
   {
     Application->ShowException(e);
   }


Name1 it's a NVARCHAR(255) value whit IS NULL option.
Value1 it's an Integer value whit IS NULL option.

The problem is in the Patameter[Name1], when put the parameter to NULL, 
raise an Exception, if put a value like " " it's all right.

The exception It's in Spanish and I try to Traduce it:

	
Not this allowed to the implicit conversion of the data type TEXT to the 
data type NVARCHAR.Use the Function CONVERT TO EXECUTE THIS query.



How I can put this value to null;

Re: NULL Parameters in TADOQuery

#420

Author: Clayton Arends
Date: Mon, 20 Nov 2006 16:44

24 lines
1831 bytes

Items[] returns an object of type TParameter.  Setting that to NULL is not 
what you want to do.  You want to access the properties of the returned 
object.  Try this instead:

  q->Parameters->Items[0]->Value = 1;
  q->Parameters->Items[1]->Value = NULL;
  q->Parameters->Items[2]->Value = NULL;

In some cases you might have to set the type of the parameter correctly:

  q->Parameters->Items[1]->DataType = ftString; // for example

Also, this isn't part of your problem but take a look at your following 
code:

>   q->Parameters->Clear();
>   q->Parameters->ParseSQL(q->SQL->Text,true);

If ParamCheck is set to true for the query "q" (which it is by default) then 
this code is already being performed when q->SQL is set.  There is no need 
to do it twice.

HTH,
- Clayton

Re: NULL Parameters in TADOQuery

#421

Author: Ruben Lopez
Date: Tue, 21 Nov 2006 12:10

40 lines
2286 bytes

Thanks for your reply.

And excuse me. I put the Property->Items[]->Value in mi source code but 
I forgot put the "->Value" in the question.

The problen it's that I can't put a NULL value in a property.
If I put NULL in a Integer value, it is converted to 0.

The q->Parameters->ParseSQL(q->SQL->Text,true); works fine, create all 
of the params for the q->SQLText query.

Thanks again.

Clayton Arends escribi�:
> Items[] returns an object of type TParameter.  Setting that to NULL is not 
> what you want to do.  You want to access the properties of the returned 
> object.  Try this instead:
> 
>   q->Parameters->Items[0]->Value = 1;
>   q->Parameters->Items[1]->Value = NULL;
>   q->Parameters->Items[2]->Value = NULL;
> 
> In some cases you might have to set the type of the parameter correctly:
> 
>   q->Parameters->Items[1]->DataType = ftString; // for example
> 
> Also, this isn't part of your problem but take a look at your following 
> code:
> 
>>   q->Parameters->Clear();
>>   q->Parameters->ParseSQL(q->SQL->Text,true);
> 
> If ParamCheck is set to true for the query "q" (which it is by default) then 
> this code is already being performed when q->SQL is set.  There is no need 
> to do it twice.
> 
> HTH,
> - Clayton 
> 
>

Re: NULL Parameters in TADOQuery

#422

Author: Clayton Arends
Date: Tue, 21 Nov 2006 23:03

13 lines
1610 bytes

> The problen it's that I can't put a NULL value in a property.
> If I put NULL in a Integer value, it is converted to 0.

I made the same mistake in my post.  Use "Null()" rather than "NULL".

> The q->Parameters->ParseSQL(q->SQL->Text,true); works fine, create all of 
> the params for the q->SQLText query.

Maybe so, but I was trying to point out that this is done by default when 
you alter the SQL property.  Remove the call to ParseSQL() and you will see 
that your code will execute the same.

- Clayton

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads