🔒 Locked

[web] C# ASP.NET 1.0 - SQL Error

Started by GDKnight Dec 2, 2005 at 9:03 AM 12 replies 1.2k views

Original Post

Dec 2, 2005 at 9:03 AM

I made a database for a blog im inserting data as follows using the SQL Jet Database: INSERT INTO BlogData ("Date", "Subject", "Body") VALUES ("12/2/2005", "", "") I keep getting the error "Operation must use an updateable query." I was looking online and some people say this is due to security permissions. Is this true? If so is there a work around? Main reason I ask is because its for a school project and well they dont give us admin access.

Michalson

Dec 2, 2005 at 9:09 AM

The SQL isn't the problem. Show us the code you are using to execute the SQL, you haven't used the right type of lock or cursor (you've used some kind of client side only/read only connection, but we don't know unless you give us the code).

GDKnight

Dec 2, 2005 at 9:11 AM

I am pretty bad at AOD that doesn't surpise me.
This code is probably wrong I am not sure of what it should be though.

Code executed for database when "post" is pressed:

			string dateString;			// Create a string that stores the date.			dateString = DateTime.Today.Month + "/" +				DateTime.Today.Day + "/" + 				DateTime.Today.Year;			// Insert data into BlogData table.			string mySelectQuery =					"INSERT INTO BlogData (\"Date\", \"Subject\", \"Body\") "+					"VALUES (\""+dateString+"\", \""+					blogTitleTextbox.Text+"\", \""+					blogBodyTextbox.Text+"\")";				// OleDbConnection.				OleDbConnection myConnection = new OleDbConnection(					"Provider=Microsoft.Jet.OLEDB.4.0;"+					@"Data Source=[path removed]\blog.mdb");				// OleDbCommand.				OleDbCommand myCommand = new OleDbCommand();				myCommand.CommandText = mySelectQuery;				myCommand.Connection = myConnection;				// OleDbAdapter.				OleDbDataAdapter m_dataAdapter = new OleDbDataAdapter();				m_dataAdapter.SelectCommand = myCommand;				// DataSet				DataSet m_DS = new DataSet();				m_dataAdapter.Fill(m_DS, "BlogData");

Mattman

Dec 2, 2005 at 9:49 AM

I don't know why you're getting the error cause I've never used SQL in C#...

But you should try changing your code to be more like this for the sake of preventing SQL injection and stuff... (Note that you'll have to change the field types/lengths in the parameter adding lines.)

...string mySelectQuery =	"INSERT INTO BlogData (\"Date\", \"Subject\", \"Body\") VALUES (?, ?, ?)";// OleDbConnection.OleDbConnection myConnection = new OleDbConnection(	"Provider=Microsoft.Jet.OLEDB.4.0;"+	@"Data Source=[path removed]\blog.mdb");// OleDbCommand.OleDbCommand myCommand = new OleDbCommand(mySelectQuery, myConnection);myCommand.Parameters.Add("@p1", OleDbType.Char, 10).Value = dateString;myCommand.Parameters.Add("@p2", OleDbType.Char, 10).Value = blogTitleTextbox.Text;myCommand.Parameters.Add("@p3", OleDbType.Char, 10).Value = blogBodyTextbox.Text;...

Mattman

Dec 2, 2005 at 9:53 AM

Also, is there a reason you can't use myCommand.ExecuteNonQuery() to execute your INSERT statement? Like I said in my previous post, I never used SQL with C#, so this is just a guess based upon the MSDN documentation...

Billr17

Dec 2, 2005 at 9:57 AM

Quote:

m_dataAdapter.Fill(m_DS, "BlogData");

I don't believe you can Fill doing a SQL "INSERT" Command. You actually don't need the data adapter either. Usually the above code is for Queries ( SQL SELECT )only. Instead try this

// Where NumRows is the number of rows in the table effected by the Insert.int NumRows = myCommand.ExecuteNonQuery()

ExecuteNonQuery() is for Insert, Update, and Create, etc........

Hope this helps you out.

- Bill

[Edited by - Billr17 on December 2, 2005 10:57:23 AM]

GDKnight

Dec 2, 2005 at 10:02 AM

I changed it to prevent mysql injection but im still getting same error.

If I remove
DataSet m_DS = new DataSet();
m_dataAdapter.Fill(m_DS, "BlogData");

It doesnt give an error but it also doesnt do any execution of the script.

I tried changing it to:
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();

I am still getting the same error.

Billr17

Dec 2, 2005 at 10:11 AM

Quote:
Original post by GDKnight
I changed it to prevent mysql injection but im still getting same error.

If I remove
DataSet m_DS = new DataSet();
m_dataAdapter.Fill(m_DS, "BlogData");

It doesnt give an error but it also doesnt do any execution of the script.

I tried changing it to:
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();

I am still getting the same error.

string mySelectQuery = ............OleDbConnection myConnection = ....... int NumRows = 0;OleDbCommand myCommand = new OleDbCommand(mySelectQuery, myConnection );myConnection .Open();NumRows  = myCommand.ExecuteNonQuery();myConnection .Close();

If that still doesn't work add a try catch block around The ExecutNonQuery. Output the exeption string and post it, so we can see if soemthing else is going on. The code above should work, unless I made a typo. I just copied and pasted from a personal project written in VB .NET. So if there is any errors, it should only be in my translation to C#. ie:

Try{    // Modify data in database.    NumRows= objCommand.ExecuteNonQuery()}Catch(OleDbException ex){    // Output error message.    MsgBox(ex.ToString, MsgBoxStyle.Critical, "Fatal Error!")}

Bill

GDKnight

Dec 2, 2005 at 10:25 AM

I tried all that. The error im getting is "Operation must use an updateable query." still.

Billr17

Dec 2, 2005 at 10:29 AM

Quote:
Original post by GDKnight
I tried all that. The error im getting is "Operation must use an updateable query." still.

Just a few dumb questions... You don't still have myCommand.CommandText = mySelectQuery; in the code do you? I See your using MS Access for the database. Is there a password or anything set on the Access Database? Maybe post your whole block of current code.

GDKnight

Dec 2, 2005 at 10:30 AM

No password.

		private void postButton_Click(object sender, System.EventArgs e)		{			string dateString;			// Insert data into BlogData table.			string oleSelectQuery = "INSERT INTO BlogData (\"Date\", \"Subject\", \"Body\") VALUES (?, ?, ?)";			// OleDbConnection.			try			{				OleDbConnection oleDbConnection = new OleDbConnection(					"Provider=Microsoft.Jet.OLEDB.4.0;"+					@"Data Source=[path removed]\blog.mdb");				// OleDbCommand.				OleDbCommand oleDbCommand = new OleDbCommand(oleSelectQuery, oleDbConnection);				// Create a string that stores the date.				dateString = DateTime.Today.Month + "/" +					DateTime.Today.Day + "/" + 					DateTime.Today.Year;				// Used to prevent SQL injection.				oleDbCommand.Parameters.Add("@p1", OleDbType.Char, 10).Value = dateString;				oleDbCommand.Parameters.Add("@p2", OleDbType.Char, 10).Value = blogTitleTextbox.Text;				oleDbCommand.Parameters.Add("@p3", OleDbType.Char, 10).Value = blogBodyTextbox.Text;				oleDbCommand.Connection = oleDbConnection;				// Execute a NonQuery				oleDbConnection.Open();				oleDbCommand.ExecuteNonQuery();				oleDbConnection.Close();				Response.Redirect("blog.aspx");			}			catch(Exception ex)			{				//Display error message and query.				errorLabel.Text = ex.Message+"
Query: "+oleDbCommand.CommandText;			}		}

Billr17

Dec 2, 2005 at 10:58 AM

[source ="c#"]private void postButton_Click(object sender, System.EventArgs e)		{			string dateString;			// Insert data into BlogData table.			string oleSelectQuery = "INSERT INTO BlogData (Date, Subject, Body) VALUES (@p1, @p2, @p3)";			// OleDbConnection.			try			{				OleDbConnection oleDbConnection = new OleDbConnection(					"Provider=Microsoft.Jet.OLEDB.4.0;"+					@"Data Source=[path removed]\blog.mdb");				// OleDbCommand.				OleDbCommand oleDbCommand = new OleDbCommand(oleSelectQuery, oleDbConnection);				// Create a string that stores the date.				dateString = DateTime.Today.Month + "/" +					DateTime.Today.Day + "/" + 					DateTime.Today.Year;				// Used to prevent SQL injection.				oleDbCommand.Parameters.Add("@p1", dateString);				oleDbCommand.Parameters.Add("@p2",blogTitleTextbox.Text);				oleDbCommand.Parameters.Add("@p3", blogBodyTextbox.Text);				oleDbCommand.Connection = oleDbConnection;				// Execute a NonQuery				oleDbConnection.Open();				oleDbCommand.ExecuteNonQuery();				oleDbConnection.Close();				Response.Redirect("blog.aspx");			}			catch(Exception ex)			{				//Display error message and query.				errorLabel.Text = ex.Message+"
Query: "+oleDbCommand.CommandText;			}		}

The SQL String itself needed the parameters added. Note the addition/modification I made. I removed the ?'s and replaced them with the parameters. As of now the Insert statment should be repeatly inserting a row of question marks into the table. Also I do not believe you need the quotes around the attributes.

As for the "oleDbCommand.Parameters.Add("@p1", OleDbType.Char, 10).Value = dateString;", try the changes I made and see what happens. See if anything at all is coing into the table.

GDKnight

Dec 2, 2005 at 11:19 AM

Nope nothing is going into the database. Same error too.

[Edited by - GDKnight on December 3, 2005 1:19:49 AM]

evolutional

Dec 5, 2005 at 3:27 AM

The most common reason for this is that the Access Database file is located in a read-only folder, or that the IIS ASP.NET process doesn't have permissions to write to the file. The solutions would be to create a 'write' directory in which everything in there is writable to by the ASPNET login account; this directory should preferably be outside of the public readable area of your site too.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.