locked
asp.net .vb UPDATE ms access database not working RRS feed

  • Question

  • User-1974669539 posted

    Hi all,

    I am unable to do an update data to my ms access database table. After i run my .aspx, i check the database table but still data not updated.

    The SELECT SQL statement works perfectly fine and I am able to set the counter to 1

    but I can't do an UPDATE. I try INSERT statement also cannot work.

    I try to use breakpoint but still cannot figure out whats wrong. My codes seems fine.

    What has actually gone wrong? My Code? Need to configure some setting on the ms access DB for update statement?

    Please see below 3 screenshot. Please advice!

     

    Saturday, January 11, 2014 10:35 PM

Answers

  • User753101303 posted

    I don't see any of your screenshots. Not sure if blocked on my side or what...

    Ah, you don't run the UPDATE statement. You assign a SQL Statement to a sqlStr string but then you execute cmd which is still a SELECT statement... Waht if you insert a cmd=new OleDbCommand(strSql,cnn) to recreate the cmd object for your new SQL statement... That is :

    dataReader.Close()
                    Dim online As String = "online"
                    sqlStr = "UPDATE Credential SET [LoginFlag]='" + online + "', [SessionID]='" + SID + "' WHERE [Username]='" + username + "'"
                    Trace.Write(sqlStr)
                    cmd=new OleDbCommand(strSql,cnn) ' Added
                    cmd.ExecuteNonQuery()
    

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 15, 2014 5:07 AM

All replies

  • User2103319870 posted

    Hi,

    Issue is that you are not executing your update statement.I couldnt see that you are executing the update statement anywhere in your code.You can use ExecuteNonQuery method to execute the update query

    You can modify  and  use the below code

    If counter = 1 Then
                dataReader.Close()
                Dim online As String = "Online"
                SqlStr = "Update Credential Set ......."
                'Execute your update statement
                cmd.ExecuteNonQuery()
            End If

    EDIT: Corrected the code and added more details

    Saturday, January 11, 2014 10:48 PM
  • User-1974669539 posted

    hi A2H. thanks for replying.

    I actually already did add the cmd.ExecuteNonQuery() before your reply but it still doesn't work.

    I am so puzzled right now. A2H Please assist! Thanks Embarassed

     

    Sunday, January 12, 2014 5:58 AM
  • User697462465 posted

    Hi I0ri,

    Can you post your sample code here, I can't see the images have your post.

    Do you have got exception or error message?

    Best Regards,
    Terry Guo

    Monday, January 13, 2014 3:40 AM
  • User-1974669539 posted

    Thanks for helping Terry! Below is the code!

    My database consist of a table called Credential with 4 fields Username, Password, LoginFlag and SessionID

    SELECT STATEMENT seems fine but UPDATE is NOT working.

     I am pretty sure my code is correct. I follow the code from asp.net workbook by Heng Ngee Mok.

    Imports System.Data.OleDb
    Public Class Authentication
        Inherits System.Web.UI.Page
    
    
        Dim cnn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\MMORPGapi\MMORPG.mdb")
    
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            Dim asd As String = HttpContext.Current.Request("username")
            testLabel.Text = asd
    
            Dim username As String = "jianda"
            Dim pwd As String = "jiandapass"
            Dim SID As String = "tester"
            UsernameAndPwdAreValid(username, pwd, SID)
    
            Response.Write("Hello JD!")
        End Sub
    
    
        Private Sub UsernameAndPwdAreValid(ByVal username As String, ByVal pwd As String, ByVal SID As String)
            Dim sqlStr As String = "SELECT COUNT(*) AS NUMBEROFROWS FROM Credential WHERE [Username] = '" + username + "' AND [Password] = '" + pwd + "'"
    
            Dim cmd As New OleDbCommand(sqlStr, cnn)
            Dim dataReader As OleDbDataReader
            Trace.Write(sqlStr)
    
    
    
            Try
                cnn.Open()
                dataReader = cmd.ExecuteReader()
    
                Dim counter As Integer
    
                Do While dataReader.Read
                    counter = Convert.ToInt16(dataReader("NUMBEROFROWS"))
                Loop
    
                If counter = 1 Then
                    dataReader.Close()
                    Dim online As String = "online"
                    sqlStr = "UPDATE Credential SET [LoginFlag]='" + online + "', [SessionID]='" + SID + "' WHERE [Username]='" + username + "'"
                    Trace.Write(sqlStr)
                    cmd.ExecuteNonQuery()
                End If
    
    
            Catch ex As Exception
    
            Finally
                If Not cnn Is Nothing Then
                    cnn.Close()
                    cnn.Dispose()
                End If
            End Try
    
    
        End Sub
    
        
    End Class

     

    Monday, January 13, 2014 5:31 AM
  • User-1199946673 posted

    I0ri

    am pretty sure my code is correct. I follow the code from asp.net workbook by Heng Ngee Mok.

    If this comes from a book, I strongly advice you to buy another one....

    By concatenating the SQL string, your code is vulnarable to SQL injections. Also, the user input could cause queries to break

    http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

    Also, to get one value of one record in a select statement, it is an overkill to use a datareader. Simply use ExecuteScalar (see an example in the link above)

    And the darareader is obly close when counter is 1. It should be closed before the If statement, not IN the If statement,

    Finally. the code is intended for authenticate and authorize users. It reinvents a wheel that is created by Microsoft, and it is called Forms Authentication:

    http://msdn.microsoft.com/en-us/library/xdt4thhy(v=vs.100).aspx

    More advanced is to use a membership provider. By default, the build in Membership provider uses a SQL Server (Express) Database, but you can use Access as well:

    http://imar.spaanjaars.com/404/using-the-microsoft-access-providers-to-replace-the-built-in-sql-server-providers
    http://imar.spaanjaars.com/560/using-the-microsoft-access-providers-for-membership-roles-and-profile-under-aspnet-4

    Monday, January 13, 2014 3:43 PM
  • User-1974669539 posted

    Hi hans_v. thanks for your advice.

    I am trying to do a simple prototype so could u be kind enough to help me solve my problem with Updating data to the MS access database?

    Select statement is fine so the connection to the ms database is working fine. but it just could not do an update.

    Tuesday, January 14, 2014 7:04 AM
  • User-1199946673 posted

    I0ri

    I am trying to do a simple prototype

    I understand, but when you start learning bad practice from day1, you'll be doing that for ever. I've seen that many times.

    I0ri

    help me solve my problem with Updating data to the MS access database?

    Read the first link in my previous post. It contains examples of both SELECT. INSERT.. UPDATE and DELETE statements in both VB.NET and C#

    When it doesn't work, you need to tell us more. Do you encounter an error? If so, please tell us WHERE the error occurs and WHAT it says. Perhaps this:

    http://www.mikesdotnetting.com/Article/74/Solving-the-Operation-Must-Use-An-Updateable-Query-error

    Tuesday, January 14, 2014 7:18 AM
  • User753101303 posted

    Hi,

    1) Get rid of the empty catch clause: if an exception happens you are ignoring it which make basically impossible to debug your app.

    2) you can check the value returned by ExecuteNonQuery (if not 1 your where criteria is not correct)

    3) also sometimes when working with db files, it does work and appears to be fine but if you don't check the actual file that has been modified, it can give the impression that the file was not updated. Sometimes, the db file is even deployed again on the next build giving the impression that the changes were not permanent. So if #1 and #2 doesn't help, double check which file exactled is used by your app...

    Also have you used the VS debugger before posting to see if the update line is hit or what happens in details ? It should be your first step if not done yet.

    Tuesday, January 14, 2014 7:48 AM
  • User-1974669539 posted

    Hi hans_v and Patrice SC. thanks for help.

     

    @Hans_V:

    Hi hans_V. I already given alot of details on the very 1st post. There is no error when i run my aspx but i just don't see my database updated with the new data. My update sql statement is correct but why?? i did a breakpoint and i realise cmd.executenonquery return a 0 which mean the update is not succesfull.. i try with and without WHERE clause but still cannot update.

    SO i decided to try to use yur method but got ERROR when i run the aspx. please help me!

    @PatriceSc:

    1. already removed exception clause but doesn't help.
    2. The value is 0 which means no rows is updated. I tried remove the WHERE clause and still cannot update. still return 0.
    3. Once i execute my aspx, i always go to my .mdb directly to check if there is any changes but it just doesn't UPDATE!!
    4. I have always been using the VS debugger to check line by line but i still can't resolve the problem. Do take a look at my very 1st post screenshot. i did use breakpoints.

     

    @Hans_V and PatriceSc:

    I am devastated right now... Select statement works fine and I can also extract Data and link it and display on the Datagrid. But update and insert just doesn't work?? could it be some setting or restriction??

     

    Tuesday, January 14, 2014 10:12 PM
  • User-1199946673 posted

    SO i decided to try to use yur method but got ERROR

    And what does the error tell you?

    "The given path's format is not supported!"

    You modified the path in the connectionstring from

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\MMORPGapi\MMORPG.mdb"

    TO

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|c:\inetpub\wwwroot\MMORPGapi\MMORPG.mdb"

    But you need to read better.! In order to use the DataDirectory Directive, Mike writes:

    "To make use of |DataDirectory| make sure that your database file is in the App_Data folder of your web site"

    And then your connectionstring should look like:

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|MMORPG.mdb"

    By the way, next time, please don't post pictures, but show the relevant code and the eroor message you get

    Wednesday, January 15, 2014 4:46 AM
  • User753101303 posted

    I don't see any of your screenshots. Not sure if blocked on my side or what...

    Ah, you don't run the UPDATE statement. You assign a SQL Statement to a sqlStr string but then you execute cmd which is still a SELECT statement... Waht if you insert a cmd=new OleDbCommand(strSql,cnn) to recreate the cmd object for your new SQL statement... That is :

    dataReader.Close()
                    Dim online As String = "online"
                    sqlStr = "UPDATE Credential SET [LoginFlag]='" + online + "', [SessionID]='" + SID + "' WHERE [Username]='" + username + "'"
                    Trace.Write(sqlStr)
                    cmd=new OleDbCommand(strSql,cnn) ' Added
                    cmd.ExecuteNonQuery()
    

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 15, 2014 5:07 AM
  • User-1199946673 posted

    I don't see any of your screenshots. Not sure if blocked on my side or what...

    the path is missing the first 2 characters

    http://postimg.org/image/kwka587y5/full/

    Ah, you don't run the UPDATE statement. You assign a SQL Statement to a sqlStr string but then you execute cmd which is still a SELECT statement...

    Well spotted. However, this was in his original cide, which should better not be used...

    IDri

    When you've worked this out, please take a minute to reconsider what you want. You seem to make an attempt to log whoch users are online, which will fail, because the web is stateless. You can catch when a user logs in, but you won't be able to catch when users  leave your website, disconnect from the internet o close their browser

    Also, don't use session for authorization and authentocation, start using Forms Autentication.

    http://msdn.microsoft.com/en-us/library/xdt4thhy(v=vs.100).aspx

    You can consider to use Membership as well

     http://imar.spaanjaars.com/404/using-the-microsoft-access-providers-to-replace-the-built-in-sql-server-providers
    http://imar.spaanjaars.com/560/using-the-microsoft-access-providers-for-membership-roles-and-profile-under-aspnet-4

    Wednesday, January 15, 2014 5:25 AM
  • User-1974669539 posted

    finally it works!!!! finally!!! thx PatriceSc u solve my problem!!! Cry

    and thx hans_V for the hard work.

    Public Sub updateTwo()
    
            cnn2.Open()
            Dim sqlStr2 As String = "Update Credential SET LoginFlag = 'asdasd', SessionID = 'asdasd' Where Username = 'jianda'"
            Dim cmd2 As New OleDbCommand(sqlStr2, cnn2)
            Trace.Write(sqlStr2)
            cmd2.ExecuteNonQuery()
            If Not cnn2 Is Nothing Then
                cnn2.Close()
                cnn2.Dispose()
            End If
        End Sub

    Wednesday, January 15, 2014 10:42 AM
  • User-1199946673 posted

    I0ri

    finally it works!!!! finally!!!

    But the code you showed isn't good practice. You open the connectopn, but doesn't close it, which sooner rather than later will cuase problems when using an Access Database.  use the "using .... end using" pattern.

    Thursday, January 16, 2014 1:40 AM
  • User-1974669539 posted

    @Hans_V: thx for advice.

     

    Hi Hans_V and Patric!

    I have another issue regarding doing a Ajax post from mobile web app to an aspx that belong to a asp.net web application api (The aspx is actually what we have been discussing which is the update database). The ajax post is working fine to a dummy standalone aspx but I can't do a post to a aspx that belong to asp.net web application. because all those aspx has a this line

    <%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Authentication.aspx.vb" Inherits="MMORPGapi.Authentication" %>

    Which is required to run the .vb but need to run via visual studio and cannot directly access via web browser.

    Could you kindly go to http://forums.asp.net/t/1960466.aspx?Send+data+from+mobile+web+application+to+asp+net+application+aspx+vb and read the very last reply/post i made. Please assist

    Thursday, January 16, 2014 9:02 PM