Share via

how to return only one row from an access database

Question

Sunday, May 17, 2009 3:00 PM

 i am trying to create a query that will return the value of the CustID in the last row in the Customers table and i cant get it to work, the query i have is

SELECT CustID FROM Customers ORDER BY CustID DECS LIMIT 1

my code is as follows

 

1    Imports System.Data.OleDb
2    Imports System.Data
3    Partial Class Checkout_Confirm
4        Inherits System.Web.UI.Page
5    
6        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
7    
8            Dim dbconn As New OleDbConnection
9            Dim sql As String
10           Dim dbcomm As New OleDbCommand
11           Dim dbread As OleDbDataReader
12   
13   
14           dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("App_Data\DWTProject.mdb"))
15           dbconn.Open()
16           sql = "SELECT CustID FROM Customers ORDER BY CustID DECS LIMIT 1"
17           dbcomm = New OleDbCommand(sql, dbconn)
18           dbread = dbcomm.ExecuteReader()
19   
20           dbconn.Close()
21   
22           dbcomm = New OleDbCommand("INSERT INTO Orders (CustID, IsComplete) VALUES (@CustID, TRUE) WHERE (OrderID = @OrderID)", dbconn)
23           dbcomm.Parameters.AddWithValue("CustID", dbread)
24           dbcomm.Parameters.AddWithValue("OrderID", Session.SessionID)
25   
26           dbconn.Open()
27           dbcomm.ExecuteNonQuery()
28           dbread.Close()
29           dbconn.Close()
30   
31   
32       End Sub
33   End Class

  the error i am getting is

 

Server Error in '/project' Application.

Syntax error (missing operator) in query expression 'CustID DECS LIMIT 1'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression 'CustID DECS LIMIT 1'.

Source Error:

Line 16:         sql = "SELECT CustID FROM Customers ORDER BY CustID DECS LIMIT 1"
Line 17:         dbcomm = New OleDbCommand(sql, dbconn)
Line 18:         dbread = dbcomm.ExecuteReader()
Line 19: 
Line 20:         dbconn.Close()

Source File: C:\Users\Steven\Desktop\DWTBackUp\project\Checkout_Confirm.aspx.vb **   Line:** 18

Stack Trace:

[OleDbException (0x80040e14): Syntax error (missing operator) in query expression 'CustID DECS LIMIT 1'.]
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) +1003520
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +255
   System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +188
   System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +58
   System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +161
   System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +116
   System.Data.OleDb.OleDbCommand.ExecuteReader() +6
   Checkout_Confirm.Page_Load(Object sender, EventArgs e) in C:\Users\Steven\Desktop\DWTBackUp\project\Checkout_Confirm.aspx.vb:18
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +50
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627

Version Information: Microsoft .NET Framework Version:2.0.50727.3074; ASP.NET Version:2.0.50727.3074

 

can anyone help please?

All replies (5)

Sunday, May 17, 2009 3:38 PM ✅Answered

try changing DECS to DESC

**16  **         sql = "SELECT CustID FROM Customers ORDER BY CustID DESC LIMIT 1"

Also, i'm not sure if access supports LIMIT, but it does support TOP

Sunday, May 17, 2009 3:50 PM ✅Answered

Also, i'm not sure if access supports LIMIT, but it does support TOP

Correct, so the syntax should be:

SELECT TOP 1 CustID FROM Customers ORDER BY CustID DESC

Sunday, May 17, 2009 4:02 PM ✅Answered

Line 11 can de deleted

Line 19: Dim CustID as Integer = dbcomm.ExecuteScalar

Line 24: Replace dbread by CustID

Sunday, May 17, 2009 3:55 PM

 I changed my code and now i dont get any errors but the information doesn't not update in the database, my code is as follows -

 

1    Imports System.Data.OleDb
2    Imports System.Data
3    Partial Class Checkout_Confirm
4        Inherits System.Web.UI.Page
5    
6        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
7    
8            Dim dbconn As New OleDbConnection
9            Dim sql As String
10           Dim dbcomm As New OleDbCommand
11           Dim dbread As OleDbDataReader
12   
13   
14           dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("App_Data\DWTProject.mdb"))
15           dbconn.Open()
16   
17           sql = "Select Top 1 Custid from Customers ORDER BY Custid DESC"
18           dbcomm = New OleDbCommand(sql, dbconn)
19           dbread = dbcomm.ExecuteReader()
20   
21           dbconn.Close()
22   
23           dbcomm = New OleDbCommand("UPDATE Orders SET CustID = @CustID, IsComplete = True WHERE OrderID = @OrderID", dbconn)
24           dbcomm.Parameters.AddWithValue("CustID", dbread)
25           dbcomm.Parameters.AddWithValue("OrderID", Session.SessionID)
26   
27           Try
28               dbconn.Open()
29               dbcomm.ExecuteNonQuery()
30               dbread.Close()
31               dbconn.Close()
32           Catch ex As InvalidOperationException
33   
34           Catch ex As Exception
35   
36           Finally
37               If (Not dbconn.State = ConnectionState.Closed) Then
38                   dbconn.Close()
39               End If
40           End Try
41   
42   
43       End Sub
44   End Class

  

Sunday, May 17, 2009 4:06 PM

 Thanks very much, it worked perfectly now! Cheers! [:D]

  • Last updated on