HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value
| Author |
Message |
|
Wim Mintien
#1 / 11
|
 HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value Hi, all out there... I am using ADO 2.6 SP1 + SQL2000 SP2 + VB 6 SP5
I created an Stored procedure that inserts 1 new record and should return a
the value of the identifier of the created record
with profiler I could see that the stored procedure returns the value
but in my vb applic i can not get any value...
I used a paper from Microsoft as example it has no number just a title:
"Using return code and output parameters for stored procedures"
1. the sp does not get the output value...
2. due to this the recordset set is empty and state is closed
CAN PLEAS SOMEONE HELP ME.....I'M DESPERATE
THANKS ......
Wim Mintiens
the stored procedure :
============================================================
CREATE procedure dbo.sp_addMsg
AS
BEGIN
INSERT MESSAGE
(indic_nr,
in_out,
subject,
mfrom,
mto,
cc,
bcc,
priority,
importance,
status_id,
date_received,
date_transmission,
doc_class)
VALUES
END
===========================================================================
my code:
Public Function SaveMsgInDB(ByVal msg As CDO.Message, ByVal iDocType As
Integer, ByVal way As String) As Integer
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim fldloop As ADODB.Field
Dim pmReturn As ADODB.Parameter
Dim pmMsgId As ADODB.Parameter
Dim pmDocClass As ADODB.Parameter
Dim pmInOut As ADODB.Parameter
Dim pmSubject As ADODB.Parameter
Dim pmMFrom As ADODB.Parameter
Dim pmMto As ADODB.Parameter
Dim pmCC As ADODB.Parameter
Dim pmBCC As ADODB.Parameter
Dim pmPriority As ADODB.Parameter
Dim pmImportance As ADODB.Parameter
Dim pmStatusId As ADODB.Parameter
Dim pmDateRcv As ADODB.Parameter
Dim pmDateTrans As ADODB.Parameter
Dim DBUsrName As String
Dim DBPWD As String
Dim DBSrvName As String
Dim DBName As String
Dim iLastIndicNr As Integer
Set cn = New Connection
Set cmd = New Command
Call EventLog("saveIndb", "init")
DBUsrName = GetRegKey(DBUSRID)
DBPWD = "" ' GetRegKey (DBPWD)
DBSrvName = GetRegKey(DBSYS)
DBName = GetRegKey(DBDBNAME)
Call EventLog("saveindb", "connec", , DBUsrName & vbTab & DBPWD &
vbTab & DBSrvName & vbTab & DBName)
' Set connection properties.
cn.ConnectionTimeout = 25 ' Set
the time out.
cn.Provider = "sqloledb" '
Specify the OLE DB provider.
cn.Properties("Data Source").Value = DBSrvName ' Set
SQLOLEDB connection properties.
cn.Properties("Initial Catalog").Value = DBName ' Set
SQLOLEDB connection properties.
cn.Properties("User ID").Value = DBUsrName
cn.Properties("Password").Value = DBPWD
cn.CursorLocation = adUseServer
Call EventLog("saveindb", "open conn")
' Open the database.
cn.Open
Set cmd.ActiveConnection = cn
cmd.CommandText = "sp_AddMsg"
cmd.CommandType = adCmdStoredProc
Call EventLog("saveinddb", "1")
Set pmMsgId = cmd.CreateParameter("OUTPUT", adInteger, adParamOutput)
cmd.Parameters.Append pmMsgId
Call EventLog("saveinddb", "3")
Set pmInOut = cmd.CreateParameter("inOut", adChar, adParamInput, 3)
cmd.Parameters.Append pmInOut
Call EventLog("saveinddb", "4")
Set pmSubject = cmd.CreateParameter("msgSubject", adVarChar,
adParamInput, 255)
cmd.Parameters.Append pmSubject
Call EventLog("saveinddb", "5")
Set pmMFrom = cmd.CreateParameter("msgFrom", adVarChar, adParamInput,
255)
cmd.Parameters.Append pmMFrom
Call EventLog("saveinddb", "6")
Set pmMto = cmd.CreateParameter("msgTo", adVarChar, adParamInput, 255)
cmd.Parameters.Append pmMto
Call EventLog("saveinddb", "7")
Set pmCC = cmd.CreateParameter("msgCC", adVarChar, adParamInput, 255)
cmd.Parameters.Append pmCC
Set pmBCC = cmd.CreateParameter("msgBCC", adVarChar, adParamInput, 255)
cmd.Parameters.Append pmBCC
Set pmPriority = cmd.CreateParameter("msgPriority", adInteger,
adParamInput)
cmd.Parameters.Append pmPriority
Set pmImportance = cmd.CreateParameter("msgImportance", adInteger,
adParamInput)
cmd.Parameters.Append pmImportance
Set pmDateRcv = cmd.CreateParameter("msgDtRcv", adDBTimeStamp,
adParamInput)
cmd.Parameters.Append pmDateRcv
Set pmDateTrans = cmd.CreateParameter("msgDtTrns", adDBTimeStamp,
adParamInput)
cmd.Parameters.Append pmDateTrans
Call EventLog("saveinddb", "a")
Select Case way
Case "IN"
pmInOut.Value = INCOMMING
pmDateRcv.Value = msg.ReceivedTime
pmDateTrans = Null
Case "OUT"
pmInOut.Value = OUTGOING
pmDateTrans.Value = msg.SentOn
pmDateRcv.Value = Null
End Select
Call EventLog("saveinddb", "b")
pmMsgId.Value = 0
pmSubject.Value = msg.Subject
pmMFrom.Value = msg.From
pmMto.Value = msg.To
pmCC.Value = msg.CC
pmBCC.Value = msg.BCC
pmPriority.Value = msg.Fields(cdoPriority)
pmImportance.Value = msg.Fields(cdoImportance)
Call EventLog("saveinddb", "c")
Set rs = cmd.execute
Call EventLog("saveindb", "c2", , CStr(rs.State))
Call EventLog("saveindb", "cmd 0 " & CStr(cmd(0)) & vbTab & "cmd 1
" & CStr(cmd(1)))
Dim i As Integer
While Not rs.EOF
For Each fldloop In rs.Fields
Call EventLog("Saveindb", CStr(rs.Fields(i)))
i = i + 1
Next fldloop
i = 0
rs.MoveNext
Wend
SaveMsgInDB = 1
Call EventLog("saveindb", CStr(cmd(0)) & vbTab & CStr(cmd(1)))
rs.Close
Call EventLog("saveinddb", "d")
cn.Close
End Function
============================================================================
|
| Fri, 30 Jul 2004 06:35:15 GMT |
|
 |
|
oj
#2 / 11
|
HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value wim, here is the correction for your stored procedure.
btw, please do not use [sp] as a prefix for your stored procedure. this will
hinder your performance.
--notice spname changed
AS
--added this
set nocount on
BEGIN
--changed this
INSERT MESSAGE
(indic_nr,
in_out,
subject,
mfrom,
mto,
cc,
bcc,
priority,
importance,
status_id,
date_received,
date_transmission,
doc_class)
VALUES
END
--
-oj
http://rac4sql.home.attbi.com
Quote: > Hi, all out there... > I am using ADO 2.6 SP1 + SQL2000 SP2 + VB 6 SP5
> I created an Stored procedure that inserts 1 new record and should return
a
> the value of the identifier of the created record
> with profiler I could see that the stored procedure returns the value
> but in my vb applic i can not get any value...
> I used a paper from Microsoft as example it has no number just a title:
> "Using return code and output parameters for stored procedures"
> 1. the sp does not get the output value...
> 2. due to this the recordset set is empty and state is closed
> CAN PLEAS SOMEONE HELP ME.....I'M DESPERATE
> THANKS ......
> Wim Mintiens
> the stored procedure :
> ============================================================
> CREATE procedure dbo.sp_addMsg
> AS
> BEGIN
> INSERT MESSAGE
> (indic_nr,
> in_out,
> subject,
> mfrom,
> mto,
> cc,
> bcc,
> priority,
> importance,
> status_id,
> date_received,
> date_transmission,
> doc_class)
> VALUES
> END
=========================================================================== Quote: > my code:
> Public Function SaveMsgInDB(ByVal msg As CDO.Message, ByVal iDocType As
> Integer, ByVal way As String) As Integer
> Dim cn As ADODB.Connection
> Dim rs As ADODB.Recordset
> Dim cmd As ADODB.Command
> Dim fldloop As ADODB.Field
> Dim pmReturn As ADODB.Parameter
> Dim pmMsgId As ADODB.Parameter
> Dim pmDocClass As ADODB.Parameter
> Dim pmInOut As ADODB.Parameter
> Dim pmSubject As ADODB.Parameter
> Dim pmMFrom As ADODB.Parameter
> Dim pmMto As ADODB.Parameter
> Dim pmCC As ADODB.Parameter
> Dim pmBCC As ADODB.Parameter
> Dim pmPriority As ADODB.Parameter
> Dim pmImportance As ADODB.Parameter
> Dim pmStatusId As ADODB.Parameter
> Dim pmDateRcv As ADODB.Parameter
> Dim pmDateTrans As ADODB.Parameter
> Dim DBUsrName As String
> Dim DBPWD As String
> Dim DBSrvName As String
> Dim DBName As String
> Dim iLastIndicNr As Integer
> Set cn = New Connection
> Set cmd = New Command
> Call EventLog("saveIndb", "init")
> DBUsrName = GetRegKey(DBUSRID)
> DBPWD = "" ' GetRegKey (DBPWD)
> DBSrvName = GetRegKey(DBSYS)
> DBName = GetRegKey(DBDBNAME)
> Call EventLog("saveindb", "connec", , DBUsrName & vbTab & DBPWD &
> vbTab & DBSrvName & vbTab & DBName)
> ' Set connection properties.
> cn.ConnectionTimeout = 25 ' Set
> the time out.
> cn.Provider = "sqloledb" '
> Specify the OLE DB provider.
> cn.Properties("Data Source").Value = DBSrvName ' Set
> SQLOLEDB connection properties.
> cn.Properties("Initial Catalog").Value = DBName ' Set
> SQLOLEDB connection properties.
> cn.Properties("User ID").Value = DBUsrName
> cn.Properties("Password").Value = DBPWD
> cn.CursorLocation = adUseServer
> Call EventLog("saveindb", "open conn")
> ' Open the database.
> cn.Open
> Set cmd.ActiveConnection = cn
> cmd.CommandText = "sp_AddMsg"
> cmd.CommandType = adCmdStoredProc
> Call EventLog("saveinddb", "1")
> Set pmMsgId = cmd.CreateParameter("OUTPUT", adInteger, adParamOutput)
> cmd.Parameters.Append pmMsgId
> Call EventLog("saveinddb", "3")
> Set pmInOut = cmd.CreateParameter("inOut", adChar, adParamInput, 3)
> cmd.Parameters.Append pmInOut
> Call EventLog("saveinddb", "4")
> Set pmSubject = cmd.CreateParameter("msgSubject", adVarChar,
> adParamInput, 255)
> cmd.Parameters.Append pmSubject
> Call EventLog("saveinddb", "5")
> Set pmMFrom = cmd.CreateParameter("msgFrom", adVarChar, adParamInput,
> 255)
> cmd.Parameters.Append pmMFrom
> Call EventLog("saveinddb", "6")
> Set pmMto = cmd.CreateParameter("msgTo", adVarChar, adParamInput, 255)
> cmd.Parameters.Append pmMto
> Call EventLog("saveinddb", "7")
> Set pmCC = cmd.CreateParameter("msgCC", adVarChar, adParamInput, 255)
> cmd.Parameters.Append pmCC
> Set pmBCC = cmd.CreateParameter("msgBCC", adVarChar, adParamInput,
255)
> cmd.Parameters.Append pmBCC
> Set pmPriority = cmd.CreateParameter("msgPriority", adInteger,
> adParamInput)
> cmd.Parameters.Append pmPriority
> Set pmImportance = cmd.CreateParameter("msgImportance", adInteger,
> adParamInput)
> cmd.Parameters.Append pmImportance
> Set pmDateRcv = cmd.CreateParameter("msgDtRcv", adDBTimeStamp,
> adParamInput)
> cmd.Parameters.Append pmDateRcv
> Set pmDateTrans = cmd.CreateParameter("msgDtTrns", adDBTimeStamp,
> adParamInput)
> cmd.Parameters.Append pmDateTrans
> Call EventLog("saveinddb", "a")
> Select Case way
> Case "IN"
> pmInOut.Value = INCOMMING
> pmDateRcv.Value = msg.ReceivedTime
> pmDateTrans = Null
> Case "OUT"
> pmInOut.Value = OUTGOING
> pmDateTrans.Value = msg.SentOn
> pmDateRcv.Value = Null
> End Select
> Call EventLog("saveinddb", "b")
> pmMsgId.Value = 0
> pmSubject.Value = msg.Subject
> pmMFrom.Value = msg.From
> pmMto.Value = msg.To
> pmCC.Value = msg.CC
> pmBCC.Value = msg.BCC
> pmPriority.Value = msg.Fields(cdoPriority)
> pmImportance.Value = msg.Fields(cdoImportance)
> Call EventLog("saveinddb", "c")
> Set rs = cmd.execute
> Call EventLog("saveindb", "c2", , CStr(rs.State))
> Call EventLog("saveindb", "cmd 0 " & CStr(cmd(0)) & vbTab & "cmd
1
> " & CStr(cmd(1)))
> Dim i As Integer
> While Not rs.EOF
> For Each fldloop In rs.Fields
> Call EventLog("Saveindb", CStr(rs.Fields(i)))
> i = i + 1
> Next fldloop
> i = 0
> rs.MoveNext
> Wend
> SaveMsgInDB = 1
> Call EventLog("saveindb", CStr(cmd(0)) & vbTab & CStr(cmd(1)))
> rs.Close
> Call EventLog("saveinddb", "d")
> cn.Close
> End Function
=========================================================================== =
|
| Fri, 30 Jul 2004 06:58:40 GMT |
|
|
|
Val Mazu
#3 / 11
|
HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value Hi, Try to close recordset before reading output parameter.
Val
Quote: >-----Original Message----- >Hi, all out there... >I am using ADO 2.6 SP1 + SQL2000 SP2 + VB 6 SP5
>I created an Stored procedure that inserts 1 new record
and should return a
>the value of the identifier of the created record
>with profiler I could see that the stored procedure
returns the value
>but in my vb applic i can not get any value...
>I used a paper from Microsoft as example it has no number
just a title:
>"Using return code and output parameters for stored
procedures"
>1. the sp does not get the output value...
>2. due to this the recordset set is empty and state is
closed
>CAN PLEAS SOMEONE HELP ME.....I'M DESPERATE
>THANKS ......
>Wim Mintiens
>the stored procedure :
>==========================================================
==
>CREATE procedure dbo.sp_addMsg
>AS
>BEGIN
> INSERT MESSAGE
> (indic_nr,
> in_out,
> subject,
> mfrom,
> mto,
> cc,
> bcc,
> priority,
> importance,
> status_id,
> date_received,
> date_transmission,
> doc_class)
> VALUES
Quote: >END
>==========================================================
=================
>my code:
>Public Function SaveMsgInDB(ByVal msg As CDO.Message,
ByVal iDocType As
>Integer, ByVal way As String) As Integer
> Dim cn As ADODB.Connection
> Dim rs As ADODB.Recordset
> Dim cmd As ADODB.Command
> Dim fldloop As ADODB.Field
> Dim pmReturn As ADODB.Parameter
> Dim pmMsgId As ADODB.Parameter
> Dim pmDocClass As ADODB.Parameter
> Dim pmInOut As ADODB.Parameter
> Dim pmSubject As ADODB.Parameter
> Dim pmMFrom As ADODB.Parameter
> Dim pmMto As ADODB.Parameter
> Dim pmCC As ADODB.Parameter
> Dim pmBCC As ADODB.Parameter
> Dim pmPriority As ADODB.Parameter
> Dim pmImportance As ADODB.Parameter
> Dim pmStatusId As ADODB.Parameter
> Dim pmDateRcv As ADODB.Parameter
> Dim pmDateTrans As ADODB.Parameter
> Dim DBUsrName As String
> Dim DBPWD As String
> Dim DBSrvName As String
> Dim DBName As String
> Dim iLastIndicNr As Integer
> Set cn = New Connection
> Set cmd = New Command
> Call EventLog("saveIndb", "init")
> DBUsrName = GetRegKey(DBUSRID)
> DBPWD = "" ' GetRegKey
(DBPWD)
> DBSrvName = GetRegKey(DBSYS)
> DBName = GetRegKey(DBDBNAME)
> Call EventLog("saveindb", "connec", , DBUsrName &
vbTab & DBPWD &
>vbTab & DBSrvName & vbTab & DBName)
> ' Set connection properties.
> cn.ConnectionTimeout = 25
{ w O""S" ?? ' Set Quote: >the time out.
> cn.Provider
= "sqloledb" ' Quote: >Specify the OLE DB provider.
> cn.Properties("Data Source").Value =
DBSrvName ' Set Quote: >SQLOLEDB connection properties.
> cn.Properties("Initia
|
| Fri, 30 Jul 2004 09:50:00 GMT |
|
|
|
Wim Mintien
#4 / 11
|
HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value OJ, VAL, thanks both of you for your reply
I do get an result back in my ado thing from the stored procedure
but still I get the following errror
"3704 Operation is not allowed when the object is closed."
any ideas
closing the recordset did not change anything
regards,
Wim
Quote: > Hi, all out there... > I am using ADO 2.6 SP1 + SQL2000 SP2 + VB 6 SP5
> I created an Stored procedure that inserts 1 new record and should return
a
> the value of the identifier of the created record
> with profiler I could see that the stored procedure returns the value
> but in my vb applic i can not get any value...
> I used a paper from Microsoft as example it has no number just a title:
> "Using return code and output parameters for stored procedures"
> 1. the sp does not get the output value...
> 2. due to this the recordset set is empty and state is closed
> CAN PLEAS SOMEONE HELP ME.....I'M DESPERATE
> THANKS ......
> Wim Mintiens
> the stored procedure :
> ============================================================
> CREATE procedure dbo.sp_addMsg
> AS
> BEGIN
> INSERT MESSAGE
> (indic_nr,
> in_out,
> subject,
> mfrom,
> mto,
> cc,
> bcc,
> priority,
> importance,
> status_id,
> date_received,
> date_transmission,
> doc_class)
> VALUES
> END
=========================================================================== Quote: > my code:
> Public Function SaveMsgInDB(ByVal msg As CDO.Message, ByVal iDocType As
> Integer, ByVal way As String) As Integer
> Dim cn As ADODB.Connection
> Dim rs As ADODB.Recordset
> Dim cmd As ADODB.Command
> Dim fldloop As ADODB.Field
> Dim pmReturn As ADODB.Parameter
> Dim pmMsgId As ADODB.Parameter
> Dim pmDocClass As ADODB.Parameter
> Dim pmInOut As ADODB.Parameter
> Dim pmSubject As ADODB.Parameter
> Dim pmMFrom As ADODB.Parameter
> Dim pmMto As ADODB.Parameter
> Dim pmCC As ADODB.Parameter
> Dim pmBCC As ADODB.Parameter
> Dim pmPriority As ADODB.Parameter
> Dim pmImportance As ADODB.Parameter
> Dim pmStatusId As ADODB.Parameter
> Dim pmDateRcv As ADODB.Parameter
> Dim pmDateTrans As ADODB.Parameter
> Dim DBUsrName As String
> Dim DBPWD As String
> Dim DBSrvName As String
> Dim DBName As String
> Dim iLastIndicNr As Integer
> Set cn = New Connection
> Set cmd = New Command
> Call EventLog("saveIndb", "init")
> DBUsrName = GetRegKey(DBUSRID)
> DBPWD = "" ' GetRegKey (DBPWD)
> DBSrvName = GetRegKey(DBSYS)
> DBName = GetRegKey(DBDBNAME)
> Call EventLog("saveindb", "connec", , DBUsrName & vbTab & DBPWD &
> vbTab & DBSrvName & vbTab & DBName)
> ' Set connection properties.
> cn.ConnectionTimeout = 25 ' Set
> the time out.
> cn.Provider = "sqloledb" '
> Specify the OLE DB provider.
> cn.Properties("Data Source").Value = DBSrvName ' Set
> SQLOLEDB connection properties.
> cn.Properties("Initial Catalog").Value = DBName ' Set
> SQLOLEDB connection properties.
> cn.Properties("User ID").Value = DBUsrName
> cn.Properties("Password").Value = DBPWD
> cn.CursorLocation = adUseServer
> Call EventLog("saveindb", "open conn")
> ' Open the database.
> cn.Open
> Set cmd.ActiveConnection = cn
> cmd.CommandText = "sp_AddMsg"
> cmd.CommandType = adCmdStoredProc
> Call EventLog("saveinddb", "1")
> Set pmMsgId = cmd.CreateParameter("OUTPUT", adInteger, adParamOutput)
> cmd.Parameters.Append pmMsgId
> Call EventLog("saveinddb", "3")
> Set pmInOut = cmd.CreateParameter("inOut", adChar, adParamInput, 3)
> cmd.Parameters.Append pmInOut
> Call EventLog("saveinddb", "4")
> Set pmSubject = cmd.CreateParameter("msgSubject", adVarChar,
> adParamInput, 255)
> cmd.Parameters.Append pmSubject
> Call EventLog("saveinddb", "5")
> Set pmMFrom = cmd.CreateParameter("msgFrom", adVarChar, adParamInput,
> 255)
> cmd.Parameters.Append pmMFrom
> Call EventLog("saveinddb", "6")
> Set pmMto = cmd.CreateParameter("msgTo", adVarChar, adParamInput, 255)
> cmd.Parameters.Append pmMto
> Call EventLog("saveinddb", "7")
> Set pmCC = cmd.CreateParameter("msgCC", adVarChar, adParamInput, 255)
> cmd.Parameters.Append pmCC
> Set pmBCC = cmd.CreateParameter("msgBCC", adVarChar, adParamInput,
255)
> cmd.Parameters.Append pmBCC
> Set pmPriority = cmd.CreateParameter("msgPriority", adInteger,
> adParamInput)
> cmd.Parameters.Append pmPriority
> Set pmImportance = cmd.CreateParameter("msgImportance", adInteger,
> adParamInput)
> cmd.Parameters.Append pmImportance
> Set pmDateRcv = cmd.CreateParameter("msgDtRcv", adDBTimeStamp,
> adParamInput)
> cmd.Parameters.Append pmDateRcv
> Set pmDateTrans = cmd.CreateParameter("msgDtTrns", adDBTimeStamp,
> adParamInput)
> cmd.Parameters.Append pmDateTrans
> Call EventLog("saveinddb", "a")
> Select Case way
> Case "IN"
> pmInOut.Value = INCOMMING
> pmDateRcv.Value = msg.ReceivedTime
> pmDateTrans = Null
> Case "OUT"
> pmInOut.Value = OUTGOING
> pmDateTrans.Value = msg.SentOn
> pmDateRcv.Value = Null
> End Select
> Call EventLog("saveinddb", "b")
> pmMsgId.Value = 0
> pmSubject.Value = msg.Subject
> pmMFrom.Value = msg.From
> pmMto.Value = msg.To
> pmCC.Value = msg.CC
> pmBCC.Value = msg.BCC
> pmPriority.Value = msg.Fields(cdoPriority)
> pmImportance.Value = msg.Fields(cdoImportance)
> Call EventLog("saveinddb", "c")
> Set rs = cmd.execute
> Call EventLog("saveindb", "c2", , CStr(rs.State))
> Call EventLog("saveindb", "cmd 0 " & CStr(cmd(0)) & vbTab & "cmd
1
> " & CStr(cmd(1)))
> Dim i As Integer
> While Not rs.EOF
> For Each fldloop In rs.Fields
> Call EventLog("Saveindb", CStr(rs.Fields(i)))
> i = i + 1
> Next fldloop
> i = 0
> rs.MoveNext
> Wend
> SaveMsgInDB = 1
> Call EventLog("saveindb", CStr(cmd(0)) & vbTab & CStr(cmd(1)))
> rs.Close
> Call EventLog("saveinddb", "d")
> cn.Close
> End Function
=========================================================================== =
|
| Fri, 30 Jul 2004 15:06:40 GMT |
|
|
|
Harinatha Reddy Gorl
#5 / 11
|
HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value HI I think stored procedure is returning multiple results . There are two options where you can solve this 1)Use SET NOCOUNT ON before each statement. 2) Or use NextRecordset in your ADO code. First option is preferred. also, refer to the link: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q235340 Harinatha Reddy Gorla (MCSD)
Software Engineer
Smart Software Technologies,
Hyderabad, India
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
|
| Fri, 30 Jul 2004 16:28:41 GMT |
|
|
|
Harinatha Reddy Gorl
#6 / 11
|
HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value HI A small correction in my previous post: I think stored procedure is returning multiple resultsets . There are two options where you can solve this 1)Use SET NOCOUNT ON at the beginning in SP. 2) Or use NextRecordset in your ADO code. First option is preferred. also, refer to the link: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q235340 Harinatha Reddy Gorla (MCSD)
Software Engineer
Smart Software Technologies,
Hyderabad, India
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
|
| Fri, 30 Jul 2004 16:56:43 GMT |
|
|
|
Wim Mintien
#7 / 11
|
HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value Harinatha, sorry but that did not change it, i stll get the error
I even adapted the code to what was specified in Q235340
still the same error, the record set stays closed....
regards & thanks
Wim
Quote: > HI > I think stored procedure is returning multiple results . > There are two options where you can solve this > 1)Use SET NOCOUNT ON before each statement. > 2) Or use NextRecordset in your ADO code. > First option is preferred. > also, refer to the link: > http://support.microsoft.com/default.aspx?scid=kb;EN-US;q235340 > Harinatha Reddy Gorla (MCSD)
> Software Engineer
> Smart Software Technologies,
> Hyderabad, India
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
|
| Fri, 30 Jul 2004 19:01:21 GMT |
|
|
|
Val Mazu
#8 / 11
|
HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value Hi, Add SET NOCOUNT ON at the beginning of SP and SET NOCOUNT
OFF at the end. See if it helps. If not, then after you
open recordset with ADO, try to use NextRecordset to see
if you get multiple one. Probably OUTPUT value comes with
last recordset.
Val
Quote: >-----Original Message----- >OJ, VAL, >thanks both of you for your reply
>I do get an result back in my ado thing from the stored
procedure
>but still I get the following errror
>"3704 Operation is not allowed when the object is closed."
>any ideas
>closing the recordset did not change anything
>regards,
>Wim
message
>> Hi, all out there...
>> I am using ADO 2.6 SP1 + SQL2000 SP2 + VB 6 SP5
>> I created an Stored procedure that inserts 1 new record
and should return
>a
>> the value of the identifier of the created record
>> with profiler I could see that the stored procedure
returns the value
>> but in my vb applic i can not get any value...
>> I used a paper from Microsoft as example it has no
number just a title: Quote: >> "Using return code and output parameters for stored procedures" >> 1. the sp does not get the output value...
>> 2. due to this the recordset set is empty and state is
closed
>> CAN PLEAS SOMEONE HELP ME.....I'M DESPERATE
>> THANKS ......
>> Wim Mintiens
>> the stored procedure :
=========================================================== = Quote: >> CREATE procedure dbo.sp_addMsg
>> AS
>> BEGIN
>> INSERT MESSAGE
>> (indic_nr,
>> in_out,
>> subject,
>> mfrom,
>> mto,
>> cc,
>> bcc,
>> priority,
>> importance,
>> status_id,
>> date_received,
>> date_transmission,
>> doc_class)
>> VALUES
indic_nr =
>> END
>==========================================================
=================
>> my code:
>> Public Function SaveMsgInDB(ByVal msg As CDO.Message,
ByVal iDocType As
>> Integer, ByVal way As String) As Integer
>> Dim cn As ADODB.Connection
>> Dim rs As ADODB.Recordset
>> Dim cmd As ADODB.Command
>> Dim fldloop As ADODB.Field
>> Dim pmReturn As ADODB.Parameter
>> Dim pmMsgId As ADODB.Parameter
>> Dim pmDocClass As ADODB.Parameter
>> Dim pmInOut As ADODB.Parameter
>> Dim pmSubject As ADODB.Parameter
>> Dim pmMFrom As ADODB.Parameter
>> Dim pmMto As ADODB.Parameter
>> Dim pmCC As ADODB.Parameter
>> Dim pmBCC As ADODB.Parameter
>> Dim pmPriority As ADODB.Parameter
>> Dim pmImportance As ADODB.Parameter
>> Dim pmStatusId As ADODB.Parameter
>> Dim pmDateRcv{ w o 4tj"?r2Dk * As
ADODB.Parameter
>> Dim pmDateTrans As ADODB.Parameter
>> Dim DBUsrName As String
>> Dim DBPWD As String
>> Dim DBSrvName As String
>> Dim DBName As String
>> Dim iLastIndicNr As Integer
>> Set cn = New Connection
>> Set cmd = New Command
|
| Fri, 30 Jul 2004 20:37:46 GMT |
|
|
|
Bruc
#9 / 11
|
HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value As far as receiving output parameters and return values (they are different) from a stored procedure to an ADO command object, here's some additional information. SQL Server sends the values in the last data packet. This means a server
side recordset must be closed before receiving the parameter values. A
clientside recordset does not have to be closed.
The return value parameter should be the first one defined in the ado
command.
As for the error "3704 Operation is not allowed when the object is closed."
Your stored procedure does not return any records. No where in your
procedure is there a Select statement. Your rs variable does get
instantiated by the cmd.Execute statement but it's state is closed. Since
you then try to access the EOF property you receive this error. You should
add a check for possible scenarios, something like.
Set rs = cmd.Execute
If Not rs is Nothing then
if rs.state = adStateOpen then
Do
' ........ record processing.
rs.movenext
Loop Until rs.EOF
else
'Recordset is closed.
end if
Endif
HTH
Bruce
Quote: > OJ, VAL, > thanks both of you for your reply
> I do get an result back in my ado thing from the stored procedure
> but still I get the following errror
> "3704 Operation is not allowed when the object is closed."
> any ideas
> closing the recordset did not change anything
> regards,
> Wim
> > Hi, all out there...
> > I am using ADO 2.6 SP1 + SQL2000 SP2 + VB 6 SP5
> > I created an Stored procedure that inserts 1 new record and should
return
> a
> > the value of the identifier of the created record
> > with profiler I could see that the stored procedure returns the value
> > but in my vb applic i can not get any value...
> > I used a paper from Microsoft as example it has no number just a title:
> > "Using return code and output parameters for stored procedures"
> > 1. the sp does not get the output value...
> > 2. due to this the recordset set is empty and state is closed
> > CAN PLEAS SOMEONE HELP ME.....I'M DESPERATE
> > THANKS ......
> > Wim Mintiens
> > the stored procedure :
> > ============================================================
> > CREATE procedure dbo.sp_addMsg
> > AS
> > BEGIN
> > INSERT MESSAGE
> > (indic_nr,
> > in_out,
> > subject,
> > mfrom,
> > mto,
> > cc,
> > bcc,
> > priority,
> > importance,
> > status_id,
> > date_received,
> > date_transmission,
> > doc_class)
> > VALUES
> > END
=========================================================================== Quote: > > my code:
> > Public Function SaveMsgInDB(ByVal msg As CDO.Message, ByVal iDocType As
> > Integer, ByVal way As String) As Integer
> > Dim cn As ADODB.Connection
> > Dim rs As ADODB.Recordset
> > Dim cmd As ADODB.Command
> > Dim fldloop As ADODB.Field
> > Dim pmReturn As ADODB.Parameter
> > Dim pmMsgId As ADODB.Parameter
> > Dim pmDocClass As ADODB.Parameter
> > Dim pmInOut As ADODB.Parameter
> > Dim pmSubject As ADODB.Parameter
> > Dim pmMFrom As ADODB.Parameter
> > Dim pmMto As ADODB.Parameter
> > Dim pmCC As ADODB.Parameter
> > Dim pmBCC As ADODB.Parameter
> > Dim pmPriority As ADODB.Parameter
> > Dim pmImportance As ADODB.Parameter
> > Dim pmStatusId As ADODB.Parameter
> > Dim pmDateRcv As ADODB.Parameter
> > Dim pmDateTrans As ADODB.Parameter
> > Dim DBUsrName As String
> > Dim DBPWD As String
> > Dim DBSrvName As String
> > Dim DBName As String
> > Dim iLastIndicNr As Integer
> > Set cn = New Connection
> > Set cmd = New Command
> > Call EventLog("saveIndb", "init")
> > DBUsrName = GetRegKey(DBUSRID)
> > DBPWD = "" ' GetRegKey (DBPWD)
> > DBSrvName = GetRegKey(DBSYS)
> > DBName = GetRegKey(DBDBNAME)
> > Call EventLog("saveindb", "connec", , DBUsrName & vbTab & DBPWD
&
> > vbTab & DBSrvName & vbTab & DBName)
> > ' Set connection properties.
> > cn.ConnectionTimeout = 25 '
Set
> > the time out.
> > cn.Provider = "sqloledb" '
> > Specify the OLE DB provider.
> > cn.Properties("Data Source").Value = DBSrvName '
Set
> > SQLOLEDB connection properties.
> > cn.Properties("Initial Catalog").Value = DBName '
Set
> > SQLOLEDB connection properties.
> > cn.Properties("User ID").Value = DBUsrName
> > cn.Properties("Password").Value = DBPWD
> > cn.CursorLocation = adUseServer
> > Call EventLog("saveindb", "open conn")
> > ' Open the database.
> > cn.Open
> > Set cmd.ActiveConnection = cn
> > cmd.CommandText = "sp_AddMsg"
> > cmd.CommandType = adCmdStoredProc
> > Call EventLog("saveinddb", "1")
> > Set pmMsgId = cmd.CreateParameter("OUTPUT", adInteger,
adParamOutput)
> > cmd.Parameters.Append pmMsgId
> > Call EventLog("saveinddb", "3")
> > Set pmInOut = cmd.CreateParameter("inOut", adChar, adParamInput, 3)
> > cmd.Parameters.Append pmInOut
> > Call EventLog("saveinddb", "4")
> > Set pmSubject = cmd.CreateParameter("msgSubject", adVarChar,
> > adParamInput, 255)
> > cmd.Parameters.Append pmSubject
> > Call EventLog("saveinddb", "5")
> > Set pmMFrom = cmd.CreateParameter("msgFrom", adVarChar,
adParamInput,
> > 255)
> > cmd.Parameters.Append pmMFrom
> > Call EventLog("saveinddb", "6")
> > Set pmMto = cmd.CreateParameter("msgTo", adVarChar, adParamInput,
255)
> > cmd.Parameters.Append pmMto
> > Call EventLog("saveinddb", "7")
> > Set pmCC = cmd.CreateParameter("msgCC", adVarChar, adParamInput,
255)
> > cmd.Parameters.Append pmCC
> > Set pmBCC = cmd.CreateParameter("msgBCC", adVarChar, adParamInput,
> 255)
> > cmd.Parameters.Append pmBCC
> > Set pmPriority = cmd.CreateParameter("msgPriority", adInteger,
> > adParamInput)
> > cmd.Parameters.Append pmPriority
> > Set pmImportance = cmd.CreateParameter("msgImportance", adInteger,
> > adParamInput)
> > cmd.Parameters.Append pmImportance
> > Set pmDateRcv = cmd.CreateParameter("msgDtRcv", adDBTimeStamp,
> > adParamInput)
> > cmd.Parameters.Append pmDateRcv
> > Set pmDateTrans = cmd.CreateParameter("msgDtTrns", adDBTimeStamp,
> > adParamInput)
> > cmd.Parameters.Append pmDateTrans
> > Call EventLog("saveinddb", "a")
> > Select Case way
> > Case "IN"
> > pmInOut.Value = INCOMMING
> > pmDateRcv.Value = msg.ReceivedTime
> > pmDateTrans = Null
> > Case "OUT"
> > pmInOut.Value = OUTGOING
> > pmDateTrans.Value = msg.SentOn
> > pmDateRcv.Value = Null
> > End Select
> > Call EventLog("saveinddb", "b")
> > pmMsgId.Value = 0
> > pmSubject.Value = msg.Subject
> > pmMFrom.Value = msg.From
> > pmMto.Value = msg.To
> > pmCC.Value = msg.CC
> > pmBCC.Value = msg.BCC
> > pmPriority.Value = msg.Fields(cdoPriority)
> > pmImportance.Value = msg.Fields(cdoImportance)
> > Call EventLog("saveinddb", "c")
> > Set rs = cmd.execute
> > Call EventLog("saveindb", "c2", , CStr(rs.State))
> > Call EventLog("saveindb", "cmd 0 " & CStr(cmd(0)) & vbTab &
"cmd
> 1
> > " & CStr(cmd(1)))
> > Dim i As Integer
> > While Not rs.EOF
> > For Each fldloop In rs.Fields
> > Call EventLog("Saveindb", CStr(rs.Fields(i)))
> > i = i + 1
> > Next fldloop
> > i = 0
> > rs.MoveNext
> > Wend
> > SaveMsgInDB = 1
> > Call EventLog("saveindb", CStr(cmd(0)) & vbTab & CStr(cmd(1)))
> > rs.Close
> > Call EventLog("saveinddb", "d")
> > cn.Close
> > End Function
=========================================================================== =
|
| Sat, 31 Jul 2004 09:27:27 GMT |
|
|
|
Wim Mintien
#10 / 11
|
HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value Bruce, thanks for the clarification of the "problem", it's clear now what i have to
do
Many thanks to your all for helping me ....
Regards,
Wim Mintiens
Belgium
Quote: > As far as receiving output parameters and return values (they are different) > from a stored procedure to an ADO command object, here's some additional > information. > SQL Server sends the values in the last data packet. This means a server
> side recordset must be closed before receiving the parameter values. A
> clientside recordset does not have to be closed.
> The return value parameter should be the first one defined in the ado
> command.
> As for the error "3704 Operation is not allowed when the object is
closed."
> Your stored procedure does not return any records. No where in your
> procedure is there a Select statement. Your rs variable does get
> instantiated by the cmd.Execute statement but it's state is closed. Since
> you then try to access the EOF property you receive this error. You
should
> add a check for possible scenarios, something like.
> Set rs = cmd.Execute
> If Not rs is Nothing then
> if rs.state = adStateOpen then
> Do
> ' ........ record processing.
> rs.movenext
> Loop Until rs.EOF
> else
> 'Recordset is closed.
> end if
> Endif
> HTH
> Bruce
> > OJ, VAL,
> > thanks both of you for your reply
> > I do get an result back in my ado thing from the stored procedure
> > but still I get the following errror
> > "3704 Operation is not allowed when the object is closed."
> > any ideas
> > closing the recordset did not change anything
> > regards,
> > Wim
> > > Hi, all out there...
> > > I am using ADO 2.6 SP1 + SQL2000 SP2 + VB 6 SP5
> > > I created an Stored procedure that inserts 1 new record and should
> return
> > a
> > > the value of the identifier of the created record
> > > with profiler I could see that the stored procedure returns the value
> > > but in my vb applic i can not get any value...
> > > I used a paper from Microsoft as example it has no number just a
title:
> > > "Using return code and output parameters for stored procedures"
> > > 1. the sp does not get the output value...
> > > 2. due to this the recordset set is empty and state is closed
> > > CAN PLEAS SOMEONE HELP ME.....I'M DESPERATE
> > > THANKS ......
> > > Wim Mintiens
> > > the stored procedure :
> > > ============================================================
> > > CREATE procedure dbo.sp_addMsg
> > > AS
> > > BEGIN
> > > INSERT MESSAGE
> > > (indic_nr,
> > > in_out,
> > > subject,
> > > mfrom,
> > > mto,
> > > cc,
> > > bcc,
> > > priority,
> > > importance,
> > > status_id,
> > > date_received,
> > > date_transmission,
> > > doc_class)
> > > VALUES
> > > END
=========================================================================== Quote: > > > my code:
> > > Public Function SaveMsgInDB(ByVal msg As CDO.Message, ByVal iDocType
As
> > > Integer, ByVal way As String) As Integer
> > > Dim cn As ADODB.Connection
> > > Dim rs As ADODB.Recordset
> > > Dim cmd As ADODB.Command
> > > Dim fldloop As ADODB.Field
> > > Dim pmReturn As ADODB.Parameter
> > > Dim pmMsgId As ADODB.Parameter
> > > Dim pmDocClass As ADODB.Parameter
> > > Dim pmInOut As ADODB.Parameter
> > > Dim pmSubject As ADODB.Parameter
> > > Dim pmMFrom As ADODB.Parameter
> > > Dim pmMto As ADODB.Parameter
> > > Dim pmCC As ADODB.Parameter
> > > Dim pmBCC As ADODB.Parameter
> > > Dim pmPriority As ADODB.Parameter
> > > Dim pmImportance As ADODB.Parameter
> > > Dim pmStatusId As ADODB.Parameter
> > > Dim pmDateRcv As ADODB.Parameter
> > > Dim pmDateTrans As ADODB.Parameter
> > > Dim DBUsrName As String
> > > Dim DBPWD As String
> > > Dim DBSrvName As String
> > > Dim DBName As String
> > > Dim iLastIndicNr As Integer
> > > Set cn = New Connection
> > > Set cmd = New Command
> > > Call EventLog("saveIndb", "init")
> > > DBUsrName = GetRegKey(DBUSRID)
> > > DBPWD = "" ' GetRegKey (DBPWD)
> > > DBSrvName = GetRegKey(DBSYS)
> > > DBName = GetRegKey(DBDBNAME)
> > > Call EventLog("saveindb", "connec", , DBUsrName & vbTab &
DBPWD
> &
> > > vbTab & DBSrvName & vbTab & DBName)
> > > ' Set connection properties.
> > > cn.ConnectionTimeout = 25 '
> Set
> > > the time out.
> > > cn.Provider = "sqloledb" '
> > > Specify the OLE DB provider.
> > > cn.Properties("Data Source").Value = DBSrvName '
> Set
> > > SQLOLEDB connection properties.
> > > cn.Properties("Initial Catalog").Value = DBName '
> Set
> > > SQLOLEDB connection properties.
> > > cn.Properties("User ID").Value = DBUsrName
> > > cn.Properties("Password").Value = DBPWD
> > > cn.CursorLocation = adUseServer
> > > Call EventLog("saveindb", "open conn")
> > > ' Open the database.
> > > cn.Open
> > > Set cmd.ActiveConnection = cn
> > > cmd.CommandText = "sp_AddMsg"
> > > cmd.CommandType = adCmdStoredProc
> > > Call EventLog("saveinddb", "1")
> > > Set pmMsgId = cmd.CreateParameter("OUTPUT", adInteger,
> adParamOutput)
> > > cmd.Parameters.Append pmMsgId
> > > Call EventLog("saveinddb", "3")
> > > Set pmInOut = cmd.CreateParameter("inOut", adChar, adParamInput,
3)
> > > cmd.Parameters.Append pmInOut
> > > Call EventLog("saveinddb", "4")
> > > Set pmSubject = cmd.CreateParameter("msgSubject", adVarChar,
> > > adParamInput, 255)
> > > cmd.Parameters.Append pmSubject
> > > Call EventLog("saveinddb", "5")
> > > Set pmMFrom = cmd.CreateParameter("msgFrom", adVarChar,
> adParamInput,
> > > 255)
> > > cmd.Parameters.Append pmMFrom
> > > Call EventLog("saveinddb", "6")
> > > Set pmMto = cmd.CreateParameter("msgTo", adVarChar, adParamInput,
> 255)
> > > cmd.Parameters.Append pmMto
> > > Call EventLog("saveinddb", "7")
> > > Set pmCC = cmd.CreateParameter("msgCC", adVarChar, adParamInput,
> 255)
> > > cmd.Parameters.Append pmCC
> > > Set pmBCC = cmd.CreateParameter("msgBCC", adVarChar, adParamInput,
> > 255)
> > > cmd.Parameters.Append pmBCC
> > > Set pmPriority = cmd.CreateParameter("msgPriority", adInteger,
> > > adParamInput)
> > > cmd.Parameters.Append pmPriority
> > > Set pmImportance = cmd.CreateParameter("msgImportance", adInteger,
> > > adParamInput)
> > > cmd.Parameters.Append pmImportance
> > > Set pmDateRcv = cmd.CreateParameter("msgDtRcv", adDBTimeStamp,
> > > adParamInput)
> > > cmd.Parameters.Append pmDateRcv
> > > Set pmDateTrans = cmd.CreateParameter("msgDtTrns", adDBTimeStamp,
> > > adParamInput)
> > > cmd.Parameters.Append pmDateTrans
> > > Call EventLog("saveinddb", "a")
> > > Select Case way
> > > Case "IN"
> > > pmInOut.Value = INCOMMING
> > > pmDateRcv.Value = msg.ReceivedTime
> > > pmDateTrans = Null
> > > Case "OUT"
> > > pmInOut.Value = OUTGOING
> > > pmDateTrans.Value = msg.SentOn
> > > pmDateRcv.Value = Null
> > > End Select
> > > Call EventLog("saveinddb", "b")
> > > pmMsgId.Value = 0
> > > pmSubject.Value = msg.Subject
> > > pmMFrom.Value = msg.From
> > > pmMto.Value = msg.To
> > > pmCC.Value = msg.CC
> > > pmBCC.Value = msg.BCC
> > > pmPriority.Value = msg.Fields(cdoPriority)
> > > pmImportance.Value = msg.Fields(cdoImportance)
> > > Call EventLog("saveinddb", "c")
> > > Set rs = cmd.execute
> > > Call EventLog("saveindb", "c2", , CStr(rs.State))
> > > Call EventLog("saveindb", "cmd 0 " & CStr(cmd(0)) & vbTab &
> "cmd
> > 1
> > > " & CStr(cmd(1)))
> > > Dim i As Integer
> > > While Not rs.EOF
> > > For Each fldloop In rs.Fields
> > > Call EventLog("Saveindb", CStr(rs.Fields(i)))
> > > i = i + 1
> > > Next fldloop
> > > i = 0
> > > rs.MoveNext
> > > Wend
> > > SaveMsgInDB = 1
> > > Call
... read more »
|
| Sat, 31 Jul 2004 16:23:03 GMT |
|
|
|
Bruc
#11 / 11
|
HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value Not sure if you know this but there are other ways to use the Execute method of a command object. If you know the stored procedure does not return a recordset then you
can/should use this syntax cmd.Execute ,, adExecuteNoRecords, this is
slightly more efficient.
You cannot update the recordset returned by the Execute method as it is a
ForwardOnly ReadOnly cursor. You can however use the Command object as the
Source to the Recordset.Open method.
... define your command object
Set rs = New ADODB.Recordset
rs.Open cmd,, adOpenStatic, adLockBatchOptimistic
Bruce
Quote: > Bruce, > thanks for the clarification of the "problem", it's clear now what i have
to
> do
> Many thanks to your all for helping me ....
> Regards,
> Wim Mintiens
> Belgium
> > As far as receiving output parameters and return values (they are
> different)
> > from a stored procedure to an ADO command object, here's some additional
> > information.
> > SQL Server sends the values in the last data packet. This means a server
> > side recordset must be closed before receiving the parameter values. A
> > clientside recordset does not have to be closed.
> > The return value parameter should be the first one defined in the ado
> > command.
> > As for the error "3704 Operation is not allowed when the object is
> closed."
> > Your stored procedure does not return any records. No where in your
> > procedure is there a Select statement. Your rs variable does get
> > instantiated by the cmd.Execute statement but it's state is closed.
Since
> > you then try to access the EOF property you receive this error. You
> should
> > add a check for possible scenarios, something like.
> > Set rs = cmd.Execute
> > If Not rs is Nothing then
> > if rs.state = adStateOpen then
> > Do
> > ' ........ record processing.
> > rs.movenext
> > Loop Until rs.EOF
> > else
> > 'Recordset is closed.
> > end if
> > Endif
> > HTH
> > Bruce
> > > OJ, VAL,
> > > thanks both of you for your reply
> > > I do get an result back in my ado thing from the stored procedure
> > > but still I get the following errror
> > > "3704 Operation is not allowed when the object is closed."
> > > any ideas
> > > closing the recordset did not change anything
> > > regards,
> > > Wim
> > > > Hi, all out there...
> > > > I am using ADO 2.6 SP1 + SQL2000 SP2 + VB 6 SP5
> > > > I created an Stored procedure that inserts 1 new record and should
> > return
> > > a
> > > > the value of the identifier of the created record
> > > > with profiler I could see that the stored procedure returns the
value
> > > > but in my vb applic i can not get any value...
> > > > I used a paper from Microsoft as example it has no number just a
> title:
> > > > "Using return code and output parameters for stored procedures"
> > > > 1. the sp does not get the output value...
> > > > 2. due to this the recordset set is empty and state is closed
> > > > CAN PLEAS SOMEONE HELP ME.....I'M DESPERATE
> > > > THANKS ......
> > > > Wim Mintiens
> > > > the stored procedure :
> > > > ============================================================
> > > > CREATE procedure dbo.sp_addMsg
> > > > AS
> > > > BEGIN
> > > > INSERT MESSAGE
> > > > (indic_nr,
> > > > in_out,
> > > > subject,
> > > > mfrom,
> > > > mto,
> > > > cc,
> > > > bcc,
> > > > priority,
> > > > importance,
> > > > status_id,
> > > > date_received,
> > > > date_transmission,
> > > > doc_class)
> > > > VALUES
> > > > END
=========================================================================== Quote: > > > > my code:
> > > > Public Function SaveMsgInDB(ByVal msg As CDO.Message, ByVal iDocType
> As
> > > > Integer, ByVal way As String) As Integer
> > > > Dim cn As ADODB.Connection
> > > > Dim rs As ADODB.Recordset
> > > > Dim cmd As ADODB.Command
> > > > Dim fldloop As ADODB.Field
> > > > Dim pmReturn As ADODB.Parameter
> > > > Dim pmMsgId As ADODB.Parameter
> > > > Dim pmDocClass As ADODB.Parameter
> > > > Dim pmInOut As ADODB.Parameter
> > > > Dim pmSubject As ADODB.Parameter
> > > > Dim pmMFrom As ADODB.Parameter
> > > > Dim pmMto As ADODB.Parameter
> > > > Dim pmCC As ADODB.Parameter
> > > > Dim pmBCC As ADODB.Parameter
> > > > Dim pmPriority As ADODB.Parameter
> > > > Dim pmImportance As ADODB.Parameter
> > > > Dim pmStatusId As ADODB.Parameter
> > > > Dim pmDateRcv As ADODB.Parameter
> > > > Dim pmDateTrans As ADODB.Parameter
> > > > Dim DBUsrName As String
> > > > Dim DBPWD As String
> > > > Dim DBSrvName As String
> > > > Dim DBName As String
> > > > Dim iLastIndicNr As Integer
> > > > Set cn = New Connection
> > > > Set cmd = New Command
> > > > Call EventLog("saveIndb", "init")
> > > > DBUsrName = GetRegKey(DBUSRID)
> > > > DBPWD = "" ' GetRegKey (DBPWD)
> > > > DBSrvName = GetRegKey(DBSYS)
> > > > DBName = GetRegKey(DBDBNAME)
> > > > Call EventLog("saveindb", "connec", , DBUsrName & vbTab &
> DBPWD
> > &
> > > > vbTab & DBSrvName & vbTab & DBName)
> > > > ' Set connection properties.
> > > > cn.ConnectionTimeout = 25
'
> > Set
> > > > the time out.
> > > > cn.Provider = "sqloledb"
'
> > > > Specify the OLE DB provider.
> > > > cn.Properties("Data Source").Value = DBSrvName
'
> > Set
> > > > SQLOLEDB connection properties.
> > > > cn.Properties("Initial Catalog").Value = DBName
'
> > Set
> > > > SQLOLEDB connection properties.
> > > > cn.Properties("User ID").Value = DBUsrName
> > > > cn.Properties("Password").Value = DBPWD
> > > > cn.CursorLocation = adUseServer
> > > > Call EventLog("saveindb", "open conn")
> > > > ' Open the database.
> > > > cn.Open
> > > > Set cmd.ActiveConnection = cn
> > > > cmd.CommandText = "sp_AddMsg"
> > > > cmd.CommandType = adCmdStoredProc
> > > > Call EventLog("saveinddb", "1")
> > > > Set pmMsgId = cmd.CreateParameter("OUTPUT", adInteger,
> > adParamOutput)
> > > > cmd.Parameters.Append pmMsgId
> > > > Call EventLog("saveinddb", "3")
> > > > Set pmInOut = cmd.CreateParameter("inOut", adChar, adParamInput,
> 3)
> > > > cmd.Parameters.Append pmInOut
> > > > Call EventLog("saveinddb", "4")
> > > > Set pmSubject = cmd.CreateParameter("msgSubject", adVarChar,
> > > > adParamInput, 255)
> > > > cmd.Parameters.Append pmSubject
> > > > Call EventLog("saveinddb", "5")
> > > > Set pmMFrom = cmd.CreateParameter("msgFrom", adVarChar,
> > adParamInput,
> > > > 255)
> > > > cmd.Parameters.Append pmMFrom
> > > > Call EventLog("saveinddb", "6")
> > > > Set pmMto = cmd.CreateParameter("msgTo", adVarChar,
adParamInput,
> > 255)
> > > > cmd.Parameters.Append pmMto
> > > > Call EventLog("saveinddb", "7")
> > > > Set pmCC = cmd.CreateParameter("msgCC", adVarChar, adParamInput,
> > 255)
> > > > cmd.Parameters.Append pmCC
> > > > Set pmBCC = cmd.CreateParameter("msgBCC", adVarChar,
adParamInput,
> > > 255)
> > > > cmd.Parameters.Append pmBCC
> > > > Set pmPriority = cmd.CreateParameter("msgPriority", adInteger,
> > > > adParamInput)
> > > > cmd.Parameters.Append pmPriority
> > > > Set pmImportance = cmd.CreateParameter("msgImportance",
adInteger,
> > > > adParamInput)
> > > > cmd.Parameters.Append pmImportance
> > > > Set pmDateRcv = cmd.CreateParameter("msgDtRcv", adDBTimeStamp,
> > > > adParamInput)
> > > > cmd.Parameters.Append pmDateRcv
> > > > Set pmDateTrans = cmd.CreateParameter("msgDtTrns",
adDBTimeStamp,
> > > > adParamInput)
> > > > cmd.Parameters.Append pmDateTrans
> > > > Call EventLog("saveinddb", "a")
> > > > Select Case way
> > > > Case "IN"
> > > > pmInOut.Value = INCOMMING
> > > > pmDateRcv.Value = msg.ReceivedTime
... read more »
|
| Sun, 01 Aug 2004 00:07:53 GMT |
|
| |
|
