Problems with Datagridview and datasets

I will try to not make this too confusing.
I have the following which works fine.
this.addressTableAdapter.Fill(this.dataSet1.Address); //loads all db info into dataGridView1
this.addressTableAdapter.Update.(this.dataSet1.Address);  //saves the changed info back to db

Below is some code I wrote to do a manual query.  The data shows up in the same dataGridView1.  and also using same dataSet1.  I just can't get it to save.
OdbcConnection con = new OdbcConnection("DSN=ShipData");
            string sqlQuery = "SELECT * FROM Address where " + searchfield.Text.ToString() + " LIKE \'%" + searchvalue.Text.ToString() + "%\'";
            OdbcCommand com = new OdbcCommand(sqlQuery, con);
            OdbcDataAdapter da = new OdbcDataAdapter(sqlQuery, con);
            DataSet1 ds = new DataSet1();
            da.Fill(ds, "Address");
            dataGridView1.DataMember = "Address";
            dataGridView1.DataSource = ds;

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
Hi casit,

How are you doing the update? With your code snippet, the DataAdapter is declared at procedure level only. You'll need that same DataAdapter ('da') to be able to perform the Update, so declare it at class level (ie, outside any procedure), as well as the DataSet. Then you can perform the update like this....



casitAuthor Commented:
Sorry just really starting out at C#.  I got the fill and update stuff from the wizard :)  The other stuff is stuff I came up with myself.  However my stuff doesn't update the db.
Wayne Taylor (webtubbs)Commented:

See my post for how you need to Update from manually created objects.

Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

casitAuthor Commented:
Alrighty I got some of it figured out now.

However now I'm getting the following error.  I'm so close now :)
System.Data.Odbc.OdbcException was unhandled
  Message="ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 22."
       at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
       at FedEx_Shared_Addy.frmSharedAddy.saveDataToolStripMenuItem_Click(Object sender, EventArgs e) in C:\Users\chris\Documents\Visual Studio 2005\Projects\FedEx Shared Addy\Form1.cs:line 79
       at FedEx_Shared_Addy.frmSharedAddy.savechanges_Click(Object sender, EventArgs e) in C:\Users\chris\Documents\Visual Studio 2005\Projects\FedEx Shared Addy\Form1.cs:line 89
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at FedEx_Shared_Addy.Program.Main() in C:\Users\chris\Documents\Visual Studio 2005\Projects\FedEx Shared Addy\Program.cs:line 17
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
Wayne Taylor (webtubbs)Commented:
Please post the code segment which caused the error.

casitAuthor Commented:
Here is my whole form.cs file
That error gets thrown when it hits function
saveDataToolStripMenuItem_Click -> da.Update(dt);
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;
namespace FedEx_Shared_Addy
    public partial class frmSharedAddy : Form
        public frmSharedAddy()
        OdbcConnection con = new OdbcConnection("DSN=ShipData");
        OdbcDataAdapter da = new OdbcDataAdapter();
        DataTable dt = new DataTable();
        private void frmSharedAddy_Load(object sender, EventArgs e)
            // TODO: This line of code loads data into the 'dataSet1.Address' table. You can move, or remove it, as needed.
            // this gets all rows from db.
        private void search_Click(object sender, EventArgs e)
            string sqlQuery = "SELECT * FROM Address where " + searchfield.Text.ToString() + " LIKE \'%" + searchvalue.Text.ToString() + "%\'";
            OdbcCommand com = new OdbcCommand(sqlQuery, con);
            da.SelectCommand = com;
            OdbcCommandBuilder cmbuilder = new OdbcCommandBuilder(da);
            dataGridView1.DataSource = dt;
        private void saveDataToolStripMenuItem_Click(object sender, EventArgs e)
        private void delete_btn_Click(object sender, EventArgs e)

Open in new window

Wayne Taylor (webtubbs)Commented:
Try specifying the QuotePrefix and QuoteSuffix properties of the CommandBuilder. There could be fields with spaces in them, which the Insert, Update and Delete commands won't like.

OdbcCommandBuilder cmbuilder = new OdbcCommandBuilder(da);
cmbuilder.QuotePrefix = "[";
cmbuilder.QuoteSuffix = "]";

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
casitAuthor Commented:
Thank you thank you.  I would have never ever gotten that :)  works beautifully.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.