Getting the primary keyy

I'm inserting a sort of data from a formview into the database. The primary key is set to increment automatically.

After the insertion I'm trying to get the primary key in order to use in another formview (same page) to insert data on different table.

The code:

asp:SqlDataSourceID="newSchemaSqlDataSource"runat="server"ConnectionString="<%$ ConnectionStrings:logprocConnectionString1 %>"

InsertCommand="INSERT INTO [LogSchema] ([Title], [Type], [InitPattern], [EndPattern], [SetupDate]) VALUES (@.Title, @.Type, @.InitPattern, @.EndPattern, @.SetupDate); SELECT @.NewID = SCOPE_IDENTITY()" OnInserted="newSchemaSqlDataSource_Inserted">

<InsertParameters>

<asp:ParameterName="Title"Type="String"/>

<asp:ParameterName="Type"Type="String"/>

<asp:ParameterName="InitPattern"Type="String"/>

<asp:ParameterName="EndPattern"Type="String"/>

<asp:ParameterName="SetupDate"Type="DateTime"/>

<asp:ParameterName="NewID"Type="Int32"/>

</InsertParameters>

Backend:

protectedvoid newSchemaSqlDataSource_Inserted(object sender,SqlDataSourceStatusEventArgs e)

{

int newid = (int)e.Command.Parameters["@.NewID"].Value;

Response.Write(newid.ToString());

}

The problem is the e.Command.Parameters["@.NewID"].Value is returning NULL as I could see at the debug. What am I missing?

Try setting the Direction property of your "NewID" Parameter to "Output".

<asp:parameter direction="Output" name="NewID" type="Int32" />

|||Thanks a lot... it works!!!!

Getting the corresponding primary key from MAX()?

I think this should be easy, but maybe my brain is just not working today,
can anyone offer suggestions?
I have a table, Table1 (simplified):
ID dollars
1 15
2 30
4 22
Using T-SQL, how can I get the ID value for the record with the highest
number in the dollars column?
"Select MAX(dollars) from Table1" gives me the actual highest value, but I
need to know the record from where that value came from.
Any thoughts?select ID from Table1 where dollars=(Select MAX(dollars) from Table1)
Note that unless there is a unique constraint on the dollars column,
you may get multiple rows back.|||That's what I needed...thanks.
<markc600@.hotmail.com> wrote in message
news:1134845143.777978.193910@.g14g2000cwa.googlegroups.com...
> select ID from Table1 where dollars=(Select MAX(dollars) from Table1)
> Note that unless there is a unique constraint on the dollars column,
> you may get multiple rows back.
>

getting sql query in c# code

Dear All

I am a beginner and looking for some help. I have a database with just one column (some names). That is the primary key aswell. Because I want the names to be unique.

I used a grid view control to display the data and included the insert functionality in the grid view by using some code and the part of the code that does the insert is

1public static void Insert(Categories category)2 {3string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;4using (IDbConnection cn =new SqlConnection(connectionString))5 {6 cn.Open();78 IDbCommand cmd =new SqlCommand();910 cmd.CommandText ="INSERT INTO Categories (CategoryName) VALUES " +11"('" + category.CategoryName +"')";1213 cmd.Connection = cn;14 cmd.ExecuteNonQuery();15 }16 }

Question: when someone tried to enter a new name which already exists in the database it throws an error page which is what I want but is there a way to be able to display the user a message sayin g that he/she has entered a name that already exists and hence they need to try a different name? instead of the ugly error page?

Thank you in advance,

Prasad.

Yes. You need to add a Try... Catch block to catch the exception, and handle it:

public static void Insert(Categories category)
{
string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection cn = new SqlConnection(connectionString))
{
try
{
SqlCommand cmd = new SqlCommand();

cmd.CommandText = "INSERT INTO Categories (CategoryName) VALUES (@.CategoryName)
cmd.Parameters.AddWithValue("@.CategoryName", category.CategoryName);

cmd.Connection = cn;
cn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Label1.Text = "That value already exists. Please try another.";
}
}

I have also changed your code to use parameters, which is best practice. This means you don't have to worry about people entering words with apostrophes causing errors, or Sql Injection.

Also, you should ideally query the database to see if the name exists prior to attempting the insert. Throwing exceptions is expensive on the server.

|||

thank you. Hmm..I knew the try catch solution but I was just thinking...that the try catch would catch any exception that came from insert right?

