I unable to update my access database in asp.net using VB.
i am using OleDB connection.
It does not show error and not update a database.
Please help me.
Imports System.Data
Imports System.Data.OleDb
Public Class Customer_Details
Inherits System.Web.UI.Page
Dim sql As String
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Dim conn As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("dbconnection").ConnectionString)
Dim da As OleDbDataAdapter
Dim dt As New DataTable
Protected Sub btnback_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnback.Click
Response.Redirect("Customer.aspx")
End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Me.txtCustNo.Text = Request.QueryString("ID")
load_Cust()
btnUpdate.Visible = False
btnCancle.Visible = False
pnAdmin.Visible = True
pnUser.Visible = False
Hhide_pan()
End Sub
Protected Sub Hhide_pan()
pnAddContact.Visible = False
pnAddMachine.Visible = False
End Sub
Public Function FixNull(ByVal o As Object) As Object
If IsDBNull(o) Then
Return Nothing
Else
Return o
End If
End Function
Protected Sub load_Cust()
Try
sql = "SELECT Firstname, Lastname, Company, Address, City, Pincode, State, Email, STD, Phoneno, Mobile, Customertype, Duration, Startdate, Enddate FROM tblCustomer WHERE CustomerID=" & txtCustNo.Text & ""
conn.Open()
cmd = New OleDbCommand(sql, conn)
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
If dr.Read Then
txtFirstName.Text = dr(0)
txtLastName.Text = dr(1)
txtCompany.Text = FixNull(dr(2))
txtAddress.Text = dr(3)
txtCity.Text = dr(4)
txtPincode.Text = dr(5)
txtState.Text = dr(6)
txtEmail.Text = FixNull(dr(7))
txtSTD.Text = dr(8)
txtPhone.Text = dr(9)
txtMobile.Text = dr(10)
drpCustType.SelectedItem.Text = dr(11)
If drpCustType.SelectedItem.Text = "AMC" Then
drpDuration.SelectedItem.Text = dr(12)
txtStartDate.Text = dr(13)
txtEndDate.Text = dr(14)
End If
End If
Catch ex As Exception
MsgBox(ex.Message)
Finally
cmd.Dispose()
conn.Close()
End Try
End Sub
Protected Sub Update_Customer()
Try
sql = "UPDATE tblCustomer SET Firstname = @FN, Lastname = @LN, Company= @CO, Address = @AD, City = @CI, Pincode = @PI, State = @ST, Email = @EM, STD = @STD, Phoneno = @PN, Mobile = @MN, Customertype = @CT, Duration = @DU, Startdate = @SD, Enddate = @ED WHERE CustomerID = @CID"
conn.Open()
Trace.Write(sql)
cmd = New OleDbCommand(sql, conn)
cmd.Parameters.AddWithValue("@FN", txtFirstName.Text)
cmd.Parameters.AddWithValue("@LN", txtLastName.Text)
cmd.Parameters.AddWithValue("@CO", txtCompany.Text)
cmd.Parameters.AddWithValue("@AD", txtAddress.Text)
cmd.Parameters.AddWithValue("@CI", txtCity.Text)
cmd.Parameters.AddWithValue("@PI", txtPincode.Text)
cmd.Parameters.AddWithValue("@ST", txtPincode.Text)
cmd.Parameters.AddWithValue("@EM", txtEmail.Text)
cmd.Parameters.AddWithValue("@STD", txtSTD.Text)
cmd.Parameters.AddWithValue("@PN", txtPhone.Text)
cmd.Parameters.AddWithValue("@MN", txtMobile.Text)
cmd.Parameters.AddWithValue("@CT", drpCustType.SelectedItem.Text)
cmd.Parameters.AddWithValue("@DU", drpDuration.SelectedItem.Text)
cmd.Parameters.AddWithValue("@SD", txtStartDate.Text)
cmd.Parameters.AddWithValue("@ED", txtEndDate.Text)
cmd.Parameters.AddWithValue("@CID", txtCustNo.Text)
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
Finally
cmd.Dispose()
conn.Close()
End Try
End Sub
Protected Sub btnedit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnedit.Click
btnUpdate.Visible = True
btnCancle.Visible = True
End Sub
Protected Sub btnUpdate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnUpdate.Click
UpdateCust()
Response.Redirect("Customer.aspx")
End Sub
End Class