Updating, Deleting and Inserting Data in the Database (DataAdapter Datatable)

EDN Admin

Well-known member
Joined
Aug 7, 2010
Messages
12,794
Location
In the Machine
I have written 2 different procedures that allow users to select data from DB table and other procedure that allows users to insert data into the database. The two procedures are triggered by different events. (Below is a copy of my code for reference)

I am trying to replicate the same functionality to that of SQL Server where new rows are added to the DB when user completes writing data into the new row. Also at the same time I want the functionality where each record is updated once user makes changes
to an exisitng record.
I was hoping to find one function that may help me achieve my goal rather than having different subs/functions.


Any help or suggestion will be appreciated.
-Thanks,
<pre class="prettyprint lang-vb Sub LoadScenario()
Dim dtResult As New DataTable
Dim ds As New DataSet

If LnkChooseScen.Focused Then
objScen.Mode = "load"
ElseIf btnSaveScen.Focused Then
objScen.Mode = "insert"
End If

Try
oConn = New SqlConnection(mdConnection.Connection)
If oConn.State = ConnectionState.Closed Then
oConn.Open()
End If

oDap = New SqlDataAdapter("SELECT * FROM Table", oConn)

oDap.Fill(dtResult)

dgMDITestScen.DataSource = dtResult

oConn.Close()
oConn.Dispose()
oDap.Dispose()
Catch ex As Exception

End Try
End Sub


Private Sub btnSaveScen_Click(ByVal sender As System.Object, ByVal e As EventArgs) Handles btnSaveScen.Click
Dim dtresult As DataTable
Dim ds As New DataSet
Dim i As Integer

Try

oConn = New SqlConnection(mdConnection.Connection)
If oConn.State = ConnectionState.Closed Then
oConn.Open()

dtresult = dgMDITestScen.DataSource

oCmd = New SqlCommand("UPDATE Table SET SCN_NAME = @SCN_NAME, SCN_DESCRIPTION = @SCN_DESCRIPTION" & _
" WHERE SCN_ID = @SCN_ID", oConn)
oDap.UpdateCommand = oCmd

oDap.UpdateCommand.Parameters.Add("@SCN_ID", SqlDbType.Int, 5, "SCN_ID")
oDap.UpdateCommand.Parameters.Add("@SCN_NAME", SqlDbType.NVarChar, 50, "SCN_NAME")
oDap.UpdateCommand.Parameters.Add("@SCN_DESCRIPTION", SqlDbType.NVarChar, 100, "SCN_DESCRIPTION")

Dim oCmdbuilder As SqlCommandBuilder = New SqlCommandBuilder(oDap)

oCmdbuilder.GetUpdateCommand.Parameters.Add(dtresult)

oDap.Update(dtresult)
dtresult.AcceptChanges()

oCmd = New SqlCommand("INSERT INTO Table (SCN_ID, SCN_NAME, SCN_DESCRIPTION)" & _
"VALUES(@SCN_ID, @SCN_NAME, @SCN_DESCRIPTION)", oConn)

oDap = New SqlDataAdapter("INSERT INTO Table (SCN_ID, SCN_NAME, SCN_DESCRIPTION)" & _
"VALUES(@SCN_ID, @SCN_NAME, @SCN_DESCRIPTION)", oConn)
oDap.InsertCommand = oCmd
oDap.InsertCommand.Parameters.Add("@SCN_ID", SqlDbType.Int, 5, "SCN_ID")
oDap.InsertCommand.Parameters.Add("@SCN_NAME", SqlDbType.NVarChar, 50, "SCN_NAME")
oDap.InsertCommand.Parameters.Add("@SCN_DESCRIPTION", SqlDbType.NVarChar, 100, "SCN_DESCRIPTION")

oDap.Update(dtresult)

oConn.Close()
oConn.Dispose()
oDap.Dispose()

End If

Catch ex As Exception

End Try

LoadScenario()


End Sub[/code]
<br/>
<br/>
<br/>

View the full article
 


Write your reply...
Back
Top