MS SQL Server :: Array Of Recordsets

Array Of Array - IRR Function
Hi, I am using the IRR function in a report. I have created the following code so it creates an array:

Public GroupIRRArray(-1) As Double
Public Function addToIRRArray(ByVal BMV As Decimal, ByVal BAB As Decimal, ByVal EMV As Decimal, ByVal EAB As Decimal, ByVal CFB As Decimal)
Dim g As Integer
g = uBound(GroupIRRArray) + 1
ReDim Preserve GroupIRRArray(g)
if g=0 then
GroupIRRArray(g) = (CFB+EAB-BAB+BMV)*-1
else if g=1 then
GroupIRRArray(g) = (BAB-CFB-EAB+EMV)
else
GroupIRRArray(g-1)= GroupIRRArray(g-1)-(BMV)
GroupIRRArray(g) = (BAB-CFB-EAB+EMV)
end if
End Function

It works fine but now I want to create multiple groups within my report. How can I change the code so it loops on another parameter? What I had in mind was to create an initial array with the parameter value that I want to use for grouping and a dynamic array based on the name of each group. So I would end up with one array containing the group name plus x number of arrays with the raw data. Alternatively, is there a way to use the IRR function without creating a custom code? Like a conversion parameter that would make my floating field a one dimensional array?

Thanks,
Jam

View Replies !

Why Recordsets
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 Replies !

How To Concatenate Recordsets
is 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 Replies !

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

View Replies !

How To Concatenate 2 Recordsets
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!

View Replies !

Multiple Recordsets
Is it possible to return multiple recordsets from SQL to ASP in a single query?

TIA
Pilot

View Replies !

Returning ADO Recordsets
I created an MTS DLL's in VB and try to use it using OLE Automation sp_OAxxx
and I faced two problems

I get ADODB Recordset from DLL call and I want to pass these recordset as procedure output (Result Set). It can be accomplished by looping all records and fields. Is there a better way such as Cursors, XML ... BTW I can use SQL Server 2000 if I want

Second problem is when calling MTS ojects some of the parameter types raises error. When I pass string parameter to MTS DLL it works but whenever I use integer or Long as parameter DLL returns "Type mismatch" error.

Pleaseeee help

View Replies !

Returning One Of Three Recordsets
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

View Replies !

Passing Recordsets
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

View Replies !

How Can I Use The Last Recordset In A SP That Return 3 Recordsets ?
Hello,

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 ?

View Replies !

Opening Mulitple Recordsets
I have a single .asp page that opens a connection and then sequentiallyopens and closes 14 recordsets from stored procedures to obtain variousproduct information before closing the connection.Is it common practice to do something like this? Or is opening 14recordsets going to become a real problem when the page goes live and startsgetting high web traffic?Thank you in advance for any information you might provide.Dave

View Replies !

Asp / Sql Server Dates In Recordsets
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 Replies !

SQL Server 2000 And XML Recordsets
Hello !
I want to save locally a recordset as xml , make changes locally to the recordset and then connect again with the database and update the database from the xml file.

i do something like this
dim cn as adodb.connection

set cn=.... ' makes the connection
dim rs as adodb.recordset
set rs=new adodb.recordset
rs.open "Select * from mytable",cn,,,adpersistxml
rs.save app.path+"myxml.xml"
rs.close
set rs=nothing

dim newrs as adodb.recordset
set newrs=new adodb.recordset
newrs.open app.path+"myxml.xml",,,adlockbatchoptimistic,adcmdfile

then i do the changes...

newrs.fields(0).value="aaaaaa"

newrs.connection=cn
newrs.updatebatch

the problem it shows an error message something like:

' insufficient information for the table you are going to make update or
refresh'

do you have an example that does that available?
if yes please send.

also should i use the "for xml" keyword in the rs.open command ?

thanks !

View Replies !

Browser Updateable Recordsets ?
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

View Replies !

How To Output Multiple Recordsets On One Row Using SQL?
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 =-

View Replies !

Paging Large Recordsets
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.

View Replies !

Disparate Recordsets-one Xml Result To Transform
My goal is to display 3 different crosstabs on 1 page.

My proc may look something like:
SELECT stuff
FROM Products

SELECT stuff
FROM Automobiles

SELECT stuff
FROM Foods

In Classic ASP I would have used the rs.NextRecordset
(or...If I werent trying to use an XSL transform, I could use sReader.NextResult(); )

Is there a way to glue these together in one XML item so I can marry it with an XSL document?
I looked at this article:
http://www.sqlxml.org/faqs.aspx?faq=99
However, I cannot use SQLTemplates like the article has in it.

Any help would be much appreciated.

View Replies !

