Problem with adding fields to existing table/schema

Question

0.00/5 (No votes)

See more:

I’m working on a .Net application in Visual Studio 2008. It contains schemas in a schema project and in a SQL Server project. I’m not very familiar with SQL Server projects and I’m having a problem with updating records that use the insert statement in the server project. I had to add two fields to a table in the SQL database. There’s a winform that displays the data for editing. If I update the table row in SSMS, the data will display in the form (it’s using the schema in the schema project). Any changes made to the data in the two new fields are not saved and if any of the other fields are updated, then the data in the two new fields disappear. I can’t find any error messages. The save is using the CLR_SP and the schema in the server project to update the table.

I can debug the code enough to see the new fields are in the datarow that is passed to the insert row code, but I can’t figure out how to debug past that to see the actual insert statement.

I found some old notes that said the server project should be deployed if there are changes to the schema. I’ve searched for more information, but haven’t found anything that explained if this is causing the problem. Is the insert using the old schema to insert the data even though the new fields are passed to it? Should there be error messages somewhere? I understand that deploying creates an assembly that's put on the server, but I can’t find where it currently is on the server.

Any help or insight is greatly appreciated!

Posted 22-Jun-15 15:44pm

cindy_m

Updated 22-Jun-15 15:51pm

v2

Add a Solution

Comments

Mycroft Holmes 22-Jun-15 22:44pm

How are you communicating with the database?
Are you using a stored procedure to do the update/insert (CLR_SP seems to indicate this)? If so then check that the stored proc has the required variables for the new fields

cindy_m 22-Jun-15 23:18pm

Yes, it's a CLR_SP in the server project. I added the new fields to the insert statement that is called by the stored procedure.

Mycroft Holmes 22-Jun-15 23:57pm

You also need to make sure the stored procedure has the additional fields and deals with them appropriately.

cindy_m 23-Jun-15 9:04am

The stored procedure is generated in code based on the schema. There's a call to schema.update with the schema name, serialized schema and serialized dataset passed to it. When the record is saved, the code checks to see if the table row changed. If it did then the old row is expired and the new data row is added. If there's no change, nothing is done. Right now, it doesn't recognize any changes to the new fields, but will save if one of the original fields is changed. I tried to find more information on deploying the server project to see if it's just missing the new schema. I did find where the assemblies are in SSMS so at least I know where to look to see if any changes happen on deployment.

Thanks for your help with this.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)