• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 766
  • Last Modified:

Update or Insert SQL Server 2005 using VB.net

I'd like to update or insert a new record into a SQL Server 2005 database using VB.net.

Knowing that my values will contain single quotes, I'd prefer to update the fields within my tables using the individual field names rather than a blanket INSERT command.  

For example, in VB6, I could write something as follows to accomplish the above:
 
   sSql = "SELECT * FROM MyTable"
  objMyRst.Open sSql, cnn, adOpenKeyset, adLockOptimistic, adCmdText
   If objMyRst.EOF Then
      If objMyRst.State = 1 Then objMyRst.Close
      sSql = "MyTable"
      objMyRst.Open sSql, cnn, adOpenKeyset, adLockOptimistic, adCmdTable
      objMyRst.AddNew
   End If
   objMyRst!Testfield = Now()
   objMyRst.Update


Any help in performing both the insert and update would be appreciated.
0
TheChos
Asked:
TheChos
  • 4
  • 3
1 Solution
 
sammySeltzerCommented:
In SQL Server, it will be more reliable if you use the EXISTS command.

For instance, if record alread exists, then update it. Otherwise, insert it.  Here is an example:

If EXISTS (select * from MyTable where someId = 'somevalue')

 Print 'Record exits - perform an Update statement here'

 ELSE

 Print 'Record doesn''t exist - perform your Insert statement here'

Hope this helps
0
 
TheChosAuthor Commented:
I like the suggestion but how do I account for the single quotes that will be involved when I perform the update or insert?
0
 
advfinanceCommented:
You can either use named parameters or escape any potential single quotes in your values by replacing single quote characters with two single quote characters.

--
Chris
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
TheChosAuthor Commented:
Got it.  thank you
0
 
TheChosAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for TheChos's comment #a39751583

for the following reason:

Very quick response and easy to follow directions.
0
 
sammySeltzerCommented:
If you are happy with the feedback you got, then assign points.

If you need additional help, please ask.

And if you want to delete the questions and keep your points, then indicate so as well.
0
 
TheChosAuthor Commented:
Very responsive and easy to follow directions.  Thank you.
0
 
sammySeltzerCommented:
You are very welcome and thanks for the points.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now