There are no rows in the current fetch buffer

[canadatct]

I am trying to update a SQL 2005 (express) table using an ADO recordset. I can't seem to nail down the exact problem here, and the error message is not overly helpful. It's the first time I've ever seen this error.

"Microsoft OLE DB Provider for SQL Server (0x80004005)
There are no rows in the current fetch buffer."

I'm receiving the error when it gets to the ".update" line. Strangely, the code executes fine one time, then the next, it fails and I get the error message.

Any ideas? Thx in advance for the help.

 

[wvdba]

what's the code looks like?

 

[canadatct]

Here is the code that updates the db.

if request.querystring("type") = "new" then
rsSave.open "Table", objConn,2,2,2
else
strSQL = "Select * from Table where FieldID=" & cint(request.querystring("fID"))
rsSave.open strSQL, objConn,2,2,1
end if
with rsSave
if request.querystring("type") = "new" then
.addnew
end if
.fields("Field1").value = trim(request.form("field1"))
.fields("Field2").value = trim(request.form("field2"))
.fields("Field3").value = trim(request.form("field3"))
.fields("Field4").value = trim(request.form("field4"))
.fields("Field5").value = trim(request.form("field5"))
.fields("Field6").value = trim(request.form("field6"))
.fields("Field7").value = trim(request.form("field7"))
.fields("Field8").value = trim(request.form("field8"))
.fields("Field9").value = trim(request.form("field9"))
.fields("Field10").value = trim(request.form("field10"))
.fields("LastUpdated").value = now
.update
end with

rsSave.close
set rsSave = nothing

 

[gmmastros]

That is a terribly inefficient way to handle your database. Seriously bad.

You see... when you add a new row to your table, you are essentially pulling ALL of the data from the table in to ASP, then adding a new row to it. If there are only a couple rows in your table, then it is probably fast, but as your table grows, this process will continue to slow down. Eventually, people will start complaining about performance.

The best way to handle this situation is to issue database commands directly (instead of that nasty field updating business).

Let me give you a nudge in the right direction.

if request.querystring("type") = "new" then
    strSQL  = "Insert Into Table(Col1, Col2, Col3) Values('" & Request.Form("Field1") & "','" & Request.Form("Field2") & "','" & Request.Form("Field3") & "')"

    objConn.Execute strSQL
else
    strSQL = "Update Table Set Col1 = '" & Request.Form("Field1") & "', Col2 = '" & Request.Form("Field2") & "', Col3 = '" & Request.Form("Field3") & "' where FieldID=" & cint(request.querystring("fID"))
    objConn.Execute strSQL
end if

This way, you don't need to retrieve anything from the database. There's just one DB call.

I encourage you to get this functionality working using the method I describe above. Then, immediately afterwards, do some research on [google]SQL Injection[/google].

In an ideal world, you should be writing stored procedures in your database. Then, on the ASP side, use command objects to interact with the database. This method is a bit more involved because it requires more code, but it is a lot faster and much more secure. This requires a reasonable effort on your part, but it is well worth it.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[canadatct]

I'm currently in the process of building a prototype, and I felt this was the easiest method to get things working for now. I'm fully aware that this is not the BEST method of working with a db, and I know enough about SQL Injections to properly plan for that. And George, I can assure you that my terribly inefficient way of going about this will be avoided when my app goes into production.

What you've done is tell me information I'm already aware of, and completely avoided my question/problem.

 

[gmmastros]

What you've done is tell me information I'm already aware of, and completely avoided my question/problem.

First, I apologize if I offended you in any way. That was not my intent.

Second, please realize that there is no possible way that I could have known that this is information that you are already aware of. How could I?

Third, I do not interpret my previous response as completely avoiding your question/problem. Quite the contrary. What I have done is to suggest an alternative method for accomplishing the same results.

Truth is, I have never used the method in which you are describing. I do have extensive knowledge of databases (Microsoft SQL Server in particular) and adequate knowledge/experience with ASP. Perhaps it would have been better if I had never posted any advice in this thread. However, I do believe that the advice I have given you is probably going to be the quickest way to resolve your problem. The time it takes to resolve your problem is even more relevant since you are prototyping an application.

One last comment. I see from your error message that you are using an OLE DB Provider for your connection. I would encourage you to modify your connection object so that it uses the SQL Native provider.

http://connectionstrings.com/?carrier=sqlserver2005

Scroll down to... SQL Native Client OLE DB Provider

Lastly, if you would prefer for me to keep my advice to myself, just say so and I promise not to reply to your question again.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I would also suggest that you check your table. Are there any triggers on this table? Do any of those triggers use a cursor? If so, I would suggest that you concentrate your efforts there.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom