|
I am just wondering how to pull the following information from SQLServer. I have a database hosted with SQLServer 2000 featuring a particular set of columns updated to wierd values. AFAIK, my application or any of the subscribed services do not have any updation code in that pattern for the column.
There could only be two reasons, I could apprehend:
1) An accidental Update Query with a broken WHERE clause.
2) New interfaces to our application is giving unwanted interferences.
Any help?
|
|
|
|
|
Hi,
I have three table
1. EmpMst
2. RefDef
3. RefValue
Empmst contains EmpCode, EmpName, DesigCode,DeptCode
RefDef contains Definitions like department, designation etc.
RefValue stores def_code from RefDef table and also stores
uniqueid of value and description.
Means Designation and Department both are stored in on table under
same column.
now i want to get result like
EmpCode, EmpName, Designation, Department.
Problem is how to get unique records.
kiranbanker
|
|
|
|
|
You would want a query like
select empmst.empcode, empmst.empname, refvalue_1.description as designation, ref_value_2.description as department
from empmst inner join refvalue refvalue_1
on empmst.desigcode = refvalue_1.value
inner join refvalue refvalue_2
on empmst.deptcode = refvalue_2.value
This assumes that the values in field RefValue.value are unique to the table. If they are not, you would have to filter the records in refvalue_1 and refvalue_2 by the appropriate def_code.
I'm not sure why you are getting duplicate records though.
Paul Marfleet
|
|
|
|
|
hello sir/mam,
i have got a problem in a query
i have two table student master and feesregister. both table have a
feild named registrationnumber.
now i want those studentmaster.registrationnumber which does not
match the conditions in feesregister
i have tried my query
select studentname from studentmaster where registrationnumber not in(
select studentmaster.registrationnumber from studentmaster
left join feesregister on studentmaster.registrationnumber =feesregister.registrationnumber
where feesregister.feesofmonth='march' and feesregister.session='2007-2008'
)
it works but it fails when i asign more condition
i want this according to feild class like
select studentname from studentmaster where registrationnumber not in(
select studentmaster.registrationnumber from studentmaster
left join feesregister on studentmaster.registrationnumber =feesregister.registrationnumber
where feesregister.feesofmonth='march' and feesregister.session='2007-2008' and feesregister.class='3' and feesregister.section='a'
)
Please help me
|
|
|
|
|
Use a correlated subquery
select studentname
from studentmaster
where not exists
(select top 1 *
from feesregister
where regisrationnumber = studentmaster.registrationnumber
and feesofmonth='march'
and session='2007-2008'
and class='3'
and section='a')
Paul Marfleet
|
|
|
|
|
Hello,
I have a question regarding the tableadapter insert query as automatically generated by the QueryBuilder. I am using VS2005 with SQL Sever 2005.
After having spent some time working on an application I have noticed the following;
In one situation I have a form bound to a table that uses an identity field as its key. When I built the tableadapter using the QueryBuilder I selected the refresh after insert option. As expected, this created an insert query that did not write back the key field so as not to override the table generated identity value. Also, as hoped, immediately after inserting a new record, the form refreshes with the database table generated key value (not a dataset datatable generated value)
However, when I look at the text of the TableAdapter's insert query, there is no select statement following the insert query. Though, if I wave the mouse over the CommandText property on the DateSet screen, the yellow preview balloon shows both an insert and select command.
Can anyone explain this?
Secondly, I was just working on another part of the application - different table, different form. Here too, when creating the TableAdapter I requested a refresh after insert. This table does not use an Identity field key, though it has several table generated fields that I want refreshed back to the dataset immediately after an insert. Here again, the Insert query does not show a following select refresh statement. Again, the yellow popup balloon over the CommandText property shows a select statement. In this case however, when the form is run, the refresh after insert does not happen. I then manually added a select statement following the insert query (the query editor tells me it cannot parse the query, but lets me ignore the fact) and everything works fine. By the way, the yellow pop up balloon over the Insert Query's CommandText property now shows two select statements.
Question: When building a table adapter, when the refresh after insert option is checked, should I or should I not expect to see a Select statement following the Insert query when I look at the actual TableAdapter Query text? Is the Select statement in fact included, but not accessible?
Question: Is it only when one is trying to refresh the value of an Identity based field that one has to explicitly enter a refresh select statement (even though in the first situation sited above, the identity field was refreshed without my having explicitly added a following select statement)?
Anyhow, I'm just trying to find some predictable and logical pattern in how this works.
If anyone can shed some light on the above I would be most thankful.
|
|
|
|
|
As a follow up to my initial post;
Immediately after posting the above, I went back to the second form mentioned in the post and ran it again. This time it did not refresh and when I opened up the insert query for a closer look, noticed that the refresh select statement I had added, and which worked momentarily, was no longer there. The trusty yellow popup balloon is still showing two queries.
Good grief.
|
|
|
|
|
Let us say I have "database.mdf" and I have been using it fine with VS2005. I'm now going to publish the site, but the server I'm on doesn't support attached databases, I can only use thier SQL Server for my database. Because VS2005 doesn't have the ability to create the table scripts I need to load up SSMSE. So I load up SSMSE, attach the database file, generate my table creation scripts and I proceed to publish the site. Now that the site is published I need to add more to the site so I open VS2005 and start editing. When I go to debug the site, the first time I try an access the database I get "Cannot open user default database. Login failed.
Login failed for user 'USER\ASPNET'."
This senario happens everytime I need to do the above. I even tried making a copy of the database before I did anything with SSMSE and still it does this.
Now the odd part to me is the VS2005 can still access the database. I can make changes to the tables, insert, delete, update data, but once I run the website I get this error. 
I have searched for a solution to this problem and while I've found many things relating to this problem. None of the solutions work for me. The only thing I've ever been able to do is uninstall SQL2005 Express, reboot, Reinstall SQL2005 Express, reboot and then it works. But the moment I use SSMSE to do anything with the database it quits working again.
There has got to be some simple explanation as to what the problem is and how to fix it. I however have yet to find it.
|
|
|
|
|
Hi frenz,
I have written an aspx search page which gets the search keyword and process the query thru full text and returns the searched records
But now am unable to retrieve the number of results..
can any of you tell me how to retrieve the no of records fetched thru a
fulltext CONTAINS query??
My Query is :
select * from tablename where CONTAINS(Username,'Keyword')
i need to obtain the no of records..
can any one of you tell me...
urs
sathya
|
|
|
|
|
s.sathyanarayanan wrote: can any of you tell me how to retrieve the no of records fetched thru a
fulltext CONTAINS query??
To get the number of records yielded by
s.sathyanarayanan wrote: select * from tablename where CONTAINS(Username,'Keyword')
you can use @@ROWCOUNT
Hence your query will look like
select * from tablename where contains(username,'keyword')
select @@rowcount
HTH
|
|
|
|
|
hi cs,
thanks for the info..
its working fine..
thanks a ton
urs
sathya
|
|
|
|
|
NP 
|
|
|
|
|
.CS wrote: select *
But can this be diluted a bit. You are just going to discard the resultset and use @@ROWCOUNT anyway. Hence a select * is just going to kill the server in the case of fat tables.
|
|
|
|
|
Hi all,
I have to design a query such that the select * statement should return only those columns which don`t contain a NULL value . If the column contains a null value,the column itself should not be returned by the query.
|
|
|
|
|
John Mecas wrote: select * statement should return only those columns which don`t contain a NULL value
You will have to use COALESCE
For example, if you are having a table named Employee with EmpID,EName,Dept
and the Dept column has NULL values, the following query will NOT return the 'Dept' column.
SELECT COALESCE(EmpID,EName,Dept) FROM Employee
You may take a look at this[^] for more info
|
|
|
|
|
Won't that return only the EmpID though?
|
|
|
|
|
I don't think so, but I'm not sure I understand the question anyway.
All rows returned by a query will have the same columns.
Are you sure you didn't say "column" when you meant "row"?
|
|
|
|
|
Why would you want to design a system like this? I'm not really sure that I understand what you are trying to achieve here.
The only way offhand that I could think to do this would be to create a temporary table and use some form of dynamic SQL to return just the columns that didn't have an entry. It seems a bit useless to me though, because running the same query two days in a row could result in you retrieving columns that you weren't expecting.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I'm not sure why you would want to not return a whole column if one of the rows contained a null value in the column?
So I'm taking a risk and guessing you mean don't return the row if the column contains a null.
In which case:
How about this?
<br />
select name,isnull(occupation,'yadayadayada'),age<br />
from persons<br />
where occupation <> 'yadayadayada'
or
<br />
select name,occupation,age<br />
from persons<br />
where occupation is not null
Both of these should work.
You mention select * so to achieve this you would need to turn the first example into a view, the second example you could just replace the selected columns with an *.
I hope this helps...
You always pass failure on the way to success.
|
|
|
|
|
guys please help me on how to create a local search engine for my website..thank you.. for example, the user can search for the different products that my website is selling..
|
|
|
|
|
LyndonJohn wrote: the user can search for the different products
You can probably have 2 tables
Table1
ProductID
ProductName
Table2
ProductID
Description
Price
When the user wants to search details pertaining to a product, you may map it onto table2 based on ProductID and fetch the corresponding info.
|
|
|
|
|
For what? Local restaurants? Local singles? ... 
|
|
|
|
|
i mean in local website..the user can just search the product within the system...G?
|
|
|
|
|
Hey guys
Not sure if this is the appropriate forum but let me give it a go anyways.
I’m trying to simulate triggers in MS Access 2003 from a .NET platform using C#. Is there anybody out there that perhaps faced the same challenge and if so can you please give me some guideline where to start.
Any background, information of guidelines will be highly appreciated.
Thanks.
R
|
|
|
|
|
hi every body I hope you are all fine.
I have project in asp 2.0 and database oracle10g and i want to preview the fields that in the last 6 month only.
i just want the query in sql thx in advance.
let's work together
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
