Error updating dynamic datagrid with SQL server

Hello, I have a datagrid which is populated with data from an MS SQL server database.

When I run an update query it always throws an exception - what is the most likely cause for this given that I am using the code below:

1public void DataGrid_Update(Object sender, DataGridCommandEventArgs e)2 {3 String update ="UPDATE Fruit SET Product = @.ID, Quantity = @.Q, Price = @.P, Total = @.T where Product = @.Id";45 SqlCommand command =new SqlCommand(update, conn);67command.Parameters.Add(new SqlParameter("@.ID", SqlDbType.NVarChar, 50));8 command.Parameters.Add(new SqlParameter("@.Q", SqlDbType.NVarChar, 50));9 command.Parameters.Add(new SqlParameter("@.P", SqlDbType.NVarChar, 50));10 command.Parameters.Add(new SqlParameter("@.T", SqlDbType.NVarChar, 50));11 command.Parameters["@.ID"].Value = DataGrid.DataKeys[(int)e.Item.ItemIndex];12 command.Connection.Open();1314try15 {16 command.ExecuteNonQuery();17 Message.InnerHtml ="Update complete!" + update;18 DataGrid.EditItemIndex = -1;19 }20catch (SqlException exc)21 {22 Message.InnerHtml ="Update error.";23 }2425 command.Connection.Close();2627 BindGrid();28 }

All of the row types in MS SQL server are set to nvarchar(50) - as I thought this would eliminate any inconsistencies in types.

Thanks anyone

Hi,

Can you show us the error message which can help us to solve the problem.

Thanks.

|||

Sorry if this does not make sense although I printed the SQL exception to screen.

I also changed my code a bit as I needed to change the look of the website therefore the error line number has changed to 65.

Updateerror.System.Data.SqlClient.SqlException: The parameterized query '(@.IDnvarchar(50),@.Q nvarchar(50),@.P nvarchar(50),@.T nvarchar(50' expectsthe parameter '@.Q', which was not supplied. atSystem.Data.SqlClient.SqlConnection.OnError(SqlException exception,Boolean breakConnection) atSystem.Data.SqlClient.SqlInternalConnection.OnError(SqlExceptionexception, Boolean breakConnection) atSystem.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObjectstateObj) at System.Data.SqlClient.TdsParser.Run(RunBehaviorrunBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReaderds, RunBehavior runBehavior, String resetOptionsString) atSystem.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, Booleanasync) atSystem.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehaviorcmdBehavior, RunBehavior runBehavior, Boolean returnStream, Stringmethod, DbAsyncResult result) atSystem.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResultresult, String methodName, Boolean sendToPipe) atSystem.Data.SqlClient.SqlCommand.ExecuteNonQuery() atASP.asp_aspx.MyDataGrid_Update(Object sender, DataGridCommandEventArgse) in c:\Inetpub\wwwroot\asp.aspx:line 65

Cheers

|||

Hi,

From the error message, i think the situation is that the parameters isn't supplied to the sql statement correctly. You can insert a breakpoint in "command.ExecuteNonQuery();" and check the local variable such as @.ID, @.Q, @.P and update statement. You can find out which parameters is not working properly.

Hope that helps. Thanks.

|||

How would I insert a breakpoint in "command.ExecuteNonQuery();". I guess just "break;" just after the statement - but would that give any more error output. Please suggest how I should do this.

Cheers

|||

Hi,

What I mean is one kind of debug tool. Just move your mouse to ""command.ExecuteNonQuery();" sentence and right click, a menu will be shown and find breakpoint and click on "Insert BreakPoint". And then run your applicaiton, it will interuppt while running to the breakpoint, and then, in the below window of your Visual Studio, you'll find a Local tab, under the tab, you can see all the current value of local variable, just check these to find if each parameters do have provided the input value properly.

Thanks.