DataTable and existing rows

[Guest]

Hi

Im wanted to know if it is possible to find out if a row has already been inserted into a datatable

i.e i only want to push new data into my datatable if it has not alreay been previously inserted. for example.. the primary key does not already exist

This is my code for an onclick event of a button

InsRoomBooking = dsFullBooking.Tables("dtRoomBooking").NewRow(
InsRoomBooking("BookingNo") = "-1
InsRoomBooking("RoomID") = RoomI
InsRoomBooking("RoomDt") = dtpRoomDate.Tex
InsRoomBooking("Room_Name") = CBoxRoomNames.Tex
InsRoomBooking("Req_Session") = CBoxSession.Tex
InsRoomBooking("Req_Layout") = CBoxLayout.Tex
InsRoomBooking("No_Of_Guests") = txtNoOfGuests.Tex

My primary key for this table is: BookingNo, RoomID, RoomDt, Req_Sessio

I only want to perform the insert into my datatable if these values do not alreay exist in my datatable. How can i do this? Do i have to use an if statemnet before the insert

Can anybody help me

 

[William Ryan]

YOu can use the .Select method to verify it's existence/nonexistence before
you attempt the insert (or some similar method wherein you do your
validation beforehand...or you can just do the addition and trap the
exception that will be thrown if it's already there. It's really no
different from how a 'real' db works..you can make sure that the data is
legit before trying to insert it, but if it's not ok, it'll bark at you and
you thereby infer that it already exists.

HTH,

Bill

Hi,

Im wanted to know if it is possible to find out if a row has already been inserted into a datatable?

i.e i only want to push new data into my datatable if it has not alreay

been previously inserted. for example.. the primary key does not already
exist.

This is my code for an onclick event of a button.

InsRoomBooking = dsFullBooking.Tables("dtRoomBooking").NewRow()
InsRoomBooking("BookingNo") = "-1"
InsRoomBooking("RoomID") = RoomID
InsRoomBooking("RoomDt") = dtpRoomDate.Text
InsRoomBooking("Room_Name") = CBoxRoomNames.Text
InsRoomBooking("Req_Session") = CBoxSession.Text
InsRoomBooking("Req_Layout") = CBoxLayout.Text
InsRoomBooking("No_Of_Guests") = txtNoOfGuests.Text

My primary key for this table is: BookingNo, RoomID, RoomDt, Req_Session

I only want to perform the insert into my datatable if these values do not

alreay exist in my datatable. How can i do this? Do i have to use an if
statemnet before the insert?

 

[Guest]

hi William

Thx for your advise. Do u have any sample codes of when this method is being used (.Select)

As i am new to VB.NET i am not sure on the coding to well
I want to verify that the primary key (Compound key) does not exist before i actually try to enter the row. Is there no way to do this through an IF STATEMENT before the insert into the datatable

 

[William Ryan]

Yes, you can use an if, but using a .Select or a .Find is just one way of
many to actually implement the If statemnt
http://www.knowdotnet.com/articles/dataviewspart2.html

You can also use the RowFilter to verify the existence of a value..
http://www.knowdotnet.com/articles/advancedrowfilter.html

Here's another link that should help...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatadatatableclassselecttopic.asp

hi William,

Thx for your advise. Do u have any sample codes of when this method is being used (.Select)?

As i am new to VB.NET i am not sure on the coding to well.
I want to verify that the primary key (Compound key) does not exist before

i actually try to enter the row. Is there no way to do this through an IF
STATEMENT before the insert into the datatable?

 

[Guest]

Hello William

Thx for the article, It was very useful and an interesting read.

I tried to use the examples but im still having a few problems.
My primary key consists of a composite key of 4 coloumns.
This is what i am searching on.

This is what i tried..

Dim x As Integer
Dim dro() As DataRow = dsFullBooking.Tables("dtRoomBooking").Select("BookingNo = '-1'
AND RoomID = RoomID AND RoomDt = dtpRoomDate.Text AND Req_Session =
CBoxSession.Text")

'if no rows are in the returned search set x to -1
If dro Is Nothing Then x = "-1"

If x = -1 Then
'insert the row into the datatable
Else
'the row alreasy exists, therefore display a mesgbox to tell the user.
End If

This code however does not seem to work. Is the method of writing the select correct?
Is this the best way to achieve the fuctionality i want to achieve.
Thx for your help )

 

[William Ryan]

The problem is in your string concatenation:

"BookingNo = '-1'
AND RoomID = RoomID AND RoomDt ='" & dtpRoomDate.Text & "'" AND
Req_Session ='" &
CBoxSession.Text & "'")

Hello William

Thx for the article, It was very useful and an interesting read.

I tried to use the examples but im still having a few problems.
My primary key consists of a composite key of 4 coloumns.
This is what i am searching on.

This is what i tried..

Dim x As Integer
Dim dro() As DataRow =

dsFullBooking.Tables("dtRoomBooking").Select("BookingNo = '-1'

 

[Guest]

Hello William,

I have tried your concatenation code but the last value ('" & CBoxSession.Text & "'")seems to be commented out!!!

Here is the code u provided me that i tried.......
Dim dro() As DataRow = dsFullBooking.Tables("dtRoomBooking").Select("BookingNo = '-1' AND RoomID = RoomID AND RoomDt ='" & dtpRoomDate.Text & "'" And Req_Session = '" & CBoxSession.Text & "'")


When i tryed putting a quote mark around the start of the last value i.e "'" & CBoxSession.Text & "'" the Req_Session column name gets underlined and has a message that it is not declared.

What am i doin wrong?

thx

 

[Ron Allen]

Bhavana,
You need to specify the RoomID being searched for as well. If you have
the primary key setup on the DataTable you could just trap the exception
that would occur when you added the row as well.
To make sure what you are selecting I suggest doing something like
Dim srch as String.
srch = String.Format("(BookingNo = '{0}') AND (RoomID = {1}) AND (RoomDt =
'{2}') AND (Req_Session = '{3}')",
"-1", RoomID.ToString(), dtpRoomDate.Text, CBoxSession.Text);
Dim dro() As DataRow = dsFullBooking.Tables("dtRoomBooking").Select(srch);
Then set a breakpoint on the Select statement to examine your search string.

Ron Allen

Hello William,

I have tried your concatenation code but the last value ('" &

CBoxSession.Text & "'")seems to be commented out!!!

Here is the code u provided me that i tried.......
Dim dro() As DataRow =

dsFullBooking.Tables("dtRoomBooking").Select("BookingNo = '-1' AND RoomID =
RoomID AND RoomDt ='" & dtpRoomDate.Text & "'" And Req_Session = '" &
CBoxSession.Text & "'")

When i tryed putting a quote mark around the start of the last value i.e

"'" & CBoxSession.Text & "'" the Req_Session column name gets underlined and
has a message that it is not declared.