[RESOLVED] Updated database, now need to re-read?

[Kielo]

I've updated my database...

And all the information goes in fine, and an Auto-Number is allocated.

Do I need to re-read the database to find & display this auto-number?

Where do I begin?

[gautamshaw]

show us your current code pls

[GaryMazzone]

You can query the database (on the same open connection for the Select @@Identity)

[Kielo]

show us your current code pls

Private Sub myAddNew(ByVal CusRef As Integer)
        Dim ConnectionString As String
        Dim SQLString As String
        Dim glassOrPlastic As String = String.Empty
        Dim scratchCoating As String = String.Empty
        Dim uvFilter As String = String.Empty
        Dim whichButtonDialogResult As DialogResult
        Dim dbCommand As System.Data.OleDb.OleDbCommand
        Dim Connection As System.Data.OleDb.OleDbConnection
 
        If GlassRadioButton.Checked = True Then
            glassOrPlastic = "Glass"
        ElseIf PlasticRadioButton.Checked = True Then
            glassOrPlastic = "Plastic"
        End If
 
        If CheckBox1.Checked = True Then
            scratchCoating = "Yes"
        Else
            scratchCoating = "No"
        End If
 
        If CheckBox2.Checked = True Then
            uvFilter = "Yes"
        Else
            uvFilter = "No"
        End If
 
        ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
        ConnectionString += "Source=" & "Opticians.accdb "
        Connection = New System.Data.OleDb.OleDbConnection(ConnectionString)
        SQLString = "INSERT INTO SpecSalesTable (CustomerID, EyeTestID, FrameID, DateOfSale, GlassOrPlastic, ScratchCoating, UVFilter, TotalCost, DepositPaid) "
        SQLString += "Values ('" & CustomerIDTextBox.Text & "','" & EyeTestIDTextBox.Text & "','" & StockTextBox.Text & "',#" & Date.Today & "#,'" & glassOrPlastic & "','" & scratchCoating & "','" & uvFilter & "','" & TextBox1.Text & "','" & TextBox2.Text & "')"
        whichButtonDialogResult = MessageBox.Show("Are You Sure You Want To Place This Order?", "Add Record", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If whichButtonDialogResult = DialogResult.Yes Then
            Connection.Open()
            If CBool(ConnectionState.Open) Then
                dbCommand = New System.Data.OleDb.OleDbCommand(SQLString, Connection)
                Try
                    dbCommand.ExecuteNonQuery()
                    MessageBox.Show("Order placed, SpecSalesTable Updated! ")
                Catch ex As Exception
                    MessageBox.Show(" Error placing order... ")    'IF ERROR DISPLAYS MESSAGE
                End Try
 
            End If
 
        End If
 
        Connection.Close()
    End Sub

Gary, where does the Select Identity go?

I suppose I'd use Select Max ID? (to find the most recent)

[NickThissen]

No, just use the sql query

select @@identity

as 'ExecuteScalar' and it should return the last autonumber ID inserted.

[GaryMazzone]

after this line:

dbCommand.ExecuteNonQuery()

add

SQLString = "Select @@IDentity"
cmd.Text = SQLString
Dim pkInserted as Integer = cmd.ExecuteScaler()

[NickThissen]

Pretty sure it's ExecuteScalar Gary
I see Scaler in many places, but it's definitely scalar, as in 'one value'.

[Kielo]

What do I declare "cmd" as?

[GaryMazzone]

use your dbCommand

[Kielo]

Error 1 'Text' is not a member of 'System.Data.OleDb.OleDbCommand'.

[Kielo]

Do I need to change something there?

cmd.Text = SQLString

Error 1 'Text' is not a member of 'System.Data.OleDb.OleDbCommand'.

[GaryMazzone]

I did not enter this in the ide just typed here.

Look at the commad objects properties one of them will be the text you want to run.


.CommandType = CommandType.Text
.CommandText = SQLString

[Kielo]

Very sorry, completely lost on how to remedy it? Don't really understand it

[GaryMazzone]

Try
     dbCommand.ExecuteNonQuery()
     MessageBox.Show("Order placed, SpecSalesTable Updated! ")
     SQLString = "SELECT @@IDENTITY"
     dbCommand.CommandType =  CommandType.Text
     dbCommand.CommandText = SQLString
     Dim PKLastInserted As Integer = dbCommand.ExecuteScaler()
     MessageBox.Show("Last inserted Identity is: " & PKLastInserted.ToString())
Catch ex As Exception
     MessageBox.Show(" Error placing order... ")    'IF ERROR DISPLAYS MESSAGE
End Try

[Kielo]

Works perfect, just incase anyone else is checking here,

ExecuteScaler needs to be ExecuteScalar

Thanks gary, once again

[GaryMazzone]

Comments of what is going on in the code

Try
    dbCommand.ExecuteNonQuery()
    MessageBox.Show("Order placed, SpecSalesTable Updated! ")
    'Reset the SQL Command I am going to run here
    SQLString = "SELECT @@IDENTITY"
    'The Select @@IDENTITY will return the last inserted identity value on the current command/connection object so we need to run it using the current command 
    'Ensure that the command object (dbCommand) command type is set to text
    dbCommand.CommandType = CommandType.Text
    'Assign the new SQLString value to the command objects text (what will be run)
    dbCommand.CommandText = SQLString
    'Declare a var as type Integer and set its value to the return of ExecuteScaler()
    Dim PKLastInserted As Integer = dbCommand.ExecuteScalar()
    MessageBox.Show("Last inserted Identity is: " & PKLastInserted.ToString())
Catch ex As Exception
    MessageBox.Show(" Error placing order... ")    'IF ERROR DISPLAYS MESSAGE
End Try