By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,886 Members | 1,342 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,886 IT Pros & Developers. It's quick & easy.

return a value from sql table

P: n/a
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?
Nov 21 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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.
"KevinMGore" <Ke********@discussions.microsoft.com> wrote in message
news:F2**********************************@microsof t.com...
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?
Nov 21 '05 #2

P: n/a
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
"KevinMGore" <Ke********@discussions.microsoft.com> wrote in message
news:F2**********************************@microsof t.com...
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?

Nov 21 '05 #3

P: n/a
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(S tring method,
Boolean executing)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at comments.Form1.getNextSeq() in
G:\Shared\VBShare\dotnet\Kevin\comments\Form1.vb:l ine 224

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

"David Lloyd" wrote:
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.
"KevinMGore" <Ke********@discussions.microsoft.com> wrote in message
news:F2**********************************@microsof t.com...
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?

Nov 21 '05 #4

P: n/a
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
"KevinMGore" <Ke********@discussions.microsoft.com> wrote in message
news:E9**********************************@microsof t.com...
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(S tring method,
Boolean executing)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at comments.Form1.getNextSeq() in
G:\Shared\VBShare\dotnet\Kevin\comments\Form1.vb:l ine 224

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

"David Lloyd" wrote:
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.
"KevinMGore" <Ke********@discussions.microsoft.com> wrote in message
news:F2**********************************@microsof t.com...
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?

Nov 21 '05 #5

P: n/a
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#" wrote:
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
"KevinMGore" <Ke********@discussions.microsoft.com> wrote in message
news:E9**********************************@microsof t.com...
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(S tring method,
Boolean executing)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at comments.Form1.getNextSeq() in
G:\Shared\VBShare\dotnet\Kevin\comments\Form1.vb:l ine 224

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

"David Lloyd" wrote:
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.
"KevinMGore" <Ke********@discussions.microsoft.com> wrote in message
news:F2**********************************@microsof t.com...
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?


Nov 21 '05 #6

P: n/a
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
"KevinMGore" <Ke********@discussions.microsoft.com> wrote in message
news:A4**********************************@microsof t.com...
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#" wrote:
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
"KevinMGore" <Ke********@discussions.microsoft.com> wrote in message
news:E9**********************************@microsof t.com...
> 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(S tring method,
> Boolean executing)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteScalar()
> at comments.Form1.getNextSeq() in
> G:\Shared\VBShare\dotnet\Kevin\comments\Form1.vb:l ine 224
>
> <<
> I'm not sure how to correct it. Please advise. For the record Michael's
> sql
> statement works perfectly in query analyzer.
>
> "David Lloyd" wrote:
>
>> 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.
>>
>>
>> "KevinMGore" <Ke********@discussions.microsoft.com> wrote in message
>> news:F2**********************************@microsof t.com...
>> 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?
>>
>>
>>


Nov 21 '05 #7

P: n/a
Thank You very much - it works now.

"Michael C#" wrote:
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
"KevinMGore" <Ke********@discussions.microsoft.com> wrote in message
news:A4**********************************@microsof t.com...
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#" wrote:
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
"KevinMGore" <Ke********@discussions.microsoft.com> wrote in message
news:E9**********************************@microsof t.com...
> 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(S tring method,
> Boolean executing)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteScalar()
> at comments.Form1.getNextSeq() in
> G:\Shared\VBShare\dotnet\Kevin\comments\Form1.vb:l ine 224
>
> <<
> I'm not sure how to correct it. Please advise. For the record Michael's
> sql
> statement works perfectly in query analyzer.
>
> "David Lloyd" wrote:
>
>> 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.
>>
>>
>> "KevinMGore" <Ke********@discussions.microsoft.com> wrote in message
>> news:F2**********************************@microsof t.com...
>> 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?
>>
>>
>>


Nov 21 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.