Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

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?
         
0
JoannieJefferson
Asked:
JoannieJefferson
  • 4
  • 4
  • 3
  • +2
5 Solutions
 
fds_fatboyCommented:
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 MoreauSenior .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 MoreauSenior .Net ConsultantCommented:
rs.open "select * from ViewName",YourConnection
0
 
anvCommented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
2. No

3. You have to write INSERT INTO sql queries
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now