NULLReferenceException From SqlDataReader.GetValue()

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 values
3    /// </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       try
16       {
17           // Open database connection
18           OpenConnection();
19  
20           // execute SP
21           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           } // while
40  
41       } // @ try
42       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           #endif
51  
52       } // @ catch
53       finally
54       {
55           // close the reader if nessessary
56           if (null != reader)
57               reader.Close();
58  
59           // close db connection
60           CloseConnection();
61  
62       } // @ finally
63  
64       return bResult;
65          
66   } // ExecuteStoredProcedure()


Any input would be greatly appreciated. 

View 1 Replies


ADVERTISEMENT

GetValue On A DTSProperty Object

Mar 1, 2006

In an Integration Services scrip ttask, I am trying to get the value from a DTSProperty of a Connection Manager object.

The code seems like it would be very straight-forward. Here is what I have...


Dim oCM As ConnectionManager
Dim oProp As DtsProperty
Dim sValue As String

oCM = Dts.Connections("SomeConnectionManager")
oProp = oCM.Properties("ServerName")
If oProp.Get Then
sValue = oProp.GetValue()
End If
The line inside the If block is the problem. Once you have a DTSProperty object, you should be able to get to the value of that property. GetValue is a method that either I can't figure out how to use correctly or it is broken. Looking at the MSDN documentation (msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtsproperty.getvalue.aspx), it says I have to provide an object parameter of the object I'm trying to get the value from. But the object I'm trying to get the value from is the object on which I'm executing this method. Gah! I've tried this sValue = oProp.GetValue(oProp) - but it doesn't work.
Am I doing something wrong? Is the object model broken?

View 7 Replies View Related

Getting NullReferenceException When Executing ExecuteScalar()

Nov 20, 2007

I'm trying to add user information to the database however I'm getting a NullReferenceException.

Here's the code:


SqlCommand UserAddCommand = new SqlCommand();

int AssignedUserID = 0;

UserAddCommand.CommandType = CommandType.StoredProcedure;

UserAddCommand.Connection = m_DBConnection;

UserAddCommand.CommandText = "SP_UserAdd";

UserAddCommand.Parameters.AddWithValue("@User_Name", DbType.String);

UserAddCommand.Parameters.AddWithValue("@Street_Address", DbType.String);

UserAddCommand.Parameters.AddWithValue("@City", DbType.String);

UserAddCommand.Parameters.AddWithValue("@State", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters.AddWithValue("@Zip_Code", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters.AddWithValue("@Email_Address", DbType.String);

UserAddCommand.Parameters.AddWithValue("@Phone_Number", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters.AddWithValue("@User_Login_Name", DbType.String);

UserAddCommand.Parameters.AddWithValue("@User_Password", DbType.String);

UserAddCommand.Parameters.AddWithValue("@Referrer_Name", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters.AddWithValue("@User_Type", DbType.AnsiStringFixedLength);

UserAddCommand.Parameters["@User_Name"].Value = UserInfo.Name;

UserAddCommand.Parameters["@Street_Address"].Value = UserInfo.StreetAddress;

UserAddCommand.Parameters["@City"].Value = UserInfo.City;

UserAddCommand.Parameters["@State"].Value = UserInfo.State;

UserAddCommand.Parameters["@Zip_Code"].Value = UserInfo.ZipCode;

UserAddCommand.Parameters["@Email_Address"].Value = UserInfo.EmailAddress;

UserAddCommand.Parameters["@Phone_Number"].Value = UserInfo.PhoneNumber;

UserAddCommand.Parameters["@User_Login_Name"].Value = UserInfo.UserName;

UserAddCommand.Parameters["@User_Password"].Value = UserInfo.UserPassword;

UserAddCommand.Parameters["@Referrer_Name"].Value = UserInfo.ReferrerName;

if ((int)UserInfo.User_Type == (int)ClassUserInfo.UserType.FAMILY)

{

UserAddCommand.Parameters["@User_Type"].Value = "Family";

}

else if ((int)UserInfo.User_Type == (int)ClassUserInfo.UserType.FRIEND)

{

UserAddCommand.Parameters["@User_Type"].Value = "Friend";

}

else if ((int)UserInfo.User_Type == (int)ClassUserInfo.UserType.MANAGER)

{

UserAddCommand.Parameters["@User_Type"].Value = "Manager";

}

else if ((int)UserInfo.User_Type == (int)ClassUserInfo.UserType.OWNER)

{

UserAddCommand.Parameters["@User_Type"].Value = "Owner";

}

try

{

AssignedUserID = (int)UserAddCommand.ExecuteScalar(); this line of code produces the NullReferenceException

UserInfo.UserID = AssignedUserID;

m_UserInfo = UserInfo;

}

catch (Exception Ex)

{

Console.WriteLine(Ex.ToString());

}



Please help me figure out what I am doing wrong.

View 1 Replies View Related

PrimeOutput() And NullReferenceException Problems With DataReader Source In SSIS

Jun 20, 2006

Hi

I have an integration services script that was working ok until a recent database upgrade. When I run the script in gui debug mode it behaves as follows. The data flow reads in data from an oracle server table VALNREQ & then uses it to populate a sqlserver table (with a little manipulation of the fields in between). For some reason although it populates the destination table with the correct number of rows (& the data looks ok) it errors out with the messages listed below. The DataReader Source dataflow source box turns red & all the other boxes turn green including the destination one. I have run out of ideas ... any suggestions? Have I inadvertently changed a property without noticing? I can't see anything obvious & the input data itself looks clean enough.

Any pointers in the right direction would be great, thanks. I've been thrown in at the deep end with this s/w so I imagine there are a number of large gaps in my knowledge so apologies if there is a simple solution to this.

B

[DataReader Source VALNREQ [1]] Error: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket)


[DTS.Pipeline] Error: The PrimeOutput method on component "DataReader Source VALNREQ" (1) returned error code 0x80004003. 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.

View 7 Replies View Related

.NET Runtime 2.0 Error Reporting Event Id 5000 System.nullreferenceexception

Aug 29, 2006

I only found http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=389835&SiteID=17 regarding this error message and it was of no help to me.

Background: 3rd party program that extracts users from a AD group and manage user creation i a MSSQL 2000 server. Also sends e-mails using the SMTP service. The funny thing is that the program manages everything correctly, but writes an error in the Event log as it exists (below). Job is scheduled with the SQL server agent and runs with a doamin user that has local admin rights. We have the same setup on a testserver where it runs without problems.

Windows server 2003, MDAC 2.8, SQL Server 2000 SP3, .NET framework 2 on both servers.

Event Type: Error
Event Source: .NET Runtime 2.0 Error Reporting
Event Category: None
Event ID: 5000
Date: 2006-08-29
Time: 09:16:28
User: N/A
Computer: STHPS0125
Description:
EventType clr20r3, P1 bzadbatch.exe, P2 1.0.0.0, P3 44e5c349, P4 bzadbatch, P5 1.0.0.0, P6 44e5c349, P7 25, P8 308, P9 system.nullreferenceexception, P10 NIL.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 63 00 6c 00 72 00 32 00 c.l.r.2.
0008: 30 00 72 00 33 00 2c 00 0.r.3.,.
0010: 20 00 62 00 7a 00 61 00 .b.z.a.
0018: 64 00 62 00 61 00 74 00 d.b.a.t.
0020: 63 00 68 00 2e 00 65 00 c.h...e.
0028: 78 00 65 00 2c 00 20 00 x.e.,. .
0030: 31 00 2e 00 30 00 2e 00 1...0...
0038: 30 00 2e 00 30 00 2c 00 0...0.,.
0040: 20 00 34 00 34 00 65 00 .4.4.e.
0048: 35 00 63 00 33 00 34 00 5.c.3.4.
0050: 39 00 2c 00 20 00 62 00 9.,. .b.
0058: 7a 00 61 00 64 00 62 00 z.a.d.b.
0060: 61 00 74 00 63 00 68 00 a.t.c.h.
0068: 2c 00 20 00 31 00 2e 00 ,. .1...
0070: 30 00 2e 00 30 00 2e 00 0...0...
0078: 30 00 2c 00 20 00 34 00 0.,. .4.
0080: 34 00 65 00 35 00 63 00 4.e.5.c.
0088: 33 00 34 00 39 00 2c 00 3.4.9.,.
0090: 20 00 32 00 35 00 2c 00 .2.5.,.
0098: 20 00 33 00 30 00 38 00 .3.0.8.
00a0: 2c 00 20 00 73 00 79 00 ,. .s.y.
00a8: 73 00 74 00 65 00 6d 00 s.t.e.m.
00b0: 2e 00 6e 00 75 00 6c 00 ..n.u.l.
00b8: 6c 00 72 00 65 00 66 00 l.r.e.f.
00c0: 65 00 72 00 65 00 6e 00 e.r.e.n.
00c8: 63 00 65 00 65 00 78 00 c.e.e.x.
00d0: 63 00 65 00 70 00 74 00 c.e.p.t.
00d8: 69 00 6f 00 6e 00 20 00 i.o.n. .
00e0: 4e 00 49 00 4c 00 0d 00 N.I.L...
00e8: 0a 00 ..

I'm not a developer, so I'm pretty lost why it behaves like this on one server and not the other. Checked Aspnet.config-file and they are the same. Found this

http://support.microsoft.com/?id=911816

that described that the exception handling was different between the .NET-versions, but since this works on one of the servers, it shouldn't be related, or could it? Tried changing the setting in method 2, but with the same behaviour. Any ideas?

Best regards

/ Jan

View 1 Replies View Related

How To Solve :Error: 0xC0047062 At CTPKPF, DataReader Source [1]: System.NullReferenceException: Object Reference Not Set To An

Apr 7, 2007

hi

i need help to solve following error in ssis package when i aun ::



Error: 0xC0047062 at CTPKPF, DataReader Source [1]: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket) Error: 0xC0047038 at CTPKPF, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "DataReader Source" (1) returned error code 0x80004003. 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. There may be error messages posted before this with more information about the failure. Error: 0xC0047021 at CTPKPF, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited. Information: 0x40043008 at CTPKPF, DTS.Pipeline: Post Execute phase is beginning. Information: 0x40043009 at CTPKPF, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at CTPKPF, DTS.Pipeline: "component "OLE DB Destination" (1993)" wrote 0 rows. Task failed: CTPKPF

View 11 Replies View Related

Object Reference Not Set To An Instance Of An Object As System.NullReferenceException

Jan 2, 2008

Hi,

I am using a stored procedure and places the value into a dataset.
But it prompts me an error.

And here is my code:

Dim cmd As New SqlCommand("testProc", mySqlConnection)

cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@PrdCode", SqlDbType.VarChar, 50).Value = strBonPrdCode
cmd.Parameters.Add("@keyWord", SqlDbType.VarChar, 250).Value = srchKeyword
cmd.Parameters.Add("@keyWord_Count", SqlDbType.Int).Value = Keyword_count

Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet

da.Fill(ds, "Results")

recCount = ds.Tables("Results").Rows.Count


Can you give me a solution to solve this problem. Thanking you in advance.

View 8 Replies View Related

System.NullReferenceException: Object Reference Not Set To An Instance Of An Object - Help

Dec 2, 2007

I have a script task that worked FINE yesterday. Now when I run it, I get the following error:

[myTask [64]] Error: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)

Help!

View 1 Replies View Related

How I Can Use SqlDataReader?

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

SqlDataReader Within Another SqlDataReader - Can It Be Done?

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

Using SqlDataReader

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

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

View 3 Replies View Related

No More Data In A SqlDataReader?

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

SqlDataReader.GetOrdinal()

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

What Is The Purpose Of Having SqlDataReader Here?

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

Question About SqlDataReader....

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

SQLDataReader Problem

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

SqlDataReader Problems

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

Dim Rd As SqlDataReader Error

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

SQLdataReader Getting Closed

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

I Have Problem!! About SqlDataReader

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

Question About SqlDataReader

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

SqlDataReader And Tinyint

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

Trouble With SqlDataReader

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

Sqldatareader Not Returning Records

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

SqlDataReader + Stored Procedure

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

Mapping Of Columns In SqlDataReader

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

Sqldatareader Reads From Second Row Skip The First Row.

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

How To Reorder A SqlDataReader Object

Oct 5, 2007

I have a SqlDataReader object that has some records:
1 hello12 hello23 hello3
Is there a fast/easy way to reorder them?
3 hello32 hello21 hello1
 
 

View 2 Replies View Related

Sqldatareader Within A Loop From Another Reader?

Dec 26, 2007

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);

writer.WriteStartDocument();
writer.WriteStartElement("siteMap");
writer.WriteAttributeString("xmlns", "http://schemas.microsoft.com/AspNet/siteMap-File-1.0");

// create the command
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;

// set up the command
myCommand.CommandText = "spGetMenuStructure";
myCommand.CommandType = CommandType.StoredProcedure;

// open the connection
myConnection.Open();

// run query
SqlDataReader myReader = myCommand.ExecuteReader();

bool HasSubElements = false;

SqlCommand myCommand2 = new SqlCommand();
SqlParameter myParameter1 = new SqlParameter();
SqlDataReader myReader2 = new SqlDataReader();

// parse the results
while (myReader.Read())
{
// create the command
myCommand2.Connection = myConnection;

// set up the command
myCommand2.CommandText = "spGetMenuSubElements";
myCommand2.CommandType = CommandType.StoredProcedure;

myParameter1.ParameterName = "@ContentID";
myParameter1.SqlDbType = SqlDbType.Int;
myParameter1.Value = Convert.ToString(myReader["ID"]);

myCommand2.Parameters.Add(myParameter1);

// run query
myReader2 = myCommand2.ExecuteReader();

while (myReader2.Read())
{
HasSubElements = true;
}

myReader2.Close();

if (Convert.ToString(myReader["HasPage"]) == "1")
{
writer.WriteStartElement("siteMapNode");
writer.WriteAttributeString("title", Convert.ToString(myReader["PageTitle"]));
writer.WriteAttributeString("description", Convert.ToString(myReader["PageTitle"]));

string PageURL = Convert.ToString(myReader["PageName"]) + "?ContentID=" + Convert.ToString(myReader["ID"]);

writer.WriteAttributeString("url", PageURL);

if (HasSubElements)
{
writer.WriteEndElement();
}
}
else
{
writer.WriteStartElement("siteMapNode");
writer.WriteAttributeString("title", Convert.ToString(myReader["PageTitle"]));
writer.WriteAttributeString("description", Convert.ToString(myReader["PageTitle"]));
}

HasSubElements = false;
}

myReader.Close();

// 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.    

View 4 Replies View Related

[Almost Resolved] SqlDataReader Vs. DataSets

Feb 1, 2008

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

View 6 Replies View Related

SqlDataReader: Accessing Columns With The Same Name!

May 12, 2008

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

View 6 Replies View Related

Query Problem Using Sqldatareader

Mar 14, 2004

I'm having some trouble reading data from a query. This has to do with a previous posting here:view post 504339

I have corrected that problem but I now only get the response "wrong password" even though the right password is entered. Here is the new code for the click subrouthine. You will notice two different scenarios with one commented out. Both do not work. I created label1 to display the password and it is show correctly. What am I doing wrong?

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
Dim test As String
SqlConnection1.Open()
SqlCommand1.Parameters.Item("@email").Value = txtUsername.Text
Dim dr As SqlDataReader = SqlCommand1.ExecuteReader
If dr.Read() Then
test = CStr(dr("pass"))
Label1.Text = test
'If dr("pass").ToString = txtPassword.Text Then
'lblMessage.Text = "login successful"
'Else
' lblMessage.Text = "Wrong password"
'End If
If String.Compare("test", "txtPassword.text") = 0 Then
lblMessage.Text = "login successful"
Else
lblMessage.Text = "Wrong Password"
End If
Else
lblMessage.Text = "Please register"
End If
dr.Close()
SqlConnection1.Close()
End Sub

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved