SELECT tblWatchInstance.WatchID,tblWatchBrands.Description,tblWatches.Serial_Number,tblWatches.OwnerFName,tblWatches.OwnerLName, tblClients.Name FROM tblWatchInstance INNER JOIN tblWatches ON tblWatchInstance.Watch_Key = tblWatches.WATCH_KEY INNER JOIN tblWatchColors ON tblWatches.COLOR_KEY = tblWatchColors.COLOR_KEY INNER JOIN tblWatchBrands ON tblWatches.BRAND_KEY = tblWatchBrands.BRAND_KEY INNER JOIN tblWatchTypes ON tblWatches.WATCHTYPE_KEY = tblWatchTypes.WatchType_KEY INNER JOIN tblClients ON tblWatchInstance.Instance_Key = tblClients.CLIENT_KEY WHERE tblWatchInstance.RepairNumber LIKE '%" + txtRepairNumber.Text + "%' AND tblWatches.OwnerLName LIKE '%" + txtOwnersName.Text + "%' AND tblWatches.Serial_Number LIKE '%" + txtSerialNo.Text + "%' AND tblWatchBrands.Description LIKE '%" + cboBrand.Text + "%' AND tblWatchColors.Description LIKE '%" + cboColor.Text + "%' AND tblWatchTypes.Description LIKE '%" + cboType.Text + "%' AND tblClients.CLIENT_CODE LIKE '%" + txtClientID.Text + "%'
When i put this into the Query Analyzer, Its executes sucessfully but when when i add this into my application it does not return any thing...This Query works when this line is not in the Query but i need it, can anyone tell me what could be causing this issue to arrive? Even with the Like In the statement it still does not return any items, Could my answer lie in the Structure of the database.
When you use the %, you should use LIKE instead of =
AND tblClients.CLIENT_CODE LIKE '%" + txtClientID.Text + "%'
|||i suggest that the query be made an sp and pass the required fields as parameters
anyway i think it lacks an end quote
tblClients.CLIENT_CODE = '%" + txtClientID.Text + "%'" --<here in red
|||I would second that all SQL queries should be written as stored procs.
I assumed the end quote was a typo, it wouldn't have compiled and run otherwise ?
|||Did you use the Profiler tool to see what does your application actually send to the server?|||Note: This is the second thread you have started on the same question
gavrilenko_s wrote:
Did you use the Profiler tool to see what does your application actually send to the server?
And print out the query you are sending in your tool (even to a messagebox since you can copy that into QA or SSMS). The query looks fine to me, just from the query you posted:
/* Powered by General SQL Parser at www.sqlparser.com */
SELECT TBLWATCHINSTANCE.WATCHID,
TBLWATCHBRANDS.DESCRIPTION,
TBLWATCHES.SERIAL_NUMBER,
TBLWATCHES.OWNERFNAME,
TBLWATCHES.OWNERLNAME,
TBLCLIENTS.NAME
FROM TBLWATCHINSTANCE
INNER JOIN TBLWATCHES
ON TBLWATCHINSTANCE.WATCH_KEY = TBLWATCHES.WATCH_KEY
INNER JOIN TBLWATCHCOLORS
ON TBLWATCHES.COLOR_KEY = TBLWATCHCOLORS.COLOR_KEY
INNER JOIN TBLWATCHBRANDS
ON TBLWATCHES.BRAND_KEY = TBLWATCHBRANDS.BRAND_KEY
INNER JOIN TBLWATCHTYPES
ON TBLWATCHES.WATCHTYPE_KEY = TBLWATCHTYPES.WATCHTYPE_KEY
INNER JOIN TBLCLIENTS
ON TBLWATCHINSTANCE.INSTANCE_KEY = TBLCLIENTS.CLIENT_KEY
WHERE TBLWATCHINSTANCE.REPAIRNUMBER LIKE '%" + txtRepairNumber.Text + "%'
AND TBLWATCHES.OWNERLNAME LIKE '%" + txtOwnersName.Text + "%'
AND TBLWATCHES.SERIAL_NUMBER LIKE '%" + txtSerialNo.Text + "%'
AND TBLWATCHBRANDS.DESCRIPTION LIKE '%" + cboBrand.Text + "%'
AND TBLWATCHCOLORS.DESCRIPTION LIKE '%" + cboColor.Text + "%'
AND TBLWATCHTYPES.DESCRIPTION LIKE '%" + cboType.Text + "%'
AND TBLCLIENTS.CLIENT_CODE LIKE '%" + txtClientID.Text + "%'
But without looking at what you are trying to send at the last moment, you never know. Any one of thos text values might containt Ascii character 0 which would terminate the string. Or some other invalid value you need to make sure that you use some function to turn all single quotes ' into two single quote characters '', or the text in the textbox could be the problem.
|||I would like to point out that I hop you are validating the input someone is putting into your text boxes. Why because this is called a SQL Injection attack.
Building up a query in this way allows the user to enter something like
'; shutdwn with nowait --
This will shutdown you SQL Server, this is quite tame because its not distructive, but imagine what else someone could do.
There are very good articles in BOL and all over the web which I suggest you read.
Finally your solution above will not perform. The optimiser has no ability of knowing what to filter the data on and so will result in an awful lot of table scans.
If these are optional search criteria you would be better off building the SQL and not including the clauses that have not had values specified for them. You should also avoid the use of % as it reduces the ability to use indexes to search the data.
|||Your right I do have some zeros in the datasource information that i am trying to SELECT FROM, from that particular combobox.. so do i need to change the type of this column in the DBase right now it is of type nvarchar, any suggestions?|||Yes, print out the query you are trying to execute and look at it. Take that query and try to run it. That is step one.
|||I am kinda cloudy on where you are going with your statement? Print the querey? And try to run it in what? I have ran it in the Q-Analyzer and it works but when i try to excute the search field pertaining to the 'RED' it gives me an System Error exception. I do have some Ascii charectars in the column like 0 and 1 and some # sounds is there a way SQL looks pass it or do i need to change the type in the column of this particular table i am working with.
This works fine until i add
SELECT tblWatchInstance.WatchID,tblWatchBrands.Description,tblWatches.Serial_Number,tblWatches.OwnerFName,tblWatches.OwnerLName, tblClients.Name FROM tblWatchInstance INNER JOIN tblWatches ON tblWatchInstance.Watch_Key = tblWatches.WATCH_KEY INNER JOIN tblWatchColors ON tblWatches.COLOR_KEY = tblWatchColors.COLOR_KEY INNER JOIN tblWatchBrands ON tblWatches.BRAND_KEY = tblWatchBrands.BRAND_KEY INNER JOIN tblWatchTypes ON tblWatches.WATCHTYPE_KEY = tblWatchTypes.WatchType_KEY INNER JOIN tblClients ON tblWatchInstance.Instance_Key = tblClients.CLIENT_KEY WHERE tblWatchInstance.RepairNumber LIKE '%" + txtRepairNumber.Text + "%' AND tblWatches.OwnerLName LIKE '%" + txtOwnersName.Text + "%' AND tblWatches.Serial_Number LIKE '%" + txtSerialNo.Text + "%' AND tblWatchBrands.Description LIKE '%" + cboBrand.Text + "%' AND tblWatchColors.Description LIKE '%" + cboColor.Text + "%' AND tblWatchTypes.Description LIKE '%" + cboType.Text + "%' AND this line here > tblClients.CLIENT_CODE LIKE '%" + txtClientID.Text + "%'
|||I think he means that you should print the sql statement generated in your code rather than EXECuting it. Just print the variable that you are using to parse together the sql statment in your programming environment. It may show you where the problem is when you see the substituted data pulled from your form fields.|||This problem has nothing to do with TSQL per se. You should post in Visual Basic General or ASP.NET or Data Access forum. It is a question of debugging the client code to figure out the query it is sending to the server.|||I figured that it was probably not a T-SQL error, but csi_hugh was exactly right, without seeing the code that is sent to the server I didn't want to send him packing :)|||The error thats occuring would be useful to.|||All i get is a System Error Exception, but that only occurs when i add the text that is in the red to the querey now that information in the column in my DB does have "zeros" and ## charectars so i think i may need to change the type in my DB column maybe ? right now it is a varchar(80)...When i dont add that red text in the query it excutes fine and returns exactly what i need.
No comments:
Post a Comment