#### error inserting a row into a datatable

Guest · Jan 18, 2004

Hi

i am trying to insert a new row into one of my datatabels that i have in my dataset when a button is clicked. here is my code

Dim ClientInsRow As DataRow = dtClient.NewRo

ClientInsRow("Surname") = txtSurname.Tex
ClientInsRow("Forename") = txtForename.Tex
ClientInsRow("OrgName") = txtOrganisation.Tex
ClientInsRow("Address") = txtAddress.Tex
ClientInsRow("Postcode") = txtPostcode.Tex
ClientInsRow("PhoneNo") = txtPhoneNo.Tex

'Add the new record to the table by calling the Add method of the DataRowCollection object.
dtClient.Rows.Add(ClientInsRow

i keep getting a message stating that....
'name dtClient is not declared

what am i doin wrong? i have followed the example from the vb.net help files!

My dataset with 5 datatables and 4 datarelations is created in the form_Load event

Can anyone help me??

Cor · Jan 18, 2004

Hi Eva

Did you try it with something as

Dim ClientInsRow As DataRow = dsFullBooking.tables("dtClient").NewRow

ClientInsRow("Surname") = txtSurname.Text
ClientInsRow("Forename") = txtForename.Text
ClientInsRow("OrgName") = txtOrganisation.Text
ClientInsRow("Address") = txtAddress.Text
ClientInsRow("Postcode") = txtPostcode.Text
ClientInsRow("PhoneNo") = txtPhoneNo.Text
dsFullBooking.tables("dtClient").Rows.Add(ClientInsRow)

Cor

Guest · Jan 18, 2004

hi cor

I have tried your code and now im gettin a message stating.....
Name 'dsFullBooking' is not declared

I dont know why i am getting this as i have declared the code further up oin the form_load event.
Dim dsFullBooking As New DataSet(
'create the customer datatabl
da = New SqlDataAdapter("SELECT * FROM Client", Cn
'da.Fill(dsFullBooking, "dtClient"
da.FillSchema(dsFullBooking, SchemaType.Mapped, "dtClient"

do u have any idea how to resolve this

Cor · Jan 18, 2004

Hi Jade (Eva),

It was just a test how many names you use.

But for me it does not botter,

Try to make your dataset private in your form
Put it beneath the row windows form designer
Private dsFullBooking as new dataset

...

Dim dsFullBooking As New DataSet(

and than delete the row above,
It is not the nicest but first get it working.

'create the customer datatable
da = New SqlDataAdapter("SELECT * FROM Client", Cn)
'da.Fill(dsFullBooking, "dtClient")
da.FillSchema(dsFullBooking, SchemaType.Mapped, "dtClient")

I think that will resolve this?

:-)

Cor

Guest · Jan 18, 2004

Hi Cor

Guest · Jan 18, 2004

Hi Cor,

thx for your help. it has solved my problem.

)

btw they is both my name.

I have tried to find articles on this subject and have learnt alot. Also my book on ado.net will be comin in next few days.

Do u know how i could update the last row in a specific datatable?
i.e when the user enters the client details in all the textboxes and clicks 'OK' the values are pushed to the datatable and the textboxes are disabled. However, when the user clicks the 'edit details' button the textboxes are all enabled and the values can be edited.

What i want is when the user clicks the 'OK' button again i want the previously inserted row to be updated instead of adding a new row. How would i do this?

thank u for help me.

)

Cor · Jan 18, 2004

Hi Eva,

Place this fore the update, I think that is what you are looking for.
DirectCast(BindingContext(ds.Tables(0)), CurrencyManager).EndCurrentEdit()

Cor

thx for your help. it has solved my problem. )

btw they is both my name.

I have tried to find articles on this subject and have learnt alot. Also

my book on ado.net will be comin in next few days.

Do u know how i could update the last row in a specific datatable?
i.e when the user enters the client details in all the textboxes and

clicks 'OK' the values are pushed to the datatable and the textboxes are
disabled. However, when the user clicks the 'edit details' button the
textboxes are all enabled and the values can be edited.

What i want is when the user clicks the 'OK' button again i want the

previously inserted row to be updated instead of adding a new row. How
would i do this?

Guest · Jan 18, 2004

Hi Cor,

ay i ask what this code does? When u say before the update, do u mean i should add this line before the values are added to the Datatable. i.e. on the 'OK' button on click event?

Cor · Jan 18, 2004

Hi Eva,

If that is just before the
(I am assuming you are using binding)
dataadapter.update(dataset)
Than that is OK yes.

Cor

ay i ask what this code does? When u say before the update, do u mean i

should add this line before the values are added to the Datatable. i.e. on
the 'OK' button on click event?

Cor · Jan 18, 2004

Hi Eva,

Wrong ofcourse I see you load them by hand.
I will try if i see what you mean

Cor

Cor · Jan 18, 2004

Hi Eva,

Do u know how i could update the last row in a specific datatable?
i.e when the user enters the client details in all the textboxes and

clicks 'OK' the values are pushed to the datatable and the textboxes are
disabled. However, when the user clicks the 'edit details' button the
textboxes are all enabled and the values can be edited.

What i want is when the user clicks the 'OK' button again i want the

previously inserted row to be updated instead of adding a new row. How
would i do this?I know not in detail what you are doing, but I have seen, you add the values
to the datatable items with something as item=textboxItem, when you do that
again, what is than the problem?

The changestate will again be set on and you can again do an update.

Cor

Guest · Jan 18, 2004

hi cor,

this is what i have done so far....
If .....this row has not already been inserted do the following....
Dim ClientInsRow As DataRow = dsFullBooking.Tables("dtClient").NewRow()
ClientInsRow("Surname") = txtSurname.Text
ClientInsRow("Forename") = txtForename.Text
ClientInsRow("OrgName") = txtOrganisation.Text
ClientInsRow("Address") = txtAddress.Text
ClientInsRow("Postcode") = txtPostcode.Text
ClientInsRow("PhoneNo") = txtPhoneNo.Text

dsFullBooking.Tables("dtClient").Rows.Add(ClientInsRow)

Else : if the row has already been inserted, right over the existing row.

End If

this is the code have placed in the onclick_event for my OK button.

what i am stuck on what to right on the first 'if' line and in the 'else' part fo the code. I have placed in pseudo code so that u can see what i am trying to achieve.

Guest · Jan 18, 2004

The problem i am having is that a new row is being inserted when the user clicks the 'OK' button instead of updating the already inserted row

What i do not know how to code is
1) how to find out if a row has already been inserted into the datatable
2) if it has already been inserted, how to update this row with the new values the user has entered in my textboxes into my datatable, instead of adding a new row

