Tracey,
Are you using a sql string to pass command and values, or a stored
procedure?
Validate all your fields and double-check how you are attaching your
parameters.
There are different ways of achieving the same thing, but I use the approach
included below with stored procedures:-
<begin sample>
Private Sub ExampleSaveButtonRoutine...
Dim booCloseConn As Boolean = False
'Note: Connection string taken from the web.config file...
'sconOHA and objCommand are declared as Private page globals
If Me.sconOHA.State = ConnectionState.Closed Then
Me.sconOHA.ConnectionString =
ConfigurationSettings.AppSettings("DBConn")
Me.objCommand.Connection = Me.sconOHA
booCloseConn = True
End If
Me.objCommand.Parameters.Clear()
Me.objCommand.CommandType = CommandType.StoredProcedure
'Example comes from an edit form that can update as well as insert, so I
use the
'CommandArgument property to determine which stored procedure to use.
If Me.cmdEditSave.CommandArgument = "Insert" Then
Me.objCommand.CommandText = "usp_FC_FCPLog_INSERT"
'Insert the owner ID for the record
.Add(New SqlParameter("@PID", SqlDbType.Int)).Value =
CInt(Me.lblPID.Text)
Else
Me.objCommand.CommandText = "usp_FC_FCPLog_UPDATE"
'Attach the record ID
.Add(New SqlParameter("@FCPLID", SqlDbType.Int)).Value =
CInt(Me.lblFCPLID.Text)
End If
'Next, I validate the form and add the parameters...
With Me.objCommand.Parameters
'Do some validation (if required for your app)
Try
Me.lblErr.Visible = False
If (Me.txtMyValue1.Text <> "") And (Me.txtMyValue2.Text = "") Then
Throw New Exception
End If
Catch ex As Exception
Me.lblErr.Visible = True
Me.lblErr.Text = "## ~~ Error message to user goes here ~~ ##"
Exit Sub
End Try
'Validation criteria met, so add parameter values...
.Add(New SqlParameter("@MyFirstParam", SqlDbType.Int)).Value =
CType(Me.txtMyIntTextBox.Text, Integer)
If Me.txtFCDateOHPTRefApptOffered.Text <> "" Then
.Add(New SqlParameter("@MySecondParam", SqlDbType.DateTime)).Value =
Me.txtMyDateBox.xDate
End If
End With
'Having all the parameters validated and added, I connect and insert (or
update, etc...)
Try
Me.lblErr.Visible = False
If Me.sconOHA.State = ConnectionState.Closed Then
Me.sconOHA.Open()
End If
'#######################################
'And here I use the ExecuteNonQuery method to execute the sproc...
Me.objCommand.ExecuteNonQuery()
'#######################################
'Any other processing here, e.g. Rebind a datagrid to update display
(seperate routine)
Me.Bind_dgFC()
Catch ex As Exception
'Put SQL error or whatever on page for user feedback.
'Could add to server logs instead for example, depending on app
requirements.
Me.lblErr.Text = "Error saving/updating record: " & ex.Message.ToString
Me.lblErr.Visible = True
Finally
If booCloseConn Then
If Me.sconOHA.State = ConnectionState.Open Then
Me.sconOHA.Close()
End If
End If
Me.objCommand.Parameters.Clear()
End Try
End Sub
<end sample>
Hope that helps.
--
Alec MacLean
Tracey said:
Sorry for the repeated post. I tried to update a record in database using
SqlCommand.ExecuteNonQuery( ) method (I failed using SqlDataAdapter). I
traced the above statement and found that it returned 1 rows afftected.
Then I checked my database, the data was not updated at all!
