Best way to find EOF Record using OleDbCommand ?

  • Thread starter Thread starter Java
  • Start date Start date
First, I would not approach the problem as you have done. I would execute a
query that does both operations in one step.

--
____________________________________
Bill Vaughn
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.
__________________________________
 
I would not take this approach. I suggest creating an INSERT query that
contains the needed new row values as parameters and include a WHERE clause
that inserts the row if it does not already exist (WHERE NOT EXISTS) based
on the primary key. That's because even if you query to see if a row is
there or not, by the time you turn around and actually do the INSERT, it
might have been added.

hth

--
____________________________________
Bill Vaughn
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 want to check whether the record exist or not based on my query, so i'll
be using OleDbCommand.
What is the best way to check on a huge table ? I used to write like this in
VB6.0...

---------------------------------------------------
Public Function SaveOrUpdate() As Boolean
sSQL = "SELECT * FROM AdmAllergy WHERE " & _
" CompanyCode = " & mQuotedStr(mCompanyCode) & _
" AND BranchCode = " & mQuotedStr(mBranchCode) & _
" AND Code = " & mQuotedStr(sCode)
Set oRs = TmpAdoSet(sSQL)
With oRs
If .EOF And .BOF Then
.AddNew
.Fields("CompanyCode") = Trim(mCompanyCode)
.Fields("BranchCode") = Trim(mBranchCode)
.Fields("Code") = Trim(sCode)
.Fields("CreatedBy") = Trim(mUserId)
Else
.Fields("EditedBy") = Trim(mUserId)
.Fields("EditedDate") = Format(Now, "yyyy-mm-dd hh:mm:ss")
End If
.Fields("Name") = Trim(Name)
.UpdateBatch
.Close
End With
SaveOrUpdate = True
Set oRs = Nothing
End Function
---------------------------------------------------

So, how do i write in VB.Net ? Want to open check record exist or not. If
exist then update other fields
or else add new record. I prefer to use OleDbCommand.

Any Help ?

Regards


--

Thank You and Warm Regards,

Mohd Yameen
Infotech East Sdn. Bhd.
Tel : (60)3-7783 2437
Fax : (60)3-7783 4462
Mobile: (60)16-235 4776
E-mail: (e-mail address removed)

Please Note:
This Message may contain confidential information intended only for the use
of the addressee named above. If you are not the intended recipient of this
message you are hereby notified that any use, dissemination, distribution or
reproduction of this message is prohibited. If you received this message in
error please notify (e-mail address removed) immediately. Any views
expressed in this message are those of the individual sender and may not
necessarily reflect the views of Infotech East Sdn Bhd.
 
Javed,

Can you please give me an example. I'm a newbie in .NET. I apologize, if i
ask you guyz too much

Regards
 
INSERT INTO [biblio].[dbo].[Authors]([Au_ID], [Author], [Year_Born])
VALUES(@Au_ID, @Author, @Year_Born)
WHERE (NOT EXISTS (SELECT AU_ID FROM Authors WHERE AU_ID = @AU_ID)

hth

--
____________________________________
Bill Vaughn
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.
__________________________________
 
I didn't know that you can follow a "INSERT INTO...VALUES..." with a
"WHERE..." clause, at least it is not mentioned in all books that I have.

William (Bill) Vaughn said:
INSERT INTO [biblio].[dbo].[Authors]([Au_ID], [Author], [Year_Born])
VALUES(@Au_ID, @Author, @Year_Born)
WHERE (NOT EXISTS (SELECT AU_ID FROM Authors WHERE AU_ID = @AU_ID)

hth

--
____________________________________
Bill Vaughn
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.
__________________________________
ItsMe said:
William,

Can you please give me a simple example. ?

regards


so
i'll not.
If
Click to expand...
Click to expand...
 
William,

I got it. Thank you very much.

Regards


William (Bill) Vaughn said:
INSERT INTO [biblio].[dbo].[Authors]([Au_ID], [Author], [Year_Born])
VALUES(@Au_ID, @Author, @Year_Born)
WHERE (NOT EXISTS (SELECT AU_ID FROM Authors WHERE AU_ID = @AU_ID)

hth

--
____________________________________
Bill Vaughn
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.
__________________________________
ItsMe said:
William,

Can you please give me a simple example. ?

regards


so
i'll not.
If
Click to expand...
Click to expand...
 
Look up Subselect in the books your using.

Norman Yuan said:
I didn't know that you can follow a "INSERT INTO...VALUES..." with a
"WHERE..." clause, at least it is not mentioned in all books that I have.

William (Bill) Vaughn said:
INSERT INTO [biblio].[dbo].[Authors]([Au_ID], [Author], [Year_Born])
VALUES(@Au_ID, @Author, @Year_Born)
WHERE (NOT EXISTS (SELECT AU_ID FROM Authors WHERE AU_ID = @AU_ID)

hth

--
____________________________________
Bill Vaughn
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.
__________________________________
ItsMe said:
William,

Can you please give me a simple example. ?

regards


I would not take this approach. I suggest creating an INSERT query that
contains the needed new row values as parameters and include a WHERE
clause
that inserts the row if it does not already exist (WHERE NOT EXISTS) based
on the primary key. That's because even if you query to see if a row is
there or not, by the time you turn around and actually do the
Click to expand...
Click to expand...
INSERT,
it for
the
Click to expand...
recipient
of may
not
Click to expand...
Click to expand...
 
Back
Top