Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Sever Insert Into

Posted on 2013-05-25
2
Medium Priority
?
425 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
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

572 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