How To Consume A Datareader Destination In Code?
Jun 20, 2005Hello,
View 9 Replies
ADVERTISEMENT
How Can I Get Data From DataReader Destination?
Apr 26, 2006Hi:
I am a beginner at SSIS subjects, i have a data stored at DataReader Destination and i want continue working whit the data. Somebody can help me?
Regards. deniscuba
SSIS Datareader Destination As Datasource For SSRS
May 17, 2007I am currently trying to use a SSIS datareader destination as a datasource in Reporting Services (RS). I have sucessfully developed the report and I am able to execute and see the results from it in the RS preview pane. I am also able to deploy it (same server as RS development and report is on) without errors. However, once deployed, I am unable to get the report to work in Report Manager. I get the following error message:
"An error has occurred during report processing. Query execution failed for data set Dynamic_POS_BO_xRef'. The package failed to execute."
I have read everything in BOL regarding configuration of the RS Execution Account and have configured it with a domain member that has admin rights and tryed it with one that has limited rights (best practice recommendation).
Any ideas about what else I can try?
Access To Datareader Destination On A Remote Package
Jan 22, 2008I'm fairly new to SSIS, and I'm looking for some general guidance on the best approach to take to a particular issue. Essentially, I've built a web application, running on a remote server, and I'm looking to take the contents of an Excel spreadsheet, load it into memory, run a whole mess of calculations against it, and then load the results into a table on MS-SQL 2005. The calculations are in a class library, written in C#, so my sense was that the best approach would be to a) load the data into a datareader, b) run the calculations against the data and c) load it to SQL. Of these steps, a) seems like the place where SSIS would come into play.
My question is, once I've got the data into a datareader, how can it be accessed from my code? I'd been playing around with DtsClient until having the realization that it was intended to be used locally. Is there a similar method for doing this sort of thing remotely?
Datareader Destination As Source For Other Datareader Source ?
Aug 30, 2006HI!
as far as I know from docs and forum datareader is for .NET data in memory. So if a use a complex dataflow to build up some data and want to use this in other dataflow componens - could i use data datareader source in the fist dataflow and then use a datareader souce in the second dataflow do read the inmemoty data from fist transform to do fursther cals ?
how to pass in memory data from one dataflow to the next one (i do not want to rebuild the logic in each dataflow to build up data data ?
Is there a way to do this ? and is the datareader the proper component ? (because its the one and only inmemory i guess, utherwise i need to write to temp table and read from temp table in next step) (I have only found examples fro .NET VB or C# programms to read a datareader, but how to do this in SSIS directly in the next dataflow?
THANKS, HANNES
DtsConnection -- Consuming DTS DataReader In Code W/ Parameters
Sep 27, 2005I am trying to pass a parameter into the DtsCommand object, but always get the error "a requested parameter does not exist in the package". I have a variable defined at the top scope named EmailPromotion so I tried the following
View 9 Replies View RelatedCode Creating A Flat File Destination
Apr 21, 2006I'm looking for a manner to create by code a flat file connection manager and a flat file destination.Greets
View 3 Replies View RelatedScript Destination Component Code Sample Here That Creates A Csv Or Xml File
Jul 11, 2006After much work and thanks to all of you who helped on this here is a code sample that can be adapted. From the dataflow task add an OLEDB source component, a row count component and finally a Script Destination Component.
On the Script Destination Component rename the Input node of the imports and outputs tree view to "ParsedInput"
The readonly User: variables that start with gs can be read in the PreExecute method
The readwrite User: variable giSuccessCount can only be used in the post execute task because it is populated by the Row Count Component which is the previous object in the Dataflow
The xml code is adapted from an idea in Donald Farmers book
enjoy
Dave
Now if someone can make a Script Source Component that can read a file with a header , data body and trailer that would b egreat!
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Xml
Public Class ScriptMain
Inherits UserComponent
Dim sw As StreamWriter
'In addition to using the Imports System.Xml statement a reference must be added to the
'System.Xml assembly (Select Project-Add Reference from IDE)
Dim xWriter As XmlTextWriter
Dim OutputFileType As String '.csv or .xml
Public Overrides Sub PreExecute()
'Read Only variables
Dim gsPickUp As String = Me.Variables.gsPickUp 'D:ftprootOutAvid'
Dim gsPickUpFilename As String = Me.Variables.gsPickUpFilename '1_AVID_'
Dim gsPickUpFileExtn As String = Me.Variables.gsPickUpFileExtn '.csv'
Dim gsMemoText As String = Me.Variables.gsMemoText 'Memo Text : credit adjustment'
Dim gsStatementText As String = Me.Variables.gsStatementText 'Statment Text : credit adjustment'
Dim gsRunMode As String = Me.Variables.gsRunMode 'UPDATE'
Dim fileName As String = gsPickUp & "" & gsPickUpFilename
fileName = fileName & (Format(Now(), "yyMMdd").ToString)
'MsgBox(fileName)
OutputFileType = gsPickUpFileExtn
If OutputFileType = ".csv" Then
fileName = fileName & gsPickUpFileExtn
sw = New StreamWriter(fileName) 'connection to dest file
'Header records
sw.Write(gsRunMode)
sw.Write(Environment.NewLine) ' end of line
sw.Write(gsMemoText)
sw.Write(Environment.NewLine)
sw.Write(gsStatementText)
sw.Write(Environment.NewLine)
sw.Write(Environment.NewLine) 'Spacer
End If
If OutputFileType = ".xml" Then
fileName = fileName & gsPickUpFileExtn
'xWriter = New XmlTextWriter(Me.Connections.XMLConnection.ConnectionString, Nothing)
'xWriter.WriteStartDocument()
'xWriter.WriteComment("Customer file parsed using script")
'xWriter.WriteStartElement("x", "customer", "http://some.org/name")
'xWriter.WriteAttributeString("FileName", Me.Connections.XMLConnection.ConnectionString)
xWriter = New XmlTextWriter(fileName, Nothing)
xWriter.WriteStartDocument()
xWriter.WriteComment("Customer file parsed using script")
xWriter.WriteStartElement("x", "customer", "http://some.org/name")
xWriter.WriteAttributeString("FileName", fileName)
End If
End Sub
Public Overrides Sub ParsedInput_ProcessInputRow(ByVal Row As ParsedInputBuffer)
If OutputFileType = ".csv" Then
Dim delim As String = ","
sw.Write(Row.ProjectID.ToString + delim)
sw.Write(Row.TransactionRefNum.ToString + delim)
sw.Write(Row.CustomerNum.ToString + delim)
sw.Write(Row.AccountNum.ToString + delim)
sw.Write(Environment.NewLine) ' end of line
sw.Flush() 'send the stream to file
End If
If OutputFileType = ".xml" Then
xWriter.WriteStartElement("CUSTOMER")
xWriter.WriteStartElement("ProjectID")
xWriter.WriteString(Row.ProjectID.ToString)
xWriter.WriteEndElement()
xWriter.WriteStartElement("TransactionRefNum")
xWriter.WriteString(Row.TransactionRefNum.ToString)
xWriter.WriteEndElement()
xWriter.WriteStartElement("CustomerNum")
xWriter.WriteString(Row.CustomerNum.ToString)
xWriter.WriteEndElement()
xWriter.WriteStartElement("AccountNum")
xWriter.WriteString(Row.AccountNum.ToString)
xWriter.WriteEndElement()
End If
End Sub
Public Overrides Sub PostExecute()
If OutputFileType = ".csv" Then
'Create the trailer
sw.Write(Environment.NewLine) ' blank line
sw.Write("RECORD_COUNT: " & Me.Variables.giSuccessCount.ToString) 'ReadWrite Varible
sw.Write(Environment.NewLine)
sw.Flush() 'send the stream to file
'Close file
sw.Close()
End If
If OutputFileType = ".xml" Then
xWriter.WriteStartElement("RecordCount")
xWriter.WriteString(Me.Variables.giSuccessCount.ToString)
xWriter.WriteEndElement()
xWriter.WriteEndElement()
xWriter.WriteEndDocument()
xWriter.Close()
End If
End Sub
End Class
Consume DataReaderDest From Asp.net Page?
Jun 29, 2007I have seen the other posts about how to use Microsoft.SqlServer.Dts.DtsClient to run a package and get back the DataReader results. But this fails when run from a client mahcine that does not have SSIS installed. I want to have this page on a web server run the package on a remote Sql Server machine and get back the results but have so far failed. Any one got this working?
protected void Page_Load(object sender, EventArgs e)
{
string path = @"C:Documents and SettingsBrandonMy DocumentsVisual Studio 2005ProjectsIntegration Services Project5Integration Services Project5FuzzyLookup.dtsx";
DtsConnection connection = new DtsConnection();
connection.ConnectionString = string.Format(@"-f ""{0}""", path);
connection.Open();
DtsCommand command = new DtsCommand(connection);
command.CommandText = "DataReaderDest";
IDataReader reader = command.ExecuteReader(CommandBehavior.Default);
DataSet set = new DataSet();
set.Load(reader, LoadOption.OverwriteChanges, reader.GetSchemaTable().TableName);
_grid.DataSource = set;
_grid.DataBind();
connection.Close();
}
Consume HTTP EndPoint
Mar 19, 2006Hello, I have problems consuming webservice, I was following this page.
http://codebetter.com/blogs/raymond.lewallen/archive/2005/06/23/65089.aspx
but in the intelisense the method returns an array of objects[], So I have a problem with this line.
localhost.GetEmployees sd = new localhost.GetEmployees();
sd.Credentials = System.Net.CredentialCache.DefaultCredentials;
DataSet ds = (DataSet)(sd.EmployeeList());------> IT CANT CONVERT.
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
Error 1 Cannot convert type 'object[]' to 'System.Data.DataSet' c:inetpubwwwrootatlas1Default.aspx.cs 18 22 http://localhost/atlas1/
CLR Stored Procedure Consume Webservice
Nov 8, 2006Can you have a CRL stored procedure call a webservice that returns a dataset?
View 7 Replies View RelatedError Trying To Consume ReportingService2006 Web Service
Nov 15, 2007Hi,
I am trying to consume the ReportingService2006.asmx web service in order to disable client side printing but i am getting an error that says:
The custom tool 'MSDiscoCodeGenerator' failed. Unable to import binding 'ReportingService2006Soap' from namespace 'http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices'
Anyone experience this problem or know of any workarounds?
Thanks in advance.
Cameron
Integration Services :: Consume JSON In SSIS
Jun 23, 2015I have a below JSON script. I need to run the below script, fetch the json files and parse them in SSIS.
var client = new Keen({
projectId: "<PROJECT_ID>",
readKey: "<READ_KEY>"
[code]....
I tried using script component, but unable to find how to capture these JSON.
Error: The ProcessInput Method On Component OLE DB Destination (513) Failed With Error Code 0xC0202009
Mar 22, 2007I'm trying to create a simple Data transfermation. I have a flat file that came of a unix server.. it's 177 bytes wide.. thought it was 175, but when I created the flat file connector, I could see some extra characters on the end.
My output is going to be an excel spreadsheet, I only want two columns from the input. I created an oledb jet 4.0 connection. and followed instructions from here :
http://aspalliance.com/889_Extracting_Data_from_a_Flat_File_with_SQL_Server_2005_Integration_Services
to create my datafow.
On my first attempt to dataflow, I ran into unicode errors and had to do this:
ran into a problem with unicode errors. went to the source for the flat file. for the output column in question changed to Unicode string [DT_WSTR].
When I run , here are the errors I get:
[OLE DB Destination [513]] Error: An OLE DB error has occurred. Error code: 0x80040E09. [DTS.Pipeline]
Error: The ProcessInput method on component "OLE DB Destination" (513) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0202009.
[GanchoFileSource [1]] Information: The total number of data rows processed for file "\ammia01dev04D$JCPcpmgancho_venta_20070321.sal" is 19036.
[GanchoFileSource [1]] Error: Setting the end of rowset for the buffer failed with error code 0xC0047020.
[DTS.Pipeline] Error: The PrimeOutput method on component "GanchoFileSource" (1) returned error code 0xC0209017. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
SQL Enterprise Manager Version 8.0 Up-DO YOU WANT TO PROLONG YOUR WORK WITH THE RESULTS PANE AND CONTINUE TO CONSUME RESOURCES
Apr 23, 2007After opening the Results Pane (though a query or open table); I frequently get the below message.
Is there any way to change the settings so the results pane will stay active for a long length of time?
Message
SQL Server Enterprise Manager
_______________________
Some time ago, you retrieved data from the database with the query or view whose name is XXXX. The returned rows appear in the Results Pane and the database continues to retain the result set in its local memory; which consumes valuable server resources. Because you have not recently used the Results pane, it will be AUTOMATICALLY CLEARED IN ONE MINUTE. The will empty the results pane, discard unsaved changes, and free resources on the database server. Then if you want to REESTABLISH the result set, you can rerun the query or view.
DO YOU WANT TO PROLONG YOUR WORK WITH THE RESULTS PANE AND CONTINUE TO CONSUME RESOURCES ON THE DATABASE SERVER?
--------------------------------------------------------------------
I want this to default to Yes, without this message comming up and if I don't click yes fast enough, my results are gone. I kinow the work arounds, but how can I default this to Yes or so this message stops displaying.
Thank you
watxnd@hotmail.com
SQL Server Compact 3.5 - I've A Sdf File Created In A .NET Windows Desktop Command Line Program. How To Now Consume The Data?
Sep 17, 2007
SQL Server Compact 3.5 - I've a sdf file created in a .NET windows desktop command line program. How to now consume the data in MS Excel?
I can see that under
C:Program FilesMicrosoft SQL Server Compact Editionv3.5
I've one DLL called
sqlceoledb35.dll
But I don't have any oledb driver listed when creating an UDL file?
How can I consume the data in other apps that are not .NET and developed in house them?
Thanks, AM.
OLE DB DESTINATION And SQL Server Destination
Jul 4, 2007Hey All:
I was totally confused.
When designing the SSIS dataflow part, firstly , i tried SQL Server Destination because my target server is a sql server.
then execute the task with failure.
Then i tried to use OLE DB DESTINATION instead of SQL Server Destination.
This Dataflow worked.
i can not figour out why.
By the way , i used the connection is OLE DB.And i choosed OLE DB source as the datasource cuz i can not find SQL server datasource.
Who can tell me some reasons for this?
Help With Datareader
Jun 8, 2007Hey guys, whats an easy way to pass a value into a stored procodure?
I tried the code below but I keep on getting a "Procedure 'sp_InsertData' expects parameter '@gpiBatchNo', which was not supplied." error. My stored proc basically gets inserts the passed variable into a databaseSqlConnection sqlSecConnection = new SqlConnection(sqlPriConnString);SqlCommand sqlSecCommand = new SqlCommand();
sqlSecCommand.Connection = sqlSecConnection;
sqlSecCommand.CommandText = "sp_InsertData";sqlSecCommand.CommandType = CommandType.StoredProcedure;sqlSecCommand.Parameters.Add("@gpiBatchNo", SqlDbType.NVarChar) ;
sqlSecConnection.Open();int returntype = sqlSecCommand.ExecuteNonQuery();
sqlSecConnection.Close();
How Can I Use Datareader
Oct 20, 2007This code is currently loading my DataGridView
How can i change this to use the Datareader
Dim myConnection As SqlConnection = New SqlConnection("Data Source=ANTEC30SQLEXPRESS;Initial Catalog=test;Integrated Security=True;Pooling=False")
Dim myCmd As SqlCommand = myConnection.CreateCommand()
myConnection.Open()
myCmd.CommandType = Data.CommandType.Text
myCmd.CommandText = "Select * From tblParts"
Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(myCmd)
Dim myDataSet As DataSet = New DataSet()
myDataAdapter.Fill(myDataSet)
DataGridView1.DataSource = myDataSet.Tables(0)
Help With Converting Code: VB Code In SQL Server 2000-&&>Visual Studio BI 2005
Jul 27, 2006Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
How To Show Description In Report Instead Of Code (Desc For Code Is In Master Table)
Mar 28, 2007Dear Friends,
I am having 2 Tables.
Table 1: AddressBook
Fields --> User Name, Address, CountryCode
Table 2: Country
Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
Thanks in advance.
Regards
Ramakrishnan
Singapore
28 March 2007
DataReader Access
Feb 22, 2007Hi,
I am facing a problem to access datareader... actually i want to get data on lables from datareader. actually i am having one table having only one column and i hav accessed all data into datareader but the problem is that i just want to get data row by row...
For example there are four labels Label1, Label2, Label3, Label4
and want to print the data from datareader on to thease labels....
plz do reply... i am in trouble
DataReader And DataAdapter
Feb 28, 2007Hi, What is the difference b/w sqldatareader and sqldataadapter? For what purpose are they used in a database connection & how do they differ from each other? Pls explain me in detail.Regards Vijay.
View 1 Replies View RelatedDataset Or Datareader?
Jun 20, 2007i need help to know what is the best practice
i have a stored proc which returns 4 different resultselts
will that be easy to use dataset or datareader?
my purpose of using dataset/datareader is to load the data in a class
thanks.
Datareader Problem
Aug 20, 2007Hi,
I cant seem to get this working right...I have a datareader which i loop through...i want to test each value to see if its null but i cant get the syntax right.
I know i use dr.item("columnname") or dr(0) to pick a certain column but i dont know the column names and want to check them all anyway. What is the syntax to do this.
Thanks for any help...this is prob very simple but just cant see it.
--------------------------------------------While dr.Read
If dr(0) Is System.DBNull.Value Then
Return "test"End If
End While
Problem With Datareader
Aug 25, 2007Hello i creae one programm, there is an two data reader and two Gridview or datagrid , and my programm have one error my programm is there using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;public partial class aries : System.Web.UI.Page{ SqlConnection con; SqlCommand cmd; SqlDataReader dr; SqlDataReader dr1; SqlCommand cmd1; protected void Page_Load(object sender, EventArgs e) { string str; str = ConfigurationSettings.AppSettings["DBconnect"]; con = new SqlConnection(str); con.Open(); cmd = new SqlCommand("select color from zodiac_color where Sno=1", con); dr = cmd.ExecuteReader(); GridView1.DataSource = dr; GridView1.DataBind(); cmd1=new SqlCommand("select number from zodiac_number where Sno=1",con); dr1 = cmd.ExecuteReader(); GridView2.DataSource = dr1; GridView2.DataBind(); }} i want to calll two value in a same database but the table is diffrent so please help me ?The error is ::--------- There is already an open DataReader associated with this Command which must be closed first. please help me ashwani kumar
View 2 Replies View RelatedDatareader Problem
Apr 9, 2008hi to all , check this once..this all data related to bus seats SeatNo1,SeatNo2 seats varchar in databaseSt1,St2 status(bit in database sqlserver2000) All my code is working when reader[i + 2].ToString() == "True" is remove from code ..so plz tell me solution gow to ckeck status with datareaderSqlCommand command = new SqlCommand("Select SeatNo1,SeatNo2,St1,St2 from tblSts where
BUSID='S0008'", sqlCon);
sqlCon.Open();
SqlDataReader
reader = command.ExecuteReader();
int x =
0;
while
(reader.Read())
{
for
(int i = 0; i <= reader.FieldCount - 1; i++)
{
x = (i + 1);
System.Web.UI.WebControls.Label myLabel = ((System.Web.UI.WebControls.Label)(Page.FindControl(("Label"
+ x))));
System.Web.UI.WebControls.CheckBox myCheckbox = ((System.Web.UI.WebControls.CheckBox)(Page.FindControl(("Checkbox"
+ x))));
if
(reader[i].ToString() != "NULL"
&& reader[i + 2].ToString() == "True"))
{
myLabel.Text =
reader[i].ToString();
myCheckbox.Checked = false;
}
else
{
myLabel.Text =
reader[i].ToString();
myCheckbox.Visible = false;
}
}
}
sqlCon.Close();
Datareader Timeout
Apr 25, 2008 Hi
In my web site I call all the content from the database with the use of querystrings. I use datareader to call the data each time a request appears from the querystring. Although I close all the connections I still get occasionally the following error:
Timeout expired the timeout period elapsed prior to obtaining a connection from the pool. This may have occured because all pooled connections were in use and max pool size was reached.
If it is not a programming error then what could it be? I use sql server 2005 and vs 2005 asp.net 2.0 .
Open Datareader
May 2, 2008"There is already an open datareader associated with this command which must be closed first."
I have received this same error before, but I'm not sure why I'm getting it here.'Create a Connection object.
MyConnection = New SqlConnection("...............................")
'Check whether a TMPTABLE_QUERY stored procedure already exists.
MyCommand = New SqlCommand("...", MyConnection)
With MyCommand
'Set the command type that you will run.
.CommandType = CommandType.Text
'Open the connection.
.Connection.Open()
'Run the SQL statement, and then get the returned rows to the DataReader.
MyDataReader = .ExecuteReader()
'Try to create the stored procedure only if it does not exist.
If Not MyDataReader.Read() Then
.CommandText = "create procedure tmptable_query as select * from #temp_table"
MyDataReader.Close()
.ExecuteNonQuery()
Else
MyDataReader.Close()
End If
.Dispose() 'Dispose of the Command object.
MyConnection.Close() 'Close the connection.
End With
As you can see, the connection is opened and closed, and the datareader is closed. Here's what comes next...'Create another Connection object.
ESOConnection = New SqlConnection("...")
If tx_lastname.Text <> "" Then
If (InStr(sqlwhere, "where")) Then
sqlwhere = sqlwhere & " AND lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'"
Else
sqlwhere = " where lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'"
End If
End If
If tx_firstname.Text <> "" Then
If (InStr(sqlwhere, "where")) Then
sqlwhere = sqlwhere & " AND fname like '" & Replace(tx_firstname.Text, "'", "''") & "%'"
Else
sqlwhere = " where fname like '" & Replace(tx_firstname.Text, "'", "''") & "%'"
End If
End If
dynamic_con = sqlwhere & " order by arr_date desc "
'create the temporary table on esosql.
CreateCommand = New SqlCommand("CREATE TABLE #TEMP_TABLE (".............", ESOConnection)
With CreateCommand
'Set the command type that you will run.
.CommandType = CommandType.Text
'Open the connection to betaserv.
ESOConnection.Open()
'Run the SQL statement.
.ExecuteNonQuery()
End With
'query our side
ESOCommand = New SqlCommand("SELECT * FROM [arrest_index]" & dynamic_con, ESOConnection)
'execute query
ESODataReader = ESOCommand.ExecuteReader()
'loop through recordset and populate temp table
While ESODataReader.Read()
MyInsert = New SqlCommand("INSERT INTO #TEMP_TABLE VALUES("......", ESOConnection)
'Set the command type that you will run.
MyInsert.CommandType = CommandType.Text
'Run the SQL statement.
MyInsert.ExecuteNonQuery()
End While
ESODataReader.Close() 'Create a DataAdapter, and then provide the name of the stored procedure.
MyDataAdapter = New SqlDataAdapter("TMPTABLE_QUERY", ESOConnection)
'Set the command type as StoredProcedure.
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
'Create a new DataSet to hold the records and fill it with the rows returned from stored procedure.
DS = New DataSet()
MyDataAdapter.Fill(DS, "arrindex")
'Assign the recordset to the gridview and bind it.
If DS.Tables(0).Rows.Count > 0 Then
GridView1.DataSource = DS
GridView1.DataBind()
End If
'Dispose of the DataAdapter
MyDataAdapter.Dispose()
'Close server connection
ESOConnection.Close() Again, a separate connection is open and closed.I've read you can only have 1 datareader available per connection. Isn't that what I have here? The error is returned on this line: MyInsert.ExecuteNonQuery()
Help is appreciated.
Datareader And Dataset
Nov 3, 2003Hi
I am using a datareader to access data via a stored procedure. The reason for using the datareader is that the stored procedure is multi level depending on the variable sent to it. However I want to do two things with the data being returned.
The first is to poulate a datagrid - which I've done.
The second is to produce an Infragistic Web Graph. However according to the background reading I have done so far, I can only populate the graph from one of the following: datatable,dataview,dataset,Array or Ilist.
I don't want to make another call to the server for the same information, so how can I get the data out of a stored procedure into a dataset or dataview?
regards
Jim
DataReader Not Reading
Jan 13, 2004Why won't this dataReader read?
Dim objCon2 As New SqlConnection()
objCon2.ConnectionString = "a standard connection string"
objCon2.Open()
Dim objCommand As SqlCommand
objCommand = New SqlCommand(strSQL, objCon2)
Dim objReader As SqlDataReader
objReader = objCommand.ExecuteReader()
Label1.Text = objReader("email")
strSQL is a select command which I've checked (using SQL Query analyzer) does return data. I know the connection string is valid (and I presume if it wasn't that it'd fail on objCon2.open, which it doesn't).
So why oh why do I get this error on the last line (and yes, there is an "email" field in the contents of the reader)
System.InvalidOperationException: Invalid attempt to read when no data is present.
Datareader And Arrays
Apr 22, 2004I have data I am retrieving using a datareader...and SQLSERVER
It could return 1 row of information or perhaps 3 rows of information
I need to know how to use an array here I would guess so I can access each element in this row or rows.
HOw might I use reader.read and get it into the array
Clarification On DataReader
Jan 19, 2006I want to create a DataList that shows products, which will be on
multiple pages. I have my stored proc to show paged results, which
contains a return value for more records.
I have found examples of coding the DataReader, defining all the
parameters etc, but what about the drag and drop SqlDataSource?? You
can select the DataSource Mode to be "DataReader". I can put select
parameters in, with input and my return value. I don't know how to then
access the return value, or output value if needed, from this? My
DataList references the SqlDataSource, but I don't know how to get the
return/output value out??? This is very frustrating, cause I can't find
any info about it anywhere. Always input parameters, but no output.
This is my current SqlDataSource...
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
DataSourceMode="DataReader" ConnectionString="<%$
ConnectionStrings:Personal %>" SelectCommand="sp_PagedItems"
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="Page" QueryStringField="page" />
<asp:Parameter Name="RecsPerPage" DefaultValue="10" />
<asp:QueryStringParameter Name="CategoryID" QueryStringField="cat"
/>
<asp:Parameter Name="RETURN_VALUE" Direction="ReturnValue" Size="1"
/>
</SelectParameters>
</asp:SqlDataSource>
If I take out the RETURN_VALUE Parameter, my results display in my data
list, but that's useless if I can't access the return value to
determine the remaining number of pages etc. Is my RETURN_VALUE
parameter wrong? How do I access that? My stored proc is shown below...
CREATE PROCEDURE sp_PagedItems
(
@Page int,
@RecsPerPage int,
@CategoryID int
)
AS
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
No varchar(100),
Name varchar(100),
SDescription varchar(500),
Size varchar(10),
ImageURL varchar(100)
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (No, Name, SDescription, Size, ImageURL)
SELECT No, Name, SDescription, Size, ImageURL FROM Products WHERE CategoryID=@CategoryID
-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ID >= @LastRec
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF