Printable View
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. ??
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
How are you opening your recoodset ?
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.
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
VB Code:
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 .
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
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.
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.
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 )
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,
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' "
Are you talking about using a stored procedure or just using SQL action with the Command.Execute?
Make sure you set the cmd.CommandType and the cmd.CommandText, then execute. Here is an example...Quote:
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
VB Code:
Remove the double quotes:
Code:CREATE PROCEDURE testsave AS
Update EmployeeInfo
Set FirstName='usu'
where ClockNumber='74001'
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
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,
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
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,
Yup, same here .Iv saved the entire thread on my desktop fr future use.Quote:
Originally posted by mdake
Been nice conversing with you.
regards, [/B]