autonumber problem in my datatable

[Guest]

Hello,

I wanted some help on a dataset question. I have created a dataset and datatables and used the dataadapters 'fill' method on them.

In my database the primary key is an autonumber. When i use the fill method and set a datagrid source to my datatable i can see 10 rows. The rows each have a primary key value from 0 to 10 (Autonumbers). When i try to insert a new row into my datatable, the primary key autonumber seems to start at the value 0 rather than continuing from the highest number. i.e 11 onwards. For this reason my inserts into the datatable keep failing. Why does it do this?

Is there anyway i can make the autonumber start from the..... 'highest already generated number' (in this case 10) + 1 ???

Another question
If i have an autonumber coloumn in my database, will this automaticcaly be created for my datatable if i simply use the dataadapters fill method?

thx

 

[Miha Markic]

Hi,

Hello,

I wanted some help on a dataset question. I have created a dataset and

datatables and used the dataadapters 'fill' method on them.

In my database the primary key is an autonumber. When i use the fill

method and set a datagrid source to my datatable i can see 10 rows. The rows
each have a primary key value from 0 to 10 (Autonumbers). When i try to
insert a new row into my datatable, the primary key autonumber seems to
start at the value 0 rather than continuing from the highest number. i.e 11
onwards. For this reason my inserts into the datatable keep failing. Why
does it do this?

Is there anyway i can make the autonumber start from the..... 'highest

already generated number' (in this case 10) + 1 ???

Yes, sure, see DataColumn.AutoIncrementSeed and Step properties. It is
recomended that you set them both to negative values, such as -1.
So they won't interfere with actual values.

Another question
If i have an autonumber coloumn in my database, will this automaticcaly be

created for my datatable if i simply use the dataadapters fill method?
No, but FillSchema will.

 

[Rajesh Patel]

Hi,

There is no need to set any properties. You should not set it to 10+1 as you
said. because, in multi-user environment you won't know which number you
will get by database in that column. Just keep as hidden column in datagrid.
don't show it to the user.

Now, you have problem in insertion. check your dataadapter's insert command.
There you can check your statement. It should not try to insert
autoincrement column into the database. because, it's database job to do
that. you just need to retrieve back the value with scope_identity() or
@@identity for that row.

Hope this helps.

Rajesh Patel

Hello,

I wanted some help on a dataset question. I have created a dataset and

datatables and used the dataadapters 'fill' method on them.

In my database the primary key is an autonumber. When i use the fill

method and set a datagrid source to my datatable i can see 10 rows. The rows
each have a primary key value from 0 to 10 (Autonumbers). When i try to
insert a new row into my datatable, the primary key autonumber seems to
start at the value 0 rather than continuing from the highest number. i.e 11
onwards. For this reason my inserts into the datatable keep failing. Why
does it do this?

Is there anyway i can make the autonumber start from the..... 'highest

already generated number' (in this case 10) + 1 ???

Another question
If i have an autonumber coloumn in my database, will this automaticcaly be

created for my datatable if i simply use the dataadapters fill method?

 

[Guest]

Hello Rajesh and Miha,

thx for your help in answering my questions.

If the autonumber is not set when i retrieve the coloumns from my database table when i use the da.fill method, can i use both the....
da.FillSchema(Mydataset, SchemaType.Mapped, "myDatatable")
da.Fill(Mydataset, "myDatatable")
??

will this fill my datatable with the values from my database table, as well as define the primary (Autonumber) key column?

can i use both or does it have to be one or the other?

Also do u have an example of how i may use the @@IDENTITY or SCOPE_IDENTITY?
Do i just use it in an sql command or stored procedure as such..
Select @@Identity From table1

will this retrieve the last inserted autonumber??

Rajesh, you mentioned that i will have to edit the dataadapters insert query? where do i get access to this? is this automatically generated or something i have to create?

And i assume i have to hide the primary datacolumn by using table styles, or do u know of another easier method?

i appologise for the million questions. )

Thank u
Bhavna

 

[Ron Allen]

Bhavna,
See the topic "Retrieving Identity or Autonumber Values" in the help
files for examples.

Ron Allen

Hello Rajesh and Miha,

thx for your help in answering my questions.

If the autonumber is not set when i retrieve the coloumns from my database

table when i use the da.fill method, can i use both the....

da.FillSchema(Mydataset, SchemaType.Mapped, "myDatatable")
da.Fill(Mydataset, "myDatatable")
??

will this fill my datatable with the values from my database table, as

well as define the primary (Autonumber) key column?

can i use both or does it have to be one or the other?

Also do u have an example of how i may use the @@IDENTITY or SCOPE_IDENTITY?
Do i just use it in an sql command or stored procedure as such..
Select @@Identity From table1

will this retrieve the last inserted autonumber??

Rajesh, you mentioned that i will have to edit the dataadapters insert

query? where do i get access to this? is this automatically generated or
something i have to create?

And i assume i have to hide the primary datacolumn by using table styles,

or do u know of another easier method?

 

[William \(Bill\) Vaughn]

I wrote an article on how this is done that should clear up the mysteries.
http://www.betav.com/msdn_magazine.htm

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Hello Rajesh and Miha,

thx for your help in answering my questions.

If the autonumber is not set when i retrieve the coloumns from my database

table when i use the da.fill method, can i use both the....

da.FillSchema(Mydataset, SchemaType.Mapped, "myDatatable")
da.Fill(Mydataset, "myDatatable")
??

will this fill my datatable with the values from my database table, as

well as define the primary (Autonumber) key column?

can i use both or does it have to be one or the other?

Also do u have an example of how i may use the @@IDENTITY or SCOPE_IDENTITY?
Do i just use it in an sql command or stored procedure as such..
Select @@Identity From table1

will this retrieve the last inserted autonumber??

Rajesh, you mentioned that i will have to edit the dataadapters insert

query? where do i get access to this? is this automatically generated or
something i have to create?

And i assume i have to hide the primary datacolumn by using table styles,

or do u know of another easier method?

 

[Miha Markic]

Hello Rajesh and Miha,

thx for your help in answering my questions.

If the autonumber is not set when i retrieve the coloumns from my database

table when i use the da.fill method, can i use both the....

da.FillSchema(Mydataset, SchemaType.Mapped, "myDatatable")
da.Fill(Mydataset, "myDatatable")
??

will this fill my datatable with the values from my database table, as

well as define the primary (Autonumber) key column?

Yes, it will.

can i use both or does it have to be one or the other?

You can use both.

Also do u have an example of how i may use the @@IDENTITY or SCOPE_IDENTITY?
Do i just use it in an sql command or stored procedure as such..
Select @@Identity From table1

will this retrieve the last inserted autonumber??

You should stick with "select scope_identity()". Yes, it will give you the
last identity inserted.

 

[William \(Bill\) Vaughn]

See >>>

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Ok, Now things are beggining to more sense )
That was a great article William.

I am trying to push my INSERTED row from my datatable into my database.
I know i have to use the dataadapters 'update' command.
The following code is under the 'Submit' button onclick event

Try
da.Update(Mydataset.Tables("MyDataTable"))
Catch x As Exception
MsgBox(x.Message)
End Try

what i dont know is where i have to code my insert query?