For example if there was some other strange problem in doing the insert, it would still say "name already exists try another name!" which will be kind of strange.

lastly, Thank you for the modified code, I am a newbie and still learning, thanks for the tip! However, when I compile your code, it says

System.Data.IDataParameterCollection' does not contain a definition for 'AddWithValue'

can you please tell me how to check if that name already exists? some code would help please.

something like doing a count and on that particular name and see if count is > 1?

|||

Are you using version 1.1? If so, change that to cmd.Parameters.Add(...). AddWithValue was added to version 2.0.

Instead of hardcoding the error message, you could also use ex.Message

Label1.Text = ex.Message;

However, sometimes the error message might be too obscure for the user to understand. The most likely error will result from an attempt to insert a duplicate value. That's why I recommended that you select the value of the submitted entry first to see if it exists. If it does, abort the insert and show a message. If not, let the insert run.

|||

my mistake, I had not replaced the IDBcommand with SqlCommand

ok now coming to the second problem,

I donot know how to count the number of times the name appears in the database!

can you get me started with the code!

|||

The SQL is "Select Count(*) AS TheCount From Categories Where CategoryName = @.CategoryName"

int theCount = (int)cmd.ExecuteScalar();

Then, if theCount is more than 0, that means there is a least one row already in the database. If it doesn't exist, the value of theCount will be 0.

|||

"For example if there was some other strange problem in doing the insert, it would still say "name already exists try another name!" which will be kind of strange."

That is because you have this column as the Primary Key. The Primary Key has to be unique (ie No Duplicates)

|||

You should use a stored procedure for this kind of operations. There you can first write the code for checking for duplicate values like below.

ifexists (select 1from Categorieswhere CategoryName = @.categoryName)beginraiserror ('The provided category name already exists' , 16 , 1 )with nowaitreturnendelsebegininsert Categories (CategoryName)values (@.categoryname)end

This is just a snap of what can be included in the SP. Look at the line where raiserror function is used. You'll still have to write the SP execution code in a try catch block, but you're sure that the error stating "The provided category name already exists" is generated by your SP and all other errors are because of other problems.

For a general help regarding executing SP from code visithttp://forums.asp.net/t/1165758.aspx.

Hope this will help.

getting primary key out of insert into statement

Is there a way to get the primary key that is causing a primary key violation when inserting via a insert into with a select statement. This violation is being caught using a try/catch statement. Is there anyway, short of doing yourself via brute force coding, to get what the values are that are causing the error. Basically, I'm doing:

begin try

insert into TableA

select col1, col2, col3

from TableB

end try

begin catch

--Catch occasional primary key violation.

end catch

not sure if this is what you want to do, but have you considered something like the following?

lets say you have a schema like this (psuedo metadata for brevity sake) and you want to insert table1 into table2

[NB: may have errors as I didn't run this through QA, but the concept is sound]

legend: TableName , primary key fields

table1
A1
A2
A3

table2
B1
B2
B3

then the table1 records that will be a violation can be selected via

select distinct table1.* into #violations
from table1 inner join table2
on table1.A1 = table2.B1
and table1.A2 = table2.B2

Then you can insert the valid records via:

insert into table2
select table1.*
from table1 left join table2
on table1.A1 = table2.B1
and table1.A2 = table2.B2
where table2.B1 is null

then, you can do

select * from #violations

and do what ever you want with these - write to another table/send in an email, etc. . .

lastly you will want to delete the temporary #violations table

drop table #violations

|||Check out the OUTPUT option of the INSERT statement.

getting primary key id on insert

i have the following code in visual studio 2005 using VB
it is running an insert query - this works fine but i want to know how can i get the primaty key value(which is auto generated) of the row that i just inserted...

Dim conn As New SqlConnection(My.Settings.connStr)

conn.Open()

Dim sql As String = "INSERT INTO tblProspect (Prspct_FirstName, Prspct_LastName, Prspct_PropIDPrimary, Prspct_PropIDSecondary, Prspct_ApplicationStatus, Prspct_DateSubmittedOn, Prspct_PrimaryRent, Prspct_SecondaryRent, Prspct_MoveInDate) VALUES ('" & Me.txtFName.Text & "','" & Me.txtLName.Text & "','" & Me.cmbPrimary.SelectedValue & "','" & Me.cmbSecondary.SelectedValue & "','Pending','" & Now & "','" & Me.txtPrimRent.Text & "','" & Me.txtSecRent.Text & "','" & Me.dtMoveIn.Value & "')"

Dim cmd As New SqlCommand(sql, conn)

cmd.ExecuteNonQuery()

i want to get the Prspct_Id which is the primary key of the row that i just inserted..

thanks

Your auto generated key is referred to as an IDENTITY column in SQL Server. The way you obtain its value is to follow the insert with a SELECT of one of the @.@.IDENTITY server variables (actually these are really system functions but behave like variables) - data type numeric(38,0): @.@.IDENTITY, @.@.IDENT_CURRENT('table_name'), or @.@.SCOPE_IDENTITY. See http://msdn2.microsoft.com/en-us/library/ms187342.aspx for more information.

However, rather than firing off two adhoc queries I would define these two steps in a stored procedure and call that, and have it return the identity as an output variable or a simple return value.

Also, your VB as it stands looks as though it leaves you wide open to a SQL injection attack - as a general rule one should use the parameterised approach rather than building up query strings that include directly concatenated values taken straight from input fields. Here's an example of this approach (it's in C# rather than VB I'm afraid, but you should get the gist regardless).

SqlConnection dbConn =

new SqlConnection(connString);

dbConn.Open();

try

{

SqlCommand command = dbConn.CreateCommand();

command.CommandText =

"insert into tblProspect([Prspct_FirstName], [Prspct_LastName])" +

"values (@.firstName, @.lastName)";

command.Parameters.AddWithValue("@.firstName", txtFName.Text().Trim());

command.Parameters.AddWithValue("@.lastName", txtLName.Text().Trim());

command.ExecuteNonQuery();

}

finally

{

dbConn.Close();

}

|||

hi,

Michael is absolutely right about the dynamic SQL code and relative SQL Injection perils, and you should at least use a parametrized execution query with relative parameters instead of simple positional substitution of value markers combining the execution string...

so the sqlString = " .... VALUES ( '" & me.txtBox.text &"');" should be avoided as evil

and Michael is again absolutely right advising for a stored procedure instead of dynamic SQL...

but, just as didactival sample, you can write

Private Sub ParametQuery()

Dim conn As New System.Data.SqlClient.SqlConnection

With conn

.ConnectionString = "Server=(Local);Database=tempdb;Trusted_Connection=True;"

.Open()

End With

Dim cmd As New System.Data.SqlClient.SqlCommand

With cmd

.CommandText = "CREATE TABLE dbo.TestTB ( Id int NOT NULL IDENTITY PRIMARY KEY, Data varchar(10) NOT NULL );"

.CommandType = CommandType.Text

.Connection = conn

End With

cmd.ExecuteNonQuery()

For i As Integer = 1 To 5

cmd = New System.Data.SqlClient.SqlCommand

With cmd

.CommandText = "INSERT INTO dbo.TestTB VALUES ( @.Data ); SELECT @.NewId = SCOPE_IDENTITY();"

.CommandType = CommandType.Text

.Connection = conn

Dim par As System.Data.SqlClient.SqlParameter

par = New System.Data.SqlClient.SqlParameter

With par

.Direction = ParameterDirection.Input

.ParameterName = "@.Data"

.SqlDbType = SqlDbType.VarChar

.Size = i.ToString.Length

.SqlValue = i.ToString

End With

.Parameters.Add(par)

par = New System.Data.SqlClient.SqlParameter

With par

.Direction = ParameterDirection.Output

.ParameterName = "@.NewId"

.SqlDbType = SqlDbType.Int

End With

.Parameters.Add(par)

par = Nothing

End With

cmd.ExecuteNonQuery()

Console.WriteLine("Retrieving NewId from parameter: value = {0}", cmd.Parameters("@.NewId").Value)

Next

cmd = New System.Data.SqlClient.SqlCommand

With cmd

.CommandText = "DROP TABLE dbo.TestTB;"

.CommandType = CommandType.Text

.Connection = conn

End With

cmd.ExecuteNonQuery()

cmd.Dispose()

conn.Dispose()

cmd = Nothing

conn = Nothing

End Sub

a parameter collection is defined, passing the "in" value to be used for insertion for the [Data] column..
as SQL Server supports multistatement execution in execution queries as well (even in stored procedures), you can get the new autogenerated IDENTITY value via already pointed out SCOPE_IDENTITY() built in function.. so you can add an additional parameter to your execution query which will be loaded with the result of
SELECT @.NewId = SCOPE_IDENTITY();
and, after the command has been executed, you can inspect the parameter value for your personal uses as desired..

regards

|||

thanks for the help;

i am kinda new to this and i am just modifying a previously written code.. so i dont want to make and big changes cause i dont know where else i would then need to modify - how can i use SCOPE_IDENTITY() with my current code?

i can add SELECT SCOPE_IDENTITY() AS LASTID at the end of the insert statement, and the how do i read or copy the value of LASTID to another variable

thanks

|||

hi,

imranmp wrote:

thanks for the help;

i am kinda new to this and i am just modifying a previously written code.. so i dont want to make and big changes cause i dont know where else i would then need to modify - how can i use SCOPE_IDENTITY() with my current code?

i can add SELECT SCOPE_IDENTITY() AS LASTID at the end of the insert statement, and the how do i read or copy the value of LASTID to another variable

thanks

very bad

as you say you are new, you should learn the "good" way and not write code just for having the job done

anyway, if you really want to go that way just replace the .ExecuteNonQuery method with a .ExecuteScalar. similar to

Dim conn As New System.Data.SqlClient.SqlConnection

With conn

.ConnectionString = "Server=(Local);Database=tempdb;Trusted_Connection=True;Connection Timeout=5;"

.Open()

End With

Dim cmd As New System.Data.SqlClient.SqlCommand

With cmd

.CommandText = "CREATE TABLE dbo.TestTB ( Id int NOT NULL IDENTITY PRIMARY KEY, Data varchar(10) NOT NULL );"

.CommandType = CommandType.Text

.Connection = conn

End With

cmd.ExecuteNonQuery()

For i As Integer = 1 To 5

cmd = New System.Data.SqlClient.SqlCommand

With cmd

.CommandText = "INSERT INTO dbo.TestTB VALUES ( " & i.ToString & " ); SELECT SCOPE_IDENTITY();"

.CommandType = CommandType.Text

.Connection = conn

End With

Dim iNewId As Integer = cmd.ExecuteScalar()

Console.WriteLine("NewId: value = {0}", iNewId.ToString)

Next

cmd = New System.Data.SqlClient.SqlCommand

With cmd

.CommandText = "DROP TABLE dbo.TestTB;"

.CommandType = CommandType.Text

.Connection = conn

End With

cmd.ExecuteNonQuery()

cmd.Dispose()

conn.Close()

cmd = Nothing

conn = Nothing

but please consider the best practices..

regards

getting latest or max value from primary key field.

Hi I have a table like
*******************
*pri key * varchar (20)*
********************
* 1 * tom *
* 2 * joe *
* 3 * paul *
Need a query that gets the highest pri key value. thanks.
mabye something like
SELECT field1
FROM table
WHERE field1 IS MAX
I did have a simple count but in working with the stored procedure that
writes to this table the pri key value got skipped for a few entries, so
count comes up short by a few.
Paul G
Software engineer.
Hi
SELECT MAX(field1)
FROM table
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:01998100-B507-4F3C-9DE3-FC4E14C3EE2D@.microsoft.com...
> Hi I have a table like
> *******************
> *pri key * varchar (20)*
> ********************
> * 1 * tom *
> * 2 * joe *
> * 3 * paul *
> Need a query that gets the highest pri key value. thanks.
> mabye something like
> SELECT field1
> FROM table
> WHERE field1 IS MAX
> I did have a simple count but in working with the stored procedure that
> writes to this table the pri key value got skipped for a few entries, so
> count comes up short by a few.
> --
> Paul G
> Software engineer.
|||ok thanks this is what I was looking for.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> SELECT MAX(field1)
> FROM table
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:01998100-B507-4F3C-9DE3-FC4E14C3EE2D@.microsoft.com...
>
>

getting latest or max value from primary key field.

Hi I have a table like
*******************
*pri key * varchar (20)*
********************
* 1 * tom *
* 2 * joe *
* 3 * paul *
Need a query that gets the highest pri key value. thanks.
mabye something like
SELECT field1
FROM table
WHERE field1 IS MAX
I did have a simple count but in working with the stored procedure that
writes to this table the pri key value got skipped for a few entries, so
count comes up short by a few.
--
Paul G
Software engineer.Hi
SELECT MAX(field1)
FROM table
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:01998100-B507-4F3C-9DE3-FC4E14C3EE2D@.microsoft.com...
> Hi I have a table like
> *******************
> *pri key * varchar (20)*
> ********************
> * 1 * tom *
> * 2 * joe *
> * 3 * paul *
> Need a query that gets the highest pri key value. thanks.
> mabye something like
> SELECT field1
> FROM table
> WHERE field1 IS MAX
> I did have a simple count but in working with the stored procedure that
> writes to this table the pri key value got skipped for a few entries, so
> count comes up short by a few.
> --
> Paul G
> Software engineer.|||ok thanks this is what I was looking for.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> SELECT MAX(field1)
> FROM table
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:01998100-B507-4F3C-9DE3-FC4E14C3EE2D@.microsoft.com...
>
>

getting latest or max value from primary key field.

Hi I have a table like
*******************
*pri key * varchar (20)*
********************
* 1 * tom *
* 2 * joe *
* 3 * paul *
Need a query that gets the highest pri key value. thanks.
mabye something like
SELECT field1
FROM table
WHERE field1 IS MAX
I did have a simple count but in working with the stored procedure that
writes to this table the pri key value got skipped for a few entries, so
count comes up short by a few.
--
Paul G
Software engineer.Hi
SELECT MAX(field1)
FROM table
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:01998100-B507-4F3C-9DE3-FC4E14C3EE2D@.microsoft.com...
> Hi I have a table like
> *******************
> *pri key * varchar (20)*
> ********************
> * 1 * tom *
> * 2 * joe *
> * 3 * paul *
> Need a query that gets the highest pri key value. thanks.
> mabye something like
> SELECT field1
> FROM table
> WHERE field1 IS MAX
> I did have a simple count but in working with the stored procedure that
> writes to this table the pri key value got skipped for a few entries, so
> count comes up short by a few.
> --
> Paul G
> Software engineer.|||ok thanks this is what I was looking for.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SELECT MAX(field1)
> FROM table
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:01998100-B507-4F3C-9DE3-FC4E14C3EE2D@.microsoft.com...
> > Hi I have a table like
> > *******************
> > *pri key * varchar (20)*
> > ********************
> > * 1 * tom *
> > * 2 * joe *
> > * 3 * paul *
> > Need a query that gets the highest pri key value. thanks.
> > mabye something like
> > SELECT field1
> > FROM table
> > WHERE field1 IS MAX
> > I did have a simple count but in working with the stored procedure that
> > writes to this table the pri key value got skipped for a few entries, so
> > count comes up short by a few.
> > --
> > Paul G
> > Software engineer.
>
>

Getting information dependant upon primary & foreign key

Hi All,

It seems I have been requested to carry out a complex query and the best way I think I can do this is with the use of a stored procedure. The problem is that I am not quite sure whether my SP is stated correctly and also how I would go about stating the SP in my VB.net code!

I would be ever so grateful if somebody could look over my SP code and possibly recommend a way of stating my code. My ability is limited so I would appreciate it if examples could be used with possible relations to my problem.

The Problem?

Tables:

1.tblRisk: Ref(pk), subject, status(fk), staff(fk),Dept(fk)

2.tblDept:Ref(pk), Department

The SP should state that Department should appear as the end result of the query when the page is loaded. So when a row is selected in tblRisk, dependant upon what the Dept is in that table, it then populates the department in which it is associated with from tblDept. I have left the SP below.

Many Thanks,

Kunal

CREATE PROCEDURE dbo.ShowMe @.yourInputValue INT
AS
SELECT tblDept.Department
FROM tblDept JOIN tblRisk
ON tblDept.Ref = tblRisk.Dept
WHEREtblDept.Ref = @.yourInputValue
RETURN 0
GO

You stored procedure is ok

You can use SqlCommand to call stored procedures in your ASP .NET application, just set the CommandType property of the SqlCommand to CommandType.StoredProcedure and pass proper parameter(s) to the stored procedure.

Getting Identity back

in my VB.NET program, when I do the following, how can I get the value of
the primary key back
which is an Identity column?
Thanks,
T
connPO.Open()
Dim strSQL As String
strSQL = "INSERT INTO Orders " & _
"(JobID, Description, Notes, Status)" & _
"VALUES (@.JobID, @.Description, @.Notes, @.Status)"
Dim mycommand As New SqlCommand(strSQL, connPO)
mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
Try
rowsAffected = mycommand.ExecuteNonQuery()
If rowsAffected = 0 Then
Return "Rows Updated were Zero - Update was not effective"
End If
Return ""
Catch db As SqlException
If db.Number <> 2627 Then '2627 means dup add
Return db.Number & " " & db.Message
End If
Catch ex As System.Exception
Return ex.Message
Finally
connPO.Close()
End Try
(I notice you also posted in the dotnet forum as I answered there also)
Tina,
Right before you Return "" enter the following code
'now get the identity back
strSQL = "Select @.@.IDENTITY as 'Identity'"
Dim GetIDCommand As New SqlCommand(strSQL, connPO)
Dim myReturn as integer = GetIDCommand.ExecuteScalar
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>
|||"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>
You need to call SCOPE_IDENTITY in the same batch that does the INSERT:
strSQL = "INSERT INTO Orders "
...
strSQL = strSQL + "; SELECT SCOPE_IDENTITY()"
The result set is the IDENTITY value. There are a couple of points to note.
Firstly, SCOPE_IDENTITY is SQL Server 2000 / 2005 only. In SQL Server 7
you'll have to use @.@.IDENTITY. Using @.@.IDENTITY means that the return value
will reflect any INSERT done in a trigger if one exists on your table.
That's generally not what is wanted, so in the case of SQL Server 7 on a
table with a trigger you'll have to use a different technique: Issue a
SELECT with a WHERE clause based on alternate key values from among those
you inserted. IDENTITY should never be the only key of a table so it should
always be possible to retrieve the value without using either SCOPE_IDENTITY
or @.@.IDENTITY. For single row inserts however, SCOPE_IDENTITY is more
efficient.
Secondly, the above is bad advice :-). Any INSERT should be done with a
parameterized stored proc rather than dynamically in client code unless you
have an exceptional reason to do otherwise. Since your code is parameterized
anyway I don't know why you wouldn't use a proc here.
I notice you posted independently to at least two other groups.
Multi-posting is very inconsiderate and devalues the newsgroup experience
for everyone. If you really must hit several different groups with your
question then it's better to cross-post - i.e. the same message copied to
multiple groups so that there is just a single thread to continue the
discussion in. Don't cross-post excessively, but 1 or 2 well-chosen groups
in a cross-post is generally acceptable whereas pretty everyone hates
multi-posting.
Hope this helps.
David Portas
SQL Server MVP
|||> I notice you posted independently to at least two other groups.
Sorry. I think you posted twice to the SAME group actually. It was still a
multi-post to this one though.
David Portas
SQL Server MVP
|||To top it off, I gave Tina the same answer as you YESTERDAY in the adonet
group. Go figure.
Greg
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>
|||David,
I posted to one other group - the adonet group. The groups behave
differently so I do that to try to get best answers. The SQL Server group
always replys and very quickly but they are not always ADO oriented - I
ususally get T_SQL type answers. The adonet group doesn't answer so fast or
so well but they are ado oriented.
I didn't know that was against the rules. Sorry.
T
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>

