pmud
asked on
How to call SQL stored procedure from VB 6.0 code?
Hi,
I am trying to use a sql stored procedure instead of a query from my VB 6.0 code. I am passing parameters. Basically the stored procedure checks if a given record or set of records exists in the db. Below is my code:
Private Function AlreadyUploaded()
Dim cn As ADODB.Connection
Dim rsCheck As ADODB.Recordset
Dim cmd As ADODB.Command
Dim rsRecCount As Integer ' For counting records in a record set
Set cn = New ADODB.Connection
cn.ConnectionString = strConnectionString
cn.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_ImgAlreadyUploaded"
' When the debugger comes to the line below it jumps out of this function. I think something is wrong here
cmd.Parameters.Append cmd.CreateParameter("@strS tudy_Date" , adVarChar, adParamInput, , " & strStudyDate & ")
cmd.Parameters.Append cmd.CreateParameter("@strS tudyTime", adVarChar, adParamInput, , " & strStudyTime & ")
cmd.Parameters.Append cmd.CreateParameter("@dteB irthDate ", adDBTime, adParamInput, , " & dteBirthDate & ")
cmd.Parameters.Append cmd.CreateParameter("@strP atientFNam e ", adVarChar, adParamInput, 50, " & LTrim$(arrPatientName(0) & ")
cmd.Parameters.Append cmd.CreateParameter("@strP atientLNam e", adVarChar, adParamInput, 50, " & LTrim$(arrPatientName(1) & ")
rsCheck = cmd.Execute
rsRecCount = rsCheck.RecordCount
If rsRecCount > 0 Then
'If images have already been uploaded, then go to the next sub Directory in the chosen drive
'MsgBox "Images for him have ALREADY BEEN uploaded."
intStudiesEarlierUploaded = intStudiesEarlierUploaded + 1
n = m + 1
RepeatLoop
ElseIf rsRecCount = 0 Then ' If not uploaded already, then get the matching records from the Db.
CheckInDatabase
End If
'Close the connection and recordset object.
rsCheck.Close
Set rsCheck = Nothing
cn.Close
Set cn = Nothing
End Function
The code is not executing properly, it jumps out of the function after coming to the first statement in the parameters list. This is the first time I am using stored procedure with VB . So there might be some stupid mistakes here. Also, I need to implement transactions in my stored procedures. So that if there is any error, the transction can be rolled back. how can i implement transcations in my stored procedure below?
CREATE PROCEDURE [dbo].[sp_ImgAlreadyUpload ed]
(
@strStudyDate varchar(20),
@strStudyTime varchar(20),
@dteBirthDate datetime,
@PatientFName varchar(50),
@PatientLName varchar(50)
)
AS
Select * from studies s inner join patient p on p.id=s.patient_idnum
WHERE study_date=@strStudyDate + ' ' +@strStudyTime and
p.patient_dob =@dteBirthDate AND
( ( p.patient_first = @PatientFName AND p.patient_last = @PatientLName)
OR
(p.patient_last =@PatientFName and p.patient_first=@PatientLN ame ) )
Go
Thanks
I am trying to use a sql stored procedure instead of a query from my VB 6.0 code. I am passing parameters. Basically the stored procedure checks if a given record or set of records exists in the db. Below is my code:
Private Function AlreadyUploaded()
Dim cn As ADODB.Connection
Dim rsCheck As ADODB.Recordset
Dim cmd As ADODB.Command
Dim rsRecCount As Integer ' For counting records in a record set
Set cn = New ADODB.Connection
cn.ConnectionString = strConnectionString
cn.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_ImgAlreadyUploaded"
' When the debugger comes to the line below it jumps out of this function. I think something is wrong here
cmd.Parameters.Append cmd.CreateParameter("@strS
cmd.Parameters.Append cmd.CreateParameter("@strS
cmd.Parameters.Append cmd.CreateParameter("@dteB
cmd.Parameters.Append cmd.CreateParameter("@strP
cmd.Parameters.Append cmd.CreateParameter("@strP
rsCheck = cmd.Execute
rsRecCount = rsCheck.RecordCount
If rsRecCount > 0 Then
'If images have already been uploaded, then go to the next sub Directory in the chosen drive
'MsgBox "Images for him have ALREADY BEEN uploaded."
intStudiesEarlierUploaded = intStudiesEarlierUploaded + 1
n = m + 1
RepeatLoop
ElseIf rsRecCount = 0 Then ' If not uploaded already, then get the matching records from the Db.
CheckInDatabase
End If
'Close the connection and recordset object.
rsCheck.Close
Set rsCheck = Nothing
cn.Close
Set cn = Nothing
End Function
The code is not executing properly, it jumps out of the function after coming to the first statement in the parameters list. This is the first time I am using stored procedure with VB . So there might be some stupid mistakes here. Also, I need to implement transactions in my stored procedures. So that if there is any error, the transction can be rolled back. how can i implement transcations in my stored procedure below?
CREATE PROCEDURE [dbo].[sp_ImgAlreadyUpload
(
@strStudyDate varchar(20),
@strStudyTime varchar(20),
@dteBirthDate datetime,
@PatientFName varchar(50),
@PatientLName varchar(50)
)
AS
Select * from studies s inner join patient p on p.id=s.patient_idnum
WHERE study_date=@strStudyDate + ' ' +@strStudyTime and
p.patient_dob =@dteBirthDate AND
( ( p.patient_first = @PatientFName AND p.patient_last = @PatientLName)
OR
(p.patient_last =@PatientFName and p.patient_first=@PatientLN
Go
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
Thats helpful. But now when I come to the execute statement, I get Sorry for the incovenience and the visual studio closes on me! I think something is wrong with the execute line or my SP:
I modified my execute statement as
cmd.Execute rsCheck, , adExecuteNoRecords
my stored procedure is:
CREATE PROCEDURE [dbo].[sp_ImgAlreadyUpload ed]
(
@strStudy_Date varchar(20),
@strStudyTime varchar(20),
@dteBirthDate datetime,
@strPatientFName varchar(50),
@strPatientLName varchar(50)
)
AS
Select * from studies s inner join patient p on p.id=s.patient_idnum
WHERE study_date=@strStudy_Date + ' ' +@strStudyTime and
p.patient_dob =@dteBirthDate AND
( ( p.patient_first = @strPatientFName AND p.patient_last = @strPatientLName)
OR
(p.patient_last =@strPatientFName and p.patient_first=@strPatien tLName ) )
GO
Thanks
Thats helpful. But now when I come to the execute statement, I get Sorry for the incovenience and the visual studio closes on me! I think something is wrong with the execute line or my SP:
I modified my execute statement as
cmd.Execute rsCheck, , adExecuteNoRecords
my stored procedure is:
CREATE PROCEDURE [dbo].[sp_ImgAlreadyUpload
(
@strStudy_Date varchar(20),
@strStudyTime varchar(20),
@dteBirthDate datetime,
@strPatientFName varchar(50),
@strPatientLName varchar(50)
)
AS
Select * from studies s inner join patient p on p.id=s.patient_idnum
WHERE study_date=@strStudy_Date + ' ' +@strStudyTime and
p.patient_dob =@dteBirthDate AND
( ( p.patient_first = @strPatientFName AND p.patient_last = @strPatientLName)
OR
(p.patient_last =@strPatientFName and p.patient_first=@strPatien
GO
Thanks
> cmd.Execute rsCheck, , adExecuteNoRecords
should be:
cmd.Execute rsCheck
as you actually WANT the records back
should be:
cmd.Execute rsCheck
as you actually WANT the records back
ASKER
Hi angel III,
I tried the statement you mentioned above and when the debugger reaches that line and I hit f5, Visual Studio again closes on me. So i tried to exceute like:
cmd.Execute and then the debugger passed to the next statement. So i think, the problem is with this statement only. I need to get all the records in the rsCheck recordSet.
Thanks
I tried the statement you mentioned above and when the debugger reaches that line and I hit f5, Visual Studio again closes on me. So i tried to exceute like:
cmd.Execute and then the debugger passed to the next statement. So i think, the problem is with this statement only. I need to get all the records in the rsCheck recordSet.
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
The code below works. But when I use the stmt, rsCheck.RecordCount after ths last statemnt in the code below, the debugger doesnt rech that line and exits the function.
set rsCheck = new adodb.recodset
set rsCheck.ActiveConnection = cn
rsCheck.open cmd
As acperkins mentioned that using the RecordCount is not a good idea so I tried to calcuate the no. of records in teh stored procedure itself and tried to use the return value from the SP in my code. But gaian the debugger doent rech that line where I use the return value. SOmething is wrong I think with the synatx: Below is my SP:
CREATE PROCEDURE [dbo].[sp_ImgAlreadyUpload ed]
@NoOfMatchedRecords integer output,
@strStudy_Date varchar(20),
@strStudyTime varchar(20),
@dteBirthDate datetime,
@strPatientFName varchar(50),
@strPatientLName varchar(50),
AS
Select @NoOfMatchedRecords= count( *) from studies s inner join patient p on p.id=s.patient_idnum
WHERE study_date=@strStudy_Date + ' ' +@strStudyTime and
p.patient_dob = @dteBirthDate AND
( ( p.patient_first = @strPatientFName AND p.patient_last = @strPatientLName)
OR
(p.patient_last =@strPatientFName and p.patient_first=@strPatien tLName ) )
RETURN @NoOfMatchedRecords
GO
VB Code:
Dim NoOfMatchedRecords As Integer
Dim cn As adodb.Connection
Dim rsCheck As adodb.Recordset
Dim cmd As adodb.Command
Set cn = New adodb.Connection
cn.ConnectionString = strConnectionString
cn.Open
Set cmd = New adodb.Command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_ImgAlreadyUploaded"
cmd.Parameters.Append cmd.CreateParameter("NoOfM atchedReco rds", adInteger, adParamReturnValue, 6)
cmd.Parameters.Append cmd.CreateParameter("@strS tudy_Date" , adVarChar, adParamInput, 20, strStudyDate)
cmd.Parameters.Append cmd.CreateParameter("@strS tudyTime", adVarChar, adParamInput, 20, strStudyTime)
cmd.Parameters.Append cmd.CreateParameter("@dteB irthDate ", adDBTimeStamp, adParamInput, 8, dteBirthDate)
cmd.Parameters.Append cmd.CreateParameter("@strP atientFNam e ", adVarChar, adParamInput, 50, LTrim$(arrPatientName(0)))
cmd.Parameters.Append cmd.CreateParameter("@strP atientLNam e", adVarChar, adParamInput, 50, LTrim$(arrPatientName(1)))
cmd.Execute
NoOfMatchedRecords = cmd.Parameters("NoOfMatche dRecords")
If NoOfMatchedRecords > 0 Then
intStudiesEarlierUploaded = intStudiesEarlierUploaded + 1
n = m + 1
RepeatLoop
ElseIf NoOfMatchedRecords = 0 Then
CheckInDatabase
End If
'Close the connection and recordset object.
rsCheck.Close
Set rsCheck = Nothing
cn.Close
Set cn = Nothing
Thanks for all your help.
The code below works. But when I use the stmt, rsCheck.RecordCount after ths last statemnt in the code below, the debugger doesnt rech that line and exits the function.
set rsCheck = new adodb.recodset
set rsCheck.ActiveConnection = cn
rsCheck.open cmd
As acperkins mentioned that using the RecordCount is not a good idea so I tried to calcuate the no. of records in teh stored procedure itself and tried to use the return value from the SP in my code. But gaian the debugger doent rech that line where I use the return value. SOmething is wrong I think with the synatx: Below is my SP:
CREATE PROCEDURE [dbo].[sp_ImgAlreadyUpload
@NoOfMatchedRecords integer output,
@strStudy_Date varchar(20),
@strStudyTime varchar(20),
@dteBirthDate datetime,
@strPatientFName varchar(50),
@strPatientLName varchar(50),
AS
Select @NoOfMatchedRecords= count( *) from studies s inner join patient p on p.id=s.patient_idnum
WHERE study_date=@strStudy_Date + ' ' +@strStudyTime and
p.patient_dob = @dteBirthDate AND
( ( p.patient_first = @strPatientFName AND p.patient_last = @strPatientLName)
OR
(p.patient_last =@strPatientFName and p.patient_first=@strPatien
RETURN @NoOfMatchedRecords
GO
VB Code:
Dim NoOfMatchedRecords As Integer
Dim cn As adodb.Connection
Dim rsCheck As adodb.Recordset
Dim cmd As adodb.Command
Set cn = New adodb.Connection
cn.ConnectionString = strConnectionString
cn.Open
Set cmd = New adodb.Command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_ImgAlreadyUploaded"
cmd.Parameters.Append cmd.CreateParameter("NoOfM
cmd.Parameters.Append cmd.CreateParameter("@strS
cmd.Parameters.Append cmd.CreateParameter("@strS
cmd.Parameters.Append cmd.CreateParameter("@dteB
cmd.Parameters.Append cmd.CreateParameter("@strP
cmd.Parameters.Append cmd.CreateParameter("@strP
cmd.Execute
NoOfMatchedRecords = cmd.Parameters("NoOfMatche
If NoOfMatchedRecords > 0 Then
intStudiesEarlierUploaded = intStudiesEarlierUploaded + 1
n = m + 1
RepeatLoop
ElseIf NoOfMatchedRecords = 0 Then
CheckInDatabase
End If
'Close the connection and recordset object.
rsCheck.Close
Set rsCheck = Nothing
cn.Close
Set cn = Nothing
Thanks for all your help.
>>so I tried to calcuate the no. of records in teh stored procedure itself and tried to use the return value from the SP in my code.<<
In order to read an output parameter AND return a resultset you must close the resultset first, before attempting to read the output (in your case Return) value. I know, this is not very useful, but that is the way it is. Alternatively you can use the less efficient method that angelIII has suggested, namely:
rsCheck.open cmd
This wil allow you to set the recordset properties so that you can use the RecordCount property.
In order to read an output parameter AND return a resultset you must close the resultset first, before attempting to read the output (in your case Return) value. I know, this is not very useful, but that is the way it is. Alternatively you can use the less efficient method that angelIII has suggested, namely:
rsCheck.open cmd
This wil allow you to set the recordset properties so that you can use the RecordCount property.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi All,
Thanks so mcuh for your responses. With the error handling i figures that the order of parameters which are passed from VB should be same as the order of parameters which are accepted by the stored procedure. This helped me a great deal in conjuction with all the other things that you guys have mentioned.
Thanks..:)
Thanks so mcuh for your responses. With the error handling i figures that the order of parameters which are passed from VB should be same as the order of parameters which are accepted by the stored procedure. This helped me a great deal in conjuction with all the other things that you guys have mentioned.
Thanks..:)
cmd.Parameters.Append cmd.CreateParameter("@strS
cmd.Parameters.Append cmd.CreateParameter("@strS
cmd.Parameters.Append cmd.CreateParameter("@dteB
cmd.Parameters.Append cmd.CreateParameter("@strP
cmd.Parameters.Append cmd.CreateParameter("@strP