Using SQL substring query in vb.net app

30 Mar 2005 9:22 PM

Michele Fondry via .NET 247

hello.
I have a webform form app in vb.Net that uses a SQL query. I am trying to use the substring function, but get the following error:
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: A field or property with the name 'description' was not found on the selected datasource.

Source Error:

Line 68:             daSearch.Fill(dsSearch, "SearchResults")
Line 69:             dgrSearchResults.DataSource = dsSearch.Tables("searchresults").DefaultView
Line 70:             dgrSearchResults.DataBind()

I know the field name description is correct, as I can remove the substring from the query and everything works...just my datagrid is too long.

Here is my SQL statement:
Dim daSearch As New SqlDataAdapter("select (substring('description',1,30)),idnum,station,iatanum,inputdate,empname,status from tbTipDetail order by idnum", conn)
            daSearch.Fill(dsSearch, "SearchResults")
            dgrSearchResults.DataSource = dsSearch.Tables("searchresults").DefaultView
            dgrSearchResults.DataBind()

Note: I get the same error with or without the dits around description.
I would appreciate any help or guidance you can offer.
Thank you.

--------------------------------
From: Michele Fondry

-----------------------
Posted by a user from .NET 247 (http://www.dotnet247.com/)

<Id>zZlUpy76YkmFj3tTqR+ZJQ==</Id>

30 Mar 2005 9:36 PM

Marina

You are taking the substring of a constant there. Not the column named
"description"

I am guessing you are trying to avoid the problem of "description" being a
keyword? In which case, use [description] for the column name.

Show quoteHide quote

"Michele Fondry via .NET 247" <anonym***@dotnet247.com> wrote in message
news:uqGGV6WNFHA.244@TK2MSFTNGP12.phx.gbl...
> hello.
> I have a webform form app in vb.Net that uses a SQL query. I am trying to
> use the substring function, but get the following error:
> Description: An unhandled exception occurred during the execution of the
> current web request. Please review the stack trace for more information
> about the error and where it originated in the code.
>
> Exception Details: System.Web.HttpException: A field or property with the
> name 'description' was not found on the selected datasource.
>
> Source Error:
>
>
> Line 68:             daSearch.Fill(dsSearch, "SearchResults")
> Line 69:             dgrSearchResults.DataSource =
> dsSearch.Tables("searchresults").DefaultView
> Line 70:             dgrSearchResults.DataBind()
>
>
> I know the field name description is correct, as I can remove the
> substring from the query and everything works...just my datagrid is too
> long.
>
> Here is my SQL statement:
> Dim daSearch As New SqlDataAdapter("select
> (substring('description',1,30)),idnum,station,iatanum,inputdate,empname,status
> from tbTipDetail order by idnum", conn)
>            daSearch.Fill(dsSearch, "SearchResults")
>            dgrSearchResults.DataSource =
> dsSearch.Tables("searchresults").DefaultView
>            dgrSearchResults.DataBind()
>
> Note: I get the same error with or without the dits around description.
> I would appreciate any help or guidance you can offer.
> Thank you.
>
> --------------------------------
> From: Michele Fondry
>
> -----------------------
> Posted by a user from .NET 247 (http://www.dotnet247.com/)
>
> <Id>zZlUpy76YkmFj3tTqR+ZJQ==</Id>

31 Mar 2005 5:00 PM

Tom Eckard

change your SQL statment to add an alias for the decription field inside the
substring call. The way it's written here, the first column will be coming
back as an unknown (or server defined) column that is not 'description'. If
you add the capitalized parts I've added below, then the first 30 characters
of the description field will be returned as a computed column (also named
'description') and your call to find the field now named 'description'
should work.

select (substring('description',1,30)) AS
DESCRIPTION,idnum,station,iatanum,inputdate,empname,status from tbTipDetail
order by idnum

"Michele Fondry via .NET 247" <anonym***@dotnet247.com> wrote in message
news:uqGGV6WNFHA.244@TK2MSFTNGP12.phx.gbl...
> hello.
> I have a webform form app in vb.Net that uses a SQL query. I am trying to

use the substring function, but get the following error:

> Description: An unhandled exception occurred during the execution of the

current web request. Please review the stack trace for more information
about the error and where it originated in the code.

>
> Exception Details: System.Web.HttpException: A field or property with the

name 'description' was not found on the selected datasource.

>
> Source Error:
>
>
> Line 68: daSearch.Fill(dsSearch, "SearchResults")
> Line 69: dgrSearchResults.DataSource =

dsSearch.Tables("searchresults").DefaultView

> Line 70: dgrSearchResults.DataBind()
>
>
> I know the field name description is correct, as I can remove the

substring from the query and everything works...just my datagrid is too
long.

>
> Here is my SQL statement:
> Dim daSearch As New SqlDataAdapter("select

(substring('description',1,30)),idnum,station,iatanum,inputdate,empname,stat
us from tbTipDetail order by idnum", conn)

> daSearch.Fill(dsSearch, "SearchResults")
> dgrSearchResults.DataSource =

dsSearch.Tables("searchresults").DefaultView
Show quoteHide quote

> dgrSearchResults.DataBind()
>
> Note: I get the same error with or without the dits around description.
> I would appreciate any help or guidance you can offer.
> Thank you.
>
> --------------------------------
> From: Michele Fondry
>
> -----------------------
> Posted by a user from .NET 247 (http://www.dotnet247.com/)
>
> <Id>zZlUpy76YkmFj3tTqR+ZJQ==</Id>