linq to sql Insert not working on deployed server(IIS)

Question

title speaks for itself,

db.ExecuteCommand("INSERT INTO tCSVFile(fileName, fileContent, mimetype, timeCreated) VALUES({0}, {1}, {2}, {3})", filename, EncodeTo64(CreateCSVFile(header, rows)), "text/csv", DateTime.Now );

this works fine from the virtual server but on iis inserting causes nothing to happen.

Also tried this..

 tCSVFile c = new tCSVFile
            {
                fileContent = EncodeTo64(CreateCSVFile(header, rows)),
                fileName = filename,
                mimetype = "text/csv",
                timeCreated = DateTime.Now
            };


 db.tCSVFiles.InsertOnSubmit(c);
 db.SubmitChanges();

again works on virtual server but on iis no luck. any help would be greatly appreciated as i have looked on the web and havent found anything of use. My selects work fine and can select without a problem.

Connection string is

<add name="db_ac_motors_testConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\db_ac_motors_test.mdf;Integrated Security=True;User Instance=True"
      providerName="System.Data.SqlClient" />

The insert is a call from a MVC Partial view and is done through ajax.beginform()

Answer 1

Are you certain the database is attaching correctly. Many ISPs have it set up so you cannot attach a database using the connection string methdology you have employed. That is the first place you should look. I would think you would get an error, but it might be swallowed up somewhere in the stack.

If you need to move SQL to a SQL instance (not SQL Express necessarily) rather than attach, this might help: http://gregorybeamer.spaces.live.com/blog/cns!B036196EAF9B34A8!630.entry?sa=214268831

Answer 2

What does the function/method CreateCSVFile() do? If it writes files to the filesystem then it looks like you have a permissions problem. i.e. The anonymous user for the site may not have write permissions.

Just a thought.

Kev

Answer 3

After finally spending couple hours looking for some sort of exception I saved the stack trace in an event log and found this error

System.Data.SqlClient.SqlException: Failed to update database "(database directory)" because the database is read-only.

So with some fishing around the internet it had to do with a user permission restriction.

here is a link if anyone runs into the same problem. I guess the debugging virtual server reads folder permissions differently hence the restriction only on deployment(IIS is a little fussy when it comes to permissions). And I was surprised that no exception was being returned...I had to do so much to access the stack trace, I don't want to even begin... Anyways the link is this just follow the steps and it should point you in the right direction.

http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/2e776fb4-6df9-4a11-96f1-948b8a2f839a/#page:2