Solved

Update or Insert SQL Server 2005 using VB.net

Posted on 2014-01-02
8
750 Views
Last Modified: 2014-01-02
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
Comment
Question by:TheChos
  • 4
  • 3
8 Comments
 
LVL 28

Accepted Solution

by:
sammySeltzer earned 500 total points
ID: 39751548
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
 

Author Comment

by:TheChos
ID: 39751553
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
 
LVL 5

Expert Comment

by:advfinance
ID: 39751571
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:TheChos
ID: 39751583
Got it.  thank you
0
 

Author Comment

by:TheChos
ID: 39751618
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
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 39751608
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
 

Author Closing Comment

by:TheChos
ID: 39751619
Very responsive and easy to follow directions.  Thank you.
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 39751652
You are very welcome and thanks for the points.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question