SQL delete Statement return 0 rows

[Denden]

hi could anyone help me fix my code..

i executed this line of code

 
delete from Test.dbo.Test where Password='0x4465727374696E65'
 
 

the password column has a data type of varbinary and i inserted a string into it.

now i have a problem querying the table, i need to convert the varbinary data type into string and at the same type i want to display the ID column.

[Jim Doherty]

Hello Denden,

Look at CAST and CONVERT functions for SQL server but here is a pointer for you from the perspective of a SELECT statement

declare @v varbinary(max)
set @v = 0x4465727374696E65
select * from [dbo].[Test] where [password]=CAST(@v as varbinary(max))

[Denden]

Thanks sir. i will try that when im at home. God Bless

update.. the password column didnt change to string :(

i tried to run this code

SELECT CAST(password AS VARCHAR(MAX))
FROM test

it works but the problem is, it just return the password column, i also want to return the Username column.

[Jim Doherty]

Username as in what? is that a statically stored value in your table or SQL servers own user/roles mechanism. If it is a statically stored value within your table then the SELECT statement can be expanded to include that column in the usual manner.

I am not at all clear on what it is you need Denden from your original post as it is not explicit enough in terms of your framework to enable me to make a concise judgement on this.

Can I suggest you read the SQL server (BOL books online) or the plentiful material that exists related to SQL statements on the net.

My perception of this is that you want to return a dataset of ID, UserName and Password in which case a simple SELECT statement crafted as such, would suffice, if that data is stored in your test table ie:

SELECT ID,UserName,CAST(password AS VARCHAR(MAX)) FROM Test

[Rabbit]

I hope you hashed the password, it's very poor security to store it in plain text.