|
SQL Server does not store rows like a flat file so when you say first row all you can really do is SELECT TOP 1 * FROM TABLE . You can include a WHERE clause though by saying SELECT TOP 1 * FROM TABLE WHERE Name="xyz" .
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that." - Tommy Boy "Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
how to create sp for username and password
-- modified at 23:54 Thursday 8th March, 2007
|
|
|
|
|
make your question more clear
daku1 wrote: how to create sp for username and password
Do you want to create a procedure that can validate user name and password ? Then it could be something like this
CREATE PROCEDURE ValidateProcedure
(
@Username varchar(20),
@Password varchar(20)
)
AS
Select * From UserTable Where UserNameColumn = @Username and PasswordColumn = @Password
GO
Get the result in some reader. If the reader has records then u r login is authenticated. If you want to make your password case sensitive then go through this snippet http://www.w3hearts.com/showsnippet.aspx?show=22
|
|
|
|
|
Hi
Can anybody please let me know that How many tables we can have in sqldatareader. please let me know anybody have the solution. Thanks in advance
Thanks and Regards
Venkat
|
|
|
|
|
I think only one table. Dataset can have more than one table
|
|
|
|
|
Hi,
Thanks for the reply. But i have one doubt please clarify. In datareader there is one method called NextResult(). It retrives the next resultset. can you please clarify how the above mentioned methods works if datareader contains only one table. I am in too much confusion, please clarify my doubt. Thanks in advance
Thanks and Regards
Venkat
|
|
|
|
|
Great!!!!!!! It is possible. Datareader can contain multiple resultsets. NextResult() will advance to the next result. See the MSDN Documentation for NextResult().
Check this article which explains about storing multiple resultsets on datareader.
But since datareader is connection oriented, I prefer using datasets to manipulate with multiple tables.
Any way thank you for bringing out this topic here
|
|
|
|
|
I have written a service (in C#) that reads and writes to an SQL database. At the begining of the service I execute sp_who2 to see if another computer connected to the same database is already running the service and if it is I stop the service. My problem is that the service is not showing up in the results of sp_who2.
Do you have to initialise anything either in the database or my service for it to be displayed by sp_who2?
|
|
|
|
|
You would need to have an open connection to the database for it to show up. Normally you would not want a service to hold open a connection to the database just so you can tell it is running.
Why not just create a sql table that the service can look at?
Hope that helps.
Ben
|
|
|
|
|
I thought of that but if one computer is running the service and crashes the service's state would still be set to running in the table and then no other computer would be able to run that service until the state was manually reset in the database.
|
|
|
|
|
Well, if it is that bad, then perhaps you should have the service on a regular basis insert a new record, so you know it is running. That way if the last record that was inserted was more then whatever time interval you decide on, you can have some certainty that the service has failed. Not a great solution, but it might work for you.
Ben
|
|
|
|
|
i'm working in company , and asked to develop an application to run in server and 4 clients , i just apply for SQL server 2000 and may use visual basic as GUI , but i need some steps to arrange ideas and make it easir for me to implement this project
many regards
|
|
|
|
|
please i need help !!!!! Please Read the guidelines provided on the top of forum before post
Make your question more clear to get good results
|
|
|
|
|
hi..
i needed help regarding an sql query..
i have an column.. containing alphanumeric values in this format.. no spaces..
1213334Alsakasd12334
653453asdgsdgghj354
i want it to be split in to this type of a format as shown below
colum1 column2
12312334 alksaksdf
6543534 wqeqweqe
is this possible using sql in the first place.. if yes.. could anyone please help me out..
Kunal Piyush
|
|
|
|
|
Is there any particular length? Try the site below to see if the substring option will work but that requires that you know how much of the string you need.
SQL Substring Command[^]
SELECT SUBSTRING(ColA, 10) AS ColumnA, SUBSTRING(ColB, 10) AS ColumnB FROM TABLE
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that." - Tommy Boy "Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
the numeric length is variable..
i tried this
Select left('123Delhi',Patindex('%[A-Z]%','123Delhi')-1 )Add1 ,
substring('123Delhi',Patindex('%[A-Z]%','123Delhi'),Len('123Delhi')) Add2 from address
but i want that the pattern applies to all the columns.. also..... the result appears 4 times...mean 4 rows.
Kunal Piyush
|
|
|
|
|
What is your opinion about writing dynamic SQL statements in stored procedure ?
Suppose I am writing something like this
Set @SQL = 'select * from table'
Exec (@SQL)
is it a good practice ?
|
|
|
|
|
Navaneethkn wrote: What is your opinion about writing dynamic SQL statements in stored procedure ?
Don't use them unless you have to, and if you have to use them be very careful as dynamic SQL has some seriously bad consequences for security if used badly.
Take a look at SQL Injection Attacks and Tips on How to Prevent them[^]
Navaneethkn wrote: Set @SQL = 'select * from table'
Exec (@SQL)
is it a good practice ?
For something as simple as that, no.
If you didn't know the name of the table in advance, then okay - but you should check that the table actually exists prior to building the dynamic sql and ensure that the table name is wrapped in square brackets: [LikeThis]
|
|
|
|
|
Sometimes it seems to be unavoidable so you have to do a risk assessment.
If you are web facing forget follow Colin's good advice.
If you are intranet or backend system with no user front end supplying parameters then you can prob take the risk.
Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)
http:\\www.frankkerrigan.com
|
|
|
|
|
Frank Kerrigan wrote: If you are intranet or backend system with no user front end supplying parameters then you can prob take the risk.
However, do keep in mind that the majority of system compromises are insider jobs.
|
|
|
|
|
Thank you. I have the following situation
I have 9 fields some thing like first name, last name, age .... I need to search on these fields. I provided 9 textboxes. If first name and last name is filled then I need to check only that. Then my query will be something like this
select * from table where firstname like 'value' and lastname like 'value'
If user entered firstname and age then query should be generated according to that. So in that case any solution rather than going for dynamic queries ?
|
|
|
|
|
A parameterised query or stored procedure can be used
SELECT FirstName, LastName, Age, ...
FROM MyTable
WHERE (FirstName = @FirstName OR @FirstName IS NULL)
AND (LastName = @LastName OR @LastName IS NULL)
AND (Age = @Age OR @Age IS NULL)...
In your application:
if (string.IsNullOrEmpty(firstNameTextBox.Text))
myCommand.Parameter.Add("@FirstName", DbNull.Value);
else
myCommand.Parameter.Add("@FirstName, firstNameTextBox.Text);
// etc.
|
|
|
|
|
My MaintenanceLog table has an entry with following data in a row.
Name: Toney Mirra
ActualDateTime: 2007-02-16 12:55:02.867
PlannedDateTime: 2008-12-05 20:08:57.000
Comments: Sample Entry
But when I run the following query it doesnt return any value.
I think there is some problem in DateTime format. Can anyone help me fixing this?
select Comments from MaintenanceLog where
ActualDateTime = '2/16/2007 12:55:02 PM' and
PlannedDateTime = '12/5/2008 8:08:57 PM'
|
|
|
|
|
Try this
select Comments from MaintenanceLog where
convert(smalldatetime,ActualDateTime,101) = '2/16/2007' and
convert(smalldatetime,PlannedDateTime,101) = '12/5/2008'
|
|
|
|
|
Hi,
It's "Normal" because the milliseconds value of ActualDateTime is 868 and u are searching for '2/16/2007 12:55:02.000.
Two ways to fix this issue :
1- Always store 000 milliseconds in the database.
If u re using DateTime.Now function in .net set the milliseconds value to 0
If u re using GetDate function in sql, u can build the date without milliseconds using year, month, day, hour, ... functions.
2- modify ure search criteria to this :
ActualDateTime >= '2/16/2007 12:55:02' and ActualDateTime < '2/16/2007 12:55 3'
HTH.
|
|
|
|