@@Identity c# help...

I am trying to follow other examples I have seen on the site, and am still getting the

Must declare the scalar variable "@.@.INDENTITY".

string sqlAdd =string.Format("INSERT INTO " + siteCode +"_campaign_table (campaign_name, prod_id, type) "

+

"VALUES('{0}', '{1}', '{2}'); SELECT @.@.INDENTITY", campaignName, prodID, type);SqlCommand comAdd =newSqlCommand(sqlAdd, con);

comAdd.CommandType =

CommandType.Text;

con.Open();

//comAdd.ExecuteNonQuery();int identity;

identity =

Decimal.ToInt32((decimal)comAdd.ExecuteScalar());

lblErrorMessageAdd.Text = identity.ToString();

con.Close();

You have a typo. It should be@.@.IDENTITY

Also, your code should use parameters; what you have currently (placeholders for string replacement) is insecure and subject to SQL injection attacks.|||thanks, Corrected a few things there and works as intended.|||Actually, since you are using SQL Server, the best idea is to SELECT SCOPE_IDENTITY(). See, for example, this blog post for an explanation:SCOPE_IDENTITY() and @.@.IDENTITY Demystified. I should have pointed this out on my last post; I am sorry for missing it.

@@FETCH_STATUS loops?

I've seen in several examples where cursors are used particularly on
this board that people use
While @.@.FETCH_STATUS <> -1
BEGIN
IF @.@.FETCH_STATUS <> -2 BEGIN
..
END
END
On most of the Microsoft BOL examples I see that they seem to always
use...
WHILE @.@.FETCH_STATUS = 0
BEGIN
..
END
I have looked up the different meanings
"0 FETCH statement was successful.
-1 FETCH statement failed or the row was beyond the result set.
-2 Row fetched is missing."
http://msdn.microsoft.com/library/d... />
s_1c1f.asp
To get to the point, my question is why use one method over the other?
Is there some drawback to useing the "@.@.FETCH_STATUS = 0" method that
might not be apparent?
Do I need to worry about endless loops?
As you might surmise I have a UDF that is running much to long, and
when I look at the number of times my objects have been called, the
number is larger than it should be.Why? Consistency. Almost every fetch loop I've run across looks like this:
FETCH
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- do something here
FETCH
END
When you or the poor slob who has to maintain your code looks at it six
months, one, or two years from now, it will be a lot easier to read if it
uses the above pattern.
<Contraptor@.gmail.com> wrote in message
news:1132634990.886050.150040@.g49g2000cwa.googlegroups.com...
> I've seen in several examples where cursors are used particularly on
> this board that people use
> While @.@.FETCH_STATUS <> -1
> BEGIN
> IF @.@.FETCH_STATUS <> -2 BEGIN
> ...
> END
> END
> On most of the Microsoft BOL examples I see that they seem to always
> use...
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> ...
> END
> I have looked up the different meanings
> "0 FETCH statement was successful.
> -1 FETCH statement failed or the row was beyond the result set.
> -2 Row fetched is missing."
> http://msdn.microsoft.com/library/d...>
als_1c1f.asp
> To get to the point, my question is why use one method over the other?
> Is there some drawback to useing the "@.@.FETCH_STATUS = 0" method that
> might not be apparent?
> Do I need to worry about endless loops?
> As you might surmise I have a UDF that is running much to long, and
> when I look at the number of times my objects have been called, the
> number is larger than it should be.
>|||Ok, so al long as I'm not doing anything to change the @.@.FETCH_STATUS
between when I FETCH and check for a valid @.@.FETCH_STATUS I have no
need to worry about infinite loops if using the
FETCH
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- do something here
FETCH
END
method.
Now I just need to figure out why my UDF totally iced our test server
last night.

@@FETCH_STATUS

hi.
i have a question about @.@.FETCH_STATUS when doing a cursor loop.
i found 2 examples and i want to know what the diffrence is between
them,
the first one i found in the help:
DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName FROM Northwind.dbo.Employees
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
here it uses @.@.FETCH_STATUS = 0, wich would be while it is successfull.
the next exaple was an example in the QA:
DECLARE <cursor_name, sysname, test_cursor> CURSOR
READ_ONLY
FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>
DECLARE @.name varchar(40)
OPEN <cursor_name, sysname, test_cursor>
FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
-- PRINT 'add user defined code here'
-- eg.
DECLARE @.message varchar(100)
SELECT @.message = 'my name is: ' + @.name
PRINT @.message
END
FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
END
CLOSE <cursor_name, sysname, test_cursor>
DEALLOCATE <cursor_name, sysname, test_cursor>
here it first says, while @.@.FETCH_STATUS <> -2 and then says
only if @.@.FETCH_STATUS <> -1
in my world this would result in the same thing, since according to the
help the 3 diffrent statuses is 0, -1 and -2.
what is the diffrence between the two? and wich one is the "right" one
to use?
/Anders0
The FETCH statement was successful.
-1
The FETCH statement failed or the row was beyond the result set.
-2
The row fetched is missing.
-oj
<aebohlin@.gmail.com> wrote in message
news:1141228790.445782.79910@.v46g2000cwv.googlegroups.com...
> hi.
> i have a question about @.@.FETCH_STATUS when doing a cursor loop.
> i found 2 examples and i want to know what the diffrence is between
> them,
> the first one i found in the help:
> DECLARE Employee_Cursor CURSOR FOR
> SELECT LastName, FirstName FROM Northwind.dbo.Employees
> OPEN Employee_Cursor
> FETCH NEXT FROM Employee_Cursor
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM Employee_Cursor
> END
> CLOSE Employee_Cursor
> DEALLOCATE Employee_Cursor
> here it uses @.@.FETCH_STATUS = 0, wich would be while it is successfull.
> the next exaple was an example in the QA:
> DECLARE <cursor_name, sysname, test_cursor> CURSOR
> READ_ONLY
> FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>
> DECLARE @.name varchar(40)
> OPEN <cursor_name, sysname, test_cursor>
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> -- PRINT 'add user defined code here'
> -- eg.
> DECLARE @.message varchar(100)
> SELECT @.message = 'my name is: ' + @.name
> PRINT @.message
> END
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> END
> CLOSE <cursor_name, sysname, test_cursor>
> DEALLOCATE <cursor_name, sysname, test_cursor>
> here it first says, while @.@.FETCH_STATUS <> -2 and then says
> only if @.@.FETCH_STATUS <> -1
> in my world this would result in the same thing, since according to the
> help the 3 diffrent statuses is 0, -1 and -2.
> what is the diffrence between the two? and wich one is the "right" one
> to use?
> /Anders
>|||You don't need <>-2 checking for READONLY CURSOR. If the cursor is Ket-Set
driven cursor, you may read non-existing rows. Then you need to cehck for <>
-2
Refer "Cursor Types" in BOL
--
Thanks & Rate the Postings.
-Ravi-
"aebohlin@.gmail.com" wrote:

> hi.
> i have a question about @.@.FETCH_STATUS when doing a cursor loop.
> i found 2 examples and i want to know what the diffrence is between
> them,
> the first one i found in the help:
> DECLARE Employee_Cursor CURSOR FOR
> SELECT LastName, FirstName FROM Northwind.dbo.Employees
> OPEN Employee_Cursor
> FETCH NEXT FROM Employee_Cursor
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM Employee_Cursor
> END
> CLOSE Employee_Cursor
> DEALLOCATE Employee_Cursor
> here it uses @.@.FETCH_STATUS = 0, wich would be while it is successfull.
> the next exaple was an example in the QA:
> DECLARE <cursor_name, sysname, test_cursor> CURSOR
> READ_ONLY
> FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>
> DECLARE @.name varchar(40)
> OPEN <cursor_name, sysname, test_cursor>
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> -- PRINT 'add user defined code here'
> -- eg.
> DECLARE @.message varchar(100)
> SELECT @.message = 'my name is: ' + @.name
> PRINT @.message
> END
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> END
> CLOSE <cursor_name, sysname, test_cursor>
> DEALLOCATE <cursor_name, sysname, test_cursor>
> here it first says, while @.@.FETCH_STATUS <> -2 and then says
> only if @.@.FETCH_STATUS <> -1
> in my world this would result in the same thing, since according to the
> help the 3 diffrent statuses is 0, -1 and -2.
> what is the diffrence between the two? and wich one is the "right" one
> to use?
> /Anders
>|||thanks alot.sql

Hello,

the examples from MSDN like the following, don't run on my computer. A System.Data.SqlServer don't exist. No SQLCommand Type, no SQLContext.GetCommand Method and so on...

Ist there something wrong with my installation or how do i execute commands
inside a CLR SQL Server Trigger?

Regards,
Markus
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlServer
Imports System.Data.SqlTypes
Imports System.Text.RegularExpressions

Public Class EmailTrigger

Public Shared Sub EmailAudit()
Dim triggContext As SqlTriggerContext = _
SqlContext.GetTriggerContext()
Dim userName As String
If triggContext.TriggerAction = TriggerAction.Insert Then
Dim sqlComm As SqlCommand = SqlContext.GetCommand()
Dim sqlP As SqlPipe = SqlContext.GetPipe()
sqlComm.CommandText = "SELECT Username from INSERTED"
Dim dataRecord As Object = sqlComm.ExecuteScalar
userName = dataRecord.ToString
If IsValidEMailAddress(userName) Then
sqlComm.CommandText = "INSERT Emails " & _
"VALUES ('" & userName & "')"
sqlP.Send(sqlComm.CommandText)
sqlP.Execute(sqlComm)
End If
End If
End Sub

Public Shared Function IsValidEMailAddress(ByVal email As String) _
As Boolean
Return Regex.IsMatch(email, _
"^([\w-]+\.)*?[\w-]+@.[\w-]+\.([\w-]+\.)*?[\w]+$")
End Function

End Class

It seems that something like this would work:

Dim pipe As SqlPipe = SqlContext.Pipe
Dim sqlcommand As New System.Data.SqlClient.SqlCommand

sqlcommand.CommandText = _
"Update Haushaltshilfe set Wochentag = 'Montag' where Wochentag = 'Dienstag'"
pipe.ExecuteAndSend(sqlcommand)

sqlcommand.CommandText = "Select * from inserted"
pipe.ExecuteAndSend(sqlcommand)

But how do i get the results?

Is it really so that the examples from msdn with System.Data.SQLServer don't work?|||The reason why it's not working for you is because the example you're trying is old, and they have now merged the old SqlServer provider for System.Data.SqlServer with the client side provider and created a new namespace: Microsoft.SqlServer.Server. In addition some of the methods on the SqlContext object have been replaced with properties.
Replace the Import System.Data.SqlServer with Import Microsoft.SqlServer.Server and do an extra import for System.Data.SqlClient. Then in the code where you say GetTriggerContext, use the propery TriggerContext instead. you also need to create a connection: Dim sqlConn as SqlConnection = new SqlConnection("Context Connection=true") and the open the connection. From the connection you can create the command: sqlConn.CreateCommand. Instead of GetPipe you use the Pipe property and finally you call ExecuteAndSend on the Pipe with your command as param.
Niels

|||Thank you very much Niels!
Are there web-pages where the latest changes in VS 2005 and SQL Server are documented?|||You can check Bob B's blog and mine. We're trying keep track of what's changing.