I'm trying to learn using sproc in ASP.NET, but ran into problems I couldn't solve. Here're the details
My Table (JournalArticle)
ArticleID - int (PK)
ArticleTitle - varchar
ArticleContent - text
I could run a normal sql string against the table itself in ASP.NET and got the results I expect.
but when using a sproc, i couldn't get anything
The sproc
CREATE PROCEDURE dbo.sp_ArticleSearch(@.srch text)
AS SELECT ArticleID, ArticleTitle, ArticleContent
FROM dbo.JournalArticle
WHERE (ArticleAbstract LIKE @.srch)
GO
After reading some of the threads here, I experimented by changing ArticleContent and @.srch to type varchar, still no luck, it's not returning anything.
I think the problem is when i set the value of @.srch (being new at this, I could be seriously wrong though), like this:
prmSearch.ParameterName = "@.srch"
prmSearch.SqlDbType = SqlDbType.Text
prmSearch.Value = Request.Form("txtSearch")
My original string looks like this
strSQL = "SELECT * FROM JournalArticle WHERE (ArticleContent LIKE '%" & Request.Form("txtSearch") & "%')"
What am I doing wrong?? Thanks in advance for any help.Hey, I'm new to sprocs but I have never seen the first line like that. i usually write them and seen them liek this;
CREATE PROCEDURE dbo.sp_ArticleSearch
@.srch text
as...
If that isn't the problem then could it be the LIKE part? Should it not have '%' too? or some other char for t-sql?
I may not know the correct answer, but try it and let me know, cuz i too am in the learning process and will use statements like that in the furture.|||You absolutely need to add the % wildcards to get this work for you the way it used to work.
You either need to add the '%' to the @.srch parameter:
CREATE PROCEDURE dbo.sp_ArticleSearch(@.srch text)
AS
SET NOCOUNT ON
SELECT @.srch = '%' + @.srch + '%'
SELECT ArticleID, ArticleTitle, ArticleContent
FROM dbo.JournalArticle
WHERE (ArticleAbstract LIKE @.srch)
GO
Or you need to append the wildcards directly in the SQL statement
CREATE PROCEDURE dbo.sp_ArticleSearch(@.srch text)
AS
SET NOCOUNT ON
SELECT ArticleID, ArticleTitle, ArticleContent
FROM dbo.JournalArticle
WHERE (ArticleAbstract LIKE '%' + @.srch + '%')
GO
It seems the general consensus is that the first option is the best way to go. Note that I added SET NOCOUNT ON to your sproc. This is especially necessary for the first sproc since without it SQL will send something like '1 item selected' as the first resultset back to your ASP.NET page.
Terri|||Thanks guys, will give this a try later.|||Terri, still no luck,
both of the sample procedures gave error:
Error 403 Invalid Operator For Data Type. Operator Equals Add. Type equals Text.
I've absolutely no idea what this means.|||OK, I got this to work if the parameter type is set to 'varchar' instead of 'text' in the stored procedure itself so it'll look like this:
CREATE PROCEDURE dbo.sp_ArticleSearch(@.srch varchar(8000))
AS
SET NOCOUNT ON
SELECT *
FROM dbo.JournalArticle
WHERE (ArticleAbstract LIKE '%' + @.srch + '%')
GO
Thank you very much guys.|||I found that error to be is data-type mismatch. Is the field 'ArticleAbstract' data-type text? as u declared @.srch to be?|||heh, u beat me to itsql
