another sql query

Hi All,

I'm really struggling to figure out and sql query that will retrieve the first 30 characters of a database field. My plan is to have a brief preview of the text and then have ...[more] afterwards. So far I have the below (text is the field I'm trying to only retrieve the first 30 characters):-

SELECT id, title, text FROM w_pages WHERE ID <> '1' ORDER BY ID

I've set the query to not retrieve id 1 as that's the text of my homepage. I've attempted LEFT(text,30) as I thought that was the way to do it, but it didn't work for me :(

Also, can these types of queries be used with sql2000 'ntext' database fields?

Many thanks in advance.

Regards,

RichI imagine the ntext is your problem. From Books online:
LEFT ( character_expression , integer_expression )

Arguments
character_expression

Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type that can be implicitly convertible to varchar. Otherwise, use the CAST function to explicitly convert character_expression.

integer_expression

Is a positive whole number. If integer_expression is negative, a null string is returned.

SO, I would change text to LEFT(CONVERT(nvarchar(30),[text]))

Giving the EXACT error message you are getting would really help quite a bit.|||Thanks for the speedy reply.

I'm still new to asp.net and sql queries and I appreciate all your help :)

I've tried what you suggested and now it's giving the below error message:-

-------

Server Error in '/' Application.
------------------------

The left function requires 2 arguments.
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.Data.SqlClient.SqlException: The left function requires 2 arguments.

Source Error:

Line 121: Dim resultsDataSet as New DataSet()
Line 122: Dim myDataAdapter as SqlDataAdapter = New SqlDataAdapter(sql, myConnection)
Line 123: myDataAdapter.Fill(resultsDataSet, "w_pages")
Line 124: Content.DataSource = resultsDataSet
Line 125: Content.DataBind()

Source File: E:\kunden\homepages\1\articles.aspx Line: 123

Stack Trace:

[SqlException: The left function requires 2 arguments.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +45
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +5
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +36
ASP.articles_aspx.GetContent() in E:\kunden\homepages\1\articles.aspx:123
ASP.articles_aspx.BindData() in E:\kunden\homepages\1\articles.aspx:107
ASP.articles_aspx.Page_Load(Object Sender, EventArgs E) in E:\kunden\homepages\1\articles.aspx:13
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +742

------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.910

--------

Regards,

Rich|||OK. Sorry about that.

SO, I would change text to LEFT(CONVERT(nvarchar(30),[text]),30)|||Thanks for that, a different error now (below) which is confusing, as I know the database field exists.

Sorry to keep bugging you with this problem.

Regards,

Rich

-----------------------------

Server Error in '/' Application.
------------------------

A field or property with the name 'text' was not found on the selected datasource.
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 'text' was not found on the selected datasource.

Source Error:

Line 123: myDataAdapter.Fill(resultsDataSet, "w_pages")
Line 124:Content.DataSource = resultsDataSet
Line 125: Content.DataBind()
Line 126:End Sub
Line 127:|||Then, perhaps the column name in the table is not text?|||Very strange, I just checked the column name and it's definately 'text'.

Oh well, I'll have a play about with it and see what happens.

Thanks for all your help, it's much appreciated.

Regards,

Rich|||Got it working :)

I used:-

SELECT id, pagedate, title, show, LEFT(CONVERT(nvarchar(300),[text]),300) as '111' FROM w_pages WHERE id <> '1' AND show = 'yes' ORDER BY ID DESC;

And then changed the datafield in my datagrid to '111' instead of 'text'

Thanks again for your help :)

Regards,

Rich