I'm trying to make an .aspx page which would allow me to change values in the database. It has a datagrid which shows everything from one database table, and allows me to edit the data row by row. Everything goes smoothly as anything, but when I try i update the database with SQL query, i get error. Here is the error:

Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters.

Source Error: 


Line 115:           
Line 116:           //Kun parametrit on luotu, suoritetaan kysely
Line 117:           objCommand.ExecuteNonQuery();
Line 118:           //Suljetaan yhteys
Line 119:           MyConn.Close();


Source File: c:\inetpub\wwwroot\phoenix\search.aspx.cs    Line: 117 

I just can't see what i'm missing, because I have triple checked my code and I don't see any errors in it. I bet this is very simple problem what i just can't see. I have also checked with debugger that the parameters DO get value in the code (for example: "parameterprodID.Value = IDcolumn" gives value of 2 as it is second row), so I'm very clueless what is wrong with this. Here is the actual update code:

        string strSQL = "UPDATE [Categories] SET [Category Name] = @Name WHERE [Category ID] = @ProductID"; 

        OleDbConnection MyConn = new OleDbConnection(ConfigurationSettings.AppSettings["strConn"]);
        MyConn.Open();  

        OleDbCommand objCommand = new OleDbCommand(strSQL, MyConn); 
        objCommand.CommandType = CommandType.Text;


        OleDbParameter parameterTarget = new OleDbParameter("@Name", OleDbType.VarWChar);
        parameterTarget.Value = ((TextBox) e.Item.Cells[2].Controls[0]).Text;
        objCommand.Parameters.Add(parameterTarget);

        OleDbParameter parameterprodID = new OleDbParameter("@ProductID", OleDbType.Integer);
        parameterprodID.Value = IDcolumn;
        objCommand.Parameters.Add(parameterprodID);



        objCommand.ExecuteNonQuery();

        MyConn.Close();

Recommended Answers

All 8 Replies

[Category ID] = @ProductID

seems like u r updating Category ID and providing product id..

is it the case...?

[Category ID] = @ProductID

seems like u r updating Category ID and providing product id..

is it the case...?

Thank you for swift reply. Unfortunately this is not the case. The column name in my database is really Category ID, I just used ProductID as parameter name as I was too lazy to change it :) I have triple checked the names on the SQL-query string and on my database and they all match up.

Have you checked the connection string?

Have you checked the connection string?

It's fine, I have used it before in previous programs. I noticed that one fieldname was wrong, i corrected it and now it says: "not an updateable query".
Still not working :/

The error means that it cannot update the database with the data it's been given. So that suggests that you have a problem with the contents/design of your database.

Is there a Foreign Key constraint that becomes invalid if you try to use this data?

did u fire the same query manually in your SQL server..?

Okay guys, I finally got it. After i fixed the wrong column name, i got that "Operation must use an updateable query" error. So this morning as i came to work i started to work around it, and finally i found a solution! It was very simple , and it wasnt even related to the code after all! I had to set permissions in Explorer for the access database so that Internet Guest Account had write permissions to the database and to the folder where the database was. I hope this will help someone else with this problem :)
I used this page to get more info about it: http://support.microsoft.com/kb/175168

cool :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.