Solved

VB.net SqlClient insert query

Posted on 2011-09-15
3
438 Views
Last Modified: 2012-05-12
Hi

I am using the following code to insert a record into an online
SQL database. The MsgBox reads
"Records Inserted 1"
Is this a good way to check if the insert was successful?
If it isn't successful would should I expect?
If this isn't a good way is there a better way?
Thanks
Sub oInsert()
        Dim ra As Integer
     
        Dim oDate As String = "03/03/2011" 'Date.Now.ToString
        Dim oText As String = "sqlclient"
        Dim oNumber As Decimal = 100
        myConnection.Open()
        Dim sSQL As String
        sSQL = "Insert Into Table1 ([oDate],[oText], [oNumber]) Select '" & oDate & "' as Expr1, '" & oText & "' as Expr2, " & oNumber & " as Expr3"
        myCommand = New SqlCommand(sSQL, myConnection)
        ra = myCommand.ExecuteNonQuery()
        'Since no value is returned we use ExecuteNonQuery
        MsgBox("Records Inserted " & ra)

        myConnection.Close()
    End Sub

Open in new window

0
Comment
Question by:murbro
3 Comments
 
LVL 37

Accepted Solution

by:
Gerwin Jansen earned 500 total points
ID: 36546457
Well, since ExecuteNonQuery returns the number of rows affected and your statement is an insert statement, you have successfully inserted 1 record. If the insert didn't work, you'd get 0 back.
0
 

Author Closing Comment

by:murbro
ID: 36546503
Thanks. Just wanted to confirm that
0
 
LVL 40
ID: 36546528
This is the recommended way, but you should add an error trap around everything you do on the server.

A lot of things can happen when you communicate with a server, so you should be prepared for it. As a minimum, the following:

Try
      myConnection.Open()
        Dim sSQL As String
        sSQL = "Insert Into Table1 ([oDate],[oText], [oNumber]) Select '" & oDate & "' as Expr1, '" & oText & "' as Expr2, " & oNumber & " as Expr3"
        myCommand = New SqlCommand(sSQL, myConnection)
        ra = myCommand.ExecuteNonQuery()
Catch ex As SqlException
        MessageBox.Show("The following error occured while insterting the record: " & ex.Message)
        'Handle errors
End Try
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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