Solved

SQL Sever Insert Into

Posted on 2013-05-25
2
416 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 500 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 65

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

860 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