?
Solved

SQL Sever Insert Into

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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

589 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