Rowcount And SQLDataReader
Aug 29, 2006
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 values
commandInd.Fill(resultDS)
'// Get the XML values of the dataset to send to SQL server and run a new query
Dim strXML As String = resultDS.GetXml()
Dim xmlFileList As SqlParameter
Dim strContainsClause As SqlParameter
'// Create and execute the search against SQL Server
connStringSQL.Open()
commandSQL.CommandType = Data.CommandType.StoredProcedure
commandSQL.Parameters.Add("@xmlFileList", Data.SqlDbType.VarChar, 1000).Value = strXML
commandSQL.Parameters.Add("@strContainsClause", Data.SqlDbType.VarChar, 1000).Value = strContainsConstruct
Dim sqlReaderSource As SqlDataReader = commandSQL.ExecuteReader()
results.DataSource = sqlReaderSource
results.DataBind()
connStringSQL.Close()
And the stored procedure is such:
DROP PROC dbo.GetAssetList2;
GO
CREATE PROC dbo.GetAssetList2
(
@xmlFileList varchar(1000),
@strContainsClause varchar(1000)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @intDocHandle int
EXEC sp_xml_preparedocument @intDocHandle OUTPUT, @xmlFileList
SELECT DISTINCT
AssetsMaster.AssetMasterUID,
SupportedFiles.AssetPath,
FROM
AssetsMaster,
OPENXML (@intDocHandle, '/NewDataSet/Table',2) WITH (FILENAME varchar(256)) AS x,
SupportedFiles
WHERE
AssetsMaster.AssetFileName = x.FILENAME
AND AssetsMaster.Extension = SupportedFiles.Extension
UNION
SELECT DISTINCT
AssetsMaster.AssetMasterUID,
SupportedFiles.AssetPath,
FROM
AssetsMaster,
OPENXML (@intDocHandle, '/NewDataSet/Table',2) WITH (FILENAME varchar(256)) AS x,
SupportedFiles
WHERE
AssetsMaster.AssetFileName <> x.FILENAME
AND CONTAINS ((Description, Keywords), @strContainsClause)
AND AssetsMaster.Extension = SupportedFiles.Extension
ORDER BY AssetsMaster.Downloads DESC
EXEC sp_xml_removedocument @intDocHandle
END
GO
How can I access the number of rows returned by this stored procedure?
Thanks,
James
View 3 Replies
ADVERTISEMENT
Jul 7, 2006
I have a vb.net application that executes a simple flat file to sql table dtsx package. I want to capture the rowcount to display back to the user to verify the number of rows that were inserted or updated to the table. I have a Row Count component placed between the flat file source(without errors) and the destination component. I have assigned a variable named RecordCount to the Row Count component. So far so good I hope : )
Now, I also use a variable to "feed" the package the flat file source. This works fine, but I cannot figure out how to retrieve the row count information and how to assign that to the variable RecordCount.
Also, if anyone has any insight on the way to work with the OnProgress method in SSIS I would appreciate that as well. In SQL 2000 using DTS I create a "PackageEventsSink" that I had found online, and it worked great for monitoring the progress of the DTS. Can't seem to figure out how to get it to work in SSIS.
Thanx,
Mike
View 11 Replies
View Related
Jul 25, 2007
In sql I perform the following
SELECT * FROM
xlsdci x LEFT OUTER JOIN fffenics f ON f.[derived deal code] = x.[manual dcd id]
which gives me a row count of 2709 rows
In SSIS I have a merge join component (left outer)
left input = xlsdci with a sort order of 1 ASC on [manual dcd id] (OLE DB source component)
right input = fffenics with a sort order of 1 ASC on [derived deal code] (OLE DB source component)
which when run in the IDE gives me a rowcount of only 2594 rows
Why is this so?
Also if I change the join to INNER in the merge join, the number of rows drops dramatically to only 802.
Fair enough, I hear you cry, maybe there are IDs in the 'xlsdci' table that are not in the 'fffenics' table. Ok. But the following SQL reveals that there are only 14 rows(IDs) in 'xlsdci' that are not in 'fffenics'
SELECT * FROM xlsdci
WHERE [manual dcd id] NOT IN (SELECT [derived deal code] FROM dbo.fffenics)
What is going on here?
View 5 Replies
View Related
Nov 29, 2007
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..
View 4 Replies
View Related
Jun 1, 2008
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
View 2 Replies
View Related
Jun 25, 2004
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"];
View 5 Replies
View Related
Feb 16, 2007
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,
View 4 Replies
View Related
Jun 18, 2007
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
View 2 Replies
View Related
Jul 23, 2007
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()
View 2 Replies
View Related
Dec 31, 2007
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 !!!!!?
View 1 Replies
View Related
Jan 4, 2008
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
View 4 Replies
View Related
Apr 25, 2008
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:
protected void Page_Load(object sender, EventArgs e) { this.lblDate.Text = DateTime.Today.ToString("d"); string username; username = Convert.ToString(Session["usr"]); SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["csSecurity"].ConnectionString); SqlDataReader rdr = null; SqlCommand cmd = new SqlCommand("SELECT PROG_OFF FROM Logon_Table WHERE usr = " + username, conn); try { conn.Open(); rdr = cmd.ExecuteReader(); }
Any suggestions here would be great. Any links to tutorials you know of would be helpful also.
Thanks
View 2 Replies
View Related
May 18, 2008
good afternoon everybody
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?
View 2 Replies
View Related
Jun 7, 2008
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.
thanx in advance,
Anil Kumar.
View 6 Replies
View Related
Jun 10, 2008
see ths code. please
== DAL ==
public static SqlDataReader ExecuteReader(SqlParameter param, string sp) { using (conn = new SqlConnection(connectString)) { SqlDataReader sdr = null; try { conn.Open();
comm = new SqlCommand(); comm.CommandText = sp; comm.CommandType = CommandType.StoredProcedure; comm.Connection = conn; comm.Parameters.Add(param); sdr = comm.ExecuteReader(CommandBehavior.CloseConnection); } catch(SqlException e) { WriteToLog(e.Message); } return sdr; } }
and, call above ExecuteReader() flowing this,
== BL ==
public UserEntity GetUserInformation(string UID, string sp) { UserEntity ue = new UserEntity();
SqlParameter userUID = new SqlParameter("@USR_ID", SqlDbType.VarChar, 20); userUID.Value = UID;
using (SqlDataReader sdr = DbHelper.ExecuteReader(userUID, "SP_GETUSERINFORMATION")) { if (sdr != null) { while (sdr.Read()) { ue.UserId = sdr["lecture_usr_id"].ToString(); ue.UserName = sdr["lecture_usr_realname"].ToString(); ue.Email = sdr["lecture_usr_email"].ToString(); ue.Phone = sdr["lecture_usr_phone"].ToString(); ue.Lastlogin = (DateTime)sdr["lecture_usr_lastlogin"]; ue.SignDate = (DateTime)sdr["lecture_usr_signdate"]; } } return ue; } }
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!
View 2 Replies
View Related
Jun 14, 2008
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?
View 5 Replies
View Related
Apr 17, 2005
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
View 1 Replies
View Related
Jan 29, 2006
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();
}
}
}
finally
{
if (reader != null)
{
reader.Close();
}
if (connection != null)
{
connection.Close();
}
}
}
View 1 Replies
View Related
Feb 14, 2002
Can anybody help ?
I need to get a limited set of records from sorted record set. For example, I have a table called Contacts which contains about 2000 records and I need to show records from 11 to 20 in the sorted order.
In Oracle I could use this query.
SELECT name FROM
(SELECT name, ROWNUM rnum from Contacts order by Name)
WHERE rnum between 11 and 20
Is anything equivalent available in SQL Server ? My need is to show
records page by page ( as in google/yahoo search ) in my asp page.
Thanks
Subhash
View 1 Replies
View Related
Nov 13, 2000
Does anybody know how to get at the value set by a SET ROWCOUNT statement from within a Stored Procedure. e.g. If I executed the following:-
SET ROWCOUNT 50
EXEC Proc1
How could Proc1 find out that the User had done a SET ROWCOUNT 50.
Any ideas would be appreciated
View 1 Replies
View Related
Feb 8, 2001
Can @@rowcount be used with a regular select statement?
Thanks,
Ninel
View 2 Replies
View Related
Aug 15, 2002
Can someone send me the script that counts the rows of the table within
a database?
Thanks,
Dianne
View 3 Replies
View Related
Jul 25, 2005
Is Set RowCount @RowCountMore efficient than simply using TOP?Thanks for any input.
View 5 Replies
View Related
May 20, 2006
i'm trying to get total rows found by query that uses top clause...for example:select top 10 myTable.* from myTable where myTable.number > 200let's say there are 13 rows matching that condition, and by using@@rowcount my result would be: 10.is there any way to get total row count, without affecting the TOPclause??? i believe that the mysql equivalent would beSQL_CALC_FOUND_ROWS().tnx...
View 1 Replies
View Related
Sep 7, 2006
I'm writing an INSERT, UPDATE and DELETE trigger on table A that needs to insert rows into a table B.
When a user issues a "SET ROWCOUNT" command to limit the number of rows, then does an insert, update or delete, the trigger is being limited to that number.
It is important that the trigger NOT be limited to ANY specific number of records, but it is undesireable for me to just wipe out the current "SET ROWCOUNT" setting for the user without their knowledge.
How do I get and preserve the current "SET ROWCOUNT" value from within my trigger, so I can cancel the limitation and then re-implement the setting once my work is done? I cannot find any documentation specifying where the ROWCOUNT value is set (I initially thought maybe the SYSPROCESSES table, but that doesn't seem to be it).
Example:
set rowcount 1
update TABLEA set val=val+1
<the TABLEA trigger fires, trying to insert multiple rows into TABLEB but is limited to a single row>
Ideas?
View 1 Replies
View Related
Sep 5, 2007
Hi,
I am using ssis to import .csv files into sql server tables.
How do I get the count of the records imported?
Thanks
View 3 Replies
View Related
Jun 26, 2006
Hi,
want to get the number of rows i'm retrieving from a source. This count should be written as " No: of roes retrieved" + varname
I have used OleDbSource, RowCount,Script [ To write in a file ]. Rows is the package level variable name used in rowcount. when i do this way it always writes as 0 in the file.
[code in Script]
Dim sw As New StreamWriter("D:Vijay1.txt")
s = Variables.Rows
sw.WriteLine(s.ToString)
sw.close
[/Code]
Can anyone help on this
View 12 Replies
View Related
Dec 17, 2007
I have a data flow task which contains an XML Source, The XML Source puts data into two OLEDB Destination tasks. What i need to do is check that the number of rows inserted in to the two data bases . Can anyone suggest the easiest way as i need to check before i can commit the transactions. Any help would be most appriciated
Thanks
View 1 Replies
View Related
Feb 13, 2007
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?
View 4 Replies
View Related
Feb 28, 2007
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?
Thanks
View 1 Replies
View Related
Mar 14, 2007
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"];
View 7 Replies
View Related
Jun 20, 2007
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
View 2 Replies
View Related
Aug 23, 2007
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.
View 1 Replies
View Related