question about like

Hello, i'm just wondering if i have for example a table with 2 colummns the first one is some primary key (int) and the other is of the image data type.
For example one row would be:

key data
232 0x7A5B6789D4E2...(thats just how sql server 2005 express shows me the data when i make a query insede Management Studio Express)

now i'm wondering if it is possible to do query for lets say %89D4% with LIKE,(i kinda want to search for a certain pattern of bits or in this case hexadecimal values ) i've tryed but i get an error. I just want to know if it is possible to do this with like and how and if it isn't, what other options do i have (full-text search - ?)?Well, the problem is that you are looking at binary data, not string data... Also, know that the image datatype is deprecated and should be replaced with varbinary(max).

What was the error you received?|||i tryed the following sql statement:
select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE 0x%89D4% ;

and got the error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'D4'.

however this statement: select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE 0x89D4 ;
does not give me any errors, but this statement doesn't really help any, since it searches for the exact value 0x89D4.

and about the part that image is depricated, well the database that i have to use already exists, and is full of data so i'm not to sure that i have anything to say about the data types that they use, just have to go with what they have ...|||

FitzFarseer wrote:

i tryed the following sql statement:
select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE 0x%89D4% ;

and got the error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'D4'.

however this statement: select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE 0x89D4 ;
does not give me any errors, but this statement doesn't really help any, since it searches for the exact value 0x89D4.

and about the part that image is depricated, well the database that i have to use already exists, and is full of data so i'm not to sure that i have anything to say about the data types that they use, just have to go with what they have ...

You need to surround your like statements with quotes...

select * from table where field like '%xxx%'|||select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE '%89D4%' ;

gives me the following error:
Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 2 of like function.|||For your reading pleasure, and for reference:

MSDN Transact-SQL Reference for: LIKE

http://msdn2.microsoft.com/en-us/library/ms179859.aspx|||

FitzFarseer wrote:

select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE '%89D4%' ;

gives me the following error:
Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 2 of like function.

I think this is a result of trying to use like on a binary column. Syntactically, it is correct.sql

Question about Joins

Hi,

I have a stored Procedure called rpt_FundLipperRatings and i want to display a image which is store in the Database which a particular fundid doesnt exist. Below is the stored procedure that exist, I tried putting a case statement like totalreturnScore = CASE When TotalReturnScore IS NULL then 0 else TotalreturnScore END, but it doesnt work if the the fund doesnt exist in the database. So can some one please tell me how can i do it. and is it possible to put a where statement in a inner join

Like

Select * from fund f

Inner Join LipperLeaderPix ll on f.TotalReturnScore = ll.score where Condition

inner join .....

ALTER

PROCEDURE [dbo].[rpt_FundLipperLeaderRatings]

@.FundId

int

AS

SELECT

NumberOfYears

,

TotalReturnScore,

ll1.OverallBW TotalReturnImage,

ConsistentReturnScore

ll2.OverallBW ConsistentReturnImage,

PreservationScore,

ll3.OverallBW PreservationImage,

ExpenseScore,

ll4.OverallBW ExpenseImage

FROM

FundLipperLeader f

INNERJOIN LipperLeaderPix ll1ON f.TotalReturnScore= ll1.ScoreINNERJOIN LipperLeaderPix ll2ON f.ConsistentReturnScore= ll2.ScoreINNERJOIN LipperLeaderPix ll3ON f.PreservationScore= ll3.ScoreINNERJOIN LipperLeaderPix ll4ON f.ExpenseScore= ll4.Score

WHERE

f

.FundId= @.FundId

There are a number of ways you could handle this but I would have to see sample data in your tables to understand it.

You can always try using a LEFT OUTER JOIN instead of INNER JOIN on the two tables if you still want to return records that have NULL on the joined field

|||

Rossoneri,

Thanks for your reply and sorry for replying late.

The Fund Lipper Leader has the following Columns - FundId,,Numberof Years(0,3,5,10), DataValidDate, TotalReturnScore, ConsistentreturnScore, PreservationScore, ExpenseScore, addDate, LastChangeDate

All the score have data ranging from 0 - 5 and each score has a different picture associated with it.

The LipperLeaderPix has Columns which store Binary data(or the pictures) the columns are Score(0,1,2,3,4,5) OverallColor, ConsistentReturnColor, ExpenseColor, PreservationColor, TaxEfficineyColor, TotalReturnColor and OverallBW.

So the score is been associated with each picture. Is this information helpful or do u need more.

Regards,

Karen

|||

Rossoneri,

I tried running it using Left Outer Join but it didnt tried doing some like for some other sproc but i had to write a union to get to handle the non existent funds, but couldnt do it for this sproc.

Regards,

Karen

question about "System.Data.SqlClient.SqlException"

I'm trying to retrieve an image from my ms sql server 2005, and i'm using VS2005...however, i have the following error during the compilation process

Code in webform2.aspx.vb:

Partial Class webform2
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connstr As String = "Data Source=DCPRJ007\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True"
Dim cnn As New Data.SqlClient.SqlConnection(connstr)
Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)
cnn.Open()
Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
Dim bindata() As Byte = dr.GetValue(1)
Response.BinaryWrite(bindata)
End Sub
End Class

System.Data.SqlClient.SqlException was unhandled by user code
Class=15
ErrorCode=-2146232060
LineNumber=1
Message="Incorrect syntax near '='."
Number=102
Procedure=""
Server="DCPRJ007\SQLEXPRESS"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at webform2.Page_Load(Object sender, EventArgs e) in C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\WebSites\WebSite7\webform2.aspx.vb:line 10
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Are you sure you are getting value in the Request.QueryString("id") ? If not, your select statement will have an incorrect syntax and so is the exception. keep a break point and debug to find out.

Thanks

|||

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connstr As String = "Data Source=DCPRJ007\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True"
Dim cnn As New Data.SqlClient.SqlConnection(connstr)
Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)
cnn.Open()
Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
Dim bindata() As Byte = dr.GetValue(1)
Response.BinaryWrite(bindata)
End Sub
End Class

i found that...this line is highlighted during debugging, what is problem with this statement?

error message is : Incorrect syntax near '='.

thx a lot!!

|||

Are you sure, you have some value in Request.QueryString("id") ?

Thanks

|||

gaze:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connstr As String = "Data Source=DCPRJ007\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True"
Dim cnn As New Data.SqlClient.SqlConnection(connstr)
Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)
cnn.Open()
Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
Dim bindata() As Byte = dr.GetValue(1)
Response.BinaryWrite(bindata)
End Sub
End Class

i found that...this line is highlighted during debugging, what is problem with this statement?

error message is : Incorrect syntax near '='.

thx a lot!!

Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)

try

Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id='" & Request.QueryString("id") & "'", cnn)

means ... use single cote before and after the your id.

If you think this post helped you marked as read.