"No value specified for some required parameters"

5 Feb 2006 8:38 PM

Andrea Raimondi

D'oh!

I'm feeling this is related to ADO.NET and maybe you can help here.
Please, don't hide behind "Microsofties don't know pascal" because
this is a very framework issue rather than Delphi.

This is the code:

procedure TFaqService.AddItem(Q,A : String);
var OleDBParam : OleDBParameter;
begin
   OleDBParam := OleDBParameter.Create('QuestionText',OleDbType.VarWChar,
                                       255, Q);
   InsertFAQ.Parameters.Add(OleDBParam);
   OleDBParam := OleDBParameter.Create('AnswerText',OleDbType.VarWChar,
                                       255, A);
   InsertFAQ.Parameters.Add(OleDBParam);
   ADOConn.Open;
   InsertFAQ.ExecuteNonQuery;
   ADOConn.Close;
end;

For your facility(even though I beleive not strictly necessary), some
explainations:

ADOConn -> OLEDBConnection
InsertFAQ -> OLEDBCommand

Other data:

Database schema(1 table, Access):

TABLE FAQS
ID_Question Autoincrement
QuestionText Text 255
AnswerText   Text 255

Query:
Insert Into Faqs(QuestionText,AnswerText)
             values(QuestionText=?,AnswerText=?)

This is the relevant code into "InitializeComponents":
(self is referred to the webservice class)
Self.ADOConn := System.Data.OleDb.OleDbConnection.Create;
Self.InsertFAQ := System.Data.OleDb.OleDbCommand.Create;
Self.InsertFAQ.CommandText := 'Insert Into Faqs(QuestionText,
                               'AnswerText) values(QuestionText=?,'+
                               'AnswerText=?)';
Self.InsertFAQ.Connection := Self.ADOConn;

Can you see anything wrong? Anything I didn't set that should
instead be?

Cheers,

Andrew

6 Feb 2006 12:20 AM

pvdg42

Show quote

"Andrea Raimondi" <raina***@tin.it> wrote in message
news:Oj$wkQpKGHA.1180@TK2MSFTNGP09.phx.gbl...
> D'oh!
>
> I'm feeling this is related to ADO.NET and maybe you can help here.
> Please, don't hide behind "Microsofties don't know pascal" because
> this is a very framework issue rather than Delphi.
>
> This is the code:
>
> procedure TFaqService.AddItem(Q,A : String);
> var OleDBParam : OleDBParameter;
> begin
>   OleDBParam := OleDBParameter.Create('QuestionText',OleDbType.VarWChar,
>                                       255, Q);
>   InsertFAQ.Parameters.Add(OleDBParam);
>   OleDBParam := OleDBParameter.Create('AnswerText',OleDbType.VarWChar,
>                                       255, A);
>   InsertFAQ.Parameters.Add(OleDBParam);
>   ADOConn.Open;
>   InsertFAQ.ExecuteNonQuery;
>   ADOConn.Close;
> end;
>
> For your facility(even though I beleive not strictly necessary), some
> explainations:
>
> ADOConn -> OLEDBConnection
> InsertFAQ -> OLEDBCommand
>
> Other data:
>
> Database schema(1 table, Access):
>
> TABLE FAQS
> ID_Question Autoincrement
> QuestionText Text 255
> AnswerText   Text 255
>
> Query:
> Insert Into Faqs(QuestionText,AnswerText)
>             values(QuestionText=?,AnswerText=?)
>
> This is the relevant code into "InitializeComponents":
> (self is referred to the webservice class)
> Self.ADOConn := System.Data.OleDb.OleDbConnection.Create;
> Self.InsertFAQ := System.Data.OleDb.OleDbCommand.Create;
> Self.InsertFAQ.CommandText := 'Insert Into Faqs(QuestionText,
>                               'AnswerText) values(QuestionText=?,'+
>                               'AnswerText=?)';
> Self.InsertFAQ.Connection := Self.ADOConn;
>
> Can you see anything wrong? Anything I didn't set that should
> instead be?
>
> Cheers,
>
> Andrew

Let's start with some basics. What, exactly,does the error message you get
say?

6 Feb 2006 7:22 AM

Andrea Raimondi

pvdg42 wrote:

> Let's start with some basics. What, exactly,does the error message you get
> say?

The *exact* error message is reported in subject and no, I'm not
kidding.

Andrew

6 Feb 2006 1:28 AM

Scott M.

Is it me or are you adding OleDBParam to the parameters collection of
InsertFAQ twice? And are you configuring OleDBParam twice as well?

Instead of re-using the parameter, I would create 2 explicit parameter
objects, configure each approprately and add each individually.

[VB.NET]

Dim paramOne as New OleDBParameter(...)
Dim paramTwo as New OleDBParameter(...)

InsertFAQ.Parameters.Add(paramOne)
InsertFAQ.Parameters.Add(paramTwo)

InsertFAQ.ExecuteNonQuery

Show quote

"Andrea Raimondi" <raina***@tin.it> wrote in message
news:Oj$wkQpKGHA.1180@TK2MSFTNGP09.phx.gbl...
> D'oh!
>
> I'm feeling this is related to ADO.NET and maybe you can help here.
> Please, don't hide behind "Microsofties don't know pascal" because
> this is a very framework issue rather than Delphi.
>
> This is the code:
>
> procedure TFaqService.AddItem(Q,A : String);
> var OleDBParam : OleDBParameter;
> begin
>   OleDBParam := OleDBParameter.Create('QuestionText',OleDbType.VarWChar,
>                                       255, Q);
>   InsertFAQ.Parameters.Add(OleDBParam);
>   OleDBParam := OleDBParameter.Create('AnswerText',OleDbType.VarWChar,
>                                       255, A);
>   InsertFAQ.Parameters.Add(OleDBParam);
>   ADOConn.Open;
>   InsertFAQ.ExecuteNonQuery;
>   ADOConn.Close;
> end;
>
> For your facility(even though I beleive not strictly necessary), some
> explainations:
>
> ADOConn -> OLEDBConnection
> InsertFAQ -> OLEDBCommand
>
> Other data:
>
> Database schema(1 table, Access):
>
> TABLE FAQS
> ID_Question Autoincrement
> QuestionText Text 255
> AnswerText   Text 255
>
> Query:
> Insert Into Faqs(QuestionText,AnswerText)
>             values(QuestionText=?,AnswerText=?)
>
> This is the relevant code into "InitializeComponents":
> (self is referred to the webservice class)
> Self.ADOConn := System.Data.OleDb.OleDbConnection.Create;
> Self.InsertFAQ := System.Data.OleDb.OleDbCommand.Create;
> Self.InsertFAQ.CommandText := 'Insert Into Faqs(QuestionText,
>                               'AnswerText) values(QuestionText=?,'+
>                               'AnswerText=?)';
> Self.InsertFAQ.Connection := Self.ADOConn;
>
> Can you see anything wrong? Anything I didn't set that should
> instead be?
>
> Cheers,
>
> Andrew

6 Feb 2006 7:28 AM

Andrea Raimondi

Scott M. wrote:

> Instead of re-using the parameter, I would create 2 explicit parameter
> objects, configure each approprately and add each individually.

The error happens again, regardless of the code change:

procedure TFaqService.AddItem(Q,A : String);
var QOleDBParam : OleDBParameter;
     AOleDBParam : OleDBParameter;
begin
   QOleDBParam := OleDBParameter.Create('QuestionText',
                                        OleDbType.VarWChar, 255, Q);
   InsertFAQ.Parameters.Add(QOleDBParam);
   AOleDBParam := OleDBParameter.Create('AnswerText',OleDbType.VarWChar,
                                         255, A);
   InsertFAQ.Parameters.Add(AOleDBParam);
   ADOConn.Open;
   InsertFAQ.ExecuteNonQuery;
   ADOConn.Close;
end;

Cheers,

Andrew

6 Feb 2006 7:47 AM

Cor Ligthert [MVP]

Andrea,

I am not sure where, however in my expirience needs OleDb sometimes an extra
parameter.
Just adding one extra add the end solves than my problems.

Although I would folllow the advice from Scott and use the more regular
methods as he shows. The way you do it is completely not to understand for
me. I should have to deeper invest it while I see not any need for myself
for that. The methods Scott shows work fine.

Cor

6 Feb 2006 8:10 AM

Andrea Raimondi

Cor Ligthert [MVP] wrote:

> Andrea,

Cor,

> Although I would folllow the advice from Scott and use the more regular
> methods as he shows. The way you do it is completely not to understand for
> me. I should have to deeper invest it while I see not any need for myself
> for that. The methods Scott shows work fine.

The method I use is simply saving a variable :-)

That is, creating a new object in an existing variable disconnects it
from the previous reference and uses it for another.
It's very handy.

However, using 2 separate parameters didn't solve the issue and I am
really at loss about what the problem could be...

> Cor

Andrew

6 Feb 2006 8:49 AM

Cor Ligthert [MVP]

Andrea,

Did you try as well that other part I wrote, add a thirth parameter in the
end in the collection. Which you do not use.

Cor

6 Feb 2006 2:56 PM

Andrea Raimondi

Cor Ligthert [MVP] wrote:

> Andrea,

Cor,

> Did you try as well that other part I wrote, add a thirth parameter in the
> end in the collection. Which you do not use.

Let me say, as a first thing, that I find your statement scary, but
that's for the second part of this post.

Secondly, there really was a field which wasn't set, so I added it.
Now all fields are set, but ADO.NET keeps saying that no value was
specified for some required parameters. The query is this now:

Insert Into Faqs(ID_QUESTION,QuestionText,AnswerText)
values(null,QuestionText=?,AnswerText=?)

By the way, what makes your statement so scary to me is the simple fact
that such a thing is a risk, if tomorrow you have to add another field.
Namely, the database might evolve to inconsistency and you may discover
this when it's too late.
For this reason, I really hope you're wrong.

However, I had been trying this:

procedure TFaqService.AddItem(Q,A : String);
var QOleDBParam : OleDBParameter;
     AOleDBParam : OleDBParameter;
     TOleDBParam : OleDBParameter;
begin
   QOleDBParam := OleDBParameter.Create('',OleDbType.VarWChar, 255, Q);
   InsertFAQ.Parameters.Add(QOleDBParam);
   AOleDBParam := OleDBParameter.Create('',OleDbType.VarWChar, 255, A);
   InsertFAQ.Parameters.Add(AOleDBParam);
   TOleDBParam := OleDBParameter.Create('',OleDbType.VarWChar, 255, '');
   InsertFAQ.Parameters.Add(QOleDBParam);
   ADOConn.Open;
   InsertFAQ.ExecuteNonQuery;
   ADOConn.Close;
end;

with this query:

Insert Into Faqs(ID_QUESTION,QuestionText,AnswerText) values(null,?,?)

And now this is my error message:
OleDBParameterCollection already contains an OleDBParameter named
"Parameter1". Sigh.

What am I doing wrong?

> Cor

Andrew

6 Feb 2006 9:25 PM

Cor Ligthert [MVP]

Andrea,

An often made error is that those parameters are added and added again. You
only have to add them once or to clean everytime the parameter collection in
advance.

I did not see any advice in this directons in the answers yet.

Cor

Show quote

"Andrea Raimondi" <raina***@tin.it> schreef in bericht
news:uyVBD2yKGHA.2036@TK2MSFTNGP14.phx.gbl...
> Cor Ligthert [MVP] wrote:
>> Andrea,
>
> Cor,
>
>> Did you try as well that other part I wrote, add a thirth parameter in
>> the end in the collection. Which you do not use.
>
> Let me say, as a first thing, that I find your statement scary, but
> that's for the second part of this post.
>
> Secondly, there really was a field which wasn't set, so I added it.
> Now all fields are set, but ADO.NET keeps saying that no value was
> specified for some required parameters. The query is this now:
>
> Insert Into Faqs(ID_QUESTION,QuestionText,AnswerText)
> values(null,QuestionText=?,AnswerText=?)
>
> By the way, what makes your statement so scary to me is the simple fact
> that such a thing is a risk, if tomorrow you have to add another field.
> Namely, the database might evolve to inconsistency and you may discover
> this when it's too late.
> For this reason, I really hope you're wrong.
>
> However, I had been trying this:
>
> procedure TFaqService.AddItem(Q,A : String);
> var QOleDBParam : OleDBParameter;
>     AOleDBParam : OleDBParameter;
>     TOleDBParam : OleDBParameter;
> begin
>   QOleDBParam := OleDBParameter.Create('',OleDbType.VarWChar, 255, Q);
>   InsertFAQ.Parameters.Add(QOleDBParam);
>   AOleDBParam := OleDBParameter.Create('',OleDbType.VarWChar, 255, A);
>   InsertFAQ.Parameters.Add(AOleDBParam);
>   TOleDBParam := OleDBParameter.Create('',OleDbType.VarWChar, 255, '');
>   InsertFAQ.Parameters.Add(QOleDBParam);
>   ADOConn.Open;
>   InsertFAQ.ExecuteNonQuery;
>   ADOConn.Close;
> end;
>
> with this query:
>
> Insert Into Faqs(ID_QUESTION,QuestionText,AnswerText) values(null,?,?)
>
> And now this is my error message:
> OleDBParameterCollection already contains an OleDBParameter named
> "Parameter1". Sigh.
>
> What am I doing wrong?
>
>> Cor
>
> Andrew