Delete Recordsets With Same Date And Line
Hi All!I need help with a Statement!I am working with an Access2000 DB.I have the following Problem.ChNrLinieDatum Code 39 Stückzahl BHL1 BHL2 BMRH582-064L2.1008.03.2005 02:30:00FCAA14821701582-064L2.1008.03.2005 02:30:00FCAA14871701582-114L2.1208.03.2005 01:00:00FAC827501240582-114L2.1208.03.2005 01:00:00FAC827441240582-114L2.1208.03.2005 01:00:00FAC827501240582-094L2.707.03.2005 19:45:00FAE74323481582-094L2.707.03.2005 19:45:00FAE74489481582-094L2.707.03.2005 19:45:00FAE74489481581-294L2.807.03.2005 18:20:00FA8V2658221581-294L2.807.03.2005 18:20:00FA8V2652221581-294L2.807.03.2005 18:20:00FA8V2658221582-114L2.1207.03.2005 17:45:00FAAR20721236As you can see I have a few recordsets that are double. The Thing is, thereis an ID that makes them different.I need a Statement that deletes the surplus records where 'Datum' and'Linie' are identical to another record. 1 record has to remain of course.I thought of something like this.DELETE FROM tbAuswertWHERE EXISTS(SELECT *FROM tbAuswertWHERE (Linie AND Datum)IN (SELECT Linie AND Datum AS SuchkritFROM tbAuswertGROUP BY SuchkritHAVING ((Count(Suchkrit)>1)))But I get an error:You wanted to execute a Query that did not have the following expression'Not Linie = 0 And Not Datum = 0' in its AggregatefunctionPerhaps you ccan help me.ThanksJulia--Message posted via http://www.sqlmonster.com

View Replies !

Empty Recordsets And Artificial Records
I'm currently running the following statement that is used in a CrystalReport. Basically, a record is returned when the T_PAYMENT.amounthas a record in the database based on the value of the T_MULTILIST.codefield. Currently, if there is no record returned, there is no listingin the report for the given T_MULTILIST.code.The user now wants a record to be displayed on the report when there isno record in the database - she wants it to display a value of '$0'for the given T_MULTILIST.code record. I tried to explain the fact thatis not possible the way things stand at the moment. Basically I needsome typeof case statement that says 'if there is no record returned, create asingle record and set T_PAYMENT.amount = 0' AFTER each query has beenexecuted.Anyone have any idea how to accomplish this?SELECT DISTINCT'English Language Arts, Grade 1' as Rec_Type, 'English Language Arts(Consumable)' as Super_Type, '' as Other_Type, 'Continuing Contracts'as Proc_Type,T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,T_PAYMENT.amountFROM (T_MULTILIST T_MULTILIST INNER JOIN (T_PAYMENT T_PAYMENTINNER JOIN T_SHIPPING_DETAILT_SHIPPING_DETAIL ONT_PAYMENT.transaction_id=T_SHIPPING_DETAIL.transac tion_id)ON T_MULTILIST.code=T_SHIPPING_DETAIL.multilist_code) INNER JOINT_MULTILIST_GRADE T_MULTILIST_GRADE ONT_MULTILIST.code=T_MULTILIST_GRADE.multilist_code, T_ORDER, T_REQUISITION, T_REQUISITION_DETAILWHERET_ORDER.id = T_SHIPPING_DETAIL.order_id ANDT_REQUISITION.id = T_ORDER.requisition_id ANDT_REQUISITION_DETAIL.requisition_id = T_REQUISITION.id ANDT_REQUISITION_DETAIL.latest_record_flag = 1 ANDT_REQUISITION.latest_record_flag = 1 ANDT_ORDER.latest_record_flag = 1AND (T_MULTILIST.code='1040')AND (T_MULTILIST.expiration_year >= '2006' )AND (T_REQUISITION.requested_shipment_date >= '2006' + '0601'AND T_REQUISITION.requested_shipment_date < dateadd(YY, 1,'2006' + '0601' ) )UNIONSELECT DISTINCT'English Language Arts, Kindergarten' as Rec_Type, 'EnglishLanguage Arts (Consumable)' as Super_Type,'' as Other_Type, 'Continuing Contracts' as Proc_Type,T_MULTILIST_GRADE.grade, T_MULTILIST.description,T_MULTILIST.code, T_PAYMENT.amountFROM (T_MULTILIST T_MULTILIST INNER JOIN (T_PAYMENT T_PAYMENTINNER JOIN T_SHIPPING_DETAIL T_SHIPPING_DETAILON T_PAYMENT.transaction_id=T_SHIPPING_DETAIL.transac tion_id)ON T_MULTILIST.code=T_SHIPPING_DETAIL.multilist_code) INNER JOINT_MULTILIST_GRADE T_MULTILIST_GRADE ONT_MULTILIST.code=T_MULTILIST_GRADE.multilist_code, T_ORDER, T_REQUISITION, T_REQUISITION_DETAILWHERET_ORDER.id = T_SHIPPING_DETAIL.order_id ANDT_REQUISITION.id = T_ORDER.requisition_id ANDT_REQUISITION_DETAIL.requisition_id = T_REQUISITION.id ANDT_REQUISITION_DETAIL.latest_record_flag = 1 ANDT_REQUISITION.latest_record_flag = 1 ANDT_ORDER.latest_record_flag = 1AND (T_MULTILIST.code='0040')and (T_MULTILIST.expiration_year >= '2006' )AND (T_REQUISITION.requested_shipment_date >= '2006' + '0601'AND T_REQUISITION.requested_shipment_date < dateadd(YY, 1,'2006' + '0601' ) )Up to 40 more UNION statements follow the above 2.

View Replies !

Rotating Information From Recordsets To Columns.
Hello,This problem perplexes me and I hope that someone has done somethingefficient.Take for example the data in the MASTER..SYSPERFINFO:SELECTCAST(RTRIM(INSTANCE_NAME) AS VARCHAR(15)),CAST(RTRIM(COUNTER_NAME) AS VARCHAR(31)),CAST(RTRIM(CNTR_VALUE) AS VARCHAR(10))FROM MASTER..SYSPERFINFOWHERE INSTANCE_NAME = N'TEMPDB'tempdb Data File(s) Size (KB) 51200tempdb Log File(s) Size (KB) 1272tempdb Log File(s) Used Size (KB) 738tempdb Percent Log Used 58tempdb Active Transactions 0tempdb Transactions/sec 186281tempdb Repl. Pending Xacts 0tempdb Repl. Trans. Rate 0tempdb Log Cache Reads/sec 0tempdb Log Cache Hit Ratio 0tempdb Log Cache Hit Ratio Base 0tempdb Bulk Copy Rows/sec 0tempdb Bulk Copy Throughput/sec 0tempdb Backup/Restore Throughput/sec 0tempdb DBCC Logical Scan Bytes/sec 0tempdb Shrink Data Movement Bytes/sec 0tempdb Log Flushes/sec 1578tempdb Log Bytes Flushed/sec 67882496tempdb Log Flush Waits/sec 226tempdb Log Flush Wait Time 47tempdb Log Truncations 248tempdb Log Growths 3tempdb Log Shrinks 0<I did the CAST and LTRIM so that it looks better when displayed in abrowser>I would like to keep statistics in a table with the following columns:INSTANCE_NAME,DATA_FILE_SIZE,LOG_FILE_FIZE,ACTIVE_TRANS,TRANS_PER_SECSo, instead of having a table with three columns and 23 rows(only 4 ofwhich I want), I would have a single row with 4 columns(plus theInstance_Name).Visualy, I want to call this a 90 degree rotation. Here's what theselect statement would then look like:SELECT *FROM SYSPERFINFO_ARCHIVEWHERE INSTANCE_NAME = N'TEMPDB'Here's the result set:tempdb 51200 1272 0 185198Is it possible to 'rotate' a recordset into columns?How would it be done?Gracias.

View Replies !

Closed Recordsets From Stored Procedures
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

View Replies !

Emty Recordsets, When Records Exist
In our web application, we are experiencing a random occurence.
On occasion, (10 or so times a day) a recordset is returned "Empth" or EOF true.

We know this is a SQL problem, because we write the exact SQL string out that caused the empty RS and it does return a row when run directly in Query analyzer.

It's a stored proc that returns a status code of what happend.
So the empty RS was actually empty on the SQL side of things.

Anyone have any ideas.
We noticed the HD on the SQL server was Very fragmented....

View Replies !

Unable To Merge Two Stand-alone Recordsets

I have two stand-alone recordsets. They have the exactly the same fields definitions. They come from different ADODB connections (Microsoft Jet). And I cannot merge them into one recordset.

I was trying to merge the second recordset into the first, but instead of adding the records, the first recordset becomes populated with infinite copies of the first record of the second recordset. This seems to me a VB6 bug.

Any help on how to go around this problem will be really appreciated. Thanks a lot. Frank

This is the code:

(Please note, the INVENTORY table contains different data but has the same structure in both databases.)

Code Snippet

Dim S1ADO As New ADODB.Connection

Dim S2ADO As New ADODB.Connection

S1ADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Z: [...]"

S2ADO.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Y: [...]"

RS1.Open INVENTORY, S1ADO, adOpenDynamic, adLockOptimistic
RS2.Open INVENTORY, S2ADO, adOpenDynamic, adLockOptimistic

RS2.MoveFirst

While Not RS2.EOF

RS1.AddNew

For A=0 to RS1.Fields(A).Count - 1

RS1.Fields(A).Value = RS2.Fields(A).Value

Next

RS1.Update

RS2.MoveNext

Wend

View Replies !

@@IDENTITY Problem With Multiple Recordsets
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

View Replies !

Report From Prcedure That Return Multiple Recordsets
Hi there,

I am creating a report from a stored procedure that returns
multiples record sets. For an example, my stored procedure is as follows

CREATE PROCEDURE MYPROCEDURE
AS

BEGIN
SELECT * FROM TABLE1
SELECT * FROM TABLE2
SELECT * FROM TABLE3
END

Now lets say I want to create a report that will display the
result of the query no 2 (which is SELECT * FROM TABLE2 in this example). How
can I do this? Is there any way to bind the second record set to the report
dataset with out changing the stored procedure?
I will appreciate any kind of suggestions on this
Thanks
Moim

View Replies !

After Moving To SQL2005, Disconnected Recordsets Are Ready-only
We're using ADO disconnected recordsets. On SQL 2000, we could update these on the client (without propagating the changes to the server) even if the underlying view or table was non-updatable.

When running our apps against SQL 2005 (using the same client-side environment), we can no longer change any attributes of those disconnected recordsets, that connect to a non-updatable database object (the rest of the app runs fine, we can update all updatable database objects through disconnected recordsets) . Does SQL 2005 respond to such calls differently from SQL 2000, so that ADO recordsets are built in a new way (which makes them read-only in our setting)?

Thanks for any suggestions.

Rene

View Replies !

Inserting Few Recordsets Into Temp Table Containing NULL Values
HiI need some help on achieving the following:I wrote a querie which collects Data out of three Tables, the Resultlooks like this:SET NOCOUNT ONDECLARE @ROWINTDECLARE @CURPTNO CURSORSET @CURPTNO = CURSORFORSELECT * FROM TEMPOPEN @CURPTNOFETCH NEXT FROM @CURPTNOINTO @ROWWHILE (@@FETCH_STATUS = 0)BEGINSELECT ONE.CYNO, ONE.ROLE, ONE.ROL_BEZ, ONE.PTNO, ONE.NOCY, TWO.TEXTAS NOCY_TEXTFROM(SELECT CY.CYNO,RE.CYNO AS RECYNO, RTRIM(RE.ROLE) AS ROLE, RTRIM(V_ROLE.TEXT) ASROL_BEZ,RE.PTNO AS PTNO, RTRIM(RE.NAME) AS SACHBEARBEITER, RE.NOCYFROM CYRIGHT OUTER JOINRE ON RE.CYNO = CY.CYNOINNER JOINV_ROLE ON RE.ROLE = V_ROLE.CODEWHERE (RE.PTNO IN(SELECT PT.PTNOFROM PTWHERE PT.PTNO IN(@ROW)AND V_ROLE.LANGUAGE = PT.CLANG))) AS ONELEFT JOIN(SELECT V_NOCY.CODE, V_NOCY.TEXTFROM V_NOCYINNER JOINPT ON PT.CLANG = V_NOCY.LANGUAGEAND PT.PTNO IN (SELECT PT.PTNOFROM PTWHERE PT.PTNO IN(@ROW))) AS TWOON ONE.NOCY = TWO.CODEFETCH NEXT FROM @CURPTNOINTO @ROWENDCLOSE @CURPTNODEALLOCATE @CURPTNOThe Result looks like this:RS1:6313,1300,Architekt,99737505,NULL,NULL2392762,100,Bauherr,99737505,NULL,NULLRS2:2693265,100,Bauherr,99756900,NULL,NULLNULL,1,Planer,99756900,2,Bauherr macht Pläne selberRS3:2691919,100,Bauherr,99755058,NULL,NULL2691962,6000,Kontakt,99755058,NULL,NULLMy Problem is, that I need to have all the Resultsets in one Table atthe end.So my further undertaking was to create a Temp Table with theexpectation to receive all the resultsets in one Step.The TSQL for this looks like that:SET NOCOUNT ONCREATE TABLE #CYRE_TEMP(CYNOINT NULL,ROLEINT NULL,ROL_BEZVARCHAR (60) NULL,PTNOINT NULL ,NOCYINT NULL,TEXTVARCHAR (60) NULL)GODECLARE @CYNOINT,@ROLEINT,@ROL_BEZVARCHAR (60),@PTNOINT,@NOCYINT,@TEXTVARCHAR (60),@ROWINT,@CURPTNOCURSORSET @CURPTNO = CURSOR FORSELECT PTNO FROM TEMPOPEN @CURPTNOFETCH NEXT FROM @CURPTNOINTO @ROWWHILE (@@FETCH_STATUS = 0)BEGININSERT INTO #CYRE_TEMP (CYNO, ROLE, ROL_BEZ, PTNO, NOCY, TEXT)VALUES(@CYNO,@ROLE ,@ROL_BEZ ,@PTNO ,@NOCY ,@TEXT)SELECT @CYNO = ONE.CYNO,@ROLE = ONE.ROLE,@ROL_BEZ = ONE.ROL_BEZ,@PTNO = ONE.PTNO,@NOCY = ONE.NOCY,@TEXT = TWO.TEXTFROM(SELECT CY.CYNO, RTRIM(RE.ROLE) AS ROLE, RTRIM(V_ROLE.TEXT) ASROL_BEZ,RE.PTNO AS PTNO, RTRIM(RE.NAME) AS SACHBEARBEITER, RE.NOCYFROM CYRIGHT OUTER JOINRE ON RE.CYNO = CY.CYNOINNER JOINV_ROLE ON RE.ROLE = V_ROLE.CODEWHERE (RE.PTNO IN(SELECT PT.PTNOFROM PTWHERE PT.PTNO =@ROWAND V_ROLE.LANGUAGE = PT.CLANG))) AS ONELEFT JOIN(SELECT V_NOCY.CODE, V_NOCY.TEXTFROM V_NOCYINNER JOINPT ON PT.CLANG = V_NOCY.LANGUAGEAND PT.PTNO IN (SELECT PT.PTNOFROM PTWHERE PT.PTNO =@ROW)) AS TWOON ONE.NOCY = TWO.CODEFETCH NEXT FROM @CURPTNOINTO @ROWENDCLOSE @CURPTNODEALLOCATE @CURPTNOSELECT * FROM #CYRE_TEMPDROP TABLE #CYRE_TEMPGOAnd the Output looks like this now:Q1:NULL,NULL,NULL,NULL,NULL,NULL2392762,100,Bauherr,99737505,NULL,NULLNULL,1,Planer,99756900,2,Bauherr macht Pläne selberCan someone help me getting all the 6 Rows into one Table as Output?I appreciate any available Help on this..Ssscha

View Replies !

Output Parameters Versus Recordsets In Stored Procedures
I've read that stored procedures should use output parameters instead ofrecordsets where possible for best efficiency. Unfortunately I need toquantify this with some hard data and I'm not sure which counters touse. Should I be looking at the SQL Server memory counters or somethingelse.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !

SQL7 + Stored Proc's = Files [Persistant Recordsets]
Hi,

I was wondering, is there any way to have a stored procedure write to a file its resultant recordset?

i.e. I have this SP which fires a query, this in turn returns a single row recordset that I need to have written to a file. I realise this could easily be facilitated using an ADO client, however, can it be done by the backend i.e. the SQL7 & SP itself, directly?

I hope you can help me here! thanks for your time!
Cheers
Gaurav

View Replies !

Access 2003 / SQLSEE 2005 - Using Forms To Filter Recordsets
I upsized an Access 2003 database to SQL Server Express Edition 2005, and have converted all tables, queries, reports and macros successfully so far. The snag I'm running into involves using macros embedded in buttons on certain forms using Access as the frontend.

With these forms, the idea is to navigate to a certain record, and hit a button to bring up a print preview view of a report. Based on the record you're looking at in the form, it should check against the data in the table to which it refers, and pull up a single record in the report.

In access as a standalone, using the OpenReport command, the code was as follows:

[MRB Report Data]![MRB Report Number]=[Forms]![MRB Data Input]![MRB Report Number]

MRB Report Data is the table containing data, and MRB Report Number is the primary key (autonumber). MRB Data Input is the form in question, and the second MRB Report Number is the control field that references back to the MRB Data Input table.

When I try to run this using Access as a frontend, an error occurs at the first '!' - is there a different syntax I need to specify to reference a form in this case?

Thanx in advance.

View Replies !

Current Provider Does Not Support Returning Multiple Recordsets From A Single Execution
Hi all -

I know this is prolly an old one but I would certainly appreciate some assistance =)

environment:

SERVER (IIS6, .NET2.0, SQL2005)

CLIENT (WIN2000,IE6,VBScript)

I have an aspx that is invoking ado on the clientside. I read somewhere that javascript does not support connecting to SQL clientside. I may be wrong. In any case I am using VBScript on the client.

I am using something like this to invoke the script

Code Snippet

<a href="#" onclick="doReport()">Click Here</< FONT>a>

doReport() looks something like this ...

Code Snippet

' ========================================================

sub doReport()

Dim stSQL, rs, oWord, oDoc, oRng

Dim stCurrentSection, stTemplatePath, dtNow, dtSOR, dtLastDataPoint

Dim iLastDataPoint

set oWord = CreateObject("Word.Application")

stTemplatePath = "http://crivm-ccdev/ccprocharts/supportfiles/CCWordReport.doc"

set oDoc = oWord.Documents.Open(stTemplatePath)

oWord.visible = true

stCurrentSection = "a"

loadDebug

'loadLive

openConnection()

' == THIS DB CALL GETS 2 RESULT SETS

'set rs = getrsCustomerInfoReport(mstUnits, left(mstCycles,3), mstAppName)

set rs = getReadOnlyRS("sCC_GetCustInfo_Report '" & mstUnits & "','" & left(mstCycles,3) & "','" & mstAppName & "'")

