Error With A Simple JOIN Query....

Apr 12, 2007

Hi!



I've a big problem by using the following query :






Code Snippet

public SqlCeResultSet selectRSQuery(String query)

{

SqlCeResultSet resultSet = initializeCommand(query).ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

return resultSet;

}



SqlCeResultSet resultSet = sgb.selectRSQuery(

"SELECT p.pId, p.pLogin FROM Profiles p, ProfilesGroups pg, Groups g " +

"WHERE g.gId = pg.tpGroupId " +

"AND p.pId = pg.tpProfileId " +

"AND g.gProfileID = '" + app.Settings.Default.id + "'");



It return me this error :

Cannot generate an updatable cursor for the query because there is a non-standard join expression.



What can I do??



Thxx

View 9 Replies


ADVERTISEMENT

Best Way To Do A Simple Join Query

Feb 28, 2007

ive seen so many ways to do this, including some using cursors (strange i know)

but i have tableA and tableB i want to show fields from tableA which don't apear in tableB

what is the MOST efficient way to do this

View 2 Replies View Related

Simple Query, INNER Join Problem

Sep 14, 2006

In a single table I have 2 columns. Date | Number2006/09/01 - 2352006/09/03   - 2452009/08/01 - 230 I want to write a query that will return the AVG number between two dates.  I am assuming this will require a JOIN but I'm having problems implementing my solution.  I think about it, it's probably not a join but a sub query...I was trying the following: SELECT Date, AVG(Number) as AVERAGE_NUMBER
FROM test.Table
WHERE ( Date>='09/01/2006' AND DATE<='09/04/2006' )  But I keep getting date is invalid in the select list because it is not contained in either an aggregate funtion or a group by clause.  Thanks in advance for your help. 

View 2 Replies View Related

Simple JOIN, INTERSECT Query

Apr 25, 2006

Hi,We are in the process of buying a new server to run mssql. Howeverbefore this as a tempory fix to using a msaccess backend i believethrough odbc i need to address the following issue:SELECT ai.entry_date as CallTime,ai.agent_login as AgentsLogin,ai.campaign as MarketingCampaign,ai.agent_input2 as ProductsSold,ai.first_name as Cust_FirstName,ai.last_name as Cust_LastName,ai.agent_input1 as Cust_PersonalNumber,ai.street_address as Cust_AddressStreet,ai.city as Cust_AddressCity,ai.state as Cust_AddressState,ai.zip as Cust_AddressZIP,rec.file_name as AgreementRecordingFileFROM agent_input ai, leads l, recordings recWHERE ai.whole_phone_number = l.whole_phone_number ANDl.call_status = 1110 ANDrec.whole_phone_number = l.whole_phone_number ANDrec.last_name = l.last_name ANDrec.agent = ai.agent_login ANDrec.campaign = l.campaign ANDlast_call_date between #04/24/2006 12:00 AM# and #04/25/2006 11:59 PM#ORDER BY ai.agent_login, ai.entry_dateI want to make the recordings entry optional so the same results comeout whether it matches a recording or not. If it does i want it topopulate the AgreementRecordingFile column above, if not just put a ''as you would with '' as AgreementRecordFile.Does anyone know how you can do this, in a access based database systemusing SQL through i believe ODBC?ThanksDavid

View 1 Replies View Related

Error While Executing Simple Query :(

Dec 21, 2007

Hi,

I am using this query to execute but it didn't

declare @AccountID numeric(3,0)
set @AccountID = 101
select * from AccountTest
WHERE AccountID IN
(CASE
WHEN @AccountID = 101 THEN (100,101)
ELSE @AccountID
END)

I m getting error like

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ','.

Pls help me out

T.I.A

View 3 Replies View Related

Error Msg While Running Relatively Simple Query (large Result Set)

Jul 17, 2007

While running query below on SQL Server 2005 (Build 3790: Service Pack 2):



SELECT DISTINCT pkg.PrimaryBarcode

FROM dbo.Package AS pkg (NOLOCK)

JOIN dbo.PackageCycle AS pc (NOLOCK)

ON pkg.PackageKey = pc.PackageKey

WHERE (pkg.BillCycleDateKey >= 20061201) OR

(pkg.BillStatusKey = 1)



I received a partial result set followed by

An error occurred while executing batch. Error message is: Couldn't replace text



I suspect this is a memory issue, but cannot find any reference to this particular msg on the Microsoft forums or the other 3rd party forums. PrimaryBarcode is a varchar(50)



I am not sure where to go from here. I would appreciate any ideas. Thanks in advance.



Cheers,

Mike Byrd

View 2 Replies View Related

INNER JOIN Query - Conversion Error

Feb 6, 2015

Trying to run a query to get total number of direct mail campaigns by company. Would like the totals to be (1) total number of direct mail campaigns (there are 2 types) (2) total number of direct mail campaigns type direct mail (3) total number of direct mail campaigns type publication

Below is the query I put together with INNER JOIN:

SELECT Agency.PrimaryAgencyName, COUNT(Distinct Campaigns.DirectMailID) AS TotalCampaigns, COUNT(Distinct DirectMail.Type) AS TotalDirectMail

FROM Agency
INNER JOIN (Clients
INNER JOIN (Campaigns
INNER JOIN DirectMail
ON DirectMail.DirectMailID = Campaigns.DirectMailID)
ON Clients.ClientID = Campaigns.CampaignID)
ON Agency.AgencyID = Clients.AgencyID

GROUP BY Agency.PrimaryAgencyName, DirectMail.Month, DirectMail.Type
ORDER BY Agency.PrimaryAgencyName

When I run the query, I recent the below error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '1683 1-15017' to data type int.

The value mentioned in the above error message is the ClientID.

Below are the tables for reference (bold for entity name and underline for PK) and datatypes

Agency
AgencyID - nvarchar(255)
PrimaryAgencyName - nvarchar(255)

Campaigns
CampaignID- int
ClientID- nvarchar(255)
DirectMailID- nvarchar(255)

Clients
ClientID - nvarchar(255)
AgencyID - nvarchar(255)

DirectMail
DirectMailID - nvarchar(255)
Campaign - nvarchar(255)
Type - nvarchar(255)
Month - float

View 1 Replies View Related

Left Join Insert Query Error

May 8, 2008

I have been working on this for a little bit and have gotten to this point. Below is the query in blue, the error in red. Now, from what I gather the error is telling me I can't insert a duplicate product code into tblProduct. Isn't that what the left join is exactly not doing, as in, the left join is inserting all records from Complete_records into tblProduct where the code is null(does not exist) in tblProduct? Or, is this an issue where the identity number, productID in tblProduct, isn't starting at the next number in turn?

Thanks.

Set Identity_Insert tblProduct on
DECLARE @MaxId int

SELECT @MaxID=MAX(productID)
FROM tblProduct

SELECT IDENTITY(int,1,1) AS ID,Complete_products.APNum, Complete_products.Title, Complete_products.CategoryID, Complete_products.Mountable, Complete_products.price,
Complete_products.Height, Complete_products.Width, Complete_products.IRank, Complete_products.frameable, Complete_products.Typ INTO #Temp
FROM Complete_products LEFT OUTER JOIN
tblProduct AS tblProduct_1 ON Complete_products.APNum = tblProduct_1.productCode
where tblProduct_1.productCode IS NULL

INSERT INTO tblProduct
(productID,productCode, productName, productNavID, CanBeMounted, productRetailPrice, productHeight, productWidth, Rank, CanBeFramed, ProductType)
SELECT @MaxID + ID, APNum, Title, CategoryID, Mountable, price,
Height, Width, IRank, frameable, Typ
FROM #Temp

(236752 row(s) affected)
Msg 2601, Level 14, State 1, Line 13
Cannot insert duplicate key row in object 'dbo.tblProduct' with unique index 'IX_tblProductt_productCode'.
The statement has been terminated.

View 18 Replies View Related

Simple Join Not So Simple

Feb 21, 2007

I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.

result set

colA colB colC colD colE colF colG colG (from yesterday)
1 1 a b c d e m
1 1 a b c d m e

So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.

Any help is appreciated.

View 7 Replies View Related

Simple Join Not So Simple

Aug 19, 2006

Hi,

I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)

I have:


Master table Hotel

table AddressCrossRef (with: RefID = Hotel.ID, RefType = 'Hotel', AddrID)
joins
table Address (key = AddrID)


table MediaCrossRef (with RefID = Hotel.ID, RefType= 'Hotel', MediaID)
joins
table Media (with MediaID,mediaType = 'thumbnail')


foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)

however not all hotels have thumbnail image

hence i have hotel inner join AddressXReff inner join Address ..... however i must have
left outer join mediaXref left outer join media


the problem is that if there is no entry in Media or mediaXref, I don't get any results

i tried to get over it by using
where (media.mediaTyple like 'thumbnail' or media.mediaType is null)
but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned


any clue?

thanks

View 5 Replies View Related

Help With Simple Join?

Sep 3, 2004

I don't know if it's Friday or what, but I can't for the life of me come up with an easy way to do this:

I have 3 tables I want to join:

Sale Table:
Sale_No Cus_No Sale_Qty
1 Joe01 250

Order Table:
Ord_No Sale_No Order_Qty ShipToCode
1 1 20 DestA
2 1 20 DestA
3 1 20 DestA
4 1 20 DestB
5 1 20 DestB

ShipTo Table:

Cus_No ShipToCode ShipToName
Joe01 DestA Philadelphia
Joe01 DestB Chicago
Bob01 DestA Boston


A sale for say 100 tons would have 5 orders (each for 20 tons) associated with it by Sale_No. Each of those orders can go to a different ShipTo destination. Since only the ShipTo Code is stored in the Orders table, I need to get the ShipToName. However, As demonstrated in the example table above, the key in the ShipTo table is both Cus_No AND ShipToCode.

I want a list of Sales and Orders, which is an inner join on Sale_No, piece of cake. However, I then need to use the ShipTo table to go from the ShipToCode to the ShipToName. Unfortunately, Cus_No is not in the Orders table, it is back in the Sales table (proper normalization is a pain sometimes).

What I came up with is this, but is this correct?:


FROM Sales INNER JOIN
Orders ON Sales.sale_no = Orders.sale_no INNER JOIN
ShipTo ON Orders.ShipToCode = ShipTo.ShipToCode AND
Sales.cus_no = ShipTo.cus_no

View 12 Replies View Related

JOIN Simple Problem

Sep 20, 2005

Hi Guys,

I have the following table called VMailMessages:


PHP Code:




 MessageNum  MailboxNum  State
========================
1                 100             1
2                 101             1
3                 101             1
4                 102             0 






Which is of messages in a mailbox system, the MessageNum is my primary key, MailboxNum indicates which mailbox it is for and State indicates whether it is 'New' (value = 1) or 'Saved' (value = 0).

What I want to do is write a query to obtain a list of mailboxes, along with how many New and how many Saved messages they have, producing a result table like this:


PHP Code:




 MailboxNum  NewCount  SavedCount
===========================
100             1             0
101             2             0
102             0             1 






My problem is I cannot seem to get my SQL right, so far I've got:


PHP Code:




 SELECT NewQuery.MailboxNum, NewQuery.NewCount, SavedQuery.SavedCount 
FROM (SELECT MailboxNum, COUNT(1) AS NewCount
          FROM VMailMessages
          WHERE (State = '1')
          GROUP BY MailboxNum) NewQuery 
FULL OUTER JOIN
         (SELECT MailboxNum, COUNT(1) AS SavedCount
           FROM VMailMessages
           WHERE (State = '0')
           GROUP BY MailboxNum) SavedQuery 
ON NewQuery.MailboxNum = SavedQuery.MailboxNum 






