return a value from sql table

**David Lloyd** · Nov 21 '05, 04:47 PM

Re: return a value from sql table

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

Managed I.T. Services & I.T. Consulting | Lemington Consulting

http://LemingtonConsulting.com

Award-winning Managed I.T. Services, I.T. Consulting, cloud solutions, database hosting, and A.I. software development for organizations nationwide.

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

"KevinMGore " <KevinMGore@dis cussions.micros oft.com> wrote in message
news:F2A93F3E-BEC7-4D9A-8302-A2FD94212163@mi crosoft.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(strs ql, sqlConn)
getNextSeq = cmd.ExecuteScal ar

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

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

**Michael C#** · Nov 21 '05, 04:47 PM

Re: return a value from sql table

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(cm t_seq_No), 0) + 1 " & _
" FROM oelincmt_sql " & _
" WHERE ord_No = @ord_No " & _
" AND line_seq_no = 0 " &
" AND Ord_type = @ord_Type"
sqlcmd.Paramete rs.Add("@ord_No ", SqlDbType.VarCh ar, 255).Value =
gstrOrder
sqlcmd.Paramete rs.Add("@ord_Ty pe", SqlDbType.VarCh ar, 255).Value =
"O"
Dim cmd As SqlCommand = New SqlCommand(strs ql, sqlConn)
getNextSeq = cmd.ExecuteScal ar
sqlcmd.Dispose( )
End Function
"KevinMGore " <KevinMGore@dis cussions.micros oft.com> wrote in message
news:F2A93F3E-BEC7-4D9A-8302-A2FD94212163@mi crosoft.com...[color=blue]
> 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(strs ql, sqlConn)
> getNextSeq = cmd.ExecuteScal ar
>
> 'then I tried this code which also errored out
> Dim lda As SqlDataAdapter = New SqlDataAdapter( strsql, sqlConn)
> lda.Fill(lds, "nextSeqNo" )
> getNextSeq = lds.Tables("nex tSeq").Rows(0). Item(0)
> end function
>
> I would apreciate some advice as to the recommended way to create such a
> function?[/color]

**KevinMGore** · Nov 21 '05, 04:48 PM

Re: return a value from sql table

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:[color=blue][color=green]
>>[/color][/color]
System.InvalidO perationExcepti on: ExecuteReader: CommandText property has
not been initialized
at System.Data.Sql Client.SqlComma nd.ValidateComm and(String method,
Boolean executing)
at System.Data.Sql Client.SqlComma nd.ExecuteReade r(CommandBehavi or
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.Sql Client.SqlComma nd.ExecuteScala r()
at comments.Form1. getNextSeq() in
G:\Shared\VBSha re\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.

"David Lloyd" wrote:
[color=blue]
> 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 " <KevinMGore@dis cussions.micros oft.com> wrote in message
> news:F2A93F3E-BEC7-4D9A-8302-A2FD94212163@mi crosoft.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(strs ql, sqlConn)
> getNextSeq = cmd.ExecuteScal ar
>
> 'then I tried this code which also errored out
> Dim lda As SqlDataAdapter = New SqlDataAdapter( strsql, sqlConn)
> lda.Fill(lds, "nextSeqNo" )
> getNextSeq = lds.Tables("nex tSeq").Rows(0). Item(0)
> end function
>
> I would apreciate some advice as to the recommended way to create such a
> function?
>
>
>[/color]

**Michael C#** · Nov 21 '05, 04:48 PM

Re: return a value from sql table

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(cm t_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(strs ql, sqlConn)
sqlcmd.Paramete rs.Add("@ord_No ", SqlDbType.VarCh ar, 255).Value =
gstrOrder
sqlcmd.Paramete rs.Add("@ord_Ty pe", SqlDbType.VarCh ar, 255).Value =
"O"
getNextSeq = cmd.ExecuteScal ar
sqlcmd.Dispose( )
End Function

"KevinMGore " <KevinMGore@dis cussions.micros oft.com> wrote in message
news:E9088543-2D74-4F82-A9E1-2A35AD983030@mi crosoft.com...[color=blue]
> 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:[color=green][color=darkred]
>>>[/color][/color]
> System.InvalidO perationExcepti on: ExecuteReader: CommandText property has
> not been initialized
> at System.Data.Sql Client.SqlComma nd.ValidateComm and(String method,
> Boolean executing)
> at System.Data.Sql Client.SqlComma nd.ExecuteReade r(CommandBehavi or
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.Sql Client.SqlComma nd.ExecuteScala r()
> at comments.Form1. getNextSeq() in
> G:\Shared\VBSha re\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.
>
> "David Lloyd" wrote:
>[color=green]
>> 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 " <KevinMGore@dis cussions.micros oft.com> wrote in message
>> news:F2A93F3E-BEC7-4D9A-8302-A2FD94212163@mi crosoft.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(strs ql, sqlConn)
>> getNextSeq = cmd.ExecuteScal ar
>>
>> 'then I tried this code which also errored out
>> Dim lda As SqlDataAdapter = New SqlDataAdapter( strsql, sqlConn)
>> lda.Fill(lds, "nextSeqNo" )
>> getNextSeq = lds.Tables("nex tSeq").Rows(0). Item(0)
>> end function
>>
>> I would apreciate some advice as to the recommended way to create such a
>> function?
>>
>>
>>[/color][/color]

**KevinMGore** · Nov 21 '05, 04:49 PM

Re: return a value from sql table

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(strs ql, sqlConn)
cmd.Parameters. Add("@ord_No", SqlDbType.VarCh ar, 8).Value = gstrOrder
strsql = "SELECT COALESCE(MAX(cm t_seq_No), 0) + 1 " & _
" FROM oelincmt_sql " & _
" WHERE ord_No = @ord_No " & _
" AND line_seq_no = 0 " & _
" AND Ord_type = 'O'"
Try
strsql = cmd.ExecuteScal ar
Catch ex As Exception
Console.WriteLi ne(ex.ToString)
MessageBox.Show (ex.ToString)
End Try

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

Thanks,

Kevin

"Michael C#" wrote:
[color=blue]
> 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(cm t_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(strs ql, sqlConn)
> sqlcmd.Paramete rs.Add("@ord_No ", SqlDbType.VarCh ar, 255).Value =
> gstrOrder
> sqlcmd.Paramete rs.Add("@ord_Ty pe", SqlDbType.VarCh ar, 255).Value =
> "O"
> getNextSeq = cmd.ExecuteScal ar
> sqlcmd.Dispose( )
> End Function
>
>
> "KevinMGore " <KevinMGore@dis cussions.micros oft.com> wrote in message
> news:E9088543-2D74-4F82-A9E1-2A35AD983030@mi crosoft.com...[color=green]
> > 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:[color=darkred]
> >>>[/color]
> > System.InvalidO perationExcepti on: ExecuteReader: CommandText property has
> > not been initialized
> > at System.Data.Sql Client.SqlComma nd.ValidateComm and(String method,
> > Boolean executing)
> > at System.Data.Sql Client.SqlComma nd.ExecuteReade r(CommandBehavi or
> > cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> > at System.Data.Sql Client.SqlComma nd.ExecuteScala r()
> > at comments.Form1. getNextSeq() in
> > G:\Shared\VBSha re\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.
> >
> > "David Lloyd" wrote:
> >[color=darkred]
> >> 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 " <KevinMGore@dis cussions.micros oft.com> wrote in message
> >> news:F2A93F3E-BEC7-4D9A-8302-A2FD94212163@mi crosoft.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(strs ql, sqlConn)
> >> getNextSeq = cmd.ExecuteScal ar
> >>
> >> 'then I tried this code which also errored out
> >> Dim lda As SqlDataAdapter = New SqlDataAdapter( strsql, sqlConn)
> >> lda.Fill(lds, "nextSeqNo" )
> >> getNextSeq = lds.Tables("nex tSeq").Rows(0). Item(0)
> >> end function
> >>
> >> I would apreciate some advice as to the recommended way to create such a
> >> function?
> >>
> >>
> >>[/color][/color]
>
>
>[/color]

**Michael C#** · Nov 21 '05, 04:53 PM

Re: return a value from sql table

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

strsql = "SELECT COALESCE(MAX(cm t_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(strs ql, sqlConn)
cmd.Parameters. Add("@ord_No", SqlDbType.VarCh ar, 8).Value = gstrOrder

"KevinMGore " <KevinMGore@dis cussions.micros oft.com> wrote in message
news:A45B2FFD-F375-470B-B993-ECB665C6161F@mi crosoft.com...[color=blue]
> 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(strs ql, sqlConn)
> cmd.Parameters. Add("@ord_No", SqlDbType.VarCh ar, 8).Value =
> gstrOrder
> strsql = "SELECT COALESCE(MAX(cm t_seq_No), 0) + 1 " & _
> " FROM oelincmt_sql " & _
> " WHERE ord_No = @ord_No " & _
> " AND line_seq_no = 0 " & _
> " AND Ord_type = 'O'"
> Try
> strsql = cmd.ExecuteScal ar
> Catch ex As Exception
> Console.WriteLi ne(ex.ToString)
> MessageBox.Show (ex.ToString)
> End Try
>
> cmd.Dispose()
> End Function
> But I still get error about the commandText property not being
> initialized...a s shown in previous post? Any advice?
>
> Thanks,
>
> Kevin
>
> "Michael C#" wrote:
>[color=green]
>> 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(cm t_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(strs ql, sqlConn)
>> sqlcmd.Paramete rs.Add("@ord_No ", SqlDbType.VarCh ar, 255).Value =
>> gstrOrder
>> sqlcmd.Paramete rs.Add("@ord_Ty pe", SqlDbType.VarCh ar, 255).Value =
>> "O"
>> getNextSeq = cmd.ExecuteScal ar
>> sqlcmd.Dispose( )
>> End Function
>>
>>
>> "KevinMGore " <KevinMGore@dis cussions.micros oft.com> wrote in message
>> news:E9088543-2D74-4F82-A9E1-2A35AD983030@mi crosoft.com...[color=darkred]
>> > 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.InvalidO perationExcepti on: ExecuteReader: CommandText property
>> > has
>> > not been initialized
>> > at System.Data.Sql Client.SqlComma nd.ValidateComm and(String method,
>> > Boolean executing)
>> > at System.Data.Sql Client.SqlComma nd.ExecuteReade r(CommandBehavi or
>> > cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
>> > at System.Data.Sql Client.SqlComma nd.ExecuteScala r()
>> > at comments.Form1. getNextSeq() in
>> > G:\Shared\VBSha re\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.
>> >
>> > "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 " <KevinMGore@dis cussions.micros oft.com> wrote in message
>> >> news:F2A93F3E-BEC7-4D9A-8302-A2FD94212163@mi crosoft.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(strs ql, sqlConn)
>> >> getNextSeq = cmd.ExecuteScal ar
>> >>
>> >> 'then I tried this code which also errored out
>> >> Dim lda As SqlDataAdapter = New SqlDataAdapter( strsql,
>> >> sqlConn)
>> >> lda.Fill(lds, "nextSeqNo" )
>> >> getNextSeq = lds.Tables("nex tSeq").Rows(0). Item(0)
>> >> end function
>> >>
>> >> I would apreciate some advice as to the recommended way to create such
>> >> a
>> >> function?
>> >>
>> >>
>> >>[/color]
>>
>>
>>[/color][/color]

**KevinMGore** · Nov 21 '05, 04:54 PM

Re: return a value from sql table

Thank You very much - it works now.

"Michael C#" wrote:
[color=blue]
> You need to assign a value to strsql before you create the SqlCommand.
>
>
> strsql = "SELECT COALESCE(MAX(cm t_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(strs ql, sqlConn)
> cmd.Parameters. Add("@ord_No", SqlDbType.VarCh ar, 8).Value = gstrOrder
>
>
> "KevinMGore " <KevinMGore@dis cussions.micros oft.com> wrote in message
> news:A45B2FFD-F375-470B-B993-ECB665C6161F@mi crosoft.com...[color=green]
> > 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(strs ql, sqlConn)
> > cmd.Parameters. Add("@ord_No", SqlDbType.VarCh ar, 8).Value =
> > gstrOrder
> > strsql = "SELECT COALESCE(MAX(cm t_seq_No), 0) + 1 " & _
> > " FROM oelincmt_sql " & _
> > " WHERE ord_No = @ord_No " & _
> > " AND line_seq_no = 0 " & _
> > " AND Ord_type = 'O'"
> > Try
> > strsql = cmd.ExecuteScal ar
> > Catch ex As Exception
> > Console.WriteLi ne(ex.ToString)
> > MessageBox.Show (ex.ToString)
> > End Try
> >
> > cmd.Dispose()
> > End Function
> > But I still get error about the commandText property not being
> > initialized...a s shown in previous post? Any advice?
> >
> > Thanks,
> >
> > Kevin
> >
> > "Michael C#" wrote:
> >[color=darkred]
> >> 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(cm t_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(strs ql, sqlConn)
> >> sqlcmd.Paramete rs.Add("@ord_No ", SqlDbType.VarCh ar, 255).Value =
> >> gstrOrder
> >> sqlcmd.Paramete rs.Add("@ord_Ty pe", SqlDbType.VarCh ar, 255).Value =
> >> "O"
> >> getNextSeq = cmd.ExecuteScal ar
> >> sqlcmd.Dispose( )
> >> End Function
> >>
> >>
> >> "KevinMGore " <KevinMGore@dis cussions.micros oft.com> wrote in message
> >> news:E9088543-2D74-4F82-A9E1-2A35AD983030@mi crosoft.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.InvalidO perationExcepti on: ExecuteReader: CommandText property
> >> > has
> >> > not been initialized
> >> > at System.Data.Sql Client.SqlComma nd.ValidateComm and(String method,
> >> > Boolean executing)
> >> > at System.Data.Sql Client.SqlComma nd.ExecuteReade r(CommandBehavi or
> >> > cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> >> > at System.Data.Sql Client.SqlComma nd.ExecuteScala r()
> >> > at comments.Form1. getNextSeq() in
> >> > G:\Shared\VBSha re\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.
> >> >
> >> > "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 " <KevinMGore@dis cussions.micros oft.com> wrote in message
> >> >> news:F2A93F3E-BEC7-4D9A-8302-A2FD94212163@mi crosoft.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(strs ql, sqlConn)
> >> >> getNextSeq = cmd.ExecuteScal ar
> >> >>
> >> >> 'then I tried this code which also errored out
> >> >> Dim lda As SqlDataAdapter = New SqlDataAdapter( strsql,
> >> >> sqlConn)
> >> >> lda.Fill(lds, "nextSeqNo" )
> >> >> getNextSeq = lds.Tables("nex tSeq").Rows(0). Item(0)
> >> >> end function
> >> >>
> >> >> I would apreciate some advice as to the recommended way to create such
> >> >> a
> >> >> function?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>[/color][/color]
>
>
>[/color]

return a value from sql table

return a value from sql table

Comment

Comment

Comment

Comment

Comment

Comment

Comment