Solved

Update or Insert SQL Server 2005 using VB.net

Posted on 2014-01-02
8
744 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
 

Author Comment

by:TheChos
ID: 39751583
Got it.  thank you
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: 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now