ASP.Net - not able to write to an access database table

can connect & display data from a table, but not add/update record

Page 1 of 1

1 Replies - 2336 Views - Last Post: 25 August 2010 - 01:30 PM Rate Topic: -----

#1 Guest_newb1*


Reputation:

ASP.Net - not able to write to an access database table

Posted 25 August 2010 - 06:25 AM

Hi

i'm a newb trying to make an ASP.NET application with VB.net.
The main page displays all records from a table in an Access database. there is a select button in the first column. there are 3 buttons on the page - Add, Edit, Delete.

When the 'Add' button is clicked, a page with relevant textboxes is presented. when the data is input into the boxes and the 'Save' button is clicked, a new row should be added to the table.
When a record is selected, and 'Edit' button clicked, the relevant record is displayed in a new page. A 'Save' button is present to save the record.
Similarly, if the 'Delete' button is clicked after selecting a record, the relevant record is displayed in a new page. A 'Confirm Delete' button is present to delete the record.

the first page works correctly. the gridview displays all records correctly. even the selected record is displayed for 'edit' or 'delete' correctly. but records cannot be added, or updated to or deleted from the table.

the errors produced are as follow:

Add: "operation must use an updateable query"
Edit: "Update unable to find TableMapping["Job1"] or DataTable 'Job1'
Delete: "Couldnot delete from specified Tables"

I had tried 2 applications before this. The first one was a very simple test of Add & Edit from a table. when i got it to work, i started a new one so that i dont mess with a working example. The second one was similar to the current app with Add, Edit, Delete etc. Again i started a new one so as not to mess up a working example. This third one uses the same (similar) code to the second one, so i cant make out why its not working in the 3rd app. I hope there is not a limit of 2 apps only on 'localhost'.

Codes are as follows:

Add code:

       Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\CVE.mdb"

        Dim str As String
        Dim connection As OleDb.OleDbConnection    ' SqlConnection
        str = "Select * From JobT1Mst where jobT1id=" & Me.txtJobT1id.Text
        connection = New OleDb.OleDbConnection(connectionString)

        Try
            connection.Open()
            Dim cmd1 As New OleDbCommand(str, connection)

            Dim dr1 As OleDbDataReader
            dr1 = cmd1.ExecuteReader
            dr1.Read()
            Dim b As Boolean
            b = CBool(dr1.HasRows)
            If b = True Then
                'MsgBox("This Item Already Exists In The Database", MsgBoxStyle.Information, "The App")
                Return
            End If
            dr1.Close()
        Catch ex As Exception
            'MsgBox(ex.Message)
        Finally
            connection.Close()
        End Try
        Dim con As New OleDb.OleDbConnection(connectionString)

        Try
            con.Open()
            Dim cmd As New OleDbCommand("insert into JobT1Mst(jobT1id,coid,venid,jobtype1,jobtype1D,jobtype1Sr,lmodi,userName) values(" & Me.txtJobT1id.Text & "," & Me.txtCOid.Text & "," & Me.txtvenid.Text & ",'" & Me.txtJobType1.Text & "','" & Me.txtJobType1D.Text & "'," & Me.txtJobType1Sr.Text & ",'" & Me.txtLmodi.Text & "','" & Me.txtusername.Text & "')", con)


            If cmd.ExecuteNonQuery Then
                'MsgBox("Patient Has Been Added To The Database", MsgBoxStyle.Information, "The App")

            End If
        Catch ex As Exception
            'MsgBox(ex.Message, MsgBoxStyle.Information, "The App")
        Finally
            con.Close()
        End Try




Edit code:

       Dim str As String
        Dim connectionString As String
        Dim connection As OleDb.OleDbConnection    ' SqlConnection
        Dim adapter As OleDb.OleDbDataAdapter       ' SqlDataAdapter
        Dim ds As New DataSet
        Dim dt As New DataTable
        str = "Select * From JobT1Mst where JobT1id=" & Me.txtJobT1id.Text
        connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\CVE.mdb"
        connection = New OleDb.OleDbConnection(connectionString)
        Try
            connection.Open()
            adapter = New OleDb.OleDbDataAdapter(str, connection)
            adapter.Fill(ds)
            '
            ds.Tables(0).Rows(0).Item("JobT1id") = Me.txtJobT1id.Text
            ds.Tables(0).Rows(0).Item("coid") = Me.txtCOid.Text
            ds.Tables(0).Rows(0).Item("venid") = Me.txtvenid.Text

            ds.Tables(0).Rows(0).Item("JobType1") = Me.txtJobType1.Text
            ds.Tables(0).Rows(0).Item("JobType1D") = Me.txtJobType1D.Text
            ds.Tables(0).Rows(0).Item("JobType1Sr") = Me.txtJobType1Sr.Text
            ds.Tables(0).Rows(0).Item("lmodi") = Now        ' Format(Now, "dd/MM/yyyy")
            ds.Tables(0).Rows(0).Item("username") = Me.txtusername.Text

            'dt.Rows.Add(newRow)
            '
            Dim cb As New OleDbCommandBuilder(adapter)
            adapter.InsertCommand = cb.GetInsertCommand
            adapter.DeleteCommand = cb.GetDeleteCommand
            adapter.UpdateCommand = cb.GetUpdateCommand

            adapter.SelectCommand = adapter.UpdateCommand


            If ds.HasChanges Then
                '
                adapter.Update(ds, "JobT1Mst")
                'adapter.Update(ds)
                ds.AcceptChanges()

            End If
            connection.Close()
        Catch ex As Exception
            'MsgBox(ex.ToString)
        End Try





Delete code:

	  Dim JobT1idTmp As Int32 = 0
        JobT1idTmp = Session("JobT1idKey")

        Dim str As String
        Dim connectionString As String
        Dim con As OleDb.OleDbConnection
        str = "Select * From JobT1Mst where JobT1id=" & JobT1idTmp

        connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\CVE.mdb"

        con = New OleDb.OleDbConnection(connectionString)

        Dim cmdStr As String

        Try
            con.Open()
            cmdStr = "delete from JobT1Mst where JobT1id=" & JobT1idTmp          'Me.txtMID.Text
            Dim cmd As New OleDbCommand(cmdStr, con)

            If cmd.ExecuteNonQuery Then
                'MsgBox("Patient Has Been Deleted from The Database", MsgBoxStyle.Information, "The App")
            End If
        Catch ex As Exception
            'MsgBox(ex.Message, MsgBoxStyle.Information, "The App")
        Finally
            con.Close()
        End Try







sorry for the long winded approach, but i wanted to be as detailed as possible, because i think this is not a coding issue as much as some kind of permissions issue.


thanks.

Is This A Good Question/Topic? 0

Replies To: ASP.Net - not able to write to an access database table

#2 Guest_newb1*


Reputation:

Re: ASP.Net - not able to write to an access database table

Posted 25 August 2010 - 01:30 PM

never mind. got it to work.

thanks.
Was This Post Helpful? 0

Page 1 of 1