SqlDataReader Access - 'using' Statement Vs 'CommandBehaviour.closeConnection'

Nov 15, 2006

Can someone explain this to me?
Whats the difference  in "using" statement of C#  Vs  "SqlDataReader rdr = cmd.ExecuteReader(CommandBehaviour.closeConnection)"????
i thought both closes all the resources both "connection" as well "underlying datareader"but i have come to believe that CommandBehaviour.closeConnection leaves the 'datareader' open and closes the connection ONLY.Here is why..----------
i have the c# code in a class where i am creating a connection,executing stored procedure and returning the datareader.I am making use of 'using' statement something like this here

public class DB
{
public SqlDataReader getReader()
{
 using (SqlConnection conn1 = new SqlConnection(param1))
            {
                SqlCommand cmd1 = new SqlCommand(sproc, conn1);
                cmd1.CommandType = CommandType.StoredProcedure;
                conn1Open();
        
                SqlDataReader rdr = cmd1.ExecuteReader();
                return rdrMIS;
            }}

-this datareader is used in the function in a main code something like this and assign it to Dropdown list(just outlining the code)

function abc(){

DB db1 = new db1()

ddl1.datasource = db1.getReader();
ddl1.DataTextField = "val1";
ddl1DataValueField = "val2";
ddl1.DataBind(); }

this code gives me the error "Invalid attempt to FieldCount when reader is closed"

- I think this is  because the 'using' statment above,is closing the connection as well as datareader,so its not available in the calling function

-however if i remove the 'using' statment and change the line
 SqlDataReader rdr = cmd.ExecuteReader(CommandBehaviour.closeConnection) instead ;
It works fine.Why?Is it because CommandBehaviour.closeConnection closes the connection but datareader is OPEN and still available to the function?If so,do i need to close the reader explicitly in my function?..i never did it and it works fine..

- i like to use 'using' statement ..it is  clean and makes sure resource disposal is properly taken care of.But i would still like to use datareader and not dataset.Also i  want to handle all the database related task such as opening,closing of connection,datareader in a class and not in my calling function.

Whats the best way to handle all this concern.

Thanks for your help.

View 1 Replies


ADVERTISEMENT

Database Schemas And This Statement Has Attempted To Access Data Whose Access Is Restricted By The Assembly.

Jul 14, 2005

Hello.

View 5 Replies View Related

First Statement From Access To SQL

May 5, 2004

I'm trying to convert this from Access to SQL:

SELECT ORDER_DETAIL.ORDER_NUMBER, Substring([ITEM_NUMBER],2,7) AS ITEMNO, First(ORDER_DETAIL.ITEM_DESC) AS FirstOfITEM_DESC
FROM ORDER_DETAIL

But it won't accept the First statement, specifically "First" is not recognized.

First(ORDER_DETAIL.ITEM_DESC) AS FirstOfITEM_DESC

Does anyone know a way around?

View 4 Replies View Related

TSQL From An Access SQL Statement

Feb 21, 2001

Good morning one and all,

I have some queries that were written in access that I need to port into SQL 7, the whole process is boring and mundane. Does any1 know of a translator (i.e. access sql to t-sql) or a reference to the differences between access SQL and t-Sql.

Any and all help appreciated,

Thanx Gurmi

View 1 Replies View Related

IIF Statement In Access To SQL Server

Aug 14, 2006

I have an IIF statment in a query in Access;
SmallY: IIf([Small]=True,"YES"," ")

How can i translate this to SQL Server?

View 7 Replies View Related

SQL Between Dates Statement In Access Database

Apr 8, 2005

I have following SQL statement which I run on an Access database. ( Program written in VB.NET )

'---------
SELECT c.*
FROM Communications AS c
WHERE CommDate Between #1/4/2005# And #5/4/2005#;
'------------

I've put dummy values in the database for dates between 1st and 8th of april... And running this query suprisingly gives back ALL rows in the table... Also the entries for the 6th, 7th and 8th of april... In fact it just gives back the WHOLE table, no matter which values I add...

If I use '1/4/2005' (not with #) then I get an error back.

Anybody an Idea how to make a BETWEEN statement work in an access database?

Thanks,
Frederik

View 2 Replies View Related

One SQL Statement To Access Databases On Different Servers

Dec 8, 2004

I know the SQL syntax to join two tables in different databases on the ame server, but does anyone know if a SQL statement will allow you to join tables from different databases on different servers?

View 8 Replies View Related

Error In Insert Into Statement Access

Jan 14, 2013

Using Access 2010 and SQL Server 2012..i added a database through SSMS and added a table named tblEmployee (dbo.tblEmployee)

The table has 3 fields
LName (nvarchar(50), null)
FName (nvarchar(50), null)
Code (nvarchar(50), null)

The access table has 3 fields
FName, text
LName, text
Code, text

I found a code snippet here to insert from the Access table to the SQL table.UtterAccess Discussion Forums > Appending Access table to SQL Server table (and vice-versa) usin...The code is generating this error

Run-time error '3134':
Syntax error in INSERT INTO statement

Code:
Option Compare Database
Sub AppToSQL()
Dim strODBCConnect As String
Dim strSQL As String
'Code from:

[code]...

View 5 Replies View Related

Convert Access IIF To Sql In Order By Statement

Jan 13, 2004

ive been having a lot of trouble converting this last line from my access database to sql server. i was hoping someone could help



order by Name, IIF(Risk.Calc like H*, 1, IIF (Risk.Calc like M*,2,3)), Risk.Chance*risk.cons DESC, risk.title


any suggestions?

View 5 Replies View Related

Convert Ms Access Sql Staement To Oracle Sql Statement

Aug 31, 2005

Hi,I currently have a ms access update query that runsperfectly well and quicly in access however I now need to add this queryto convert this qeryu to oracles equivelant sql syntax and add it to the endof an oracle sql script.Unfortunately Im not having much success although i seem to be able toconvert it to a working oracle sql. it takes hours to run the statement inoracle where as in access it runs in secondsany help is appreciated.Ms Access sql :UPDATE (PRO_STY_TPRICES INNER JOIN PRO_STYLE_COLOURS ON PRO_STY_TPRICES.STY_ID = PRO_STYLE_COLOURS.STY_ID) INNER JOIN PRO_TST_RV3X_RPT_WRK ON(PRO_STYLE_COLOURS.SEASON = PRO_TST_RV3X_RPT_WRK.SEASON) AND(PRO_STYLE_COLOURS.STY_NUM = PRO_TST_RV3X_RPT_WRK.STY_NUM) AND(PRO_STYLE_COLOURS.STY_QUAL = PRO_TST_RV3X_RPT_WRK.STY_QUAL) AND(PRO_STYLE_COLOURS.BF_MAT_CHAR_VAL = PRO_TST_RV3X_RPT_WRK.BF_MAT_CHAR_VAL)SET PRO_TST_RV3X_RPT_WRK.MKD_DATE = pro_sty_tprices.new_active_date,PRO_TST_RV3X_RPT_WRK.MKD_PRICE = pro_sty_tprices.new_tpriceWHERE (((PRO_STY_TPRICES.NEW_ACTIVE_DATE) Is Not Null));Oracle SQL :update pro.tst_rv3x_rpt_wrk xset(x.mkd_date, x.mkd_price) =(Select a.new_active_date, a.new_tpricefrom pro.sty_tprices a, pro.style_colours bwhere a.sty_id=b.sty_idand b.bf_mat_char_val = x.bf_mat_char_valand b.season = x.seasonand b.sty_num = x.sty_numand b.sty_qual = x.sty_qualand a.new_active_date is not null)

View 1 Replies View Related

UNION Statement That Works In SQL Server But Not Access

Nov 29, 2006



Hi,

Is there a way I can get this select Union statement to work in Access.

SELECT '' AS Router UNION SELECT DISTINCT Router FROM IPVPNRouterUpgradeCharges WHERE SchemeID = 12 AND

Router <> 'IPVPN Lite' AND Router <> 'VPN Bridge'

AND Router <> 'IPVPN Aggregated Bandwidth' ORDER By Router

I get this message in Access: Query input must contain at least input of query

Thanks for any help

Chris

View 1 Replies View Related

MS Access Vs. Transact-SQL – Conditional SQL Select Statement Problem

Dec 13, 2004

Hi All,

I am trying to write a Transact-SQL statement and am having no success. I have a customers table that has some duplicate Customer Numbers in it, however the records do have a unique Reference Number. I am trying select all records that match a list of Customer Numbers but if there are more than 1 matching Customer Number I only want the one with the largest Reference Number.

My BASIC Select Statement is:

SELECT Cust_Ref_No, Customer_No, Customer_Name, Address1,
Address2, Suburb, State, Postcode, Phone
FROM Customers
WHERE Customer_No IN
(SELECT Customer_No FROM temp_Customers)

Much to my HORROR I found that what I am trying to do is simple in MS Access using the “First” Function – Such as below:

SELECT First(Cust_Ref_No) AS Cust_Ref_No, Customer_No
FROM Customers
GROUP BY Customer_No
ORDER BY First(Cust_Ref_No), Customer_No;

There appears to be no “First” Function in Transact-SQL. Is someone able to show me how to achieve the same results in Transact-SQL as I can get with MS Access (I’d HATE to think that MS Access has more functionality in querying data than SQL Server in any way at all)?

P.S. I really need to run the select statement as one step rather than splitting it up into parts.

Regards, Trog28

View 3 Replies View Related

HOW? Access Results Of Computed SQL Statement In A Data Flow

Apr 24, 2007

The SQL computed is complex enough that I can't see a way to make it a parameterized query. The obvious approach seems to be to compute the SQL in a CONTROL FLOW SCRIPT TASK and then use it to load a variable to set the VARIABLE SOURCE of a CONTROL FLOW EXECUTE SQL TASK.



I see that I can return a resultset to a variable.



But getting the rows of the results into a dataflow is not obvious. I have heard mentione that a Derived Column can do this. I can see using a dummy SCRIPT COMPONENT as DATA SOURCE with nothing in it to then drop into DERIVED COLUMN. But when setting up DERIVED COLUMN I don't see how to pull the columns out of the RESULTSET variable.



If it makes a difference I think the columns of the resultset will always be the same in this scenario.



Maybe this is totally the wrong approach? Any clues would be appreciated.

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

Data Access :: Delete Statement Blocking Insert Statements

Jul 17, 2015

We are using MS SQL Server 2008. I am running a batch job which deletes 21 days older records(6-7 million records). But daily we have transaction is going on in the database. When the delete occurs, all the insert statements got blocked and waits till the delete statement to complete. May I know why the blocking occurs? 

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

Is There A TSQL Statement For Importing A Singular Table Into Sql Server 2005 From Access?

Jan 18, 2008

Hello all.

I was wondering if there was a simple Import statement I could use in SQL to import an Access Table into SQL Server 2005.

I know how to use the SSIS Import/Export Wizard, but that seems excessive to import a single 204 record table

Any help on this would be greatly appreciated.

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







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