Click here to Skip to main content
15,892,005 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
VB
 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()         'Show data Basic Information of Textbox

        btnUpdate.Visible = False
        btnCancle.Visible = False
 
        'Menu Show / Hide
        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()   'Show data Basic Information of Textbox
        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] = '" & txtFirstName.Text & "', [Lastname] = '" & txtLastName.Text & "', [Company] = '" & txtCompany.Text & "', [Address] = '" & txtAddress.Text & "', [City] = '" & txtCity.Text & "', [Pincode] = '" & txtPincode.Text & "', [State] = '" & txtState.Text & "', [Email] = '" & txtEmail.Text & "', [STD] = '" & txtSTD.Text & "', [Phoneno] = '" & txtPhone.Text & "', [Mobile] = '" & txtMobile.Text & "', [Customertype] = '" & drpCustType.SelectedItem.Text & "', [Duration] = '" & drpDuration.SelectedItem.Text & "', [Startdate] = '" & txtStartDate.Text & "', [Enddate] = '" & txtEndDate.Text & "' WHERE [CustomerID]=" & txtCustNo.Text & " "
            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
        'Update_Customer()
        UpdateCust()
        Response.Redirect("Customer.aspx")
 
    End Sub
End Class
Posted
Updated 13-Apr-15 23:32pm
v2
Comments
Kenneth Haugland 12-Apr-15 3:27am    
Whatever you do don't do that. SQL injection attacks ring any bells? Use parameterized SQL queries instead.

Oh dear...

Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
The chances are that that will cure your problem at the same time.

VB
sql = "UPDATE tblCustomer SET Firstname = @FN, Lastname = @LN, Company = @CO, ... WHERE CustomerID=@CI"
conn.Open()
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("@CI", txtCustNo.Text)
cmd.ExecuteNonQuery()
Just fill in the dots, and off you go.
 
Share this answer
 
Comments
SPhutane 13-Apr-15 3:09am    
Still Output is same.
NOt to update and not shows a error
OriginalGriff 13-Apr-15 5:52am    
So show us the code you used to test last.
Also use the debugger to look at the value in txtCustNo, and check using SSMS that that value exists in the DB.
SPhutane 14-Apr-15 4:17am    
i past my full code.
OriginalGriff 14-Apr-15 4:24am    
So what is in txtCustNo? Did you look?
SPhutane 14-Apr-15 5:33am    
it receive CustomerID value from table
It might at least be few reasons of your database isn't being updated...

1. the database is stored in the same directory as project files
    Move it into different directory (for example: D:\Mydatabase\)
2. the database is locked by another user
    Close the database and project and re-run it.
3. connection string issues
4. command text issues
5. etc...
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900