This .NET stuff is so confusing!!!

Cor · Jan 18, 2004

Hi Eva,

I see what you mean now.

if you now the row number from the datatable it is also easy

Everything again roughly here written, so you do know very good testing, I
think it is right but I never know for sure
\\\\
dim ClientInsRow as datarow
If .....this row has not already been inserted do the following....
ClientInsRow = dsFullBooking.Tables("dtClient").NewRow()
dsFullBooking.Tables("dtClient").Rows.Add(ClientInsRow)
////
..........if you are using primary keys than add them here and not again in
the rows below
\\\
else
dim ClientInsRow as datarow =
dsfullbooking.tables.tables("dtClient").rows(myrownumber)
end if
ClientInsRow("Surname") = txtSurname.Text
ClientInsRow("Forename") = txtForename.Text
ClientInsRow("OrgName") = txtOrganisation.Text
ClientInsRow("Address") = txtAddress.Text
ClientInsRow("Postcode") = txtPostcode.Text
///
When you not want to use the primary key you can do something as this if you
do not know the rownumber
\\\
dim myrownumber as integer
for myrownumber = 0 to
ds.dsfullbooking.tables.tables("dtClient").rows.count-1
if dsfullbooking.tables.tables("dtClient").rows(myrownumber)("Forename")
= txtForename.Text andalso xxxxx = txtSurname.Text.
exit for
next
////
However if you use a primary key you can simple do by instance
\\\
Dim findTheseVals(1) As Object
findTheseVals(0) = txtForename.Text
findTheseVals(1) = txtSurname.Text
ClientInsRow =
dsfullbooking.tables.tables("dtClient").rows.find(findTheseVals)
////

I hope it will go all

Cor

this is what i have done so far....
If .....this row has not already been inserted do the following....
Dim ClientInsRow As DataRow = dsFullBooking.Tables("dtClient").NewRow()
ClientInsRow("Surname") = txtSurname.Text
ClientInsRow("Forename") = txtForename.Text
ClientInsRow("OrgName") = txtOrganisation.Text
ClientInsRow("Address") = txtAddress.Text
ClientInsRow("Postcode") = txtPostcode.Text
ClientInsRow("PhoneNo") = txtPhoneNo.Text

dsFullBooking.Tables("dtClient").Rows.Add(ClientInsRow)

Else : if the row has already been inserted, right over the existing row.

End If

this is the code have placed in the onclick_event for my OK button.

what i am stuck on what to right on the first 'if' line and in the 'else'

part fo the code. I have placed in pseudo code so that u can see what i am
trying to achieve.

Cor · Jan 18, 2004

Eva,

All answers in the other thread,

:-)

Cor

The problem i am having is that a new row is being inserted when the user

clicks the 'OK' button instead of updating the already inserted row.

What i do not know how to code is:
1) how to find out if a row has already been inserted into the datatable.
2) if it has already been inserted, how to update this row with the new

values the user has entered in my textboxes into my datatable, instead of
adding a new row.

Guest · Jan 18, 2004

Hi Cor

Sorry i must have missed your last post.

i just read it. I will try your code and will let u know how it works out

I am not setting the primary key as it is an autonumber so i guess i will try the other method u showed me

thx for you help Cor

Guest · Jan 18, 2004

Hi Cor

I have tried your code but i am very confused as to how it goes into my if-then-else statement!
Is the myrownumberpart used if i know the primary key value or if i am searching on the value of my textboxes?

I dont not understand what u mean by.."If u use a primary key". Do u mean if i have a primary key in my datatable (I have an autonumber) that i know the value of after the first insert?

