Re: Stored Procedures & Parameters Insert Problem

incubusaurus@xxxxxxxxx wrote:
Hi,

I am using ADO, in a fairly simple VB 6.0 client based database
application, to manipulate an Access database. When I use a stored
procedure with parametised inputs to insert multiple records into a
table, I end up with the same values repeated for each record, despite
changing the value of the parameter. I suspect that somewhere along
the line, the parameter value is being cached. To simplify this post,
I have written a small procedure (see below) which produces the same
results as the application.

<snip>

adoDBConn.Open "Provider=MSDataShape;" & _

Why use the MSDataShape provider? It is not needed for this activity. If
your application has need for the functionality provided by this provider,
You should consider using a separate Connection for that activity.


"Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\Test.mdb"

adoCommand.ActiveConnection = adoDBConn

On Error Resume Next
adoDBConn.Execute "DROP PROCEDURE sp_InsertCount"

Why drop and recreate this procedure? It is contrary to the purpose of using
stored procedures: create it once and re-use it.

On Error GoTo 0

strCmd = "CREATE PROCEDURE sp_InsertCount AS "

This is now the time to get out of the habit of using "sp_" to prefix your
stored procedure names. it is not hurting you in Jet, but it will definitely
impair the efficiency of your procedures in SQL Server, which uses that
prefix to identify system stored procedures. When told to execute a query
with that prefix, SQL Server will waste time looking for the procedure in
the Master database, only looking for it in the current database when it is
not found in Master. Also, if you inadvertantly give te procedure the same
name as a system stored procedure, you may find yourself trying to figure
out errors due to the attempted execution of the wrong procedure.

strCmd = strCmd & "INSERT INTO TestCount (lngTestCount) "
strCmd = strCmd & "VALUES (@lngTestCount)"

adoDBConn.Execute strCmd

adoCommand.CommandText = "sp_InsertCount"
adoCommand.CommandType = adCmdStoredProc

Set prmADOParam = adoCommand.CreateParameter _
("@lngTestCount", adInteger, adParamInput, , Null)

adoCommand.Parameters.Append prmADOParam

' ** This produces 20 rows all with the same value **
For lngTestCount = 1 To 20

adoCommand.Parameters("@lngTestCount").Value = lngTestCount

It will be more efficient to use the prmADOParam object you went to such
trouble to create instead of going through the Parameters colection here.
prmADOParam .Value=lngTestCount


I would experiment with
1) changing the Provider to the Jet provider, eliminating the MSDataShape
provider. If that does not help:
2) setting the Command's Prepared property to True or False to see which
setting solves the issue.


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • Re: MS Oracle Data Provider BUG: Data corrupt after 16 rows with O
    ... Could you use this to check the data in your database? ... Once you have done this with both the Oracle managed provider and either ... imported into the 9i database without any errors (i tried 8i Client as well ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: application design problem
    ... Actual action is depends on your Provider which provide service for a particual database or data store. ... When I would click Button1 this sql would execute: ... I dont think its a good idea to put all your client spcific logic ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Why the police now have to ask teenage muggers: Do you eat chips?
    ... can you prefix the subject with 'SHIT' so that sensible people can ... within an Every Child <atters programme. ... life tied to this database. ... If people don't resist information gathering and information ...
    (uk.legal)
  • Re: Choosing a Windows Database
    ... >> use some kind of relational database. ... >> multiple users can access and something scalable and fast. ... >> ADO.NET or ODBC provider. ... >> out there using the DBF type files. ...
    (microsoft.public.vb.database)
  • Re: VS.net installs sql express as default datastore for role/membersh
    ... I can use sql or access databases for dataaccess, ... Asp.net web site administration tool. ... Select provider configuration ... it seems easy to select an other database. ...
    (microsoft.public.dotnet.framework.aspnet)