|
Hi,
I am having problem. I want to sql query to insert a row, but before that i want update in the same table with a condition.
for eg: i am having a table, in that there is a field named default. this default value must true for only one row based on the foreign key. whn i insert a row for tht foreign key, before insert it should check is the default value is true before for that foreign key. if yes then i have to reset that old default value to false and set the new one to true.
id name isdefault fkey
1 aaa true 12
2 aas false 12
3 aer false 12
4 sdf true 17
5 ter false 32
6 zxc false 32
in the above example if i enter row for fkey 12, then the query should reset the previous all default value to false and then inset the new value to true(if the new row have true value) value to new row.
if there is no default true value thn it shold enter the true value to new row.
any one please suggest me a query.
thanks in advance.
Know is Drop, Unknown is Ocean
|
|
|
|
|
Run an UPDATE query against your table before the insert to set the value of 'isdefault' to false for all records with the same 'fkey' value as the record you are inserting. Then run your INSERT query to insert your new row with an 'isdefault' value of true.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
hello once again, haven't solved the tableName problem that i posted a few minutes ago but want to ask one more thing.. .is it possible to have different number of parameters for an sql stored procedure... wat i mean is that is it possible to specify the columns that needs to be selected at run time for a stored procedure...
if so plz provide help
haseeb
|
|
|
|
|
I don't think there is a way to have a variant number of parameters in a sql stored procedure. However you can pass a list via 'array', Check the following article. It might haelp you to get what you want.
http://www.sommarskog.se/arrays-in-sql.html
This may help you too:
http://www.sommarskog.se/share_data.html
|
|
|
|
|
hello
i wanted to make a single stored procedure which takes as parameter the table name from which all the rows should be selected ... in query analyzer i tried
declare @var varchar(30);
set @var='tableName';
select * from @var;
but it won't work... the variable declaration and assignment works fine, but the select statement doesnt work...
Please help in this regard... i also tried making
declare @col_name varchar(30);
set @col_name='studentName';
select @col_name from studentTable;
it works but the rows returned have col_name as it's value and the original value doesn't appear
Plz help
haseeb
|
|
|
|
|
You have to use Dynamic EXECUTE or sp_executesql (recommended). Check Books Online sp_executesql topic. It has a good example.
|
|
|
|
|
Hello everyone,
I'm working on a project that requires extensive searhing and I want to implement demand paging technique for displaying data in the gridview. I've read a few articles showing how to implement the retrieval of n to m rows from Oracle DB but I'm using SQL Server 2005. Can anyone plz tell me how I can implement this functionality.
Thank you
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
|
I have not used what is in the ROW_NUMBER() topic of SQL2005 BOL but it should give you some ideas.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/82fa9016-77db-4b42-b4c8-df6095b81906.htm
modified on Thursday, February 07, 2008 12:23:52 PM
|
|
|
|
|
I have a dataset/datatable that has a column 'name'. The data may have accents as in 'João'.
I need to search for Joao (no accent) and get rows including João.
row 1 - Joao Pedro
row 2 - Maria João
row 3 - João Antonio
datatable.Select("name LIKE '%Joao%'") should return all 3.
|
|
|
|
|
Hi,
I need some help with an SQl statement please. Can someone please adapt the following statement to not only retreive the top 10 results but also return a counr of the total records in the DB.
<code>SELECT TOP 10 * FROM [tbl_Gallery] WHERE ([gal_Cat] LIKE '%' + @gal_Cat + '%') ORDER BY [gal_Id] DESC</code>
Thanks!!!
|
|
|
|
|
SELECT TOP 10 *, (SELECT COUNT(*) FROM [tbl_Gallery]) FROM [tbl_Gallery] WHERE ([gal_Cat] LIKE '%' + @gal_Cat + '%') ORDER BY [gal_Id] DESC
is this what you wnat?
|
|
|
|
|
Nearly, how do i recall the COUNT value?
|
|
|
|
|
you can assigne any name to the count
SELECT TOP 10 *, 'anything'=(SELECT COUNT(*) FROM [tbl_Gallery]) FROM [tbl_Gallery] WHERE ([gal_Cat] LIKE '%' + @gal_Cat + '%') ORDER BY [gal_Id] DESC
|
|
|
|
|
Hello
Its not like I am giving any assignment work to the genius people here.
I want some brainstorming and so asking this question.
Server – 1 (MySQL DB installed)
Client – 12 ~ 20 (practical condition, ideal condition may be more than 20) (MySQL DB installed)
Client application writes values to the server DB the local DB is updated immidiately. DB name is unique across client machines.
what are the different methods to do that.
Thanks
Regards
Anil
|
|
|
|
|
Is it necessary for each client to have a local copy of the database? Could they not just connect to the remote database?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi
I have a website which is sitting on a web server
I am trying to connect to sql server but it shows the following message:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
However I can remote desktop into sql server
Does this mean that there is another problem with the connection or do i have to configure sql server to allow remote connection from sql server
thanks!!!
|
|
|
|
|
You need to configure SQL Server so that it accepts remote connections. Try googling and you will find solution.
|
|
|
|
|
Hi
I cannot find the following location
Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
I cannot find configuration tools, is it named differntly?
thanks
|
|
|
|
|
Hi
I Managed to find it, it is in a different position to sql 2005.
However the problem is still there can you suggest anything else?
thanks
|
|
|
|
|
Make sure sql server is running
|
|
|
|
|
yes sql server is running, anymore advice that is actually useful?
|
|
|
|
|
Jamie1985 wrote: anymore advice that is actually useful?
Yeah, don't talk to people like that. What you said was pretty rude. Good luck getting help with your problem after that.
I'm going to become rich when I create a device that allows me to punch people in the face over the internet.
"If an Indian asked a programming question in the forest, would it still be urgent?" - John Simmons / outlaw programmer
|
|
|
|
|
i'm not worrying about not getting help, no one is replying anyway even before 'the comment'
|
|
|
|
|
I have had that problem before. You want to know what I did? I plugged that error message into google, and it gave me tons of results. I found the solution that applied to me, and I fixed it. Maybe you should try the same.
I'm going to become rich when I create a device that allows me to punch people in the face over the internet.
"If an Indian asked a programming question in the forest, would it still be urgent?" - John Simmons / outlaw programmer
|
|
|
|
|
Thanks for the reply
However I have spent all day looking on the net
I have configured the TCP/IP and Named Pipes so they are enabled
I ahve also tried editing the firewall on my computer
I keep getting the same error, I have only recently put the site on a web server and it worked fine before on citrix server
I cannot think of anything else hence why im posting and mad
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