Getting Identity back

in my VB.NET program, when I do the following, how can I get the value of
the primary key back
which is an Identity column?
Thanks,
T
connPO.Open()
Dim strSQL As String
strSQL = "INSERT INTO Orders " & _
"(JobID, Description, Notes, Status)" & _
"VALUES (@.JobID, @.Description, @.Notes, @.Status)"
Dim mycommand As New SqlCommand(strSQL, connPO)
mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
Try
rowsAffected = mycommand.ExecuteNonQuery()
If rowsAffected = 0 Then
Return "Rows Updated were Zero - Update was not effective"
End If
Return ""
Catch db As SqlException
If db.Number <> 2627 Then '2627 means dup add
Return db.Number & " " & db.Message
End If
Catch ex As System.Exception
Return ex.Message
Finally
connPO.Close()
End Try(I notice you also posted in the dotnet forum as I answered there also)
Tina,
Right before you Return "" enter the following code
'now get the identity back
strSQL = "Select @.@.IDENTITY as 'Identity'"
Dim GetIDCommand As New SqlCommand(strSQL, connPO)
Dim myReturn as integer = GetIDCommand.ExecuteScalar
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>|||"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>
You need to call SCOPE_IDENTITY in the same batch that does the INSERT:
strSQL = "INSERT INTO Orders "
...
strSQL = strSQL + "; SELECT SCOPE_IDENTITY()"
The result set is the IDENTITY value. There are a couple of points to note.
Firstly, SCOPE_IDENTITY is SQL Server 2000 / 2005 only. In SQL Server 7
you'll have to use @.@.IDENTITY. Using @.@.IDENTITY means that the return value
will reflect any INSERT done in a trigger if one exists on your table.
That's generally not what is wanted, so in the case of SQL Server 7 on a
table with a trigger you'll have to use a different technique: Issue a
SELECT with a WHERE clause based on alternate key values from among those
you inserted. IDENTITY should never be the only key of a table so it should
always be possible to retrieve the value without using either SCOPE_IDENTITY
or @.@.IDENTITY. For single row inserts however, SCOPE_IDENTITY is more
efficient.
Secondly, the above is bad advice :-). Any INSERT should be done with a
parameterized stored proc rather than dynamically in client code unless you
have an exceptional reason to do otherwise. Since your code is parameterized
anyway I don't know why you wouldn't use a proc here.
I notice you posted independently to at least two other groups.
Multi-posting is very inconsiderate and devalues the newsgroup experience
for everyone. If you really must hit several different groups with your
question then it's better to cross-post - i.e. the same message copied to
multiple groups so that there is just a single thread to continue the
discussion in. Don't cross-post excessively, but 1 or 2 well-chosen groups
in a cross-post is generally acceptable whereas pretty everyone hates
multi-posting.
Hope this helps.
David Portas
SQL Server MVP
--|||> I notice you posted independently to at least two other groups.
Sorry. I think you posted twice to the SAME group actually. It was still a
multi-post to this one though.
David Portas
SQL Server MVP
--|||To top it off, I gave Tina the same answer as you YESTERDAY in the adonet
group. Go figure.
Greg
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>|||David,
I posted to one other group - the adonet group. The groups behave
differently so I do that to try to get best answers. The SQL Server group
always replys and very quickly but they are not always ADO oriented - I
ususally get T_SQL type answers. The adonet group doesn't answer so fast or
so well but they are ado oriented.
I didn't know that was against the rules. Sorry.
T
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>

