Reading Records

[paulthepaddy]

Hi Guys, not sure how to put this question so here goes.

I am looking to retrieve records from a database and then create a new value from what was retrieved, so example

I am looking to search a table For order Numbers related to a Car. the Order Number is going to be made up as follows

Car Reg + Date + Char + number
Dim partOrdernumber As String = reg.Replace(" ", "") + Format(D_Date, "ddMMyy") + StrConv(inital, VbStrConv.Uppercase)
CEZ2525210713P1
CEZ2525210713P2

The problem im having is the number if two order numbers are done on the same date same car same person(which is likely) then i need to use the last number to define them

but how would i do this. this is what i have so far

Dim con As SqlClient.SqlConnection = WhatConnection()
        Dim CompleteOrderNumber As String
        Dim partOrdernumber As String = reg.Replace(" ", "") + Format(D_Date, "ddMMyyyy") + StrConv(inital, VbStrConv.Uppercase)
        Dim CMD As New SqlClient.SqlCommand("SELECT * FROM OrderNumbers WHERE OrderNumber LIKE @OrderNumber", con)

        CMD.Parameters.AddWithValue("@OrderNumber", partOrdernumber)

i need to search for 'CEZ2525210713P' and get all results to then determine what the next number is going to be

im really quite lost here so any help is much appropriated

 

[IanRyder]

Hi,

Here is one idea for you to get you started:-

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
  Dim myConn As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
  Dim SampleNumber As String = "CEZ2525210713P%"
 
  Dim sqlQuery = <SQL>
                   SELECT MAX(CAST(SUBSTRING(OrderNumber, 15, LEN(OrderNumber) - 14) AS INT)) AS LastOrderNumber
                   FROM dbo.OrderNumbers
                   WHERE (OrderNumber LIKE @OrderNumber)
                 </SQL>
 
  Dim CMD As New SqlClient.SqlCommand(sqlQuery.Value, myConn)
  Dim returnedNumber As Object = DBNull.Value
  Dim nextOrderNumber As Integer
 
  CMD.Parameters.AddWithValue("@OrderNumber", SampleNumber)
  Try
    myConn.Open()
    returnedNumber = CMD.ExecuteScalar
    myConn.Close()
  Catch ex As Exception
    MsgBox(ex.Message)
  End Try
 
  If IsDBNull(returnedNumber) Then
    nextOrderNumber = 1
  Else
    nextOrderNumber = CInt(returnedNumber) + 1
  End If
 
  MsgBox(nextOrderNumber.ToString)
End Sub

In this example I use SQL Syntax to strip away the first 14 characters of the order number to leave me with the last numbers from the end of the string. This is then converted to an Integer and finally only the Maximum value is returned by the query.

This is then executed using the ExecuteScalar method of the SQLCommand object and stored in a variable of type Object. This is to accommodate the return of NULL values from the Database if NO records match the search criteria. Once done, 1 is added to the returned number to get the Next Order Number to be Used in your project.

The limitation with this example at the moment is the assumption that the OrderNumber SearchString is always 14 characters long (i.e the Car Reg's are always the same length) . If this is not the case then you may need to add some funky Case statements to the SQL query to accommodate varying lengths of Order Numbers or you could bring all the Order Numbers into your project and use a For Loop (in reverse) to get the used order numbers and then get the last one used.

Hope that helps.

Cheers,

Ian

 

[paulthepaddy]

Hi Ian, Thanks for your reply.
i had at first thought about using a data reader, but did think their would have been a more effective way of doing it, and that looks pretty perfect, didn't think it could have been done in the SQL code mind you.

nope that looks pretty dead on MOST car regs are the same length apart from the odd private plate, but either way as you said i can use select case, would prefere to do this.

My Server came today in the post so i would say most of my time will be spent getting it up and running , but im sure this code will work without much hastle

Thanks dude

 

[IanRyder]

Hi,

Good to hear that the example helps and good luck with the new server.

I just put my head in gear for a moment and came up with a better SQL query which takes into account VARYING lengths of your search string depending on Car Reg's. Here you go:-

Dim SampleNumber As String = "X1YAN210713P%"
Dim sqlQuery = <SQL>
                 SELECT MAX(CAST(SUBSTRING(OrderNumber, Len(@OrderNumber), LEN(OrderNumber) - (Len(@OrderNumber)-1)) AS INT)) AS LastOrderNumber
                 FROM dbo.OrderNumbers
                 WHERE (OrderNumber LIKE @OrderNumber)
               </SQL>

So long as you always make sure the last character in the Search string is the "%" sign (for wildcard selections), there will therefore never be any need for messing about with CASE or FOR statements in either SQL or VS.

Cheers,

Ian

 

[paulthepaddy]

Hi Ian, i got using the code and tested it, it worked perfectly thanks very much