The only row that i will ever need to update will be the last row in the datatable (that is if it has been added after the datatable has been created

i have shown what i understand about your code below. Im sure that i have got the layout wrong or misunderstood u.....

dim ClientInsRow as dataro

If
'if this row has not already been inserted do the following...
ClientInsRow = dsFullBooking.Tables("dtClient").NewRow(
dsFullBooking.Tables("dtClient").Rows.Add(ClientInsRow
///
...........if you are using primary keys than add them here and not again i
the rows belo
\\els
dim ClientInsRow as datarow
dsfullbooking.tables.tables("dtClient").rows(myrownumber
end i

ClientInsRow("Surname") = txtSurname.Tex
ClientInsRow("Forename") = txtForename.Tex
ClientInsRow("OrgName") = txtOrganisation.Tex
ClientInsRow("Address") = txtAddress.Tex
ClientInsRow("Postcode") = txtPostcode.Tex
//
When you not want to use the primary key you can do something as this if yo
do not know the rownumbe
\\dim myrownumber as intege
for myrownumber = 0 to ds.dsfullbooking.tables.tables("dtClient").rows.count-
if dsfullbooking.tables.tables("dtClient").rows(myrownumber)("Forename"
= txtForename.Text andalso xxxxx = txtSurname.Text
exit fo
nex
///
However if you use a primary key you can simple do by instanc
\\Dim findTheseVals(1) As Objec
findTheseVals(0) = txtForename.Tex
findTheseVals(1) = txtSurname.Tex
ClientInsRow
dsfullbooking.tables.tables("dtClient").rows.find(findTheseVals
///

Cor · Jan 18, 2004

Hi Eva,

It is late for me but that last row number is
dim lastrownumber as integer = ds.table("xxx).rows.count - 1

Cor

I dont not understand what u mean by.."If u use a primary key". Do u mean

if i have a primary key in my datatable (I have an autonumber) that i know
the value of after the first insert??

The only row that i will ever need to update will be the last row in the

datatable (that is if it has been added after the datatable has been
created)

i have shown what i understand about your code below. Im sure that i have

got the layout wrong or misunderstood u......

Guest · Jan 19, 2004

Hi Cor

Sorry that i keep bugging u. After a good nights sleep im fresh to work again

I have tried out something similar to what u have suggested and am close to get it working. Just a small error stands in my way

( This is what i have tried...

1) i have declared a varibale to hold a initial datatable row coun
Dim Private NoOfRows as Intege

2) When i create my dataset, 5 datatables and 4 datarelations i count the initial number of rows in the dtClient datatable. I dont know why, but this count is always 0 even though there are many rows in my datatable!!!

da = New SqlDataAdapter("SELECT * FROM Client", Cn
da.Fill(dsFullBooking, "dtClient"
da.FillSchema(dsFullBooking, SchemaType.Mapped, "dtClient"
MyRowNumber = dsFullBooking.Tables("dtClient").Rows.Coun

3) then i right code in the On_Click event for my 'Submit_Guest' button. I only add a new row to my datatable if the current row count (MyRowNumberAfterIns) still equals the initial row count (NoOfRows) that was obtained when the datatable was first loaded. This ensures that the row is not added twice.
If the row has already been added, i run the 'Else' part of the code and edit the last row in the datatable

'PUSH THE DATA INTO MY DATATABL
If dsFullBooking.Tables("dtClient").Rows.Count = NoOfRows The
'if the inital row count = the current row count of the datatable, the row ha
'not been added to the datatable therefore i add a new ro
ClientInsRow = dsFullBooking.Tables("dtClient").NewRow(
ClientInsRow("Surname") = txtSurname.Tex
ClientInsRow("Forename") = txtForename.Tex
ClientInsRow("OrgName") = txtOrganisation.Tex
ClientInsRow("Address") = txtAddress.Tex
ClientInsRow("Postcode") = txtPostcode.Tex
ClientInsRow("PhoneNo") = txtPhoneNo.Tex

dsFullBooking.Tables("dtClient").Rows.Add(ClientInsRow

Else 'if the inital row count is not equal to the current row count then the row has
'already been added therefore i must edit the last row and not add a new row
Dim MyRowNumberAfterIns = dsFullBooking.Tables("dtClient").Rows.Count -
ClientInsRow = dsFullBooking.Tables("dtClient").Rows(MyRowNumberAfterIns)
("Forename") = txtForename.Tex
ClientInsRow = dsFullBooking.Tables("dtclient").Rows(MyRowNumberAfterIns
("Surname") = txtSurname.Tex
End I

The weird results im getting is that the initial row count 'NoOfRows' is always equal to 0, even though the datatable contains 12 rows
Also, the first time i add a guest it is all ok. However when i edit the details and click the 'Submit Guest' the code falls over in the 'Else' part of my code

i get the following message...

An unhandled exception of type 'System.InvalidCastException' occurred in Caravan.ex
Additional information: Specified cast is not valid

the application falls over on the following line..
ClientInsRow = dsFullBooking.Tables("dtClient").Rows(MyRowNumberAfterIns)("Forename")
= txtForename.Tex

what do u think is wrong

Cor · Jan 19, 2004

Hi Eva,

Becomes a long thread

da = New SqlDataAdapter("SELECT * FROM Client", Cn)
da.Fill(dsFullBooking, "dtClient")
da.FillSchema(dsFullBooking, SchemaType.Mapped, "dtClient")

this one above should not be needed
only when you want to fill an empty dataset with a schema

MyRowNumber = dsFullBooking.Tables("dtClient").Rows.Count

I think you did want to fill NoOfRows here

'PUSH THE DATA INTO MY DATATABLE
If dsFullBooking.Tables("dtClient").Rows.Count = NoOfRows Then
'if the inital row count = the current row count of the datatable, the row has
'not been added to the datatable therefore i add a new row
ClientInsRow = dsFullBooking.Tables("dtClient").NewRow()
ClientInsRow("Surname") = txtSurname.Text
ClientInsRow("Forename") = txtForename.Text
ClientInsRow("OrgName") = txtOrganisation.Text
ClientInsRow("Address") = txtAddress.Text
ClientInsRow("Postcode") = txtPostcode.Text
ClientInsRow("PhoneNo") = txtPhoneNo.Text

dsFullBooking.Tables("dtClient").Rows.Add(ClientInsRow)

Else 'if the inital row count is not equal to the current row count then the row has
'already been added therefore i must edit the last row and not add a new row.
Dim MyRowNumberAfterIns =

dsFullBooking.Tables("dtClient").Rows.Count - 1
maybe you can put option strict on
because I think you did wanted to write
dim myrownumberafterIns as integer = etcetera

ClientInsRow = dsFullBooking.Tables("dtClient").Rows(MyRowNumberAfterIns)
Clientrow ("Forename") = txtForename.Text
Clientrow ("Surname") = txtSurname.Text

End If

The weird results im getting is that the initial row count 'NoOfRows' is

always equal to 0, even though the datatable contains 12 rows.

Also, the first time i add a guest it is all ok. However when i edit the

details and click the 'Submit Guest' the code falls over in the 'Else' part
of my code

(

i get the following message....

An unhandled exception of type 'System.InvalidCastException' occurred in Caravan.exe
Additional information: Specified cast is not valid.

the application falls over on the following line...
ClientInsRow = dsFullBooking.Tables("dtClient").Rows(MyRowNumberAfterIns)("Forename")
= txtForename.Text

what do u think is wrong.

I think those things are wrong,

Cor