October 7, 2008 at 6:07 am
I have the following stored procedure that gives me results in Query Analyser but none when I try to run it in Visual Basic.
CREATE PROCEDURE usp_NextFileName
(
@TableName varchar(50),
@fieldID varchar(75)
)
AS
DECLARE
@DynSQL1 VARCHAR(4000),
@DynSQL2 VARCHAR(4000),
@fieldVal nvarchar(255),
@Prefix nvarchar(11),
@MyValue nvarchar(75)
IF @FieldID = 'TempPolicyID' OR @FieldID = '000020' OR @FieldID = 'ClaimID' OR @FieldID = 'FileNumber'
BEGIN
UPDATE syscontrol SET @fieldVal = FieldValue = FieldValue+1,@Prefix =Prefix WHERE FieldID = @fieldID
INSERT INTO TempPolicy VALUES ( @prefix, @fieldVal)
SELECT * from TempPolicy WHERE FieldValue = @FieldVal
END
ELSE
BEGIN
SELECT @DynSQL1 = 'SELECT Prefix, FieldValue FROM ' + @TableName + ' WHERE FieldID = ''' + @fieldID + ''''
EXEC (@DynSQL1)
END
GO
I'm using Ado on the VB side.
Public Function OpenRS(ByVal db As ADODB.Connection, ByVal sql As Variant)
Dim Rcds As Variant
Dim adCmdText As Variant
On Error GoTo OnOpenRSError
CloseRS
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
Set rs = db.Execute(sql)
rsOpenFlag = True
mRecordPtr = 0
mRecordCount = 0
Do Until rs.Eof
mRecordCount = mRecordCount + 1
rs.MoveNext
Loop
If mRecordCount = 0 Then
mBof = False
mEof = True
Else
mRecordPtr = 1
mBof = True
mEof = False
rs.MoveFirst
End If
Exit Function
Exit Function
I was creating a temp table, but I was getting the same results. Is there a way to get my stored procedure to give a me a record set? The code will execute the SP but gives a rs is not open error when I try to get the record set.
This whole thing came about because we are trying to eliminate the creation of duplicate records. If anyone has a better idea...
October 7, 2008 at 10:15 am
You seem to have not provided all of the VB code. For example, where are you providing the name of the stored procedure to run? Where are you setting the values of the parameters? Where are you passing the parameters to the stored procedure?
If you haven't got this code I can write you something that will work.
Regards,
Ash
October 7, 2008 at 11:07 am
Here is the vb execution code:
Option Explicit
Private rc As Variant
Private Sub cmdRun_Click()
Dim PrintPostDate As Date
Dim cAppSettings As clsAppSettings
Dim strServer As String
Dim strDatabase As String
Dim sql As String
Dim dtDailyTask As String
Dim rsWork As clsSql
Set rsWork = New clsSql
Dim strUID As String
Dim strPWD As String
Dim fso As New Scripting.FileSystemObject
Set cAppSettings = New clsAppSettings
Dim i As Integer
If optCompany(0).Value = True Then
gstrCONFIGFILE = "c:\winnt\system32\AIH.config"
ElseIf optCompany(1).Value = True Then
gstrCONFIGFILE = "c:\winnt\system32\AIM.config"
ElseIf optCompany(2).Value = True Then
gstrCONFIGFILE = "c:\winnt\system32\ATICH.config"
ElseIf optCompany(3).Value = True Then
gstrCONFIGFILE = "c:\winnt\system32\ATICM.config"
ElseIf optCompany(4).Value = True Then
gstrCONFIGFILE = "c:\winnt\system32\MUH.config"
ElseIf optCompany(5).Value = True Then
gstrCONFIGFILE = "c:\winnt\system32\MUM.config"
ElseIf optCompany(6).Value = True Then
gstrCONFIGFILE = "c:\winnt\system32\NCI.config"
ElseIf optCompany(7).Value = True Then
gstrCONFIGFILE = "c:\winnt\system32\LMC.config"
ElseIf optCompany(8).Value = True Then
gstrCONFIGFILE = "c:\winnt\system32\UNITED.config"
Else
MsgBox "Nothing here"
Exit Sub
End If
Screen.MousePointer = vbHourglass
Call GetConfigSettings(strServer, strDatabase, strUID, strPWD)
OpenDatabase
sql = txtStored.Text
For i = 0 To cboRun.List(cboRun.ListIndex)
rsWork.OpenRS db, sql
dtDailyTask = rsWork.Column("FieldValue")
Next i
rsWork.CloseRS
Set rsWork = Nothing
Screen.MousePointer = vbDefault
End Sub
The sub is a bit long since I want to test on 7 different databases. I know the vb code works with the else section. Below is the original stored procedure that I started tweaking . It works fine in the code.
Here an example of the value: EXEC usp_NextFileName2 'syscontrol', '000020'
CREATE PROCEDURE usp_NextFileName
(
@TableName varchar(50),
@fieldID varchar(75)
)
AS
DECLARE
@DynSQL1 VARCHAR(4000),
@DynSQL2 VARCHAR(4000)
SELECT @DynSQL1 = 'SELECT Prefix, FieldValue FROM ' + @TableName + ' WHERE FieldID = ''' + @fieldID + ''''
SELECT @DynSQL2 = 'UPDATE ' + @TableName + ' SET FieldValue = FieldValue+1 WHERE FieldID = ''' + @fieldID + ''''
EXEC (@DynSQL1)
IF @FieldID = 'TempPolicyID' OR @FieldID = '000020' OR @FieldID = 'ClaimID' OR @FieldID = 'FileNumber'
BEGIN
EXEC (@DynSQL2)
END
GO
October 7, 2008 at 12:06 pm
I figured it out. I had to add
SET NOCOUNT ON
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply