Solved

VB.net SqlClient insert query

Posted on 2011-09-15
3
437 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

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
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, f…

895 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

18 Experts available now in Live!

Get 1:1 Help Now