vbscript Recordset Not Updating against a sql server connection

outlook 2010 vbscript  custom form

In the following i'm getting an error about updating the recordset.

Current recordset does not support updating. This could be a limitationof the provider. or the selected locktype

Const adUseClient = 3

Sub Item_Open()

   ' Sets the name of page on the form (P.2)
   Set FormPage = Item.GetInspector.ModifiedFormPages("Survey")

       Item.GetInspector.SetCurrentFormPage "Survey"

   ' Sets Control to a list box called Comboox1.
   Set Control = FormPage.Controls("Combobox1")

   ' Assign values to the list box.
   Control.PossibleValues = "Price;Proposal;Unable to Cross Items or Match Enough Items;Operational Capabilities;Other"

   


 End Sub

Sub CommandButton2_Click()
  msgbox "Hello World"


Dim cnn 
   Set cnn = CreateObject("ADODB.Connection")
Dim rs1
Dim connectstring
Dim tp

Dim i  ' ID
Dim s  ' Seller Name
Dim e  ' Email Status
Dim f  ' Feedback
Dim c  ' Why Did Not Take Proposal
Dim y  ' Yes they did
Dim n  ' No They Did Not

Set objPage = Item.GetInspector.ModifiedFormPages("Survey")
Set objControla = objPage.Controls("txtProposalId") ' id
Set objControlb = objPage.Controls("txtSname")  ' specialist name
Set objControlc = objPage.Controls("txtEstatus")  ' email status
Set objControld = objPage.Controls("txtFeedback")  ' feeback memo
Set objControle = objPage.Controls("ComboBox1")  '  why did not take proposal 
Set objControlf = objPage.Controls("Optionbutton1")  '  yes take proposal
Set objControlg = objPage.Controls("OptionButton2")  '  no did not take proposal


' check to make sure they made any selection on win or lose options
If  objControlf.Value =  False and objControlg.Value =  False Then
Msgbox "Please Mark Did You Win Or Lose The Bid."
Exit Sub
end if


' OptionButton1  =  Yes they won the Proposal
If  objControlf.Value =  true then
msgbox "yes true"
y = "YES"
n = ""
end if
' OptionButton2  =  No they did NOT win  the Proposal
If  objControlg.Value =  true then
msgbox "No"
n = "NO"
y = ""
End If

' ComboBox1 Why Did they Not Get Proposal
c = objControle.Value
if n = "NO" AND  c = "Please Make A Selection" Then
Msgbox "Please Give A Reason To Not Winning The Proposal"
Exit Sub
End If

' feedback Value
f = objControld.Value
' Email Status
e = objControlc.Value
' Specialist Name
s =  objControlb.Value
' Project Id
i = objControla.Value






'i = objControle.Value

msgbox "hello '" & i & "' world"

i = "SLS-000143-PROP"



   ' Open a Connection using an ODBC DSN named "WORKFLOW".
cnn.CursorLocation = adUseClient
'cnn.CursorLocation = adOpenKeyset  
'cnn.CursorLocation = adOpenDynamic ' adUseClient






cnn.open "PROVIDER=SQLOLEDB;DATA SOURCE=xx.bbb.com;UID=xxxxuser;PWD=xxxprd;DATABASE=workflow"

' working 11-12-2015
sQuery =  "SELECT proposal_id, specialist_responsible, customer_name, date_proposal_completed, seller_racf_id, " & _ 
"seller_email, wl_emailed_on, wl_survey_completed_on, wl_status, wl_Q1_answer, " & _
"wl_Q2_answer, wl_Q3_answer, wl_Feedback, wl_ReasonLoss " & _ 
"FROM temp_proposal_survey_results " & _
"WHERE proposal_id = '" &  i & "' " 




Set rs1 = CreateObject("ADODB.Recordset")
rs1.open sQuery, cnn
rs1.MoveFirst
tp =  rs1.recordcount


with rs1
 .fields(12) = f ' feedback entry
 .fields(9) = y  ' option check Yes
 .fields(9) = n  ' option check No
 .fields(13) = c ' Dropdown value for Reason did not get proposal
 .fields(7) = now()
 .fields(8) = e
end with

rs1.Update

'If tp = 1 Then
'MsgBox "One Record"
'End if



   ' Close the connection.
   cnn.Close
Set cnn = nothing

End Sub


    



    

Open in new window

LVL 3
FordraidersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Najam UddinCommented:
try
rsi.Open sQuery, cnn, adOpenStatic, adLockOptimistic

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
najam, sorry same error.

this line also causing a problem ?
cnn.CursorLocation = adUseClient
FordraidersAuthor Commented:
si.Open sQuery, cnn, 1,2,1  works
FordraidersAuthor Commented:
Solution resolved by.

rsi.Open sQuery, cnn, 1,2,1  

works
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.