Issue with Exporting Data
(111 Views)
my SQL version is 5.1, I'm unable to export my query results to excel.....The Help Content instructs me to "right mouse" click on the "Query Results Grid" then select the Export Option, however, when I "right mouse" the mini menu does not appear.....
Any assistance is greatly appreciated.
|
Help with UPDATE SQL
(111 Views)
Hi ,
In my Database (DB2), I have 3 tables.
MDDT_BRANCHES
MDDT_BRANCH_TIME_ON_HAND
MDDT_CALCULATION_VARIABLES
I need to update 1 field in MDDT_TABLES based on Values within all 3 tables.
MDDT_BRANCHES.BRANCHID can be linked to MDDT_BRANCH_TIME_ON_HAND.ALLOCATEDTO
MDDT_CALCULATION_VARIABLES is an Unrelated table with a single record.
The end result I am looking for is to make the following:
MDDT_BRANCHES.CURRENTWORKCAPACITY =
(MDDT_BRANCHES.TIME
* MDDT_CALCULATION_VARIABLES.DAYSTOKEEP)
- MDDT_BRANCH_TIME_ON_HAND.SECONDSWOH
I seem to be able to get Bits working, but cant get it all to work together
|
Need some help with a query asap please
(202 Views)
i have a temp table with data
I want to insert records into the first table(tbl1) and use the ID field that is auto inc in tbl1 and insert some other data from tblTemp into tbl2
so i created a column to hold the identity from tbl1 in tblTemp named tbl1ID now I need to loop through the records inserting them and get the scope_identity of each record and insert that into the tblTemp.tbl1ID column
help please how would I put this together
Code:
INSERT INTO tbl1
(
col1,col2
)
select
col1,col2
FROM tblTemp
and now i need to for each record
INSERT INTO tblTemp
SELECT @tbl1ID = SCOPE_IDENTITY()
FROM tbl1
|
Query with temperorary tables?
(137 Views)
,
ik have the following tables
Order
-----
Orderno
customer
note_id
orderline
------------
ordern
lineno
note_id
note_text
chargeline
------------
orderno
chargelineno
note_id
note
--------
note_id
note_text
An order can have more than one orderline and more than one chargeorderline. You can store a text at orderlevel, orderlinelevel and at chargelinelevel (is not mandatory). I want to create a report which can show all these 3 text per order (and only shows an order when a text is available). I tried it with the following query which put the text for the orderlines and the text for the chargeorderlines in a temperorary table first. But this doesn't seem to work. Does anyone has an idea
|
Help with stored procedures in sql 2000
(186 Views)
HI ,
I was hoping someone would be able to help me with some thing. I am learning ASP.NEt v 2.0 and am using SQL server 2000 developerd edition to build an exommerce website using the Apress booking Beginning E-Commerce in C# 2005.
I have been managing fine up to now but the book is building stored procedures for retrieving products lists from the database. The book is written for SQL 2005 express edition but says that with some extra woork there is no problems with getting the code to work in 2000. I have two procedures that implement paging which the book says doesn't work in sql 2000 and gives a very brief solution which doesn't make sense to me. Would anybody be able to help me convert these so i can use them in sql 2000
I get the rough idea but am having problems re-writing this and I am now holding my hands and admitting defeat.
The procedures are as follows:
Code:
CREATE PROCEDURE GetProductsOnDepartmentPromotion
(@DepartmentID INT,
@DescriptionLength INT,
@PageNumber INT,
@ProductsPerPage INT,
@HowManyProducts INT OUTPUT)
AS
-- declare a new TABLE variable
DECLARE @Products TABLE
(RowNumber INT,
ProductID INT,
Name VARCHAR(50),
Description VARCHAR(5000),
Price MONEY,
Image1FileName VARCHAR(50),
Image2FileName VARCHAR(50),
OnDepartmentPromotion BIT,
OnCatalogPromotion BIT)
-- populate the table variable with the complete list of products
INSERT INTO @Products
SELECT ROW_NUMBER() OVER (ORDER BY ProductID) AS Row,
ProductID, Name, SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description,
Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM
(SELECT DISTINCT Product.ProductID, Product.Name,
SUBSTRING(Product.Description, 1, @DescriptionLength) + '...' AS Description,
Price, Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM Product INNER JOIN ProductCategory
ON Product.ProductID = ProductCategory.ProductID
INNER JOIN Category
ON ProductCategory.CategoryID = Category.CategoryID
WHERE Product.OnDepartmentPromotion = 1
AND Category.DepartmentID = @DepartmentID
) AS ProductOnDepPr
-- return the total number of products using an OUTPUT variable
SELECT @HowManyProducts = COUNT(ProductID) FROM @Products
-- extract the requested page of products
SELECT ProductID, Name, Description, Price, Image1FileName,
Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM @Products
WHERE RowNumber > (@PageNumber - 1) * @ProductsPerPage
AND RowNumber (@PageNumber - 1) * @ProductsPerPage
AND RowNumber (@PageNumber - 1) * @ProductsPerPage
AND RowNumber
|
Help with SQL statement!
(160 Views)
Hi im trying to write a SQL statement. I have attached a copy of the relationhips windows so you will understand.
In the InterComp table I want to add up all InterComp_Quantity fields and also the InterComp_Cost. Which is easy. The problem is I want to group them by month i.e show the total Quantity and cost for each month and obvously I need to be able to seperate the years (In other words I dont want Jan 05 and Jan 06 added together I need Jan 05 & 06 totals shown seperately!).
Is this possibly
|
hi asp.net with vb.net and some sql stuffs
(202 Views)
hi are there any smart willing to help a dumb girl out with a project in asp.net with vb.net and usage of sql. i have to make a small login page and some sort of sql login after that. but if u can help
|
(223 Views)
I am a super noob to ASP and SQL but I am trying my best to learn.
Please dont take this as a "please make a script for me!" post but rather someone asking for guidance: basically am i going the right way Is there an easier/better way of doing what I'm trying to achieve. Any links that you might have are also appreciated of course.
now that i've gotten that out of the way... allow me to pose my situation:
I am trying to create a form where users enters:
nameemailGroup Name (just a variable text)upload a file (.doc)i also want the script to automatically enter in two more pieces of info into my table:
timestamp for submission of docset status of .doc file to pendingand to provide the user with a unique ID for their submission so that they may enter it to update their .doc documents and upload them again.
curretnly i have this as my code (i am using code from asp101 as a template) - please note that I am aware that I am not done, that is why I am here - asking for help.
Code:
' primary code taken from http://www.asp101.com
'Create an ADO connection to a database
'load the includes file for the conn's used
|
Help with setting up Many to Many relationship
(142 Views)
I am pretty new to SQL, but having fun learning at this point. One of the things I am trying to do is setup an asp page here at work that list some of the clients, and the contacts for them.
Most of the sites have many contacts. A few of the contacts have many sites. How would I go about setting this up
Currently, I have a table setup for sites and one for contacts. One the contacts table, I have a field for "site_id" and just relate it back for the site. This works fine to have multiple contacts associated to a site, but what do I do if the contact needs to be associated to more than one site
|
Recordset Problem with Absolutepage and adOpenStatic PLEASE HELP !!!!!!
(139 Views)
I have this script for navigating through a recordset, it all works fine,
except when I click to move to the next page
it displays all results again rather than the results only for that page.
I've tried every combination of Record cursor types etc. and still no joy.
Could someone please help me.
Kind
|
need help with complicated select distinct query
(417 Views)
I have a table called gms_tests that keeps track of the results of tests run against servers. Columns are hostname, testname, status and time. The tests are run (about 5 against each server) every half hour, and the results are put in the table.
I need to be able to search this table and obtain the most recent result for each test. For example, I need something similar to:
select distinct hostname,testname from gms_tests order by time desc
but I also need to be able to see the time and status columns.
I've tried something like this:
select hostname,testname,status,max(time) from gms_tests group by hostname,testname
but the data returned in the status column is not correct - it doesn't match the status that corresponds with the latest test time.
Any ideas what might work I'm using MySQL version 3.23.58, so I can't use subqueries.
|
Deleteing records with related tables
(123 Views)
once again.
I seem to be getting this annoying error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] The record cannot be deleted or changed because table 'tblMovieActor' includes related records.
/deleteentry.asp, line 3
The record is related to a junction table ( tblMovieActor ).
I have to delete the relation between the Movie and the Actor before I delete the movie, but I don't know how to querey the database and delete the relation.
Can someone help out
|
Updating datetime value with NULL
(268 Views)
I'm trying to figure out how to insert a NULL value into a datetime column that already contains a date.
The column contains NULL values by default before a date is inserted into it, but how can I insert NULL back into it
|
Need help with join
(229 Views)
I have 2 tables that I'd like to join based on a supplied project number.
Table1:
Columns = ProjectNum, Name
example:
01011111, Test Project
01012222, Another Project
Table2:
Columns = ProjectNum, DescriptionCategory, Description
example:
01011111, SD, The Short Description
01011111, LD, The Long Description
01012222, LD, Also a Long Description
etc...
I want to supply a project number and create a record for that project number from table1 that contains a Short Description column and a Long Description Column per record.
ie. I supply 01011111 and get
01011111, Test Project, The Short Description, The Long Description.
So far I am able to join the tables and get a record as long as records exist in Table2. If records don't exist in Table 2 then I get an empty result set. What I want is:
I supply 01012222 and get:
01012222,Another Project,'',Also a Long Descrption
|
Problem with TIME field
(157 Views)
I am using SQL Server 2000.
I made this following simple query:
SELECT [TimeOperationBegan],[TimeOperationEnded],
(DATEDIFF(minute,[TimeOperationBegan],[TimeOperationEnded])) AS Diftime
FROM [ORRecord]
The sample results are as follows:
TimeOperationBegan |TimeOperationEnded |Diftime
__________________________________________________ ________
1899-12-30 10:40:00.000| 1899-12-30 11:22:00.000| 42
1899-12-30 11:41:00.000| 1899-12-30 11:52:00.000| 11
1899-12-30 16:03:00.000| 1899-12-30 16:32:00.000| 29
1899-12-30 23:05:00.000| 1899-12-30 00:32:00.000| -1353
How can i get rid of this negative value and make the query to calculate the difference between 23:05 and 24:32 for the fourth record
|
problems with LIKE query
(164 Views)
Hi
I have an access database which has a table full of users and their scores and another table full of unacceptable user names.
If a user tries to enter their name as "damn this game" i want the asp to spot that 'damn' is listed in my table (tblSwear) and replace the entire name with "anon".
I read on 4 from rolla that when using a like statement if u place % around either side of the word u are comparing then it should allow for other characters appearing around the word.
ie '%damn%'
would find "blah-damn-blah"
but for some reason my code below will only detect a user entering purely "damn" despite my use of the % symbol and it will not detect blahdamnblah"
Code:
dim thename
thename=request("rude")
sql = "SELECT * FROM tblSwear WHERE tblSwear.word LIKE '%" & thename & "%'"
'Select * from Tablename where (Firstname) LIKE '%" & FirstNameVar & "%'"
rs.open sql,myconn,3,3
if rs.recordcount > 0 then
response.Write("anon")
else
response.Write(thename)
rs.close
end if
I keep staring at my code and i cant see what the mistake is, i have attached it and the database and files in case any1 has the time to give it a glance.
I will be so grateful for any help.
|
trouble with query
(212 Views)
I have a database (Access 2000) that holds ship itinerary information and a list of businesses at ports that are visited
I want to get a list businesses at the ports a ship visits over a period of time:
Eg. Find all businesses in the ports that the Carnival Destiny visits over the next fortnight.
---------------------------------------------------------------------------------
TABLES:
ItMain:
CID primary key Cruise ID
Ship name of ship
ItDates :
CID
Date dates an itinerary is repeated on e.g. 7-7-03, 7-14-03 etc
ItPorts:
CID
Day cruise day (seven day cruise 0-1-2-3-4-5-6)
Port port that ship visits
BizMain:
BID primary key - Business ID
Port port that business is located in
-----------------------------------------------------------------------------------
I thought this would be easy to solve, even with my poor knowledge of SQL.
Alas, No! My subquery refused to cooperate!
Can anyone post a solution for me, or even point me in the right direction (so that I can have the pleasure of solving it myself)
|
Deploy ASP application with MS SQL 7.0 data ?
(127 Views)
Originally posted by : MaliciousHUT (MaliciousHUT@yahoo.co.uk),I have developped a web site using MS SQL 7.0 as DBMS. Now I have to deploy but I dont know how to deploy all my data in database server. If I use SQL script , i can generate only database structure( table, trigger, stored procedure) not the data itself.Any ideas are appreciated
|
how can select distinct records within loop..??
(150 Views)
Originally posted by : dev (dev0505@rediffmail.com)i wanna select distinct records within loop..i have got normalize database which carry one user table(which carryes unique records) and another user's skills(repeatetion of records) i made a search on user's skill table and stored in a record set(repeatation is there in the record set) query is1.got search result in record set called rs:userid = rs("userid")while not rs.eof userid = rs("userid")set rss = con.execute(select distinct(user) from user where userid = '"& userid &"')")rs.movenextwendwhen i execute this query it's does'nt give me distinct record i believe we cann't use distinct function in while loop bec'z every record is unique it self within loop. is there any way to select distinct records in loop
|
store carriage return with text in table
(137 Views)
Originally posted by : wk (skrecycle@rediffmail.com),anybody knows the best menthod to store 2-3 paragraphs of text in SQL database How do i store the carriage return (enter) with the textthanx!
|
|
