Geting event from ActiveX into database

Hello everyone,

I was wondering. Is it possible, to recive an event from ActiveX into
database?
I was looking alredy with notification services, but I think that's
the wrong way.

Lets say, that there is a ActiveX which realize some tasks.
The database trigers the ActiveX like below:

================================================== =============

declare @.iRetValint
declare @.iObjectint
declare @.sPropertyvarchar(2560)
declare @.sSource varchar(1000)
declare @.sDescription varchar(1000)
declare @.sLog varchar(1000)
declare @.dDateEVT datetime
declare @.sText1varchar(10)
declare @.sText2varchar(10)
declare @.iProperty int

set @.iObject = 0
set @.dDateEVT = getdate()
set @.iRetVal = 0
set @.sText1 = '0000000000'
set @.sText2 = '0000000000'
set @.iProperty = 7

exec @.iRetVal = sp_OACreate 'MComponent.pidMess', @.iObject OUTPUT,1
EXEC sp_OAGetErrorInfo @.iObject, @.sSource OUT, @.sDescription OUT
IF @.iRetVal <> 0
begin
set @.sLog = 'LOG1: No object created. Source: ' + @.sSource + '
Description: ' + @.sDescription
print @.sLog
end

-- Method
exec @.iRetVal = sp_OAMethod @.iObject,'Send_FromA', @.iProperty
OUT,@.nMessageNr = 5, @.bstrDateTime = @.dDateEVT, @.textFromA1 = @.sText1,
@.textFromA2 = @.sText2
EXEC sp_OAGetErrorInfo @.iObject, @.sSource OUT, @.sDescription OUT

IF @.iRetVal <> 0
begin
set @.sLog = 'LOG3: Source: ' + @.sSource + ' Description: ' +
@.sDescription
print @.sLog
end

PRINT 'Property from method:'
PRINT @.iProperty

================================================== =============

This function works properly. I recive the data into ActiveX.
After the ActiveX process the data, it returns the event, wit a
response for this call of method.

Now ... how do I can get, this event into SQL server?
Is it possible to do that enyhow, without acctions like:
- do, that the ActiveX writes the data in to a interface table, where
from it will be readed.

Any help would be appreciated

Mateuszmarzec@.sauron.xo.pl (Matik) wrote in message news:<8b6e9bf5.0408190717.705a3dec@.posting.google.com>...
> Hello everyone,
> I was wondering. Is it possible, to recive an event from ActiveX into
> database?
> I was looking alredy with notification services, but I think that's
> the wrong way.
> Lets say, that there is a ActiveX which realize some tasks.
> The database trigers the ActiveX like below:
> ================================================== =============
> declare @.iRetValint
> declare @.iObjectint
> declare @.sPropertyvarchar(2560)
> declare @.sSource varchar(1000)
> declare @.sDescription varchar(1000)
> declare @.sLog varchar(1000)
> declare @.dDateEVT datetime
> declare @.sText1varchar(10)
> declare @.sText2varchar(10)
> declare @.iProperty int
> set @.iObject = 0
> set @.dDateEVT = getdate()
> set @.iRetVal = 0
> set @.sText1 = '0000000000'
> set @.sText2 = '0000000000'
> set @.iProperty = 7
> exec @.iRetVal = sp_OACreate 'MComponent.pidMess', @.iObject OUTPUT,1
> EXEC sp_OAGetErrorInfo @.iObject, @.sSource OUT, @.sDescription OUT
> IF @.iRetVal <> 0
> begin
> set @.sLog = 'LOG1: No object created. Source: ' + @.sSource + '
> Description: ' + @.sDescription
> print @.sLog
> end
> -- Method
> exec @.iRetVal = sp_OAMethod @.iObject,'Send_FromA', @.iProperty
> OUT,@.nMessageNr = 5, @.bstrDateTime = @.dDateEVT, @.textFromA1 = @.sText1,
> @.textFromA2 = @.sText2
> EXEC sp_OAGetErrorInfo @.iObject, @.sSource OUT, @.sDescription OUT
> IF @.iRetVal <> 0
> begin
> set @.sLog = 'LOG3: Source: ' + @.sSource + ' Description: ' +
> @.sDescription
> print @.sLog
> end
> PRINT 'Property from method:'
> PRINT @.iProperty
> ================================================== =============
> This function works properly. I recive the data into ActiveX.
> After the ActiveX process the data, it returns the event, wit a
> response for this call of method.
> Now ... how do I can get, this event into SQL server?
> Is it possible to do that enyhow, without acctions like:
> - do, that the ActiveX writes the data in to a interface table, where
> from it will be readed.
> Any help would be appreciated
> Mateusz

I suspect this is not possible, since Books Online has no information
on event handling with the sp_OA% procedures. You should probably
consider using an external program or script to do what you need,
perhaps scheduled to poll a table on the server at intervals. I know
you mentioned that you're trying to avoid this, so if you need an
alternative approach you might want to give some more details of
exactly what you're trying to do, and why an "interface table" isn't a
good solution in your situation.

Simon|||Thank you for reply.

Well... that's the problem. The ActiveX shouldn't be changed any more,
and that's why I have no possibility to implement a function in it,
which writes the data in to interface table. That's why I do not want
such a solution.

I do some algorithm in the database, and in one step, I must 'ask'
ActiveX, for data. This data are the condition for the next steps in the
algorithm from DB.
Now, via sp_OAx procedures, it is possible, to interact with this
ActiveX to give it the data. But the ActiveX do not respond immiedetly,
only via event. It is immposible, to change the ActiveX, that on event,
it writes the data back into DB. I have the solution, that I can have
another program, which catches the event from ActiveX and then writes it
back into DB (or a program which will be called from sp_OAx, and waits
internaly for the ActiveX so long, that as return value can give the
values recived from ActiveX). But these are solutions, which I use
reluctantly.

But anyway ... The solution with table, want be bad, but then I must
have a while loop, til I become answer. I would like not to implement
such a solution.

The best way, as I said, will be, to get the event directly (anyhow) in
to DB, with mechanics and solutions of SQL DB.

Mateusz

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Mateusz Marzec (marzec@.sauron.xo.pl) writes:
> Well... that's the problem. The ActiveX shouldn't be changed any more,
> and that's why I have no possibility to implement a function in it,
> which writes the data in to interface table. That's why I do not want
> such a solution.
> I do some algorithm in the database, and in one step, I must 'ask'
> ActiveX, for data. This data are the condition for the next steps in the
> algorithm from DB.
> Now, via sp_OAx procedures, it is possible, to interact with this
> ActiveX to give it the data. But the ActiveX do not respond immiedetly,
> only via event. It is immposible, to change the ActiveX, that on event,
> it writes the data back into DB. I have the solution, that I can have
> another program, which catches the event from ActiveX and then writes it
> back into DB (or a program which will be called from sp_OAx, and waits
> internaly for the ActiveX so long, that as return value can give the
> values recived from ActiveX). But these are solutions, which I use
> reluctantly.

An event is essentially a callback, and T-SQL has no mechanism for this.
So if you can't change the COM module, you would have to write a wrapper
that reacts on the callback.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Get Scope Identity Value using ObjectDataSource and Vb.Net

Hi,

I have been trying to get the scope Identity after inserting a record using an ObjectDataSource.

I can't find what event, or how to get the value that the scope identity returns.

Here is my Sproc.

ALTER PROCEDUREdbo.[YourCompany_LanCustomer_Insert]

(

@.DNNUserIDint,

@.FirstNamenvarchar(50),

@.LastNamenvarchar(50),

@.Addressnvarchar(50),

@.Address2nvarchar(50),

@.Citynvarchar(50),

@.Statenvarchar(50),

@.Zipnvarchar(50),

@.EmailAddressnvarchar(50),

@.PhoneNumbernvarchar(50),

@.CustomerIDint OUTPUT

)

AS

INSERT INTOYourCompany_LanCustomer

(DNNUserID, FirstName, LastName, Address, Address2, City, State, Zip, EmailAddress, PhoneNumber, DateEntered)

