Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 dataAdapter.Update problem

Author  Topic 

BobLewiston
Starting Member

29 Posts

Posted - 2009-03-21 : 17:06:44
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
try
{
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
try
{
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?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-22 : 14:06:34
change the call to AddParams as
AddParams (dataAdapter.UpdateCommand, "ContactID", "FirstName", 
"LastName", "Phone", "EmailAddress")

That will fix the specific error you are getting, but I don't think that will update the Person.Contact table. May be they will if the parameter values are set somewhere else. After returning from AddParams, look at the parameter collection of the command object and see if they have the values you want.
Go to Top of Page

BobLewiston
Starting Member

29 Posts

Posted - 2009-03-22 : 17:01:36
sunitabeck:

Thanks for your help. I THINK it may be helping me get closer to being able to insert new records.

As you suggested, I changed the code from
AddParams (dataAdapter.UpdateCommand, "ContactID, FirstName, LastName, Phone, EmailAddress");

to
AddParams (dataAdapter.UpdateCommand, "ContactID", "FirstName", "LastName", "Phone", "EmailAddress");

but at first it had no effect.

But then I also changed the InsertCommand code from
AddParams (dataAdapter.InsertCommand, "ContactID, FirstName, LastName, Phone, EmailAddress");

to
AddParams (dataAdapter.InsertCommand, "ContactID", "FirstName", "LastName", "Phone", "EmailAddress");

I didn't get the error I originally posted about, but I got a different error:

" Cannot insert explicit value for identity column in table 'Contact' when IDENTITY_INSERT is set to OFF. "

For both the error I originally posted about and the above error, for what it's worth, the exact kind of exception I got was:

" System.Runtime.InteropServices.ExternalException "

I guess all this means is that the error is occurring outside of my program.

ContactID is the key column in table Person.Contact, so maybe the message about IDENTITY_INSERT is telling me that I can't insert a new ContactID in the table until I somehow adjust some attribute of the table to allow me to do so.

However, I can't find the AdventureWorks tables via Windows' file system (SQL Server's database engine finds them for me), so I guess they're internal to other files unknown to me, and it's not just a matter of adjusting the tables' file attributes in Windows.

And in SQL Server Management Studio, I can't find any way to check for any table attributes such as "no insertions".

>After returning from AddParams, look at the parameter collection of the command object and see if they have the values you want.

Yes, the parameters in AddParams’ SqlCommand object are "@ContactID", "@FirstName", "@LastName", "@Phone", "@EmailAddress" all right.

Put all this together and I THINK it means that your suggestion, when applied to the InsertCommand as well as the UpdateCommand, has fixed my original problem, and that I now just need to find out how to tell SQL Server to allow me to insert a new record in the table. Would you have any ideas about this? Or maybe you disagree with my analysis of the situation?

BTW, do you know of any other properties of the Exception class besides Message and StackTrace that might help me figure this out?

Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-22 : 17:27:22
If you are using the AdventureWorks database distributed with the SQL product, the ContactID column is an identity column. As the error message indicates, you cannot insert data into identity column unless you set identity_insert to ON. By default, identity_insert is set to OFF.

So you have two choices (for inserting):
A) Change your query and code so you don't send the ContactID in the insert statements; i.e., remove ContactID parameter from the insert statement and in the parameter list. SQL will automatically assign a ContactID and store it in the database.

B) Make a database call to explicitly set identity_insert to on. If you go this route, use the SAME connection object to make the identity_insert off call and then the update call; identity_insert on setting is valid only within the context of the connection object.

For various reasons, I would prefer (A) over (B). Information about identity columns are available here: http://www.sqlteam.com/article/understanding-identity-columns and in SQL Books Online.
Go to Top of Page

BobLewiston
Starting Member

29 Posts

Posted - 2009-03-24 : 12:16:06
sunitabeck:

Why would you prefer (A) over (B)? And are there any disadvantages to implementing (A) instead of (B)?
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-24 : 19:23:03
Usually, when a column is designed as an identity column, that is intended as an auto-increment column where you let the server decide the next value to be inserted into it. If you want to override that behavior, you have to set the identity_insert to ON, and find a value that will not conflict with existing values. So, unless there is a compelling reason to override the default behavior, I prefer simply letting the server do its thing.

If you do follow that pattern, you can retrieve the value inserted into the identity column using the SCOPE_IDENTITY() SQL function. When calling from C#, make sure you make the call immediately after the call and in the same connection. I think the ADO datasets may even have pre-built facility to retrieve the scope_identity() value.
Go to Top of Page
   

- Advertisement -