unsuccessful datagrid update

[sladd]

I am trying to allow a user to update one record at a time from the datagrid to the database using the built-in update function. No errors are reported (or perhaps I don't know where to look in the logs) but the database is not updated. I have outputted the update values and row key field during the operation and they are correct, yet the update is unsuccessful. Can anyone help?

sub update_orders_dg(sender As Object, e As datagridcommandEventArgs)
Dim OrdersTextbox as integer = ctype(e.item.cells(3).controls(0),textbox).text
Dim Salestextbox as integer = ctype(e.item.cells(4).controls(0),textbox).text
Dim keyid as Integer = orders_dg.datakeys(e.item.itemindex)

label1.text = updateorders(keyid, orderstextbox, salestextbox)
orders_dg.edititemindex=-1
orders_dg.datasource = orders_dg_query(storeddl.Items(storeddl.SelectedIndex).Text,segmentddl.Items(segmentddl.SelectedIndex).Text,monthddl.Items(monthddl.SelectedIndex).Text)
orders_dg.databind()
end sub

sub cancel_orders_dg(sender As Object, e As datagridcommandEventArgs)
orders_dg.edititemindex= -1
orders_dg.datasource = orders_dg_query(storeddl.Items(storeddl.SelectedIndex).Text,segmentddl.Items(segmentddl.SelectedIndex).Text,monthddl.Items(monthddl.SelectedIndex).Text)
orders_dg.databind()
end sub


'update connection

Function updateorders(ByVal key As integer, ByVal orders As integer, ByVal proj_Sales As integer) As Integer
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=d:\2004 Orders."& _
"mdb"
Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

Dim queryString As String = "UPDATE [Orders_by_Month] SET [Orders]=@Orders, [Proj_Sales]=@Proj_Sales WHERE ([O"& _
"rders_by_Month].[key] = @key)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_key As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_key.ParameterName = "@key"
dbParam_key.Value = key
dbParam_key.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_key)
Dim dbParam_orders As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_orders.ParameterName = "@Orders"
dbParam_orders.Value = orders
dbParam_orders.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_orders)
Dim dbParam_proj_Sales As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_proj_Sales.ParameterName = "@Proj_Sales"
dbParam_proj_Sales.Value = proj_Sales
dbParam_proj_Sales.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_proj_Sales)



Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Catch ex As Exception

Finally
dbConnection.Close
End Try

Return rowsAffected
End Function

 

[sladd]

I've now updated successfully - but with the wrong data. If I test my three variables prior to calling the update function, they have the correct data. However, by the time they get to the function they are somehow corrupted. Any help is greatly appreciated.

Steve

 

[Rilez]

are you saying that the key field is correct but the values are wrong? are they very wrong? try ...

Dim OrdersTextbox as integer = cint(ctype(e.item.cells(3).controls(0),textbox).text)
Dim Salestextbox as integer = cint(ctype(e.item.cells(4).controls(0),textbox).text)

Its difficult to know whats going on without further information. is it the same wrong value every time? You should also really be putting your stored procedure into access and calling the procedure by its name but thats by the by

 

[sladd]

Rilez,

Thank you for the reply. I can't figure out whats going on. When I display the value of the 3 parameters in either the sub or the function, they are all correct. It now reports that one record is updated but I don't see any update. If I remove the key contraint - resulting in every record in the database being updated to the user input in one row, an update occurs. THe update is incorrect however. This is tricky so I will try to explain. It behaves as follows.

1) The user clicks edit, enters values and then clicks update. The orders field in every record is updated to the record key of the record that was updated
2) the user initiates a second update in the same manner. This time the sales field will be populated with the record key

I'm as confused as I've ever been

I'm interested in what you said regarding calling a procedure that is stored in access from my asp page. Are you saying that its possible to call a function that resides in a module in my .mdb from the web page?? If so, how? This would solve all my problems in life

 

[sladd]

I determined my problem. My SQL string called for parameters in the order of 1) orders 2)sales 3)key . The parameters were added with the System.Data.OleDb.OleDbParameter in the order of 1)key 2)orders 3)sales. Despite having named the parameters in the system.data.oledb.oledbparameter statement, the values were added in the incorrect order. I adjusted the order of the paramter declarations and this solved the problem.

Still very interested in a method of calling an access module from asp.

Thanks for your help.
Steve

 

[Rilez]

No sorry the query you are passing to Access could be put into Access as a query and then you could call it from its name I think. I'm probabily wrong. It's been a long day . When it comes to functions - can't you call them from queries anyway?