By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,064 Members | 1,388 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,064 IT Pros & Developers. It's quick & easy.

Urgent: I can't update database with SqlCommand

P: n/a
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

Can someone give me a clue ?
Nov 18 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
can you trace it using SQL Profiler and run it in the query analyzer
directly.. and check.

Av.
"Tracey" <an*******@discussions.microsoft.com> wrote in message
news:F4**********************************@microsof t.com...
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!

Can someone give me a clue ?

Nov 18 '05 #2

P: n/a
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" <an*******@discussions.microsoft.com> wrote in message
news:F4**********************************@microsof t.com...
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!
Can someone give me a clue ?

Nov 18 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.