Inserting data into SQL database

Question

I wrote code to insert TextBox data into SQL database. My code working properly but when I open the table no data was added. Here is my code:

Private Sub save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles save.Click


    Dim connetionString As String
    Dim connection As SqlConnection
    Dim adapter As New SqlDataAdapter
    Dim tabl As New DataTable
    Dim sql As String


    connetionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"
    connection = New SqlConnection(connetionString)
    Try
        sql = "insert into model (no,fistname,lastname) values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "')"
        adapter.InsertCommand = New SqlCommand(sql, connection)
        connection.Open()

        adapter.InsertCommand.ExecuteNonQuery()

        MsgBox("Row inserted !! ")
        connection.Close()

    Catch ex As Exception
        MsgBox(ex.ToString)
        connection.Close()
    End Try

End Sub

Justin Niessner · Accepted Answer · 2010-03-05 13:54:17Z

9

Don't use a Data Adapter. That just over-complicates things in this case. Try:

Using SqlConnection connection = new SqlConnection(connectionString)

    sql = "insert into model (no, firstname, lastname)" & _ 
        " values (@val1, @val2, @val3)"

    Dim SqlCommand command = new SqlCommand(sql, connection)

    command.Parameters.Add("val1", TextBox1.Text)
    command.Parameters.Add("val2", TextBox2.Text)
    command.Parameters.Add("val3", TextBox3.Text)

    command.ExecuteNonQuery()

End Using

This way, you don't have to worry about the Adapter (since you're not using a GridView) and you're using parameterized queries rather than dynamically building SQL (which allows for SQL Injection attacks).

edited Mar 5, 2010 at 13:54

answered Mar 5, 2010 at 13:47

Justin Niessner

246k40 gold badges416 silver badges546 bronze badges

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

3 Comments

Andomar Over a year ago

-1 for dragging SQL injection into an unrelated question, +1 for a working answer and +1 for data adapters over-complicating things :)

Philip Kelley Over a year ago

Besides SQL injection, it might also be worth mentioning error checking/trapping...?

Justin Niessner Over a year ago

I only posted a subset of his original code. He does have everything wrapped in a Try/Catch block. Although it would be worth mentioning that if he doesn't use a Using block around the call...he should add a finally to the try/catch to Dispose of everything.

Pointy · Accepted Answer · 2010-03-05 13:46:28Z

1

I'll go way out on a limb here — I'm not even sure what language that is — and suggest that perhaps you need to explicitly commit your database transaction.

answered Mar 5, 2010 at 13:46

Pointy

415k62 gold badges600 silver badges632 bronze badges

Comments

user207462 · Accepted Answer · 2010-03-05 13:57:02Z

1

How do you confirm that the data was not inserted?

I suspect your issue may be related to using User Instances of SQL Express. See http://msdn.microsoft.com/en-us/library/bb264564%28SQL.90%29.aspx

answered Mar 5, 2010 at 13:57

user207462

914 bronze badges

Collectives™ on Stack Overflow

Inserting data into SQL database

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related