Avatar of anthonytr
anthonytr
Flag for United Kingdom of Great Britain and Northern Ireland asked on

VB.net equivalent to VBA coder

Hi,

I have the following code in an Access database:

Me.batch_number = 1 + Nz(DMax("batch_number", "tbl_batches", "customer_id = '" & Me.customer_id & "'"), 0)
Me.create_date = Date
Me.batch_reference = Me.customer_id & "-" & Me.batch_number
Me.cd_reference = "CD" & Me.customer_id & "-" & Me.batch_number

Open in new window


Which checks the tbl_batches table against a Customer ID to see of they have a current Batch Number in the system.  If they do it adds one to this figure or returns 1 if the customer does not have any batches.

I'm now in the process of converting this database to VB.NET and I'm having issues trying to do the same thing.

I currently have:

        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Try
            con.ConnectionString = My.Settings.TestValue()
            con.Open()
            cmd.Connection = con

            txtBatchNumber = 1 + Nz(DMax("batch_number", "tbl_batches", "customer_id = '" & customer_id.text & "'"), 0)

            Dim customer_id, batch_number, batch_reference, create_date As String

            customer_id = txtCustomerNumber.Text
            batch_number = txtBatchNumber.Text
            batch_reference = txtBatchReference.Text
            create_date = Date.Today

            cmd.CommandText = "INSERT INTO tbl_batches([customer_id], [batch_number], [batch_reference], [create_date]) VALUES('" & customer_id & "', '" & batch_number & "', '" & batch_reference & "', '" & create_date & "')"
            cmd.ExecuteNonQuery()

        Catch ex As Exception
            MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
        Finally
            con.Close()
        End Try

    End Sub

Open in new window


However the  Nz(DMax is obviously not a VB.NET expression

Hope someone can help.
Visual Basic.NET.NET ProgrammingProgramming

Avatar of undefined
Last Comment
anthonytr

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
David Johnson, CD

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Jacques Bourgeois (James Burger)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
anthonytr

ASKER
Looks like it will be the stored procedure route for me then.
anthonytr

ASKER
I've requested that this question be closed as follows:

Accepted answer: 0 points for anthonytr's comment #a40053172
Assisted answer: 250 points for ve3ofa's comment #a40052459
Assisted answer: 250 points for JamesBurger's comment #a40052854

for the following reason:

.
anthonytr

ASKER
.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes