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 |
|
|
|