' == POPULATE VARS FROM 1st RESULT SET

dtLastDataPoint = rs(0)

iLastDataPoint = rs(1)

set rs = rs.NextRecordset ' <== THIS IS WHERE IS ERROR IS

msgbox rs(0)

end sub

here is my openconnection sub ... oConn is global

Code Snippet

<script type="text/vbscript" language="vbscript">

'== Cursor Location

CONST adUseClient = 3

CONST adUseServer = 2

' == Cursor Type

CONST adOpenStatic = 3

CONST adOpenForwardOnly = 0

CONST adOpenDynamic = 2

' == Lock Type

CONST adLockReadOnly = 1

CONST adLockOptimistic = 3

CONST adLockPessimistic = 2

' ========================================================

sub openConnection()

Dim stConn

set oConn = CreateObject("ADODB.Connection")

'stConn = "DRIVER={sql server};Server=crivm-ccdevsql2005;Database=catcheck;Integrated Security=SSPI"

stConn = "Provider=SQLOLEDB;Server=crivm-ccdevsql2005;Database=catcheck;Integrated Security=SSPI"

'msgbox oConn.state & vbcrlf & stConn1

oConn.open(stConn)

'msgbox oConn.state

ApplySecurity

end sub

' ============================================================

Sub ApplySecurity

'gbhasDBAccess is a global variable on both client and server sides.

'Server side is set in seccheck.asp, which should be at the top of every page

'Client side is set in ApplySecurity() of ConnectServer.asp

Dim appRole

appRole = "appWriters,(tsvc123)"

oConn.Execute "sp_setapprole '" & split(appRole,",")(0) & "','" & split(appRole,",")(1) & "'"

End Sub

</< FONT></script>

Before we moved to SQL2005 I always used this

Code Snippet

' ========================================================

Function getReadOnlyMultRS(strSQL)

set rs = CreateObject("ADODB.Recordset")

rs.CursorLocation = adUseClient

rs.Open strSQL, oConn, adOpenForwardOnly, adLockReadOnly

'Disconnect the Recordset

Set rs.ActiveConnection = Nothing

'Return the Recordset

Set getReadOnlyMultRS = rs

End Function

and this always allowed me to use set rs = rs.nextresultset

but now that we switched to SQL2005 it does not seem to work. I have verified in sql studio that this sp does indeed return 2 resultsets

View Replies !

ADO Error 3251: Current Provider Does Not Support Returning Multiple Recordsets From A Single Execution?
I posted this in the MS Access group, but no luck.------------------------------------------I've got another stored procedure in the same app that returns multiplerecordsets and the code works.But now I've written another SP and the code traps out with the 3251 message.The SP is writing two recordsets.When I run the SP in Query Analyzer, both recordsets appear.But when I step through the code, when the first RS should be there, it's"Closed" and nothing I've tried will make it open.Provider=SQLOLEDB.1 (which works on the other screen...)Seems like I've been here before, but I can't remember what the problem was.--PeteCresswell

