Number Of Row Returned By ExecuteReader

Nov 14, 2007

when I execute the line:

reader = comm.ExecuteReader();

Is there a way to get a count of the number of records returned (the query is a SELECT with no count in it)? I want to vary the display of the results set based on the number of records returned.

For example if no records are returned I want it to display nothing, if one, I want the header to be in the singular, but if more than one record is returned, I want it to display the header in plural form.

Here is my code snippet with further explanation of what I am trying to do:int Inumber = 0;foreach (string item in menuHeaders)

{string title = menuHeaders[Inumber];

sp.Value = menuHeaders[Inumber];

Inumber++;

conn.Open();reader = comm.ExecuteReader(CommandBehavior.CloseConnection);

//Get the culture property of the thread.CultureInfo cultureInfo = Thread.CurrentThread.CurrentCulture;

//Create TextInfo object.TextInfo textInfo = cultureInfo.TextInfo;

// WHAT I AM TRYING TO DO....... Here I would like to wrap this with an if statement, if  Records returned by the reader are 0, skip while loop and header display

// If one, then display in singular and if 2 add an s to the title. Convert to title case and display.content.Text += "<H3>" + textInfo.ToTitleCase(title) + "</H3>";while (reader.Read())

{

content.Text += "<a href='" + reader["website"] + "'>" + reader["f_name"] + reader["l_name"] + "</a>"+ ", " +reader["organization"]+"<br />";

}

//Close the connection.

reader.Close();

conn.Close();

}

View 1 Replies


ADVERTISEMENT

Set Number Of Returned Rows !!! Please Help Me.

Jun 3, 2004

I want to select data from a table with 5.000.000 rows. It's very slowly. Do you now, how I can select only a XY number of rows? I can't use TOP in select query. User see only 20-30 rows on his page, but he can use page_up, page_down. Is possible to something as lazy load?

View 2 Replies View Related

Number Of Rows Returned By Sp

Jul 9, 2004

Is it possible to get the number of Rows returned by a stored procedure in a profiler ?
if yes, what parameters shud I be looking for?

THanks

Harshal

View 7 Replies View Related

Get Number Of Rows Returned

Oct 18, 2007

I have a report that calls a stored procedure. I want to display the number of records that the stored procedure returned. I can't seem to find an expression that will do this. Is there an expression that will display the record count for a dataset?

Thanks,
Rob

View 1 Replies View Related

Count The Number Of Rows Returned - How??

Oct 13, 2007

hello,
i have a stored procedure SELECT CommentID, UserName, CommentingDate
FROM Comm
WHERE PictureID = @PictureID
ORDER BY CommentingDate DESC
 witch shows me the users who commented a Picture with PictureID = x
I need to add two rows at that stored procedure, one to show the number of total comments at that picutre (like counting the number of rows returned) and the second to show count the DISTINCT users who commented that picture
I tryied with COUNT but i have to use GROUP BY and i don't think this is good...
I hope you understand... please help me,
thanks

View 5 Replies View Related

Limit Number Of Results Returned

Sep 2, 2004

I am doing some SELECT queries on my database through ASP, but for example, I only want to return the 50 most recent entries that match the criteria. Is there any easy way to limit the number of results returned?

View 1 Replies View Related

Count The Total Number Of Results Returned

May 17, 2007

Iam using front page to dispalay my results.

At the bottom it shows me 1/10 i.e 1st page of 10 pages.

but what do i do if i want it to be shown as 1-10 out of 100 (if each page contains 10 results).

or it would be really good if i get count of both no. of recors as well as no. of pages.

View 2 Replies View Related

Extending The Number Of Rows Returned Per Page In RS

Jun 7, 2007

Is there a way to extend the number of rows that will be returned so that Reporting services doesn't display such a large number of pages for the users to page through? It would be easier for them to "wheel mouse" through a long page on the screen.



Anyone out there have any thoughts on how this might be accomplished?



Thanks!



Travis

View 1 Replies View Related

How To Get Number Of Records A Query Has Returned Using SqlDataSource Control?

May 17, 2007

Hi,
I'm using SqlDataSource control.
Is there a way to know how many records a query has returned?

View 3 Replies View Related

Limit The Number Of Records Returned In Stored Procedure.

Aug 17, 2007

In my ASP page, when I select an option from the drop down list, it has to get the records from the database stored procedure. There are around 60,000 records to be fetched. It throws an exception when I select this option. I think the application times out due to the large number of records. Could some tell me how to limit the number of rows to be returned to avoid this problem. Thanks.
 Query
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'

View 2 Replies View Related

Inflated Number Of Records Returned When Adding Bit Field Criteria

Jul 20, 2005

When querying a bit field, I am encountering a problem with MS SQLServer returning a larger number of records for a table than theactual number of records that exist within that table.For example, my customer table has 1 million unique records, so theresults of the following query are as such:select count(customer_nbr) from customer = 1,000,000There is bit field in the customer table that denotes whether acustomer has placed an order with us called. That flag is calledorder_flagIf I run the following query:select count(customer_nbr) from customer where order_flag = 1The result is 3,000,000 records.There is no logical way that this is possible, as my table onlycontains 1,000,000 unique records and the number of customers with anorder should be a subset of this.If a run the above query with a distinct before customer number, I getthe results I want:select count(distinct customer_nbr) from customer where order_flag = 1600,000 records.So while I can get to the answer I want, I have no idea why I amreturning incorrect values if I don't select distinct.Can anyone help? I checked microsoft support and message boards buthaven't seen anything.I should note that the bit field is indexed.I am not sure if that isthe problem or not.

View 1 Replies View Related

Receiving There Is An Invalid Number Of Result Bindings Returned For The ResultSetType: ResultSetType_Rowset

Sep 20, 2007



I am trying to set up an Execute SQL Task that will feed into a foreach loop for processing of data. I am using an ADO.NET Connection with Direct input with full result set. I have tried using "select * from <table>" and also "select <column1>, <column2> from <table>". I have user variables set in the package and have the table cfolumn names listed in result name and the appropriate package variable in variable name on the Result Set tab. There is a total of 53 columns per row and the number of rows that should be returned can be anywhere from none to several hundred. Upon trying to run the package, I am receiving
Error: 0xC00291E2 at Execute SQL Task, Execute SQL Task: There is an invalid number of result bindings
returned for the ResultSetType: "ResultSetType_Rowset".

Anyone have any ideas here? Thanks in advance!

View 2 Replies View Related

Need Help With ExecuteReader() Command

Jan 18, 2008

HI I am having problem with my Execute Reader. I am trying to insert values from 2 different tables into another table.    SqlCommand comm2; SqlDataReader reader2; /* Grabs the stuff out of the database */
comm2 = new SqlCommand("SELECT HiraganaCharacter,HiraganaImage FROM Hiragana", getConnection()); /* opens the database */
comm2.Connection.Open();
/* starts the reader */
reader2 = comm2.ExecuteReader();
/* goes through the first array list */
for (int i = 0; i < checkedLetters.Count; i++) { /* find the data by using the array list value as a where clause */ comm2.CommandText = "SELECT HiraganaCharacter,HiraganaImage FROM Hiragana WHERE HiraganaCharacter ='"
+ checkedLetters[i] + "'"; /* reads through the data */ reader2.Read(); /* puts the ID- this id was set somewhere else */ CommQuickLinksItems.Parameters["@QuickLinkID"].Value = QuickLinkId; CommQuickLinksItems.Parameters["@CharacterName"].Value = reader2["HiraganaCharacter"].ToString(); CommQuickLinksItems.Parameters["@CharacterImagePath"].Value = reader2["HiraganaImage"].ToString(); CommQuickLinksItems.ExecuteNonQuery(); } for (int j = 0; j < checkedLettersKata.Count; j++) { comm2.CommandText = "SELECT KatakanaCharacter,KatakanaImage FROM Katakana WHERE KatakanaCharacter ='"
+ checkedLettersKata[j] + "'"; reader2.Read(); CommQuickLinksItems.Parameters["@QuickLinkID"].Value = QuickLinkId; /* line it dies on */ CommQuickLinksItems.Parameters["@CharacterName"].Value = reader2["KatakanaCharacter"].ToString(); CommQuickLinksItems.Parameters["@CharacterImagePath"].Value = reader2["KatakanaImage"].ToString(); CommQuickLinksItems.ExecuteNonQuery(); } CommQuickLinksItems.Connection.Dispose(); CommQuickLinksItems.Dispose(); comm2.Connection.Dispose(); comm2.Dispose(); My first question is there a better way to setup a SqlCommand to just get the connection and wait on the Command object text? Right now I am doing comm2 = new SqlCommand("SELECT HiraganaCharacter,HiraganaImage FROM Hiragana", getConnection());Which is kinda pointless since in the for loop I change the command to something different right away. At the same time though I don't really want to make a new SqlCommand object in the for loop since then everytime it goes through the loop it would then re grab the connection what I find pointless tooNow the problem How I have it right now it does not grab the right stuff. The first for loop works great and everything gets inserted. The next loop does not work It seems like it it trying to take the data from the first for loop and insert that stuff again since I get this error System.IndexOutOfRangeException was unhandled by user code Message="KatakanaCharacter" Source="System.Data" StackTrace: at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at System.Data.SqlClient.SqlDataReader.get_Item(String name) at Practice.QuickLinks() in g:WebsiteJapanesePractice.aspx.cs:line 385 at Practice.btnQuickLink_Click(Object sender, EventArgs e) in g:WebsiteJapanesePractice.aspx.cs:line 411 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) InnerException: Basically what I did was for the first loop I chose 2 items and for the 2nd loop I chose 3 items. When it died on this line  CommQuickLinksItems.Parameters["@CharacterName"].Value = reader2["KatakanaCharacter"].ToString();The value was "i" but that was one of the values I choose for the first for loop. It should have been either u,e,o. So I am not sure what I am doing wrong. I thought as long as I change the Command text I would not need to do anything else but it seems like I am missing something.  

View 5 Replies View Related

Using ExecuteReader() And GetString()

Mar 3, 2008

Hi.
I'm trying to read data from a database. This is my code:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
connection.Open();SqlCommand cmd = new SqlCommand(sql, connection);
myReader = cmd.ExecuteReader();if (myReader.Read())
{
name1TextBox.Text = myReader.GetString(1);
addr1TextBox.Text = myReader.GetString(2);
code1TextBox.Text = myReader.GetString(5);
tel1TextBox.Text = myReader.GetString(6);
fax1TextBox.Text = myReader.GetString(7);
:
:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
The above code works fine until one of the GetString calls trys to return NULL (in this case myReader.GetString(5)).
In other words, this code will run through about 30 rows of data until it runs in to a NULL entry for one of the columns.
At that stage it's too late. I'm not allowed call GetString( ) on a NULL value.
Is there anyway I can test the column entry before calling GetString( ).
Regards (& thanks in advance)
Garrett

View 4 Replies View Related

Data Returned From .WriteXML Is Different Than What Is Returned In Query Analyzer.

Jun 20, 2006

I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.
Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).
Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.
If someone is able to help me figure this out, I would appreciate it.
1. public DataSet GetMarketList(string region, string marketRegion)2. {3.   string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE  a.RegionCode = b.RegionCode"; 5.   DataSet dsMarketList = new DataSet();6.   SqlConnection sqlConn = new SqlConnection(intranetConnStr);   7.   SqlCommand cmd = new SqlCommand(sql,sqlConn);8.  sqlConn.Open();9.   SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10.   try11.   {12.   adapter.Fill(dsMarketList);
 13.  String bling = adapter.SelectCommand.CommandText;//BRG 14.   dsMarketList.DataSetName="RegionMarket"; 15.  dsMarketList.Tables[0].TableName = "MarketList"; 16.    dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to  17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18.           // from the SQL query 19.  } 20.  catch(Exception e) 21. {  22. // Handle the exception (Code not shown)

View 2 Replies View Related

Getting Data From An ExecuteReader Variable

Jan 28, 2008

Hi all,I have a script which I am running to get the minimum date from a database table.I've connected to the database and run the sql but when I try to get the result i get an error saying "No data exists for the row/column."This is the code I have for it at the moment.1 Dim mySql As String = "SELECT MIN(LOSS_DATE) AS minDate FROM dbo_CLAIMS"
2 Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|NexusHolding.mdb;Persist Security Info=True"
3 Dim dbCon As New OleDbConnection(connectionString)4
5 dbCon.Open()6
7 Dim dbComm As New OleDbCommand(mySql, dbCon)8 Dim dbRead = dbComm.ExecuteReader()9 Dim minDate As String = dbRead.GetValue(0)10
11 Response.Write(minDate)Thanks in advance for any help. 

View 3 Replies View Related

Catch Raiserror From ExecuteReader

Feb 27, 2004

Hi. I am executing a stored procedure. The stored procedure raises an error and all I need is to catch this error. Pretty simple, but it only works with an ExecuteNonQuery and not with an Executereader statement. Can anybody explain to me why this happens?

Here's the sp:

CREATE PROCEDURE dbo.rel_test
AS
select 1
raiserror ('My error.', 11, 2)
return
GO

Here's the ASP.Net page:

<% @Page Language="VB" debug="True" %>
<% @Import Namespace="System.Data.SqlClient" %>
<script runat="server">
Public Function RunSP(ByVal strSP As String) As SqlDataReader
Dim o_conn as SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))
AddHandler o_conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)

