primary key problem

[Newbie]

hello!

i'm new to sql server and having some problem getting the primary key or
index (Reference column). opening up the design table, the primary key or
index column has an identity seed number that may vary in time. the identity
increment is 1. in my code, i'm trying to get the next value and showing it
in a textbox (txtReference). but i'm getting the wrong value.

example: if the last row in the table TaskOrder has a value of 150 in the
Reference column, i'm getting the value of 151. but bec. of the identity seed
of 200, when the row is actually added, the value for the Reference column is
201 or higher.

how do i get the actual value that the database will use? i need to change
my select statement.

my code:

sReference = "Select max(Reference) From TaskOrder";
sqlConn = new SqlConnection(ConfigurationSettings.AppSettings["sql2000"]);
sqlCMD = new SqlCommand(sReference);
sqlCMD.Connection = sqlConn;
sqlConn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sqlCMD);
DataSet ds = new DataSet();
adapter.Fill(ds);
int tableRowCount = ds.Tables[0].Rows.Count;
if (tableRowCount == 1)
{
sReference = ds.Tables[0].Rows[0].ItemArray[0].ToString();
nReference = int.Parse(sReference) + 1;
sReference = nReference.ToString();
Session["sReference"] = sReference;
txtReference.Text = sReference;
}
else
{
txtReference.Text = "1";
}
sqlConn.Close();

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

You can't know in advance what the new value will be it's only after you
added it that you know which was assigned.

Imagine what happens if two person execute at the same time the code below?
both will get the same "id" but only one ( at the most ) will really get it
after all.
what is what you want to do?

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation


"Newbie" <Ne****@discussions.microsoft.com> wrote in message
news:B7**********************************@microsof t.com...

hello!

i'm new to sql server and having some problem getting the primary key or
index (Reference column). opening up the design table, the primary key or
index column has an identity seed number that may vary in time. the
identity
increment is 1. in my code, i'm trying to get the next value and showing
it
in a textbox (txtReference). but i'm getting the wrong value.

example: if the last row in the table TaskOrder has a value of 150 in the
Reference column, i'm getting the value of 151. but bec. of the identity
seed
of 200, when the row is actually added, the value for the Reference column
is
201 or higher.

how do i get the actual value that the database will use? i need to change
my select statement.

my code:

sReference = "Select max(Reference) From TaskOrder";
sqlConn = new
SqlConnection(ConfigurationSettings.AppSettings["sql2000"]);
sqlCMD = new SqlCommand(sReference);
sqlCMD.Connection = sqlConn;
sqlConn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sqlCMD);
DataSet ds = new DataSet();
adapter.Fill(ds);
int tableRowCount = ds.Tables[0].Rows.Count;
if (tableRowCount == 1)
{
sReference = ds.Tables[0].Rows[0].ItemArray[0].ToString();
nReference = int.Parse(sReference) + 1;
sReference = nReference.ToString();
Session["sReference"] = sReference;
txtReference.Text = sReference;
}
else
{
txtReference.Text = "1";
}
sqlConn.Close();

[Newbie]

i need to show the value in a textbox (software requirement). i also need to
add a new row in another table wherer the value is stored in column 1. in
retrieving the data later, i need to use the value to fetch the right row in
both tables.

"Ignacio Machin ( .NET/ C# MVP )" wrote:

Hi,

You can't know in advance what the new value will be it's only after you
added it that you know which was assigned.

Imagine what happens if two person execute at the same time the code below?
both will get the same "id" but only one ( at the most ) will really get it
after all.
what is what you want to do?

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation


"Newbie" <Ne****@discussions.microsoft.com> wrote in message
news:B7**********************************@microsof t.com...

hello!

i'm new to sql server and having some problem getting the primary key or
index (Reference column). opening up the design table, the primary key or
index column has an identity seed number that may vary in time. the
identity
increment is 1. in my code, i'm trying to get the next value and showing
it
in a textbox (txtReference). but i'm getting the wrong value.

example: if the last row in the table TaskOrder has a value of 150 in the
Reference column, i'm getting the value of 151. but bec. of the identity
seed
of 200, when the row is actually added, the value for the Reference column
is
201 or higher.

how do i get the actual value that the database will use? i need to change
my select statement.

my code:

sReference = "Select max(Reference) From TaskOrder";
sqlConn = new
SqlConnection(ConfigurationSettings.AppSettings["sql2000"]);
sqlCMD = new SqlCommand(sReference);
sqlCMD.Connection = sqlConn;
sqlConn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sqlCMD);
DataSet ds = new DataSet();
adapter.Fill(ds);
int tableRowCount = ds.Tables[0].Rows.Count;
if (tableRowCount == 1)
{
sReference = ds.Tables[0].Rows[0].ItemArray[0].ToString();
nReference = int.Parse(sReference) + 1;
sReference = nReference.ToString();
Session["sReference"] = sReference;
txtReference.Text = sReference;
}
else
{
txtReference.Text = "1";
}
sqlConn.Close();

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Well, you would have to change that requirement :) , I had a similar
situation so what I did was show this in the Textbox:
"N/A *"

Below in little red font:

"* The ID will be assigned after the data is inserted in the DB"

Remember you will have the ID only AFTER you insert it in the DB.

Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Newbie" <Ne****@discussions.microsoft.com> wrote in message
news:1D**********************************@microsof t.com...

i need to show the value in a textbox (software requirement). i also need
to
add a new row in another table wherer the value is stored in column 1. in
retrieving the data later, i need to use the value to fetch the right row
in
both tables.

"Ignacio Machin ( .NET/ C# MVP )" wrote:

Hi,

You can't know in advance what the new value will be it's only after you
added it that you know which was assigned.

Imagine what happens if two person execute at the same time the code
below?
both will get the same "id" but only one ( at the most ) will really get
it
after all.
what is what you want to do?

cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation


"Newbie" <Ne****@discussions.microsoft.com> wrote in message
news:B7**********************************@microsof t.com...

> hello!
>
> i'm new to sql server and having some problem getting the primary key
> or
> index (Reference column). opening up the design table, the primary key
> or
> index column has an identity seed number that may vary in time. the
> identity
> increment is 1. in my code, i'm trying to get the next value and
> showing
> it
> in a textbox (txtReference). but i'm getting the wrong value.
>
> example: if the last row in the table TaskOrder has a value of 150 in
> the
> Reference column, i'm getting the value of 151. but bec. of the
> identity
> seed
> of 200, when the row is actually added, the value for the Reference
> column
> is
> 201 or higher.
>
> how do i get the actual value that the database will use? i need to
> change
> my select statement.
>
> my code:
>
> sReference = "Select max(Reference) From TaskOrder";
> sqlConn = new
> SqlConnection(ConfigurationSettings.AppSettings["sql2000"]);
> sqlCMD = new SqlCommand(sReference);
> sqlCMD.Connection = sqlConn;
> sqlConn.Open();
> SqlDataAdapter adapter = new SqlDataAdapter(sqlCMD);
> DataSet ds = new DataSet();
> adapter.Fill(ds);
> int tableRowCount = ds.Tables[0].Rows.Count;
> if (tableRowCount == 1)
> {
> sReference = ds.Tables[0].Rows[0].ItemArray[0].ToString();
> nReference = int.Parse(sReference) + 1;
> sReference = nReference.ToString();
> Session["sReference"] = sReference;
> txtReference.Text = sReference;
> }
> else
> {
> txtReference.Text = "1";
> }
> sqlConn.Close();
>
>

[Wessel Troost]

> i need to show the value in a textbox (software requirement). i also

need to
add a new row in another table wherer the value is stored in column 1. in
retrieving the data later, i need to use the value to fetch the right
row in
both tables.

You could add the row right away. This will give you a new ID.

When the client enters the other data, you update the existing row instead
of inserting it.

Obviously the row will be mostly empty at first. The row could be marked
as "WaitingForData" by some field, and if it has been in that state for
more than a day, you can clean it up.

Greetings,
Wessel