Database Discussion Boards

Re: TableAdapter Insert Query - a mystery

dmagnant4-Oct-07 9:57

4-Oct-07 9:57

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.

Problem with local website after using SSMSE to access the database

Sunset Towers4-Oct-07 8:13

Sunset Towers

4-Oct-07 8:13

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'." Mad | :mad:

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. Confused | :confused:

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.

How to fetch the no of records retrieved thru FULLTEXT

s.sathyanarayanan4-Oct-07 8:12

s.sathyanarayanan

4-Oct-07 8:12

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

Re: How to fetch the no of records retrieved thru FULLTEXT

Joe4-Oct-07 9:05

Joe

4-Oct-07 9:05

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

Re: How to fetch the no of records retrieved thru FULLTEXT

s.sathyanarayanan4-Oct-07 9:11

s.sathyanarayanan

4-Oct-07 9:11

hi cs,

thanks for the info..

its working fine..

thanks a ton

urs
sathya

Re: How to fetch the no of records retrieved thru FULLTEXT

Joe4-Oct-07 19:30

Joe

4-Oct-07 19:30

Re: How to fetch the no of records retrieved thru FULLTEXT

Vasudevan Deepak Kumar4-Oct-07 19:43

Vasudevan Deepak Kumar

4-Oct-07 19:43

.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.

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

How do we select only those columns that aren`t null

NetBot4-Oct-07 8:06

NetBot

4-Oct-07 8:06

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.

Re: How do we select only those columns that aren`t null

Joe4-Oct-07 8:57

Joe

4-Oct-07 8:57

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

Re: How do we select only those columns that aren`t null

PIEBALDconsult4-Oct-07 9:32

PIEBALDconsult

4-Oct-07 9:32

Won't that return only the EmpID though?

Re: How do we select only those columns that aren`t null

PIEBALDconsult4-Oct-07 9:38

PIEBALDconsult

4-Oct-07 9:38

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"?

Re: How do we select only those columns that aren`t null

Pete O'Hanlon4-Oct-07 10:48

Pete O'Hanlon

4-Oct-07 10:48

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.

Re: How do we select only those columns that aren`t null

GuyThiebaut5-Oct-07 9:32

GuyThiebaut

5-Oct-07 9:32

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'

<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.

how to create a local search engine?

LyndonJohn4-Oct-07 7:40

LyndonJohn

4-Oct-07 7:40

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..

Re: how to create a local search engine?

Joe4-Oct-07 8:43

Joe

4-Oct-07 8:43

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.

Re: how to create a local search engine?

PIEBALDconsult4-Oct-07 9:46

PIEBALDconsult

4-Oct-07 9:46

For what? Local restaurants? Local singles? ... Big Grin | :-D

Re: how to create a local search engine?

LyndonJohn5-Oct-07 7:24

LyndonJohn

5-Oct-07 7:24

i mean in local website..the user can just search the product within the system...G? Big Grin | :-D

MS Access 2003 and Triggers

Ryno Burger4-Oct-07 7:17

Ryno Burger

4-Oct-07 7:17

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

problem with date calculation

ahmed_fci20064-Oct-07 1:39

ahmed_fci2006

4-Oct-07 1:39

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. Smile | :)

let's work together

Re: problem with date calculation

pmarfleet4-Oct-07 4:16

pmarfleet

4-Oct-07 4:16

How can anyone help you when you haven't given details of your tables/fields?

Paul Marfleet

Re: problem with date calculation

Blue_Boy4-Oct-07 4:22

Blue_Boy

4-Oct-07 4:22

select cast( month(getdate()) as varchar), cast(month(getdate())-6 as varchar)

I Love SQL

group by problem(plz help me)

Arihant rathi4-Oct-07 1:31

Arihant rathi

4-Oct-07 1:31

hi i m ajay rathi..i m facing a problem in group by clause in sql query..plz any one of u help me

The problem is... following are 3 queries and i need to combine all three queries into one query..because this time these queries are giving me data in three table and i am showing this data in repeater in ASP.NET with SQL SERVER 2003..

select count(*)as active from tbl_Post_Job where Company_Id_Fk = 60
and Status = 'Active' and Post_Date between '01/01/2007' and '01/01/2008' group by year(Post_Date), month(Post_Date)

select count(*)as billed from tbl_Post_Job where Company_Id_Fk = 60
and Status = 'Inactive' and Post_Date between '01/01/2007' and '01/01/2008'
group by year(Post_Date), month(Post_Date)

select count(*)as billed , month(Post_Date) as month_date ,year(Post_Date)as year_date from tbl_Post_Job where Company_Id_Fk = 60 and Status='Inactive' group by year(Post_Date), month(Post_Date)

actually the main problem i m facing is this that when i need month and year together and active postion and billed position saperatlly..
actually i need this data in following form...

Date | Active | Billed
------------------------------------
9,2007 | 4 | 8
10,2007 | 7 | 2
12,2007 | 6 | 6
------------------------------------

please if any one of you can help me it's very good for me plzzzzzzzzzzzzzz
thanks

Ajay Rathi
software engineer
NOIDA(UP),INDIA

Re: group by problem(plz help me)

andyharman4-Oct-07 1:40

andyharman

4-Oct-07 1:40

Hi Ajay

Try something like:

select month(Post_Date) as Post_Month,
    year(Post_Date) as Post_Year,
    sum(case when Status = 'Active' then 1 else 0 end) as Active,
    sum(case when Status = 'Inactive' then 1 else 0 end) as Billed
from tbl_Post_Job
where Company_Id_Fk = 60
group by year(Post_Date), month(Post_Date)
order by year(Post_Date), month(Post_Date)

The case expressions allow you to count the number of rows that match their criteria.

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Re: group by problem(plz help me)

Arihant rathi4-Oct-07 3:13

Arihant rathi

4-Oct-07 3:13

thx a lot of thx it's working
u gave me a big support....
but i have one more problem related to this problem...actually when i m using that query which u gave me that is not working when i m using a sub query in that query ...

following is that query..

sum(case when Type_of_view like 'Applicants' and Posting_Id_Fk in
(select Id_PK from tbl_Post_Job where Company_Id_Fk = @Company_Id_Fk and Job_Title like @Job_Title and Job_Type like @Job_Type and Job_Location like @Job_Location ) then 1 else 0 end) as Applicants,

in this query i know the value of @Company_Id_Fk, @Job_Type, @Job_Location ,@Job_Title

Ajay Rathi
software engineer
NOIDA(UP),INDIA

inserting values with a nested join query

anuradha.rrao4-Oct-07 1:18

anuradha.rrao

4-Oct-07 1:18

Hi,

I have 3 tables: City_master(cityid(PK),cityname), zone_master(zoneid(PK),zname,cityid(FK)) and city_x_zone(czid(PK),cityid(FK),zoneid(FK).

I am running a join query to get the cityid and zone id into city_x_zone.the query is something like this:
select a.cityid,b.zoneid from city_master as a inner join zone_master as b on a.cityid=b.cityid where cityname='tb1.text' and zname='tb2.text'

I am running an insert query for city_x_zone like this:
insert into cityzone values(newid(),select a.cityid,b.zoneid from city_master as a inner join zone_master as b on a.cityid=b.cityid where cityname='Bangalo' and zname='south')

this is throwing an error.

What can be the solution to this?

Regards,
Anuradha

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.