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
con.ConnectionString = My.Settings.TestValue()
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 & "')"
Catch ex As Exception
MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
However the Nz(DMax is obviously not a VB.NET expression
Hope someone can help.