I have a table which records the amount of times a user views a webpage
tblpageview
id
pagename
view_date
userid
I would like to have a query that returns the unique pagename and the amount of times that page has been viewed. I've had a go with count etc. but cant seem to get it. Anyone can help?
Can i show my view data on a web page is it possible.How can i show the data if it is too large.
View 1 Replies View RelatedHi all, newbie here (to SQL Server and Visual Studio)...
After a few days of configuring and setting things up, I finally got visual studio 2005 to connect to my sql server 2005 express database. I used server management studio to construct a database with 8 linked tables for my first main application. I plugged in some data and, after setting up proper permissions, was able to get visual studio to connect and return data.
I set up a simple gridview on a page with a sql server connection object to test things out. When I set the connection string to return data from a single table, everything works fine. When I test the connnection within visual studio, I get the results I expect. I build the page and load it up in my browser through the localhost, and I get the expected results.
The problem comes when I try to use a view rather than a table. I build the view in server management studio, and from within visual studio, when I test the connection I get the expected results. However, after building the pages (and rebuilding the site), I get a blank grid in my broswer when I test through localhost. I added another gridview to the page set to a table with a different connection string, and that grid appeared in my browser. I modified that connection string in visual studio to the sql server view, and again, nothing appears in my browser.
I'm not sure if, during all of my tinkering with security and setups I may have inadvertantly shut off the abilty to use views, but I'm not receiving an error messages. I just get a blank page.
Any ideas?Brian
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC ">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
I have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.
View 3 Replies View RelatedI'd like to write a query that lists all the views for a particular database, can anyone help me out with this?
I've played around with the information_schema.views that the online books references, but I can't get it to return a list of the views? I know I can just
look at the views through enterprise manager, but I need to do this through a query. Many thanks
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies View RelatedDoes anyone know how to count rows of data from 1 table that meet one or two different criteria and then get the probability of occurance for that criteria ... as an output column?
Do I use 'Having' or temp tables or Views?
Here is my output for now. I am trying to solve for 'Prob' - everything else works on it's own.Craig
SELECT Wins_Lng/Trades_Lng as Prob
FROM Transactions
SELECT Count(Ticker) as Trades_Lng
FROM Transactions
WHERE TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND TransKind='B' (SELECT COUNT(Ticker)as Wins_Lng
FROM TransactionsWHERE Transkind='B' AND TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND Profit_Lng>=0)
Here's the setup:
Client database has a complex view with eight nested subqueries used to return "dashboard" information. The application code uses NHibernate to call and filter the view with three parameters, one of which is the CustomerID.
A certain customer, (the biggest client), has more than ten times the number of records of the next largest customer.
Occasionally, the database reaches a state where when this particular customer tries to run the dashboard view, the application times out.
If I open up the view and re-save it, all is well again for a few days.
What gives?
Views are supposedly not pre-compiled, though I know that 2000 stores bits and pieces of query plans.
Any ideas on what causes this and what to do about it?
In SQL server 2008 how can I run the same query against each view in that database?
One database about 75 views.
Or even something like
Select 'yes' from view
Where shipping_status = 'delayed'
And return a list of view names so I know which views to query against.
I ran
Code:
select name from sysobjects where type = v
And got a list of my views. How can I now run my SQL statement against the list to see which views contain the info?
We know that a query execution plan exists for Stored Procedures in the Procedure Cache.
What about Views? Does a view have a query execution plan? We know that a View is a virtual table and that virtual table is populated
when the view is induced but does it have a Query Execution Plan?
I have tried to find this info for Views in BOL but I cannot see it anywher in BOL.
Ok, What I want to achieve is plain stuff, I want to join 2 views on a common key.It all works well with the SQL2000 Query Analyzer, but not trough ADO.NET or should I say my webapplication.With that I mean that my query return rows when executed from SQL2000 Query Analyzer, But not when used in my application or Executed from the Visual Studio Server Explorer.I have struggled with this one for several hours, I cant get this one right.So lets bring in the one who actually know what his doing View1: 1 select
2 cast((PS.RabattProsent/100.00)*PS.Pris AS decimal(11,2)) AS Rabatt
3 ,cast((PS.MVAProsent/100.00)*PS.Pris AS decimal(11,2)) AS MVA
4 ,cast(PS.Antall * ((PS.Pris*(100-PS.RabattProsent))/100)*((PS.MvaProsent/100.00)+1) AS decimal(11,2)) AS Belop
5 ,PS.*
6 ,K.Kunde_ID
7 FROM
8 tbl_ProduktSalg AS PS
9 INNER JOIN
10 tbl_Ordre AS O
11 ON
12 O.Ordre_ID = PS.Ordre_ID
13 INNER JOIN
14 tbl_Kunde AS K
15 ON
16 K.Kunde_ID = O.Kunde_IDView2: 1 SELECT
2 PS.Ordre_ID
3 ,SUM(cast((PS.RabattProsent/100.00)*PS.Pris AS decimal(11,2))) AS TotalRabatt
4 ,SUM(cast(PS.Antall * ((PS.Pris*(100-PS.RabattProsent))/100)*((PS.MvaProsent/100.00)+1) AS decimal(11,2))) AS TotalBelop
5 ,SUM(PS.Pris) AS TotalPris
6 ,SUM(cast((PS.MVAProsent/100.00)*PS.Pris AS decimal(11,2))) AS TotalMVA
7 FROM
8 tbl_ProduktSalg AS PS
9 GROUP BY
10 PS.Ordre_ID
MyQuery/SPRC: 1 create procedure %PROC% (@Kunde_ID int, @Ordre_ID int)
2 as
3 begin
4 SELECT
5 v_PSD.*
6 ,v_OTS.TotalRabatt
7 ,v_OTS.TotalBelop
8 ,v_OTS.TotalPris
9 ,v_OTS.TotalMVA
10 FROM
11 v_ProduktSalgDetaljer AS v_PSD
12 INNER JOIN
13 v_OrdreTotalSum AS v_OTS
14 ON
15 v_OTS.Ordre_ID = v_PSD.Ordre_ID
16 WHERE
17 v_PSD.Kunde_ID = @Kunde_ID
18 AND
19 v_PSD.Ordre_ID = @Ordre_ID
20
21 end
22
When working with databases containing myriad of huge tables, I am very much tempted to create categorized views on those tables in order to simplify and facilitate data query programming? Some developers I talk to say such views generally slow down query performance. Is this true?
Thanks.
Over the years I've read and experienced where joining more then 5 tables can lead to performance problems. This number can vary based upon the amount of data in each table, if and how indexes are used and the complexity of the query, but 5 has always been a good rule of thumb. Unfortunately I do not know what rule to apply in regards to joing views.
A developer has experienced timeout problems periodically when opening a view in EM or when running the code which makes-up the view. I decided to look at the view and noticed it references tables and views, which reference more views, which in turn reference other views. In all the initial view references 5 tables and 8 views directly and indirectly, with some of the views containing function calls. What are your thoughts on how many views and tables are too many when it comes to joins and query performance.
Thanks, Dave
I've just installed SQL Server 2005 Developer Edition, upgraded from SQL Server 2000, and have encountered a problem in trying to query against the System Catalogs.
I've tried SELECT * FROM sys.objects in addition to SELECT suser_name( role_principal_id ) FROM sys.server_role_members. In both cases I receive the following error: Invalid object name 'sys.objects' or 'sys.server_role_members' in the second example.
I changed the properties of the registration for my local db instance to use SQL authentication in lieu of Windows authentication. I used the sa login account to see if that made a difference, no go.
Can someone *please* tell me what I'm doing wrong here?
I would also like to know if it's possible to run a query confirming the user account I'm logged in to the system as.
Thanks!
Yes, I do know what this means and why the error is thrown but this is not my question.
I have two servers that are both running Windows Server 2003 and SQL Server 200 SP3. Below are the results from both servers using @@version
Sever 1 (BB)
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Server 2 (Genesis)
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
These servers are identical or so it seems. I've got a real ugly query that uses views and a derived table to get results. The problem is the 256 limit message only comes up on one server and on the other (Genesis) the query runs fine. I get the error though it reads a 260 limit on a box with SP4 applied. I've also run the query on a box that is Windows 2003, sql2k and sp4 and the query runs but not on a similar server here. This is all very odd. Please note that the database structure, views, etc are all exactly the same as far as I know.
Any suggestions? There seems to be no pattern between versions of Windows and/or SP levels.
Hi,
I have come across this problem with SQL server both on 2000 and 2005. I am stating an example here.
I have two partitioned tables and a view on top of both tables as below:
create table [dbo].[Table_1]
(
[TableID] INTEGER PRIMARY KEY NONCLUSTERED
CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1 AND 999),
[AnyOtherColumn] int NOT NULL ,
) ON [Primary]
GO
create table [dbo].[Table_2]
(
[TableID] INTEGER PRIMARY KEY NONCLUSTERED
CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1000 AND 1999),
[AnyOtherColumn] int NOT NULL ,
) ON [Primary]
GO
create view TableView
as
select * from Table_1
union all
select * from Table_2
GO
Note the NOT FOR REPLICATION clause on the check constraint on the TableID column.
I then ran the query execution plan for the following query on both SQL server 2000 and 2005.
select * from TableView where TableID = 10
On both the versions the execution plan shows and Index seek on both the tables in the view. This means that my partitioning is not working. If I remove the primary key constraint from the TableID column, the same query on the view shows a table scan on all the underlying tables. This is even worse.
Next, create the same tables and views again, now without the NOT FOR REPLICATION clause on the check constraint as show below:
create table [dbo].[Table_1]
(
[TableID] INTEGER PRIMARY KEY NONCLUSTERED
CHECK ([TableID] BETWEEN 1 AND 999),
[AnyOtherColumn] int NOT NULL ,
) ON [Primary]
GO
create table [dbo].[Table_2]
(
[TableID] INTEGER PRIMARY KEY NONCLUSTERED
CHECK ([TableID] BETWEEN 1000 AND 1999),
[AnyOtherColumn] int NOT NULL ,
) ON [Primary]
GO
create view TableView
as
select * from Table_1
union all
select * from Table_2
GO
Now run the query execution plan for the same query again.
select * from TableView where TableID = 10
This time you would see that it does an index scan only on the first parititon table. This time it proves that the partitioning works.
I would like to know why does the NOT FOR REPLICATION clause in the check constraint make such a huge difference?
Is it a bug in SQL server?
Or am I missing any thing?
Any help appreciated.
Thanks
I've been using partitioned views in the past and used the check constraint in the source tables to make sure the only the table with the condition in the where clause on the view was used. In SQL Server 2012 this was working just fine (I had to do some tricks to suppress parameter sniffing, but it was working correct after doing that). Now I've been installing SQL Server 2014 Developer and used exactly the same logic and in the actual query plan it is still using the other tables. I've tried the following things to avoid this:
- OPTION (RECOMPILE)
- Using dynamic SQL to pass the parameter value as a static string to avoid sniffing.
To explain wat I'm doing is this:
1. I have 3 servers with the same source tables, the only difference in the tables is one column with the server name.
2. I've created a CHECK CONSTRAINT on the server name column on each server.
3. On one of the three server (in my case server 3) I've setup linked server connections to Server 1 and 2.
4. On Server 3 I've created a partioned view that is build up like this:
SELECT * FROM [server1].[database].[dbo].[table]
UNION ALL SELECT * FROM [server2].[database].[dbo].[table]
UNION ALL SELECT * FROM [server3].[database].[dbo].[table]5. To query the partioned view I use a query like this:
SELECT *
FROM [database].[dbo].[partioned_view_name]
WHERE [server_name] = 'Server2'
Now when I look at the execution plan on the 2014 environment it is still using all the servers instead of just Server2 like it should be. The strange thing is that SQL 2008 and 2012 are working just fine but 2014 seems not to use the correct plan.
I have a dynamic SQL query that uses various indexes and views.
When a user wants to filter records based on last one day, last one week, last 30 days ...etc.. i use the following code:
@date is an integer.
begindate is datetime format.
begindate > cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20))
The above code slows my query performance by at least 400%!
Would it be faster to add a computed column to my table, using the suggested method in the link below?
[URL] ....
Then i could add an indexed view to improve performance, or can this be done another way?
Hi,
I want to run an asp file from a query written in sql 2005.
How can I achieve this in sql?
Please let me know its very urgent.
Hi, I have an SQl database that i want to build a simple search query for that would be accessible from an HTML page, what would be the best way of going about this? and what would be the best language to use?
Thanks in advance
Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?
View 1 Replies View Relatedscript to get the list of views in a database, involving tables from other databases?I AM using SQL server 2014
View 2 Replies View RelatedI am trying to write some admin only procedures which will collect information to one of my development server from other production and development servers.
I have created linked servers to access these other servers on the development server. This development server is SQL Server 2000 EE. Other servers which I want to access are 2000 and 2005 (vaious editions)
E.g I have another development server called PRODTEST which is SQL Server 2005 and on the development server I have created a linked server pointing to PRODTEST called TESTLINKSRV. I want to access new object catalog view (as I do not want to use sysobjects)
When I run the following query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys].[objects]
I get following error,
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName=' TESTLINKSRV ', TableName='" DBNAME "."sys"."objects"'].
Msg 7314, Level 16, State 1, Line 1
OLE DB provider ' TESTLINKSRV ' does not contain table '"DBNAME"."sys"."objects"'. The table either does not exist or the current user does not have permissions on that table.
So I try this query
SELECT * FROM [TESTLINKSRV].[DBNAME].[sys.objects]
and I get following error
Msg 208, Level 16, State 1, Line 1
Invalid object name TESTLINKSRV.DBNAME.sys.objects'.
So bottom line is how do I access catalog views on a 2005 server from a 2000 server using linked server?
I hope someone understands what I am trying to achieve. Please let me know what is it that I am doing wrong.
Thank you
You can display both by referring to their position in the recordset or by aliasing them in the query and referring to the alias in your code.
Note that in order to put Products.Quantity or ProductDetails.Quantity in just one column you'll have to add some logic in the query, using a coalesce, like:
sql Code:
Original
- sql Code
SELECT
Products.ProductID, Products.ProductName, Products.ProductPict,
COALESCE(Products.Quantity, ProductDetails.Quantity, 0) Quantity,
ProductDetails.ProductDetailID, ProductDetails.P_Size, ProductDetails.P_Color
FROM
Products
LEFT OUTER JOIN
ProductDetails
ON Products.ProductID = ProductDetails.ProductID
WHERE
Products.Quantity <= "& +rsResults__var1 & "
OR
ProductDetails.Quantity <= "& +rsResults__var1 & "
Order by Products.ProductID asc;
SELECT Products.ProductID, Products.ProductName, Products.ProductPict, COALESCE(Products.Quantity, ProductDetails.Quantity, 0) Quantity,ProductDetails.ProductDetailID, ProductDetails.P_Size, ProductDetails.P_ColorFROM Products LEFT OUTER JOIN ProductDetails ON Products.ProductID = ProductDetails.ProductID WHERE Products.Quantity <= "& +rsResults__var1 & " OR ProductDetails.Quantity <= "& +rsResults__var1 & " ORDER BY Products.ProductID ASC;
Untested, beware of syntax errors!
In this case you'll have just one Quantity colum and you won't have to change anything in your asp code.
Hmm... the query should return the number of athletes with scores >= to your own.
So if you have the highest score in the world, for example, then the only score >= to your own is... yours. The count of that is 1, giving you rank 1.
If the order seems backwards to you just reverse the <= operator in the WHERE clause. Either way, as a responsible developer, never never never put a SQL query you don't understand into production.
Fixed the ServerID thing, too. It was there because I don't have a table that matches yours and wrote the query against one with a compatible structure, but different names.
Hello I have a project that uses a large number of MS Data access pages created in Access 2003 and runs on MS SQL2005.
When I am on lets say my client, (first page in a series) data access page and I have completed the fields in the (DAP), I am directing my users to the next step of the registration process by means of a hyperlink to another Data access page in the same web but in a linked or sometimes different table.
I need to pass data entered /created on the first page to the next page and populate the next page with some data from the first page / table. (like staying on the client name and ID when i go to the next page)
I also need the first data access page to open and display a blank or new record. Not an existing record. I will also be looking to creata a drop down box as a record selector.
Any pointers in the right direction would be appreciated.
I am some what new to data access pages so a walk through would be nice but anything you got is welcome. Thanks Peter€¦
I am extremely new with .asp. We have the .net framework 1.1 installed on our server and the following query works in Crystal Reports (older version). We would like to have this converted to a .asp page with two inputs. One for the date range and one for the state. Can someone please tell me how to go about this. I do have downloaded as test. Visual Studio 2003.net and Web Matrix. I really am looking for (if there is any) a way to pretty quickly convert query to web pages so users can use them without a huge expense. IF someone already has something like this done. That would be great. The following query is for Visual Enterprise (Manufacturing software). Thanks
SELECT
SHIPPER."CUST_ORDER_ID", SHIPPER."SHIPPED_DATE", SHIPPER."INVOICE_ID",
SHIPPER_LINE."USER_SHIPPED_QTY", SHIPPER_LINE."SHIPPED_QTY", SHIPPER_LINE."UNIT_PRICE",
RECEIVABLE_CURR."CURRENCY_ID", RECEIVABLE_CURR."SELL_RATE",
CUST_ORDER_LINE."PART_ID", CUST_ORDER_LINE."ORDER_QTY", CUST_ORDER_LINE."USER_ORDER_QTY",
INVENTORY_TRANS."TYPE", INVENTORY_TRANS."ACT_MATERIAL_COST", INVENTORY_TRANS."ACT_LABOR_COST", INVENTORY_TRANS."ACT_BURDEN_COST", INVENTORY_TRANS."ACT_SERVICE_COST",
CUSTOMER_ORDER."CUSTOMER_ID", CUSTOMER_ORDER."SHIP_TO_ADDR_NO", CUSTOMER_ORDER."CURRENCY_ID",
PART."DESCRIPTION", PART."STOCK_UM",
CUSTOMER."STATE",
CUST_ADDRESS."ADDR_NO", CUST_ADDRESS."NAME", CUST_ADDRESS."STATE"
FROM
{ oj ((((((("VMFG"."dbo"."SHIPPER" SHIPPER INNER JOIN "VMFG"."dbo"."RECEIVABLE_CURR" RECEIVABLE_CURR ON SHIPPER."INVOICE_ID" = RECEIVABLE_CURR."INVOICE_ID") INNER JOIN "VMFG"."dbo"."SHIPPER_LINE" SHIPPER_LINE ON SHIPPER."PACKLIST_ID" = SHIPPER_LINE."PACKLIST_ID" AND SHIPPER."CUST_ORDER_ID" = SHIPPER_LINE."CUST_ORDER_ID") INNER JOIN "VMFG"."dbo"."INVENTORY_TRANS" INVENTORY_TRANS ON SHIPPER_LINE."TRANSACTION_ID" = INVENTORY_TRANS."TRANSACTION_ID") INNER JOIN "VMFG"."dbo"."CUSTOMER_ORDER" CUSTOMER_ORDER ON SHIPPER_LINE."CUST_ORDER_ID" = CUSTOMER_ORDER."ID") INNER JOIN "VMFG"."dbo"."CUST_ORDER_LINE" CUST_ORDER_LINE ON SHIPPER_LINE."CUST_ORDER_ID" = CUST_ORDER_LINE."CUST_ORDER_ID" AND SHIPPER_LINE."CUST_ORDER_LINE_NO" = CUST_ORDER_LINE."LINE_NO") INNER JOIN "VMFG"."dbo"."CUSTOMER" CUSTOMER ON CUSTOMER_ORDER."CUSTOMER_ID" = CUSTOMER."ID") INNER JOIN "VMFG"."dbo"."PART" PART ON CUST_ORDER_LINE."PART_ID" = PART."ID") LEFT OUTER JOIN "VMFG"."dbo"."CUST_ADDRESS" CUST_ADDRESS ON CUSTOMER_ORDER."CUSTOMER_ID" = CUST_ADDRESS."CUSTOMER_ID" AND CUSTOMER_ORDER."SHIP_TO_ADDR_NO" = CUST_ADDRESS."ADDR_NO"}
WHERE
SHIPPER."SHIPPED_DATE" >= {ts '2002-08-01 00:00:00.00'} AND
SHIPPER."SHIPPED_DATE" < {ts '2004-08-25 00:00:00.00'}
ORDER BY
SHIPPER."SHIPPED_DATE" ASC
Here you go:
The telephone numbers are just strings of the same integer,
1's: Calls made every day
2's: Calls made every day
3's: Two, non consecutive days without calls
4's: No calls made from 2nd till 6th
5's: Only one day with calls
6's: No calls made from 2nd to 6th, but many made on the 1st
7's: No calls made from 2nd till 13th
If you'd like a bigger sample set then I will create one.
Code:
CREATE TABLE tblCallSummary(
[CallID] [int] IDENTITY(1,1) NOT NULL,
[TelephoneNumber] [varchar](11) NOT NULL,
[StartTime] [smalldatetime] NOT NULL,
[Duration] [int] NOT NULL,
CONSTRAINT [PK_tblCallSummary] PRIMARY KEY CLUSTERED
(
[CallID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-01'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-02'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-03'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-04'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-05'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-06'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-07'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-08'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-09'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-10'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-11'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-12'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-13'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-14'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-15'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-16'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-17'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-18'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-19'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-20'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-21'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-22'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-23'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-24'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-25'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-26'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-27'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-28'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-29'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-30'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('11111111111',CONVERT(smalldatetime,'2007-12-31'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-01'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-02'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-03'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-04'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-05'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-06'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-07'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-08'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-09'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-10'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-11'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-12'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-13'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-14'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-15'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-16'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-17'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-18'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-19'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-20'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-21'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-22'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-23'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-24'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-25'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-26'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-27'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-28'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-29'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-30'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('22222222222',CONVERT(smalldatetime,'2007-12-31'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-02'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-03'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-05'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-07'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-09'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-11'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-12'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-13'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-14'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-15'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-16'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-17'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-18'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-19'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-20'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-21'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-22'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-23'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-24'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-25'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-26'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-27'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-28'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-29'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-30'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('33333333333',CONVERT(smalldatetime,'2007-12-31'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-01'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-07'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-08'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-09'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-10'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-11'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-12'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-13'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-14'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-15'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-16'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-17'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-18'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-19'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-20'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-21'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-22'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-23'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-24'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-25'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-26'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-27'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-28'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-29'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-30'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('44444444444',CONVERT(smalldatetime,'2007-12-31'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('55555555555',CONVERT(smalldatetime,'2007-12-10'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-01'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-07'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-08'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-09'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-10'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-11'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-12'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-13'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-14'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-15'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-16'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-17'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-18'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-19'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-20'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-21'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-22'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-23'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-24'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-25'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-26'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-27'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-28'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-29'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-30'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('66666666666',CONVERT(smalldatetime,'2007-12-31'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-01'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-14'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-15'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-16'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-17'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-18'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-19'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-20'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-21'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-22'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-23'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-24'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-25'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-26'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-27'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-28'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-29'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-30'),1)
INSERT INTO tblCallSummary(TelephoneNumber,StartTime,Duration) VALUES('77777777777',CONVERT(smalldatetime,'2007-12-31'),1)
Our requirements have just changed and we only need customers which were active in the last five days, a very easy query. So please don't spend too much of your time on this now, however, I am still keen to find an elegant way of solving this problem, if one exists so any input from you would be appreciated.
Thanks for you help
Dave
Code:
select *
from linkboxes l
left outer
join subjects s
on l.sub_link_id
= s.sub_id
left outer
join external_links e
on l.ext_link_id
= e.ext_id
where l.sub_id = subid
I am using a very basic query in Front Page 2002 against a SQL table inSQL 2000SELECT * FROM tablename WHERE (f_docnumber = ::f_docnumber::)and f_docnumber is a search form field.When I run I getDescription: The precision is invalid.Number: -2147467259 (0x80004005)The field is defined in the table as numberic 9(10,0)Any ideas?
View 1 Replies View RelatedDear Friends,
I am working on search customer information page.
I have 5 search options,
Name,
Email,
Order Number,
Product Name,
Order Date
I am using check boxes, I need to allow admin to enter above information and click on search,
How will I make my query and sends to DB server to pull up records which satisfies where clause:
For example,
Select * from orders where Email = #Email#
This is simple, but I can not hardcode all queries, I don€™t know in advance what different search option ADMIN may choose.
Any suggestion for logic or query make up,
Thanks,
Fahim.