WHich works only if the mailbox has New messages as well as Saved messages. For mailboxes with only Saved messages, the count appears but, the MailboxNum is NULL. The opposite occurs if I change my SELECT clause to ask for SavedQuery.MailboxNum, but I really want both.

Can anyone help me?

Thanks

Richard

View 1 Replies View Related

Simple JOIN Question

Mar 28, 2007

I feel like this is an easy question, but I can't describe it well enough to find the answer I need by searching. Anyway, in my spare time (I'm definitely not a SQL Query pro) I'm putting together a small app for our local Little League to help with scheduling.

I have 2 tables I need to join:

T1 = Schedule
has the following fields:
ID
hTeamID (ID of Home team)
vTeamID (ID of Vistor team)
Time
Date

T2 = Teams
ID
Name
Other info...

I want to be able to do a SELECT statement on the schedule table and JOIN the team names for both home and visitor. I've tried a bunch of different ways but keep getting errors. I can think of 100's of reasons to join the same table more than once, but I still don't know how to and can't find the answer online.

Thanks in advance.

View 2 Replies View Related

T-SQL (SS2K8) :: Simple Self Join

Aug 6, 2014

I have listed two tables table 1 has some data. I have to update table 2 [reports] column from table 1 [reports] using self join..I should get as table 2 after updating

View 1 Replies View Related

Join In A View - Simple Question

Jan 19, 1999

I've got a simple ( I think) question on views. I've got a view that has a table join in it.
With this view, we want to be able to perform updates, inserts, and deletes. At this time
we can do the updates and inserts, but not deletes. I've checked the permissions and
the users have SELECT, INSERT, UPDATE, and DELETE. Am I missing something or are
deletes just not possible in a view with a join?

CREATE VIEW update_bd_view
AS select
D.BD_ID, D.BD_DESC, T.BT_TYPE_TID, T.BT_TYPE_FID, T.BT_JOB_FID
FROM BILLING_DESC D JOIN BILLING_TIME T ON D.BD_ID=T.BT_ID
GO

thank you for your time!
Toni Eibner

View 1 Replies View Related

Simple Question Regarding Outer Join (was SQL Help)

Sep 4, 2006

Hi Everyone,

I have a simple question regarding outer join.

Please see the attached word file. It has screen shots of the query I am running. My first query shows the result where i have M.ReservationID = MA.MeetingID and it counts NoofRSVP (# of times the query runs). I have to modify first query in such a way that it returns records from eCDReservations table even if there is no matching MeetingID in MeetingAttendees table (means Null, see the result of 2nd query in attached file). So in my result for that case NoofRSVP column should show either Null or 0.

View 3 Replies View Related

Simple Outer Join Question

May 31, 2007

Ok here is the situation. I have 2 tables.

Movies
MovieID Title Year
21 A Beautiful Mind 2002
22 Forrest Gump 1994
23 The English Patient 1999


Actors
ActorID MovieID Name
1 22 Tom Hanks
2 21 Russell Crowe
3 23 Ralph Fiennes
4 NULL Nachiket Mehta

Here is the SQL Query.

SELECT ActorID, Name, Title, Year FROM Actors LEFT OUTER JOIN Movies ON Actors.MovieID = Movies.MovieID

Now, I only want to show movies made in 1990's and display all 4 actors. If I put

WHERE Year < 2000

it won't show the fourth actor because he doesn't have any movies. I need to show all 4 actors here and NULL for movies if they don't have any.

Hope this makes sense. Thank you.



Nachiket

View 3 Replies View Related

Simple OUTER JOIN (I Thought)

Sep 11, 2007

Two tables:FruitfruitID, fruitNameBasketbuyerID, fruitID(ie. we can see which buyer has what fruit in their basket)I simply want to display all available fruit and whether or not it'sin a specific persons' basket.SELECT Fruit.fruitID, Fruit.fruitName, IsNull(buyerID, 0)FROM Fruit INNER JOIN Basket ON Fruit.fruitID = Basket.fruitIDWHERE Basket.buyerID = 12but this just gives me what's in buyer 12s' basket.What am I doing wrong? Am I a basket case...

View 2 Replies View Related

Strange Result From A Simple JOIN

Sep 18, 2007



I am currently studying Transact SQL and playing around with queries from a sample database. Recently I created the following query.


USE MemtrackSQL

SELECT m1.MemberID, m1.Surname, m1.FirstName, m1.DateOfBirth

FROM tblMember m1 JOIN tblMember m2

ON m1.FirstName = m2.FirstName

WHERE m1.MemberID <> m2.MemberID


This simple query is designed to show all members with the same first name as other members. The result I got shows duplicates of existing members an inconsistent number of times even though I specified not to show duplicates with WHERE m1.MemberID <> m2.MemberID


2 Scharenguivil Rodney 1958-06-24 00:00:00.000
2 Scharenguivil Rodney 1958-06-24 00:00:00.000
2 Scharenguivil Rodney 1958-06-24 00:00:00.000
5 O'Grady Patrick 1975-09-23 00:00:00.000
7 Greenfield Lynne 1955-07-26 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000


Any help in explaining where I have gone wrong here would be greatly appreciated.

Cheers

View 3 Replies View Related

Deceptively Simple Join / Select Question

Mar 24, 2004

Ok, I have two tables with a child/parent or one -> many relationship:

parent_table:
pid int primary key
pname varchar

child_table:
cid int primary key
pid int
cname varchar

Say the contents of these two tables are:

parent_table:
pid pname:
1 Ben
2 Jesse
3 Michael

child_table
pid cid cname
1 1 ben_Child1
1 2 ben_Child2
1 3 ben_Child3
2 4 jesse_Child1
2 5 jesse_Child2
2 6 jesse_Child3
3 7 michael_Child1
3 8 michael_Child2
3 9 michael_Child3

Now what I would like to be able to do is:

select pname, cname
from
parent table a,
child_table b
where a.pid = b.pid

Except! Instead of getting the results in the form of:

Ben ben_Child1
Ben ben_Child2
Ben ben_Child3
...

I would like them in

Ben ben_Child1 ben_Child2

Now normally this would be impossible (I think) since the query would return an unknown number of columns. But in this case I only care about the FIRST TWO children for each parent. So I'm sure there's some way to do this with a simple select, but I don't know how. Anyone?

View 6 Replies View Related

Simple Group By And Count With Join Not Matching Between Sql Server And Sql CE

Apr 2, 2007

In Sql Server




Code Snippet

CREATE TABLE t_contact

(

Id uniqueidentifier,

FirstName nvarchar(50),

LastName nvarchar(50),

TaskId uniqueidentifier

)

GO

CREATE TABLE t_task

(

Id uniqueidentifier,

Start datetime

)

GO



INSERT INTO t_task (Start, Id) VALUES ('3/25/2007 12:00:00 AM', '5949b899-3230-4d30-b210-9903015b2c6b')

INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Adam', 'Tybor', '5949b899-3230-4d30-b210-9903015b2c6b', '304fc653-d366-404b-878d-9903015b2c6f');

INSERT INTO t_task (Start, Id) VALUES ('4/1/2007 12:00:00 AM', '4bd2df60-ca6c-493d-8824-9903015b2c6f')

INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('John', 'Doe', '4bd2df60-ca6c-493d-8824-9903015b2c6f', '7b91f7d6-d71e-47b4-a7ec-9903015b2c6f')

INSERT INTO t_task (Start, Id) VALUES ('3/29/2007 12:00:00 AM', '05167e74-cf63-452a-8f25-9903015b2c6f')

INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Jane', 'Doe', '05167e74-cf63-452a-8f25-9903015b2c6f', '6871ee8d-bc83-478c-8a7c-9903015b2c6f')

GO

SELECT task1_.Start as y0_, count(this_.FirstName) as y1_ FROM t_contact this_ inner join t_task task1_ on this_.TaskId=task1_.Id GROUP BY task1_.Start

GO





Result (Expected)

2007-03-25 00:00:00.000 1
2007-03-29 00:00:00.000 1
2007-04-01 00:00:00.000 1



Result In Sql CE (UnExpected)

2007-03-25 00:00:00.000 3
2007-03-29 00:00:00.000 3
2007-04-01 00:00:00.000 3



Can SQL CE not count with a join? Seems like this a bug with aggregates or joins. I tried everything to try and get the correct result but no luck.



Thanks Adam

View 3 Replies View Related

Multi-table JOIN Query With More Than One JOIN Statement

Apr 14, 2015

I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.

For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.

The carid (primary key) and carmodelname belong to the Cars table.
The makeid and orderdate belong to the OrderDetails table.
The productname and carcategory belong to the Product table.

The number of rows returned should be the same as the number of rows in OrderDetails.

View 2 Replies View Related

Why Does My Query Timeout Unless Force Join To Hash Join?

Jul 25, 2007

I'm using SQL Server 2005.



A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).



The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).

If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.



If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.



So, this works:

SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC



and this works:

SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC



But this does't:

SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC



What should I be looking for here to understand why this is happening?



Thanks,

john















View 1 Replies View Related

SQL Query Question - JOIN Or Not To JOIN

Jan 2, 2006

I have 2 tables, I will add sample data to them to help me explain...Table1(Fields: A, B)=====1,One2,Two3,ThreeTable2(Fields: A,B)=====2,deux9,neufI want to create a query that will only return data so long as the key(Field A) is on both tables, if not, return nothing. How can I dothis? I am thnking about using a 'JOIN' but not sure how to implementit...i.e: 2 would return data- but 9 would not...any help would be appreciated.

View 3 Replies View Related

Working Since Morning, Simple Error But Unable To Resolve, ERROR : 26 , Please Help

Dec 15, 2006

dear friends,
 i started using Asp.net for developing webparts, web parts automatically connect to a database to be created and saved in the database.
i had already Sql Server 2005 Express Edition installed so my webpart page ran and automatically created the database "AspNetDb" in the "App_Data" folder.
but when i uninstalled Sql Server 2005 Express Edition and then installed the Sql Server 2005 Enterprise Edition, it gave me the following Error 26 as below :

--------------------------------------------------------- Beginning of Error 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. SQLExpress database file auto-creation error:
The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:

If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist.
If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database.
Sql Server Express must be installed on the machine.
The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts.
--------------------------------------------------------- End of Error Message -------------------------------------------------- 
then when i checked the Machine.config file, i found this....
  <connectionStrings>    <add name="LocalSqlServer" connectionString="data source=.SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />  </connectionStrings>
then i thought it's becoz, of the datasource thing that the Machine.Config file supports by default the default free edition of Sql Server 2005 which is the Sql Server 2005 Express Edition.
but friend in my company we use Sql Server 2005 Enterprise Edition, so can anybody pls guide me and get me out of this soup on helping me to use Sql Server 2005 Enterprise Edition by modifying either 1) Machine.Config File and/or  2) Web.Config file and/or 3) any other way, but please help i want to do this, please......
regards and thanks
Gurjit Singh 

View 2 Replies View Related

Help With A Simple Query

Oct 1, 2007

I am trying to make a single display page for an author's books.the books page only displays books of a type "type" (novels, non-fiction, etc)I would like to make it so that it can also show all books if "type" isn't selected.  I THOUGHT the string would look like this: <asp:SqlDataSource ID="SqlDSBooks" runat="server" ConnectionString="<%$ ConnectionStrings:csK2Reader %>"
SelectCommand="SELECT * FROM [Books] ( If @Type <> "" then WHERE ([Type] = @Type)) ORDER BY [SortDate] DESC"> But it doesn't seem to want to work.  I get a "server tag is not well formed" error. 

View 2 Replies View Related

I Need Help With A Simple Query

Jan 7, 2008

I have a table called "member" that has two fields (amongst others) called "firstname" and "city"I want to return a list that sorts people who live in the same city, but does not return people who do not have a city in common.For example if this is my data:Karen - ParisMike - RomeTim - Dallas

Jim - ParisChris - DallasJohn - ManhattanSarah - OrlandoDavid - DallasThe query would return this:Dallas - ChrisDallas - DavidDallas - TimParis - JimParis - KarenI have this SQL so far:  Select city, firstname From member Order By city, firstnamebut it includes all the cities including those that only have 1 resident.Can someone tell me how to do this?Thanks a lot,Chris

View 2 Replies View Related

Simple Sql Query

Mar 18, 2008

hi,i have three fields in the database tablep1                    p2       rank10%               20%          3                        21%                40%          2now i am passing the input as 15%...this 15% is related to 3rd rank.. so i need the o/p rank as 3..if the p1 and p2 datatypes are varchar..then how we can we write the query for that one tgo get the rank as 3.Ramesh

View 2 Replies View Related

Help With Simple SQL Query

Apr 21, 2005

Hi All
Can anyone tell me what this simple SQL query would be: 
Find all the words "black", "dvd" and "player" from any of the fields "Product", "Brand" or "Description".
 
Many thanks
 
accelerator

View 2 Replies View Related

Simple Query Help...

May 15, 2005

I am trying to select players from a database based on the userid not having any user's name in it with an order by ASC. I basically want all the players that haven't been taken. Here is what I came up with and it gives me empty.
 
strSQLA="SELECT UserID, playerName from Allplayers WHERE UserID = '' ORDER BY playername ASC"

View 4 Replies View Related

Simple SQL Query -- Perhaps You Can Help

May 28, 2006

I hope it's considered appropriate to post a SQL query question here; it's not I'll gladly post this question elsewhere.  That having been said, I've run into a simple but quite tricky SQL query and was wondering if the community might be able to help.  Here's what's going on:
I have a "Documents" table.  Let's call it DI have a "Benefits" table.  Let's call it B.There is a many-to-many relationship between D and B so I have a third table to store the relationships.  Let's call that table BD.
When I create a document it creates a row in D.  Let's call that D1, D2...DN.  When I create a benefit it creates a row in B.  Let's call that B1, B2...BN
When I associate a document to a paritcular benefit, let's say that if D1 and B2 are associated, that I created row B2-D1 in my BD table.  Now here's what my challenge is.  Suppose I have these entries in my tables:
D: D1, D2, D3B: B1, B2, B3
Table BD:B1-D1B2-D1B2-D2
When the user goes to add documents to benefit B1, I want to show the user only documents that are not yet added to B1.  In this case, I want to show the user D2 and D3 but not D1 since that one's already added.  How do I write a SQL query to do this?
Here's what I've been using, but it shows too many results (e.g. included D1, D2, D3)
SELECT D.*, BD.*FROM Documents AS d LEFT JOIN rel_BenefitsDocuments AS bd ON d.doc_id = bd.doc_idWHERE (benefit_id <> @benefit_id OR benefit_id IS NULL)
Any thoughts?

View 2 Replies View Related

Simple Query Help

Mar 7, 2003

Hi,

I want to write a sql query for an asp page which will display only unique rows from the specified column along with the number of count for each unique row.

Example:

Table that I want to query

Last Name || First Name
Gates || Bill
Boyce || Mike
Gates || Bill
Gates || Phil


Results I want:

Last Name || First Name || Count
Gates || Bill || 2
Boyce || Mike || 1
Gates || Phil || 1

Thanks a lot,
Heta

View 2 Replies View Related

Simple Query

Aug 21, 2004

Hi,

I have a very simple question:

declare @treshold int
set @treshold = 10

SELECT
dbo.fn_Calc(t.column1) as calc,
t.column2
FROM
mytable t
WHERE
dbo.fn_Calc(t.column1) > @treshold


I can't think of a way to get rid of the function call in the WHERE clause.
Is this actualy a problem ?

I mean does realy the function fn_Calc execute 2 times in this statement and isn't this a performance issue then?

thanks

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved