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
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
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:
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:
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:
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.
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:
already removed exception clause but doesn't help.
The value is 0 which means no rows is updated. I tried remove the WHERE clause and still cannot update. still return 0.
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!!
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??
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()
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.
finally it works!!!! finally!!! thx
PatriceSc u solve my problem!!!
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
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.
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
None
0 Points
38 Posts
asp.net .vb UPDATE ms access database not working
Jan 11, 2014 10:35 PM|I0ri|LINK
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!
All-Star
50801 Points
9892 Posts
Re: asp.net .vb UPDATE ms access database not working
Jan 11, 2014 10:48 PM|A2H|LINK
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 IfEDIT: Corrected the code and added more details
Aje
My Blog | Dotnet Funda
None
0 Points
38 Posts
Re: asp.net .vb UPDATE ms access database not working
Jan 12, 2014 05:58 AM|I0ri|LINK
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
Star
12777 Points
1635 Posts
Re: asp.net .vb UPDATE ms access database not working
Jan 13, 2014 03:40 AM|Terry Guo - MSFT|LINK
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
None
0 Points
38 Posts
Re: asp.net .vb UPDATE ms access database not working
Jan 13, 2014 05:31 AM|I0ri|LINK
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 ClassAll-Star
25756 Points
7014 Posts
Re: asp.net .vb UPDATE ms access database not working
Jan 13, 2014 03:43 PM|hans_v|LINK
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
None
0 Points
38 Posts
Re: asp.net .vb UPDATE ms access database not working
Jan 14, 2014 07:04 AM|I0ri|LINK
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.
All-Star
25756 Points
7014 Posts
Re: asp.net .vb UPDATE ms access database not working
Jan 14, 2014 07:18 AM|hans_v|LINK
I understand, but when you start learning bad practice from day1, you'll be doing that for ever. I've seen that many times.
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
All-Star
47860 Points
17748 Posts
Re: asp.net .vb UPDATE ms access database not working
Jan 14, 2014 07:48 AM|PatriceSc|LINK
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.
None
0 Points
38 Posts
Re: asp.net .vb UPDATE ms access database not working
Jan 14, 2014 10:12 PM|I0ri|LINK
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:
@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??
All-Star
25756 Points
7014 Posts
Re: asp.net .vb UPDATE ms access database not working
Jan 15, 2014 04:46 AM|hans_v|LINK
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
All-Star
47860 Points
17748 Posts
Re: asp.net .vb UPDATE ms access database not working
Jan 15, 2014 05:07 AM|PatriceSc|LINK
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()All-Star
25756 Points
7014 Posts
Re: asp.net .vb UPDATE ms access database not working
Jan 15, 2014 05:25 AM|hans_v|LINK
the path is missing the first 2 characters
http://postimg.org/image/kwka587y5/full/
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
None
0 Points
38 Posts
Re: asp.net .vb UPDATE ms access database not working
Jan 15, 2014 10:42 AM|I0ri|LINK
finally it works!!!! finally!!! thx PatriceSc u solve my problem!!!
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 SubAll-Star
25756 Points
7014 Posts
Re: asp.net .vb UPDATE ms access database not working
Jan 16, 2014 01:40 AM|hans_v|LINK
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.
None
0 Points
38 Posts
Re: asp.net .vb UPDATE ms access database not working
Jan 16, 2014 09:02 PM|I0ri|LINK
@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
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