Link to home
Start Free TrialLog in
Avatar of anthonytr
anthonytrFlag 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.
ASKER CERTIFIED SOLUTION
Avatar of David Johnson, CD
David Johnson, CD
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of anthonytr

ASKER

Looks like it will be the stored procedure route for me then.
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:

.
.