This is a multi-part message in MIME format.

------=_NextPart_000_008C_01C1E3B2.A2784780
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi all

I have a SQL Server Database (MSDE), with a number of tables and 
relationships.
I've been using wizards to create most of my forms and datasets etc.


One on of the forms, I would rather generate the next available primary 
key value then have it entered by the user.  (for a Uni assignment)

I am trying to retrieve the max value from a column in one of my tables, 
its so I can generate the next highest number to use as a primary key 
field.

I understand that the SQL might go along the lines of:

SELECT MAX(idkey) FROM tablename

I am not sure how to get the value from the database into a variable.

Can anyone guide me in the right direction(I've been using C# and still 
on beta 2)

Thanks

James

This is a multi-part message in MIME format.

------=_NextPart_000_00C5_01C1E3C3.3237F360
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

ok, i think ive worked it out

This is what im doing at the moment, maybe there is a cleaner way.

firstly i use the SQL statement

select MAX(ID) as expr1 from table

i then fill a dataset with the result

daNewID.Fill(dataset, "Table");

and take the result out like so

string tempID =3D dataset.Tables[0].Rows[0][0].ToString();

i then have a few steps where i increment the value by 1

hey presto, a new ID for a record. Which i insert into a textbox on my 
form.

If anyone has a better solution i would like to know.

Thanks James

  ----- Original Message -----
  From: j.l.porter
  To: ADO.NET
  Sent: Sunday, April 14, 2002 12:48 PM
  Subject: [ado_dotnet] Help, MAX value from a SQL server database table


  Hi all

  I have a SQL Server Database (MSDE), with a number of tables and 
relationships.
  I've been using wizards to create most of my forms and datasets etc.


  One on of the forms, I would rather generate the next available 
primary key value then have it entered by the user.  (for a Uni 
assignment)

  I am trying to retrieve the max value from a column in one of my 
tables, its so I can generate the next highest number to use as a 
primary key field.

  I understand that the SQL might go along the lines of:

  SELECT MAX(idkey) FROM tablename

  I am not sure how to get the value from the database into a variable.

  Can anyone guide me in the right direction(I've been using C# and 
still on beta 2)

  Thanks

  James

Hi James,

You could save yourself some overhead by not using a "select" statement in 
your SQL, but using a sproc that RETURNs the scalar value retrieved from 
the MAX clause.
Then, in ADO.NET retrieve the return value from a command object.

An even better way is to use the ExecuteScalar method of a SqlCommand or 
OleDBCommand. This method is specifically designed to return just a single 
scalar value.

But why are you retrieving the ID value before you insert a record? This is 
quite dangerous as it is possible that two concurrent users retrieve the 
same MAX value. Then when they try to insert the new record with the (same) 
ID, an error will occur for the second user. SQL Server has an IDENTITY 
type (autonumber) that hands out a new ID every time a record gets 
inserted. Unless you really need the ID up front, I'd look into the 
capabilities of SQL Server for handling IDs.

Imar



At 02:46 PM 4/14/2002 +0100, you wrote:
>ok, i think ive worked it out
>
>This is what im doing at the moment, maybe there is a cleaner way.
>
>firstly i use the SQL statement
>
>select MAX(ID) as expr1 from table
>
>i then fill a dataset with the result
>
>daNewID.Fill(dataset, "Table");
>
>and take the result out like so
>
>string tempID = dataset.Tables[0].Rows[0][0].ToString();
>
>i then have a few steps where i increment the value by 1
>
>hey presto, a new ID for a record. Which i insert into a textbox on my form.
>
>If anyone has a better solution i would like to know.
>
>Thanks James
>
>----- Original Message -----
>From: <mailto:j.l.porter@n...>j.l.porter
>To: <mailto:ado_dotnet@p...>ADO.NET
>Sent: Sunday, April 14, 2002 12:48 PM
>Subject: [ado_dotnet] Help, MAX value from a SQL server database table
>
>Hi all
>
>I have a SQL Server Database (MSDE), with a number of tables and 
>relationships.
>I've been using wizards to create most of my forms and datasets etc.
>
>
>One on of the forms, I would rather generate the next available primary 
>key value then have it entered by the user.  (for a Uni assignment)
>
>I am trying to retrieve the max value from a column in one of my tables, 
>its so I can generate the next highest number to use as a primary key field.
>
>I understand that the SQL might go along the lines of:
>
>SELECT MAX(idkey) FROM tablename
>
>I am not sure how to get the value from the database into a variable.
>
>Can anyone guide me in the right direction(I've been using C# and still on 
>beta 2)
>
>Thanks
>
>James
>