o_conn.Open

Dim cmd As New SqlCommand(strSP, o_conn)
cmd.CommandType = System.Data.CommandType.StoredProcedure
Dim rdr as SqlDataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
rdr.Close()
cmd.Dispose()

Response.Write(o_conn.State)


End Function

Private Sub OnInfoMessage(sender as Object, args as SqlInfoMessageEventArgs)
Dim err As SqlError
For Each err In args.Errors
Response.Write(String.Format("The {0} has received a severity {1}, state {2} error number {3}" & _
"on line {4} of procedure {5} on server {6}:{7}", _
err.Source, err.Class, err.State, err.Number, err.LineNumber, _
err.Procedure, err.Server, err.Message))
Next
End Sub

Sub Page_Load(sender as Object, e as EventArgs)
RunSP("rel_test")
End Sub
</script>

View 2 Replies View Related

Stored Procedure And ExecuteReader

Jul 5, 2005

I am not seeing why this is not executing the reader, it just goes right by it when stepping through the code...
command.CommandType = CommandType.StoredProcedure;
// course
command.Parameters.Add( "@courseId", courseId );
// Parameter: LessonName
SqlParameter sLessonName = command.Parameters.Add( "@lessonName", SqlDbType.VarChar );
sLessonName.Size = 256;
sLessonName.Direction = ParameterDirection.Output;
// error code
SqlParameter pErrCode = command.Parameters.Add( "@errCode", SqlDbType.Int );
pErrCode.Direction = ParameterDirection.Output;
// execute the stored procedure
SqlDataReader spResults;
conn.Open();
spResults = command.ExecuteReader();
while( spResults.Read() ) // It never steps into the while statement like the reader is completed
{
RetrieveObjId objNames = new RetrieveObjId( spResults.GetString( 0 ));
searchResults.Add( objNames );
}
spResults.Close();And the stored procedure is.....CREATE  PROCEDURE dbo.retrieveLessonNames @courseId  VARCHAR(20), @lessonName  VARCHAR(256) OUTPUT, @errCode         INT               OUTPUT  ASBEGIN
 SELECT @lessonName = objName       FROM   objStructure WHERE  courseId = @courseId
 SET @errCode = 0  RETURN @errCode HANDLE_APPERR: SET @errCode = 1  RETURNHANDLE_DBERR: SET @errCode = -1  RETURNENDGOSuggestions?Thanks all,Zath

View 3 Replies View Related

Intermittent ExecuteReader Problems

Oct 26, 2006

I have VS 2005 and SQL CE 3.0. I sometimes get the a FileNotFoundException when I first use ExecuteReader. I believe this is because a dll has not been copied across because if I restart the emulator I can get it to work again.

Do I need to add a cab file/dll to my project to stop this happening?

Thanks

Emmet

View 1 Replies View Related

Problems With : SqlDataReader = Command.ExecuteReader()

Apr 11, 2007

I am currently tryinh to have this variable declared :   Dim SQLLecteur As SqlDataReader = Command.ExecuteReader()And receiving the following error : 'ExecuteReader' is not member of 'String'.1. The ExecuteReader was not present in the list following the Command.2. The variable is declared from a : Public Shared Sub3. This sub is located in a code library referenced in the web.config as a namespace : <add namespace="PAX20070409" />4. If used directly in the .vb file within this sub : Protected Sub btnConnection_Click, I am not receiving any errors about the Dim.It is pretty clear why the code is not working, but I have not been able to find a way to fix the problem. I am currently trying to find a way to make the Dim work from within my code library. If you have any idea on how this could be achieve, it would be greatly apreciated.Thank you :)RV3 

View 4 Replies View Related

ExecuteReader: Connection Property Has Not Been Initialized.

Apr 23, 2008

I'm writing my first vb.net app.  Have a default page that uses a persons network login to query a database to get all their  timekeeper id, firstname, last name, etc.  But I keep getting this error.  (My code is below)  What am I missing??? 
ExecuteReader: Connection property has not been initialized.
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: ExecuteReader: Connection property has not been initialized.Source Error:



Line 21: conn.Open()
Line 22:
Line 23: reader = comm.ExecuteReader()
Line 24: If reader.Read() Then
Line 25: EmployeesLabel.Text = reader.Item("tkinit") 
<script runat="server">Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)Dim conn As SqlConnectionDim comm As SqlCommandDim reader As SqlDataReaderDim connectionString As String = ConfigurationManager.ConnectionStrings("xxxConnectionString").ConnectionStringcomm = New SqlCommand("Select top 1 tkinit, tklast, tkfirst +' '+ tklast as fullname from txxx WHERE login = @login)", conn)comm.Parameters.Add("@Login", Data.SqlDbType.VarChar)comm.Parameters("@Login").Value = Me.User.Identity.Name.Substring(User.Identity.Name.IndexOf("") + 1)conn = New SqlConnection(connectionString)conn.Open()reader = comm.ExecuteReader()If reader.Read() ThenEmployeesLabel.Text = reader.Item("tkinit")FirstLastName.Text = reader.Item("fullname")End Ifreader.Close()conn.Close()End Sub</script>

View 1 Replies View Related

ExecuteReader: Connection Property Has Not Been Initialized.

Mar 11, 2005

I have a web form that is generating an error and I can't seem to figure out why for the life of me. Below is the code:


Private Sub VerifyNoDuplicateEmail()
Dim conn As SqlConnection
Dim sql As String
Dim cmd As SqlCommand
Dim id As Guid
sql = "Select UserID from SDCUsers where email='{0}'"
sql = String.Format(sql, txtEmail.Text)
cmd = New SqlCommand(sql, conn)
conn = New SqlConnection(ConfigurationSettings.AppSettings("cnSDCADC.ConnectionString"))
conn.Open()
Try
'The first this we need to do here is query the database and verify
'that no one has registed with this particular e-mail address
id = cmd.ExecuteScalar()
Response.Write(id.ToString & "<BR>")
Catch
Response.Write(sql & "<BR>")
Response.Write("An error has occurred: " & Err.Description)
Finally
If Not id.ToString Is Nothing Then
'The e-mail address is already registered.
Response.Write("Your e-mail address has already been registered with this site.<BR>")
conn.Close()
_NoDuplicates = False
Else
'It's safe to add the user to the database
conn.Close()
_NoDuplicates = True
End If
End Try
End Sub

Web.Config
<appSettings>
<!-- User application and configured property settings go here.-->
<!-- Example: <add key="settingName" value="settingValue"/> -->
<add key="cnSDCADC.ConnectionString" value="workstation id=STEPHEN;packet size=4096;integrated security=SSPI;data source=SDCADC;persist security info=False;initial catalog=sdc" />
</appSettings>


Can anyone show me the error of my ways?

Thanks,
Stephen

View 4 Replies View Related

Command Doesnt Support Executereader

Nov 26, 2006

Code:

connstr = "my connction info"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open connstr

Set objCmd=Server.CreateObject("ADODB.Command")
set objCmd.ActiveConnection = objconn

objCmd.CommandText = "my stored procedure"
objCmd.CommandType = 4

objcmd.parameters.append objcmd.createparameter("@itemcheckduplicate", 200, 1, 30, itemadd)
SqlDataReader forduplicate = objcmd.ExecuteReader()

my procedure :

@itemcheckduplicate varchar(30)

as
select sw_item_itemid
from sw_item
where sw_item_item like @itemcheckduplicate



why my command doesnt support ExecuteReader ?

Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'ExecuteReader'

View 2 Replies View Related

ExecuteReader: CommandText Propert Has Not Been Initialized

Oct 2, 2007



Hello All,

I receive the following error when attempting to preview or run a simple report:

ExecuteReader: CommandText propert has not been initialized


I have searched the help website and other forums, but I have had no success.

I am new to Reporting Services and this is one of the first reports I have created. All of my reports have this error.

Any ideas?

Thanks in advance.

View 1 Replies View Related

ExecuteReader Error A Parameter Is Missing [Paremeter Ordinal = 1]

Sep 24, 2007

Hello and thanks in advance.

This error was received executing a SqlCeCommand method of ExecuteReader();

I have only found 3 hits in Google about this error and the time it was asked in a MS forum it was not answered. I need an answer, please.

bombar...see code below


SqlCeDataAdapter adp = null;

adp = new SqlCeDataAdapter();

SqlCeCommand cmd;

cmd = _Con.CreateCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "insert into InventoryCounts(DateTime,WarehouseID,PartID,LotID,UserName,Count) VALUES(?,?,?,?,?,?)";

adp.InsertCommand = cmd;

cmd = _Con.CreateCommand();

cmd.Parameters.Clear();

SqlCeParameter p1 = cmd.Parameters.Add("DateTime",SqlDbType.DateTime);

SqlCeParameter p2 = cmd.Parameters.Add("WarehouseID",SqlDbType.NChar,20);

SqlCeParameter p3 = cmd.Parameters.Add("PartID", SqlDbType.NChar,20);

SqlCeParameter p4 = cmd.Parameters.Add("LotID", SqlDbType.NChar,20);

SqlCeParameter p5 = cmd.Parameters.Add("UserName", SqlDbType.NChar,20);

SqlCeParameter p6 = cmd.Parameters.Add("Count", SqlDbType.Int);

p1.Value = Convert.ToString(DateTime.Now);

p2.Value = "Warehouse1";

p3.Value = txtBPartIDShow.Text;

p4.Value = txtBLotIDEntry.Text;

p5.Value = "JHB";

p6.Value = Convert.ToInt32(txtBCountEntry.Text);



cmd = _Con.CreateCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "insert into InventoryCounts(DateTime,WarehouseID,PartID,LotID,UserName,Count) VALUES(?,?,?,?,?,?)";

cmd.Prepare();

cmd.ExecuteReader();

View 4 Replies View Related

System.InvalidOperationException: ExecuteReader: Connection Property Has Not Been Initialized.

Jan 29, 2008

Hi,

I have written a CLR Function in C#. The function works as expected except that I am trying to read data some data during the function call and get the following error:


Msg 6522, Level 16, State 1, Line 1

A .NET Framework error occurred during execution of user-defined routine or aggregate "fn_SLARemaining":

System.InvalidOperationException: ExecuteReader: Connection property has not been initialized.

System.InvalidOperationException:

at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader()

at SLARemaining.SupportTimes.addSupportHours()

at SLARemaining.CalculateTimeRemaining.GetTimeRemaining(String openDate, Decimal SLADuration)

at UserDefinedFunctions.fn_SLARemaining(SqlDateTime DateFrom, SqlDateTime DateTo, SqlInt32 PriorityTime, SqlDecimal AdjustmentTime, SqlDecimal Status)

.


The main code for the function is this:


using System;

using System.Data;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]

public static SqlString fn_SLARemaining(SqlDateTime @DateFrom, SqlDateTime @DateTo, SqlInt32 @PriorityTime, SqlDecimal @AdjustmentTime, SqlDecimal @Status)

{

SLARemaining.CalculateTimeRemaining remaining;

remaining = new SLARemaining.CalculateTimeRemaining();

int duration = Convert.ToInt32(PriorityTime.ToString());

if (!DateFrom.IsNull)

{

string date = DateFrom.Value.ToShortDateString() + " " + DateFrom.Value.ToShortTimeString();

SqlString result = remaining.GetTimeRemaining(date, duration);

return result;

}

else

{

return null;

}

}

};


The function calls the following method from another class:


public string[] addSupportHours()

{

string[] supportedHours = new string[28];

SqlDataReader dr;



SqlCommand cmd = new SqlCommand();

cmd.CommandText = "xxxxxxx"; //<-- commented out for this post

using (SqlConnection cn = new SqlConnection("context connection=true;"))

{

cn.Open();


dr = cmd.ExecuteReader();



while (dr.Read())

{
//do some stuff
}

cn.Close();

cn.Dispose();
}

The error message claims that the connection has not been initialized - can't work out why? Any help will be appreciated!

View 3 Replies View Related

ExecuteReader Requires An Open And Available Connection. The Connection's Current State Is Closed.

Apr 26, 2007

I am accessing SQL2005 with C# code using OleDbConnection.



A try and catch block catches the following error once a while between the Open() and Close() of the connection:

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.



I do not even have any idea where to start to debug this. The ExecuteNonQuery() runs a delete SQL query. It works 99.9% of the time. I do not see anything wrong when this error happens.



Any hint would be greatly appreciated.



View 9 Replies View Related

The Number Of Requests For XXXServerXXXUser Has Exceeded The Maximum Number Allowed For A Single User

Oct 15, 2007



I have created a local user on Report Server Computer and the user has the administrative rights.
When i try to connect Report Server (http://xxx.xxx.xxx.xxx/reportserver) with this user's credantials. (ReportServer directory security is set -only- to Basic Authentication. ).
I get the following error.


Reporting Services Error
--------------------------------------------------------------------------------

The number of requests for "XXXServerXXXUser" has exceeded the maximum number allowed for a single user.
--------------------------------------------------------------------------------
SQL Server Reporting Services


Then i try to login using a different user with administrative rights on the machine, i can logon successfully.
The system is up for a month but this problem occured today?!? What could be the problem?!?

View 2 Replies View Related

How To Enter More Number Of Rows In A Table Having More Number Of Columns At A Time

Sep 24, 2007

Hi

I want to enter rows into a table having more number of columns

For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?

Suppose i am having my data in .txt file (or ) in .xls

( SQL Server 2005)

View 1 Replies View Related

Transact SQL :: How To Select A Number Less Or Equal Than A Supplied Number

Jun 23, 2015

Got this query and I need the following result;

declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @tableĀ 
select 4

[Code] ....

The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5

View 4 Replies View Related

How To Change A Decimal Number To Percent Format Number?

Oct 8, 2006

in my sql, i want to change a decimal number to percent format number, just so it is convenient for users. for example there is a decimal number 0.98, i want to change it to 98%, how can i complete it?

thks

View 4 Replies View Related

Limitations In Term Of Number Of Tasks And Number Of Columns

Jun 5, 2007

Hi,

I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.

To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow.
I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.

Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.

Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?

If not, then do you have any idea why it's so slow ?

Thanks in advance for any answer.

View 1 Replies View Related

How To Number Records / Duplicates Receiving Same Number

Feb 19, 2013

I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.

View 8 Replies View Related







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