Solved

Update or Insert SQL Server 2005 using VB.net

Posted on 2014-01-02
8
754 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 29

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 29

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 29

Expert Comment

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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
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…

738 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