Specified cast is not valid - Update Datagrid error
-
When I run my app in the browser and attempt to update a row of data in my datagrid which also updates an Access database using an ExecuteNonQuery method I get a 'Specified cast is not valid' server error in the application. Below is the code snippet with the line bringing up the error between *****: private void MedicalHistoryListDataGrid_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { int MedicalID = (int) MedicalHistoryListDataGrid.DataKeys[(int) e.Item.ItemIndex]; *****string nino = ((TextBox)e.Item.Cells[1].Controls[0]).Text;***** string tobaccouse = ((TextBox)e.Item.Cells[2].Controls[0]).Text; string alcoholuse = ((TextBox)e.Item.Cells[3].Controls[0]).Text; string druguse = ((TextBox)e.Item.Cells[4].Controls[0]).Text; string allergies = ((TextBox)e.Item.Cells[5].Controls[0]).Text; string immunisations = ((TextBox)e.Item.Cells[6].Controls[0]).Text; string familydiseaseillness = ((TextBox)e.Item.Cells[7].Controls[0]).Text; string other = ((TextBox)e.Item.Cells[8].Controls[0]).Text; string sql = "UPDATE MEDICAL_HISTORY SET NINo=\"" + nino + "\", TobacccoUse=\"" + tobaccouse + "\", AlcoholUse=\"" + alcoholuse + "\", DrugUse=\"" + druguse + "\", Allergies=\"" + allergies + "\", Immunisations=\"" + immunisations + "\", FamilyDiseaseIllness=\"" + familydiseaseillness + "\", Other=\"" + other + "\"" + " WHERE MedicalID=" + MedicalID; ExecuteNonQuery(sql); MedicalHistoryListDataGrid.EditItemIndex = -1; ReadRecords(); } private void ExecuteNonQuery(string sql) { OleDbConnection conn = null; try { conn = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0; " + @"Data Source=" + Server.MapPath("GeneralPractice/GeneralPractice.mdb")); conn.Open(); OleDbCommand cmd = new OleDbCommand(sql, conn); cmd.ExecuteNonQuery(); } // catch (Exception e) // { // Response.Write(e.Message); // Response.End(); // } finally { if (conn != null) conn.Close(); } } Any help would be much appreciated. I've spent ages trying to figure it out but no joy. Cheers Rory
-
When I run my app in the browser and attempt to update a row of data in my datagrid which also updates an Access database using an ExecuteNonQuery method I get a 'Specified cast is not valid' server error in the application. Below is the code snippet with the line bringing up the error between *****: private void MedicalHistoryListDataGrid_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { int MedicalID = (int) MedicalHistoryListDataGrid.DataKeys[(int) e.Item.ItemIndex]; *****string nino = ((TextBox)e.Item.Cells[1].Controls[0]).Text;***** string tobaccouse = ((TextBox)e.Item.Cells[2].Controls[0]).Text; string alcoholuse = ((TextBox)e.Item.Cells[3].Controls[0]).Text; string druguse = ((TextBox)e.Item.Cells[4].Controls[0]).Text; string allergies = ((TextBox)e.Item.Cells[5].Controls[0]).Text; string immunisations = ((TextBox)e.Item.Cells[6].Controls[0]).Text; string familydiseaseillness = ((TextBox)e.Item.Cells[7].Controls[0]).Text; string other = ((TextBox)e.Item.Cells[8].Controls[0]).Text; string sql = "UPDATE MEDICAL_HISTORY SET NINo=\"" + nino + "\", TobacccoUse=\"" + tobaccouse + "\", AlcoholUse=\"" + alcoholuse + "\", DrugUse=\"" + druguse + "\", Allergies=\"" + allergies + "\", Immunisations=\"" + immunisations + "\", FamilyDiseaseIllness=\"" + familydiseaseillness + "\", Other=\"" + other + "\"" + " WHERE MedicalID=" + MedicalID; ExecuteNonQuery(sql); MedicalHistoryListDataGrid.EditItemIndex = -1; ReadRecords(); } private void ExecuteNonQuery(string sql) { OleDbConnection conn = null; try { conn = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0; " + @"Data Source=" + Server.MapPath("GeneralPractice/GeneralPractice.mdb")); conn.Open(); OleDbCommand cmd = new OleDbCommand(sql, conn); cmd.ExecuteNonQuery(); } // catch (Exception e) // { // Response.Write(e.Message); // Response.End(); // } finally { if (conn != null) conn.Close(); } } Any help would be much appreciated. I've spent ages trying to figure it out but no joy. Cheers Rory
It looks like the referenced cell's first control is not a TextBox. Is it a button, or a linkbutton or something else? One thing to try if an answer is not readily obvious would be to set an early breakpoint, and use QuickView to look at the grid item in question. That way you can verify that the types of all the controls are really what you expect them to be.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
-
It looks like the referenced cell's first control is not a TextBox. Is it a button, or a linkbutton or something else? One thing to try if an answer is not readily obvious would be to set an early breakpoint, and use QuickView to look at the grid item in question. That way you can verify that the types of all the controls are really what you expect them to be.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
You were right the first two cells were link buttons. I've changed the numbers of the cell references accordingly (they do start at 0 right?). Thanks very much you dont realise how many forums ive asked that question and got no response. Unfortunately my troubles aren't over yet. Im getting the folowing error message now: Server Error in '/GP' Application. -------------------------------------------------------------------------------- No value given for one or more required parameters. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters. Source Error (from the ExecuteNonQuery method): Line 140: OleDbCommand cmd = Line 141: new OleDbCommand(sql, conn); Line 142: cmd.ExecuteNonQuery(); Line 143: } Line 144: // catch (Exception e) Source File: c:\inetpub\wwwroot\gp\medicalhistorylist.aspx.cs Line: 142 Stack Trace: [OleDbException (0x80040e10): No value given for one or more required parameters.] System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41 System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +174 System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92 System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65 System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112 System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +66 GP.MedicalHistoryList.ExecuteNonQuery(String sql) in c:\inetpub\wwwroot\gp\medicalhistorylist.aspx.cs:142 GP.MedicalHistoryList.MedicalHistoryListDataGrid_UpdateCommand(Object source, DataGridCommandEventArgs e) in c:\inetpub\wwwroot\gp\medicalhistorylist.aspx.cs:124 System.Web.UI.WebControls.DataGrid.OnUpdateCommand(DataGridCommandEventArgs e) +109 System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source, EventArgs e) +507 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source, EventArgs e) +106 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +121 System.Web.UI.W
-
You were right the first two cells were link buttons. I've changed the numbers of the cell references accordingly (they do start at 0 right?). Thanks very much you dont realise how many forums ive asked that question and got no response. Unfortunately my troubles aren't over yet. Im getting the folowing error message now: Server Error in '/GP' Application. -------------------------------------------------------------------------------- No value given for one or more required parameters. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters. Source Error (from the ExecuteNonQuery method): Line 140: OleDbCommand cmd = Line 141: new OleDbCommand(sql, conn); Line 142: cmd.ExecuteNonQuery(); Line 143: } Line 144: // catch (Exception e) Source File: c:\inetpub\wwwroot\gp\medicalhistorylist.aspx.cs Line: 142 Stack Trace: [OleDbException (0x80040e10): No value given for one or more required parameters.] System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41 System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +174 System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92 System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65 System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112 System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +66 GP.MedicalHistoryList.ExecuteNonQuery(String sql) in c:\inetpub\wwwroot\gp\medicalhistorylist.aspx.cs:142 GP.MedicalHistoryList.MedicalHistoryListDataGrid_UpdateCommand(Object source, DataGridCommandEventArgs e) in c:\inetpub\wwwroot\gp\medicalhistorylist.aspx.cs:124 System.Web.UI.WebControls.DataGrid.OnUpdateCommand(DataGridCommandEventArgs e) +109 System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source, EventArgs e) +507 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source, EventArgs e) +106 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +121 System.Web.UI.W
My guess is that one of the values you're pulling from the grid item is null. Since you're not using a parameterized query, there's no chance that ADO.Net will correctly substitute DBNull.Value for a null variable. In general, you should always use parameterized queries for a variety of reasons (one prominent reason is they help avoid SQL injection attacks). And you should probably do some validation of your data before tossing it at the database. Hope this helps.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
-
My guess is that one of the values you're pulling from the grid item is null. Since you're not using a parameterized query, there's no chance that ADO.Net will correctly substitute DBNull.Value for a null variable. In general, you should always use parameterized queries for a variety of reasons (one prominent reason is they help avoid SQL injection attacks). And you should probably do some validation of your data before tossing it at the database. Hope this helps.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
I actually haven't got any null values, so I don't think that is the problem? You wouldn't be able to give me an example of how I can use use parameterized queries with my code as null values will have to be returned though. Also would you be able to show me how to add validation using regular expressions in my code. A quick example using my coding structure would be much appreciated. Sorry if im asking for too much. Thanks for the help. Rory
-
I actually haven't got any null values, so I don't think that is the problem? You wouldn't be able to give me an example of how I can use use parameterized queries with my code as null values will have to be returned though. Also would you be able to show me how to add validation using regular expressions in my code. A quick example using my coding structure would be much appreciated. Sorry if im asking for too much. Thanks for the help. Rory
Without seeing the database, there's not a lot more I can do to troubleshoot this. The SQL is valid, but I can't tell if it matches the database schema. One possibility is that 'Other' is a used word in Access SQL (don't have time to look that one up). Another is that there is a column name misspelled in the statement. No way for me to check that either. I would suggest running the code in debug, with a breakpoint immediately prior to executing the UPDATE statement, then using QuickView to grab the command text and attempting to run it directly in Access. If you can, then I am stumped. If there is a possibility that there is a used word in the statement, try prepending the table name (e.g. MEDICAL_HISTORY.NINo etc) to the updated column names. You can also place them in square brackets to identify them as literal values instead of key words (e.g. [MEDICAL_HISTORY].[NINo]). As for creating a parameterized query, it's easy. You're most of the way there already. Just declare your SQL statement like:
sql = "UPDATE MEDICAL_HISTORY SET NINo=?, TobaccoUse=?, AlcoholUse=?, DrugUse=?, " + "Allergies=?, Immunisations=?, FamilyDiseaseIllness=?, Other=? " + "WHERE MedicalID=?";Then create your command just as you've already done. Finally, add parameter values to the command in the order they appear in the SQL statement, so:command.Parameters.Add("[parameter name]", value);Sorry, no time to get into validation with regex here, and without knowing how you want to validate or what you want to validate, I can't do much to help anyway. There is, however, an excellent regex tutorial here[^] if you're interested. Good luck.The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
-
Without seeing the database, there's not a lot more I can do to troubleshoot this. The SQL is valid, but I can't tell if it matches the database schema. One possibility is that 'Other' is a used word in Access SQL (don't have time to look that one up). Another is that there is a column name misspelled in the statement. No way for me to check that either. I would suggest running the code in debug, with a breakpoint immediately prior to executing the UPDATE statement, then using QuickView to grab the command text and attempting to run it directly in Access. If you can, then I am stumped. If there is a possibility that there is a used word in the statement, try prepending the table name (e.g. MEDICAL_HISTORY.NINo etc) to the updated column names. You can also place them in square brackets to identify them as literal values instead of key words (e.g. [MEDICAL_HISTORY].[NINo]). As for creating a parameterized query, it's easy. You're most of the way there already. Just declare your SQL statement like:
sql = "UPDATE MEDICAL_HISTORY SET NINo=?, TobaccoUse=?, AlcoholUse=?, DrugUse=?, " + "Allergies=?, Immunisations=?, FamilyDiseaseIllness=?, Other=? " + "WHERE MedicalID=?";Then create your command just as you've already done. Finally, add parameter values to the command in the order they appear in the SQL statement, so:command.Parameters.Add("[parameter name]", value);Sorry, no time to get into validation with regex here, and without knowing how you want to validate or what you want to validate, I can't do much to help anyway. There is, however, an excellent regex tutorial here[^] if you're interested. Good luck.The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
There are three pages in my project which are getting this error but all the rest work. Confusing?? Getting a bit of difficulty understanding your terminology. Where did I create my command? and how would I specify the paramenter values using: command.Parameters.Add("[parameter name]", value); in my code. An example of how I would do this would be great. I entered this but got errors like: The type or namespace name 'command' could not be found (are you missing a using directive or an assembly reference?) By the way I am writing in c# using access as a DB. Appreciate the help! Rory
-
Without seeing the database, there's not a lot more I can do to troubleshoot this. The SQL is valid, but I can't tell if it matches the database schema. One possibility is that 'Other' is a used word in Access SQL (don't have time to look that one up). Another is that there is a column name misspelled in the statement. No way for me to check that either. I would suggest running the code in debug, with a breakpoint immediately prior to executing the UPDATE statement, then using QuickView to grab the command text and attempting to run it directly in Access. If you can, then I am stumped. If there is a possibility that there is a used word in the statement, try prepending the table name (e.g. MEDICAL_HISTORY.NINo etc) to the updated column names. You can also place them in square brackets to identify them as literal values instead of key words (e.g. [MEDICAL_HISTORY].[NINo]). As for creating a parameterized query, it's easy. You're most of the way there already. Just declare your SQL statement like:
sql = "UPDATE MEDICAL_HISTORY SET NINo=?, TobaccoUse=?, AlcoholUse=?, DrugUse=?, " + "Allergies=?, Immunisations=?, FamilyDiseaseIllness=?, Other=? " + "WHERE MedicalID=?";Then create your command just as you've already done. Finally, add parameter values to the command in the order they appear in the SQL statement, so:command.Parameters.Add("[parameter name]", value);Sorry, no time to get into validation with regex here, and without knowing how you want to validate or what you want to validate, I can't do much to help anyway. There is, however, an excellent regex tutorial here[^] if you're interested. Good luck.The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
I have implementeed the method as follows taking your advice: private void MedicalHistoryListDataGrid_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { int MedicalID = (int) MedicalHistoryListDataGrid.DataKeys[(int) e.Item.ItemIndex]; string nino = ((TextBox)e.Item.Cells[2].Controls[0]).Text; string tobaccouse = ((TextBox)e.Item.Cells[3].Controls[0]).Text; string alcoholuse = ((TextBox)e.Item.Cells[4].Controls[0]).Text; string druguse = ((TextBox)e.Item.Cells[5].Controls[0]).Text; string allergies = ((TextBox)e.Item.Cells[6].Controls[0]).Text; string immunisations = ((TextBox)e.Item.Cells[7].Controls[0]).Text; string familydiseaseillness = ((TextBox)e.Item.Cells[8].Controls[0]).Text; string notes = ((TextBox)e.Item.Cells[9].Controls[0]).Text; //string sql = // "UPDATE MEDICAL_HISTORY SET NINo=\"" + nino + // "\", TobacccoUse=\"" + tobaccouse + // "\", AlcoholUse=\"" + alcoholuse + // "\", DrugUse=\"" + druguse + // "\", Allergies=\"" + allergies + // "\", Immunisations=\"" + immunisations + // "\", FamilyDiseaseIllness=\"" + familydiseaseillness + // "\", Notes=\"" + notes + "\"" + // " WHERE MedicalID=" + MedicalID; string sql = "UPDATE MEDICAL_HISTORY SET NINo=?, TobaccoUse=?, AlcoholUse=?, DrugUse=?, " + "Allergies=?, Immunisations=?, FamilyDiseaseIllness=?, Notes=? " + "WHERE MedicalID=?"; //command.Parameters.Add("[parameter name]", value); OleDbCommand.Parameters.Add("NINo Use", OleDbType.Char, 9); OleDbCommand.Parameters.Add("Tobacco Use", OleDbType.Char, 9); OleDbCommand.Parameters.Add("AlcoholUse", OleDbType.Char, 9); OleDbCommand.Parameters.Add("Drug Use", OleDbType.Char, 9); OleDbCommand.Parameters.Add("Allergies", OleDbType.Char, 9); OleDbCommand.Parameters.Add("Immunisations", OleDbType.Char, 9); OleDbCommand.Parameters.Add("FamilyDiseaseIllness", OleDbType.Char, 9); OleDbCommand.Parameters.Add("Notes", OleDbType.Char, 9); ExecuteNonQuery(sql); MedicalHistoryListDataGrid.EditItemIndex = -1; ReadRecords(); } But an error keeps arising in the OleDbCommand.Parameters. It was command.parameters giving errors, so I changed it to this, but its still not right. Errors going like: -An object reference is required for the nonstatic field, method, or property 'System.Data.OleDb.OleDbCommand.Parameters' Any words of wisdom. Im v stuck and cant get out! Rory
-
I have implementeed the method as follows taking your advice: private void MedicalHistoryListDataGrid_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { int MedicalID = (int) MedicalHistoryListDataGrid.DataKeys[(int) e.Item.ItemIndex]; string nino = ((TextBox)e.Item.Cells[2].Controls[0]).Text; string tobaccouse = ((TextBox)e.Item.Cells[3].Controls[0]).Text; string alcoholuse = ((TextBox)e.Item.Cells[4].Controls[0]).Text; string druguse = ((TextBox)e.Item.Cells[5].Controls[0]).Text; string allergies = ((TextBox)e.Item.Cells[6].Controls[0]).Text; string immunisations = ((TextBox)e.Item.Cells[7].Controls[0]).Text; string familydiseaseillness = ((TextBox)e.Item.Cells[8].Controls[0]).Text; string notes = ((TextBox)e.Item.Cells[9].Controls[0]).Text; //string sql = // "UPDATE MEDICAL_HISTORY SET NINo=\"" + nino + // "\", TobacccoUse=\"" + tobaccouse + // "\", AlcoholUse=\"" + alcoholuse + // "\", DrugUse=\"" + druguse + // "\", Allergies=\"" + allergies + // "\", Immunisations=\"" + immunisations + // "\", FamilyDiseaseIllness=\"" + familydiseaseillness + // "\", Notes=\"" + notes + "\"" + // " WHERE MedicalID=" + MedicalID; string sql = "UPDATE MEDICAL_HISTORY SET NINo=?, TobaccoUse=?, AlcoholUse=?, DrugUse=?, " + "Allergies=?, Immunisations=?, FamilyDiseaseIllness=?, Notes=? " + "WHERE MedicalID=?"; //command.Parameters.Add("[parameter name]", value); OleDbCommand.Parameters.Add("NINo Use", OleDbType.Char, 9); OleDbCommand.Parameters.Add("Tobacco Use", OleDbType.Char, 9); OleDbCommand.Parameters.Add("AlcoholUse", OleDbType.Char, 9); OleDbCommand.Parameters.Add("Drug Use", OleDbType.Char, 9); OleDbCommand.Parameters.Add("Allergies", OleDbType.Char, 9); OleDbCommand.Parameters.Add("Immunisations", OleDbType.Char, 9); OleDbCommand.Parameters.Add("FamilyDiseaseIllness", OleDbType.Char, 9); OleDbCommand.Parameters.Add("Notes", OleDbType.Char, 9); ExecuteNonQuery(sql); MedicalHistoryListDataGrid.EditItemIndex = -1; ReadRecords(); } But an error keeps arising in the OleDbCommand.Parameters. It was command.parameters giving errors, so I changed it to this, but its still not right. Errors going like: -An object reference is required for the nonstatic field, method, or property 'System.Data.OleDb.OleDbCommand.Parameters' Any words of wisdom. Im v stuck and cant get out! Rory
You add
OleDbParametersto an instance of anOleDbCommand, not the type itself. The syntax you are using is referring to theOleDbCommandtype, not an instance. You should create anOleDbCommandinstance, such as:OleDbCommand cmd = new OleDbCommand(sql);Then add parameters to theParameterscollection of 'cmd':cmd.Parameters.Add("whatever", OleDbType, size);You might consider revising the way you're adding the parameters, as well, to just let .Net deal with the sizes and types:cmd.Parameters.Add("parametername", value);There's nothing wrong technically with the way you're doing it, but it's just shorter to do the 'name, value' method. You will also need to set theConnectionof the command in yourExecuteNonQuerymethod. I would probably refactor theExecuteNonQuerymethod to accept a SQL command string and array of parameters, or just to accept anOleDbCommandobject with the parameters attached. It will be invaluable to you to read the documentation for ADO.Net provided with Visual Studio, or on any of the thousands of web sites with information on the topic. The object hierarchy is not complicated, but trying to use it without knowing what's available to you will be frustrating. Hope this helps.The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
-
You add
OleDbParametersto an instance of anOleDbCommand, not the type itself. The syntax you are using is referring to theOleDbCommandtype, not an instance. You should create anOleDbCommandinstance, such as:OleDbCommand cmd = new OleDbCommand(sql);Then add parameters to theParameterscollection of 'cmd':cmd.Parameters.Add("whatever", OleDbType, size);You might consider revising the way you're adding the parameters, as well, to just let .Net deal with the sizes and types:cmd.Parameters.Add("parametername", value);There's nothing wrong technically with the way you're doing it, but it's just shorter to do the 'name, value' method. You will also need to set theConnectionof the command in yourExecuteNonQuerymethod. I would probably refactor theExecuteNonQuerymethod to accept a SQL command string and array of parameters, or just to accept anOleDbCommandobject with the parameters attached. It will be invaluable to you to read the documentation for ADO.Net provided with Visual Studio, or on any of the thousands of web sites with information on the topic. The object hierarchy is not complicated, but trying to use it without knowing what's available to you will be frustrating. Hope this helps.The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
How would I go about setting the connection of the command in the execute nonquery method, shown in my first thread? I did the rest of what you said, and the errors are still appearing. Wish I could get this working, severely running out of time! Cheers for helping. Rory
-
How would I go about setting the connection of the command in the execute nonquery method, shown in my first thread? I did the rest of what you said, and the errors are still appearing. Wish I could get this working, severely running out of time! Cheers for helping. Rory
The OleDbCommand class has a read/write property called Connection which should be readily apparent through Intellisense. Once your connection is created, set the property. I'm sorry if you're running out of time, but you really should use the online help. I don't want to write this piece of code for you any more than I already have. Doing so doesn't allow you any chance to learn anything on your own, and decreases your odds of improving your skills, in my opinion. It may not seem so right now, but this is a very routine type of function, and if you work with databases using the .Net Framework on a regular basis, you are going to need to understand how ADO.Net works better than you currently do. Note: If you implemented the changes I indicated above, it is very unlikely that you are receiving the same error(s) you were previously receiving. If you are still getting the errors you indicated last, re-read my last post, or just check out the online help. Best of luck.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
-
You add
OleDbParametersto an instance of anOleDbCommand, not the type itself. The syntax you are using is referring to theOleDbCommandtype, not an instance. You should create anOleDbCommandinstance, such as:OleDbCommand cmd = new OleDbCommand(sql);Then add parameters to theParameterscollection of 'cmd':cmd.Parameters.Add("whatever", OleDbType, size);You might consider revising the way you're adding the parameters, as well, to just let .Net deal with the sizes and types:cmd.Parameters.Add("parametername", value);There's nothing wrong technically with the way you're doing it, but it's just shorter to do the 'name, value' method. You will also need to set theConnectionof the command in yourExecuteNonQuerymethod. I would probably refactor theExecuteNonQuerymethod to accept a SQL command string and array of parameters, or just to accept anOleDbCommandobject with the parameters attached. It will be invaluable to you to read the documentation for ADO.Net provided with Visual Studio, or on any of the thousands of web sites with information on the topic. The object hierarchy is not complicated, but trying to use it without knowing what's available to you will be frustrating. Hope this helps.The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
I have implemented the code in a slightly different way shown below: private void DiagnosisListDataGrid_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { int DiagnosisNo = (int) DiagnosisListDataGrid.DataKeys[(int) e.Item.ItemIndex]; string nino = ((TextBox)e.Item.Cells[3].Controls[0]).Text; string datetime = ((TextBox)e.Item.Cells[4].Controls[0]).Text; string details = ((TextBox)e.Item.Cells[5].Controls[0]).Text; string currentstatus = ((TextBox)e.Item.Cells[6].Controls[0]).Text; string sql = "UPDATE DIAGNOSIS SET NINo=@nino, [DateTime]=@datetime, Details=@details, CurrentStatus= " + " @currentstatus WHERE DiagnosisNo= @DiagnosisNo"; OleDbCommand cmd = new OleDbCommand(sql); cmd.Parameters.Add("@nino", nino); cmd.Parameters.Add("@datetime", datetime); cmd.Parameters.Add("@details", details); cmd.Parameters.Add("@currentstatus", currentstatus); ExecuteNonQuery(sql); DiagnosisListDataGrid.EditItemIndex = -1; ReadRecords(); } This has got rid of all the build errors but now when I attempt to use the update function in the browser, the following error is returned: Server Error in '/GP' Application. -------------------------------------------------------------------------------- No value given for one or more required parameters. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters. Source Error: Line 148: OleDbCommand cmd = Line 149: new OleDbCommand(sql, conn); Line 150: cmd.ExecuteNonQuery(); Line 151: } Line 152: // catch (Exception e) Source File: c:\inetpub\wwwroot\gp\diagnosislist.aspx.cs Line: 150 Stack Trace: [OleDbException (0x80040e10): No value given for one or more required parameters.] System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41 System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +174 System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92 System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65 System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112 System