Can't Update and Delete into Access DataBase

Question

0.00/5 (No votes)

See more:

I’m trying to create Web Application using ASP.Net via Visual Studio 2010.
The Database that I use is Access, with connection Method OleDb

When using the query “Update” or “Delete”, the programs shows no errors, but the data in Access does not change.
While for query “Select” and “Insert”, the data is displayed and inserted successfully.

The Following code is the sample query “Delete” that I used..

HTML

Imports System.Data.OleDb

Partial Class frmTEst
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
       
    Dim dbConnection As OleDbConnection
        Dim dbCommand As OleDbCommand
        Dim sqlString As String

        dbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; _
Data Source=" & Server.MapPath("App_Data/dbKAE.mdb"))
        dbConnection.Open()

        sqlString = "DELETE FROM tblPengawasan WHERE no='31'"

        dbCommand = New OleDbCommand(sqlString, dbConnection)
        dbCommand.ExecuteNonQuery()

    End Sub
End Class

Previously I tried a direct query using AccessDataSource, but the result just the same, “Update” and “Delete” can’t be done. But the “Select” and “Insert” can be executed successfully.

Is there any problem with my Access configuration or whether the problem is in the coding itself..?

Thanks..

Posted 25-Oct-11 22:42pm

sacrofia

Add a Solution

Comments

RaisKazi 26-Oct-11 4:44am

What is a Data-Type of Column-"no"?

Amir Mahfoozi 26-Oct-11 5:11am

after delete or update , close and reopen the connection. I think it is a refresh problem.

5 solutions

Solution 2

Delete
sqlString = "DELETE FROM tblPengawasan WHERE columnname= '" + 31(based on your desired string) + "'"

Update
SqlCommand sqlCmd = new SqlCommand(string.Format("Update [tblPengawasan ] set Column1= '{0}' where columnname='{1}'", Column1.String1, columnname.String2), dbConnection );

For more information please refer to Google ^_^

Happy Coding

Posted 25-Oct-11 23:05pm

janwel

Comments

janwel 26-Oct-11 5:12am

Im not into C# cause ive focused on vb but hope you catch what I am trying to say

Solution 5

Thanks everyone. I can executed "Update" and "Delete" now..
you are right, the problem is type mismatch in my "no" column, that was autoNumber type.

Thank you too for the suggestion in using try-catch block (usually i not use it heheheh).. i'll use it for my entire coding..

thanks again for helping me..

Posted 26-Oct-11 0:25am

sacrofia

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Bala Selvanayagam · Accepted Answer · 2011-10-25T23:15:00

Looks like your statement does not work when you user a where clause (update/Delete) and the possible reason for this could be the data type mismatch.

Your "no" column in the database may be not a text type but a number and when you use WHERE no='31' the query does not get executed (data type mismatch in your where clause) and goes unnoticed because you have not hanled the exceptions.

Check you database and if the column type is number / autonumber try using
WHERE no=31 (without single quote) and it should work

OriginalGriff · Accepted Answer · 2011-10-25T22:46:00

If you do not get any exception when you execute the code, then the most likely error is that the database does not contain any columns where "no" is equal to "31". Check it, and make sure you are referencing the right database both when you check, and when you run the code.

It is also worth adding some logging to make sure you are executing the code. I would also put a try-catch block around the whole code to make sure that you don't get any exceptions, and to report them if you do.

wade333 · Accepted Answer · 2011-10-25T23:24:00

SQL

Vote:


Solution 3Accept Solution Reject Solution
Looks like your statement does not work when you user a where clause (update/Delete) and the possible reason for this could be the data type mismatch.

Your "no" column in the database may be not a text type but a number and when you use WHERE no='31' the query does not get executed (data type mismatch in your where clause) and goes unnoticed because you have not hanled the exceptions.

Check you database and if the column type is number / autonumber try using
WHERE no=31 (without single quote) and it should work

from Cheap New Era Hats

Can't Update and Delete into Access DataBase

5 solutions

Solution 3

Solution 1

Solution 2

Solution 5

Solution 4

Add your solution here

Preview 0