• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 624
  • Last Modified:

ADO parameter not changing its value

I am new to parameterized queries, and I am having a logic error (not syntax).

I need to update two related tables. I have declared two command objects on the form level (one for each table), and in form_load event I have appended parameters to both command object. This is because I think that it will take much less memory if I append only once parameters, and to reuse them (just to change their value property) later in the code. But it is not working. In save_event I have following code (more or less, I am typing it from head):

conn.begintrans

cmd1.parameters(0).value="some value"
cmd1.parameters(1).value=5
cmd1.parameters(2).value="some value"

cmd1.execute recchanged,,adexecutenorecord

set rs=conn.execute("SELECT @IDENTITY From SomeTable",,adcmdtext)

cmd2.parameters(0).value=rs(0) ' related record (foreign key)

' and here I am looping through some grid rows
for cnt=1 to grid.rows-1
    cmd2.parameters(1).value=grid.textmatrix(cnt,0)
    cmd2.parameters(2).value=grid.textmatrix(cnt,1)
    cmd2.parameters(3).value=grid.textmatrix(cnt,2)
next cnt

cmd2.execute recchanged,,adexecutenorecord

conn.comittrans

And the error is next:

It always inserts the same data - data that was first loaded in parameter. If I unload/load the form, it will insert correctly only 1 row per each table, and every next time I try to insert new data, it will insert the same data as the first time.

So, how to 'refresh' parameter values? Or I am having a bad approach here?
0
Priest04
Asked:
Priest04
  • 10
  • 9
  • 5
  • +1
1 Solution
 
RDWaibelCommented:
Try This...

for cnt=1 to grid.rows-1
    cmd2.parameters(1).value=grid.textmatrix(cnt,0)
    cmd2.parameters(2).value=grid.textmatrix(cnt,1)
    cmd2.parameters(3).value=grid.textmatrix(cnt,2)

    cmd2.execute recchanged,,adexecutenorecord

next cnt


0
 
Priest04Author Commented:
Ups, sorry, I made typing mistake.....

In my code,

cmd2.execute .......

is also in the For....Next loop, not outside it. For both cmd1 and cmd2 the behaviour is the same: they insert the same values which were given to their parameters for the first time.

Any other ideas?
0
 
RDWaibelCommented:
could you past the parameterized queries here for me?

I would like to review them and how you are loading them into cmd1 and cmd2
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
PePiCommented:

for cnt=1 to grid.rows-1

    set cmd2 = Nothing: Set cmd2 = New ADODB.Command   '<--- add this

    cmd2.parameters(1).value=grid.textmatrix(cnt,0)
    cmd2.parameters(2).value=grid.textmatrix(cnt,1)
    cmd2.parameters(3).value=grid.textmatrix(cnt,2)

    cmd2.execute recchanged,,adexecutenorecord

next cnt
0
 
PePiCommented:
Ooooops too.


for cnt=1 to grid.rows-1

    set cmd2 = Nothing: Set cmd2 = New ADODB.Command   '<--- add this

    cmd2.parameters(0).value=rs(0) ' related record (foreign key) '<--- move this here
    cmd2.parameters(1).value=grid.textmatrix(cnt,0)
    cmd2.parameters(2).value=grid.textmatrix(cnt,1)
    cmd2.parameters(3).value=grid.textmatrix(cnt,2)

    cmd2.execute recchanged,,adexecutenorecord

next cnt


HTH!



0
 
Priest04Author Commented:
Here is how I apend parameters

' form_load code
Set cmd1 = New ADODB.Command
   
With cmd1
        .ActiveConnection = conn
        .CommandText = "Proc1"
        .CommandType = adCmdStoredProc
       
        .Parameters.Append .CreateParameter("par1", adInteger, adParamInput, 4, 0)
        .Parameters.Append .CreateParameter("par2", adVarWChar, adParamInput, 10, "")
        .Parameters.Append .CreateParameter("par3", adVarWChar, adParamInput, 50, "")
       etc
End With
0
 
RDWaibelCommented:
That looks right...

Proc1 sets on a SQL Server?
0
 
Priest04Author Commented:
@ PePi

Your code recreates command object every time I execute a stored procedure. It also forces a refresh method in order to get parameter information from stored procedure. This will affect performance and will be resource expensive, no?

@ RDWaibel

I have posted code where I load parameters in one command object before I saw your post. As for the query, its basic update query

PARAMETERS par1 Long, par2 Text ( 10 ), par3 Text ( 50 );
INSERT INTO TableName ( field1, field2, field3 )
SELECT par1 AS Expr1, par2 AS Expr2, par3 AS Expr3;

I can post code for the other query/command object/parameters too, but since both of them behave the same, I didnt want to double the code.
0
 
RDWaibelCommented:
This might sound strange, but have you walked the code to see what is happening?
0
 
Priest04Author Commented:
And another thing:

@PePi

I dont need to set foreign key in for next loop since it is 1 to many relationship, so I need to set it only once, and it is the same for every record in child table.

The database system is access. I had to use query because if I use INSERT INTO statement from VB code, I am receiving an error when adding numbers with format 1.325,50

If decimal symbol is period (.), it will be ok

INSERT INTO TableName (field1,field2,field3) VALUES (3,"some value", 1325.50)

But if decimal symbol is comma (,) (and it must be) then it will look like this

INSERT INTO TableName (field1,field2,field3) VALUES (3,"some value", 1325,50)

and will result in 3 fields and 4 values. This is the reason why I am using access insert query with parameters, then no problem will occur no matter of decimaly symbol.

Got me? :)
0
 
RDWaibelCommented:
Yes, thank you...

Still pounding this one out in my head...
0
 
Priest04Author Commented:
@ RDWaibel

Yes I did, and if I move cursor over parameters to see their values, then parameters have the right value, but when CommitTrans, it only inserts values that were given during the first insert.

0
 
RDWaibelCommented:
Are you setting the Begin tran on each Run?
0
 
RDWaibelCommented:
try it this way...

for cnt=1 to grid.rows-1
conn.begintrans
    conn.begintrans
    cmd2.parameters(0).value=rs(0) ' related record (foreign key) '<--- move this here
    cmd2.parameters(1).value=grid.textmatrix(cnt,0)
    cmd2.parameters(2).value=grid.textmatrix(cnt,1)
    cmd2.parameters(3).value=grid.textmatrix(cnt,2)

    cmd2.execute recchanged,,adexecutenorecord
    conn.comittrans
next cnt
adding a conn.comittrans After the cmd1.Execute as well
0
 
Priest04Author Commented:
Nope, on the begining and on the end of the sub. I even commented transactions, so it is a regular immediate insertation. And still the same ........
0
 
RDWaibelCommented:
ok...
Lets Add
cmd1.Prepared = True
cmd2.Prepared = True
to where you are creating these ADODB Commands
0
 
PePiCommented:
>>@ PePi
>>
>>Your code recreates command object every time I execute a stored procedure. It also forces a refresh method in >>order to get parameter information from stored procedure. This will affect performance and will be resource >>expensive, no?

Basically it it destroys your old command object and instatiates a new one. By destroying the command object, you are re-initializing all the parameters. My guess is that is the reason why the inserted data are all the same in the first place is because your parameters are not being reset. And No this will not be resource expensive and will not affect the performance.

>>@PePi
>>
>>I dont need to set foreign key in for next loop since it is 1 to many relationship, so I need to set it only once, and it >>is the same for every record in child table.

Remember that you are dealing with parameters here. If you destroyed the command object then all the previously set parameters are gone. You need to set those parameters again which inlcudes cmd2.parameters(0).


Here's another suggestion: if you are executing an insert and you are not expecting a return value from your stored procedure. Try using the connection object instead. Here's what I would do:

conn.BeginTrans

for cnt=1 to grid.rows-1

    conn.Execute "Proc1 (" & rs(0) & ", '" & grid.textmatrix(cnt,0) & "', '" & grid.textmatrix(cnt,1) & "', '" & _
          grid.textmatrix(cnt,2) & "'"

next cnt

conn.CommitTrans


0
 
PePiCommented:
Oppps again...


conn.BeginTrans

for cnt=1 to grid.rows-1

    conn.Execute "Proc1 (" & rs(0) & ", '" & grid.textmatrix(cnt,0) & "', '" & grid.textmatrix(cnt,1) & "', '" & _
          grid.textmatrix(cnt,2) & "')"    '<--- forgot to add the close parenthesis

next cnt

conn.CommitTrans
0
 
Priest04Author Commented:
@  PePi

I know it can be done this way, but I didnt want to destroy/reinstanciate command objects, because one of the best ADO guru Bill Vaughn suggest that we should never do such thing, and for the best performance we should reuse existing one. And your code does exactly what he is telling not to do. Especially if I need to insert 200 rows in child table, then I would be creating 201 comand objects in one turn. And consider the possibility that user will have 50 sales of this quantity....

There must be some way to reuse those parameters... I have write some code for testing purposes only:

I created a table named TableName and added 3 fields: field1 (primary key/auto number), field 2 (Text field (5)), and field3 (number (long))

and I have placed this code in a form:

Option Explicit
Dim conn As ADODB.Connection

Private Sub cmdCreateProc_Click()
    conn.Open
   
    conn.Execute "CREATE PROC ProcExample (prm1 VARCHAR(50), prm2 LONG) AS " _
        & "INSERT INTO TableName (field2,field3) VALUES (prm1,prm2)"
    conn.Close
End Sub

Private Sub cmdInsertData_Click()
Dim cmd As New ADODB.Command

    With cmd
       
        .CommandType = adCmdStoredProc
        .CommandText = "ProcExample"
        .Parameters.Append .CreateParameter("par1", adVarWChar, adParamInput, 50, "some value")
        .Parameters.Append .CreateParameter("par2", adInteger, adParamInput, 4, 10)
       
        conn.Open
       
        .ActiveConnection = conn
   
        .Execute , , adExecuteNoRecords
   
        .Parameters(0).Value = "some value 2"
        .Parameters(1).Value = 20
       
       
        .Execute , , adExecuteNoRecords
   
        conn.Close
    End With
   
    Set cmd = Nothing
   
End Sub

Private Sub Form_Load()
    Set conn = New ADODB.Connection
   
    conn.ConnectionString = "provider=microsoft.jet.oledb.4.0;" _
        & "data source=c:\test.mdb;"
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set conn = Nothing
End Sub


and the result was:

TableName:

field1      field2                      field3
1      some value      10
2      some value      10


Could be the reason that I am having autonumber field?
0
 
Priest04Author Commented:
Again typing mistake. It is

created a table named TableName and added 3 fields: field1 (primary key/auto number), field 2 (Text (50)), and field3 (number (long))

@  RDWaibel
Prepared property worked. The reason why I have never tried to use this property is...
Another thing:

Quoting from Bill Vaughn's article in SQL Server magazine:

Don't force SQL Server to recompile and construct a query plan for your query each time it's executed. An easy way to avoid this repetition is to create and use parameter-based stored procedures. Don't bother with the ADO Command object's Prepare property—it doesn't work correctly. When creating stored procedures, you can further help ADO performance by eliminating unneeded "rows affected" values from the returned resultsets—simply add SET NOCOUNT ON to the stored procedure.

Now that I have seen that article is from 2001 year, it could be that in newer ADO versions this mistake is corrected. :)

May I ask only, why is it necessary to use this property? Why isnt it working without it?
0
 
RDWaibelCommented:
I use ADO 2.8 in my applications, and many of those issues have been fixed.

to be honest, I don't rightly know.  I do know that letting ADO know that it is prepared allows for faster running, which why I use it.

MSDN 2004 reads:
The query string is prepared so that different sets of parameters can be supplied. Because the Prepared property is set to TRUE, different values can be supplied to cmdPrep1 without reconstructing and re-executing the query string.

/Rob
0
 
PePiCommented:


Private Sub cmdInsertData_Click()
Dim cmd As New ADODB.Command

    With cmd
       
        .CommandType = adCmdStoredProc
        .CommandText = "ProcExample"
        .Parameters.Append .CreateParameter("par1", adVarWChar, adParamInput, 50, "some value")
        .Parameters.Append .CreateParameter("par2", adInteger, adParamInput, 4, 10)
       
        conn.Open
       
        .ActiveConnection = conn
   
        .Execute , , adExecuteNoRecords
   
        .Parameters(0).Value = "some value 2"
        .Parameters(1).Value = 20
       
       
        .Execute , , adExecuteNoRecords

        'humor me for a moment... can you add these line:
        conn.Execute "ProcExample (' & "another value" & "', " & 50 & ")"
        conn.Execute "ProcExample (' & "some value 2" & "', " & 20 & ")"
        'here you did not use a command object, you are using the connection object which means
        'no destroying of objects and no re-instantiation

   
        conn.Close
    End With
   
    Set cmd = Nothing
   
End Sub


Your table should have something like this:

TableName:

field1     field2                     field3
1     some value     10
2     some value     10
3     another value  50
4     some value 2   20


0
 
Priest04Author Commented:
@ PePi

As I mentioned, I am using access database, so I could write something like this:

conn.Execute "ProcExample '" & somestring & "', " & 50 & ")"

And it will work as long as I have period (.) as a decimal symbol.

But if I have comma (,) as a decimal symbol then if I try to insert 50,55 decimal number it would look something like this:

conn.Execute "ProcExample 'some value,50,55)

and access would truncate 55 and store

'some value',50

this is the reason why I had to use parameters and stored queries. The above example I gave has 2nd parameter type Long, but as I said, it was just for demonstration purposes, so it can be easier to find out why parameters arent refreshing. In real application, both tables have more than 20 fields together.

PS. As for the performanse, I have measured the speed when reusing command object and whwn recreating it for every execute statement, and in 1000 loop first one was almost 3 times faster than the 2nd one.
0
 
Anthony PerkinsCommented:
>>As I mentioned, I am using access database, so I could write something like this<<
Than you can ignore all Bill's comments regarding caching a query plan, it does not apply.

PePi has given you the reason why it is happening, and usually the way this is done is to destroy the Command object after each iteration.  This does not cause a roundtrip to the server (in the case of SQL Server), but since you are using MS Access this again is a non-issue.  The only other choice you have is to clear individual parameters using the Delete method.

You may not like the answer to your problem, but that is the way it goes.



0
 
Anthony PerkinsCommented:
The only other choice you have and only IF the Command object is executing over the same query than you can Declare it globally append the parameters once and just assign the values in each iteration.  

This has the side-effect of using global variables which will make your code ugly as sin, but if that is your preference...

Good luck
0
 
Priest04Author Commented:
@  acperkins

I have wrote the first code almost the same as Pepi said, and it was working. But since this Save_click event will be the most used, since it is for selling purposes, then I need it to be fast. This is the main reason why I started to search for alternatives, since for some medium buy it needed around 1.5 seconds to finish. With method that is resuing the command/parameters, it was down to 0.6 seconds. The computer where it will be working is Pentium II 366 MHz, so every performance benefits needs to be embraced.

So, this is the only reason why I couldnt accept the idea to recreate command/parameters. Not accepting the idea is the reason why I have posted the question here.

I have declared this object on a form level (no need for global declaration), appended parameters during the form load, and I need it as long as the form is opened. This 2nd idea was performing better, but I had a great issue with the parameters.

RDWaibel's solution with the Prepared property works, and I am now testing it to see if there is some downside of it.

Since you didnt mention this property to be used as a solution, do you find this method unsecure?
0
 
Anthony PerkinsCommented:
>>But since this Save_click event will be the most used, since it is for selling purposes, then I need it to be fast.<<
I suspect one reason it is slow is because you are using transactions.  You may want to test without, to see if you see any significant difference.

>>I have declared this object on a form level (no need for global declaration)<<
It is still a public variable to the form, so the same undesrirable code side-effects.

>>Since you didnt mention this property to be used as a solution, do you find this method unsecure?<<
I don't know, I have never used it.  Probably because I read the same paragraph as you :)  On the other hand, I have not used MS Access in over 5 years, so my opinion in that regard does not really apply.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 9
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now