Using A Datareader In A SQL Helper Select Query

Jan 28, 2005

Hi,





I am trying to retrieve a value from a SQL Server table based on the results of a datareader.This is the string i am trying to create





Me.ViewState("Make") = SqlHelper.ExecuteScalar(MyConnectionString, "usp_GetMake", (dr("MakeID")).To String)





Im not sure of the syntax as I know that it is basically using two different ways of reading a database.Is there an equivalent for the datreader which can be used with SQL Helper?





Thank you for your help





Julie

View 1 Replies


ADVERTISEMENT

Get Value From SQL Server 2005 Select Statement With Datareader

Nov 6, 2006

I just want a simple datareader, that i can read the value returned from a select statement executed on a SQL server 2005 db.  The code below should work in, but email[calc]= rdr[0].ToString(); when i want to read some data a get a exception saying:System.InvalidOperationException was unhandled by user code  Message="Invalid attempt to read when no data is present."  Source="System.Data"  StackTrace:       at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)       at System.Data.SqlClient.SqlDataReader.get_Item(Int32 i)       at _Default.Login_Click(Object sender, EventArgs e) in d:My DocumentsVisual Studio 2005WebSitesWebSite1Default.aspx.cs:line 47       at System.Web.UI.WebControls.Button.OnClick(EventArgs e)       at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)       at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) If anybody could  advise me where my stupid mistake is then i would highly appreciate it!         SqlConnection conn = new SqlConnection(getConnection());        SqlDataReader rdr = null;        SqlCommand cmd = new SqlCommand();        cmd.CommandText = "SELECT * FROM Customer";        cmd.CommandType = CommandType.Text;        cmd.Connection = conn;        try        {            conn.Open();                           rdr = cmd.ExecuteReader();                        int calc = 0;            Boolean login = false;            string[] email = new string[100];            object[] password = new object[100];            while (rdr.HasRows) // or rdr.Read();             {                rdr.Read();                email[calc]= rdr[0].ToString();                password[calc] = rdr["Password"].ToString();                if (UserName.Text.Equals(email[calc]) && Password.Text.Equals(password[calc]))                {                    login = true;                }                calc++;            }        }        finally        {            rdr.Close();        }  thanks.... 

View 1 Replies View Related

Select Case Does Not Evaluate The Value Returned By Datareader Or Dataset

Sep 13, 2006

I am working on a company sign out sheet for our Intranet. The application accesses a sql database.  I tried using a datareader as well as a dataset to access a small piece of information regarding the group that each employee belongs to in the company.  My last attempt was to assign the value to the text property of a text box and evaluate the text from the text box with the Select Case but that did not work either.The application is supposed to generate an email to our receptionist as well as to the group technical assistant responsible for each group according to the value that is passed into the Select Case statement.  With the datareader as well as the dataset and even now with creating a text box and accessing the text property, I was able to assign the group value to a variable (I can see the value in the subject of the email), but when the variable is supposed to be evaluated by the select case statement it skips right through all of the cases to case else and uses only the receptionist's address in the email as if it doesn't even see the value of the variable.   I have searched for a possible answer to the problem I am but so far have had no luck.  Any ideas?   I included the part of the code that I am having trouble with: '********************************************************************************************' New DataSet is created to determine the group the employee belongs to so that the appropriate' group technical assistant is emailed when employee signs out'******************************************************************************************** ' declare variablesDim sqlGroup As String ' string variable to store sql statement for datasetDim BLGroup As String ' string variable to store the bl group of the employeeDim emailAddress As String ' string variable to store resulting email address Dim groupDS As DataSet ' dataset variableDim groupAdapter As SqlDataAdapter ' sql data adapter variable' sql statement to access group value from the databasesqlGroup = "SELECT BLGroup FROM BLGroupsView WHERE (RTRIM(fname) + ' ' + LTRIM(lname)='" & employee & "')" ' repopen the connection (leftover from working with the datareader)Connection.Open()' create new SqlDataAdapter applying sql statement and connectiongroupAdapter = New SqlDataAdapter(sqlGroup, Connection)' create new dataset groupDS = New DataSet()' populate the datasetgroupAdapter.Fill(groupDS, "BLGroupsView") ' get the value stored in the BLGroup column (only one row is returned at a time) BLGroup = groupDS!BLGroup' Create GroupTextBox TextBox control.Dim GroupTextBox As New TextBox()' Set options for the UserTextBox TextBox control.GroupTextBox.ID = "GroupTextBox"GroupTextBox.Visible = "False"GroupTextBox.Text = BLGroup' just trying anything with this next line, even when i didn't set this the select case statement did not seem to see the value returned by the variableGroupTextBox.runat = "server"  ' use a select case statement to evaluate the value of the group (didn't work when I passed in the BLGroup variable eitherSelect Case GroupTextBox.TextCase "bh"emailAddress = "receptionist@myaddress.com; bhassistant@myaddress.com"Case "env"emailAddress = "receptionist@myaddress.com; envassistant@myaddress.com"Case "sw"emailAddress = "receptionist@myaddress.com; swassistant@myaddress.com"Case "fac"emailAddress = "receptionist@myaddress.com; facassistant@myaddress.com"Case "www"emailAddress = "receptionist@myaddress.com; wwwassistant@myaddress.com"Case ElseemailAddress = "receptionist@myaddress.com"End Select '*************************************************************************************************' set new message objectDim myMessage As System.Web.Mail.MailMessage = New System.Web.Mail.MailMessage()'**********************************************************' set message properties'********************************************************** myMessage.From = "me@myaddress.com"myMessage.To = emailAddressmyMessage.Subject = employee & " Signing Out at " & TimeOut.Text & " " & BLGroupmyMessage.Body = employee & " Signing Out at " & TimeOut.Text & " for " & Destination.Text & " will potentially return at " & EstTimeIn.Text'***********************************************************' set smtp server namesmtpMail.SmtpServer = "mailserver1"' send email using smtpsmtpMail.Send(myMessage) It sends the message and returns the BLGroup value everytime but only sends to the receptionist.  Thanks for your time! 

View 1 Replies View Related

Datareader Query Timeout

Oct 4, 2005

Hello,

View 16 Replies View Related

Sql Helper Help

May 29, 2008

Hi I am building 3 tier application using ASP.NET and i am using sqlhelper class in Data Access Layer..now my question is "How to make use of sqltransaction using sqlhelper class.."means i want to make use of sqltransaction object for concurrency control...but since i m new to sql helper. that's why i am not able to do this... Please solve my problem by giving some sample code..so that i can understand the code & implement in my application.... Thanks in Advance...Abdul Javed Khan  

View 9 Replies View Related

SQL Helper Help

Jun 16, 2008

Hi..

Could you please send me the  sample code on how to pass sqltransaction to sql helper class..bcoz as i know i need to declare sqltransaction in aspx form & then i need to pass which is not a good approach in 3-tier..so please suggest me, if u have some sample code..please send me... Thanks in AdvancedAbdul Javed Khan

View 8 Replies View Related

DataReader ODBC Query Timeout

Oct 25, 2006

Hello !

I get in my SSIS Package a Query Timeout in the Datareader!

I Use the ADO.Net OBC Connection with the Connection String:

Dsn=xxx;uid=xxx;connection timeout=0;command timeout=0;query timeout=0

Is there any Option to set the Query Timeout ?



Thanks !



Pseudo

View 5 Replies View Related

Replication Configuration Helper (Id Buy It!)

Sep 18, 2006

Let's face it - setting up SQL 2005 merge pull replication using HTTPS between numerous SQL Server 2005 Express clients and one or more Publishers is an overly complicated exercise.

It should be easy. Why is it so hard?

In a nutshell: The connection chain and security chain is long and when there's a problem the error messages just don't point you to the answer - You have to search for it;re-read the doco; talk to gurus; use logging tools;re-read the error message 100 times; etc. etc.

It should be possible to create a 'Replication Configuration Helper' that steps -step by step - through both the connection chain (App - to SQL Express - to local network components - to the TCP/IP network - to the web server - to the publishers/distributers) and through the security chain (App - to SQL Express authentication - local agents including certificate errors - to ISS server authentication - to Distributer authentication -to Publisher authentication to Article authentication).

Let's face it - the steps occur in sequence and each step can be tested and the appropriate error message raised - TOGETHER WITH INSTRUCTIONS ON HOW TO RESOLVE! (I mean - what's use is an error message when you have to cut and past the error message into a browser and search for ages before you get the instructions on how to resolve the problem?)

So c'mon - someone volenteer to write a 'Replication Configuration Helper'

View 1 Replies View Related

SQL Server Active Directory Helper Service

May 9, 2007

Hi,
Couple questions regarding SQL Server Active Directory Helper service:
a)What is its purpose?
b)Where can I get more detailed information about this service and
SQL Server 2005 services in general?
c)How can the following error during start up phase of this service
be avoided?
Error Message:
'0' is an invalid number of start up parameters. This service takes two start up parameters.

FYI: SQL Server 2005 is run on Windows XP professional.

Thanks,
Mike

View 2 Replies View Related

Active Direcory Helper Service Fails To Launch

Oct 10, 2007

I just installed a copy of sql2005 express, and need to use windows authentication. the ad helper service bombs on startup, any helpfull suggestions would be appreciated.
thanks

View 2 Replies View Related

Active Directory Helper Service Aborting Upon Startup

Sep 6, 2007



On a fairly new install of SQL 2005 64-bit, upon bootup the Active Directory Helper Serivice is aborting with the error 3221225572 (0XC0000064). When an attempt is made to start the service manually the following error appears: "Windows could not start teh SQL Server Active Directory Helper on Local Computer. For more infromation, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to the service-specific error code - 1073741724."

The service is strating up under the network services account, which I am suspicous may be a problem. Currently the SQL server is running under SQL 2005 SP2 with no post service pack hotfixes. Do you have any advice on how to get the service to launch without errors?

