return a value from sql table

[Guest]

I'm trying to create a getFunction that returns a single value from an sql
table (the next sequence number actually in a group of records). I tried two
different methods, a command and data adaptor. Here is the code:
Private Function getNextSeq()
Dim strsql
Dim lds As DataSet = New DataSet()
strsql = "select max(cmt_seq_No) from oelincmt_sql where ord_No = '"
& gstrOrder & "'" _
& " line_seq_no = 0" & " Ord_type = '" & "O" & "'"
' the following 2 lines of code errored out:
Dim cmd As SqlCommand = New SqlCommand(strsql, sqlConn)
getNextSeq = cmd.ExecuteScalar

'then I tried this code which also errored out
Dim lda As SqlDataAdapter = New SqlDataAdapter(strsql, sqlConn)
lda.Fill(lds, "nextSeqNo")
getNextSeq = lds.Tables("nextSeq").Rows(0).Item(0)
end function

I would apreciate some advice as to the recommended way to create such a
function?

 

[Michael C#]

Note the COALESCE function which ensures the SELECT will return a "1" even
if the results of the query are NULL. Also, the AND operators between
conditions in the WHERE clause of the SQL command.

Private Function getNextSeq() As Integer
Dim strsql As String
strsql = "SELECT COALESCE(MAX(cmt_seq_No), 0) + 1 " & _
" FROM oelincmt_sql " & _
" WHERE ord_No = @ord_No " & _
" AND line_seq_no = 0 " &
" AND Ord_type = @ord_Type"
sqlcmd.Parameters.Add("@ord_No", SqlDbType.VarChar, 255).Value =
gstrOrder
sqlcmd.Parameters.Add("@ord_Type", SqlDbType.VarChar, 255).Value =
"O"
Dim cmd As SqlCommand = New SqlCommand(strsql, sqlConn)
getNextSeq = cmd.ExecuteScalar
sqlcmd.Dispose()
End Function

 

[David Lloyd]

Your first approach seems like the way to go. You did not give the error
message so it is hard to know exactly what the issue is. However, one
suggestion is the ExecuteScalar method returns an object and you will need
to cast the return value to the appropriate data type.

If that does not solve the issue, please post the exact error message you
are receiving.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I'm trying to create a getFunction that returns a single value from an sql
table (the next sequence number actually in a group of records). I tried two
different methods, a command and data adaptor. Here is the code:
Private Function getNextSeq()
Dim strsql
Dim lds As DataSet = New DataSet()
strsql = "select max(cmt_seq_No) from oelincmt_sql where ord_No = '"
& gstrOrder & "'" _
& " line_seq_no = 0" & " Ord_type = '" & "O" & "'"
' the following 2 lines of code errored out:
Dim cmd As SqlCommand = New SqlCommand(strsql, sqlConn)
getNextSeq = cmd.ExecuteScalar

'then I tried this code which also errored out
Dim lda As SqlDataAdapter = New SqlDataAdapter(strsql, sqlConn)
lda.Fill(lds, "nextSeqNo")
getNextSeq = lds.Tables("nextSeq").Rows(0).Item(0)
end function

I would apreciate some advice as to the recommended way to create such a
function?

 

[Guest]

Excellent, we're getting there. I liked Michael's function better so I used
it. It looks like you are correct about the data type issue. Here's the error
I'm getting:System.InvalidOperationException: ExecuteReader: CommandText property has
not been initialized
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method,
Boolean executing)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at comments.Form1.getNextSeq() in
G:\Shared\VBShare\dotnet\Kevin\comments\Form1.vb:line 224

<<
I'm not sure how to correct it. Please advise. For the record Michael's sql
statement works perfectly in query analyzer.

 

[Michael C#]

Looks like I made a typo, and put the Dim sqlcmd line in the wrong place.
Try this:

Private Function getNextSeq() As Integer
Dim strsql As String
strsql = "SELECT COALESCE(MAX(cmt_seq_No), 0) + 1 " & _
" FROM oelincmt_sql " & _
" WHERE ord_No = @ord_No " & _
" AND line_seq_no = 0 " &
" AND Ord_type = @ord_Type"
Dim sqlcmd As SqlCommand = New SqlCommand(strsql, sqlConn)
sqlcmd.Parameters.Add("@ord_No", SqlDbType.VarChar, 255).Value =
gstrOrder
sqlcmd.Parameters.Add("@ord_Type", SqlDbType.VarChar, 255).Value =
"O"
getNextSeq = cmd.ExecuteScalar
sqlcmd.Dispose()
End Function

 

[Guest]

Michael,

I did rewrite it with the command in the right place as follows:
Private Function getNextSeq() As Integer
Dim strsql As String
Dim cmd As SqlCommand = New SqlCommand(strsql, sqlConn)
cmd.Parameters.Add("@ord_No", SqlDbType.VarChar, 8).Value = gstrOrder
strsql = "SELECT COALESCE(MAX(cmt_seq_No), 0) + 1 " & _
" FROM oelincmt_sql " & _
" WHERE ord_No = @ord_No " & _
" AND line_seq_no = 0 " & _
" AND Ord_type = 'O'"
Try
strsql = cmd.ExecuteScalar
Catch ex As Exception
Console.WriteLine(ex.ToString)
MessageBox.Show(ex.ToString)
End Try

cmd.Dispose()
End Function
But I still get error about the commandText property not being
initialized...as shown in previous post? Any advice?

Thanks,

Kevin

 

[Michael C#]

You need to assign a value to strsql before you create the SqlCommand.


strsql = "SELECT COALESCE(MAX(cmt_seq_No), 0) + 1 " & _
" FROM oelincmt_sql " & _
" WHERE ord_No = @ord_No " & _
" AND line_seq_no = 0 " & _
" AND Ord_type = 'O'"
Dim cmd As SqlCommand = New SqlCommand(strsql, sqlConn)
cmd.Parameters.Add("@ord_No", SqlDbType.VarChar, 8).Value = gstrOrder

 

[Guest]

Thank You very much - it works now.

You need to assign a value to strsql before you create the SqlCommand.


strsql = "SELECT COALESCE(MAX(cmt_seq_No), 0) + 1 " & _
" FROM oelincmt_sql " & _
" WHERE ord_No = @ord_No " & _
" AND line_seq_no = 0 " & _
" AND Ord_type = 'O'"
Dim cmd As SqlCommand = New SqlCommand(strsql, sqlConn)
cmd.Parameters.Add("@ord_No", SqlDbType.VarChar, 8).Value = gstrOrder