Update on Datagrid 1

[nicks60610]

I keep getting this error message:

The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not String objects

And I have been searching online but cannot find anything relevant to explain what I am missing...

This is the code:

Sub DataGrid1_Update(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)

Dim i As Integer
Dim DGI As DataGridItem
Dim SunderlandOnly As TextBox
Dim PcsInspected As TextBox
Dim PcsDefective As TextBox
Dim PcsSold As TextBox
Dim ProdComp As TextBox
Dim PcsSoldBF As TextBox
Dim ProdCompBF As TextBox


Dim connectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Quality Metrics Database.mdb"

Dim conMetrics As OleDbConnection = New OleDbConnection(connectString)

conMetrics.Open()
Dim strUpdateQuality As String
Dim dcmdUpdateQuality = New OleDb.OleDbCommand(strUpdateQuality, conMetrics)

strUpdateQuality = "UPDATE [All Plants Summary Data] SET [Sunderland Only] = ?, [Pieces Inspected] =?, [Pieces Defective] =?, [Pieces Sold] =?, [Product Complaints] =?, [Pieces Sold Big Flats] = ?, [Product Complaints Big Flats] =? where [Month] = '" & Session("Month") & "' and [Year] = '" & Session("Year") & "' and [Plant] = '" & Session("Plant") & "'"

For i = 0 To DataGrid1.Items.Count - 1
DGI = DataGrid1.Items(i)
SunderlandOnly = CType(DGI.FindControl("SunderlandOnly"), TextBox)
PcsInspected = CType(DGI.FindControl("PcsInspected"), TextBox)
PcsDefective = CType(DGI.FindControl("PcsDefective"), TextBox)
PcsSold = CType(DGI.FindControl("PcsSold"), TextBox)
ProdComp = CType(DGI.FindControl("ProdComp"), TextBox)
PcsSoldBF = CType(DGI.FindControl("PcsSoldBF"), TextBox)
ProdCompBF = CType(DGI.FindControl("ProdCompBF"), TextBox)
dcmdUpdateQuality.Parameters.Add("@Sunderland").Value = SunderlandOnly.Text
dcmdUpdateQuality.Parameters.Add("@PcsInsp").Value = PcsInspected.Text
dcmdUpdateQuality.Parameters.Add("@PcsDef").Value = PcsDefective.Text
dcmdUpdateQuality.Parameters.Add("@PcsSold").Value = PcsSold.Text
dcmdUpdateQuality.Parameters.Add("@ProdComp").Value = ProdComp.Text
dcmdUpdateQuality.Parameters.Add("@PcsSoldBF").Value = PcsSoldBF.Text
dcmdUpdateQuality.Parameters.Add("@ProdCompBF").Value = ProdCompBF.Text

Next

DataGrid1.EditItemIndex = -1

 

[dvannoy]

your not using the ? in your update statement are you?

 

[nicks60610]

Well, that is what the MSDN article I followed was showing me to do...which I only used in INSERT statements before, so I thought that was a bit odd...

 

[dvannoy]

replace the ? with your actual parameters and see if that works. I don't use access myself..but it looks like your trying to loop throught the entire grid to update all your records at once correct? are you doing this with a button outside the grid? or are you using the update feature of the grid?

 

[nicks60610]

ok, I will try that, I am trying to update the entire grid using the Update feature of the grid...I am wondering now if part of the issue is using the parameters query with Access...I have done this before with SQL but then I updated one row only...

Thanks dvannoy..

 

[nicks60610]

I get the same error when I set it up like this:

strUpdateQuality = "UPDATE [All Plants Summary Data] SET [Sunderland Only] = @Sunderland , [Pieces Inspected] = @PcsInsp, [Pieces Defective] = @PcsDef, etc, etc.

I wonder if I have to try some other function to update the grid as it seems that Access does not like the parameters...hence the error..
The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not String objects

 

[dvannoy]

if you use the update button in the grid it should be done line by line bases. you also have to specify your parameter type..e.g. varchar(10) etc.. like I said I don't use access but I would have to assume you would need to specify.

 

[nicks60610]

Hmm, so maybe a better idea would be to write a separate sub routine with an "Update" button functionality...

 

[dvannoy]

if you use the update button in the grid properties builder you can simply do a seperate sub. then in your html for the grid you can say something like OnUpdateCommand="dg_Update".
this will call your update sub..

take a look at this website

http://aspnet.4guysfromrolla.com/articles/040502-1.aspx

this has alot of good info to get you started on the grid.

 

[nicks60610]

Thanks dvannoy!