Thanks you for the reply

unfortunate, this has to be finished shortly(Uni assignment), so I don't
have the time to look into it, especially if it means a lot of rethinking.
Although I will look into the other method when I have time.  Its a good job
this prog isn't going to be used for anything :).

Thanks James

----- Original Message -----
From: "Imar Spaanjaars" <Imar@S...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Sunday, April 14, 2002 2:56 PM
Subject: [ado_dotnet] Re: Help, MAX value from a SQL server database table


> Hi James,
>
> You could save yourself some overhead by not using a "select" statement in
> your SQL, but using a sproc that RETURNs the scalar value retrieved from
> the MAX clause.
> Then, in ADO.NET retrieve the return value from a command object.
>
> An even better way is to use the ExecuteScalar method of a SqlCommand or
> OleDBCommand. This method is specifically designed to return just a single
> scalar value.
>
> But why are you retrieving the ID value before you insert a record? This
is
> quite dangerous as it is possible that two concurrent users retrieve the
> same MAX value. Then when they try to insert the new record with the
(same)
> ID, an error will occur for the second user. SQL Server has an IDENTITY
> type (autonumber) that hands out a new ID every time a record gets
> inserted. Unless you really need the ID up front, I'd look into the
> capabilities of SQL Server for handling IDs.
>
> Imar
>
>
>
> At 02:46 PM 4/14/2002 +0100, you wrote:
> >ok, i think ive worked it out
> >
> >This is what im doing at the moment, maybe there is a cleaner way.
> >
> >firstly i use the SQL statement
> >
> >select MAX(ID) as expr1 from table
> >
> >i then fill a dataset with the result
> >
> >daNewID.Fill(dataset, "Table");
> >
> >and take the result out like so
> >
> >string tempID = dataset.Tables[0].Rows[0][0].ToString();
> >
> >i then have a few steps where i increment the value by 1
> >
> >hey presto, a new ID for a record. Which i insert into a textbox on my
form.
> >
> >If anyone has a better solution i would like to know.
> >
> >Thanks James
> >
> >----- Original Message -----
> >From: <mailto:j.l.porter@n...>j.l.porter
> >To: <mailto:ado_dotnet@p...>ADO.NET
> >Sent: Sunday, April 14, 2002 12:48 PM
> >Subject: [ado_dotnet] Help, MAX value from a SQL server database table
> >
> >Hi all
> >
> >I have a SQL Server Database (MSDE), with a number of tables and
> >relationships.
> >I've been using wizards to create most of my forms and datasets etc.
> >
> >
> >One on of the forms, I would rather generate the next available primary
> >key value then have it entered by the user.  (for a Uni assignment)
> >
> >I am trying to retrieve the max value from a column in one of my tables,
> >its so I can generate the next highest number to use as a primary key
field.
> >
> >I understand that the SQL might go along the lines of:
> >
> >SELECT MAX(idkey) FROM tablename
> >
> >I am not sure how to get the value from the database into a variable.
> >
> >Can anyone guide me in the right direction(I've been using C# and still
on
> >beta 2)
> >
> >Thanks
> >
> >James
> >
>
>
>
>

Using ExecuteScalar can be as simple as the following example:

         Dim objconn As SqlConnection = New 
SqlConnection(GetConnectionString("MyDatabase"))
         Dim objCommand As SqlCommand = New SqlCommand("SELECT MAX(ID) FROM 
MyTable", objconn)
         objconn.Open()
         Dim i As Integer = objCommand.ExecuteScalar()
         objconn.Close()
         Label1.Text = "Max ID is " & i.ToString

Not too much rethinking involved, I'd say ;-)

Regards,


Imar



At 04:12 PM 4/14/2002 +0100, you wrote:
>Thanks you for the reply
>
>unfortunate, this has to be finished shortly(Uni assignment), so I don't
>have the time to look into it, especially if it means a lot of rethinking.
>Although I will look into the other method when I have time.  Its a good job
>this prog isn't going to be used for anything :).
>
>Thanks James
>
>----- Original Message -----
>From: "Imar Spaanjaars" <Imar@S...>
>To: "ADO.NET" <ado_dotnet@p...>
>Sent: Sunday, April 14, 2002 2:56 PM
>Subject: [ado_dotnet] Re: Help, MAX value from a SQL server database table
>
>
> > Hi James,
> >
> > You could save yourself some overhead by not using a "select" statement in
> > your SQL, but using a sproc that RETURNs the scalar value retrieved from
> > the MAX clause.
> > Then, in ADO.NET retrieve the return value from a command object.
> >
> > An even better way is to use the ExecuteScalar method of a SqlCommand or
> > OleDBCommand. This method is specifically designed to return just a single
> > scalar value.
> >
> > But why are you retrieving the ID value before you insert a record? This
>is
> > quite dangerous as it is possible that two concurrent users retrieve the
> > same MAX value. Then when they try to insert the new record with the
>(same)
> > ID, an error will occur for the second user. SQL Server has an IDENTITY
> > type (autonumber) that hands out a new ID every time a record gets
> > inserted. Unless you really need the ID up front, I'd look into the
> > capabilities of SQL Server for handling IDs.
> >
> > Imar
> >
> >
> >
> > At 02:46 PM 4/14/2002 +0100, you wrote:
> > >ok, i think ive worked it out
> > >
> > >This is what im doing at the moment, maybe there is a cleaner way.
> > >
> > >firstly i use the SQL statement
> > >
> > >select MAX(ID) as expr1 from table
> > >
> > >i then fill a dataset with the result
> > >
> > >daNewID.Fill(dataset, "Table");
> > >
> > >and take the result out like so
> > >
> > >string tempID = dataset.Tables[0].Rows[0][0].ToString();
> > >
> > >i then have a few steps where i increment the value by 1
> > >
> > >hey presto, a new ID for a record. Which i insert into a textbox on my
>form.
> > >
> > >If anyone has a better solution i would like to know.
> > >
> > >Thanks James
> > >
> > >----- Original Message -----
> > >From: <mailto:j.l.porter@n...>j.l.porter
> > >To: <mailto:ado_dotnet@p...>ADO.NET
> > >Sent: Sunday, April 14, 2002 12:48 PM
> > >Subject: [ado_dotnet] Help, MAX value from a SQL server database table
> > >
> > >Hi all
> > >
> > >I have a SQL Server Database (MSDE), with a number of tables and
> > >relationships.
> > >I've been using wizards to create most of my forms and datasets etc.
> > >
> > >
> > >One on of the forms, I would rather generate the next available primary
> > >key value then have it entered by the user.  (for a Uni assignment)
> > >
> > >I am trying to retrieve the max value from a column in one of my tables,
> > >its so I can generate the next highest number to use as a primary key
>field.
> > >
> > >I understand that the SQL might go along the lines of:
> > >
> > >SELECT MAX(idkey) FROM tablename
> > >
> > >I am not sure how to get the value from the database into a variable.
> > >
> > >Can anyone guide me in the right direction(I've been using C# and still
>on
> > >beta 2)
> > >
> > >Thanks
> > >
> > >James
> > >
> >
> >
> >
> >
>
>
>

just been playing with my SQL database, and found the IDENTITY options  :)
why didn't I find that earlier before I attempted to-do it by hand :/, I
suppose that's what learning is all about

I'm going to create my database again..  I have some changes I wanted to
make anyhow.

This time ill use identity, and let the DB do the hard work :)

Thanks Imar

James


----- Original Message -----
From: "Imar Spaanjaars" <Imar@S...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Sunday, April 14, 2002 4:21 PM
Subject: [ado_dotnet] Re: Help, MAX value from a SQL server database table


