How to pull the last row inserted in a database

to get the AutoNum that it incremented to

Page 1 of 1

13 Replies - 9139 Views - Last Post: 10 November 2008 - 01:19 PM Rate Topic: -----

#1 4x4pirate  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 54
  • Joined: 18-October 06

How to pull the last row inserted in a database

Post icon  Posted 09 November 2008 - 11:52 PM

I am baffled..

I am just trying to get the CustomerID in the last row that I created in my table.

Obviously I cannot get it when it is in use, so how can I go about getting it after the fact?


I have searched like mad for the answer and I am not having any luck.


I am working with two tables.. CustomerTbl and ReservationsTBL

I need the CustomerID in the ReservationsTBL to match the CustomerID in the CustomerTBL.

Any help would be great.

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
		Try '--- Throws data into the customer database ---------------------------------------------------------------------
			Me.CustomerTBLTableAdapter.Insert(Me.txtFName.Text.ToUpper, Me.txtLName.Text.ToUpper, _
											  Me.txtAddress1.Text.ToUpper, txtAddress2.Text.ToUpper, _
											  Me.txtCity.Text.ToUpper, txtSelectState.Text.ToUpper, _
											  Me.txtPhone.Text, txtEmail.Text.ToUpper, txtZip.Text)
			Me.CustomerTBLTableAdapter.Fill(CustomersDatabaseDataSet.CustomerTBL)
			'---------------------------------------------------------------------------------------------------------------

			
'The code would go in here after I made the insert into the database.. 

I can't get the code below to work unless I can grab the auto number from the last insert.

 



			'---------------------------------------------------------------------------------------------------------------
			'Me.ReservationsTBLTableAdapter.Insert(CDate(DateTimeCheckInPicker.ToString), (CDate(DateOUT.ToString)), CInt(g_RoomNumber.ToString), g_RoomType.ToString, (CInt(strNumberofnights.ToString)), (CDec(dblReservationTotal.ToString("C"))), txtCreditCard.Text, Me.cboCardType.Text, CDate(txtCardExpDate.ToString)), 
			'Me.ReservationsTBLTableAdapter.Fill(CustomersDatabaseDataSet.ReservationsTBL)
		Catch ex As Exception
			MessageBox.Show(ex.Message, "Data input error")
		End Try
	End Sub
End Class

This post has been edited by 4x4pirate: 09 November 2008 - 11:53 PM


Is This A Good Question/Topic? 0
  • +

Replies To: How to pull the last row inserted in a database

#2 kasbaba  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 133
  • Joined: 03-November 08

Re: How to pull the last row inserted in a database

Posted 10 November 2008 - 06:58 AM

Hi,

If you have an autonumber field (CustomerID) in the table ReservationTbl, the easiest method would be to Descending sort the CustomerID field and get the first record.

Here is the code. You may need add reference to Oledb

Dim Rs1 As OleDb.OleDbDataReader
Dim SqlComm1 As OleDb.OleDbCommand
Dim CON As New OleDbConnection
Dim CustomerIDfromtheTbl

CON = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=yourdatabase.mdb;")
CON.open()

SqlComm1 = CON.CreateCommand
SqlComm1.CommandText = "SELECT CustomerID from ReservationsTBL Order By CustomerID DESC"
Rs1 = SqlComm1.ExecuteReader
	if Rs1.Read = True then
		CustomerIDfromtheTbl = Rs1(0)
	end if
Rs1.close
SqlComm1.close
CON.close

Msgbox(CustomerIDfromtheTbl)


hope this helps...

kasbaba

This post has been edited by kasbaba: 10 November 2008 - 07:47 AM

Was This Post Helpful? 0
  • +
  • -

#3 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1639
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: How to pull the last row inserted in a database

Posted 10 November 2008 - 08:11 AM

Another way, and I believe MAX is supported in Access,. is to do something like

SqlComm1.CommandText = "SELECT MAX(CustomerID) from ReservationsTBL "


Was This Post Helpful? 1
  • +
  • -

#4 4x4pirate  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 54
  • Joined: 18-October 06

Re: How to pull the last row inserted in a database

Posted 10 November 2008 - 08:22 AM

View PostPsychoCoder, on 10 Nov, 2008 - 07:11 AM, said:

Another way, and I believe MAX is supported in Access,. is to do something like

SqlComm1.CommandText = "SELECT MAX(CustomerID) from ReservationsTBL "




Thank you both!!!

One problem.. getting an error:


Throws:
Option Strict ON requires all variable declarations to have an 'As" clause.
Looking at: Dim CustomerIDfromtheTbl



'Close; is not a member of 'System.Data.OleDb.OleDbCommand'
Looking at: SqlComm1.close()

FIXED the SqlComm1.Close()
SqlComm1.close() (FIXED: I believe it needed to be: SqlComm1.Connection.Close()


(I remind you I am a newb)

Is this: Dim CustomerIDfromtheTbl supposed to be: Dim CustomerIDfromtheTbl As new CustomerTBL ???

Thanks again for all your help!

I have attached my code in a zip file

And I am working in the frmReservations form under the Button control to submit reservation

Attached File(s)


This post has been edited by 4x4pirate: 10 November 2008 - 08:40 AM

Was This Post Helpful? 0
  • +
  • -

#5 magicmonkey  Icon User is offline

  • D.I.C Regular

Reputation: 106
  • View blog
  • Posts: 484
  • Joined: 12-September 08

Re: How to pull the last row inserted in a database

Posted 10 November 2008 - 08:45 AM

In sql if you use a proc I add an output parameter and set it to SET @NEW_ID = SCOPE_IDENTITY(), if you are passing SQL from your client then tag it to the end of the insert statement insert into table(x,x) value ('x','x');select scope_identity();.

If it is access then you are not so lucky as it does not support output parms, nor multiple sql statements, so here is a link to MSDN. In that link it shows you how to use the OnRowUpdated event to send out a SELECT @@IDENTITY to get the new autonumber from access and update the inserted row.
Was This Post Helpful? 0
  • +
  • -

#6 kasbaba  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 133
  • Joined: 03-November 08

Re: How to pull the last row inserted in a database

Posted 10 November 2008 - 08:57 AM

View Post4x4pirate, on 10 Nov, 2008 - 07:22 AM, said:

Thank you both!!!
One problem.. getting an error:
Throws:
Option Strict ON requires all variable declarations to have an 'As" clause.
Looking at: Dim CustomerIDfromtheTbl
...



Hey,

try this :

Dim CustomerIDfromtheTbl as Double



hope this helps!

kasbaba
Was This Post Helpful? 0
  • +
  • -

#7 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1639
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: How to pull the last row inserted in a database

Posted 10 November 2008 - 09:31 AM

You have to declare that variable as something, since it's holding the ID from your table declare it as an Integer

Dim CustomerIDfromtheTbl As Integer



Hope that helps :)
Was This Post Helpful? 0
  • +
  • -

#8 4x4pirate  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 54
  • Joined: 18-October 06

Re: How to pull the last row inserted in a database

Posted 10 November 2008 - 09:50 AM

Yes it helped.. but I am now getting:

CON = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=CustomersDatabase.mdf;")
CON.Open()



CON.Open() "File already in use"

.. Man I am SOOOO CLOSE!
Was This Post Helpful? 0
  • +
  • -

#9 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1639
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: How to pull the last row inserted in a database

Posted 10 November 2008 - 09:52 AM

You are getting that because that MDF file is open either by you or another process. Do you have the file open (say in Access), and are you closing it when you are done (CON.Close())?
Was This Post Helpful? 0
  • +
  • -

#10 4x4pirate  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 54
  • Joined: 18-October 06

Re: How to pull the last row inserted in a database

Posted 10 November 2008 - 10:02 AM

View PostPsychoCoder, on 10 Nov, 2008 - 08:52 AM, said:

You are getting that because that MDF file is open either by you or another process. Do you have the file open (say in Access), and are you closing it when you are done (CON.Close())?



It's open because the program opens it to throw the customer in the customers table

How can I close it so I can re-open it or is there a better way?


