rs updation causing probs

[udit99]

I need to update my recordset with records drawn from the textboxes with code lik

rsEmployeeInfo!FirstName = txtFName.Text
rsEmployeeInfo!MiddleInitial = txtMInitial.Text
rsEmployeeInfo.Update


but this is giving me an error :


Run time error 3251:

Operation requested by application is not supported by Provider


So do I hv 2 change some recordset properties 2 get about it. ??

[mdake]

You have probably opened a Readonly Recordset.
Try changing the LockType either in the Recordset.Open method or by setting the Recordset.LockType Property.
Try setting it to adLockOptimistic

[Serge]

How are you opening your recoodset ?

[udit99]

well, the problem probably has to do with the lock type...but
I just can't get it to change, see if u guys can help....here's the connection code.




With cmd1
.ActiveConnection = OpenConnection
.CommandType = adCmdStoredProc
.CommandText = "s_EmployeeDetails"

rsEmployeeInfo.LockType = adLockOptimistic

Set rsEmployeeInfo = cmd1.Execute

End With


where OpenConnection is a function defined in the module as


Public Function OpenConnection() As ADODB.Connection

Dim cnConnect As ADODB.Connection

'Initialize the connection object
Set cnConnect = New ADODB.Connection

cnConnect.CursorLocation = adUseClient

cnConnect.Open sconnstring, "SA", ""

Set OpenConnection = cnConnect

End Function




where sconnstring is a global variable defined in the function


Public Function GetConnectionString()

sconnstring = "Provider=SQLOLEDB.1;User ID=" & username & ";password=" & password & ";Initial Catalog=practice;Data Source = " & servername & ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"

End Function



so where can I specify the locktype `coz when I do it b4 the rs=cmd.execute , the cmd.execute overrides it and resets it to the original locktype and when I try to do it after it, it givz an error msg that can't perform operation when object is alredy open.

[mdake]

Using the Command Object to open a recordset returns a readonly recordset. You need to use the Recordset.Open to get an updateable recordset.

Heres and example

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb"
 
rs.Open "SELECT * Titles", cn, adOpenKeyset, adLockOptimistic

[udit99]

well due to some company standard limitations imposed on me , I need to use the command object fr the execution and no other way. so I'm trying to figure how to specify the LockType using the command object method of populating the recordset.Give it a try,`coz I think it should be possible .

[mdake]

If you have to use the Command Object and you specify a LockType the Command Object will change it adLockReadOnly, if it is not already.

So, if you have to update records, but, don't have a Recordset Object then use SQL to do the update through the Command.Execute, something like this...

Dim SQL As String
SQL = "UPDATE Table1 SET Field1 = '" & Text1.Text &"'"

cmd.Execute SQL

[udit99]

Just thinking....is there any way that I can use an Update Stored Procedure to update the records in the database by taking records from the textboxes.

[mdake]

Sure, you can create a stored procedure to update your record. You will have to pass in some way to tell which record and the new value you want.
Then you can call the stored procedure from VB.

[udit99]

Well there's a new prob now.The Update stmt. is not giving an error but the updation is not taking effect either. The same SQL stmt when run in Query Analyzer is working fine.


this is the saving code I'm using


tempsql = "UPDATE EmployeeInfo SET FirstName = '" & txtFName.Text & "'" & "where ClockNumber= '" & txtCnum.Text & "'"
cmd1.ActiveConnection = OpenConnection
cmd1.Execute tempsql


(the code fr OpenConnection is given in an earlier posting )

[Serge]

You can actually use the same command object to open your rs


rs.Open CommandObject, , adOpenKeySet, adLockOptimistic

Notice, the second paramter is ommited? It's becase you now rely on the activeconnection of the command.


Regards,

[udit99]

and whats the problem with this Stored Proc. stmt.--> It givz a syntax error




CREATE PROCEDURE testsave AS

"Update EmployeeInfo Set FirstName='usu' where ClockNumber='74001' "

[mdake]

Are you talking about using a stored procedure or just using SQL action with the Command.Execute?

this is the saving code I'm using

tempsql = "UPDATE EmployeeInfo SET FirstName = '" & txtFName.Text & "'" & "where ClockNumber= '" & txtCnum.Text & "'"
cmd1.ActiveConnection = OpenConnection
cmd1.Execute tempsql

Make sure you set the cmd.CommandType and the cmd.CommandText, then execute. Here is an example...

Dim tempsql As String
tempsql = "UPDATE T_BATCH SET ScanOperator = '" & txtScanOp.Text & "'" & "where BatchNo= '" & txtBNum.Text & "'"
cmd.CommandType = adCmdText
cmd.CommandText = tempsql
cmd.Execute

[vb_dba]

Remove the double quotes:

CREATE PROCEDURE testsave AS 

Update EmployeeInfo 
Set FirstName='usu' 
where ClockNumber='74001'

[udit99]

Yup , the double quotes wuz the problem wid the stored proc- And mdake, I was actually using a Stored Procedure. But now I don't need 2 use it `coz the code tt Serge posted was perfect fr the requirements.-It changes the locktype 2 Optimistic while using the command object fr the recordset population.

BTW . how do u deal with an error while saving a blank date field in the SQL Database . I tried appending a "" to it but tt didn't help.

rsEmployeeInfo!TrainingStartDate = " " & txtTSDate.Text


TrainingStartDate is a SmallDateTime field

[mdake]

udit99,
The code Serge posted reinforced what I told you from the beginning. If you want an updateable recordset you need to open one..

rs.Open blah, blah

What you were doing was returning a rs from a Command.Execute but trying to make it not read only. I told you you can not do that and to make the rs updateable you would need to open one secifically. You said your company standard limitations want let you. Now you do as I said all along, maybe I did not make myself clear, but, hopefully you have learned.

regrards,

[udit99]

Well, sorry actually `coz I missed yr point in that earlier post. It was actually bcoz I needed 2 use the command object accoding to standards and yr code wasn't using the command object--> But it never occurred 2 me tt I cd straightaway use the Command object in the rs.Open stmt. It was only when i read Serge's post tt I realized tt.Nevertheless I did learn sumthing frm you.
thnx

[mdake]

Yes I did use the Connection Object, that was confusing, but the result is the same when returning a recordset.

The rule of thumb I use is if I just want to read some records to fill a list or dictionary or something, knowing that I will not be updating them, then I usually get a recordset from my connection or command, whichever I have open.

If I need a recordset that I know I will be saving updates back to the DB then I get one using rs.Open

Been nice conversing with you.

regards,

[udit99]

Originally posted by mdake

Been nice conversing with you.

regards, [/B]

Yup, same here .Iv saved the entire thread on my desktop fr future use.