Problems running T-SQL statement ADO from VB .NET

[Zaur Bahramov]

Hi!

I'm trying to insert some data into SQL server using ADO. I've created a
sample application that tries to copy a row from Person.Contact to
Person.ContactTemp basing on a parameter (AdventureWorks database).

Something like this:
Imports Microsoft.SqlServer
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnInsert.Click
Dim contactID As Integer
contactID = CInt(txtContactID.Text)
Dim connectionString As String _
= "Data Source =localhost\SqlExpress; Initial Catalog=AdventureWorks;
Integrated Security =True"
Dim connection As New SqlClient.SqlConnection(connectionString)
connection.Open()
Dim insertStatement As String _
= "INSERT INTO Person.ContactTemp (FirstName, LastName, EmailAddress, Phone)
" _
& "SELECT c.FirstName, c.LastName, c.EmailAddress, c.Phone " _
& "FROM Person.Contact c " _
& "WHERE c.ContactID=" & contactID.ToString
Dim insert As New SqlClient.SqlCommand(insertStatement, connection)
txtOutput.Text _
= "insertStatement: " & insertStatement & vbCrLf _
& "insert:" & vbTab & insert.ToString & vbCrLf _
& "ContactID: " & contactID.ToString
connection.Close()
End Sub
End Class

But it doesn't work... What is wrong here?
Thanks!

 

[Norman Yuan]

What does it mean "it does not work"? How could you tell? Did you get any
error? Which line causes error? Did you do due debugging?

With all said, if that is your whole code, of course it DOES NOT work,
because yoru code DID not execute the SqlCommand object. You need a line of
code like:

insert.ExecuteNonQuery();

before

txtOutput.Text=.....

 

[Cor Ligthert[MVP]]

Zaur,

Like Norman I saw that you wrote Something like this.

On that the answer can than only be.

Something like that is not working because there is missing a lot.
Can you show us what it acutaly is?

Any anwer on this is something what is wasting time.

Cor

 

[Zaur Bahramov]

Hi!

I've modified the code as per advice of Norman Yuan and now this code works
OK. Indeed, my code was missing the .ExecuteNonQuery().
Plus, I've modified original code to use SqlParameter.

P.S. How do I make in ADO and VB .NET check what really happens when code
runs ExecuteNonQuery()? I mean, in vb .net I can place breakpoints and step
through the code to see values during execution of the app, but in case of
ADO, when it runs a query, what is the best practice for troubleshooting.

Thanks a lot!

Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnInsert.Click
Dim contactID As Integer
contactID = txtContactID.Text
Dim connectionString As String _
= "Data Source =localhost\SqlExpress; Initial
Catalog=AdventureWorks; Integrated Security =True"
Dim connection As New SqlConnection(connectionString)
Dim insertStatement As String _
= "INSERT INTO Person.ContactTemp (FirstName, LastName,
EmailAddress, Phone) " _
& "SELECT c.FirstName, c.LastName, c.EmailAddress, c.Phone " _
& "FROM Person.Contact c " _
& "WHERE c.ContactID=@ContactID"
Dim param As SqlParameter
param = New SqlParameter("@ContactID", SqlDbType.Int)
param.Value = CInt(txtContactID.Text)
connection.Open()
Dim command As SqlCommand = connection.CreateCommand()
command.CommandText = insertStatement
command.Parameters.Add(param)
Dim nResult As Integer = command.ExecuteNonQuery()
If nResult > 0 Then
txtOutput.Text = "Insert completed" & vbCrLf & nResult & "
records inserted"
End If
connection.Close()
command.Dispose()
End Sub
End Class
--
Zaur Bahramov, MCP

Zaur,

Like Norman I saw that you wrote Something like this.

On that the answer can than only be.

Something like that is not working because there is missing a lot.
Can you show us what it acutaly is?

Any anwer on this is something what is wasting time.

Cor

 

[William Vaughn]

First, define "Doesn't work".
Next, it's not a good idea to build "concatenated" statements where you fold
in the parameter values yourself in code. It's best to build a Command that
has a Parameters collection which handles any number of issues like string
framing, date management and also prevents SQL injection.
Next, I see where you're building up a SqlCommand object but you never
execute it. How do you expect the server to process the INSERT without
running the query?

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
http://betav.com http://betav.com/blog/billva
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________