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 from a coworker

How in the world can this possibly be valid syntax? It apparently works,
because QA is chewing on it now. Why not just simply say WHERE
LEN(MiddleName) = 35 ?

> SELECT *
> FROM CMR WITH (nolock)
> WHERE ({ fn LENGTH(MiddleName) } = 35)
> SELECT *
> FROM CMR WITH (nolock)
> WHERE ({ fn LENGTH(LastName) } = 35)
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James HetfieldThey are called "ODBC escape sequences".
For more informations, see:
http://msdn.microsoft.com/library/e...r_functions.asp
http://msdn.microsoft.com/library/e..._ar_ad_87sj.asp
Razvan|||{ fn ... } is ODBC syntax. For example, you can say SELECT { fn CURDATE() }
to get a string containing today's date in YYYY-MM-DD format.
I'm not sure why they would do it this way either; maybe to avoid LEN()
which is proprietary? Did you ask your coworker?
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:ep$dZZ1oFHA.3552@.TK2MSFTNGP10.phx.gbl...
> How in the world can this possibly be valid syntax? It apparently works,
> because QA is chewing on it now. Why not just simply say WHERE
> LEN(MiddleName) = 35 ?
>
>
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>|||> They are called "ODBC escape sequences".
> For more informations, see:
> http://msdn.microsoft.com/library/e...dbc.as
p
> http://msdn.microsoft.com/library/e...r_functions.asp
> http://msdn.microsoft.com/library/e..._ar_ad_87sj.asp
I cannot believe she would have written code like that. Since she works in
EM a lot, do you think this could have been code that EM generated from her
working in the graphical designer?
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||> I'm not sure why they would do it this way either; maybe to avoid LEN()
> which is proprietary? Did you ask your coworker?
This was something she did late last night, and she's not in today. And
yeah, she modified my VB code too. Outside of SourceSafe. It's been a
wonderful afternoon of tracking down and reconciling her changes. -- Changes
that don't even have a single bloody comment. @.#$%^&*!!!!
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||> I cannot believe she would have written code like that. Since she works
> in EM a lot, do you think this could have been code that EM generated from
> her working in the graphical designer?
EM does a lot of unnecessary and even bad things, but I don't think I've
ever seen it produce ODBC stuff.
Purely out of curiosity, why are the values where len=35 of particular
interest anyway?|||Maybe you could use the deprecated but still functional { fn
FIRING_SQUAD() }
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:O29ilk1oFHA.320@.TK2MSFTNGP09.phx.gbl...
> This was something she did late last night, and she's not in today. And
> yeah, she modified my VB code too. Outside of SourceSafe. It's been a
> wonderful afternoon of tracking down and reconciling her changes. --
> Changes that don't even have a single bloody comment. @.#$%^&*!!!!
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>|||"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:ep$dZZ1oFHA.3552@.TK2MSFTNGP10.phx.gbl...
> How in the world can this possibly be valid syntax? It apparently works,
> because QA is chewing on it now. Why not just simply say WHERE
> LEN(MiddleName) = 35 ?
>
>
In my experience, there is usually a reason when somebody wrote something
like this. It usually involves a bug they had to get around.
I think you should ask him why he did it.
... seriously.|||"John" <please.reply@.to.the.group.com> wrote in message
news:ut9d$m1oFHA.3408@.tk2msftngp13.phx.gbl...
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> news:ep$dZZ1oFHA.3552@.TK2MSFTNGP10.phx.gbl...
> In my experience, there is usually a reason when somebody wrote something
> like this. It usually involves a bug they had to get around.
> I think you should ask him why he did it.
> ... seriously.
>
I just read some of the other posts, and your response. And another idea
came to mind.
She may have cut & pasted code from somewhere not knowing what it was .. but
since it solved the problem ... she left it.
I guess it really depends on the knowledge level of your coworker.|||> Purely out of curiosity, why are the values where len=35 of particular
> interest anyway?
Business logic rule to truncate stuff to make sure it fits in the column
without exploding:
CREATE TABLE Contact (
-- other columns
LastName NVARCHAR(35)
)
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield

Coalesce with Sum

I am having a problem with syntax. I am trying to sum a column where some of the values will be null and because I want to include the rows where the column may be null I am attempting to coalesce to zero.

Below is my sample:

SELECT *

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY q.StudentID

Having

(SUM(Coalesce(q.AbsenceValue),0) = 0.00)

COALESCE(SUM(q.AbsenceValue) = 0.00,0)

I have tried using the coalesce statement a couple of ways with no resolution, pls help!!

Change to this:

COALESCE( q.AbsenceValue, 0)

|||Ok, but how does that incorporate summing the column?|||

Try something like this: (in case you need the student name from your student table)

SELECT w.StudentID, w.StudentName, SUM(Coalesce(q.AbsenceValue,0) ) AS sumAbsenceValue

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY w.StudentID, w.StudentName

But you don't need to do the coalesce: SUM and AVG will skip the NULL value in the caculation.

The follwing should return the same result:

SELECT w.StudentID, w.StudentName, SUM(q.AbsenceValue) AS sumAbsenceValue

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY w.StudentID, w.StudentName

|||

Thanks for putting me on the right track. I actually got the result I needed by modifying your first example a little.

SELECT w.StudentID, SUM(Coalesce(q.AbsenceValue,0) ) AS sumAbsenceValue

FROM dbo.Student w

LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID

Group BY w.StudentID

Having SUM(Coalesce(q.AbsenceValue,0) ) = 0.00

This gets me the desired result. I still needed to compare the result of the sum so that it equalled 0.00.

Thanks for setting me straight, I was about an inch from pulling hairs .