Member Avatar for IT21

hey im in the middle of creating a little program that allows a user to search and if in admin mode can delete and add a new record to the database i have my search part working fine
i have made the query using the dataset.xsd query builder i used the select, from where query after making the query i have gone to my form and called it using Me.peopleTableAdapter.FillByPeople(systemDataSet.people, Me.peopleTextBox.Text, Me.peopleTextBox.Text, Me.ageTextBox.Text, Me.ageTextBox.Text)
my problem is how do i insert into by database using the dataset.xsd query builder and then calling it in the same way rather than doing dimcon as new connection as i have set my connection string in the settings of my application
i had made the query
INSERT INTO Admin
(person_Forename, Person_Surname, Admin_Username, Admin_Password)
VALUES (and listed the textboxes used to enter data here)
and then i called it in the same way as i did the select query
but it does not seem to work i dont know where i am going wrong and would really yappriciate if someone could tell me

  • 2 Contributors
  • 3 Replies
  • 136 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by IT21

Dani AI

Generated

Short diagnosis and the simplest fix

The error you see usually means the command you executed either had parameter placeholders with no parameters added, or Access could not find one of the column names (Access treats unknown names as parameters). pointed you toward configuring a DataAdapter — that is the right direction. If you used the typed DataSet/TableAdapter designer (dataset.xsd), the easiest and most robust approach is to let the TableAdapter generate an Insert method and call it from the form instead of manually building commands.

Two recommended workflows

Using the generated TableAdapter Insert method (preferred when you used the designer):

' call the Insert method created by the dataset designer
Me.YourTableAdapter.Insert(txtForename.Text, txtSurname.Text, txtUser.Text, txtPassword.Text)

Using the DataTable then Update pattern (also safe for typed datasets):

Dim r = SystemDataSet.YourTable.NewYourRow()
r.Forename = txtForename.Text
r.Surname  = txtSurname.Text
SystemDataSet.YourTable.Rows.Add(r)
Me.YourTableAdapter.Update(SystemDataSet.YourTable)

What to check if you must build commands manually

  • OleDb with ? placeholders is positional: add OleDbParameter objects in the exact order of the placeholders. Parameter names are ignored by the provider.
  • Verify every column name used in the SQL exists and is spelled correctly; a misspelled column becomes a parameter.
  • Do not call AcceptChanges() before Update(); calling it before Update will clear the changed state so nothing is sent to the database.
  • Open/close the connection properly and inspect InsertCommand.Parameters.Count at runtime to ensure it matches the number of placeholders.
  • Use try/catch and log CommandText plus parameter values to test the SQL directly in Access if needed.

If the TableAdapter was configured by the dataset wizard, calling its generated Insert or using its Update on the typed DataTable will avoid manual parameter ordering and is usually the fastest path to a working insert.

Recommended Answers

All 3 Replies

Member Avatar for kvprajapati

>how do i insert into by database using the dataset.xsd query builder and then calling it in the same way rather than doing

Take a look at MSDN - r?

Member Avatar for IT21

thanks for the link i have followed what it has said to do and now have the dataadapter manually made with the querys but do you know how i call them?

Member Avatar for IT21

i have created a dataadapter manually and through the wizrd the select,insert,delete and update commands where built. i now need to know the code to call the commands i am trying to insert data from text boxes on a form into the database this is the code i have

OleDbConnection1.Open()
        OleDbDataAdapter1.InsertCommand = New OleDb.OleDbCommand("Insert into Admin(Employee_Forename, Employee_Surname, Admin_Username, Admin_Password) Values (?,?,?,?)", OleDbConnection1)

        OleDbDataAdapter1.InsertCommand().CommandText = ("Insert into Admin(Employee_Forename, Employee_Surname, Admin_Username, Admin_Password) Values (?,?,?,?)")
        OleDbDataAdapter1.InsertCommand.ExecuteNonQuery()

        OleDbDataAdapter1.Update(SystemDataSet)
        SystemDataSet.AcceptChanges()
        OleDbDataAdapter1.Fill(SystemDataSet)

but im getting an error saying a value isnt being given to one or more of the parameters
could someone please tell me where im going wrong

Edited by kvprajapati because: Added [code] tags.
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.