Call Stored Proc from VB that will allow updating to tables

I get a runtime error 3251 Current Recordset does not support updating.  This may be a limitation of the provider or of the selected locktype.

The following is the code I am using to call the stored proc.:

Dim rstProgram As ADODB.Recordset
Dim m_cmd As New ADODB.Command

Set rstProgram = New ADODB.Recordset

With m_cmd
    .ActiveConnection = conSQL
    .CommandType = adCmdStoredProc
    .CommandText = "sp_AllProgram"       ' Stored Procedure name.
End With

Set rstProgram = m_cmd.Execute
rstProgram.MoveFirst

 With rstProgram
        .AddNew                                'HERE IS WHERE I GET THE ERROR
          ......
          ......


What am i doing wrong?
         
JoannieJeffersonAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
2. No

3. You have to write INSERT INTO sql queries
0
 
fds_fatboyConnect With a Mentor Commented:
You cannot update a sp like this. This is a Server Side Cursor. Instead of a SP use a View.
0
 
fds_fatboyCommented:
PS:

Server side cursors i.e. dynasets should not in general be used in multiuser environments. In fact every single DBA I have ever met would disembowel me for unleashing one on their database.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
JoannieJeffersonAuthor Commented:
Instead of a SP use a View.

I am not familiar with views...How would I set up a view, call it from vb and update records?
0
 
fds_fatboyCommented:
Wat dbms are you using?
0
 
JoannieJeffersonAuthor Commented:
I am using SQL Server 2000 with Visual Basic 6.0
0
 
fds_fatboyCommented:
Have I misunderstood what you are trying to acheive?

Are you  trying to add new rows into the database, or just add extra records into your recordset?
0
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
Instead of using the AddNew method of the Recordset, you could use the "INSERT INTO TableName..." syntax to insert data into the databases. These statements can be executed using YourConnnection.Execute
0
 
JoannieJeffersonAuthor Commented:
Does anyone know how to call a view from sql server 2000 from vb 6?
0
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
rs.open "select * from ViewName",YourConnection
0
 
anvConnect With a Mentor Commented:
hi JoannieJefferson

u cannto do what u r trying to achiev using a Stored Procu\dure in this fashion..

if u want to use the stored procedure to insert data into the table for that u need to

pass parameters to the SP which will be the values u want to insert into the database..

for eg.

'here is ur procedure u created in SQL server..

Create Proc abc1(p1,p2,p3)
  insert into table t1 values(p1,p2,p3)
end proc

here is ur code in VB...

str1 = "abc1('" & Item1 & "','" & Item2 & "',"' & Item3 & "'")

conSQL.Execute str1
0
 
niranjanrampureCommented:
what are u trying to do with .addnew
as said fds_fatboy  u cant insert data into a table without passing parameters to sp.
also insert statement is better than recordse.addnew.pls explain u r probleum in detail
0
 
JoannieJeffersonAuthor Commented:
Here is a sample procedure:

CREATE PROCEDURE sp_Report  @ProgramID nchar(20), @dtStartDate datetime , @dtEndDate datetime
AS

SELECT T1.x,T2.z,T2.y
FROM TABLE1
INNER JOIN TABLE2
ON T1.TABLE1ID = T2.TABLE2ID
WHERE (((T1.TABLE1.ID)=@ProgramId)
AND ((T2.TABLE2Date)> @StartDate)
AND ((T2.TABLE2Date)< @EndDate)
ORDER BY TABLE1.CardHolderLastName
GO


1. I want to know how to call this procedure (sp_Report) from VB and pass the parameters to this procedure.
2. Can I use the recordset to update or add new records to the table?  If so how?
3. What is the best way for me to achieve what i am trying to do using store procedures?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.