Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update or Insert SQL Server 2005 using VB.net

Posted on 2014-01-02
8
Medium Priority
?
757 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 2000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

662 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