I noticed that when SqlDataReader is pending, if another page require the same connection the page remain in work tail until SqlDataReader in first page is closed.
It is a real disappointment for a multi tasking system like Windows.
How to absolutely avoid it?
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Public Class var
Public Structure varName
Public backUserColor, borderUserColor As String
End Structure
Public Shared Function getVar(ByVal pageCode As String, ByVal level1 As String) As varName
Dim connection As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename='G:Documents and SettingsAdministratorDocumentiVisual Studio 2005WebSitesWebSite1App_Datavar.mdf';Integrated Security=True;Asynchronous Processing=True;Connect Timeout=30;User Instance=True")
Dim query As String = "SELECT value FROM common UNION ALL SELECT spec FROM spec WHERE pageCode='"+pageCode+"'"
Dim command As New SqlCommand(query, connection)
connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
While reader.Read
getVar.backUserColor = reader.Item(0)
MsgBox(getVar.backUserColor, , )
End While
reader.Close()
connection.Close()
End Function
End Class
I have a DB that is currently not normalized and will be getting about 100K concurrent users that will mostly be doing Read-Only operations from multiple tables.
I am trying to figure out if I should start thinking of having a DB per client (1000 clients) or if I should normalize the database and keep it as a single DB with good indexes and partitioning.
Hardware is not a problem but 100K concurrent users is.
Disaster Recovery Options based on the following criteria.
--Currently running SQL 2012 standard edition --We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately --Recovery needs to happen within 1 hour (Not sure that this is realistic -- We are building a new data center and building dr from the ground up.
What I have looked into is:
1. Transactional Replication: Too Much Data Not viable 2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances 3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options with be a nightmare.
More often than not, I typically don't touch DTC on clusters anymore; however on a project where the vendor states that it's required. So a couple things here.
1) Do you really need DTC per instance or one for all? 2) Should DTC be in its own resource group or within the instance's group? 2a) If in it's own resource group, how do you tie an instance to an outside resource group? tmMappingSet right?
Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid
Hi there, Here we have got a asp.net application that was developed when database was sitting on SQL server 6.5. Now client has moved all of their databases to SQL server 2000. When the database was on 6.5 the previous development team has used oledb connections all over. As the databases have been moved to SQL server 2000 now i am in process of changing the database connection part. As part of the process i have a login authorization code. Private Function Authenticate(ByVal username As String, ByVal password As String, ByRef results As NorisSetupLib.AuthorizationResult) As Boolean Dim conn As IDbConnection = GetConnection() Try Dim cmd As IDbCommand = conn.CreateCommand() Dim sql As String = "EDSConfirmUpdate" '"EDSConfirmUpdate""PswdConfirmation" 'Dim cmd As SqlCommand = New SqlCommand("sql", conn)
cmd.CommandText = sql cmd.CommandType = CommandType.StoredProcedure NorisHelpers.DBHelpers.AddParam(cmd, "@logon", username) NorisHelpers.DBHelpers.AddParam(cmd, "@password", password) conn.Open() 'Get string for return values Dim ReturnValue As String = cmd.ExecuteScalar.ToString 'Split string into array Dim Values() As String = ReturnValue.Split(";~".ToCharArray) 'If the return code is CONTINUE, all is well. Otherwise, collect the 'reason why the result failed and let the user know If Values(0) = "CONTINUE" Then Return True Else results.Result = Values(0) 'Make sure there is a message being returned If Values.Length > 1 Then results.Message = Values(2) End If Return False End If Catch ex As Exception Throw ex Finally If (Not conn Is Nothing AndAlso conn.State = ConnectionState.Open) Then conn.Close() End If End Try End Function ''' ----------------------------------------------------------------------------- ''' <summary> ''' Getting the Connection from the config file ''' </summary> ''' <returns>A connection object</returns> ''' <remarks> ''' This is the same for all of the data classes. ''' Reads a specific connection string from the web.config file for the service, creates a connection object and returns it as an IDbConnection. ''' </remarks> ''' ----------------------------------------------------------------------------- Private Function GetConnection() As IDbConnection 'Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection conn.ConnectionString = NorisHelpers.DBHelpers.GetConnectionString(NorisHelpers.DBHelpers.COMMON) Return conn End Function in the above GetConnection() method i have commented out the .net dataprovider for oledb and changed it to .net dataprovider for SQLconnection. this function works fine. But in the authenticate method above at the line Dim ReturnValue As String = cmd.ExecuteScalar.ToString
for some reason its throwing the below error. Run-time exception thrown : System.Data.SqlClient.SqlException - @password is not a parameter for procedure EDSConfirmUpdate. If i comment out the Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection and uncomment the .net oledb provider, Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection then it works fine. I also have changed the webconfig file as below. <!--<add key="Common" value='User ID=**secret**;pwd=**secret**;Data Source="ESMALLDB2K";Initial Catalog=cj_common;Auto Translate=True;Persist Security Info=False;Provider="SQLOLEDB.1";' />--> <add key="Common" value='User ID=**secret**;pwd=**secret**;Data Source="ESMALLDB2K";Initial Catalog=cj_common;' />
I am new to Reporting Services and hope that what I am looking to do is within capabilities :-)
I have many identical schema databases residing on a number of data servers. These support individual clients accessing them via a web interface. What I need to be able to do is run reports across all of the databases. So the layout is:
Dataserver A
Database A1
Database A2
Database A3
Dataserver B
Database B1
Database B2
Dataserver C
Database C1
Database C2
Database C3
I would like to run a report that pulls table data from A1, A2, A3, B1, B2, C1, C2, C3
Now the actual number of servers is 7 and the number of databases is close to 1000. All servers are running SQL2005.
Is this something that Reporting Services is able to handle or do I need to look at some other solution?
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"];
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();
My query is as follows:Dim CurrentDate As DateCurrentDate = "09/02/2007" MyCommand = New SqlCommand("SELECT RegisterID FROM Registers WHERE RegisterDate = @RegisterDate AND IsMorningRegister = 1", MyConn)MyCommand.Parameters.Add("@RegisterDate", Data.SqlDbType.DateTime)MyCommand.Parameters("@RegisterDate").Value = CurrentDate My DB table is called RegisterDate and is of type DateTime. The record that should be matched is: Register ID: 13 RegisterDate: 09/02/2007 09:00:00IsMorningRegister: TrueIsAfternoonRegister: False But no records are returned. Any idea why?
I am trying to access data using this code: SqlCommand myCommand = new SqlCommand("myStoredProcedure", myConnection); myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.Add(new SqlParameter("@myID", SqlDbType.Int)); myCommand.Parameters["@myID"].Value = myIDValue; myConnection.Open(); myDataReader = myCommand.ExecuteReader(); myTextBox.Text = myDataReader["myColumn"].ToString(); myDataReader.Close(); myConnection.Close(); I get "data reader has no data" error. A problem with the parameter? The stored procedure returns a row when executed from the database explorer. I have used datasets and table adapters up to now as in the data access tutorial on this site Any Ideas?
Hi,I use SqlDataReader to read one row from database and than set some properties to values retrieved like this:string myString = myReader.GetValue(0) // this sets myString to first value in a rowIf, however, I change order of columns returned by stored procedure myString would be set to wrong value. Is there a way to do something like this: string myString = myReader.GetValue["ColumnName"];
Hello, im using sqldatareader to read my data and whenever time i loop through the reader it starts from second row why is that? here is my code:while (reader.Read()){hinfo.Name = reader["_name"].ToString();hi.Add(hinfo);} i look at the database and i have two rows but its reading only the second row, skiping the first row
I am getting a random exception coming out of ADO.NET's SqlDataReader that I can't seem to track down for the life of me. I have put SQL Server and IIS under heavy load, with neither giving me the exceptions except occasionally (they come when there is no load as well). Web Site is built in VS2005 using .NET 2.0. Basically, I have a web page that caches a few tables from a SQL database (about 10 tables are loaded into cache from this page) and the error can come from any of the tables while they are being read from the database randomly. I had assumed it was possible that a timeout was occuring while reading the data but from what the brief results google shows, there would be a different exception thrown it a timeout had occured. Here is the exception that is showing up: System.NullReferenceException: Object reference not set to an instance of an object. at System.Data.SqlClient.SqlDataReader.ReadColumnHeader(Int32 i) at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout) at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i) at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i) at Library.Data.DataProxy.ExecuteStoredProcedure(ArrayList& returnValues, TypeFactory factory) I got the following exception once from the same section of code as well: System.InvalidOperationException: Invalid attempt to FieldCount when reader is closed. at System.Data.SqlClient.SqlDataReader.get_FieldCount() at Library.Data.DataProxy.ExecuteStoredProcedure(ArrayList& returnValues, TypeFactory factory) The code generating these errors is on line 29 below: 1 /// <summary>2 /// Executes a stored procedure that returns values3 /// </summary>4 /// <param name="returnValues">An arraylist of Types</param>5 /// <param name="factory">The Type specific factory</param>6 /// <returns>True if successful, false otherwise</returns>7 public bool ExecuteStoredProcedure(out ArrayList returnValues, TypeFactory factory)8 {9 bool bResult = true;10 SqlDataReader reader = null;11 12 returnValues = new ArrayList();13 MenseType nextType = null;14 15 try16 {17 // Open database connection18 OpenConnection();19 20 // execute SP21 reader = m_sqlCommand.ExecuteReader();22 23 while(reader.Read())24 {25 nextType = factory.CreateInstance();26 27 for(int i = 0; i < reader.FieldCount; i++)28 {29 object val = reader.GetValue(i);30 31 if(DBNull.Value == val)32 val = null;33 34 nextType.SetValue(reader.GetName(i), val);35 }36 37 returnValues.Add(nextType);38 39 } // while40 41 } // @ try42 catch(Exception ex)43 {44 returnValues = null;45 m_LastError = ex.ToString();46 bResult = false;47 48 #if(DEBUG)49 Library.IO.Logging.AddSQLMessage(ex);50 #endif51 52 } // @ catch53 finally54 {55 // close the reader if nessessary56 if (null != reader)57 reader.Close();58 59 // close db connection60 CloseConnection();61 62 } // @ finally63 64 return bResult;65 66 } // ExecuteStoredProcedure() Any input would be greatly appreciated.
I have an SqlDataReader which loops through records returned from an SP, within that loop I would like to initiate another SP, but for some darn reason the following code won't work: // create SqlConnection object string ConnectionString = ConfigurationManager.ConnectionStrings["aiv3cs"].ConnectionString; SqlConnection myConnection = new SqlConnection(ConnectionString);
try { // Create a new XmlTextWriter instance XmlTextWriter writer = new XmlTextWriter(Server.MapPath("products.sitemap"), Encoding.UTF8);
// end the xml document and close writer.WriteEndElement(); writer.WriteEndDocument(); writer.Close(); }
finally { myConnection.Close(); }I've gotten the following two errors:There is already an open DataReader associated with this Command which must be closed first.And a build error:Error29The type 'System.Data.SqlClient.SqlDataReader' has no constructors defined Any suggestions would be most appreciated.
Hi, I am pretty new to harcore ASP.NET and .NET in general but I know the basics of the language and stuff like that. Basically I used to be a hardcore object pascal Delphi developer doing Windows Applications but have now moved to .NET world. The reason I posted this question here is I couldn't find any other specific place to ask so here it goes. To get me started with good programming source and practices I downloaded the ASP.NET TimeTracker Starter Kit and after some modificaion in connection string (using SQL 2005 Developer edition not the Express one) I have managed to make it work. Things done in there are pretty interesting way by using a delegate to retrieve data and present it using databound controls. As much simple as it sounds I am more of a fan of not using too many databound controls except in dropdown box, list box and stuff like that. So basically I need to develop DataAccessLayer (DAL) in such a way that it's useful for both Windows and Web application. So with my research I found that I should be using DataSets in the DAL because they are serializable and thatz what Web Applications & Services love over using SqlDataReader. Other advantage of using DataSet is to use ForEach syntex over While Loop in SqlDataReaders. So below is some code I extracted from the TimeTracker Starter Kit to get me started. I also noticed that the return result is a list array of certain object, in this case Category. So does it mean that it can be used in frontend using ForEach syntex? How can I convert the code below to use DataSets over SqlDataReader? Private Delegate Sub TGenerateListFromReader(Of T)(ByVal returnData As SqlDataReader, ByRef tempList As List(Of T))
Public Overrides Function GetAllCategories() As List(Of Category) Dim sqlCmd As SqlCommand = New SqlCommand() SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETALLCATEGORIES)
Dim categoryList As New List(Of Category)() TExecuteReaderCmd(Of Category)(sqlCmd, AddressOf TGenerateCategoryListFromReader(Of Category), categoryList)
Return categoryList End Function
Public Overrides Function GetCategoryByCategoryId(ByVal Id As Integer) As Category If Id <= DefaultValues.GetCategoryIdMinValue() Then Throw New ArgumentOutOfRangeException("Id") End If
Dim sqlCmd As SqlCommand = New SqlCommand() AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, Id) SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETCATEGORYBYID)
Dim categoryList As New List(Of Category)() TExecuteReaderCmd(Of Category)(sqlCmd, AddressOf TGenerateCategoryListFromReader(Of Category), categoryList)
If categoryList.Count > 0 Then Return categoryList(0) Else Return Nothing End If End Function
Private Sub TGenerateCategoryListFromReader(Of T)(ByVal returnData As SqlDataReader, ByRef categoryList As List(Of Category)) Do While returnData.Read()
Dim actualDuration As Decimal = 0 If Not returnData("CategoryActualDuration") Is DBNull.Value Then actualDuration = Convert.ToDecimal(returnData("CategoryActualDuration")) End If
Dim category As Category = New Category(CStr(returnData("CategoryAbbreviation")), actualDuration, _ CInt(returnData("CategoryId")), CDec(returnData("CategoryEstimateDuration")), CStr(returnData("CategoryName")), _ CInt(returnData("ProjectId"))) categoryList.Add(category) Loop End Sub Hope this makes sense. Sorry if I have posted this in a wrong section..! Cheers,Nirav
Hi there I am using an SqlDataReader to read and write to my back end database and i have got it to work using the code: myDataReader["myFieldName"].ToString(); However, when i have two fields of the same name (e.g a "Surname" belonging to students in a table, and "Surname" belonging to teachers in a different table), I can't Pick up the two different fields even though i have given aliases. I don't really want to access these fields using an integer as the index as this will make management in the future difficult. as a side note, i have to use aliases as i access the teachers table a number of different times to specify who the tutors are and who teachers of all their different subjects are. thanks pete