CustomerEntryForm:
Public Class Frm2CustomerEntry

	Public strFName As String
	Public strLName As String
	Public strAddress1 As String
	Public strAddress2 As String
	Public strCity As String
	Public strState As String
	Public strZip As String
	Public strEmail As String
	Public strPhone As String

	Private Sub Frm2CustomerEntry_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		'TODO: This line of code loads data into the 'CustomersDatabaseDataSet.CustomerTBL' table. You can move, or remove it, as needed.
		Me.CustomerTBLTableAdapter.Fill(Me.CustomersDatabaseDataSet.CustomerTBL)

	End Sub

	Private Sub btnCreateReservation_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateReservation.Click

		Try '--- Throws data into the customer database ---------------------------------------------------------------------
			Me.CustomerTBLTableAdapter.Insert(Me.txtFName.Text.ToUpper, Me.txtLName.Text.ToUpper, _
											  Me.txtAddress1.Text.ToUpper, txtAddress2.Text.ToUpper, _
											  Me.txtCity.Text.ToUpper, cboSelectState.Text.ToUpper, _
											  Me.txtPhone.Text, txtEmail.Text.ToUpper, txtZip.Text)
			Me.CustomerTBLTableAdapter.Fill(CustomersDatabaseDataSet.CustomerTBL)
			'---------------------------------------------------------------------------------------------------------------

			'---------------------------------------------------------------------------------------------------------------
		Catch ex As Exception
			MessageBox.Show(ex.Message, "Data input error")
		End Try

		frm4Reservations.Show()
		frm4Reservations.txtFName.Text = Me.txtFName.Text
		frm4Reservations.txtLName.Text = Me.txtLName.Text
		frm4Reservations.txtAddress1.Text = Me.txtAddress1.Text
		frm4Reservations.txtAddress2.Text = Me.txtAddress2.Text
		frm4Reservations.txtCity.Text = Me.txtCity.Text
		frm4Reservations.txtSelectState.Text = Me.cboSelectState.Text
		frm4Reservations.txtZip.Text = Me.txtZip.Text
		frm4Reservations.txtPhone.Text = Me.txtPhone.Text
		frm4Reservations.txtEmail.Text = Me.txtEmail.Text
	End Sub

	Private Sub btnExitNOSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExitNOSave.Click
		Me.Close()
		Frm1Main.Show()
	End Sub

	Private Sub btnClearFields_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClearFields.Click
		Me.txtFName.Clear()
		Me.txtLName.Clear()
		Me.txtAddress1.Clear()
		Me.txtAddress2.Clear()
		Me.txtCity.Clear()
		Me.cboSelectState.SelectedIndex = 0
		Me.txtZip.Clear()
		Me.txtPhone.Clear()
		Me.txtEmail.Clear()
	End Sub

	Private Sub btnSaveCust_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

	End Sub
	Private Sub txtZip_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles txtZip.Validating
		' validate zip is numeric
		If Not IsPositive(txtZip.Text) Then
			' Display error message
			MessageBox.Show("Zipcode must be 5 digit numerical number", "ERROR Invalid ZipCode")
			txtZip.SelectAll()
			txtZip.Focus()
			e.Cancel = True
		Else
			e.Cancel = False
		End If
		txtZip.Focus()

   
	End Sub

	Private Sub txtPhone_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles txtPhone.Validating
		' validate zip is numeric
		If Not IsPositive(txtPhone.Text) Then
			' Display error message
			MessageBox.Show("Zipcode must be 5 digit numerical number", "ERROR Invalid ZipCode")
			txtPhone.SelectAll()
			txtPhone.Focus()
			e.Cancel = True
		Else
			e.Cancel = False
		End If
	End Sub
End Class




ReservationForm

Imports DELANEY_INN.CustomersDatabaseDataSet
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.IO
Imports System.Data

Public Class frm4Reservations

	Public strFName As String
	Public strLName As String
	Public strAddress1 As String
	Public strAddress2 As String
	Public strCity As String
	Public strState As String
	Public strZip As String
	Public strEmail As String
	Public strPhone As String
	Public dblNightlyCharge As Double
	Public dblRoomCharge As Double
	Public strNumberofnights As String
	Public strRadioButtonChoice As String
	Dim intRoomStatus As Integer
	Dim intNumberofNights As Integer
	Dim dblReservationSubTotal As Double
	Dim dblTaxes As Double
	Dim dteNewDate As Date
	Public dblReservationTotal As Double


	Private Sub rad1double_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rad1double.CheckedChanged
		dblRoomCharge = 55.0
		g_RoomType = 1
		g_RoomID = "1-Double"
		g_RoomNumber = 100
	End Sub

	Private Sub rad2doubles_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rad2doubles.CheckedChanged
		dblRoomCharge = 70.0
		g_RoomType = 2
		g_RoomID = "2-Doubles"
		g_RoomNumber = 200
	End Sub

	Private Sub rad1Queen_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rad1Queen.CheckedChanged
		dblRoomCharge = 75.0
		g_RoomType = 3
		g_RoomID = "1-Queen"
		g_RoomNumber = 300
	End Sub

	Private Sub rad2Queens_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rad2Queens.CheckedChanged
		dblRoomCharge = 99.0
		g_RoomType = 4
		g_RoomID = "2-Queens"
		g_RoomNumber = 300
	End Sub

	Private Sub frm4Reservations_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		'TODO: This line of code loads data into the 'CustomersDatabaseDataSet.ReservationsTBL' table. You can move, or remove it, as needed.
		Me.ReservationsTBLTableAdapter.Fill(Me.CustomersDatabaseDataSet.ReservationsTBL)
		txtDate.Text = DateString
		dtmSystemDate = Today
		DateTimeCheckInPicker.MinDate = Today			   'On page load, sets the Minimum date to select  to Today
		DateTimeCheckInPicker.MaxDate = Today.AddDays(552)  'Sets the max calendar to 1.5 years into the future
		DateTimeCheckOutPicker.MinDate = Today.AddDays(1)   'On page load, sets the Minimum date to select  to Today + 1
		DateTimeCheckOutPicker.MaxDate = Today.AddDays(552) 'Sets the max calendar to 1.5 years into the future
		DateIN = DateTimeCheckInPicker.Value				'
	End Sub

	Private Sub DateTimeCheckInPicker_ValueChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimeCheckInPicker.ValueChanged
		'**************** CHECK IN *********************************************************************************************************************************
		If DateTimeCheckInPicker.Value < Today Then									 ' This code sets the the calendars
			MessageBox.Show("Check-IN Date is earlier than Today" & _
							("Please change the Check-In Time to Today or later"))	  ' To accept only a max of 1.5 years
		ElseIf DateTimeCheckInPicker.Value = Today.AddDays(553) Then					' in advance of the current date
			MessageBox.Show("Maximum of 1.5 years reservation allowance," & _
							("Please make sure date is less than 1.5 years from Today")) ' Since I changed the calendar after I wrote the code 
		Else																			 ' to look at the DatePicker calendar with minimum's and maximum's
			DateIN = DateTimeCheckInPicker.Value			' You should never see this error. 
			DateTimeCheckOutPicker.MinDate = DateTimeCheckInPicker.Value.AddDays(1)
			DateTimeCheckOutPicker.MaxDate = DateTimeCheckInPicker.Value.AddDays(552)
		End If																		   '
		'************************************************************************************************************************************************************
		DateTimeCheckInPicker.Text = DateIN.ToString
		DateTimeCheckOutPicker.Value = DateTimeCheckInPicker.Value.AddDays(1) 'Changes the DatePicker for Checked Out +1 day ahead of checked in
		DateOUT = DateTimeCheckOutPicker.Value
		DateTimeCheckOutPicker.Text = DateTimeCheckOutPicker.Value.ToString
		'************************************************************************************************************************************************************
		rad1double.Checked = False													  ' This code was added to erase the calculation totals and
		rad1Queen.Checked = False													   ' text fields if someone was to change the check-in and check-out dates
		rad2doubles.Checked = False													 ' without formally resetting the values by pusing the button
		rad2Queens.Checked = False													  '
		dblNightlyCharge = 0															'
		dblReservationSubTotal = 0													  '
		dblReservationTotal = 0														 '
		dblRoomCharge = 0															   '
		g_RoomType = 0
		g_RoomNumber = 0
		lblChargesPerNight.Text = ("")												  '
		lblNumberofNights.Text = ("")												   '
		lblReservationSubTotal.Text = ("")											  '
		lblTaxes.Text = ("")															'
		lblReservationTotal.Text = ("")												 '
		'************************************************************************************************************************************************************
	End Sub

	Private Sub DateTimeCheckOutPicker_ValueChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimeCheckOutPicker.ValueChanged
		'**************** CHECK OUT *************************************
		DateTimeCheckOutPicker.MinDate = DateTimeCheckInPicker.Value
		DateTimeCheckOutPicker.MaxDate = Today.AddDays(552) 'Reservation up to 1.5 years from current date
		DateOUT = DateTimeCheckOutPicker.Value

		If DateTimeCheckOutPicker.Value = Today.AddDays(553) Then
			MessageBox.Show("Maximum of 1.5 years reservation allowance," & _
							("Please make sure date is less than 1.5 years from Today"))
			DateOUT = Today
			DateTimeCheckOutPicker.Text = DateIN.ToString

		End If
		If DateOUT < Today Then
			MessageBox.Show("Minimum of 1 day beyond Check-In date is required" & _
							("Please change the Check-Out Time"))
			DateIN = Today.AddDays(1) ' if todays date is greater, this sets date out to date in + 1 day
			DateTimeCheckOutPicker.Text = DateOUT.ToString

		Else
			DateOUT = DateTimeCheckOutPicker.Value
		End If

		'************************************************************************************************************************************************************
		rad1double.Checked = False													  ' This code was added to erase the calculation totals and
		rad1Queen.Checked = False													   ' text fields if someone was to change the check-in and check-out dates
		rad2doubles.Checked = False													 ' without formally resetting the values by pusing the button
		rad2Queens.Checked = False													  '
		dblNightlyCharge = 0															'
		dblReservationSubTotal = 0													  '
		dblReservationTotal = 0														 '
		dblRoomCharge = 0															   '
		g_RoomType = 0
		g_RoomNumber = 0
		lblChargesPerNight.Text = ("")												  '
		lblNumberofNights.Text = ("")												   '
		lblReservationSubTotal.Text = ("")											  '
		lblTaxes.Text = ("")															'
		lblReservationTotal.Text = ("")												 '
		'************************************************************************************************************************************************************

	End Sub

	'****************** CALCULATE BUTTON **********************************
	Private Sub btnCalculateStay_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCalculateStay.Click
		'*************************************************
		strNumberofnights = CStr(DateOUT.Subtract(DateIN).Days) 'compares two dates and subracts the days
		'************************************************

		If g_RoomType = 0 Then
			MessageBox.Show("Please select a Room")
		End If

		'Dim intNumberofNights As Integer
		'If CDbl(strNumberofnights) = 0 Then
		'strNumberofnights = CStr(CDbl(strNumberofnights) + 1)
		'lblNumberofNights.Text = strNumberofnights.ToString
		'Else
		'lblNumberofNights.Text = strNumberofnights.ToString 'Places "Days" calculation on form
		'End If

		lblNumberofNights.Text = strNumberofnights.ToString
		intNumberofNights = CInt(strNumberofnights)
		lblChargesPerNight.Text = dblRoomCharge.ToString("C")
		'Calculate Stay **********************************************
		dblReservationSubTotal = dblRoomCharge * intNumberofNights
		dblTaxes = dblReservationSubTotal * g_tax
		dblReservationTotal = dblReservationSubTotal + dblTaxes
		'************************************************************
		lblReservationSubTotal.Text = dblReservationSubTotal.ToString("C") ' places subtotal on the form
		lblReservationTotal.Text = dblReservationTotal.ToString("C") ' places total on the form
		lblTaxes.Text = dblTaxes.ToString("C") ' Places tax amount from g_tax on the form




	End Sub



	Private Sub btnResetReservDetails_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnResetReservDetails.Click
		'*********** RESET BUTTON ********************************************
		'Makes sure the following are cleared if the date is changed
		rad1double.Checked = False
		rad1Queen.Checked = False
		rad2doubles.Checked = False
		rad2Queens.Checked = False
		dblNightlyCharge = 0
		dblReservationSubTotal = 0
		dblReservationTotal = 0
		dblRoomCharge = 0
		g_RoomType = 0
		lblChargesPerNight.Text = ("")
		lblNumberofNights.Text = ("")
		lblReservationSubTotal.Text = ("")
		lblTaxes.Text = ("")
		lblReservationTotal.Text = ("")
		dtmSystemDate = Today
		txtDate.Text = DateString


		DateTimeCheckInPicker.MaxDate = Today.AddDays(552)  'Sets the max calendar to 1.5 years into the future
		DateTimeCheckInPicker.Value = Today
		DateTimeCheckInPicker.Text = DateTimeCheckInPicker.Value.ToString
		'----------------------------------------------------------------------------------------------------------------
		'**********************************************************************************************

	End Sub

	Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

		Try
			Me.CustomerTBLTableAdapter.Insert(Me.txtFName.Text.ToUpper, Me.txtLName.Text.ToUpper, Me.txtAddress1.Text.ToUpper, txtAddress2.Text.ToUpper, txtCity.Text.ToUpper, txtSelectState.Text.ToUpper, txtPhone.Text, txtEmail.Text.ToUpper, txtZip.Text)
			Me.CustomerTBLTableAdapter.Fill(CustomersDatabaseDataSet.CustomerTBL)

		Catch ex As Exception
			MessageBox.Show(ex.Message, "Data input error")
		End Try
	End Sub

	Private Sub SearchByLastNameToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
		Try
			Me.CustomerTBLTableAdapter.SearchByLastName(Me.CustomersDatabaseDataSet.CustomerTBL)
		Catch ex As System.Exception
			System.Windows.Forms.MessageBox.Show(ex.Message)
		End Try

	End Sub

	Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
		'Dim strCustomerID As String

		Dim Rs1 As OleDb.OleDbDataReader
		Dim SqlComm1 As OleDb.OleDbCommand
		Dim CON As New OleDbConnection
		Dim CustomerIDfromtheTbl As Integer

		CON = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=CustomersDatabase.mdf;")
		CON.Open()

		SqlComm1 = CON.CreateCommand
		SqlComm1.CommandText = "SELECT CustomerID from ReservationsTBL Order By CustomerID DESC"
		Rs1 = SqlComm1.ExecuteReader
		If Rs1.Read = True Then
			CustomerIDfromtheTbl = CInt(Rs1(0))
		End If
		Rs1.Close()
		SqlComm1.Connection.Close()
		CON.Close()
		MsgBox(CustomerIDfromtheTbl)


		Try
			Me.ReservationsTBLTableAdapter.Insert(CDate(DateTimeCheckInPicker.Value.ToString), (CDate(DateTimeCheckOutPicker.Value.ToString)), (CInt(g_RoomID.ToString)), g_RoomType.ToString, (CDec(Me.lblChargesPerNight.ToString)), (CDec(Me.lblReservationTotal.ToString)), txtCreditCard.Text, cboCardType.ToString, (CDate(txtCardExpDate.ToString)), CustomerIDfromtheTbl)
			Me.ReservationsTBLTableAdapter.Fill(CustomersDatabaseDataSet.ReservationsTBL)
		Catch ex As Exception
			MessageBox.Show(ex.Message, "Data input error")
		End Try
	End Sub

End Class

'lblNumberofNights.Text = strNumberofnights.ToString
'	 intNumberofNights = CInt(strNumberofnights)
'	lblChargesPerNight.Text = dblRoomCharge.ToString("C")
'Calculate Stay **********************************************
'dblReservationSubTotal = dblRoomCharge * intNumberofNights
'dblTaxes = dblReservationSubTotal * g_tax
'dblReservationTotal = dblReservationSubTotal + dblTaxes














Was This Post Helpful? 0
  • +
  • -

#11 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1639
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: How to pull the last row inserted in a database

Posted 10 November 2008 - 10:14 AM

A quick and dirty solution to your current problem (since I havent written it and dont have 100% of the code, that and I know little about TableAdapter objects since I've never used them) would to be check the state of the connection before you open it, see if this helps any

CON = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=CustomersDatabase.mdf;")

'Check the status 
If Not CON.State = ConnectionState.Open Then
      CON.Open()
End If



EDIT: Not 100% sure that will work since the error is saying that a process has already got that file open, so we need to find what is hoping your MDF file open and not allowing you to access it
Was This Post Helpful? 0
  • +
  • -

#12 4x4pirate  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 54
  • Joined: 18-October 06

Re: How to pull the last row inserted in a database

Posted 10 November 2008 - 10:18 AM

I will give it a shot...

The only time that I referenced any SQL connection where I OPEN the Mdf is in the code I just got in this thread..

Thanks again for the help.. I will let you know in a sec if it works!
Was This Post Helpful? 0
  • +
  • -

#13 4x4pirate  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 54
  • Joined: 18-October 06

Re: How to pull the last row inserted in a database

Posted 10 November 2008 - 10:31 AM

Nope.. still cant figure out why it says that the file is open, unless it is circlereferencing the fact that I am running Visual Studio.


Or could it possibly be, because I binded each text box in the field to the table adapter?


(I attached the latest version)

Attached File(s)


Was This Post Helpful? 0
  • +
  • -

#14 4x4pirate  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 54
  • Joined: 18-October 06

Re: How to pull the last row inserted in a database

Posted 10 November 2008 - 01:19 PM

So much for my brownie points.. I can't get reservations submit button to write to the database witout throwing the code.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1