Query for inserting data into a database

Hi.Can someone please attempt this and help me.

I am jus constructing a simple system that saves info into the database from 4 textboxes on the form. The three text boxes take in the title, surname and name of a given person. I have dropped an OleDbConnection Command on to the system thus setting up the connection to the database. I have also dropped an OleDbCommand object onto the form. I have renamed the OleDbCommand to ObjSaveCus. This will be my vehicle for saving a given persons name into the customer table in access. I have so far completed the following code:

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

Dim Title As String
Dim Surname As String
Dim Name As String

Title = txtCusTitle.Text
Surname = txtCusSurname.Text
Name = txtCusName.Text

ObjSaveCus.Parameters("Title").Value = Title
ObjSaveCus.Parameters("Surname").Value = Surname
ObjSaveCus.Parameters("Name").Value = Name

ObjOleDbConnection.Open()

ObjSaveCus.ExecuteNonQuery()

ObjOleDbConnection.Close()

MessageBox.Show("Record Inserted Sucessfully")

End Sub
End Class

I have been through the query builder at this point and the SQL statement there reads:

INSERT INTO Customer
([Customer ID], Name, Surname, Title)
VALUES ( , , , )

In that i want to enter the cusID, name, surname and title, however i dont know what the values are as of yet but i will gain this information from the form. It compiles without problem however when it gets as far down as 'ObjSaveCus.ExecuteNonQuery()' it comes up with a 'continue or break' error. I have been racking my brain for hours but cant find a way around this. Can anyone help me Sorry for the long posst.

Answer this question

Query for inserting data into a database

kay don
I tried all of the above and the error message is:

'An unhandled exception of type 'system.IndexOutOfRangeException' Occured in system.data.dll

Additional information: An OldDbParameter with ParameterName 'Title' is not contained by this OleDbParameter Collection.

Anyone know what this means
Adeel Hussain

Hi Nabz!

        Dim Name As String

        Dim CustomerID As String

        Title = txtCusTitle.Text
        Surname = txtCusSurname.Text
        Name = txtCusName.Text

        CustomerID = txtCusID.Text

        ObjSaveCus.Parameters("Cust").Value = CustomerID
        ObjSaveCus.Parameters("Title").Value = Title
        ObjSaveCus.Parameters("Surname").Value = Surname
        ObjSaveCus.Parameters("Name").Value = Name

Add one text for CustomeID as txtCusID and then run the routine.

Sohail.
ChristofWollenhaupt
Thanx for that Sohail but now there is an unhandled exception at ObjSaveCus.ExecuteNonQuery()

It accepts the parameters but breaks an unhandled exception when it comes to executing the insert.

Any ideas anybody
cs_coder

It means your collection of the parameters (number of the parameters) does not match to the number of the parameters in your statement. Based on error it looks like you do not have parameters in your command at all. Have you created parameters for the ObjSaveCus first Check next KB with sample how to use parameters for your SQL statement

http://support.microsoft.com/default.aspx scid=kb;en-us;308049&Product=adonet
kaandinc

Hi Nabz!

There is no problem with loger replies, longer infact gives more insight of what exactly is happening but not always though. Post your code and error/exception completely so that readers have a chance to uderstand whats going on.

Sohail.
Thomas Wolfram MVP

Hi!

I thot you are using VS 2005.

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        Dim Title As String
        Dim Surname As String
        Dim Name As String

        Dim CustID As String
        CustID = txtCustID.Text

        Title = txtCusTitle.Text
        Surname = txtCusSurname.Text
        Name = txtCusName.Text

        ObjSaveCus.Parameters.Add("[Customer ID]")

        ObjSaveCus.Parameters.Add("Title")
        ObjSaveCus.Parameters.Add("Surname")
        ObjSaveCus.Parameters.Add("Name")

        ObjSaveCus.Parameters("[Customer ID]").Value = CustID

        ObjSaveCus.Parameters("Title").Value = Title

        ObjSaveCus.Parameters("Surname").Value = Surname

        ObjSaveCus.Parameters("Name").Value = Name

        ObjOleDbConnection.Open()

        ObjSaveCus.ExecuteNonQuery()

        ObjOleDbConnection.Close()

        MessageBox.Show("Record Inserted Sucessfully")

    End Sub
End Class

cheers

Sohail
denaes

Hi!

Will you be kind enough to show me what exception you are getting so that anyone reading your post could get some idea of whats going on.

Sohail.
Broken

Hi!

You are not adding parameter to the command at all as I can see from you code there is nothing that adds these params to command object thats why at very fist parameter when you are trying to assing value its throwing exception. Check bolded lines or copy whole itself. Dont' forget to add one textbox for customer id.

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

Dim Title As String
Dim Surname As String
Dim Name As String

Dim CustID As String
CustID = txtCustID.Text

Title = txtCusTitle.Text
Surname = txtCusSurname.Text
Name = txtCusName.Text

ObjSaveCus.Parameters.AddWithValue("[Customer ID]", CustID)

ObjSaveCus.Parameters.AddWithValue("Title", Title)
ObjSaveCus.Parameters.AddWithValue("Surname", Surname)
ObjSaveCus.Parameters.AddWithValue("Name", Name)

ObjOleDbConnection.Open()

ObjSaveCus.ExecuteNonQuery()

ObjOleDbConnection.Close()

MessageBox.Show("Record Inserted Sucessfully")

End Sub
End Class
CosmicFred
Has anyone got any ideas where im going wrong wih this
Pinal Patel
Thanx for your replies.

above where you told me to write:

objSavecus.Parameters.AddWithValue....

the system doesnt recognise the 'addwithvalue' bit. It only recognises add.

Any more suggestions Anything appreciated.
steveculshaw
Thanx for the Sohail! I think i have a problem with my SQL also. I built it in query builder and although i have specified which fields i want to add a record to, in the value column of query builder i hatve put = to show that they will come from the form. Is this correct
Query for inserting data into a database

Answer this question