View 4 Replies View Related

Return The Results Of A Select Query In A Column Of Another Select Query.

Feb 8, 2008

Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?

View 9 Replies View Related

Select Query Based Upon Results Of Another Select Query??

Sep 6, 2006

Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return.
This is implemented as a stored procedure

declare @OwnerIdent varchar(7)
set @OwnerIdent='A12345B'

SELECT table1.val1 FROM table1 INNER JOIN table2
ON table1. Ident = table2.Ident
WHERE table2.Ident = @OwnerIdent

'Now for each result of the above I need to run the below query

SELECT Clients.Name , Clients.Address1 ,
Clients.BPhone, Clients.email
FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey
WHERE Growers.PIN = @newpin)

'@newpin being the result from first query

Any help appreciated

View 4 Replies View Related

Datareader Destination As Source For Other Datareader Source ?

Aug 30, 2006

HI!

as far as I know from docs and forum datareader is for .NET data in memory. So if a use a complex dataflow to build up some data and want to use this in other dataflow componens - could i use data datareader source in the fist dataflow and then use a datareader souce in the second dataflow do read the inmemoty data from fist transform to do fursther cals ?

how to pass in memory data from one dataflow to the next one (i do not want to rebuild the logic in each dataflow to build up data data ?

Is there a way to do this ? and is the datareader the proper component ? (because its the one and only inmemory i guess, utherwise i need to write to temp table and read from temp table in next step) (I have only found examples fro .NET VB or C# programms to read a datareader, but how to do this in SSIS directly in the next dataflow?

THANKS, HANNES

View 7 Replies View Related

Result Sets Using Select In Query Anlyzer Vs BCP Vs Select Into

Jul 9, 2002

When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into.
Thanks

View 1 Replies View Related

Date Select Query - Select Between Two Dates

Aug 22, 2006

have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003.

i have tried with the following query, i need help putting my queries together
select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004')

when i run this query i get this message

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15

please help
mustfa

View 6 Replies View Related

SQL Server 2012 :: Adding Count To Query Without Duplicating Original Select Query

Aug 5, 2014

I have the following code.

SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,

[code]...

However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.

View 2 Replies View Related

SQL Server 2012 :: How To Pull Value Of Query And Not Value Of Variable When Query Using Select Top 1 Value From Table

Jun 26, 2015

how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...

create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)

[code]...

View 4 Replies View Related

Transact SQL :: Use Query Results As Select Criteria For Another Query

Jul 10, 2015

I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.

While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.

View 6 Replies View Related

Transact SQL :: SELECT On Column Name From Query Result Set In Same Query?

May 9, 2015

I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).

To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:

COLUMN_NAME Value
----------- -----
colB        123
colA        XYZ

I've tried dynamic SQL to no success, probably not executing the concept correctly...

Below is what I have:

CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC

[Code] ....

View 4 Replies View Related

Declaring A Table Variable Within A Select Table Joined To Other Select Tables In Query

Oct 15, 2007

Hello,

I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.

I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.

This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.

So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?

Thanks in advance,
Andy



Select * from

(

declare @years table (years int);

insert into @years

select

CASE

WHEN month(getdate()) in (1) THEN year(getdate())-1

WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())

END

select

u.fullname

, sum(tx.Dm_Time) LastMonthBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-1

and

year(dm_date) = (select years from @years)

and tx.dm_billable = 1

group by u.fullname

) as A

left outer join

(select

u.FullName

, sum(tx.Dm_Time) Billhours

, ((sum(tx.Dm_Time))

/

((day(getdate()) * ((5.0)/(7.0))) * 8)) perc

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

tx.Dm_Billable = '1'

and

month(tx.Dm_Date) = month(GetDate())

and

year(tx.Dm_Date) = year(GetDate())

group by u.fullname) as B

on

A.Fullname = B.Fullname

Left Outer Join

(

select

u.fullname

, sum(tx.Dm_Time) TwomosagoBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-2

group by u.fullname

) as C

on

A.Fullname = C.Fullname

View 1 Replies View Related

Select Query Vs Store Procedure Query

Oct 29, 1998

hi, does it make a difference to write the following select statement in either query window or create a sp and then calling the store procedure to be executed..

select * from authors

OR


create procedure authors as

select * from authors





lets assume that we have million records in the author table. is it faster to run the query from within a store procedure or not ?
thanks for your input

Ali

View 1 Replies View Related

Help With Datareader

Jun 8, 2007

Hey guys, whats an easy way to pass a value into a stored procodure?
 I tried the code below but I keep on getting a "Procedure 'sp_InsertData' expects parameter '@gpiBatchNo', which was not supplied." error. My stored proc basically gets inserts the passed variable into a databaseSqlConnection sqlSecConnection = new SqlConnection(sqlPriConnString);SqlCommand sqlSecCommand = new SqlCommand();
sqlSecCommand.Connection = sqlSecConnection;
sqlSecCommand.CommandText = "sp_InsertData";sqlSecCommand.CommandType = CommandType.StoredProcedure;sqlSecCommand.Parameters.Add("@gpiBatchNo", SqlDbType.NVarChar) ;
sqlSecConnection.Open();int returntype = sqlSecCommand.ExecuteNonQuery();
sqlSecConnection.Close();

View 2 Replies View Related

How Can I Use Datareader

Oct 20, 2007

This code is currently loading my DataGridView
How can i change this to use the Datareader


Dim myConnection As SqlConnection = New SqlConnection("Data Source=ANTEC30SQLEXPRESS;Initial Catalog=test;Integrated Security=True;Pooling=False")


Dim myCmd As SqlCommand = myConnection.CreateCommand()

myConnection.Open()


myCmd.CommandType = Data.CommandType.Text

myCmd.CommandText = "Select * From tblParts"


Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(myCmd)

Dim myDataSet As DataSet = New DataSet()

myDataAdapter.Fill(myDataSet)


DataGridView1.DataSource = myDataSet.Tables(0)

View 7 Replies View Related

DataReader Access

Feb 22, 2007

Hi,
I am facing a problem to access datareader... actually i want to get data on lables from datareader. actually i am having one table having only one column and i hav accessed all data into datareader but the problem is that i just want to get data row by row...
 
For example there are four labels Label1, Label2, Label3, Label4
and want to print the data from datareader on to thease labels....
 
 
plz do reply... i am in trouble

View 1 Replies View Related

DataReader And DataAdapter

Feb 28, 2007

Hi,    What is the difference b/w sqldatareader and sqldataadapter? For what purpose are they used in a database connection & how do they differ from each other? Pls explain me in detail.Regards Vijay.

View 1 Replies View Related

Dataset Or Datareader?

Jun 20, 2007

i need help to know what is the best practice
i have a stored proc which returns 4 different resultselts
will that be easy to use dataset or datareader?
my purpose of using dataset/datareader is to load the data in a class
thanks.
 

View 5 Replies View Related

Datareader Problem

Aug 20, 2007

Hi,
I cant seem to get this working right...I have a datareader which i loop through...i want to test each value to see if its null but i cant get the syntax right.  
I know i use dr.item("columnname") or dr(0) to pick a certain column but i dont know the column names and want to check them all anyway.  What is the syntax to do this.
Thanks for any help...this is prob very simple but just cant see it.
--------------------------------------------While dr.Read
If dr(0) Is System.DBNull.Value Then
Return "test"End If
End While

View 3 Replies View Related

Problem With Datareader

Aug 25, 2007

Hello     i creae one programm, there is an two data reader and two Gridview or datagrid , and my programm have one error my programm is there  using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;public partial class aries : System.Web.UI.Page{    SqlConnection con;    SqlCommand cmd;    SqlDataReader dr;    SqlDataReader dr1;    SqlCommand cmd1;        protected void Page_Load(object sender, EventArgs e)    {        string str;        str = ConfigurationSettings.AppSettings["DBconnect"];        con = new SqlConnection(str);        con.Open();        cmd = new SqlCommand("select color from zodiac_color where Sno=1", con);        dr = cmd.ExecuteReader();        GridView1.DataSource = dr;        GridView1.DataBind();        cmd1=new SqlCommand("select number from zodiac_number where Sno=1",con);        dr1 = cmd.ExecuteReader();        GridView2.DataSource = dr1;        GridView2.DataBind();    }}  i want to calll two value in a same database but the table is diffrent so please help me ?The error is ::---------    There is already an open DataReader associated with this Command which must be closed first. please help me ashwani kumar 

View 2 Replies View Related

Datareader Problem

Apr 9, 2008

hi to all , check this once..this all data related to bus seats SeatNo1,SeatNo2 seats varchar in databaseSt1,St2    status(bit in database sqlserver2000) All my code is working when reader[i + 2].ToString() == "True" is remove from code ..so plz tell me solution gow to ckeck status with datareaderSqlCommand command = new SqlCommand("Select SeatNo1,SeatNo2,St1,St2 from tblSts where
BUSID='S0008'", sqlCon);

        sqlCon.Open();

 

        SqlDataReader
reader = command.ExecuteReader();

        int x =
0;

        while
(reader.Read())

        {

            for
(int i = 0; i <= reader.FieldCount - 1; i++)

            {

                x = (i + 1);

                System.Web.UI.WebControls.Label myLabel = ((System.Web.UI.WebControls.Label)(Page.FindControl(("Label"
+ x))));

                System.Web.UI.WebControls.CheckBox myCheckbox = ((System.Web.UI.WebControls.CheckBox)(Page.FindControl(("Checkbox"
+ x))));

                if
(reader[i].ToString() != "NULL"
&& reader[i + 2].ToString() == "True"))

                {

                    myLabel.Text =
reader[i].ToString();

                    myCheckbox.Checked = false;

                }

                else

                {

                    myLabel.Text =
reader[i].ToString();

                    myCheckbox.Visible = false;

                }

            }

        }

        sqlCon.Close(); 

View 8 Replies View Related

Datareader Timeout

Apr 25, 2008

 Hi
In my web site I call all the content from the database with the use of querystrings. I use datareader to call the data each time a request appears from the querystring.  Although I close all the connections I still get occasionally the following error:
Timeout expired the timeout period elapsed prior to obtaining a connection from the pool. This may have occured because all pooled connections were in use and max pool size was reached.
 
If it is not a programming error then what could it be? I use sql server 2005 and vs 2005 asp.net 2.0 .

View 6 Replies View Related

Open Datareader

May 2, 2008

"There is already an open datareader associated with this command which must be closed first." 
I have received this same error before, but I'm not sure why I'm getting it here.'Create a Connection object.
MyConnection = New SqlConnection("...............................")

'Check whether a TMPTABLE_QUERY stored procedure already exists.
MyCommand = New SqlCommand("...", MyConnection)

With MyCommand
'Set the command type that you will run.
.CommandType = CommandType.Text

'Open the connection.
.Connection.Open()

'Run the SQL statement, and then get the returned rows to the DataReader.
MyDataReader = .ExecuteReader()

'Try to create the stored procedure only if it does not exist.
If Not MyDataReader.Read() Then
.CommandText = "create procedure tmptable_query as select * from #temp_table"

MyDataReader.Close()
.ExecuteNonQuery()
Else
MyDataReader.Close()
End If

.Dispose() 'Dispose of the Command object.
MyConnection.Close() 'Close the connection.
End With
As you can see, the connection is opened and closed, and the datareader is closed.   Here's what comes next...'Create another Connection object.
ESOConnection = New SqlConnection("...")

If tx_lastname.Text <> "" Then
If (InStr(sqlwhere, "where")) Then
sqlwhere = sqlwhere & " AND lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'"
Else
sqlwhere = " where lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'"
End If
End If
If tx_firstname.Text <> "" Then
If (InStr(sqlwhere, "where")) Then
sqlwhere = sqlwhere & " AND fname like '" & Replace(tx_firstname.Text, "'", "''") & "%'"
Else
sqlwhere = " where fname like '" & Replace(tx_firstname.Text, "'", "''") & "%'"
End If
End If

dynamic_con = sqlwhere & " order by arr_date desc "

'create the temporary table on esosql.
CreateCommand = New SqlCommand("CREATE TABLE #TEMP_TABLE (".............", ESOConnection)

With CreateCommand
'Set the command type that you will run.
.CommandType = CommandType.Text

'Open the connection to betaserv.
ESOConnection.Open()

'Run the SQL statement.
.ExecuteNonQuery()

End With

'query our side
ESOCommand = New SqlCommand("SELECT * FROM [arrest_index]" & dynamic_con, ESOConnection)

'execute query
ESODataReader = ESOCommand.ExecuteReader()

'loop through recordset and populate temp table
While ESODataReader.Read()

MyInsert = New SqlCommand("INSERT INTO #TEMP_TABLE VALUES("......", ESOConnection)

'Set the command type that you will run.
MyInsert.CommandType = CommandType.Text

'Run the SQL statement.
MyInsert.ExecuteNonQuery()

End While

ESODataReader.Close()  'Create a DataAdapter, and then provide the name of the stored procedure.
MyDataAdapter = New SqlDataAdapter("TMPTABLE_QUERY", ESOConnection)

'Set the command type as StoredProcedure.
MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure

'Create a new DataSet to hold the records and fill it with the rows returned from stored procedure.
DS = New DataSet()
MyDataAdapter.Fill(DS, "arrindex")

'Assign the recordset to the gridview and bind it.
If DS.Tables(0).Rows.Count > 0 Then
GridView1.DataSource = DS
GridView1.DataBind()
End If

'Dispose of the DataAdapter
MyDataAdapter.Dispose()

'Close server connection
ESOConnection.Close() Again, a separate connection is open and closed.I've read you can only have 1 datareader available per connection. Isn't that what I have here? The error is returned on this line: MyInsert.ExecuteNonQuery()
Help is appreciated.
 

View 3 Replies View Related

Datareader And Dataset

Nov 3, 2003

Hi

I am using a datareader to access data via a stored procedure. The reason for using the datareader is that the stored procedure is multi level depending on the variable sent to it. However I want to do two things with the data being returned.

The first is to poulate a datagrid - which I've done.
The second is to produce an Infragistic Web Graph. However according to the background reading I have done so far, I can only populate the graph from one of the following: datatable,dataview,dataset,Array or Ilist.

I don't want to make another call to the server for the same information, so how can I get the data out of a stored procedure into a dataset or dataview?

regards

Jim

View 1 Replies View Related







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