I have a single .asp page that opens a connection and then sequentially
opens and closes 14 recordsets from stored procedures to obtain various
product information before closing the connection.
Is it common practice to do something like this? Or is opening 14
recordsets going to become a real problem when the page goes live and starts
getting high web traffic?
Thank you in advance for any information you might provide.
Dave
I am trying to join two tables on multiple fields. But the nulls aren't considered a match so they aren't included in the results set.
Select A.Lot, A.Block, A.Plan, B.Key
from A join B on
A.Lot=B.Lot
A.Block=B.Block
A.Plan=B.Plan
In the data, there can be an instance where Block is null in both tables so it "matches" but not in SQL. How do I get the "matched" nulls to be returned as well?
Okay I wanted to get this to work right so I am wondering what I am doing wrong here.
"SELECT COUNT(A.Status) AS TOTAL FROM tts_tickets A,tts_reporters B WHERE A.Ref_Reporter_ID="123"AND A.Status=1"
I need to count the status options but only where from table b is the ID equal to the ID in question.
How do I do this?
I Have three tables
TASK
taskid, taskname, projectid, workid
1,,1,1
2,,1,2
3,,2,3
PROJECT
projectid, projectname
1, project1
2, project2
3, project3
WORK
workid, workname
1,work1
2,work2
3,work3
I need to do an update this way
Update the taskname as 'projectname' + '_' + 'workname' for any projectid.
projectname and workname coming from the projectid and workid in the task record
so the task table becomes
1,project1_work1,1,1
2,project1_work2,1,2
3,project2_work3,2,3
I can get all the records doing this
SELECT p.projectname+ ' ' + w.workname AS 'NEWNAME'
FROM task t
JOIN work w
ON t.workid = w.workid
JOIN project p
ON t.projectid = p.projectid
WHERE projectid = 2
how do i do an UPDATE?
any help is appreciated
Hello,I need to do a seach in multiple columns for a certain word. With SQLI have to use one specific column right?I.E. select * from DB where Column1 like '%search%'That works.But what if I want to seach multiple columns in the table for thesearch word?You can't do this:select * from DB where Column1, Column2, Column3 like '%search%'Is there a way to do this?Thanks,Tmuld.
View 1 Replies View RelatedDear all,Our application needs a bit of database redundancy.Our application only accesses database for reading purposes.We want to have two databases in separate machines. Incase one databaseserver dies the application should automatically extract data from theother server.Is there any JDBC driver available so that it can detect failure in themain database server and then tries to extract data from the standbydatabase server?Kind regards--Posted via http://dbforums.com
View 2 Replies View Related I need to update information for a user and if the user is classified
as a primary (@blnPrimary) then I need to update information for all
users within his agency (AgencyUniqueId). The issue is that the second
UPDATE to "cdds_User_Profile" always returns a rowcount of 0 (should be
1) even though the values for "@Original_AgencyUniqueId" and
"@Original_UserId" are correct. This is just a snippet of the whole
procedure. I'm trying to implement similar logic in other parts of the
procedure and I'm observing the same behavior there as well. Any help
anyone can provide is greatly appreciated. </p><pre>/*** Update User Profile ***/UPDATE [cdds_User_Profile] SET [FirstName] = @FirstName, [LastName] = @LastName, [Title] = @Title, [Phone] = @Phone, [AcctType] = @AcctType, [AcctStatus] = @AcctStatus, [LastUpdatedDate] = GETDATE() WHERE ([FirstName] = @Original_FirstName AND [LastName] = @Original_LastName AND [Title]=@Original_Title AND [Phone]=@Original_Phone AND [AcctType]=@Original_AcctType AND [AcctStatus]= @Original_AcctStatus AND [AgencyUniqueId] = @Original_AgencyUniqueIdAND [UserId] = @Original_UserId);IF @@ROWCOUNT = 0BEGINSET @err_message = 'Data has been edited by another user since you began viewing this information.'RAISERROR (@err_message,11, 1)ROLLBACK TRANSACTIONRETURNEND IF @@ERROR <> 0 BEGINROLLBACK TRANSACTIONRETURN ENDIF @blnPrimary = 1 BEGIN IF LOWER(@AcctStatus) <> LOWER(@AgencyAcctStatus)/*** Update Users Acct. Status ***//* update all users in same agency profile */UPDATE [cdds_User_Profile] SET [AcctStatus] = @AcctStatus,[LastUpdatedDate] = GETDATE() WHERE ([AgencyUniqueId] = @Original_AgencyUniqueIdAND [UserId] = @Original_UserId); IF @@ROWCOUNT = 0BEGINSET @err_message = 'Data for this agency has been edited by another user since you began viewing this information.'RAISERROR (@err_message,11, 1)ROLLBACK TRANSACTIONRETURNENDIF @@ERROR <> 0 BEGINROLLBACK TRANSACTIONRETURN ENDEND</pre><pre>
Hi!
Can anybody help me in solving this problem
SCRIPT
declare @var1 varchar(10)
set @var1 = '1,4,9,10'
select @var1
select TGNO, TGNAME from carriers where TGNO in (@var1)
RESULT
----------
1,4,9,10
(1 row(s) affected)
Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value '1,4,9,10' to a column of data type smallint.
I want to pass a list of values to a column of DATA Type smallint.
Thanks in advance.
What is the way to pass the values throug varibale as shown below?
SCRIPT
declare @var1 varchar(10)
set @var1 = '1,4,9,10'
select @var1
select TGNO, TGNAME from carriers where TGNO in (@var1)
RESULT
----------
1,4,9,10
(1 row(s) affected)
Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value '1,4,9,10' to a column of data type smallint.
I want to pass a list of values to a column of DATA Type smallint.
Hey everyone,
I am beggining in SQL and the .NET framework and have been running into some problems trying to design a relational database. I am completely new to it so I bought a book that was recommended in this Forum called "Handbook of Relational Database Design" and it has been pretty usefull so far. RIght now I am trying to make the Logical Data Model before I make the Relational Data Model.
The problem that I am having right now is creating a table that is a derivation from another table. For example, in the book they have a table called Property, and then two other tables called MountainProperty and BeachProperty. MountainProperty and BeachProperty are a type (relationship here) of a property. So basically Property will hold some data in a table, and then MountainProperty and BeachProperty will extend that property to contain more specific data. This is very similar to what I want to do. However I am having a problem understanding how an instance (or row) in Property, will have a link (foreign key) to a piece of data that is in Mountain or BeachProperty. I understand the foreign key in Mountain and BeachProperty and how they will link back to their "parent". But how will Property know its children, where is the link for that, how can one make a link to that. You could make a column with a foreign key, but which table would it point to, can one column point to mulitple tables? That doesn't make very much sense to me.
Basically what I am trying to say is that a row in the Property table can be multiple types, and these types will store more additional data about that row. How can I link to that data from that row in the Table Property.
I am terribly sorry if this is confusing or if it is so appartently easy for you, but this is the first time that I have ever tried to make a relational database and I am really struggling on seeing how to organize these tables properly. Thank yor for your time.
Jeremy
Hi all,
Is it possible to generate mulitple reports from parameter? what I want is for example: I have a report with a sales orders table, it has 2 input parameter fields; two dates to make up a date range. If the date range is between yesterday and today, I will get two reports (two reports with two different total page number); one is for yesterday and one is for today.
I did some research on resetting the total page, but their solution doesnt work on my special case (very complicate..). A similiar work out also apperciate.
Regards,
Bryan
why should one use Recordsets and not SQL itself in database work. Isn't the latter muvch faster, more portable, and needs to be learnt only once?
View 1 Replies View RelatedI have a DLL that acts as an interface between the application and the database. So you create a new instance to the database via:
MyDatabaseClass db = new MyDatabaseClass()
Each instance of the class creates a new connection to the database.
So if I were to have the following, then 4 connections would be made.
MyDatabaseClass db1 = new MyDatabaseClass()
MyDatabaseClass db2 = new MyDatabaseClass()
MyDatabaseClass db3 = new MyDatabaseClass()
MyDatabaseClass db4 = new MyDatabaseClass()
My question is...What if instead of creating new connections upon each instantiation, I re-use the same connection through a "ConnectionManager" class. In this way, the above code will only create 1 connection instead of 4.
What do you guys think about each approach? Obviously the 2nd approach makes sense for non-web applications, but what about websites? Would that just cause that single connection to be overloaded??
Thanks for any insights.
I have several ResultSet Querys/Statements within my page. An exampleof the code looks like this:ResultSet rs1 = stmt1.executeQuery("SELECT right('' + '$' + convert(varchar,SUM(ActivePrim),1), 15) AS 'ActivePrim',right(' ' + '$' + convert(varchar,SUM(KGAP),1), 15) AS'KGAP', right(' ' + '$' +convert(varchar,SUM(PrimaryRepo),1), 15) AS 'PrimaryRepo', right('' + '$' + convert(varchar,SUM(WeeklyTotal),1), 15) AS'WeeklyTotal' FROM Intranet..InsuranceStats WHERE EmployeeName ='Jamie' and Date BETWEEN '01/01/04' and '01/31/04'");What would be the correct way to retrieve each result set? Icurrently have it as the example below. But this doesn't allow foreach result set to be displayed separately.<td valign=top><b>Active Primary:</b><%= ActivePrim %></td>Any help would be greatly appreciated.Catherine
View 1 Replies View Relatedis it possible to concatenate 2 recordsets ? i have 2 select, and i need then to be displayed one after other.using union all the results are gathered, ordered like only one select.thanks for all
View 5 Replies View RelatedIs it possible to return multiple recordsets from SQL to ASP in a single query?
TIA
Pilot
Hi all,
I've written a query which needs to return different records depending on its argument.
I've used an IF statment to test which of the three SELECT statments should be returned. However, when I try to access the data from VBA in Access I find that the recordset is empty, whereas the query returns thr correct data if I run it in the SQL environment. WHy is this? More importantly, how do I make it return records?
Code:
ALTER PROC [dbo].[spFilteredContractListByUser]
@UserID varchar(7) --The user's 'T' number
AS
PRINT 'User ID is' + @UserID
--Get this user's filter type from tbl
DECLARE @FilterType int
SELECT @FilterType = FilterID
FROM tblFindContractUserFilter
WHERE UserID = @UserID
-- Standard filter
-- Returning all queries matching the username
IF @FilterType = 1
BEGIN
SELECT DISTINCT [Acc No], Business, Type, [Log Date], [Logged By], [Ack. Date], [Ack. By], [Late?]
FROM qryQueriesWorkList
WHERE (suser_sname = @UserID)
END
ELSE IF @FilterType = 2
-- Return only queries acknowledged by @UserID
BEGIN
SELECT DISTINCT q.[Acc No], q.Business, q.Type, q.[Log Date], q.[Logged By], q.[Ack. Date], q.[Ack. By], q.[Late?]
FROM qryQueriesWorkList q
WHERE (q.[Ack. By] = dbo.getEmployeeName(@UserID))
END
ELSE IF @FilterType = 3
-- Return all the queries which have not been acknowledged
BEGIN
SELECT DISTINCT q.[Acc No], q.Business, q.Type, q.[Log Date], q.[Logged By], q.[Ack. Date], q.[Ack. By], q.[Late?]
FROM qryQueriesWorkList q
WHERE (q.[Ack. By] IS NULL)
END
The VBA code is:
Code:
rst.Open ("EXEC spFilteredContractListByUser '" & Me.cmbUserFilter.Value & "'")
Me.cmbUserFilter.Value contains the argument.
Thanks all
Dave
I need to decrease the amount of time it takes to return a set of data of the following format:
State[Provided vai user intput]->All Counties in State->All Cities in County->All Zones in City[usually < 50 or 75 per city]
current behavior:
1. currently user selects state all counties are returned from db
2. foreach county cities are returned
3. foreach city streets are returned
4. objects hydrated with data & display drawn
As you can see this gets expensive. I know there has to be a better way to do this. Any suggestions? We use sql server/c#.net.
is it possible to concatenate 2 recordsets ?
i have 2 selects and i want to display all results from the first
select and then the results from the second select query
with paging.
if i use union, all the results will be mixed.
thanks for the help!
Greetings,
I'm having SSIS block right now and thought I'd see if anyone had a simple solution to this.
What I have is SSN data coming in from several different tables, which needs to be inserted into a SurrogateSSN table which just uses an Identity field for the surrogate.
I normally build a seperate package for each detination table, so in each package that has SSN data in the source, I need to insert into the SurrogateSSN table (then I can use the surrogate values later in my package).
What I'm looking for is a simple solution if possible (preferrable using existing non-script tasks, or very short script), where I can pass in the SSN. Is there way to create a package that will accept a recordset (of SSN's) for this insert, that I can call from several different packages, and pass in the recordset of SSN's?
Thanks for your ideas
How to assign multiple values to emp1 variable separated by comma
Ex: If ajay karthik vijay are employee names in emp table
then emp1= ajay,karthik, vijay
set emp1= (select employeename from emp) returns error
error: subquery returns morethan one value
We shall be taking a bunch of 7.0 instances and moving/upgrading to a SQL 2000 cluster server. I was thinking of creating new named instances on the 2000 cluster and upgrading each 7.0 server to it's respective named instance. Also thought of using the 2000 copy database wizard; I was told this didn't always work. Anyone hear of problems with this?
Thanks
I'm sure there is probably a very easy solution that I am just not seeing or can't Google...
I have a DataFlow that includes a column of Delimited values (i.e. Value1,Value2,etc..). As this DataFlow is populating a parent table, I need split the values into their own dataflow and populate a child table. I've tried a script transformation and couldn't figure out how to accept 1 delimited input row and output multiple rows after a split. Any ideas?
TIA,
Matthew
Has any figured out a work around to having multiple reports in a single email sent out by a data driven sub in SSRS 2005?
View 3 Replies View RelatedHello,
I have a SP1 that is calling another SP2,
SP2 is using select and returning records
So in SP1 I get 3 different record sets
(as I can see it on Query Analyzer - one after the other)
When I'm trying using the ASP page I get this error:
"Item cannot be found in the collection corresponding to the requested name or ordinal."
How can I access the last recordset ?
I think it whould be simpler to explain what I am trying to accomplish.
I wish to pull a query to return multiple records from a SQL 7.0 database.
The query is a customers standing order.
I wish to make edits to multiple records then return those records for update.
The HTML pages that I have seen all seem to display tables on a singular record format.
Is it possible to display records in a 'Table' format and allow it to be updated.
Can any one answer this or direct me to a site with the relevent info.
Thanks Stuart
Indtec@eircom.net
Hello Folks,
Using just SQL (within SQL Server 2000), I'm looking to output 2 (or more) recordsets from a one-to-many join between table all on one row. Help! Can someone please show me how to do this if it can be done.
Below is an example of the situation.
Thanks a lot folks, - Jerry
************* Tables *******************
** ITEMS **
ItemID
=======
10
11
12
** ITEMSTOPARTS **
ItemID ---- PartID ---- Funktion
=================================
10 -------- 100 ------- headlight
10 -------- 120 ------- taillight
** PARTS **
PARTID ---- Manf ---- ProductName
=================================
100 ------- M1 ------ Halogens
120 ------- M2 ------ Red Lights
************* Queries *******************
-- QUERY 1 --
==============
(dashes preserve indentation)
SELECT
- i.itemID AS ItemID,
- p1.partID AS HLPartID,
- p1.manf AS HLManf,
- p1.name AS HLName,
- p2.partID AS TLPartID,
- p2.manf AS TLManf,
- p2.name AS TLName
FROM - ZItems i
- - - - INNER JOIN ZItemsToParts ip
- - - - - - ON i.itemID = ip.itemID AND
- - - - - - (ip.funktion = 'headlight' OR
- - - - - - ip.funktion = 'taillight') AND
- - - - - - i.itemID = 10
- - - - LEFT OUTER JOIN ZParts p1
- - - - - - ON ip.partID = p1.partID AND
- - - - - - ip.funktion = 'headlight'
- - - - LEFT OUTER JOIN ZParts p2
- - - - - - ON ip.partID = p2.partID AND
- - - - - - ip.funktion = 'taillight'
************* Output *******************
Using "Query 1" I get this:
(Note: HL = Headlight; TL = Taillight)
ItemID - HLPartID - HLManf - HLName --- TLPartID - TL Manf - TL Name
================================================== ==============
10 ----- 100 ------ M1 ----- Halogens - NULL ----- NULL ---- NULL
10 ----- NULL ----- NULL --- NULL ----- 120 ------ M2 ------ Red Lights
I wish to output the proceeding two record set in one row
Any ideas how this can be done with SQL Server 2000
(with JOINS in the FROM clause)?
- DESIRED OUTPUT:
ItemID - HLPartID - HLManf - HLName --- TLPartID - TL Manf - TL Name
================================================== =============
10 ----- 100 ------ M1 ----- Halogens - 120 ------ M2 ------ Red Lights
-= End-O-Message =-
Here's a question you'll never quit hearing: is there a convenient way to page through large recordsets in SQL Server 2000?
I'm writing some software which, for all intents and purposes, works like a messageboard: users can create threads, leave replies, and so on.
I have about a half million records in a few of my tables, and some of my queries return 1000s of results. I'd prefer not to return 1000s of records all at once, so I don't want to page my records in code; I'd rather page them in SQL Server. Naturally, I want to page replies. However, I don't know of a convenient way to page records in SQL Server 2000.
Hey,I've got an Classic ASP app pulling some records out of a DB, whichworked fine when it was Access. When we upped to SQL Server, all of asudden, when the Recordsets came back, all the dates were empty, eventhough the data was showing up in the DB.About a year ago (only code asp apps once and a while), I rememberseeing this and finding something that just had something to do with theorder in which something was being referenced, but I just can't for thelife of me remember what it was.The Dates are set as datetime, length of 8.Anyone know why this happens and a solution?many thanks,rob
View 1 Replies View Relatedhello,
I have several tables that have guids as their primary keys and the tables are related as follows:
Table1 - primary key = ServiceNo (Guid), Filter Key = CampaignNo
Table2 - primary key = CostBasisNo (Guid), Foreign Key = ServiceNo (from Table1)
Table3 - primary key = UserId, Foreign Key = ServiceNo (from table1)
Table4 - primary key = SourceServiceNo (Foreign Key from Table1), MemberServiceNo(Foreign Key from Table1)
what I need to do is copy all records from Table1 where CampaignNo = @CampaignNo and insert them into table1, this I can do easily but I will generate a new ServiceNo for each one and associated a new CampaigNo which is fine.
The problem comes in that I need to also copy the contents of Table2 = Table3 for all ServiceNos that have been copied from Table1 but insert the new Guid that will have been created when copying the rows in Table1
This is further compounded when I need to do the same to Table4 but this time I need to insert the newid's for SourceServiceNo and the related MemberServiceNo which all would have changed.
I haven't the first clue where to start with this task, do I need to use temporary tables, cursors? any help gratefully received, even if it's a pointer to the most efficient approach.
regards
Hi,
I'm fairly new to the SSIS world, and I've recently ported a bunch of dts packages over to SSIS. I'm an ASP.NET developer so I'm very familiar with the capabilities that configuration files give you, and I attempted to set up my solution as follows:
All of my "Data Sources" are at the project level, and added (with the same name) to each package. I wanted to have a single config file that had all of the project-level settings (i.e. connection strings, data file paths, etc). I then have a config for each package with the package level settings - i.e. variables, etc.
The problem becomes that all packages do not use all data sources. This results in an error when I try to open up a package for editing, it complains that it doesn't have a reference to data source XYZ that it is seeing in the configuration file.
Is there any way that I can get around this? If I have a password to a database change, I don't want to have to look through every config file and change it in multiple places.
Hello all,
I'm writing some stored procedures that first do an Insert or an Update, and then also do a Select to return a Recordset back to an ADO client. However an Insert or Update causes a closed recordset to be produced in ADO. I can skip over the closed recordset with the NextRecordset method.
So the question is, is there any way of stopping the closed recordset being returned? I don't want to have to call the NextRecordset method from ADO as this would mean that the client would need to know about the implementation of the stored procedure.
Has anyone got any ideas on how to get round this?
Thanks
Dave Sykes
Can anyone help with this problem.
I am doing some mods to an existing db web app in asp. I am providing a facility to copy a subset of related records in a few tables. I am using one recordset to read in the selected records and then copying the data into another recordset creating a new record in the same table. I have to then pick up the @@IDENTITY of the new record so that I can update linked records in other tables.
The @@IDENTITY value returned is null unless I close the selection recordset.
I have created a simplified version of the code to demonstrate this.
The underlying table is called 'test' and it has 2 fields, 'test_id', integer, identity and 'test_desc' varchar(50)
ASP Code
<%
dim objConn, objRS1, objRS2, objRS3
sub OpenDatabase()
'open database object here instead of in page
Set objConn = Server.CreateObject("ADODB.Connection")
'Connect object to database through global.asa application variable
objConn.Open = Application("test_ConnectionString")
'Create recordset object to use
Set objRS1 = Server.CreateObject("ADODB.Recordset")
Set objRS2 = Server.CreateObject("ADODB.Recordset")
end sub
sub CloseDatabase
Set objRS1 = Nothing
Set objRS2 = Nothing
objConn.Close
Set objConn = Nothing
end sub
function UnNull(numval)
if isnull(numval) then
UnNull="NULL"
else
UnNull=cstr(numval)
end if
end function
OpenDatabase
strSQLQuery = "select * from test where test_id in (1,2) order by test_id"
objRS1.Open strSQLQuery, objConn, 1, 3
do while not objRS1.eof
objRS2.Open "test", objConn, 1, 3
objRS2.AddNew
old_test_id = objRS1("test_id")
objRS2("test_desc") = objRS1("test_desc")
objRS2.Update
objRS2.Close
' *1 objRS1.Close
strSQLQuery = "select @@IDENTITY as new_id"
objRS2.Open strSQLQuery, objConn
new_test_id=objRS2("new_id")
objRS2.close
response.write("Old ID = " & UnNull(old_test_id) & ", New ID = " & UnNull(new_test_id))
response.write("<p>")
' *2 response.end
objRS1.movenext
loop
objRS1.close
CloseDatabase
%>
Output
Old ID = 1, New ID = NULL
Old ID = 2, New ID = NULL
If I unrem the lines *1 and *2 then the output is
Old ID = 1, New ID = 11
I realise that there are other ways of doing this but this is only a simplified example whereas the actual application is far more complicated.
Thanks is advance
Kennedy