max record + 1

Hi everybody,

I have this:

[code]

Sub FormToData(ByVal datObj As DataLayer.ScreenGroupItems)

Dim newkey As Integer = 0

Dim IRecordStatusValue As Integer = 0

Dim IInpUserKey As String = System.DBNull.Value.ToString()

Dim IJavaValue As String = ""

Dim IInpChangeDateValue As Date = Date.Now.ToShortDateString()

Dim IChangeDateValue As Date = Date.Now.ToShortDateString()

'Dim IOrder As Integer

If String.IsNullOrEmpty(IJavaValue.ToString().Trim) = True Then

IJavaValue = vbNull

End If

Dim objMax As New DataLayer.ScreenGroupAuthItems

Dim newMax As Integer = 0

objMax.GetRows(, , , , "SELECT max(IOrder) + 1 FROM ScreenGroupItems")

If objMax.Count > 0 Then

newMax = CInt("0" & objMax.GetVal("IOrder"))

End If

With datObj

.GetRow(wform.Keys)

.SetVal("IDescriptionNL", TxtDescription.Text.Trim)

.SetVal("IURL", TxtURL.Text.Trim())

.SetVal("IIcon", txtIcon.Text.Trim)

.SetVal("IToolTipNL", TxtTooltip.Text.Trim)

.SetVal("Height", txtHoogte.Text.Trim)

.SetVal("Width", txtBreedte.Text.Trim)

.SetVal("IRecordStatus", IRecordStatusValue)

.SetVal("IInpChangeDate", IInpChangeDateValue.ToShortDateString())

.SetVal("IChangeDate", IChangeDateValue.ToShortDateString())

.SetVal("IJava", IJavaValue)

.SetVal("IOrder", newMax)

newkey = .UpdateRow()

IInpUserKey = .UpdateRow()

'IChangeDateValue = .UpdateRow().ToString()

'IInpChangeDateValue = .UpdateRow().ToString()

'IJavaValue = .UpdateRow()

'IChangeUserKey = .UpdateRow()

'IRecordStatusValue = .UpdateRow()

End With

Dim objScreenGroup As New DataLayer.ScreenGroupAuthItems

'Insert new row

objScreenGroup.GetRows(, , "AITKey =" & newkey & "AND AIGRKey =" & wform.DetailKeys)

If objScreenGroup.Count = 0 Then

objScreenGroup.GetNewRow()

End If

objScreenGroup.SetVal("AITKey", newkey)

'objScreenGroup.SetVal("IRecordStatus", IRecordStatusValue)

objScreenGroup.SetVal("AIGRKey", SelMenuGroeplist.SelectedValue)

objScreenGroup.SetVal("IInpUserKey", IInpUserKey)

objScreenGroup.UpdateRow()

End Sub

[/code]

And it goes about this piece of code:

objMax.GetRows(, , , , "SELECT max(IOrder) + 1 FROM ScreenGroupItems")

If objMax.Count > 0 Then

newMax = CInt("0" & objMax.GetVal("IOrder"))

End If

I want to have Max Iorder + 1(if you insert new record). But in the Database I still see the value 0.

Thank you

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes12y ago
Accepted answer
Without knowing the inner workings of the GetVal method I can only guess, but try instead:

objMax.GetRows(, , , , "SELECT max(IOrder) + 1 AS newMax FROM ScreenGroupItems")
If objMax.Count > 0 Then
newMax = CInt("0" & objMax.GetVal("newMax"))
End If
albert albert12y ago
Hi,

Thank you:

I have it now like this:

[code]
If String.IsNullOrEmpty(IJavaValue.ToString().Trim) = True Then
IJavaValue = vbNull
End If

Dim objMax As New DataLayer.ScreenGroupItems
Dim newMax As Integer = 0
Dim Prio As Integer = 0

objMax.GetRows(, "max(IOrder) + 1 as iOrder", , , , , )

' Prio = objMax.GetVal("maxOrder")

If objMax.Count > 0 Then
newMax = CInt("0" & objMax.GetVal("IOrder"))
End If

[/code]

And it works
Vulpes12y ago
OK, so it sounds like you can only pass a column name to the GetVal method.

But what would happen if the SQL query returned multiple rows. What would GetVal return then?

Does GetVal do any processing itself?
albert albert12y ago
Hi,

Thank you. But that doesnt work. Because IOrder is a column field in the table: ScreenGroupAuthItems. And the highest number in that column is: 72