code to return a dataset

Our database is normalized to the point that I need to build a dataset to
render a report from. The code to build the dataset has logic that I would
like to keep in a custom assembly. What is the best way to call that code
and have it pass the dataset back for use by the report?
Thanks,
ShawnYou'd make life alot easier for yourself if you build the code using a
stored procedure... Any reason not to? You can encrypt it if that is your
concern.
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"sysdesigner" <sysdesigner@.discussions.microsoft.com> wrote in message
news:AA8B7D2A-4A54-4BA8-B7F5-598FFF1BBCF1@.microsoft.com...
> Our database is normalized to the point that I need to build a dataset to
> render a report from. The code to build the dataset has logic that I
> would
> like to keep in a custom assembly. What is the best way to call that code
> and have it pass the dataset back for use by the report?
> Thanks,
> Shawn|||Thanks, but the users that will be implementing the reports won't have the
ability to make stored procedures. Is there another way?
"Mary Bray [MVP]" wrote:
> You'd make life alot easier for yourself if you build the code using a
> stored procedure... Any reason not to? You can encrypt it if that is your
> concern.
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "sysdesigner" <sysdesigner@.discussions.microsoft.com> wrote in message
> news:AA8B7D2A-4A54-4BA8-B7F5-598FFF1BBCF1@.microsoft.com...
> > Our database is normalized to the point that I need to build a dataset to
> > render a report from. The code to build the dataset has logic that I
> > would
> > like to keep in a custom assembly. What is the best way to call that code
> > and have it pass the dataset back for use by the report?
> >
> > Thanks,
> > Shawn
>
>

Code inside! --> How to return the @@identity parameter without using stored procedures

Hi.
here is my code with my problem described in the syntax.
I am using asp.net 1.1 and VB.NET
Thanks in advance for your help.
I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row.

PublicFunction howToReturnID(ByVal aCompanyAsString,ByVal aNameAsString)AsInteger

'that is the variable for the new id.
Dim intNewIDAsInteger

Dim strSQLAsString ="INSERT INTO tblAnfragen(aCompany, aName)" & _
"VALUES (@.aCompany, @.aName); SELECT @.NewID = @.@.identity"

Dim dbConnectionAs SqlConnection =New SqlConnection(connectionString)
Dim dbCommandAs SqlCommand =New SqlCommand()
dbCommand.CommandText = strSQL

'Here is my problem.
'What do I have to do in order to add the parameter @.NewID and
'how do I read and return the value of @.NewID within that function howToReturnID
'any help is greatly appreciated!
'I cannot use SPs in this application - have to do it this way! :-(

dbCommand.Parameters.Add("@.aFirma", aCompany.Trim)
dbCommand.Parameters.Add("@.aAnsprAnrede", aName.Trim)

dbCommand.Connection = dbConnection

Try
dbConnection.Open()
dbCommand.ExecuteNonQuery()

'here i want to return the new ID!
Return intNewID

Catch exAs Exception

ThrowNew System.Exception("Error: " & ex.Message.ToString())

Finally

dbCommand.Dispose()
dbConnection.Close()
dbConnection.Dispose()

EndTry

EndFunction

Why don't you put your SQL statement something like this;
Insert Into table (col1, col2) Values (1, 2); Select @.@.IDENTITY
And from there, to retrieve the @.@.IDENTITY, you will execute a scalar return. For example:
Dim identity As Integer = Convert.ToInt32(command.ExecuteScalar())
|||

Hi,

thank you very much for your help.
I tried your suggestion but got always two rows inserted.

Obviously the command object executed the insert statement two times?

first here: db.command.ExecuteNonQuery()
and here:Dim identity As Integer = Convert.ToInt32(command.ExecuteScalar())
this is what I did:
Is this correct ? -at least it works ;-) - but is it the "right way" to do it?
PublicFunction howToReturnID(ByVal aCompanyAsString,ByVal aNameAsString)AsInteger

'that is the variable for the new id.
Dim intNewIDAsInteger

Dim strSQLAsString ="INSERT INTO tblAnfragen(aCompany, aName)" & _
"VALUES (@.aCompany, @.aName);"

'I separated the SQL query string
Dim strSQL2AsString ="Select @.@.IDENTITY;"

Dim dbConnectionAs SqlConnection =New SqlConnection(connectionString)
Dim dbCommandAs SqlCommand =New SqlCommand()
dbCommand.CommandText = strSQL

dbCommand.Parameters.Add("@.aFirma", aCompany.Trim)
dbCommand.Parameters.Add("@.aAnsprAnrede", aName.Trim)

dbCommand.Connection = dbConnection

Try
dbConnection.Open()
'execute first query
dbCommand.ExecuteNonQuery()

'execute second query - this actually returns the id of the currently inserted row.
'But is this the CORRECT WAY to do it?? Any objections?
'this solution only inserts one row not two - as it did when the SQL-query was in one string
dbCommand.CommandText = strSQL2
newID = Convert.ToInt32(dbCommand.ExecuteScalar)

Return intNewID

Catch exAs Exception

ThrowNew System.Exception("Error: " & ex.Message.ToString())

Finally

dbCommand.Dispose()
dbConnection.Close()
dbConnection.Dispose()

EndTry

EndFunction

|||Don't call the ExecuteNonQuery() method. Every Execute*() method that you call runs it as a completely new query, if you know what I mean.
Regards,
Justin|||Well, not really
You mean the .ExecuteScalar method of the command object does also run the insert query?
If so why is there a ExecuteNonQuery method at all?
Is the way I did it in my second source code example not recommendable?
But it works for me - or is there a good reason not to do it that way (performance issues, etc.) ?|||Well, with your second code example, you have described what is wrong with it - it inserts the same data twice into the table. So this breaks logics reason. Besides calling the Execute*() twice, I don't see anything else wrong with the second code posting.
The reason why we have the three Execute() methods (ExecuteNonQuery, ExecuteReader, and ExecuteScalar) on the commands are that each one tells the 'executer' (loosely saying it here) on what type of result to expect. With ExecuteNonQuery(), it returns how many rows were affected. With ExecuteScalar() method, you are expect a simple value type to be returned. And with the ExecuteReader(), it returns a data reader...
They all 'execute' but you have to decide on what information you need to retrieve from execution of that command. Am I making sense now?|||Hi,
yes, everything works now the way it is supposed to :)
Thank you very much for your help!
But in my second code example that i have posted it does not insert the row twice ;-)
Just take a closer look at it - I have provided the command object with a second query that only selects @.@.identity .
But I did that "workaround" because I didn't know that executeScalar also "executes" insert queries ...!
My problem is solved now!|||You are right. I did overlook that. Silly me. Anyway, it was a pleasure.

Code for 1st day & the Last day of the Previous Month

Hi there,

See if you can help me with the following:

I need to write an SQL code that will return me:

The 1st day & the Last day of the Previous Month in the following format
(smalldatetime):

yyyy-mm-dd hh:mi:ss (24h)

Regards,

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200507/1If you don't have a calendar table (which can be very helpful for
situations like this), you can do it on-the-fly using SQL Server like
so:

SELECT DATEADD(m, -1, FirstOfCurrentMonth) as FirstOfPreviousMonth,
DATEADD(d, -1, FirstOfCurrentMonth) as LastOfPreviousMonth
FROM (SELECT CONVERT(smalldatetime,DATENAME(m, GetDate())
+ ' 1,' + DATENAME(yyyy, GETDATE())) as FirstOfCurrentMonth) a

Of course, you'll want to do the formatting on the client side.

HTH,
Stu|||On Wed, 20 Jul 2005 16:56:42 GMT, Ray via SQLMonster.com wrote:

> Hi there,
> See if you can help me with the following:
> I need to write an SQL code that will return me:
> The 1st day & the Last day of the Previous Month in the following format
> (smalldatetime):
> yyyy-mm-dd hh:mi:ss (24h)
> Regards,

declare @.d1 smalldatetime
declare @.d2 smalldatetime
declare @.d3 smalldatetime

-- @.d1 is the input date
set @.d1 = CURRENT_TIMESTAMP
-- truncate hours, min, etc.
set @.d1 = convert(smalldatetime, floor(convert(float, @.d1)))

-- @.d2 - last day of previous month

set @.d2 = dateadd(day, - datepart(day, @.d1), @.d1)

-- @.d3 - first day of previous month

set @.d3 = dateadd(day, - datepart(day, @.d2) + 1, @.d2)|||Hi Stu,

Thank you very very much .... it was a great help.

Best regards,

Stu wrote:
>If you don't have a calendar table (which can be very helpful for
>situations like this), you can do it on-the-fly using SQL Server like
>so:
>SELECT DATEADD(m, -1, FirstOfCurrentMonth) as FirstOfPreviousMonth,
>DATEADD(d, -1, FirstOfCurrentMonth) as LastOfPreviousMonth
>FROM (SELECT CONVERT(smalldatetime,DATENAME(m, GetDate())
> + ' 1,' + DATENAME(yyyy, GETDATE())) as FirstOfCurrentMonth) a
>Of course, you'll want to do the formatting on the client side.
>HTH,
>Stu

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200507/1|||Build a calendar table with all the temporal information you need
instead of trying to compute it on the fly. Next, the display of
temporal data has nothing to do how it is stored.|||Ross,

Thank you so much ...

Ross Presser wrote:
>> Hi there,
>>
>[quoted text clipped - 8 lines]
>>
>> Regards,
>declare @.d1 smalldatetime
>declare @.d2 smalldatetime
>declare @.d3 smalldatetime
>-- @.d1 is the input date
>set @.d1 = CURRENT_TIMESTAMP
>-- truncate hours, min, etc.
>set @.d1 = convert(smalldatetime, floor(convert(float, @.d1)))
>-- @.d2 - last day of previous month
>set @.d2 = dateadd(day, - datepart(day, @.d1), @.d1)
>-- @.d3 - first day of previous month
>set @.d3 = dateadd(day, - datepart(day, @.d2) + 1, @.d2)

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200507/1

Code a function to return a dataset in which there are two tables and relationship

I used a function to create dataset as below:

PublicFunction GetSQLDataSet(ByVal SQLAsString)As DataSet

.....

MyConnection =

New SqlConnection(MyConnectionString)

MyCommand =

New SqlCommand(SQL, MyConnection)

MyDataSet =

New DataSet

MySQLDataAdapter =

New SqlDataAdapter(MyCommand)

MySQLDataAdapter.Fill(MyDataSet)

.....

End function

It works fine.

How to code a function to return a dataset in which there are two tables and relationship?

You can use a SqlCommand which returns 2 tables--that can be a stored procedure which returns 2 tables, or a batch contains 2 SELECT commands seperated by ';' (e.g. "select * from t1; select * from t2). Then when you use the SqlDataAdapter to fill the DataSet, the DataSet will contain 2 tables. Then you can add relationships between the 2 tables as you like, you can refer to:

http://msdn2.microsoft.com/en-us/library/ay82azad.aspx

coalesce does not seem to work

Hi,

I have the following table with some sample values, I want to return the first non null value in that order. COALESCE does not seem to work for me, it does not return the 3rd record. I need to include this in my select statement. Any urgent help please.

Mobile Business Private

NULL 345 NULL
4646 65464 65765
NULL 564
654654 564 6546

I want the following as my results:

Number

345
4646
564
654654

Select COALESCE(Mobile,Business,Private) as Number from Table returns:

345
4646

654654

(this is a test to see if private returns & it did with is not null but then how do i include in my select statement to show any one of the 3 fields)

select mobile,business,private where private is not null returns:

65765
564
6546

thanks

As you mentioned, COALESCE returns the first Non NULL value. You result is not what you want but the COALESCE is correct. You have a blank cell in your table. It is not NULL. You can use a CASE statement to check either NULL or blank to get the result you want. Or you can make sure your missing value cells are NULL.

HTH.

Coalesce / Comma Delimitted List

I'm trying to return multiple column, one of which is a column + a comma delimitted list of values. Below is a simplified, non working, query:

(assume @.MyBit, @.MyVarChar, and @.MyValue are all declared)

SELECT distinct
IsNull(Column1, '') AS Column1,
IsNull(Column2,'') + CASE WHEN @.MyBit = 1 THEN ' My Test: ' + (select @.MyVarChar = COALESCE(Column3 + ', ', '') from TableSub where Value = @.MyValue) ELSE '' END AS Column2,
FROM TableMain

I get an ADO error: Incorrect syntax near '='

Thoughts?

Thanks(select @.MyVarChar = COALESCE(Column3 + ', ', '') from TableSub where Value = @.MyValue)

use "[ ]" brackets instead of "( )" around the above select statment.

(and remove the last comma before the FROM stmnt)|||I now have:

SELECT distinct
IsNull(Column1, '') AS Column1,
IsNull(Column2,'') + CASE WHEN @.MyBit = 1 THEN ' My Test: ' + [select @.MyVarChar = COALESCE(Column3 + ', ', '') from TableSub where Value = @.MyValue] ELSE '' END AS Column2
FROM TableMain

and I get:
ADO error: Invalid column name 'select @.MyVarChar = COALESCE(Column3 + ',', '') from TableSub where Value = @.MyValue'

Thanks|||my apologies - here is the correct query

SELECT distinct
IsNull(Column1, '') AS Column1,
IsNull(Column2,'') + CASE WHEN @.MyBit = 1 THEN ' My Test: ' + (select COALESCE(Column3 + ', ', '') from TableSub where Value = @.MyValue) ELSE '' END AS Column2
FROM TableMain

put the "( )" back on and remove the @.MyVarChar = (that is where the err is)
you just need select coalesce...