Re: Problems running T-SQL statement ADO from VB .NET

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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)
____________________________________________________________________________________________


"Zaur Bahramov" <zbakhramov@xxxxxxx> wrote in message news:OZZQn1gOJHA.860@xxxxxxxxxxxxxxxxxxxxxxx
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!
--
Zaur Bahramov, MCP


.

Quantcast