Getting Identity back

in my VB.NET program, when I do the following, how can I get the value of
the primary key back
which is an Identity column?
Thanks,
T
connPO.Open()
Dim strSQL As String
strSQL = "INSERT INTO Orders " & _
"(JobID, Description, Notes, Status)" & _
"VALUES (@.JobID, @.Description, @.Notes, @.Status)"
Dim mycommand As New SqlCommand(strSQL, connPO)
mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
Try
rowsAffected = mycommand.ExecuteNonQuery()
If rowsAffected = 0 Then
Return "Rows Updated were Zero - Update was not effective"
End If
Return ""
Catch db As SqlException
If db.Number <> 2627 Then '2627 means dup add
Return db.Number & " " & db.Message
End If
Catch ex As System.Exception
Return ex.Message
Finally
connPO.Close()
End Try(I notice you also posted in the dotnet forum as I answered there also)
Tina,
Right before you Return "" enter the following code
'now get the identity back
strSQL = "Select @.@.IDENTITY as 'Identity'"
Dim GetIDCommand As New SqlCommand(strSQL, connPO)
Dim myReturn as integer = GetIDCommand.ExecuteScalar
--
Regards,
Gary Blakely
Dean Blakely & Associates
www.deanblakely.com
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>|||"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:ejJHsp65FHA.3312@.TK2MSFTNGP15.phx.gbl...
> in my VB.NET program, when I do the following, how can I get the value of
> the primary key back
> which is an Identity column?
> Thanks,
> T
> connPO.Open()
> Dim strSQL As String
> strSQL = "INSERT INTO Orders " & _
> "(JobID, Description, Notes, Status)" & _
> "VALUES (@.JobID, @.Description, @.Notes, @.Status)"
> Dim mycommand As New SqlCommand(strSQL, connPO)
> mycommand.Parameters.Add(New SqlParameter("@.JobID", JobID))
> mycommand.Parameters.Add(New SqlParameter("@.Description", Description))
> mycommand.Parameters.Add(New SqlParameter("@.Notes", Notes))
> mycommand.Parameters.Add(New SqlParameter("@.Status", Status))
> Try
> rowsAffected = mycommand.ExecuteNonQuery()
> If rowsAffected = 0 Then
> Return "Rows Updated were Zero - Update was not effective"
> End If
> Return ""
> Catch db As SqlException
> If db.Number <> 2627 Then '2627 means dup add
> Return db.Number & " " & db.Message
> End If
> Catch ex As System.Exception
> Return ex.Message
> Finally
> connPO.Close()
> End Try
>
You need to call SCOPE_IDENTITY in the same batch that does the INSERT:
strSQL = "INSERT INTO Orders "
...
strSQL = strSQL + "; SELECT SCOPE_IDENTITY()"
The result set is the IDENTITY value. There are a couple of points to note.
Firstly, SCOPE_IDENTITY is SQL Server 2000 / 2005 only. In SQL Server 7
you'll have to use @.@.IDENTITY. Using @.@.IDENTITY means that the return value
will reflect any INSERT done in a trigger if one exists on your table.
That's generally not what is wanted, so in the case of SQL Server 7 on a
table with a trigger you'll have to use a different technique: Issue a
SELECT with a WHERE clause based on alternate key values from among those
you inserted. IDENTITY should never be the only key of a table so it should
always be possible to retrieve the value without using either SCOPE_IDENTITY
or @.@.IDENTITY. For single row inserts however, SCOPE_IDENTITY is more
efficient.
Secondly, the above is bad advice :-). Any INSERT should be done with a
parameterized stored proc rather than dynamically in client code unless you
have an exceptional reason to do otherwise. Since your code is parameterized
anyway I don't know why you wouldn't use a proc here.
I notice you posted independently to at least two other groups.
Multi-posting is very inconsiderate and devalues the newsgroup experience
for everyone. If you really must hit several different groups with your
question then it's better to cross-post - i.e. the same message copied to
multiple groups so that there is just a single thread to continue the
discussion in. Don't cross-post excessively, but 1 or 2 well-chosen groups
in a cross-post is generally acceptable whereas pretty everyone hates
multi-posting.
Hope this helps.
--
David Portas
SQL Server MVP
--|||> I notice you posted independently to at least two other groups.
Sorry. I think you posted twice to the SAME group actually. It was still a
multi-post to this one though.
--
David Portas
SQL Server MVP
--|||To top it off, I gave Tina the same answer as you YESTERDAY in the adonet
group. Go figure.
Greg
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
>> I notice you posted independently to at least two other groups.
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>|||David,
I posted to one other group - the adonet group. The groups behave
differently so I do that to try to get best answers. The SQL Server group
always replys and very quickly but they are not always ADO oriented - I
ususally get T_SQL type answers. The adonet group doesn't answer so fast or
so well but they are ado oriented.
I didn't know that was against the rules. Sorry.
T
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:T_qdnTqwkN2VouveRVnytw@.giganews.com...
>> I notice you posted independently to at least two other groups.
> Sorry. I think you posted twice to the SAME group actually. It was still a
> multi-post to this one though.
> --
> David Portas
> SQL Server MVP
> --
>

getting highest primary key value

Hi I have a table like
*******************
*pri key * varchar (20)*
********************
* 1 * tom *
* 2 * joe *
* 3 * paul *
Need a query that gets the highest pri key value. thanks.
mabye something like
SELECT field1
FROM table
WHERE field1 IS MAX
Paul G
Software engineer.meant to put this in the sqlserver group
"Paul" wrote:

> Hi I have a table like
> *******************
> *pri key * varchar (20)*
> ********************
> * 1 * tom *
> * 2 * joe *
> * 3 * paul *
> Need a query that gets the highest pri key value. thanks.
> mabye something like
> SELECT field1
> FROM table
> WHERE field1 IS MAX
> Paul G
> Software engineer.|||Paul
Try this
SELECT field2, MAX(field1)
FROM table
GROUP BY field2
Paul G
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:A2B750B6-990D-4B56-B0CE-301F81FF384C@.microsoft.com...[vbcol=seagreen]
> meant to put this in the sqlserver group
> "Paul" wrote:
>|||ok thanks this seemed to work.
"Uri Dimant" wrote:

> Paul
> Try this
> SELECT field2, MAX(field1)
> FROM table
> GROUP BY field2
> Paul G
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:A2B750B6-990D-4B56-B0CE-301F81FF384C@.microsoft.com...
>
>