EDN Admin
Well-known member
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
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