[RESOLVED] RESOLVED (Can't save textbox data into sql database, Help!)

Hi

I am currently puting together a data driven application just for fun and to get to know sql. The problem i am having is on my new customer form i can not get the data in my text boxes to save into my database table at runtime.

I simply have 4 textboxes and a save button the code for my save button is as follows:

Code:

---

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim Connection As SqlConnection = MSDB.GetConnection
        Dim mySqlCommand As SqlCommand = New SqlCommand
        Connection.Open()

        mySqlCommand.Connection = Connection
        Try
            mySqlCommand.CommandText = "INSERT INTO customers (CustomerID, Customer, Contact, Email) VALUES ('@CustomerID', '@Customer', '@Contact', '@Email')"

            Dim InsertCommand As SqlCommand = New SqlCommand
            InsertCommand.Parameters.AddWithValue("@CustomerID", tbCustomerID.Text)
            InsertCommand.Parameters.AddWithValue("@Customer", tbCustomer.Text)
            InsertCommand.Parameters.AddWithValue("@Contact", tbContact.Text)
            InsertCommand.Parameters.AddWithValue("@Email", tbEmail.Text)

            mySqlCommand.ExecuteNonQuery()
            MessageBox.Show("New Customer Added To Your Database")
        Catch ex As Exception
            MessageBox.Show("Customer could not be added!" & ex.Message)
        Finally
            If Connection.State = ConnectionState.Open Then
                Connection.Close()
            End If
        End Try

    End Sub

---

All this does is bring up an error in runtime please can someone help me fix this so that when i press save the details in my text boxes are actually saved into my database table

Thanks Roo

Since you are adding parameters, you need to Prepare the statement prior to execution.

Put this right after your add the parameters:

Code:

---

InsertCommand.Prepare

---

Quote:

---

Originally Posted by Roofuss

All this does is bring up an error in runtime

---

This doesn't do us any good...what is the error that it brings up and what line of code causes it?

You know what I just realized?

Why are you using TWO Command objects? Why are you putting the SQL into 1 object, then the parameters in to the other?

You need to use one or the other.

Thanks for the quick relpy

i have dont that and the error message that comes up now says: "Object Reference not set to an instance of an object" ? so now my code looks as follows :

Code:

---

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim Connection As SqlConnection = MSDB.GetConnection
        Dim mySqlCommand As SqlCommand = New SqlCommand
        Connection.Open()

        mySqlCommand.Connection = Connection
        Try
            mySqlCommand.CommandText = "INSERT INTO customers (CustomerID, Customer, Contact, Email) VALUES ('@CustomerID', '@Customer', '@Contact', '@Email')"

            Dim InsertCommand As SqlCommand = New SqlCommand
            InsertCommand.Parameters.AddWithValue("@CustomerID", tbCustomerID.Text)
            InsertCommand.Parameters.AddWithValue("@Customer", tbCustomer.Text)
            InsertCommand.Parameters.AddWithValue("@Contact", tbContact.Text)
            InsertCommand.Parameters.AddWithValue("@Email", tbEmail.Text)
            InsertCommand.Prepare()
            mySqlCommand.ExecuteNonQuery()
            MessageBox.Show("New Customer Added To Your Database")
        Catch ex As Exception
            MessageBox.Show("Customer could not be added!" & ex.Message)
        Finally
            If Connection.State = ConnectionState.Open Then
                Connection.Close()
            End If
        End Try

    End Sub

---

any ideas on how to resolve this?

Quote:

---

Originally Posted by Campion

You know what I just realized?

Why are you using TWO Command objects? Why are you putting the SQL into 1 object, then the parameters in to the other?

You need to use one or the other.

---

Lol thanks for pointing that out i was tired when i started this but ive fixed that now the error im getting is

SqlCommand.Prepare method requires all parameters to have an explicitly set type.

and my code know looks like this?

Code:

---

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim Connection As SqlConnection = MSDB.GetConnection
        Dim mySqlCommand As SqlCommand = New SqlCommand
        Connection.Open()

        mySqlCommand.Connection = Connection
        Try
            mySqlCommand.CommandText = "INSERT INTO customers (CustomerID, Customer, Contact, Email) VALUES ('@CustomerID', '@Customer', '@Contact', '@Email')"
            mySqlCommand.Parameters.AddWithValue("@CustomerID", tbCustomerID.Text)
            mySqlCommand.Parameters.AddWithValue("@Customer", tbCustomer.Text)
            mySqlCommand.Parameters.AddWithValue("@Contact", tbContact.Text)
            mySqlCommand.Parameters.AddWithValue("@Email", tbEmail.Text)
            mySqlCommand.Prepare()
            mySqlCommand.ExecuteNonQuery()
            MessageBox.Show("New Customer Added To Your Database")
        Catch ex As Exception
            MessageBox.Show("Customer could not be added!" & ex.Message)
        Finally
            If Connection.State = ConnectionState.Open Then
                Connection.Close()
            End If
        End Try

    End Sub

---

Get rid of the apostrophes in the values section of your SQL statement, and that should resolve that issue.

Thanks Campion you legend ive done that and its working perfect now .