|
Well, you could always do this:
select @room_code = @room_code + '%'
select count(room_code) from rooms where room_code like @room_code
|
|
|
|
|
i am using ASP.net, C# anD SQL Server2005,in the following query:
string q = "SELECT collapsed_building.b_name,collapsed_building.b_desc FROM collapsed_building WHERE collapsed_building.b_name LIKE '" + crimewithdate.text2 + "' ";
I WANT TO USE % AFTER THE KEYWORD like SO THAT ALL THE BUILDING NAMES WHICH MATCH THE VALUE ENTERED BY THE USER ARE DISPLAYED
WHEN I WRITE (LIKE '" + %crimewithdate.text2 %+ "'), IT GIVES ERROR, WHAT WILL BE THE CORRECT SENTAX
|
|
|
|
|
Please, don't do this. You leave yourself with open to a Sql Injection attack. Take a look at this[^] article on how to avoid this. Now, you many want to consider using something like this:
DECLARE @building_name NVARCHAR(100)
SELECT @building_name = 'Test'
-- The lines above are just a sample to get you started. Move this into a stored procedure and use
-- the code below to do the actual work. Note that you would want to determine whether or not
-- @building_name ended in a % before using this code.
SELECT @building_name = @building_name + '%'
SELECT collapsed_building.b_name,collapsed_building.b_desc FROM collapsed_building WHERE collapsed_building.b_name LIKE @building_name
|
|
|
|
|
Pete is correct in saying that this is dangerous code. You have to use strong
protection to make sure that crimewithdate.text2 does not contain
any malicious SQL code instead of the expected search parameter.
To answer your question as asked, here is one solution:
string q = string.Format("SELECT collapsed_building.b_name,
collapsed_building.b_desc FROM collapsed_building
WHERE collapsed_building.b_name LIKE '%{0}%'", crimewithdate.text2);
(The line breaks are for readability only)
Do realize that an evil user could enter something like;
x'; TRUNCATE TABLE collapsed_building; SELECT * FROM collapsed_building WHERE b_name LIKE 'x
and wreck your whole day.
It is much better to pass crimewithdate.text2 as a parameter and not expose your database to bad people.
modified on Tuesday, January 22, 2008 11:26:49 AM
|
|
|
|
|
Hi, i want to create a job.
http://msdn2.microsoft.com/en-us/library/ms191439.aspx[^]
By following this link, i did the same, but i unable to see the sqlserver agent in object explorer. i started the service in adminsitrative tools ->services. And also, i restarted the sqlserver. still not able to see sqlserver agent..can any one help regarding this.....
G. Satish
|
|
|
|
|
I have 2 Databases: in that, In the Database DB1, i write a stored procedure: In that i write
Select command for a the table that has to fetch from Database DB2.
How to write a Stored Procedure for this?
Regards,
LEE
|
|
|
|
|
If you are using SQL Server, identify your tables using the following naming convention:
databasename.owner.tablename
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Doing that sort of thing is generally a bad idea.
I have some code that does that and now we want to move one of the databases to a different server and can't without breaking the code. At best we could link the servers, but I'd still have to modify the code. (And we've had trouble with linked servers in the past.)
Plus, not all database systems allow that construct so it may lock you into SQL Server.
You may want to create a view that handles the cross-database query.
Anyway, be aware of the potential problems.
|
|
|
|
|
Hi freinds i want to develop crystal report which used different data source to dispaly different record in one singal report
There i have some Question
1 : Can i used Subreport for differnt datasource in main Crystal report and can i send parameter to them
2: can i useHide subreport i.e if i there is Condition to show subreport(a)
then all remain subreport shoul be hide mean to say shoe singal subreport in each case Can i do this Can some body tell me
wasim khan
|
|
|
|
|
|
Hi there,
Can someone tell me, is it possible in SQL Server 2005 how to Decrypt and Encrypted SP or View.
Mujtaba
"If both of us are having one apple each and we exchange it, at the end we both will have one apple each. BUT if both of us are having one idea each and we exchange it, at the end both of us will be having two ideas each."
|
|
|
|
|
The procedure encryption is actually referred to as obfuscation in Books Online. Also, there is no SQL Server command for decrypting it back. You could use third party components to accomplish this, there sure can be found some by searching in google for them.
The only feature for protecting code in SQL Server is the WITH ENCRYPTION clause (http://databases.aspfaq.com/database/how-do-i-protect-my-stored-procedure-code.html[^]). It is weak not necessarily because the encryption is weak (it uses RC4), but because the encryption key can be easily found. An attacker will focus on finding the encryption key rather than breaking the encryption algorithm in such a solution.
|
|
|
|
|
Hi, i want a procedure to schedule a job in sqlserver 2005.
How to create a job in sqlserver 2005?
G. Satish
|
|
|
|
|
This information is available in SQL Server BOL. Did it occur to you to read the documentation?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
I cant understand your answer. I dont know anything regarding schdeuling and sqlserver agent also. I dont know BOL. what is this? can u give link rgarding this concept?
G. Satish
|
|
|
|
|
Satish - Developer wrote: I dont know BOL. what is this?
SQL Server Books Online. It's the online documentation for SQL Server. Reading documentation is what people usually do when they want to learn how something works. I suggest you follow their example.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
hi
can any body explain me how to send mails from sql server 2000
thanks
Suman
|
|
|
|
|
|
you must join your computer of sql server to domain and make profile for your email Exchange on Sql server 2000.
then used it.
123
|
|
|
|
|
Hi,
I have written a stored procedure for displaying data page-wise in ASP.Net 1.1 version. Even though data is present in database, when I tried to execute the following stored procedure, nothing is executing. I'm also not getting any errors. Could anybody please help me in finding out what's I'm doing wrong in these Stored Procedure?
ALTER PROCEDURE GETRICSBYPAGE
(
@Page int,
@RecsPerPage int
)
AS
SET NOCOUNT ON
CREATE TABLE #TEMPRICSBYPAGE
(
SLNO INT,
PK_VALUE INT,
RIC_NAME VARCHAR(50),
NO_OF_DAYS INT,
LAST_UPDATE_DATE DATETIME,
PE_FID1 BIGINT,
HEADEND_FID1383 VARCHAR(30),
LOCATION_FID836 VARCHAR(30),
CTBTR_BKDG VARCHAR(50)
)
INSERT INTO #TEMPRICSBYPAGE (SLNO,PK_VALUE,RIC_NAME, NO_OF_DAYS, LAST_UPDATE_DATE,
PE_FID1, HEADEND_FID1383,LOCATION_FID836, CTBTR_BKDG)
SELECT '' SLNO,PK_VALUE,RIC_NAME RIC, NO_OF_DAYS Days, LAST_UPDATE_DATE,
PE_FID1, HEADEND_FID1383,LOCATION_FID836, CTBTR_BKDG
FROM STALERICSTEMP2 WHERE PK_VALUE IS NOT NULL ORDER BY RIC_NAME
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1)* @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
SELECT '' SLNO,PK_VALUE,RIC_NAME,NO_OF_DAYS,LAST_UPDATE_DATE,PE_FID1,
HEADEND_FID1383,LOCATION_FID836, CTBTR_BKDG
FROM #TEMPRICSBYPAGE AS STLRICNEW WHERE PK_VALUE > @FirstRec
AND PK_VALUE < @LastRec
Is there anything wrong with the above? When I tried to execute in the query analyzer, EXEC GETRICSBYPAGE 1, 10, it is not giving me anything. Only blank headings. Please help.
Thanks,
Meeram395
modified on Monday, January 21, 2008 3:40:43 AM
|
|
|
|
|
Hi,
Sorry... Please ignore. It got resovled. It is checking with the PK_Value which is starting with 700. So I reset the PK_value. Issue is resolved now.
Sorry for the inconvenience caused.
Thanks,
Meeram395
|
|
|
|
|
I've always counted on a DataReader's RecordsAffected and FieldCount properties to tell me whether or not the command was a SELECT statement:
While the DataReader is open, use FieldCount; when the DataReader is closed, use RecordsAffected.
But I just ran into a snag... DDL statements, I hadn't tested my code with them until tonight.
While the DataReader is open, FieldCount properly indicates that it's not a SELECT statement; but when the DataReader is closed RecordsAffected == -1 so it tests as a SELECT statement!
Has anyone else run up against this and have a solution?
I don't think it'll really cause any trouble because by the time it's a problem the DataReader is closed anyway.
"
IDataReader.RecordsAffected Property
The number of rows changed, inserted, or deleted; 0 if no rows were affected or the statement failed; and -1 for SELECT statements.
The RecordsAffected property is not set until all rows are read and you close the IDataReader.
IsClosed and RecordsAffected are the only properties that you can call after the IDataReader is closed.
"
"
IDataRecord.FieldCount Property
When not positioned in a valid recordset, 0; otherwise the number of columns in the current record. The default is -1.
After executing a query that does not return rows (for example, using the ExecuteNonQuery method), FieldCount returns -1.
"
|
|
|
|
|
Hello I have a column in a datatable which has value
Title Book [Note values are in double quotes only]
"Does 'Grease Money' Speed Up the Wheels of Commerce?"
When I iterate the dataset I get the value as :
"\"Does 'Grease Money' Speed Up the Wheels of Commerce?\""
Hence I am not able to create an valid XML. Any idea how can I remove this \" from string
Regards,
Pavas
|
|
|
|
|
Sounds like code somewheres is adding the \ before you insert into the database.
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
|
|
|
|
|
Is that what you're seeing in the debugger tooltip or watch window in C#? For reasons best known to themselves, they show the string as you would have to write a string literal in the code. The outer quotes and backslashes aren't present, which you can confirm by sending it to Console.WriteLine.
I believe VB.NET shows doubled quote marks for the same reason.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
