How Can I Return A Collection Instead Of A SqlDataReader?
Jan 12, 2008
I have to create a CLR Proc for Reporting Services report that will use SharePoint (MOSS 2007) assemblies, so my datasource won't be a database, but the SharePoint object model that returns a collection.
How can I return these data (it's not necessary to be a collection exactly) without having to create a temp table just to return the SqlDataReader?
Thanks.
And sorry for my previous message, I just forgot that I was posting a message at the Global MSDN forums.
This is my function, it returns SQLDataReader to DATALIST control. How to return page number with the SQLDataReader set ? sql server 2005, asp.net 2.0
Function get_all_events() As SqlDataReader Dim myConnection As New SqlConnection(ConfigurationManager.AppSettings("...........")) Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection) myCommand.CommandType = CommandType.StoredProcedure
Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2) parameterState.Value = Request.Params("State") myCommand.Parameters.Add(parameterState)
Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4) parameterPagesize.Value = 20 myCommand.Parameters.Add(parameterPagesize)
Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4) parameterPagenum.Value = pn1.SelectedPage myCommand.Parameters.Add(parameterPagenum)
Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4) parameterPageCount.Direction = ParameterDirection.ReturnValue myCommand.Parameters.Add(parameterPageCount)
myConnection.Open() 'myCommand.ExecuteReader(CommandBehavior.CloseConnection) 'pages = CType(myCommand.Parameters("@pagecount").Value, Integer) Return myCommand.ExecuteReader(CommandBehavior.CloseConnection) End Function
Variable Pages is global integer.
This is what i am calling DataList1.DataSource = get_all_events() DataList1.DataBind()
How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck
Is there a way to return the fields collection of a dataset using the Reporting services Web Service? I can get to the reports but not the datasets. Thank you in advance for any support you can provide.
I have SCCM 2012 R2. I'm working on a custom report, using MS SQL Server Report Builder. I have other custom reports that work fine....they prompt for a collection and return results just on that one collection. The problem with the one I'm working on now is that it runs fine except for that....it prompts for a collection, lets you choose one collection,returns the desired info on each line, but it returns all systems, not just the one collection you selected. IOW, I'm having trouble getting the "where" clause to work right.
Here is the code for a report that works (it returns the selected collection and counts the patches needed by each machine):
select    CS.Name0,      CS.UserName0,      CS.ResourceID, case when (sum(case when UCS.status=2 then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 then 1 else 0 end)as int)))
[code]....
Here is the code for the report that I'm having trouble with. What you see runs but it has none of the collection selection code I've tried since I haven't gotten it to work:
SELECT Â DISTINCT(CPU.SystemName0) AS [System Name], Â CPU.Manufacturer0 AS Manufacturer, Â CPU.Name0 AS Name, Â COUNT(CPU.ResourceID) AS [Number of CPUs], Â CPU.NumberOfCores0 AS [Number of Cores per CPU], Â [Code] ....
get the collection selection code to work in the second (processor count) sample?
this is sanjeev, i have SSIS package, using my c# program i want to add one execute package task to this package's sequence container.
it is creating the new package with out any probelm. but when i opened the package and try to move the newly created exeute package task it is giving the following error.
the element cannot be found in a collection. this error happens when you try to retrieve an element from a collection on a container during the execution of the package
Hi.. Every time I want to read any record from data base I read it in dataset for example:SqlConnection con = new SqlConnection(@"Data Source=localhost ;Initial Catalog=university ;Integrated Security=True"); SqlCommand cmd = new SqlCommand("select [User_AuthorityID] from users where [UserID]='" + TextBox1.Text + "' and [UserPassword]='" + TextBox2.Text + "' ", con);SqlDataAdapter adp = new SqlDataAdapter(); adp.SelectCommand = cmd;DataSet ds = new DataSet(); adp.Fill(ds, "UserID");foreach (DataRow dr in ds.Tables["UserID"].Rows) { user_type = dr[0].ToString(); Session.Add("User_AuthorityID", user_type); ......... Is there easier way to read data from data base? How I can use SqlDataReader to do that? Thanks..
Hey All, I have come across a situation with two tables, they are dynamic and the user can add and edit values in the tables so I need to build a dynamic display control. It is all based around an FAQ system I have built. The user can create new FAQ categories (that is one table) then create a new FAQ Question & Answer (that is the second table). The tables are linked by the category id. So now I am trying to display the FAQ section like so. CATEGORY NAME QuestionAnswerQuestionAnswerCATEGORY NAME QuestionAnswerQuestionAnswerCATEGORY NAME QuestionAnswerQuestionAnswer So my idea was to run a loop within a loop. First loop the category name, then within the category name, loop a second time to grab all of the questions & answers within the category id captured from the first loops sql select statement, then proceed to loop the category name again and of course repeat the process until all loops are completed. However I am getting, and I kinda figured I would get an error about my SQLDataReader. Bellow is my code maybe some type of edit or different recommendation is needed. Any help will do, thanks!Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load '--- Database Connection ---Dim sConnStr As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString Dim cnBKPost As New SqlConnection(sConnStr) '--- End DB Connection ---- '----- FAQ's ------- Dim sql As String = "SELECT category_id, category_name FROM faq_category ORDER BY category_name DESC"Dim cmd As New SqlCommand(sql, cnBKPost) cnBKPost.Open()Dim reader As SqlDataReader = cmd.ExecuteReader() Dim str As New StringBuilder() Dim catid As IntegerDo While reader.Read() '--- Category Titles ----catid = reader("category_id") str.Append("<h2>" & reader("category_name") & "</h2>") '--- End Category Title --- '--- Get FAQ's In Category --- Dim sqlcat As String = "SELECT faq_question, faq_answer FROM tbl_faq WHERE faq_category = '" & catid & "'"Dim cmdcat As New SqlCommand(sqlcat, cnBKPost) Dim readerfaq As SqlDataReader = cmdcat.ExecuteReader()Do While readerfaq.Read() str.Append("<p><font style='font-size:12pt;font-color:#daa520;>'" & reader("faq_question") & "</font><br />")str.Append(reader("faq_answer") & "</p>") str.Append("<br /><br /><br />") Loop readerfaq.Close() '--- End Get FAQ's in Category --- Loop reader.Close() cnBKPost.Close()Me.Literal1.Text = str.ToString() End Sub End Class
i'm using c# and SqlDataReader to simply retrieve data from one column of a database. problem is it's an integer i'm trying to retrieve, and so i'm trying to put it into an int variable, and i get the error "CS0029: Cannot implicitly convert type 'object' to 'int'" . i've looked for an answer for about an hour and every example for the SqlDataReader that i can find deals with strings only or the examples are too complex for me to understand.
there must be an easy way to retrieve this data and put it into an integer! help...
my line of code that creates the error:
int intGuestNum = dtrSelectTotalSessions["online_numSessions"];
I recently migrated a database from server A to server B. The backup jobs I am trying to build on server B are failing because of the following error: -------------- [Microsoft SQL-DMO] Error 21776: [SQL-DMO]The name 'WinDat' was not found in the Databases collection. If the name is a qualified name, use [] to separate various parts of the name, and try again. -------------
How do I add this database to the databases collection so it will be recognized?
I can add two reportitem controls, ie reportitems!begbal.value + reportitems!deposits.value, without a problem. However, when I add the 3rd reportitem control to the expression, ie + reportitems!withdrawals.value, some really funky arithmetic occurs. All of these controls I am referring to are in the same group footer.
Hi, from what I can find, there isn't a way to get the number of rows returned from a SQLDataReader command. Is this correct? If so, is there a way around this? My SQLDataReader command is as follows:Dim commandInd As New System.Data.OleDb.OleDbDataAdapter(strQueryCombined, connInd)Dim commandSQL As New SqlCommand("GetAssetList2", connStringSQL)Dim resultDS As New Data.DataSet()'// Fill the dataset with valuescommandInd.Fill(resultDS)'// Get the XML values of the dataset to send to SQL server and run a new queryDim strXML As String = resultDS.GetXml()Dim xmlFileList As SqlParameterDim strContainsClause As SqlParameter'// Create and execute the search against SQL ServerconnStringSQL.Open()commandSQL.CommandType = Data.CommandType.StoredProcedurecommandSQL.Parameters.Add("@xmlFileList", Data.SqlDbType.VarChar, 1000).Value = strXMLcommandSQL.Parameters.Add("@strContainsClause", Data.SqlDbType.VarChar, 1000).Value = strContainsConstructDim sqlReaderSource As SqlDataReader = commandSQL.ExecuteReader()results.DataSource = sqlReaderSourceresults.DataBind()connStringSQL.Close()And the stored procedure is such:DROP PROC dbo.GetAssetList2;GOCREATE PROC dbo.GetAssetList2(@xmlFileList varchar(1000),@strContainsClause varchar(1000))ASBEGINSET NOCOUNT ONDECLARE @intDocHandle intEXEC sp_xml_preparedocument @intDocHandle OUTPUT, @xmlFileListSELECT DISTINCTAssetsMaster.AssetMasterUID,SupportedFiles.AssetPath,FROM AssetsMaster, OPENXML (@intDocHandle, '/NewDataSet/Table',2) WITH (FILENAME varchar(256)) AS x,SupportedFilesWHEREAssetsMaster.AssetFileName = x.FILENAMEAND AssetsMaster.Extension = SupportedFiles.Extension UNIONSELECT DISTINCTAssetsMaster.AssetMasterUID,SupportedFiles.AssetPath,FROM AssetsMaster, OPENXML (@intDocHandle, '/NewDataSet/Table',2) WITH (FILENAME varchar(256)) AS x,SupportedFilesWHEREAssetsMaster.AssetFileName <> x.FILENAMEAND CONTAINS ((Description, Keywords), @strContainsClause)AND AssetsMaster.Extension = SupportedFiles.ExtensionORDER BY AssetsMaster.Downloads DESCEXEC sp_xml_removedocument @intDocHandle ENDGOHow can I access the number of rows returned by this stored procedure?Thanks,James
How do I tell when there is no more data to read in a SQLDataReader? For example, I have an open datareader that I pass into a function that MIGHT still have a valid row in it when it returns from the function. How do I tell? I can't do a read() because then that current record will go away. I need to be able to tell if there is a current record without doing another read. TIA,
Say I have this SQL query running into an SqlDataReader select TaskName, TaskDescription from tblTasks where TaskID = 5 There are two different ways to get the data out of the reader (maybe more) TaskName.Text = Reader.GetString(0);andTaskName.Text = Reader.GetString(Reader.GetOrdinal("TaskName")); My question is, is there a major difference in terms of efficiency between these two? The second one is definitely more robuts (in a situation where you are calling a stored procedure, and the stored procedure might change, etc) but the first one has fewer operations. Is the increase in robustness of the second one worth the potential performance hit, if any? Thank you, -Madrak
May I know what is the purpose of having SqlDataReader in Example A? I can see the same output when I tried out both. Should I use Example A or Example B? Currently, I'm using Example B since it is lesser code.Example A Dim objDR As SqlDataReader 'Create Data Reader
LoginConn.Open() strSQL = "SELECT CountryID, CountryName FROM Country ORDER BY CountryName " cmd = New SqlCommand(strSQL, LoginConn) objDR = cmd.ExecuteReader() 'Populate the DataReader ddlNationality.DataSource = objDR ddlNationality.DataBind() ddlNationality.SelectedValue = dvUserProfile.Item(0)("Nationality")LoginConn.Close() Example BLoginConn.Open() strSQL = "SELECT CountryID, CountryName FROM Country ORDER BY CountryName " cmd = New SqlCommand(strSQL, LoginConn) ddlNationality.DataSource = cmd.ExecuteReader() 'Populate the DataReader ddlNationality.DataBind() ddlNationality.SelectedValue = dvUserProfile.Item(0)("Nationality")LoginConn.Close()
Hello every one and happy new year... i have a problem with SqlDataReader used in asp.net application: I defined a public object of SqlDataReader and assigned it the resultset of a query, well, this happened ,lets say in page 1# but whan i want to use this datareader in another page it keeps telling my that the reader is closed so i can't abstract information from, regarding that i used it's methods such "Read()" and "NextResult()" but there is no use, what should i do, help me please !!!!!?
Hello im fairly new to ASP.Net and have a problem with an Intranet Page I am creating. In this part of the page I want to retrive a value from an SQL Table based on criteria and then store it as a variable to be used elsewhere on my page. I have tried using the SQL Data reader to retrive the value but somewhere in my code I am going wrong. Can anyone advise me on this please? See code below My Visual Studio debugger has point out that there is a problem with my r = cmd.ExecuteReader() line Oh and the connection to my SQL database is opened further up the page from this code. Dim strSQL As String = "SELECT top 1 OrderID FROM tblStationeryOrder WHERE ORDERMADEBY = '" & lstUsers.SelectedValue & "' ORDER BY OrderID DESC"Dim cmd As New System.Data.SqlClient.SqlCommand cmd.CommandText = strSQLDim r As System.Data.SqlClient.SqlDataReader r = cmd.ExecuteReader()Do While r.Read() Dim OrderID As Integer = r!OrderID Exit Do
I found this tutorial at C# Station called "Reading Data with the SqlDataReader". In my code behind file I followed what I think the tutorial was telling me to do but I keep getting a syntax error near '='
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '='. (Line 45: rdr = cmd.ExecuteReader();) Heres my code:
this is my code: and their is an error ,,really dunno where and why coz it seems logical to me :)Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click If RadioButton1.Checked = True Then
Dim admin As Stringadmin = "SELECT * from ADMINISTRATOR where UserName='" & TextBox1.Text & "' and UserPassword='" & TextBox2.Text & "'"
Dim sConnect As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True"Dim cnt As New SqlConnection cnt.ConnectionString = sConnect cnt.Open()Dim com As New SqlCommand com.Connection = cnt com.CommandText = adminerror!!>>>>Dim rd As SqlDataReader rd = com.ExecuteReader If rd.Read ThenSession("admn") = rd("UserName")Session("id") = rd("UserID") rd.Close() cnt.Close()Response.Redirect("~/adminpage.aspx") ElseSession("admn") = 0 Label3.Visible = True rd.Close() cnt.Close() End If Else Dim instrct As Stringinstrct = "SELECT * from instructor where name='" & TextBox1.Text & "' and passs='" & TextBox2.Text & "'"
Dim sConnect As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True"Dim con As New SqlConnection con.ConnectionString = sConnect con.Open()Dim com As New SqlCommand com.Connection = con com.CommandText = instrctDim r As SqlDataReader r = com.ExecuteReader If r.Read ThenSession("inst") = r("inst_name") r.Close() con.Close()Response.Redirect("~/instructorpage.aspx?id=" & Session("inst")) ElseSession("inst") = 0 Label3.Visible = True r.Close() con.Close() End If End If End Sub ----------------- this is the error line: The data types text and varchar are incompatible in the equal to operator. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: The data types text and varchar are incompatible in the equal to operator.... so,,,any suggestions?
I have an sp, which has 2 select statements, so iam using a sqldatareader and binding the data to a dropdown. the first binding is fine, but when i say dataReader.NextResult(), It is null.It says the reader is closed. Can any one tell a work around for this.
error message is, Invalid attempt to call Read when reader is closed. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: Invalid attempt to call Read when reader is closed.Source Error:
Line 77: if (sdr != null) Line 78: { Line 79: while (sdr.Read()) Line 80: { Line 81: ue.UserId = sdr["lecture_usr_id"].ToString();Source File: C:Users aphyrDocumentsVisual Studio 2008ProjectslectureBusinessLogicUserManager.cs Line: 77 I can't understand!!! I try to debuging!!! but, I don't know. please help me!
suppose I have a data reader which is returned by excuting a command "SELECT [xxx], [yyy], FROM [zzz]" , then I reads the data as normal. while I am reading , there is another thread that excute an insert command to that table , does this insertion effect the order of data that I am reading?
I have an SP that returns a result set that contains a tinyint. My problem is that, when I try and access this value using GetInt16 (or 32), I get an error saying that "Specified cast is not valid". TinyInt is 1 byte, or 8 bits. GetInt16 'Gets the value of the specified column as a 16-bit unsigned integer'. I am assuming that this is the root cause of my problem. But, there doesn't seem to be a GetInt8 ?! Any ideas? Thanks, Martin
hi..i am kind of new to asp.net n having trouble with the SqlException error message.. n this code works as 1st page pass the id to second page and the second page took the id to execute the query..i dun know the wer the error occurs..can give a help..Thanks. private void Page_Load(object sender, System.EventArgs e) {
SqlConnection connection = null; SqlCommand command = null; string sqlConnection = null; string sql = null; string ab = null; sqlConnection = ConfigurationSettings.AppSettings["MSQLConnectionString"]; connection = new SqlConnection(sqlConnection); if (!Page.IsPostBack) { try { if (Request.QueryString["categoryID"] == null) { } else { ab= Request.QueryString["categoryID"].ToString(); //getting the id from page that pass this values
sql = "Select groupname, categoryid, description from groups where groups.categoryid=?"; // can this query execute? command = new SqlCommand(sql, connection); connection.Open(); command.Parameters.Add(new SqlParameter("categoryid", ab)); reader = command.ExecuteReader(); // error on here "SqlException" while (reader.Read()) { group.InnerText = reader["groupname"].ToString(); desc.InnerText = reader["description"].ToString();
actuallu the earlier obviously worked but now i am fetching values from 2 colums and in collection we can pass 2 strings i did that.but then how to increment the collection c.will just post the code . query fetching 2 records can add only 1 NameValueCollection c = new NameValueCollection(); cmd2.CommandText="select Pname ,HoursWorked from TimeSheet1 ts , ProjResource pr,ProjectDetails pd where ts.rid = pr.rid and ts.pcode=pr.pcode and pd.Pcode = ts.Pcode and pr.Rid = '" + Ridtxt.Text + " '"; cmd2 .Connection = con1; con1.Open(); SqlDataReader dr = cmd2.ExecuteReader();
while (dr.Read()) { c.Add(dr["pname"].ToString(), dr["hoursworked"].ToString()); }
as earliar supose c# 12 asp 11 only c# getting added even though loop is while dr.read() { } actually how to increment key as in c both strings are occupied
hi evryone I have a question! in my ASP.NET page I use a SqlDataAdapter object for working on my database first I declare it as a global vaiable(SqlDataAdapter objDataAdapter;) then I construct it in my Page_Load procedure like this (objDataAdapter=new SqlDataAdapter(strSql,objConnention)) then when I want to use it in my other procedures an error tells me that your object does not exist for solving this error I should construct my objDataAdapter in evry procedure using it. but I think such variables should not be erased from Heap automatically(with Garbage Collection) because it has a reference to Stack. I mean the global variable... any opinion ? Thanks
I'm having trouble obtaining errors raised in a stored procedure via the ADO Errors collection after the second FETCH NEXT statement from within that stored procedure.
Consider the following table created in a SQL Server database:
This is a very simple table with one column, and three rows containing the values 1, 2 and 3.
Consider this stored procedure: CREATE PROCEDURE TestStoredProc as BEGIN set rowcount 0 Set NoCount ON
declare @TestInt int declare @ErrMsg char(7) declare TestCursor cursor forward_only for select * from TestTable
open TestCursor Fetch next from TestCursor into @TestInt
While @@fetch_status<>-1 Begin select @ErrMsg = 'Error ' + convert(char, @testint) raiserror(@ErrMsg, 16, 1) raiserror(@ErrMsg, 16, 1) Fetch next from TestCursor into @TestInt end
Close TestCursor DeAllocate TestCursor return END
This stored procedure simply defines a cursor on all rows in TestTable. For each row fetched from the cursor, the error message 'Error n' is raised twice, where n is the integer that had just been fetched from the cursor.
Finally, consider this VB code using ADO to execute the above stored procedure. After the stored procedure is executed, the code loops through the errors collection, and creates a message box for each error in the collection:
Private Sub Form_Load() Dim cn As Connection Dim cm As Command Dim oErr As Error
If I have N different xml document formats and I want to store those xml data files as typed xml in one table (one column), can I do it using xml schema collection (by adding schema file to schema collection for each document type and assigning xml schema collection to this column)?
Is this possible using xml schema collection? Or did I miss something about xml schema collection usage scenario?
I need to feed head office sql server with the data from regional servers. Servers are spread through all continents Data input done locally on Head office server as well and plus need to ship data from other servers. So clarify this - Head office server is not standby one. Mirroring is out of the picture, I think.. Initially, I thought ship a log every 15 min and restore on Head office server but is this going to create an issue for the local data processing?
Precedence Constraint Editor Values: Expression Operation: Expression and Constraint Value: Failure Expression: @GotRecs == 1 Radio button: Logical AND. All contraints must evaluate to True
Stored Procedure has an IF (that contains a SELECT) statement that checks for records if no records are returned processing stops. I SET the parameter after the BEGIN that follows the IF statement.
The step that contains the above code is step #3. I placed the constraint between step #3 and step #4 (Data Flow task) just Exports to a Flat File.
When I clicked the TEST button in the Precedence Constraint Editor I got the following msg: Error at Constraint 3: The variable €œGotRecs€? was not found in the variables collection. The variable might not exist in the correct scope.
