updating records to access database

[CGreenMTU]

I'm having problems figuring out the code to update records from a web form to an access database. I have a button which Adds a new record to the database, which works. However, I'm having trouble trying to code the Update button.

Here is what i'm using to Add Records to my database. Any suggestions for Updating records?

Private Sub lnkSaveClaim_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lnkSaveClaim.Click
'If user has filled every text box correctly...
If Page.IsValid Then
'Create a temporary dataset to contain the new record
Dim dsTemp As New DataSet

'Fill the temporary dataset
daNewClaim.Fill(dsTemp)

'Create a new row
Dim r As DataRow = dsTemp.Tables(0).NewRow()

'Add the category, reading its value from the text box
r("Account Name") = ddlInsured.SelectedItem()
r("Division") = ddlDivisions.SelectedItem
r("TrailorType") = ddlTrailorType.SelectedItem
r("INSCompany") = txtInsCo.Text
r("Policy_ID") = txtPolicyID.Text
r("Policy Number") = txtPolicyNumber.Text
r("Claim Number") = txtClaimNUmber.Text
r("Type of Loss") = ddlLossType.SelectedItem
r("Insurance Claim Number") = txtInsClaimNumber.Text
r("Claim Status") = ddlStatus.SelectedItem
r("Date Closed") = txtDateClosed.Text
r("Reopen") = txtReopened.Text
r("ReClosed") = txtReClosed.Text
r("Report Date") = txtReportDate.Text
r("Date of Loss") = txtDateofLoss.Text
r("Time of Loss") = txtTimeofLoss.Text
r("City of Loss") = txtCityofLoss.Text
r("State of Loss") = txtStofLoss.Text
r("Location Code") = ddlLocationType.SelectedItem
r("Tractor") = txtTractor.Text
r("Trailer1") = txtTrailor.Text
r("VIN") = txtVIN.Text
r("Unladen?") = ckbUnladen.Checked
r("TPA") = ckbTPA.Checked
r("Subrogation") = ckbSubro.Checked
r("Last Name") = txtLast.Text
r("First Name") = txtFirst.Text
r("MI") = txtMiddle.Text
r("License Number") = txtLicNum.Text
r("License State") = txtLicSt.Text
r("Driver Hire Date") = txtStartDAte.Text

'Add the new row into the dataset's row collection
dsTemp.Tables(0).Rows.Add(r)

'Update the database using the temporary dataset
daNewClaim.Update(dsTemp)
End If

 

[jzelhart]

I would use an Insert and Update query instead setting parameters equal to your text. The following is for SQL but should point you in the right direction...

Public Function insertExpDetail(ByVal strID As String, ByVal strDate As String, ByVal strClient As String, ByVal strMiles As String, ByVal strUser As String) As Boolean
Dim blnInsert As Boolean = True

Try
Dim strSQL As String = "INSERT INTO Detail_T (Expense_DC, Date_DT, Client_VC, Miles, Table_ID ) SELECT [MileageRate_DC]* @strMiles AS ExtMileRate, @strDate, @strClient, @strMiles, @strID FROM User_T WHERE (((User_T.User_ID)=@strUser));"

conn = New SqlConnection(strConn)
Dim cmd As SqlCommand = New SqlCommand(strSQL, conn)
cmd.Parameters.Add(New SqlParameter("@strUser", SqlDbType.Int))
cmd.Parameters("@strUser").Value = strUserD
cmd.Parameters.Add(New SqlParameter("@strID", SqlDbType.Int))
cmd.Parameters("@strID").Value = strID
cmd.Parameters.Add(New SqlParameter("@strDate", SqlDbType.DateTime))
cmd.Parameters("@strDate").Value = strDate
cmd.Parameters.Add(New SqlParameter("@strClient", SqlDbType.VarChar, (100)))
cmd.Parameters("@strClient").Value = strClient
cmd.Parameters.Add(New SqlParameter("@strMiles", SqlDbType.Decimal, (5)))
cmd.Parameters("@strMiles").Value = strMiles
conn.Open()
blnInsert = cmd.ExecuteNonQuery()
Catch ex As Exception
blnInsert = 0
Finally
If Not conn Is Nothing Then
conn.Close()
End If
End Try

Return blnInsert
End Function

Update is similar:

Public Function updateExpenseDetail(ByVal strID As String, ByVal strDate As String, ByVal strClient As String, ByVal strMiles As String) As Integer
Dim intUpdate As Integer = 0

Try
Dim strSQL As String = "UPDATE dbo.Detail_T Set dbo.Detail_T.Date_DT = @strDate, dbo.Detail_T.Client_VC = @strClient, dbo.Detail_T.Miles = @strMiles, dbo.Detail_T.Expense_DC = @strMiles*dbo.User_T.MileageRate_DC FROM dbo.Expense_T INNER JOIN dbo.ExpenseDetail_T ON dbo.Expense_T.Expense_ID = dbo.ExpenseDetail_T.Expense_ID INNER JOIN dbo.User_T ON dbo.Expense_T.User_ID = dbo.User_T.User_ID WHERE dbo.ExpenseDetail_T.ExpenseDetail_ID = @strID"

conn = New SqlConnection(strConn)
Dim cmd As SqlCommand = New SqlCommand(strSQL, conn)
cmd.Parameters.Add(New SqlParameter("@strID", SqlDbType.Int))
cmd.Parameters("@strID").Value = strID
cmd.Parameters.Add(New SqlParameter("@strDate", SqlDbType.DateTime))
cmd.Parameters("@strDate").Value = strDate
cmd.Parameters.Add(New SqlParameter("@strClient", SqlDbType.VarChar, (100)))
cmd.Parameters("@strClient").Value = strClient
cmd.Parameters.Add(New SqlParameter("@strMiles", SqlDbType.Decimal, (5)))
cmd.Parameters("@strMiles").Value = strMiles
conn.Open()
intUpdate = cmd.ExecuteNonQuery()
Catch ex As Exception
intUpdate = 0
Finally
If Not conn Is Nothing Then
conn.Close()
End If
End Try

Return intUpdate
End Function

Hope this helps!

Hope everyone is having a great day!

Thanks - Jennifer

 

[CGreenMTU]

i'm still having problems because i'm trying to insert records into my database which retrieve data from two data tables....

what would be the easiest way to do this?

 

[jzelhart]

You should have two separate insert statements that will need to be ran.

Hope everyone is having a great day!

Thanks - Jennifer