SQL command will not insert into database

Question

I'm trying to use a VB button to insert data into a database, but it keeps bringing up the error message I have in place for exceptions.

Can anyone help me with why this does not update the database?

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

    Dim connetionString As String
    Dim sqlCnn As SqlConnection
    Dim sql As String
    Dim adapter As New SqlDataAdapter
    Dim Customer As String = TextBox1.Text
    Dim Product As String = TextBox2.Text
    Dim Location As String = TextBox3.Text
    Dim Details As String = TextBox4.Text
    Dim Owners As String = DropDownList1.Text
    Dim Urgency As String = DropDownList2.Text


    connetionString = "Data Source=ZUK55APP02;Initial Catalog=BugFixPortal;User ID=SLC***;Password=rep***"
    sql = "INSERT INTO Requests (Owner, Customer, Product, Location, Urgency, Details) VALUES ('" & Owners & ", " & Customer & ", " & Product & ", " & Location & ", " & Urgency & ", " & Details & "')"
    sqlCnn = New SqlConnection(connetionString)

    Try
        sqlCnn.Open()
        adapter.UpdateCommand = sqlCnn.CreateCommand
        adapter.UpdateCommand.CommandText = sql
        adapter.UpdateCommand.ExecuteNonQuery()
        sqlCnn.Close()

    Catch ex As Exception
        MsgBox("Unable to update Database with Request - Please speak to Supervisor!")

    End Try

End Sub

Don't spit out fixed text for a db exception. The exception should contain the exact error message. most like you've got an sql syntax error, due to being having a gaping wide open sql injection problem. — Marc B
– Marc B, Commented May 25, 2012 at 16:31
Apparently you're still enough of a newbie to concatenate strings straight from user-enterable fields. You're vulnerable to SQL Injection. — Clockwork-Muse
– Clockwork-Muse, Commented May 25, 2012 at 16:32

Massimiliano Peluso · Accepted Answer · 2012-05-25 16:47:05Z

2

I would not go down this road as your code is weak against SQL Injection

you should use parameters instead.Something like the below

c.Open();
string insertString = @"insert into YourTable(name, street, city,....) values(@par1,  @par2, @parN,....)"
SqlCommand cmd = new SqlCeCommand(insertString, c);
cmd.Parameters.Add("@par1", SqlDbType.VarChar).Value = "MyName";
//etc
cmd.ExecuteNonQuery();
c.Close();

edited May 25, 2012 at 16:47

answered May 25, 2012 at 16:35

Massimiliano Peluso

26.8k6 gold badges65 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

D'Arcy Rittich · Accepted Answer · 2012-05-25 16:32:21Z

1

You are incorrectly quoting your values.

This string has an opening and closing single quote around ALL the values, which is incorrect.

VALUES ('" & Owners & ", " & Customer & ", " & Product & ", " & Location & ", " & Urgency & ", " & Details & "')"

Instead, put single quotes around character data, eg., if Product is a varchar, it would look like this:

VALUES (" & Owners & ", " & Customer & ", '" & Product & "', " & Location & ", " & Urgency & ", " & Details & ")"

The real problem, though, is that you should be using parameterized queries instead. This code is prone to SQL injection attacks.

answered May 25, 2012 at 16:32

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Comments

Phill · Accepted Answer · 2012-05-25 16:34:46Z

1

Change this;

MsgBox("Unable to update Database with Request - Please speak to Supervisor!")

to Something like this;

MsgBox("Unable to update Database with Request - Please speak to Supervisor!" & ex.Message)

It will give you more details on the exception, however at a quick glance I can see a problem, the values you are trying to insert are strings, you've enclosed all your values in a single set of ' characters, rather than enclosing each string parameter in a pair of ' values, i.e.

sql = "INSERT INTO Requests (Owner, Customer, Product, Location, Urgency, Details) VALUES ('" & Owners & "', '" & Customer & "', '" & Product & "',' " & Location & "', '" & Urgency & "', '" & Details & "')"

answered May 25, 2012 at 16:34

Phill

1,43312 silver badges14 bronze badges

Comments

Mathew Thompson · Accepted Answer · 2012-05-25 16:40:57Z

You really should look at parameterizing your queries as you're wide open to SQL injection attacks. See HERE

In terms of your code itself, your SQL syntax is wrong as you need to put apostrophes around each value. Try this:

sql = "INSERT INTO Requests (Owner, Customer, Product, Location, Urgency, Details)
VALUES ('" & Owners & "', '" & Customer & "', '" & Product &
     "', '" & Location & "', '" & Urgency & "', '" & Details & "')"

Here's an example using Parameters

sql = "INSERT INTO Requests (Owner, Customer, Product, Location, Urgency, Details)
VALUES ('@Owners', '@Customer', '@Product', '@Location', '@Urgency', '@Details')"

Then add parameters like so:

command.Parameters.AddWithValue("@Owners", Owners)
command.Parameters.AddWithValue("@Customer", Customer)
command.Parameters.AddWithValue("@Product", Product)
command.Parameters.AddWithValue("@Location", Location)
command.Parameters.AddWithValue("@Urgency", Urgency)
command.Parameters.AddWithValue("@Details", Details)

Minus · Accepted Answer · 2012-05-25 16:46:44Z

0

I think you want to use adapter.InsertCommand instead of adapter.UpdateCommand

in

Try
    sqlCnn.Open()
    adapter.UpdateCommand = sqlCnn.CreateCommand //(adapter.InsertCommand)
    adapter.UpdateCommand.CommandText = sql //(adapter.InsertCommand)
    adapter.UpdateCommand.ExecuteNonQuery() //(adapter.InsertCommand)
    sqlCnn.Close()

Catch ex As Exception
    MsgBox("Unable to update Database with Request - Please speak to Supervisor!")

End Try

and agree with parametrized sql query

see http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.aspx for more infos

answered May 25, 2012 at 16:46

Minus

7298 silver badges20 bronze badges

Collectives™ on Stack Overflow

SQL command will not insert into database

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related