Go Back   Computer Forums > General Computing > Programming
Join Computer forums Today

Thread Tools Search this Thread Display Modes
Old 03-21-2009, 03:37 PM   #1
Baseband Member
Join Date: Nov 2008
Posts: 91
Default dataAdapter.Update problem

dataAdapter.Update problem
I can read in an SQL table ("Person.Contact") from AdventureWorks and step through it one row at a time, but when I try to save a record, either one I'm inserting or one I'm editting, I get the following exception:

Incorrect syntax near ','. Must declare scalar variable "@ContactID".

Here's the code:
private void btnSave_Click (object sender, EventArgs e)
    DataRow row = dataTable.Rows [currentRecord];
    row.BeginEdit ();

    // get data from input TextBoxes
    row ["ContactID"]    = txtContactID.Text;
    row ["FirstName"]    = txtFirstName.Text;
    row ["LastName"]     = txtLastName.Text;
    row ["Phone"]        = txtPhone.Text;
    row ["EmailAddress"] = txtEmailAddress.Text;

    row.EndEdit ();

    try { dataAdapter.Update (dataSet, "Person.Contact"); }  // <--PROBLEM
    catch (Exception exc) { MessageBox.Show (exc.Message); }

    dataSet.AcceptChanges ();
I don't think the problem is with initializing the SQL commands. Here's the code for that (shown without the "Delete SQL Command" section). No exceptions are thrown.

private void InitializeCommands ()
    // Preparing Insert SQL Command
        dataAdapter.InsertCommand = conn.CreateCommand ();
        dataAdapter.InsertCommand.CommandText = 
            "INSERT INTO Person.Contact (ContactID, FirstName, LastName, 
            Phone, EmailAddress) VALUES (@ContactID, @FirstName, @LastName, 
            @Phone, @EmailAddress)";
        AddParams (dataAdapter.InsertCommand, "ContactID, FirstName, 
            LastName, Phone, EmailAddress");
    catch (Exception exc) { MessageBox.Show (exc.Message, "InsertCommand"); }

    // Preparing Update SQL Command
        dataAdapter.UpdateCommand = conn.CreateCommand ();
        dataAdapter.UpdateCommand.CommandText = 
            "UPDATE Person.Contact SET FirstName = @FirstName, LastName = 
            @LastName, Phone = @Phone, EmailAddress = @EmailAddress 
            WHERE ContactID = @ContactID";
        AddParams (dataAdapter.UpdateCommand, "ContactID, FirstName, 
            LastName, Phone, EmailAddress");
    catch (Exception exc) { MessageBox.Show (exc.Message, "UpdateCommand"); }

// add column name(s) supplied in params (prefixed with '@') into Parameters 
// collection of SqlCommand class
// SqlDbType.Char: type of parameter, 0: size of parameter, column: column 
// name
private void AddParams (SqlCommand cmd, params string [ ] columns)
    foreach (string column in columns)
        cmd.Parameters.Add ("@" + column, SqlDbType.Char, 0, column); 
Any ideas?

On the off-chance that this will help, this is the exact kind of exception that's occurring:


Are there any other properties of the Exception class besides Message and StackTrace that might help me figure this out?

BobLewiston is offline   Reply With Quote
Old 03-23-2009, 12:32 PM   #2
In Runtime
Daeva's Avatar
Join Date: Dec 2005
Posts: 407
Send a message via AIM to Daeva Send a message via MSN to Daeva Send a message via Yahoo to Daeva
Default Re: dataAdapter.Update problem

Looks to me like you're passing in the cmd object of your sql command to your AddPrams function.

This seems like it would work, but I'm not sure in C# if by default the arguments are passed by reference or by value. If they are passed by value, then your parameters are not being set because you don't have a solid handle for your cmd parameter.

Instead, you could try:
SqlCommand cmd = new SqlCommand("myCommand",myConnection);

Then, pass this cmd object into AddParams and make AddParams return a SqlCommand Object, instead of nothing

private SqlCommand AddParams(SqlCommand cmd, params string [ ] columns) {
foreach(string column in columns) {
cmd.Parameters.Add("@" + column, SqkDBType.Char,0,column);
return cmd;

Then, set it like this:
dataAdapter.InsertCommand = AddParams(cmd,"name1,name2,etc");

See if that helps you out at all, the same should apply for the update command.

Also, exceptions won't be thrown when you initialize the commands because the compiler doesn't know how many parameters you're supposed to have. If there is a "data" error, it won't occur until you run the query.
**Official Self-proclaimed glorified excessive (insert additional adjectives here) post editor/modifier.
Edit = Best feature ever
Daeva is offline   Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

All times are GMT -5. The time now is 06:44 PM.

Powered by vBulletin® Version 3.8.8 Beta 4
Copyright ©2000 - 2016, vBulletin Solutions, Inc.
Search Engine Friendly URLs by vBSEO 3.6.0