VALUES(@.DNNUserID, @.FirstName, @.LastName, @.Address, @.Address2, @.City, @.State, @.Zip, @.EmailAddress, @.PhoneNumber,getdate())

SET@.CustomerID =Scope_Identity()

RETURN

When I try to execute the stored procedure in Sql Manager I get the CustomerID Value, how do I get this value in the VB.Net code behind?

Any help is greatly appreciated.

In the Inserted event of the ObjectDataSource and you use the OutputParameters collection to get the value

Protected Sub ObjectDataSource1_Inserted(ByVal senderAs Object,ByVal eAs ObjectDataSourceStatusEventArgs)Dim _customerIdAs Integer =CInt(e.OutputParameters("@.CustomerID"))End Sub

Thanks

-Mark post(s) as "Answer" that helped you

|||

You'll get the collection of the insert parameters for the data source. You can retrieve the value of any parameter using yourDataSourceID.InsertParameters("parameterName"). You haven't explained how you're executing the stored procedure here. If possible post the aspx page code so that we can know how you've setup the data source.

|||

Now I'm lost, do I get the Scope Identity at the iteminserting or the iteminserted. I tried the first sample and get a parameter is not equal error.

Here is my ascx (I'm using dotnetnuke) for the objectDataSource.

<asp:ObjectDataSourceID="ObjectDataSource_Customer"runat="server"TypeName="YourCompany.Modules.Lan.LanCustomerController"SelectMethod="LanCustomer_GetCustomers"DataObjectTypeName="YourCompany.Modules.Lan.LanCustomerInfo"DeleteMethod="LanCustomer_Delete"OldValuesParameterFormatString="original_{0}"InsertMethod="LanCustomer_Insert">

</asp:ObjectDataSource>

here is the code behind (vb.net) that I am using on insert:

ProtectedSub NewItem(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.FormViewInsertEventArgs)Handles CustomerFormView.ItemInserting

Try

e.Values.Item("CustomerId") = 0

If e.Values.Item("DNNUserID") ="-1"Then

e.Values.Item("DNNUserID") ="0"

Else

e.Values.Item("DNNUserID") = UserId

EndIf

If e.Values.Item("FirstName") =""Then

e.Values.Item("FirstName") = Null.NullString

EndIf

If e.Values.Item("LastName") =""Then

e.Values.Item("LastName") = Null.NullString

EndIf

If e.Values.Item("Address") =""Then

e.Values.Item("Address") = Null.NullString

EndIf

If e.Values.Item("Address2") =""Then

e.Values.Item("Address2") = Null.NullString

EndIf

If e.Values.Item("City") =""Then

e.Values.Item("City") = Null.NullString

EndIf

If e.Values.Item("State") =""Then

e.Values.Item("State") = Null.NullString

EndIf

If e.Values.Item("Zip") =""Then

e.Values.Item("Zip") = Null.NullString

EndIf

If e.Values.Item("EmailAddress") =""Then

e.Values.Item("EmailAddress") = Null.NullString

EndIf

If e.Values.Item("PhoneNumber") =""Then

e.Values.Item("PhoneNumber") = Null.NullString

EndIf

Catch exAs Exception

ProcessModuleLoadException(Me, ex)

EndTry

EndSub

The insert sproc worked before, I just can't get it to work now.

|||

Hi,

SET @.CustomerID = Scope_Identity()

From the code you provided, the @.CustomerID is an OUTPUT parameter you set, right?

And we assume that you are using SqlCommand to execute the stored procedure in your business object method, and then you can retrieve the OUTPUT parameter in stored procedure by declaring a SqlParameter which in an OUTPUT direction. Make your business object method return the parameter's value after you invoking ExecuteNonQuery() method.

And then, in ObjectDataSource1_Selected event, try to get the value from RetrunValue property of ObjectDataSourceStatusEventArgs.

Thanks.

|||

Nai-Dong Jin - MSFT:

you can retrieve the OUTPUT parameter in stored procedure by declaring a SqlParameter which in an OUTPUT direction. Make your business object method return the parameter's value after you invoking ExecuteNonQuery() method.

And then, in ObjectDataSource1_Selected event, try to get the value from RetrunValue property of ObjectDataSourceStatusEventArgs.

Why do we need to "RETURN" the "OUTPUT PARAMETER" ? Do you know that RETURN values and OUTPUT parameters are independent of each other and we could retrieve either "RETURN" value or "OUTPUT" parameter or both of them?

Note: I dont know how the thread was marked as "Answer"

Thanks

-Mark post(s) as "Answer" that helped you

|||

Hi e_screw,

First, I think you've misunderstood my words. What I suggest is to declare an OUTPUT parameter in his stored procedure, and then assign the parameter with the value of Identity_Scope(). That's all. What the rest is retrieving parameters in .NET application by using SqlParameter which is in OUTPUT direction. Is there anything wrong? In stored procedure level, can you find any words on "RETURN" in my previous post?

Make your business object method return the parameter's value after you invoking ExecuteNonQuery() method.

And since the original poster was using ObjectDataSource, so he must had invoked the ExecuteNonQuery() in the business object method, right? What I said "return the parameter's value" means return the value from the business method. In this stage, that's totally nothing related with the OUTPUT parameter in procs.

Now I'm lost,

Second,of course, you also can use "Return" to achieve that, but since the original poster was lost, kept asking against previous solution and no one followed up, I just provide another solution for him to refer.

So if you are able to help him further with your solution, I appreciate it. And it also can be beneficial to other community members reading the thread.

Thanks.

|||

This is your previous post:

Nai-Dong Jin - MSFT:

And then, in ObjectDataSource1_Selected event, try to get the value from RetrunValue property of ObjectDataSourceStatusEventArgs.

Last post:

Nai-Dong Jin - MSFT:

First, I think you've misunderstood my words. What I suggest is to declare an OUTPUT parameter in his stored procedure, and then assign the parameter with the value of Identity_Scope(). That's all. What the rest is retrieving parameters in .NET application by using SqlParameter which is in OUTPUT direction. Is there anything wrong? In stored procedure level, can you find any words on "RETURN" in my previous post?

In the first you said, get the value from the ReturnValue property , after assigning the value of OUTPUT parameters to it. In the second, you are just talking about OUTPUT parameters.

Have you had looked at the ObjectDataSourceStatusEventArgs, there is OutputParameters (which returns a collection of output parameters and their values) and a ReturnValue (which gets the return value returned by the business object, if any). Now read your replies again.

Note: Its not with my solution or your solution. Its all about a correct solution, which helps many other community members.

Thanks

|||

Hi,

To Dan5150,

Here's the sample code for you which describes the solution in my previous posts.

First, in your Procedure:

set ANSI_NULLSONset QUOTED_IDENTIFIERONgoALTER PROCEDURE [dbo].[ProcName]@.TOINSERTNVARCHAR(50),@.RESULTINT OUTPUT-- THE OUTPUT Parameter has been set as OUTPUTAS INSERT INTO MYTABLE(TOINSERT)VALUES (@.TOINSERT)SET@.RESULT = SCOPE_IDENTITY();

Second, here's the method in business object class:

Public Function BusinessMethod(ByVal TOINSERTAs String)As String Dim connAs String = ConfigurationManager.ConnectionStrings("SampleDbConnectionString").ConnectionStringDim myconnAs New SqlConnection(conn)Dim mycommAs New SqlCommand() mycomm.Connection = myconn mycomm.CommandText ="ProcName" mycomm.CommandType = CommandType.StoredProcedureDim sp1As New SqlParameter() sp1.ParameterName ="TOINSERT" sp1.Value = TOINSERTDim sp2As New SqlParameter() sp2.ParameterName ="RESULT"' This parameter has been set in OUTPUT direction sp2.Direction = ParameterDirection.Output sp2.Size = 4 sp2.SqlDbType = SqlDbType.Int mycomm.Parameters.Add(sp1) mycomm.Parameters.Add(sp2) myconn.Open() mycomm.ExecuteNonQuery() myconn.close()' Return the parameter in OUTPUT direction.Return sp2.Value.ToString()End Function

Third, you can get the value in Inserted event of ODS by accessing ReturnValue property.

Protected Sub ObjectDataSource1_Inserted(ByVal senderAs Object,ByVal eAs ObjectDataSourceStatusEventArgs) Response.Write(e.ReturnValue.ToString())' You can get the id of new inserted row here.End Sub

To e_screw,

Please read my codes, and especially the comment parts in bold. And let's back to your solution which given in the second post:

Protected Sub ObjectDataSource1_Inserted(ByVal sender As Object, ByVal e As ObjectDataSourceStatusEventArgs)
Dim _customerId As Integer = CInt(e.OutputParameters("@.CustomerID"))
End Sub

You can use OutputParameters collection to retrieve the value, while output parameters would be ByRef (out in C#) parameters.

But since the original poster hadn't posted out his business method signature, how can you make sure that he was declaring parameters that are passed to the business object method by reference? If the parameters was passed by val, how could he get the value in OutputParameters collection?

Thanks.

Get Minimum day in a continuous series

Hi,

l've a series of day which record the date of an event. l would like to count the # of continuous days for the event. In this case, it would be 14/5, 15/5, 16/5, 17/5, 18/5, 19/5 and 20/5. Any idea to do this in SQL?

Date
--
20/5
19/5
18/5
17/5
16/5
15/5
14/5
09/5
07/5
06/5
05/5
And what about May/5 May/6 and May/7?.. those also meet your request.|||

The only thing that comes to mind is to use a cursor to loop through your rows.

You can then check each row to determine if it is exactly one day beyond the last row.

Do you just want to get a count of the highest number of days? Or what do you want to return?

|||

If you are using SQL Server 2005 you
can do this (this can be modified to work
with SQL Server 2000 if required)

set dateformat dmy
create table #dates(dt datetime)

insert into #dates(dt) values('20/5/2006')
insert into #dates(dt) values('19/5/2006')
insert into #dates(dt) values('18/5/2006')
insert into #dates(dt) values('17/5/2006')
insert into #dates(dt) values('16/5/2006')
insert into #dates(dt) values('15/5/2006')
insert into #dates(dt) values('14/5/2006')
insert into #dates(dt) values('09/5/2006')
insert into #dates(dt) values('07/5/2006')
insert into #dates(dt) values('06/5/2006')
insert into #dates(dt) values('05/5/2006');

with dt_rn(dt,rn)
as
(select dt,
dt-rank() over(order by dt)
from #dates)
select convert(char(5),min(dt),103) as EventStart,
count(*) as ContinuousDays
from dt_rn
group by rn
having count(*)>1

drop table #dates

|||

Hello

To get the Minimum day of each sequence you could try this...

drop table #dates

set dateformat dmy
create table #dates(dt datetime)

insert into #dates(dt) values('20/5/2006')
insert into #dates(dt) values('19/5/2006')
insert into #dates(dt) values('18/5/2006')
insert into #dates(dt) values('17/5/2006')
insert into #dates(dt) values('16/5/2006')
insert into #dates(dt) values('15/5/2006')
insert into #dates(dt) values('14/5/2006')
insert into #dates(dt) values('09/5/2006')
insert into #dates(dt) values('07/5/2006')
insert into #dates(dt) values('06/5/2006')
insert into #dates(dt) values('05/5/2006')

select * from #dates od where not exists (select dt from #dates id where id.dt = od.dt-1)
and exists (select dt from #dates id2 where id2.dt = od.dt+1)

The 2nd Part of the where clause is needed to eliminate the 09/05 date... If you consider that single date a "sequence" also then remove the 2nd part.

Be warned though that this querry can eat up a lot of performance.

|||this query must return the event start date and count of continous event dates. you must to replace date_column and table_name

select start_date, count(event_date) as cnt from (
select date_column as event_date,
(select max(date_column) from (
select date_column
from table_name t1
left outer join table_name t2
on t1.date_column=dateadd(t2,1,date_column)
where t2.date is null
) as start_dates
where start_dates.date_column<=events.date_column
) as start_date
from table_name events
) as event_dates
group by event_start_date