> Using ExecuteScalar can be as simple as the following example:
>
>          Dim objconn As SqlConnection = New
> SqlConnection(GetConnectionString("MyDatabase"))
>          Dim objCommand As SqlCommand = New SqlCommand("SELECT MAX(ID)
FROM
> MyTable", objconn)
>          objconn.Open()
>          Dim i As Integer = objCommand.ExecuteScalar()
>          objconn.Close()
>          Label1.Text = "Max ID is " & i.ToString
>
> Not too much rethinking involved, I'd say ;-)
>
> Regards,
>
>
> Imar
>
>
>
> At 04:12 PM 4/14/2002 +0100, you wrote:
> >Thanks you for the reply
> >
> >unfortunate, this has to be finished shortly(Uni assignment), so I don't
> >have the time to look into it, especially if it means a lot of
rethinking.
> >Although I will look into the other method when I have time.  Its a good
job
> >this prog isn't going to be used for anything :).
> >
> >Thanks James
> >
> >----- Original Message -----
> >From: "Imar Spaanjaars" <Imar@S...>
> >To: "ADO.NET" <ado_dotnet@p...>
> >Sent: Sunday, April 14, 2002 2:56 PM
> >Subject: [ado_dotnet] Re: Help, MAX value from a SQL server database
table
> >
> >
> > > Hi James,
> > >
> > > You could save yourself some overhead by not using a "select"
statement in
> > > your SQL, but using a sproc that RETURNs the scalar value retrieved
from
> > > the MAX clause.
> > > Then, in ADO.NET retrieve the return value from a command object.
> > >
> > > An even better way is to use the ExecuteScalar method of a SqlCommand
or
> > > OleDBCommand. This method is specifically designed to return just a
single
> > > scalar value.
> > >
> > > But why are you retrieving the ID value before you insert a record?
This
> >is
> > > quite dangerous as it is possible that two concurrent users retrieve
the
> > > same MAX value. Then when they try to insert the new record with the
> >(same)
> > > ID, an error will occur for the second user. SQL Server has an
IDENTITY
> > > type (autonumber) that hands out a new ID every time a record gets
> > > inserted. Unless you really need the ID up front, I'd look into the
> > > capabilities of SQL Server for handling IDs.
> > >
> > > Imar
> > >
> > >
> > >
> > > At 02:46 PM 4/14/2002 +0100, you wrote:
> > > >ok, i think ive worked it out
> > > >
> > > >This is what im doing at the moment, maybe there is a cleaner way.
> > > >
> > > >firstly i use the SQL statement
> > > >
> > > >select MAX(ID) as expr1 from table
> > > >
> > > >i then fill a dataset with the result
> > > >
> > > >daNewID.Fill(dataset, "Table");
> > > >
> > > >and take the result out like so
> > > >
> > > >string tempID = dataset.Tables[0].Rows[0][0].ToString();
> > > >
> > > >i then have a few steps where i increment the value by 1
> > > >
> > > >hey presto, a new ID for a record. Which i insert into a textbox on
my
> >form.
> > > >
> > > >If anyone has a better solution i would like to know.
> > > >
> > > >Thanks James
> > > >
> > > >----- Original Message -----
> > > >From: <mailto:j.l.porter@n...>j.l.porter
> > > >To: <mailto:ado_dotnet@p...>ADO.NET
> > > >Sent: Sunday, April 14, 2002 12:48 PM
> > > >Subject: [ado_dotnet] Help, MAX value from a SQL server database
table
> > > >
> > > >Hi all
> > > >
> > > >I have a SQL Server Database (MSDE), with a number of tables and
> > > >relationships.
> > > >I've been using wizards to create most of my forms and datasets etc.
> > > >
> > > >
> > > >One on of the forms, I would rather generate the next available
primary
> > > >key value then have it entered by the user.  (for a Uni assignment)
> > > >
> > > >I am trying to retrieve the max value from a column in one of my
tables,
> > > >its so I can generate the next highest number to use as a primary key
> >field.
> > > >
> > > >I understand that the SQL might go along the lines of:
> > > >
> > > >SELECT MAX(idkey) FROM tablename
> > > >
> > > >I am not sure how to get the value from the database into a variable.
> > > >
> > > >Can anyone guide me in the right direction(I've been using C# and
still
> >on
> > > >beta 2)
> > > >
> > > >Thanks
> > > >
> > > >James
> > > >
> > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
>

Hi James.

Another option would be to use GUIDs for primary keys. Every GUID you
create at the client is unique. And you can work with the keys of
newly created rows even before you persist them to the database. This
is sometimes troublesome if you deal with Identity fields and offline
data (which DataSets are). Also, you won't have any concurrency
problems with reading back newly created Identity fields in a
multiuser system.

Some people say, that using GUIDs instead of Identity fields might
be a performance hit. But that's probably irrelevant for your Uni
assignment. I personally didn't experience much performance
decrease in my tests.

Norbert



Sunday, April 14, 2002, 5:53:18 PM, you wrote:

jlp> just been playing with my SQL database, and found the IDENTITY options  :)
jlp> why didn't I find that earlier before I attempted to-do it by hand :/, I
jlp> suppose that's what learning is all about

jlp> I'm going to create my database again..  I have some changes I wanted to
jlp> make anyhow.

jlp> This time ill use identity, and let the DB do the hard work :)

jlp> Thanks Imar

jlp> James


jlp> ----- Original Message -----
jlp> From: "Imar Spaanjaars" <Imar@S...>
jlp> To: "ADO.NET" <ado_dotnet@p...>
jlp> Sent: Sunday, April 14, 2002 4:21 PM
jlp> Subject: [ado_dotnet] Re: Help, MAX value from a SQL server database table


>> Using ExecuteScalar can be as simple as the following example:
>>
>>          Dim objconn As SqlConnection = New
>> SqlConnection(GetConnectionString("MyDatabase"))
>>          Dim objCommand As SqlCommand = New SqlCommand("SELECT MAX(ID)
jlp> FROM
>> MyTable", objconn)
>>          objconn.Open()
>>          Dim i As Integer = objCommand.ExecuteScalar()
>>          objconn.Close()
>>          Label1.Text = "Max ID is " & i.ToString
>>
>> Not too much rethinking involved, I'd say ;-)
>>
>> Regards,
>>
>>
>> Imar
>>
>>
>>
>> At 04:12 PM 4/14/2002 +0100, you wrote:
>> >Thanks you for the reply
>> >
>> >unfortunate, this has to be finished shortly(Uni assignment), so I don't
>> >have the time to look into it, especially if it means a lot of
jlp> rethinking.
>> >Although I will look into the other method when I have time.  Its a good
jlp> job
>> >this prog isn't going to be used for anything :).
>> >
>> >Thanks James
>> >
>> >----- Original Message -----
>> >From: "Imar Spaanjaars" <Imar@S...>
>> >To: "ADO.NET" <ado_dotnet@p...>
>> >Sent: Sunday, April 14, 2002 2:56 PM
>> >Subject: [ado_dotnet] Re: Help, MAX value from a SQL server database
jlp> table
>> >
>> >
>> > > Hi James,
>> > >
>> > > You could save yourself some overhead by not using a "select"
jlp> statement in
>> > > your SQL, but using a sproc that RETURNs the scalar value retrieved
jlp> from
>> > > the MAX clause.
>> > > Then, in ADO.NET retrieve the return value from a command object.
>> > >
>> > > An even better way is to use the ExecuteScalar method of a SqlCommand
jlp> or
>> > > OleDBCommand. This method is specifically designed to return just a
jlp> single
>> > > scalar value.
>> > >
>> > > But why are you retrieving the ID value before you insert a record?
jlp> This
>> >is
>> > > quite dangerous as it is possible that two concurrent users retrieve
jlp> the
>> > > same MAX value. Then when they try to insert the new record with the
>> >(same)
>> > > ID, an error will occur for the second user. SQL Server has an
jlp> IDENTITY
>> > > type (autonumber) that hands out a new ID every time a record gets
>> > > inserted. Unless you really need the ID up front, I'd look into the
>> > > capabilities of SQL Server for handling IDs.
>> > >
>> > > Imar
>> > >
>> > >
>> > >
>> > > At 02:46 PM 4/14/2002 +0100, you wrote:
>> > > >ok, i think ive worked it out
>> > > >
>> > > >This is what im doing at the moment, maybe there is a cleaner way.
>> > > >
>> > > >firstly i use the SQL statement
>> > > >
>> > > >select MAX(ID) as expr1 from table
>> > > >
>> > > >i then fill a dataset with the result
>> > > >
>> > > >daNewID.Fill(dataset, "Table");
>> > > >
>> > > >and take the result out like so
>> > > >
>> > > >string tempID = dataset.Tables[0].Rows[0][0].ToString();
>> > > >
>> > > >i then have a few steps where i increment the value by 1
>> > > >
>> > > >hey presto, a new ID for a record. Which i insert into a textbox on
jlp> my
>> >form.
>> > > >
>> > > >If anyone has a better solution i would like to know.
>> > > >
>> > > >Thanks James
>> > > >
>> > > >----- Original Message -----
>> > > >From: <mailto:j.l.porter@n...>j.l.porter
>> > > >To: <mailto:ado_dotnet@p...>ADO.NET
>> > > >Sent: Sunday, April 14, 2002 12:48 PM
>> > > >Subject: [ado_dotnet] Help, MAX value from a SQL server database
jlp> table
>> > > >
>> > > >Hi all
>> > > >
>> > > >I have a SQL Server Database (MSDE), with a number of tables and
>> > > >relationships.
>> > > >I've been using wizards to create most of my forms and datasets etc.
>> > > >
>> > > >
>> > > >One on of the forms, I would rather generate the next available
jlp> primary
>> > > >key value then have it entered by the user.  (for a Uni assignment)
>> > > >
>> > > >I am trying to retrieve the max value from a column in one of my
jlp> tables,
>> > > >its so I can generate the next highest number to use as a primary key
>> >field.
>> > > >
>> > > >I understand that the SQL might go along the lines of:
>> > > >
>> > > >SELECT MAX(idkey) FROM tablename
>> > > >
>> > > >I am not sure how to get the value from the database into a variable.
>> > > >
>> > > >Can anyone guide me in the right direction(I've been using C# and
jlp> still
>> >on
>> > > >beta 2)
>> > > >
>> > > >Thanks
>> > > >
>> > > >James
>> > > >
>> > >
>> > >
>> > >
>> > >
>> >
>> >
>> >
>>
>>
>>
jlp> 
>>



jlp> ---
jlp> 
jlp>