| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
10-09-03, 10:27
|
|
Registered User
|
|
Join Date: Oct 2003
Location: South Africa
Posts: 5
|
|
|
Recordset update error
|
|
Hi
I'm stuck with someone else's code that is not working and the person has already left the company so I have to sort it out.
When trying to insert or update a record(SQL Server2000) I get the following error:
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype
No I know that it's better to use a query or even better a StoredProc but time is of the essense and I have to sort this out ASAP.
The strange thing is that it works on our DEV server running Win2000 with service pack 4, but NOT on the LIVE server running the same.
I've also installed the latest MDAC on the server but it didn't make a difference.
Your help would be MUCH appreciated!! 
|
|
10-09-03, 16:22
|
|
Useless...
|
|
Join Date: Jul 2003
Location: SoCal
Posts: 721
|
|
It would help to see some code. You could also try making it a client-side recordset:
Code:
objRS.CursorLocation = adUseClient
objRS.Open <SQL>, <Connection Object>, adOpenKeySet, adLockOptimistic, adCmdText
The values for the constants are (incase you don't have the VB constants):
adUseClient = 3
adOpenKeySet = 1
adLockOptimisitc = 3
adCmdText = 1
__________________
That which does not kill me postpones the inevitable.
|
|
10-10-03, 01:07
|
|
Registered User
|
|
Join Date: Oct 2003
Location: Pune
Posts: 59
|
|
|
|
Use adopendynamic. that will solve ur problem surely.
and it would be better if u can share that piece of code.
cyrus
|
|
10-10-03, 04:21
|
|
Registered User
|
|
Join Date: Oct 2003
Location: South Africa
Posts: 5
|
|
|
Code
Thanx for the responses.
Sorry about that, here's the code:
<%
Dim SelectClause
Dim WhereClause
Dim OrderClause
Dim AutoNum
Dim FilterFirst
SelectClause = "SELECT [AdminID], [menuID], [name], [description], [url], [newwin], [imgpath], [Active] FROM [MenuLinks]"
WhereClause = ""
If Session("CurrentSection") <> "" Then
If WhereClause = "" Then
WhereClause = "WHERE "
Else
WhereClause = WhereClause & " AND "
End If
WhereClause = WhereClause & "MenuID LIKE (SELECT TOP 1 menuID FROM MenuLinks WHERE Name LIKE '" & Session("CurrentSection") & "%') + '.%'"
End IF
OrderClause = "ORDER BY menuID"
AutoNum = "AdminID"
FilterFirst = True
recordID = Request("RecordID")
If recordID & "" = "" Then recordID = Request("navID")
Dim MyCon
Dim RS, strSQL
Set MyCon = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")
MyCon.Open application("ConnectionString")
IF NOT Request.QueryString("Mode") = "AddNew" Then
'Description string for Security Log table
strDescription = "Edit Menu Item: " & Request("name") & " (" & Request("menuid") & ")"
IF WhereClause & "" = "" Then
WhereClause = "WHERE convert(varchar," & AutoNum & ") = '" & recordID & "'"
Else
WhereClause = WhereClause & " AND convert(varchar," & AutoNum & ") = '" & recordID & "'"
End If
Else
'Description string for Security Log table
strDescription = "Add Menu Item: " & Request("name")
End If
strSQL = SelectClause & " " & WhereClause & " " & OrderClause
'response.Write
RS.Open strSQL, MyCon, 1, 3
If Request.QueryString("Mode") = "AddNew" Then RS.AddNew
'Change Each field to request.form values
' On error resume next
dim item
For each item in Request.Form
'response.Write(Request.Form(item))
If Left(item,3) <> "cmd" Then
Response.Write RS(item) & " = " & Request.Form(item) & "<br>"
If Request.Form(item) & "" = "" Then
RS(item) = Null
Else
RS(item) = Request.Form(item)
End if
End If
Next
IF Err.number = 0 Then RS.Update
RS.Close
Set RS = nothing
MyCon.Close
Set MyCon = nothing
If err.number > 0 then
%>
|
|
10-10-03, 11:56
|
|
Useless...
|
|
Join Date: Jul 2003
Location: SoCal
Posts: 721
|
|
|
Re: Code
Quote:
Originally posted by johanp
MyCon.Open application("ConnectionString")
|
Can we see "ConnectionString"? Remove the identifying information of course (db name, server name, user ID, password), but we need to see what type of Provider you're using...
__________________
That which does not kill me postpones the inevitable.
|
|
10-13-03, 04:33
|
|
Registered User
|
|
Join Date: Oct 2003
Location: South Africa
Posts: 5
|
|
|
Re: Code
Quote:
Originally posted by Seppuku
Can we see "ConnectionString"? Remove the identifying information of course (db name, server name, user ID, password), but we need to see what type of Provider you're using...
|
Looks like they're using this:
"DSN=DBdsn;UID=Username ;PWD=Password"
|
|
10-13-03, 04:57
|
|
Registered User
|
|
Join Date: Oct 2003
Location: Pune
Posts: 59
|
|
Replace the line
RS.Open strSQL, MyCon, 1, 3
with
RS.Open strSQL, MyCon, 2, 3
i.e 2 - dynamic
cyrus
|
|
10-13-03, 05:03
|
|
Registered User
|
|
Join Date: Oct 2003
Location: South Africa
Posts: 5
|
|
Quote:
Originally posted by cyrus
Replace the line
RS.Open strSQL, MyCon, 1, 3
with
RS.Open strSQL, MyCon, 2, 3
i.e 2 - dynamic
cyrus
|
Thanx! I'll give it a try and let U know if it worked.
|
|
10-13-03, 11:47
|
|
Useless...
|
|
Join Date: Jul 2003
Location: SoCal
Posts: 721
|
|
|
Re: Code
Quote:
Originally posted by johanp
Looks like they're using this:
"DSN=DBdsn;UID=Username ;PWD=Password"
|
Where'd you see that?.. unless my "Find" isn't working, I saw no actual connection string...
__________________
That which does not kill me postpones the inevitable.
|
|
10-13-03, 11:54
|
|
Useless...
|
|
Join Date: Jul 2003
Location: SoCal
Posts: 721
|
|
Quote:
Originally posted by cyrus
Replace the line
RS.Open strSQL, MyCon, 1, 3
with
RS.Open strSQL, MyCon, 2, 3
i.e 2 - dynamic
cyrus
|
KeySet and Dynamic are virtually the same thing, with one difference... new records are not immediately visible in KeySet recordsets, where they are in Dynamic.. the draw back is, that added functionality decreases its performance
Quote:
Using Active Servicer Pages SE (Que)
"The KeySet cursor is a read-write cursor type that can move throughout a recordset. Its largest limitation is its inability to see the additional records that are created. You still see changes and deletions, but additional records are a no-no.
.
.
.
The dynamic cursor is the heavyweight of cursor types. It is the most powerful. But this power comes at the cost of slower performance. Basically, use the dynamic cursor when no other cursor type will work.
.
.
.
For instance, you may have a recordset use this cursor when moving through a recordset based on all the employees in a table with the last name "Smith." If another recordset adds a new Smith before you are done, you will be able to see this new record and display it also."
|
__________________
That which does not kill me postpones the inevitable.
|
|
10-14-03, 00:09
|
|
Registered User
|
|
Join Date: Oct 2003
Location: Pune
Posts: 59
|
|
sepakku I am aware of that but once a similar problem was faced And the soln was the same.
Any way it was just a try and not a perfect soln
cyrus
|
|
10-14-03, 00:39
|
|
Useless...
|
|
Join Date: Jul 2003
Location: SoCal
Posts: 721
|
|
I wasn't attempting to prove you wrong, but instead, explain the difference between the two... no more no less...
__________________
That which does not kill me postpones the inevitable.
|
|
10-14-03, 00:45
|
|
Registered User
|
|
Join Date: Oct 2003
Location: Pune
Posts: 59
|
|
it ok SIR.
I just wanted to tell u I am aware of it
Sorry if u feeled bad
Cyrus
|
|
11-24-03, 02:32
|
|
Registered User
|
|
Join Date: Oct 2003
Location: South Africa
Posts: 5
|
|
Thanx for the advice guys!
I changed the connection string from DSN to OleDB and it worked 
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Recordset update error
