troubleshooting Question

VB.net equivalent to VBA coder

Avatar of anthonytr
anthonytrFlag for United Kingdom of Great Britain and Northern Ireland asked on
Programming.NET ProgrammingVisual Basic.NET
5 Comments2 Solutions936 ViewsLast Modified:
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

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

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

Hope someone can help.
ASKER CERTIFIED SOLUTION
David Johnson, CD
The More I know, the more I don't know
Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros