|
declare a variable and, after the update,
set @Var = @@Rowcount<br />
Select @Var Records
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks alot, it worked!
And ever has it been that love knows not its own depth until the hour of separation
Mohammad Gdeisat
|
|
|
|
|
Or something that can allow us to select column names from a one-row table where a specific column value exists
I mean, I want to get the column names of a table that has for example the value '1' in the first and only row of that table
Example:
A B C
-- -- --
0 0 1
The output should be:
C
Please tell me if you can make this happen from Sql.
Many thanks guys!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
modified on Saturday, December 13, 2008 3:48 AM
|
|
|
|
|
It could be done but not in 1 select statement.
A combination of a while loop to get the column names from the system info views and a select on the table to test the value of the column could acheive it.
What happens when 2 or more column have a value?
I suspect this is a really badly designed table. I recommend that you look into the data structure if it is possible.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hey Holms,
Mycroft Holmes wrote: I suspect this is a really badly designed table
No, it's "terribly designed" and no, there's no way I can modify the structure
Mycroft Holmes wrote: What happens when 2 or more column have a value?
It will return those Column names just like rows.. like
A
B
C
Thanks for your help though!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
You might look into unpivot, I've never used it though.
Sounds like an 80s legacy system to me
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yah, tell my Holmes, do you know how to transpose columns with rows in a select statement, that should be pretty doable and I see it everywhere when googling but cant apply it
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
I m trying to connect two informix server but problem is that DSN continously chages so that i coudnt connect..plz give me solution...
|
|
|
|
|
I m trying to connect two informix server through .Net but problem is that DSN continously chages so that i coudn't connect..plz give me solution...
|
|
|
|
|
What possible business reason could you possibly have that the servers DSN changes. I can understand an occassional change, say once a decade but "constantly" seems a little weird to me.
As you connectin relys on stable properties I see no way this could be acheived. It would require foreknowledge of the servers name/address
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello.
In my DB exist a filed with stName name.
How can I delete rows that have a same stName except one of them with SQL.
Many thanks in advance.
|
|
|
|
|
Ahh did this last week, I used a combination of rank and partition over to put the results of the IDs and their ranking into a CTE and then deleted all the rows with a rank > 1
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a WinForm on which i have a gridview. After changing i am making this call
int result=employeeObject.UpdateEmployee(ds.GetChanges());
int UpdateEmployee(Dataset ds)
{
int res=da.Update(ds); <<ds contains modified Rows exception occurs here>>
return res;
}
I am getting folowing Exception.
Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
I googled but i am unable to troubleshoot the problem.
|
|
|
|
|
You need to ensure the UpdateCommand is properly set. If you are working with straightforward data and used a SqlCommandBuilder when creating and populating the DataSet , it can usually infer this information. Step into that line and see what the UpdateCommand property of the DataAdapter is.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
What i am doing is.
Populating dataset using SqlDataAdapter.
Then providing this dataset to datagridview.
After doing some changes(deleting a row,changing some cells adding a row) in gridview i get those changes using
ds.GetChanges();
now i am updating the database using adapter's update method.
I am not getting this statement.
You need to ensure the UpdateCommand is properly set.
How can i set the UpdateCommand?
|
|
|
|
|
There is a property of the DataAdapter object named UpdateCommand . This property is a DbCommand that controls the statement that is actually executed to perform the update. You should put a breakpoint in code at the line 'int res = da.Update(ds); ' and check the UpdateCommand property of da . Also, this seems to be more of a C# or .NET question than a database question. In the future you should post this type of inquiry to one of those forums.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Hi,
I am looking for examples of automated report building software that creates reports build of sentences that are located in a database (MySQL). For example, the user should enter certain data into a web interface, and based on that data a report is created that should be readable as if written by a person instead of a computer. Program language is less important, I am looking more for general principles of fluent report building, algorithms about how not to repeat sentence structure that makes it ackward to read for users, etc.
Many thanks,
Steffen Ruefer
|
|
|
|
|
Steffen, I sit here shaking my head in despair at some of the silly questions asked in the forums but this one is a classic.
Please understand something, this is a SUPPORT forum where we help developers to improve their skills and get over problems they have encountered while building their systems. What you are asking for is how to build an application, a very different animal.
I think someone actually has built this and uses it to generate books. He makes a living publishing 1000s of books.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear Team,
i have an requirement that : i want to search all the columns of all the tables in the particular database based on the specific key word or an free text.
example :
table 1: columns data
empname sam
empid 01
table 2 columns data
deptname sam
departmentid 10
table 3 columns data
organization name sam
organization id 1
when i search for text " SAM"
it should search me from the entire database, all tables and columns of it and display the result
output : tablename cloumn value
table1 empname sam
table2 deptname sam
table3 organizationame sam
the example is just an sample not the real data .can this can be happen by using indexes
please help me with sample code or any link related to it .
thanks in advance
|
|
|
|
|
Use system tables. Loop through all the tables based on sysobjects and then loop through each tables columns based on syscolumns . Then try to find the text you want on character columns.
If you're using SQL Server 2005 or above use:
- sys.objects instead of sysobjects
- sys.columns instead of syscolumns
|
|
|
|
|
Thank you very much mika .
i tried that thing but it is taking so much time for execution say for example if i am haiving the database of 400mb it is taking around the 2.25 min for executing.. is there any alternative way for that
|
|
|
|
|
No there's no direct alternative as far as I know. Whta you could do is to create a new keyword table where you store keywords and references to the rows and tables containing them. Kind of an index, if you like.
|
|
|
|
|
Dear Team,
Please help me out by giving any related link or code . for those kind of search .
thanx in advance .
|
|
|
|
|
Mika has told you how to do it, and he has also suggested a good alternative, build a keyword table for indexing.
What else do you expect, someone to do it for you?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi bob,
i know the solution that mika has provided is very good .but we dont know what the user will type and want to search there is no constant keywords predefined so that i can create them as separate table and provide the index.
the user will just type any keyword/word in the text box from the frontend he required and it should search all the tables and columns of the table in the database and i have to show this in the front ent in the table format. here the user will analyse the information based on the searched records .
It is just like the google search we does( type the keyword in free text) it will display the result.
but here it is mandatory for me to search all the table and columns. here different users will update data in different tables . so in order to analyze the data i have to go through all the tables and columns .
it is just like we are investigating and analyzing some thing based on the input string and output it display( just like linking from one record to another record and analyzing and making decisions etc this is based on pure bussiness logic).
thanks in advance and sorry for trouble
|
|
|
|