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

casitAsked:
Who is Participating?
 
Wayne Taylor (webtubbs)Connect With a Mentor 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.

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

Open in new window

0
 
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....

    da.Update.(ds);

Regards,

Wayne
0
 
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.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Wayne Taylor (webtubbs)Commented:
casit

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

Wayne
0
 
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."
  Source="odbcjt32.dll"
  ErrorCode=-2146232009
  StackTrace:
       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()
0
 
Wayne Taylor (webtubbs)Commented:
Please post the code segment which caused the error.

Wayne
0
 
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()
        {
            InitializeComponent();
        }
        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.addressTableAdapter.Fill(this.dataSet1.Address);
            // this gets all rows from db.
            //this.addressTableAdapter.Fill(this.dataSet2.Address);
        }
 
 
 
 
        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);
            da.Fill(dt);
            dataGridView1.DataSource = dt;
        }
 
 
        private void saveDataToolStripMenuItem_Click(object sender, EventArgs e)
        {
            dataGridView1.BindingContext[dt].EndCurrentEdit();
            da.Update(dt);
        }
 
        private void delete_btn_Click(object sender, EventArgs e)
        {
            dataGridView1.Rows.RemoveAt(dataGridView1.SelectedRows[0].Index);
        }
    }
}

Open in new window

0
 
casitAuthor Commented:
Thank you thank you.  I would have never ever gotten that :)  works beautifully.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.