Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Sever Insert Into

Posted on 2013-05-25
2
Medium Priority
?
420 Views
Last Modified: 2013-05-26
I have written the following statement to insert into a record. The statement works except the value from the text box is not going into the record my variable is.

 

 Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim Var1 As String = TXTDB1.Text

        Dim Var2 As String = TXTDB2.Text

        Dim Var3 As String = TXTDB3.Text

        Dim Var4 As String = TXTDB5.Text

        Dim Var5 As String = TXTDB4.Text


        Dim sqlConnection3 As New System.Data.SqlClient.SqlConnection("Data Source=RHINO-ET\BLACKRHINO;Initial Catalog=BlackRhino;Integrated Security=True")

        Dim cmd As New System.Data.SqlClient.SqlCommand
        cmd.CommandType = System.Data.CommandType.Text
        ' cmd.CommandText = "INSERT Region (RegionID, RegionDescription) VALUES (5, 'NorthWestern')"
        cmd.CommandText = "INSERT INTO Picture(Asset, Description, Location, Asset_Img, IMG_Location) VALUES('Var1', 'Var2', 'Var3', 'Var4', 'Var5')"
        cmd.Connection = sqlConnection3





        Try

            sqlConnection3.Open()

            cmd.ExecuteNonQuery()

            lblMessage.Text = "Record inserted successfully"

        Catch ex As Exception

            lblMessage.Text = "Unable to insert record"

        Finally

            sqlConnection3.Close()

        End Try
        TXTDB1.Text = " "
        TXTDB2.Text = " "
        TXTDB3.Text = " "
        TXTDB4.Text = " "
        TXTDB5.Text = " "


    End Sub

Open in new window

Result
The result of my insert into statement
0
Comment
Question by:laskydiver
[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
2 Comments
 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 1500 total points
ID: 39197367
Hi,
you assign the strings var1 to var5 directly and not the variables content.
Quick way:
cmd.CommandText = String.Format("INSERT INTO Picture(Asset, Description, Location, Asset_Img, IMG_Location) VALUES('{0}', '{1}', '{2}', '{3}', '{4}')",Var1,Var2,Var3,Var4,Var5)

Open in new window


Secure way:
Using parametrized insert statements:
http://www.vistadb.net/tutorials/insertrows-vb.aspx

HTH
Rainer
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39197685
Unless I'm missing something here, variables need to be outside of the double-quotes, otherwise you're passing the variable name.

cmd.CommandText = "INSERT INTO Picture(Asset, Description, Location, Asset_Img, IMG_Location) VALUES('" & Var1 & "', '" & Var2 & "', '" & Var3 & "', '" & Var4 & "', '" & Var5 & "')"

You can aways test this using step-through debugging like this (Access VBA, so modify to suit your needs.

Dim sSQL as String

sSQL =  "INSERT INTO Picture(Asset, Description, Location, Asset_Img, IMG_Location) VALUES('" & Var1 & "', '" & Var2 & "', '" & Var3 & "', '" & Var4 & "', '" & Var5 & "')"

Debug.Print sSQL   ' This dumps sSQL into your Immediate Window (Ctrl-G), so you can verify it's working correctly.   Delete once you're good.


cmd.CommandText = sSQL
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

610 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