sql experts help!
-
this one has me scratching my head....any ideas what it means or how to fix it?
the query i'm using from the c# app is
EXEC GetProductsActiveBySearchManModDesc '(p.Model LIKE ''%aed%'' OR m.Manufacturer LIKE ''%aed%'' OR p.Description LIKE ''%aed%'')'
my stored proc is
CREATE PROCEDURE GetProductsActiveBySearchManModDesc
(
@search varchar(8000)
)
AS
EXEC('
SELECT
p.ID AS pID,
p.Model AS pModel,
p.Description AS pDescription,
p.Price AS pPrice,
p.MSRP AS pMSRP,
p.Weight AS pWeight,
p.IsPublic AS pIsPublic,
p.IsUsed AS pIsUsed,
p.InfoURL AS pInfoURL,
p.InfoURLWinWidth AS pInfoURLWinWidth,
p.InfoURLWinHeight AS pInfoURLWinHeight,
p.ScreenShot AS pScreenShot,
p.ScreenShotWinWidth AS pScreenShotWinWidth,
p.ScreenShotWinHeight AS pScreenShotWinHeight,
p.FrontPage AS pFrontPage,
p.ImagePath AS pImagePath,
p.BannerID AS pBannerID,
p.ManufacturerID AS pManufacturerID,
p.AvailabilityID AS pAvailabilityID,
p.TabID AS pTabID,
p.CellColorID AS pCellColorID,
p.BorderColorID AS pBorderColorID,
p.OnSpecial AS pOnSpecial,
p.ETAID AS pETAID,
p.Clearance AS pClearance,
m.ID AS mID,
m.Manufacturer AS mManufacturer,
m.ImagePath AS mImagePath,
m.URL AS mURL,
m.BannerID AS mBannerID,
b.ID AS bID,
b.Name AS bName,
b.HTML AS bHTML,
b.ColorID AS bColorID,
b.TypeID AS bTypeID,
bt.ID AS btID,
bt.Type AS btType ,
a.ID AS aID,
a.Name AS aName,
a.ShortName AS aShortName,
a.SpecialID AS aSpecialID,
a.ImagePath AS aImagePath,
a.ButtonImagePath AS aButtonImagePath,
aas.ID AS asID,
aas.Name AS asName,
aas.ShortName AS asShortName,
aas.ImagePath AS asImagePath,
aas.URL AS asURL ,
t.ID AS tID,
t.Tab AS tTab,
t.ImagePath AS tImagePath ,
cc.ID AS ccID,
cc.Color AS ccColor,
cc.Code AS ccCode,
cc.Place AS ccPlace ,
bc.ID AS bcID,
bc.Color AS bcColor,
bc.Code AS bcCode,
bc.Place AS bcPlace,
mo.ID AS moID,
mo.Name AS moName
FROM Products p
LEFT JOIN Manufacturers m
ON m.ID=p.ManufacturerID
LEFT JOIN Banners b
ON b.ID=p.BannerID
LEFT JOIN BannerTypes bt
ON bt.ID=b.TypeID
LEFT JOIN Availability a
ON a.ID=p.AvailabilityID
LEFT JOIN AvailabilitySpecials aas
ON aas.ID=a.SpecialID
LEFT JOIN Tabs t
ON t.ID=p.TabID
LEFT JOIN Colors cc
ON cc.ID=p.CellColorID
LEFT JOIN Colors bc
ON bc.ID=p.BorderColorID
LEFT JOIN Months mo
ON mo.ID=p.ETAID
WHERE '+@search+' AND p.IsPublic=1
')
GO
thanks in advance
cheers
DATABIND ERROR! System.Data.Odbc.OdbcException: ERROR [HY007] [Microsoft][ODBC SQL Server Driver]Associated statement is not prepared at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle, SQL_HANDLE hType, RETCODE retcode) at System.Data.Odbc.OdbcDataReader.get_FieldCount() at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Library_808Code.Utilities.GetODBCDataSet(String query) at novamusik.com.producthorbot.BindData(String query)
i should note...
i can run this query directly inside query analyzer and have no problems
only when run from the web app does the error occur
something to do with .net handling it???
thanks again
cheers
-
I would guess that the problem is databinding in the .NET-application rather then the procedure. But the procedure is not easy to handle, as there are no defined output-parameters - the driver has no real way of knowing what kind of result it is going to get until the procedure already has executed.
Here's another (non-dynamic) version, that may give the driver (& .NET-databinding functionality, which I unfortunately don't know very much about) a bit more to work with:
CREATE PROCEDURE GetProductsActiveBySearchManModDesc
(
@search varchar(8000)
)
AS
SELECT
p.ID AS pID,
p.Model AS pModel,
-- (... Rest of your statement)
WHERE p.IsPublic=1
and (p.Model like @search
or m.Manufacturer like @search
or p.Description like @search)
GO
-- Usage
exec GetProductsActiveBySearchManModDesc '%aed%'
On another note - be careful with dynamic SQL and procedures called from the client. It can be very vulnerable to SQL injection.
-
I would agree with olavho with these exceptions.
I feel safer putting the wildcards in my procedure not in the Parameters.
I also delcare the Parameters so I can use a commandtype of storedProc. (this also makes me feel better about injection)
A subtle difference but here is what I would Have:
CREATE PROCEDURE GetProductsActiveBySearchManModDesc
(
@search varchar(8000)
)
AS
SELECT
p.ID AS pID,
p.Model AS pModel,
-- (... Rest of your statement)
WHERE p.IsPublic=1
and (p.Model like '%'+@search+'%'
or m.Manufacturer like '%'+@search+'%'
or p.Description like '%'+@search+'%'
GO
-- Usage (Sorry for the VB code---to early in the morning to think c#)
Dim sqlcommand1 As New System.Data.SqlClient.SqlCommand
sqlcommand1.CommandText = "GetProductsActiveBySearchManModDesc"
sqlcommand1.CommandType = System.Data.CommandType.StoredProcedure
sqlcommand1.Connection = Me.SqlConnection1
sqlcommand1.Parameters.AddNew System.Data.SqlClient.SqlParameter("@Search", System.Data.SqlDbType.VarChar, 50 sqlcommand1.Parameters.Item(0).Value ="aed"
sqlconnection1.open
sqlcommand1.ExecuteNonQuery
sqlconnection1.close
Hope that Helps
Tal McMahon
-
hello!
thanks to both of you for the replies!
i'm new to t-sql and greatly appreciate it
i'm totally open to doing this a different way...
here are my requirements
a) have a search box on the website that takes any number of words separated by space, eg "black cat"
b) try to match against model, description, manufacturer
c) use stored procedure
d) logic: any word from search can match any column from b)
so, it should try to match model like black, model like cat, descriptoin like black, description like cat, etc
any ideas greatly appreciated
cheers!
-
I would either
1. Built the search TSQL on the client with sp_execsql and declare each word as a parameter
or
2. create a wrapper to the procedure that "Tal McMahon" proposed and insert the results in a temp table calling it as many times as words you have and at the end returning the temp table contents
of the two I find 1 preferable but you are free to choose
HTH
* Noel -
http://www.sqlteam.com/item.asp?ItemID=5857
thanks!
taking your suggestion, i did some research and found this....
very cool - maybe it'll help someone else out 🙂
thanks for everybodies help
cheers
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply