System.Data.OleDb.OleDbException: No value given for one or more requi 2

[chilly442]

I am getting the error:
System.Data.OleDb.OleDbException: No value given for one or more required parameters.
Here is my code.
<asp:GridView
ID="GridView1"
runat="server"
Font-Size="X-Small"
AutoGenerateColumns="False"
OnRowEditing="GridView1_RowEditing"
OnRowUpdating="GridView1_RowUpdating"
OnRowCancelingEdit="GridView1_RowCancelingEdit"
OnRowDataBound="GridView1_RowDataBound">
<Columns>
<asp:CommandField ShowEditButton="True" UpdateText="UPDATE"/>
<asp:BoundField DataField="Accepted" HeaderText="Accepted" SortExpression="Accepted" />
<asp:BoundField DataField="Unit" HeaderText="Unit" SortExpression="Unit" />
<asp:BoundField DataField="OutageType" HeaderText="OutageType" SortExpression="OutageType" />
<asp:BoundField DataField="StartTime" HeaderText="StartTime" SortExpression="StartTime" />
<asp:BoundField DataField="EndTime" HeaderText="EndTime" SortExpression="EndTime" />
<asp:BoundField DataField="Capacity" HeaderText="Capacity" SortExpression="Capacity" />
<asp:BoundField DataField="NetCapacity" HeaderText="NetCapacity" SortExpression="NetCapacity" />
<asp:BoundField DataField="DurationHours" HeaderText="DurationHours" SortExpression="DurationHours" HtmlEncode="false" dataformatstring="{0:n}"/>
<asp:BoundField DataField="MWhLost1" HeaderText="MWhLost1" SortExpression="MWhLost1" />
<asp:BoundField DataField="CauseCode1" HeaderText="CauseCode1" SortExpression="CauseCode1" />
<asp:BoundField DataField="Cause1Desc" HeaderText="Cause1Desc" SortExpression="Cause1Desc" />
<asp:BoundField DataField="CauseCode2" HeaderText="CauseCode2" SortExpression="CauseCode2" />
<asp:BoundField DataField="Cause2Desc" HeaderText="Cause2Desc" SortExpression="Cause2Desc" />
<asp:BoundField DataField="MWhLost2" HeaderText="MWhLost2" SortExpression="MWhLost2" />
<asp:BoundField DataField="User1" HeaderText="User1" SortExpression="User1" />
<asp:BoundField DataField="Comments" HeaderText="Comments" SortExpression="Comments" />
</Columns>
</asp:GridView>
Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
Dim cmd As New OleDb.OleDbCommand
Dim sql As String
Dim txtAccepted As String = e.RowIndex.ToString
Dim txtUnit As String = e.RowIndex.ToString
Dim txtOutageType As String = e.RowIndex.ToString
Dim txtStartTime As String = e.RowIndex.ToString
Dim txtEndTime As String = e.RowIndex.ToString
Dim txtCapacity As String = e.RowIndex.ToString
Dim txtNetCapacity As String = e.RowIndex.ToString
Dim txtDurationHours As String = e.RowIndex.ToString
Dim txtMWhLost1 As String = e.RowIndex.ToString
Dim txtCauseCode1 As String = e.RowIndex.ToString
Dim txtCause1Desc As String = e.RowIndex.ToString
Dim txtCauseCode2 As String = e.RowIndex.ToString
Dim txtCause2Desc As String = e.RowIndex.ToString
Dim txtMWhLost2 As String = e.RowIndex.ToString
Dim txtUser1 As String = e.RowIndex.ToString
Dim txtComments As String = e.RowIndex.ToString
cmd.Parameters.Add(New OleDb.OleDbParameter("@Accepted", txtAccepted.ToString))
cmd.Parameters.Add(New OleDb.OleDbParameter("@Unit", txtUnit.ToString))
cmd.Parameters.Add(New OleDb.OleDbParameter("@OutageType", txtOutageType.ToString))
cmd.Parameters.Add(New OleDb.OleDbParameter("@StartTime", txtStartTime.ToString))
cmd.Parameters.Add(New OleDb.OleDbParameter("@EndTime", txtEndTime.ToString))
cmd.Parameters.Add(New OleDb.OleDbParameter("@Capacity", txtCapacity.ToString))
cmd.Parameters.Add(New OleDb.OleDbParameter("@NetCapacity", txtNetCapacity.ToString))
cmd.Parameters.Add(New OleDb.OleDbParameter("@DurationHours", txtDurationHours.ToString))
cmd.Parameters.Add(New OleDb.OleDbParameter("@MWhLost1", txtMWhLost1.ToString))
cmd.Parameters.Add(New OleDb.OleDbParameter("@CauseCode1", txtCauseCode1.ToString))
cmd.Parameters.Add(New OleDb.OleDbParameter("@Cause1Desc", txtCause1Desc.ToString))
cmd.Parameters.Add(New OleDb.OleDbParameter("@CauseCode2", txtCauseCode2.ToString))
cmd.Parameters.Add(New OleDb.OleDbParameter("@Cause2Desc", txtCause2Desc.ToString))
cmd.Parameters.Add(New OleDb.OleDbParameter("@MWhLost2", txtMWhLost2.ToString))
cmd.Parameters.Add(New OleDb.OleDbParameter("@User1", txtUser1.ToString))
cmd.Parameters.Add(New OleDb.OleDbParameter("@Comments", txtComments.ToString))
sql = "UPDATE Events SET [Accepted]=@Accepted,[Unit]=@Unit,[StartTime]=@StartTime,[EndTime]=@EndTime,[OutageType]=@OutageType,[Capacity]=@Capacity,[NetCapacity]=@NetCapacity,[DurationHours]=@DurationHours,[MWhLost1]=@MWhLost1,[CauseCode1]=@CauseCode1,[Cause1Desc]=@Cause1Desc,[CauseCode2]=@CauseCode2,[Cause2Desc]=@Cause2Desc,[MWhLost2]=@MWhLost2,[User1]=@User1,[Comments]=@Comments WHERE [Unit]=@Unit AND [StartTime]=@StartTime AND [EndTime]=@EndTime"
Con.ConnectionString = connString
Con.Open()
cmd = New OleDb.OleDbCommand(sql, Con)
cmd.ExecuteNonQuery()
Con.Close()
GridView1.EditIndex = -1
BindData()
End Sub

Searches online say that this error comes up when something is misspelled or called and does not exist. But I have looked and that is not the problem.
Any help is worth a star!!!
Thanks chilly442

 

[jbenson001]

I would suggest using a stored procedure instead of inline code. You will be able to better trap the error, trace it and it will be easier to maintain your code.

 

[jmeckley]

there are lots of problems with this code. but the current problem is that you define the cmd object, set the parameters and then set the cmd object to a new object. thus removing all the parameters. use this code instead

using(OleDbConnection cnn = new OleDbConnection("...")
using (OleCommand cmd = cnn.CreateCommand())
{
   cmd.CommandText = sql;
   cmd.Parameters.AddWithValue("@...", value);
   ...
   cmd.ExecuteNonQuery();
}
GridView1.EditIndex = -1
DataBind();

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[chilly442]

Now with this code I am getting the error:
Command text was not set for the command object.
on this line:
cmd.ExecuteNonQuery()

Do you have a quick example of how this should look. I have been trying to get this to work for about a week now with no luck.

Is what I am trying to do even possible? The user needs to be able to edit a row in the grid, and have any changes show up in the DB.

Dim cmd As New OleDb.OleDbCommand
Dim sql As String
Dim cnn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\WebSite1\App_Data\ODRT.mdb")
Dim txtAccepted As String = e.RowIndex.ToString
Dim txtUnit As String = e.RowIndex.ToString
(and so on)

cmd.Parameters.AddWithValue("@Accepted", txtAccepted.ToString)
cmd.Parameters.AddWithValue("@Unit", txtUnit.ToString)
(and so on)

sql = "UPDATE Events SET [Accepted]=@Accepted(...)"
cnn.Open()
cmd.CommandText = sql
cmd = cnn.CreateCommand()
cmd.ExecuteNonQuery()
cnn.Close()
GridView1.EditIndex = -1
DataBind()

Thanks,
Chilly442

 

[jmeckley]

again, you are overwriting the command after you set the text and parameters. follow the model I provided above you will be fine.
1. use connection
2. create command from connection
3. set command text
4. set command parameters
5. open connection
6. execute command
7. close
8. dispose

by using the "using" keyword steps 7 and 8 are done automatically by the closing bracket (end using in vb)

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[chilly442]

Here is what I have now. When I change a value in the grid, and click the UPDATE button, the page reloads, but the DB is not updated.
Thank you for taking the time to look at my code.
Any suggestions?

Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
Dim sql As String
sql = "UPDATE Events SET [Accepted]=@Accepted..."
Using cnn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\WebSite1\App_Data\ODRT.mdb")
Using cmd As New OleDbCommand(sql, cnn)
cmd.CommandText = sql
Dim txtAccepted As String = e.RowIndex
(and so on)

cmd.Parameters.AddWithValue("@Accepted", txtAccepted)
(and so on)

cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()
GridView1.EditIndex = -1
DataBind()
End Using
End Using
End Sub

Thanks,
Chilly442

 

[jbenson001]

TEst your update statement in Access and see if it is working the way you want.

 

[chilly442]

I just tested the UPDATE statement. I get the results that I am looking for in Access.
I need the Accepted value to be an "x" showing that the user accepts the values for that row in the grid.
So, I set up a msgbox to test the value and the value is "0" not "x".
What am I missing?

Thanks jbenson001 and jmeckley for all the help.
I need all that I can get!
Chilly442

 

[jmeckley]

So, I set up a msgbox to test the value and the value is "0" not "x".

message boxes don't work in asp.net because of the nature of client server. this works on desktop apps because the code runs locally on the box.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[chilly442]

So how do I test to see if there is even a value for Accepted?

Thanks!

 

[jmeckley]

there are a number of ways
1. step through code with breakpoints
2. use unit testing frameworks to test object behavior
3. implement a logging framework to log key information for debugging.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[chilly442]

Is there anything else that you would suggest I change in my code to get the resluts that I am looking for?

 

[chilly442]

I have done everything that I can find online to fix my problem. Nothing has worked.
The project that i am working on is done accept for this one thing.
This code does not update the DB.
This is a web form if that makes any difference in the help given.
The front end code is still the same at it was when I first posted my problem.
Please someone help if possible.

Here is the code that I am using.

Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
Dim sql As String
sql = "UPDATE Events SET [Accepted]=@Accepted,[Unit]=@Unit,[StartTime]=@StartTime,[EndTime]=@EndTime,[OutageType]=@OutageType,[Capacity]=@Capacity,[NetCapacity]=@NetCapacity,[DurationHours]=@DurationHours,[MWhLost1]=@MWhLost1,[CauseCode1]=@CauseCode1,[Cause1Desc]=@Cause1Desc,[MWhLost2]=@MWhLost2,[CauseCode2]=@CauseCode2,[Cause2Desc]=@Cause2Desc,[User1]=@User1,[Comments]=@Comments WHERE [Unit]=@Unit AND [StartTime]=@StartTime AND [EndTime]=@EndTime"
Using cnn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\WebSite1\App_Data\ODRT.mdb")
Using cmd As New OleDbCommand(sql, cnn)
cmd.CommandText = sql
Dim txtAccepted As String = GridView1.Rows(0).Cells(1).ToString
Dim txtUnit As String = GridView1.Rows(0).Cells(2).ToString
Dim txtOutageType As String = GridView1.Rows(0).Cells(3).ToString
Dim txtStartTime As String = GridView1.Rows(0).Cells(4).Text
Dim txtEndTime As String = GridView1.Rows(0).Cells(5).Text
Dim txtCapacity As String = GridView1.Rows(0).Cells(6).Text
Dim txtNetCapacity As String = GridView1.Rows(0).Cells(7).Text
Dim txtDurationHours As String = GridView1.Rows(0).Cells(8).Text
Dim txtMWhLost1 As String = GridView1.Rows(0).Cells(9).Text
Dim txtCauseCode1 As String = GridView1.Rows(0).Cells(10).Text
Dim txtCause1Desc As String = GridView1.Rows(0).Cells(11).ToString
Dim txtCauseCode2 As String = GridView1.Rows(0).Cells(12).Text
Dim txtCause2Desc As String = GridView1.Rows(0).Cells(13).ToString
Dim txtMWhLost2 As String = GridView1.Rows(0).Cells(14).Text
Dim txtUser1 As String = GridView1.Rows(0).Cells(15).ToString
Dim txtComments As String = GridView1.Rows(0).Cells(16).ToString
cmd.Parameters.AddWithValue("@Accepted", txtAccepted)
cmd.Parameters.AddWithValue("@Unit", txtUnit)
cmd.Parameters.AddWithValue("@OutageType", txtOutageType)
cmd.Parameters.AddWithValue("@StartTime", txtStartTime)
cmd.Parameters.AddWithValue("@EndTime", txtEndTime)
cmd.Parameters.AddWithValue("@Capacity", txtCapacity)
cmd.Parameters.AddWithValue("@NetCapacity", txtNetCapacity)
cmd.Parameters.AddWithValue("@DurationHours", txtDurationHours)
cmd.Parameters.AddWithValue("@MWhLost1", txtMWhLost1)
cmd.Parameters.AddWithValue("@CauseCode1", txtCauseCode1)
cmd.Parameters.AddWithValue("@Cause1Desc", txtCause1Desc)
cmd.Parameters.AddWithValue("@MWhLost2", txtMWhLost2)
cmd.Parameters.AddWithValue("@CauseCode2", txtCauseCode2)
cmd.Parameters.AddWithValue("@Cause2Desc", txtCause2Desc)
cmd.Parameters.AddWithValue("@User1", txtUser1)
cmd.Parameters.AddWithValue("@Comments", txtComments)
cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()
GridView1.EditIndex = -1
DataBind()
End Using
End Using
End Sub

As always, help gets you stars, whether it solves my problem or not.
Thanks,
Chilly442

 

[jmeckley]

start fresh. delete the page, the entire file, from the web project.
now start simple. between each step run the page and make sure it works with the information you have provided.

1. add a gridview
2. to start bind the gridview to datatable with fake data, no editing, sorting, paging etc, just present the data as readonly in a table
3. now add an edit button to the grid
4. assign an edit event handler to the action. at this point the handler should only throw a not implemented exception

at this point when you click edit the page should fail. this is good, it means the event was wired properly.

5. within the handler parse out all the values needed. it will look something like this

Textbox x = e.Row.FindControl("id of control") as Textbox;
if(x != null) assign x.Text a variable;

6. set a break point at the beginning of the handler and debug the page. check each value to make sure you are pulling the right values.
7. add the database conneciton/command and execute.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[chilly442]

I got it.
I deleted the page and started from nothing.
The UPDATE statement had the [] around the column names. This is the way that I kept seeing it done online. Once I removed them and put the WHERE clause in (), then it started to work. I think that I just kept compounding the error the more code I played with.

Simple is always better!

Thanks to jmeckley for all the help.

Chilly442

 

[jmeckley]

congradulations chilly!

I think that I just kept compounding the error the more code I played with.

This is true for all developers from novices to veteran. for more information on simplifying code research the SOLID design principles. I find Chad Myer's post to me the best introduction.

Jason Meckley
Programmer
Specialty Bakers, Inc.