I was answering a newsgroup question this evening and it reminded me of a revelation I had awhile ago but forgot to mention.
One of the problems developers have to worry about when using stored procedures to perform complex updates (where several tables may (or may not) be updated), is figuring out how to trick ADO.NET into thinking the update/insert/delete succeeded. What if developers had the ability to change what ADO.NET expects to return from the action commands? As it is, ADO.NET expects rows affected = 1 and nothing else. What if the developer could choose to set a Return value of True (1), an OUTPUT parameter or some other mechanism to signal that the action succeeded? This way their own stored procedure logic could determine whether or not the action triggered a collision or other problem—that should not be considered as a concurrency issue.
Since ADO.NET is not getting any better at dealing with concurrency, it’s going to force more developers to use their own home-grown stored procedures to carry out these changes. It would be nice if they had more control over the interface.
Now back to your regularly scheduled program…
Bill
Maybe I didn't catch the meaning of your post, but --
"how to trick ADO.NET into thinking the update/insert/delete succeeded"
Why would you wanna do that anyway?
As it is, there is only one way to signal "success"--to return rows affected = 1. This can only be done via a single-row action command. Having another mechanism would permit developers to have more sophisticated update logic and still have a way to signal success.
It also gives your code the ability to indicate whether some other condition has caused the update to fail. There are lots of reasons other than concurrency that can make an update fail. It would be nice to be able to differentiate between these issues. It would be nice to be able to pass back an exception code to the handler to let the client deal intelligently with the exception.
I apologize for cross posting, this is just driving me crazy. I have been
beating myself up all day trying to find one good example of adArray as part
of a parameter. I went this route.
Sub Test1()
dim cn as new adodb.Connection
dim s$
s = "Provider=Microsoft.Jet.OleDb.4.0; Data Source = somefile.mdb;"
cn.Open s
dim cmd as new adodb.Command
with cmd
..ActiveConnection = cn
.CommandText = "SELECT CompanyName From [Names] WHERE NameType IN ?"
.CommandType = adCmdText
End With
Dim ax() As String
ReDim ax(1) As String
ax(0) = "Member"
ax(1) = "Exhibitor"
With prm1
.Type = 8392 'Errors right about here, I want the Array ( ax() in
this example ) to provide the parameter values for an SQL IN Clause
.Direction = adParamInput
.Size = 20
.Value = ax()
End With
cmd.Parameters.Append prm1
Dim rs As New ADODB.Recordset
rs.Open cmd, , adOpenStatic, adLockPessimistic
Debug.Print rs.RecordCount
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
I have also tried cmd.Parameters.Append cmd.CreateParameter(, adArray or
adVarChar, adParamInput, 20, az()). I get an error saying wrong type of
arguments. I have tried just about possible DataType in the Array, Also
tried adVarray + adVarChar, still doen't worh right.
Any ideas? If you see anything obvious or any suggestions to try it a
different way, please let me know. Thank you to all whom reply.
Regards,
Ah, no. You can't pass an array to an IN clause--especially not with a JET database. You can't really pass an array to any procedure in JET or SQL Server. There is no corresponding type that's an array. There are ways to address the "IN" clause issue but they require creating an ad hoc query on the fly and populating it with the arguments. This is dangerous as it can lead to SQL injection attacks. In SQL Server you can pass a delimited string and parse it with a function (returning a Table) and use that with an IN clause, but this is not an option with JET.
I suggest asking technical questions on the Microsoft newsgroups. You'll get better response time...