queries on the fly, ADO.NET requires you to construct your own. This can be
done with the CommandBuilder (which most folks don't recommend) or by
setting up Commands that call stored procedures (which most do recommend). I
discuss all of this in detail in my book.

In williams article i saw he had a select statment below the insert to

retrieve the identity coloumn. Do i code all this in a stored procedure in
my MSDE database, or code it in my appliaction? Do i have to use the DACW?
Do i code it in the try-catch statment??
do the INSERT and this SQL includes (after the INSERT) a SELECT to return
the new identity value (using SCOPE_IDENTITY() ). You can use the DACW to
hook up the SPs you create--or it can create some for you--with the extra
SELECT.

So many options!!!
Can anyone plz help me in coding this? Once i have one working example, im

hoping i can tailor it to other situations.

 

[Guest]

Hi

i have tried to create this stored procedure but i keep getting a message sayin
ADO error: must declare the variable '@Lname'

Why do i get this when i have already declared this

CREATE PROCEDURE dbo.InsRentAndSelIdentit
/

@Lname char(50)
@Fname char(50)
@Address varchar(300)
@Postcode varchar(50)
@PhoneNo varchar(25

*
A
SET NOCOUNT ON

INSERT INTO Rent(Lname, Fname, Addr, RentPostcode, RentPhoneNo) VALUES (@Lname, @Fname, @Address, @Postcode, @PhoneNo)
SELECT Lname, Fname, Addr, RentPostcode, RentPhoneNo, RentID FROM Rent WHERE (RentID = @@IDENTITY)

RETURN

 

[William \(Bill\) Vaughn]

Well, you forgot to remove the comments from around the input parameters.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Hi,

i have tried to create this stored procedure but i keep getting a message saying
ADO error: must declare the variable '@Lname'.

Why do i get this when i have already declared this?

CREATE PROCEDURE dbo.InsRentAndSelIdentity
/*
(
@Lname char(50),
@Fname char(50),
@Address varchar(300),
@Postcode varchar(50),
@PhoneNo varchar(25)
)
*/
AS
SET NOCOUNT ON

INSERT INTO Rent(Lname, Fname, Addr, RentPostcode, RentPhoneNo) VALUES

(@Lname, @Fname, @address, @Postcode, @PhoneNo);

 

[Guest]

Hello William

Thank you for pointing out my error. The SPROC now works
What i want to ask is how i add my insertCommand to the dataadapter

I keep getting a error when i click my 'Submit' butto
Update requires a valid InsertVommand when passed DataRow collection with new rows

This is my coding..

Private Sub btnConfirmBooking_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConfirmBooking.Clic

Dim cmdInsRenter As New SqlCommand("InsRenterAndSelIdentity", cn
cmdInsRenter.CommandType = CommandType.StoredProcedur

'declare the parameter
cmdInsRenter.Parameters.Add("@Lname", SqlDbType.Char, 50
cmdInsRenter.Parameters.Add("@Fname", SqlDbType.Char, 50
cmdInsRenter.Parameters.Add("@Addr", SqlDbType.Char, 300
cmdInsRenter.Parameters.Add("@RenterPostcode", SqlDbType.Char, 50
cmdInsRenter.Parameters.Add("@RenterPhoneNo", SqlDbType.Char, 25

'now set the value
cmdInsRenter.Parameters("@Lname").Value = txtLname.Tex
cmdInsRenter.Parameters("@Fname").Value = txtFname.Tex
cmdInsRenter.Parameters("@Addr").Value = txtAddr.Tex
cmdInsRenter.Parameters("@RenterPostcode").Value = txtRenterPcode.Tex
cmdInsRenter.Parameters("@RenterPhoneNo").Value = txtRenterPhoneNo.Tex

daRenter.InsertCommand.CommandType.StoredProcedure(
Tr
cn.Open(
daRenter.Update(Mydataset.Tables("MyDataTable")
cmdInsRenter.ExecuteNonQuery(

Catch x As Exceptio
MsgBox(x.Message
Finall
cn.Close(
End Tr
End Su

 

[William \(Bill\) Vaughn]

Ah no.
The Update command walks through the DataSet and if it finds a new row IT
calls the DataAdapter.InsertCommand as you have previously defined it. You
should not call executenonquery yourself unless you're NOT using the Update
method.

I really think you need to read some more before going on. My walks you
through this in great detail.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Hello William,

Thank you for pointing out my error. The SPROC now works.
What i want to ask is how i add my insertCommand to the dataadapter?

I keep getting a error when i click my 'Submit' button
Update requires a valid InsertVommand when passed DataRow collection with new rows.

This is my coding...

Private Sub btnConfirmBooking_Click(ByVal sender As System.Object, ByVal e

As System.EventArgs) Handles btnConfirmBooking.Click