View Replies !

Named SQL 2005 Instance Multiple Active Recordsets-C# Stored Procedure-Connection String
I am working on a C# stored procedure for SQL 2005, and i've uncovered a couple questions.

First a description of the procedure:

I have a series of equations taking place to calculate a score based on activities in which the user participated in, that will give them an over all grade or rating. The calculations currently take place in the database, and I am moving this from T-SQL to C# CRL.

1. In order to connect the stored procedure to the database I use a SqlConnection and a SqlCommand to execute either dynamic sql or a stored procedure to return data to a data reader. Is there an easier way to connect to the database? In SSMS if i open up the query it knows what database i am connected to. Do I have to make a sql connection in C# stored Procedures?

2. I have multiple functions within the main C# Stored Procedure that I'm working on. This ends up requiring Multiple Active Recordsets. I must set this withing the connection string. Seeing as I'm using a named instance of SQL 2005, I now must put the userid, password, and server name into the code. Is there a more secure way to connect to SQL Server in a C# Stored procedure that allows MARS?

3. I encryped the connection string, and put it into the assembly, I wrote a decryption class, and in the procedure itself everytime I need to refrence the connection string, I call it, decrypt it, and pass it along. But my code to decrypt the connection string is in the compiled DLL, if the server was ever compromised the encrypted connection string and the key to decrypt it are sitting in the DLL. Is there a config file that I can use for C# Stored Procedures?

4. If I have to keep the connection string in the file, then I need to change that per environment. Example I have 3 test environments before production. So I would need to change the connection string for each file. That may be fine for one procedure, but what if I have 20, that will quickly get of hand?

5. Along the security lines, can the assembly for a C# Stored Procedure be called from outside the assembly? From a command prompt, or by a maliceous program? Or could it be called directly by a .NET application instead of going through a T-SQL Stored Procedure that is using
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [PROJECTNAME].[CLASSNAME].[METHODNAME]

Thanks

View Replies !

Is There Any Way To Ignore Those &&"virtual Recordsets&&" When I Use Oledb To Connect To SQL Server?
I have a SQL database and a lot of stored procedure in it. and i have an excle vba application used to get datas from the database and make reports of these datas. we connected to the sql server using odbc link with dsn. now i want to use oledb insead so that we do not need to create a dsn everytime we move to a new machine, but after i changed the connection string to oledb style, i got an error on calling a procedure: "Operation is not allowed when the object is closed".

i have found the reason that causes this probem on the Internet, that is: those procedures in the database were written without a "set nocount on" statement, so they returns "virtual recordset" when exeuteing every statement that do not produce real recordset, odbc will throw these "vitrual recordsets" while oledb sent them to my app directly.

i don't want to modify those procedure as thay have too many...i thought maybe i can use a dsn-less odbc connection to access the database to achieve my purpose, but i just want to know: is there any way to let oledb ignore or filter those "virtual recordsets" away?

View Replies !

Array
I messed up my question so badly a few posts ago, that I'm going to lay out the problem carefully as follows:

On a spreadsheet:

--there are a few names down column A, one name per row (e.g., r3c1Mary, r4c1Scott, r5c1Jane, r6c1Ann, r7c1Cathy, r8c1Jim)

--there are a bunch of columnar animals heading up row 1 (e.g., r1c2cat, r1c4dog, r1c6horse, r1c8bird, r1c10snake, r1c12elephant, r1c14goat, r1c16giraffe, r1c18ox, r1c20rat, r1c22monkey, r1c24pig)

--(might be addressed in a later post to this or another thread): on row 2, there is a Budget field and a Cost field for each animal (e.g., r2c2Bud, r2c3Cost, r2c4Bud, r2c5Cost, r2c6Bud, r2c7Cost, r2c8Bud, r2c9Cost, r2c10Bud, r2c11Cost, r2c12Bud, r2c13Cost, r2c14Bud, r2c15Cost, r2c16Bud, r2c17Cost, r2c18Bud, r2c19Cost, r2c20Bud, r2c21Cost, r2c22Bud, r2c23Cost, r2c24Bud, r2c25Cost)

But for now, just the people names down the left side, and the animals across the top would suffice to pose my question.

The question is this: the number of animal types gets added to, or subtracted from, across the top of the page every month. On July, there might be 12 animal kinds listed; but on August, there might be 19--or there might be 11. (The number of people involved change as well.)

As a user, I don't know how many and of what kind of animals (aka fields) will be on the page on any given month. The underlying query to the table doesn't know whether a Giraffe is going to be a category, among others at the top of the sheet, from one month to the next. If a garden variety query was written, it would hard code: SELECT r1c2cat, r1c4dog, r1c6horse, r1c8bird, r1c10snake, r1c12elephant, r1c14goat, r1c16giraffe, r1c18ox, r1c20rat, r1c22monkey, r1c24pig.

But that rots. The query would have to be re-written for every new list of applicable animals each month. Or even more ugly, I'd have a predetermined number of variables to hold each type of animal; some would go unused while a number exceeding the variables available wouldn't make it into the query, or whatever.

I could on the other hand slowly loop through a SELECT sAnimalType for each sPerson. Slowly.

That's where my question comes in: Can a query be written that allocates a SELECT Array(sAnimalType)?

(And if so, I hope I can extract the sequential info accordingly.)

Simplified, I currently have an Access table with two columns (four if we go whole hog): Person, Animal (, Budget, Cost). (But I'm perfectly happy to leave Budget and Cost for a separate posting. Maybe that's a sub query or something.)

Thanks for any assistance that you can provide.

View Replies !

Array
how can i send array from asp.net to sql?

View Replies !

Parameterized Or Array With This SQL?
I have two CheckBoxList controls. One CheckBoxList is a group of area codes as they apply to our customers, and the second CheckBoxList is a group of categories of those customers. The code below works fine with either CheckBoxList as a standalone (this code applies to the Area Codes selection), but what I need is the VB code to combine the choices a user makes in both CheckBoxLists. Is this where parameterized SQL comes into play? Or can I/should I use an array statement to combine both CheckBoxList choices? Sometimes a user will select nothing in one CBL and a few choices in the other, or vice versa, or a handful of choices in both CBLs, so that they might want only customers in, say two area codes and then only the selected categories of those area codes. Need help on this one, thanks...Protected Sub btn_CustomerSearchCombine_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_CustomerSearchCombine.Click Dim ACSelections As Boolean = False Dim ACItem As ListItem For Each ACItem In cbl_CustomerSearchAREA.Items If ACItem.Selected Then ACSelections = True End If Next If ACSelections = True Then Dim ACSqlString As String ACSqlString = "SELECT Customers.CustomerID, Customers.CustomerName, Customers.CategoryID, Customers.EstHours, Customers.Locality, Categories.Category FROM Customers INNER JOIN Categories ON Customers.CategoryID = Categories.CategoryID WHERE " For Each ACItem In cbl_CustomerSearchAREA.Items If ACItem.Selected Then ACSqlString &= "Customers.AreaCodeID = '" & ACItem.Value & "' OR " End If Next ACSqlString = Left(ACSqlString, Len(ACSqlString) - 4) ACSqlString &= "ORDER By Customers.CustomerName" sql_CustomerSearchGrid.SelectCommand = ACSqlString End IfEnd Sub

View Replies !

DataRow Array
Hi,
i m pretty new to this forum and c#.net
i m doin a project in c#.net
I have four values in my datarow array
for example
DataRow[] cmb;
cmb=dsResult.Tables[0].Select("Controls Like 'cmb%'");// Here i m getting four Rows

for(i=0;i<cmb.Length;i++)
{
cmb[i]=Session["cmb'+i].ToString().Trim()//Here i m getting error;Cannot implicitly convert type 'string' to 'System.Data.DataRow'
}

How to assign my session values to them.
I want to assign my value stored in the session variable to that array.Is there any way i can do it.Can i convert datarow array to string array! Please can any one help
me.

View Replies !

SqlCommand Array Help
I want to do something like the following but I get an error: Object reference not set to an instance of an object. SqlConnection sqlConnection = new SqlConnection("server=xxxxx"); SqlCommand [] cmd = new SqlCommand[3]; Object returnValue; cmd[0].CommandText = "DO QUERY"; cmd[1].CommandText = "DO QUERY"; cmd[2].CommandText = "DO QUERY"; cmd[3].CommandText = "DO QUERY"; } sqlConnection.Open();int i = 0;while(i<4){ cmd[i].CommandType = CommandType.Text; cmd[i].Connection = sqlConnection; cmd[i].ExecuteNonQuery(); returnValue[i] = cmd[i].ExecuteScalar();i++} sqlConnection.Close();How should I do the followingThanks

View Replies !

Array In WHERE Clause
Hello, how can I use an array in a WHERE clause? If I had an array saystring[] NamesArray = new string[] {"Tom", "***", "Harry"} How would I do this:string myquery = "SELET name, city, country FROM myTable WHERE name in NamesArray" Thanks in advance,Louis

View Replies !

Array Problem
Hi All,
I have this code below:Dim a As Integer = 0 While a <= myArray.Length() Conn.Open() Dim UpdateCmd As New SqlClient.SqlCommand("Update email_addr SET category = 'Deleted' where HP = @hp", Conn) UpdateCmd.Parameters.Add("@hp", SqlDbType.VarChar, 50).Value = myArray(a) UpdateCmd.ExecuteNonQuery() a = a + 1 Conn.Close() End While
BUt, When I run it, I receive error "Prepared statement '(@hp varchar(50))Update email_addr SET category = 'Deleted' wher' expects parameter @hp, which was not supplied. "
DOes anyone have the solution???
Thanks....

View Replies !

SQL Query In A VB.net Array
I want to do a query on an SQL Server 2005 db and have the results returned into aarray or collection in vb.net... how do I do this? I know the basicconnection and stuff.. just not how to get the result to an array thanks!

View Replies !

Get Array Value From A Loop?
Hi, I am trying to do a loop while a list of array is assigned ('CHP,CNH,COW') ... I am using comma seperator to get each list value ... but, it donest really do what I am trying to do ... pls help!!! How do I loop through each value and do the rest ...??
=====================================
DECLARE @ABBR AS NVARCHAR(50)SET @ABBR = 'CHP,CNH,COW'
DECLARE @SEP AS NVARCHAR(5)SET @SEP = ','
WHILE patindex('%,' + @ABBR + ',%', @ABBR ) > 0 BEGIN
-- do the rest
END

View Replies !

Result Set Into Array
Hi All,

How can I read a query result set (which are of type VARCHAR) into an array? i.e the result set comprises of just one column and 5 rows and i want to save these into an array to easily extract each row whenever i want to. Hope I have conveyed my idea clearly.

Gayathri

View Replies !

How To Identify An Array
I have seen several examples explaining the fact that a tablecontaining a field for each day of the week is for the most part anarray. An specific example is where data representing worked hours isstored in a table.CREATE TABLE [hoursWorked] ([id] [int] NOT NULL ,[location_id] [tinyint] NOT NULL,[sunday] [int] NULL ,[monday] [int] NULL ,[tuesday] [int] NULL ,[wednesday] [int] NULL ,[thursday] [int] NULL ,[friday] [int] NULL ,[saturday] [int] NULL)I had to work with a table with a similar structure about 7 years agoand I remember that writing code against the table was pretty close toHell on earth.I am now looking at a table that is similar in nature - but different.CREATE TABLE [blah] ([concat_1_id] [int] NOT NULL ,[concat_2_id] [int] NOT NULL ,[code_1] [varchar] (30) NOT NULL ,[code_2] [varchar] (20) NULL ,[code_3] [varchar] (20) NULL ,[some_flg] [char] (1) NOT NULL) ON [PRIMARY]The value for code_2 and code_3 will be dependently null and they willrepresent similar data in both records (i.e. the value "abc" can existin both fields) . For example if code_2 contains data then code_3 willprobably not contain data.I do not think that this is an array. But with so many rows wherecode_2 and code_3 will be NULL something just does not feel right.I will appreciate your input.

View Replies !

Array Of Recordsets

Hi, I have a form with several tabs, on each tab has a similar structure : on each tab description of a table in database (different tables on each tab). I was wondering : is there a possibility to create an array of recordsets? (in this case i could attach an index of recordset to index of tab )

Hi,
I have a form with several tabs, on each tab has a similar structure : on each tab description of a table in database (different tables on each tab). I was wondering : is there a possibility to create an array of recordsets? (in this case i could attach an index of recordset to index of tab )