I'm using a SqlDataSource with a GridView to look at the contents of a table. I'd like to display the number of rows returned by the query in a label above the grid. How do I get a hold of this value? I can't find where it's exposed through the DataSource or the Grid. I can get the number of rows in the currently displayed page, but that's not the value I'm after.
I have a web page that has been in production for over a year. On occasion the web app users report sporadic errors which last a few minutes then go away. The page loads up a generic DataSet, and then that DataSet is assigned to a datagrid on the page. The error is that a column that is expected to be in the dataset is not found. I know this column must exist in the database because its a required field. After we reset IIS, then all returns to normal and the errors go away. Below is the stack trace. Exception: System.IndexOutOfRangeException Message: Cannot find column LastName. Source: System.Data at System.Data.DataTable.ParseSortString(String sortString) at System.Data.DataView.UpdateIndex(Boolean force) at System.Data.DataView.SetIndex(String newSort, DataViewRowState newRowStates, DataFilter newRowFilter) at System.Data.DataView..ctor(DataTable table, String RowFilter, String Sort, DataViewRowState RowState) at MySystem.Web.GuestSearch.ShowResults(DataSet ds, Int32 currentPageIndex) at MySystem.Web.GuestSearch.LoadResults(Int32 currentPageIndex) at MySystem.GuestSearch.btnSearch_Click(Object sender, EventArgs e) at System.Web.UI.WebControls.Button.OnClick(EventArgs e) 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() Our DBA can find no problems on the database side and our web servers don't log any system event problems either. I do not understand why resetting IIS helps the issue. I need to find the real cause of the problems rather than just resetting IIS all the time. Any ideas?
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!
I have an existing call to a webservice that updates an MDB with data from a dataset. I was now moving to a process using SQL Compact Edition sdf instead of an mdb.
I belive I have to DROP/Truncate the table in the SDF file, then create a new table then somehow insert the results of the retrieved dataset into the new table. DOes anybody have any pointers to help me out?
Here is my old way of doing it, using access.
Thanks.
Private Sub retrieveData(ByVal intMode As Integer) 'This process will call a webservice passing a parameter which in turn executes a SQL stored procedure, returning a dataset Dim MyObj As localhost.Service1 = New localhost.Service1 Dim ds As DataSet ds = MyObj.RetrieveSQLdata(txtSecureString, intMode) Dim myConnection As OleDbConnection Dim cmd As OleDbCommand = New OleDbCommand Dim Connstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & FileName & ";Persist Security Info=False" myConnection = New OleDbConnection(Connstr) myConnection.Open() cmd.Connection = myConnection Dim mystr As String = "" For Each row As DataRow In ds.Tables(0).Rows mystr = "" mystr = mystr & "INSERT INTO CustMast ( " mystr = mystr & "StoreNo, CustNo, ShipNo" mystr = mystr & "values(" mystr = mystr & "'" & row("StoreNo").ToString & "', " mystr = mystr & "'" & row("CustNo").ToString & "', " mystr = mystr & "'" & row("ShipNo").ToString & "')" cmd.CommandText = mystr cmd.ExecuteNonQuery() Next System.Windows.Forms.Application.DoEvents() cmd.Dispose() myConnection.Close() myConnection.Dispose()
In one of my projects, i have been using the SSRS web service to render a report as pdf and then manually emailing it out.
I need to know whether the data set for the report returned any data or not (so that i can avoid emailing the report with a blank report body).
Ofcource this can be done by executing the same query (same as the query for the report) from C# code and checking the count of the resultset, before calling the SSRS Render method. But it might not be an efficient method.
Was wondering the the SSRS Web Service provides an property or method that can let me figure out whether the resultset was empty or not (after the report execution).
I have a database that is 1.7terabyte in size with 136gb free and throws a "transport level error" telling me to discard the results when I run dbccshrinkfile ('DBNAME', size). I have tried various increments of size, from truncateonly to 1MB below its current value, and nothing works. I have tried to detach and reattach the db, restart the service, restart the server, and none have provided a solution. Any ideas?
I was working on the SSRS Report Designer and was trying to copy and paste a huge SQL query from SQL Management Studio (like I always do) to the dataset window.
This method usually works, but for this extremely long SQL query with nested SELECTs and JOINs, it seems as though the Report Designer dataset query window limits the number of characters including carriage returns I can input in it.
When I paste my SQL query from SQL Management Studio, it seems to paste only three quarters of the query, and thereafter I am not able to manually key in anything else or even make a line feed in the dataset query window.
Is there a limit to the number of characters I can key in here ? If so, is there any workarounds or configurations I can try ?
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)
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
I have a small number of rows in a dataset, Table 1. There is a CLOB on a large dataset, Table 2. They join on a PK. I would like to retrieve this CLOB and add it to the data flow for Table1. In short I want to emulate the following:
Table 1: Small table without CLOB, 10 rows. Table 2: Large table with CLOB, 10,000,000 rows
select CLOB from table2 where pk = (select pk from table1)
I want this to return the CLOBs for the small number of rows in Table 1. The PK is indexed obviously so it should be a fast look up.
Table 1 and Table 2 live on different Oracle databases. How do I perform this operation efficiently in SSIS? It seems the Lookup and Merge Join wont do this.
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters.
I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
I found out the data I need for my SQL Report is already defined in a dynamic dataset on another web service. Is there a way to use web services to call another web service to get the dataset I need to generate a report? Examples would help if you have any, thanks for looking
Hi, I have a stored procedure attached below. It returns 2 rows in the SQL Management studio when I execute MyStorProc 0,28. But in my program which uses ADOHelper, it returns a dataset with tables.count=0. if I comment out the line --If @Status = 0 then it returns the rows. Obviously it does not stop in if @Status=0 even if I pass @status=0. What am I doing wrong? Any help is appreciated.
ALTER PROCEDURE [dbo].[MyStorProc]
(
@Status smallint,
@RowCount int = NULL,
@FacilityId numeric(10,0) = NULL,
@QueueID numeric (10,0)= NULL,
@VendorId numeric(10, 0) = NULL
)
AS
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
If @Status = 0
BEGIN
SELECT ...... END If @Status = 1 BEGIN SELECT...... END
i have two datasets.one dataset have old data from some other database.second dataset have original data from sql server 2005 database.both database have same field having id as a primary key.i want to transfer all the data from first dataset to new dataset retaining the previous data but if old dataset have the same id(primary key) as in the new one then that row will not transfer. but if the id(primary key) have changed values then the fields updated with that data.how can i do that.
D2 is a list of data. each row in D2 has a classid. D2 may or may not have all the classids in D1. all classids in D2 must be in D1.
I want to show fields in D2 and group the data with classids in D1 and show every group as a seperate table. If no data in D2 is available for a classid, It shows a empty table.
I installed sql 2005 a while back. Then I recently found out my file system was fat32 (I don't understand why the hardware people did this...) and I had to convert to NTFS. Naturally the sql service no longer worked so I uninstalled inorder to reinstall now I can't reinstall it I keep getting this message
native_error=5039, msg=[Microsoft][SQL Native Client][SQL Server]MODIFY FILE failed. Specified size is less than current size.
=CountDistinct(IIF(Fields!Released_DT.Value = Fields!Date2.Value, Fields!Name.Value, Nothing)) Released_DT = a date - 09/03/2015 or 09/02/2015 Date2 = returns another date value in this case 09/03/2015
What I'm trying to do is: count distinct number of people (Fields!Name.Value) if the Relased_DT = Date2.My IIF statement is returning a zero value.
Hi folks,Can anyone enlighten me here? I'm trying to use a SPROC which, when supplied with an int, looks up the table and returns certain columns from it. I'm using a SqlCommand, here's my codebehind: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SqlCommand dataSource = new SqlCommand("retrieveData", new SqlConnection(dbConnString)); dataSource .CommandType = CommandType.StoredProcedure; dataSource .Parameters.AddWithValue("id", poid); dataSource .Parameters.AddWithValue("title", title).Direction = ParameterDirection.Output; dataSource .Parameters.AddWithValue("creator", creator).Direction = ParameterDirection.Output; dataSource .Parameters.AddWithValue("assignee", assignee).Direction = ParameterDirection.Output; etc, etc... And the SPROC:------------------------------------------------------------------------------------------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[retrieveData] @id int, @title varchar(50) OUTPUT, @creator varchar(50) OUTPUT, @assignee varchar(50) OUTPUT, @contact varchar(50) OUTPUT, @deliveryCost numeric(18,2) OUTPUT, @totalCost numeric(18,2) OUTPUT, @status tinyint OUTPUT, @project smallint OUTPUT, @supplier smallint OUTPUT, @creationDateTime datetime OUTPUT, @amendedDateTime datetime OUTPUT, @locked bit OUTPUT AS /**SET NOCOUNT ON; **/ SELECT [title] AS [@title], [datetime] AS [@creationDateTime], [creator] AS [@creator], [assignee] as [@assignee], [supplier] as [@supplier], [contact] AS [@contact], [delivery_cost] AS [@deliveryCost], [total_cost] AS [@totalCost], [amended_timestamp] AS [@amendedDateTime], [locked] AS [@locked] FROM purchase_orders WHERE [id] = @id; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ The id being passed in is definately not null, and is set to a value of an item I know exists. The resulting error is:
Exception Details: System.InvalidOperationException: String[1]: the Size property has an invalid size of 0.Line 63: retrievePODetails.Connection.Open();Line 64: retrievePODetails.ExecuteNonQuery();[InvalidOperationException: String[1]: the Size property has an invalid size of 0.] System.Data.SqlClient.SqlParameter.Validate(Int32 index) +717091... ... Can anyone see anything I'm missing? Thanks,Ally
Using C#, SQL Server 2005, ASP.NET 2, in a web app, I've tried removing the size from parameters of type NCHAR, NVARCHAR, and VARCHAR. I'd rather just send a string and let the size of the parameter in the SP truncate any extra chars if need be. I began getting the error below, and eventually realized it happened only with output parameters, as in the code snippet below.String[3]: the Size property has an invalid size of 0. par = new SqlParameter("@BusinessEntity", SqlDbType.NVarChar); par.Direction = ParameterDirection.Output; cmd.Parameters.Add(par); cmd.ExecuteNonQuery();What's the logic behind this? Is there any way around it other than either finding out what the size should be, or assigning a size larger than would ever be needed? ThanksMike Thomas
I have one db test with one .mdf and .ldf file...mdf file size is 100mb and for some reson i removed all the tablesfrom that .mdf file and transfer it into new secondary file so all thetables moved into secondary file now i want to reduce the first .mdffile from 100 mb to 50mb is that possible,it's showing 90mb is free.Please reply
I am getting error to run stored procedure using executenonquery method. The Stored Procedure is having OUTPUT parameter. ExecuteNonQuery statement is called using SqlHelper. Error : String[18]: the Size property has an invalid size of 0
Just wanted to know what is a general rule of thumb when determining log file space against a database's data file.We allow our data file for our database to grow 10%, unlimited. We do not allow our log file to autogrow due to a specific and poorly written process (which we are in a three month process of remove) that can balloon the log file size.Should it be 10% of the Data file, i.e. if the Date file size is 800MB the log file should be 8MB?I realize there are a myraid of factors that go against file size but a general starting point would be nice.ThanksJeff--Message posted via http://www.sqlmonster.com
Hi every body... I have a probleme I have a web Services which contains a method getValue(IDEq (int), idIndicator(int), startTime(dateTime), endTime(dateTime)) I need to call this method. But my problem is how pass parameter ? I see the tab Param but it isn't work as I wait,... maybe I do a mistake...
I want that statTime and endTime are select by the user via a calendar for example... now idIndicator and idEq was result of an other dataSet from a xml datasource...
But I don't how integrate dynamically... I try to enter a parameter via the param tab, and create and expression : =First(Fields!idEq.Value, "EquipmentDataSet") but when i execute the query, the promter display <NULL>... So I don't know how to do and if it is possible ! I hope someone can help me ! Thank you !
Hi, i use this script that show me the size of each table and do the sum of all the table size.
SELECT X.[name], REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows], REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved], REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data], REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size], REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused] FROM (SELECT CAST(object_name(id) AS varchar(50)) AS [name], SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows], SUM(CONVERT(bigint, reserved)) * 8 AS reserved, SUM(CONVERT(bigint, dpages)) * 8 AS data, SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size, SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused FROM sysindexes WITH (NOLOCK) WHERE sysindexes.indid IN (0, 1, 255) AND sysindexes.id > 100 AND object_name(sysindexes.id) <> 'dtproperties' GROUP BY sysindexes.id WITH ROLLUP) AS X ORDER BY X.[name]
the problem is that the sum of all tables is not the same size when i make a full database backup. example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?
We have an application with replicated environment setup on sql server 2012 . Users will have a replica on their machines and they will replicate to the master database. It has 3 subscriptions subscribed to the publications on the master db.
1) We set up a replica(which uses sql server 2012) on a machine with no sql server on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 33gigs and ldf has grown to 41 gigs. I went to sql server management studion . Right click and checked the properties of the local database. over all size is around 84 gb with little empty free space available.
2) We set up a replica(which uses sql server 2012) on a machine with sql server 2008 on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.
3) We set up a replica(which uses sql server 2012) on a machine with sql server 2012 on it. We have dropped the local database and recreated the local db and did the initial synchronization using replmerge tool. The mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.
Why it is allocating the space differently? This is effecting our initial replica set up times.