Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Question Unanswered: Asp Access Autonumber Problem.

    Hi guys,

    I'm not even sure if this is the right section to be posting in but I'm at the end of my tether here. I don't understand what i've done here basically because it involves snatching code from all around the internet but it works so far...

    I now need to retrieve the Autonumber (EventID) of a newly entered record but everything I've tried has either failed, or I've completly missed the plot.

    One problem is that everything seems to say SET, where I get an error saying SET or LET is no longer valid.

    Can anyone modify the below, of at least give me a very basic snipet to extract the autonumber of a newly created record?

    My current jumble of code is below....


    <?xml version="1.0" encoding="iso-8859-1"?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <%@ Page Language="VB" Debug="true"%>
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.OleDb" %>

    <script language="VB" runat="server">

    Dim gConnString as string = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath(".") & "\Course Evaluations_Be.mdb; Jet OLEDBatabase Password=Learning;"

    Sub Page_Load ()
    If Not IsPostBack Then

    Drp_TrainingCourse.DataSource = LoadCourseTitleData()
    Drp_TrainingCourse.DataTextField = "CourseTitle"
    Drp_TrainingCourse.DataValueField = "CourseID"
    Drp_TrainingCourse.DataBind()

    Drp_VenueTitle.DataSource = LoadVenueTitleData()
    Drp_VenueTitle.DataTextField = "VenueTitle"
    Drp_VenueTitle.DataValueField = "VenueID"
    Drp_VenueTitle.DataBind()

    Else

    pnlForm.Visible = "False"
    pnlConfirm.Visible = "True"

    Dim conn as new OleDbconnection
    conn.connectionstring = gConnString

    Dim strSQL as string
    strSQL = "INSERT INTO Tbl_Events (CourseDate, CourseID, VenueID, CourseEntryBy) Values (@Date, @CourseID, @VenueID, @EntryBy);"

    Dim dbComm as New OleDbCommand
    dbComm.CommandText = strSQL
    dbComm.Connection = Conn

    Dim paramDate as New OleDBParameter
    paramDate.ParameterName = "@Date"
    paramDate.Value = Val_Date.text
    paramDate.DbType = DbType.date
    dbComm.Parameters.Add(paramDate)

    Dim paramTrainingCourse as New OleDBParameter
    paramTrainingCourse.ParameterName = "@CourseID"
    paramTrainingCourse.Value = Drp_TrainingCourse.SelectedValue
    paramTrainingCourse.DbType = DbType.string
    dbComm.Parameters.Add(paramTrainingCourse)

    Dim paramVenueTitle as New OleDBParameter
    paramVenueTitle.ParameterName = "@VenueID"
    paramVenueTitle.Value = Drp_VenueTitle.SelectedValue
    paramVenueTitle.DbType = DbType.String
    dbComm.Parameters.Add(paramVenueTitle)

    Dim paramEntryBy as New OleDBParameter
    paramEntryBy.ParameterName = "@EntryBy"
    paramEntryBy.Value = Val_EntryBy.text
    paramEntryBy.DbType = DbType.String
    dbComm.Parameters.Add(paramEntryBy)

    Conn.open
    dbComm.ExecuteNonQuery()

    conn.Close

    dbcomm = nothing
    conn = Nothing
    strSQL = nothing

    End If

    End Sub

    Private Function LoadCourseTitleData() As OleDbDataReader
    Dim strSQLCT As String

    strSQLCT = "SELECT Tbl_Detail_TrainingCourse.CourseID, Tbl_Detail_TrainingCourse.CourseTitle, Tbl_Detail_TrainingCourse.CourseObsolete FROM Tbl_Detail_TrainingCourse WHERE (((Tbl_Detail_TrainingCourse.CourseObsolete)=0)) ORDER BY Tbl_Detail_TrainingCourse.CourseTitle;"

    Dim dbConn As New OleDbConnection
    dbConn.ConnectionString = gConnString

    Dim dbComm As New OleDbCommand
    dbComm.Commandtext = strSqlCT
    dbComm.Connection = dbConn

    dbConn.Open
    dbComm.ExecuteNonQuery()

    Dim result As OleDbDataReader = dbComm.ExecuteReader(CommandBehavior.CloseConnecti on)

    Return result
    End function

    Private Function LoadVenueTitleData() As OleDbDataReader
    Dim strSQLVT As String

    strSQLVT = "SELECT Tbl_Detail_TrainingVenue.VenueID, Tbl_Detail_TrainingVenue.VenueTitle, Tbl_Detail_TrainingVenue.VenueObsolete FROM Tbl_Detail_TrainingVenue WHERE (((Tbl_Detail_TrainingVenue.VenueObsolete)=0)) ORDER BY Tbl_Detail_TrainingVenue.VenueTitle;"

    Dim dbConn As New OleDbConnection
    dbConn.ConnectionString = gConnString

    Dim dbComm As New OleDbCommand
    dbComm.Commandtext = strSqlVT
    dbComm.Connection = dbConn

    dbConn.Open
    dbComm.ExecuteNonQuery()

    Dim result As OleDbDataReader = dbComm.ExecuteReader(CommandBehavior.CloseConnecti on)

    Return result

    End function

    </script>

  2. #2
    Join Date
    Jan 2008
    Posts
    6
    You can get the AutoNumber value by executing SELECT @@Identity on the database, so you should add code after your code that insert the record that looks something like this:

    Dim dbComm as New OleDbCommand
    dbComm.CommandText = "SELECT @@Identity"
    dbComm.Connection = Conn
    Dim eventID as Integer = dbComm.ExecuteScalar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •