Entries for qwert231

VS 2005 SQL report OutOfMemory in Preview [VS 2005 SQL report OutOfMemory in Preview]

qwert231 — Mon, 23 Nov 2009 22:24:24 GMT

I'm trying a report in VS 2005 and I get:

Exception of type 'System.OutOfMemoryException' was thrown.

I know the query is big, about 700-800k records. But shouldn't it be able to handle that?

What can I check/do to troubleshoot this?

in reply to VS 2005 SQL report OutOfMemory in Preview

Oracle query question [Oracle query question]

qwert231 — Mon, 16 Nov 2009 17:08:25 GMT

I'm adding an Oracle SQL query to an app that already handles one Oracle query correctly.

I have this query string:

SELECT RSIPLATE.DEALER.NAME AS BUSINESSNAME, RSIPLATE.DEALER.AREA_CODE || '-' || SUBSTR(RSIPLATE.DEALER.PHONE_NUMBER, 1, 3) || '-' || SUBSTR(RSIPLATE.DEALER.PHONE_NUMBER, 4, 4) AS BUSINESSPHONE, SUBSTR(RSIPLATE.ADDRESS.ADDRESS_LINE1, 1, 40) AS ADDRESSLINE1, '' AS ADDRESSLINE2, RSIPLATE.ADDRESS.CITY_NAME AS CITY, RSIPLATE.ADDRESS.STATE_CODE AS STATE, SUBSTR(RSIPLATE.ADDRESS.ZIP_CODE, 1, 5) AS POSTALCODE FROM RSIPLATE.DEALER INNER JOIN RSIPLATE.ADDRESS ON RSIPLATE.DEALER.ADDRESS_SET_ID = RSIPLATE.ADDRESS.ADDRESS_SET_ID WHERE RSIPLATE.DEALER.ACTIVE_IND = 'Y' AND RSIPLATE.DEALER.ACCOUNT_NUMBER NOT LIKE '%B%' AND RSIPLATE.ADDRESS.ADDRESS_TYPE = 2 AND SUBSTR(RSIPLATE.DEALER.ACCOUNT_NUMBER, 1, 2) = :CountyPK ORDER BY City, BusinessName

which works.

In my code I have this line:

ListingDataAdapter.SelectCommand.Parameters.Add(

New OracleParameter("CountyPK", CountyPK.ToString("00")))

I have a second query string:

SELECT rsiplate.OWNER.work_area_code||'-'||SUBSTR(rsiplate.OWNER.work_phone_number, 1, 3)||'-'||SUBSTR(rsiplate.OWNER.work_phone_number, 4, 4) AS BUSINESSPHONE, trim(rsiplate.OWNER.first_name||' '||rsiplate.OWNER.middle_name||' '||rsiplate.OWNER.last_name) AS BUSINESSNAME, rsiplate.ADDRESS.address_line1 AS ADDRESSLINE1, rsiplate.ADDRESS.address_line2 AS ADDRESSLINE2, rsiplate.ADDRESS.city_name AS CITY, rsiplate.ADDRESS.state_code AS STATE, SUBSTR(rsiplate.ADDRESS.zip_code, 1, 5) AS POSTALCODE FROM rsiplate.WATERCRAFT_MASTER, rsiplate.WATERCRAFT_CURRENT, rsiplate.WATERCRAFT_REGIST_CURRENT, rsiplate.OWNER, rsiplate.WATERCRAFT_OWNER_CURRENT, rsiplate.ADDRESS WHERE rsiplate.WATERCRAFT_MASTER.WATERCRAFT_ID = rsiplate.WATERCRAFT_CURRENT.WATERCRAFT_ID AND rsiplate.WATERCRAFT_MASTER.LF_WATERCRAFT_REGIST_ORDER = rsiplate.WATERCRAFT_REGIST_CURRENT.WATERCRAFT_REGIST_ORDER AND rsiplate.WATERCRAFT_MASTER.LF_WATERCRAFT_ORDER = rsiplate.WATERCRAFT_CURRENT.WATERCRAFT_ORDER AND rsiplate.WATERCRAFT_MASTER.watercraft_id = rsiplate.WATERCRAFT_REGIST_CURRENT.watercraft_id and rsiplate.WATERCRAFT_MASTER.watercraft_id = rsiplate.WATERCRAFT_OWNER_CURRENT.watercraft_id and rsiplate.WATERCRAFT_OWNER_CURRENT.owner_id = rsiplate.OWNER.owner_id and rsiplate.WATERCRAFT_MASTER.watercraft_owner_group = rsiplate.WATERCRAFT_OWNER_CURRENT.watercraft_owner_group and rsiplate.ADDRESS.address_set_id = rsiplate.OWNER.address_set_id and rsiplate.WATERCRAFT_MASTER.data_status_code = 1 and rsiplate.WATERCRAFT_OWNER_CURRENT.watercraft_owner_order = 1 and rsiplate.WATERCRAFT_REGIST_CURRENT.REGIST_EXPIRATION_DATE >= SYSDATE AND rsiplate.WATERCRAFT_CURRENT.WATERCRAFT_USE_CODE = 3 AND rsiplate.ADDRESS.COUNTY_NUMBER = :CountyPK GROUP BY rsiplate.OWNER.work_area_code, rsiplate.OWNER.work_phone_number, rsiplate.OWNER.first_name, rsiplate.OWNER.middle_name, rsiplate.OWNER.last_name, rsiplate.ADDRESS.address_line1, rsiplate.ADDRESS.address_line2, rsiplate.ADDRESS.city_name, rsiplate.ADDRESS.state_code, rsiplate.ADDRESS.zip_code;

which uses the same code line. Yet I get this message:

ORA-00911: invalid character

What am I doing wrong?

in reply to Oracle query question

Can I deploy reports from VS2008 onto SSRS 2005? [Can I deploy reports from VS2008 onto SSRS 2005?]

qwert231 — Fri, 30 Oct 2009 17:25:54 GMT

We have an SSRS 2005 server currently running. We just got VS 2008. Is it possible to have VS 2008 target reports so they will work on 2005 servers?

in reply to Can I deploy reports from VS2008 onto SSRS 2005?

SQL Report Filter only allows ANDs [SQL Report Filter only allows ANDs]

qwert231 — Thu, 08 Oct 2009 18:17:30 GMT

I want to do a simple filter on a report,

(Field1 <> Field2) or (Field3 <> Field4)

So, if the first set or the second set are not equal, show the results. (We may have the first set be equal, but not the second set, and we want to see that.)

So, in my table's Filters dialog, I do

Expression: Field1

Operator: !=

Value: Field2

Expression: Field3

Operator: !=

Value: Field4

the And/Or column for the first filter, shows 'and', and I cannot change it.

How do I get the filter I want?

in reply to SQL Report Filter only allows ANDs

OUTER APPLY syntax error [OUTER APPLY syntax error]

qwert231 — Wed, 07 Oct 2009 14:21:24 GMT

I was given a report to use with our database, and I'm getting errors. The report query uses an OUTER APPLY, and this is the first I've worked with them.

Here's the query:

DECLARE @begin_process_date datetime, @end_process_date datetime

SELECT @begin_process_date = '9/1/2009', @end_process_date = '9/30/2009'

Select
Q.*,
lpc.counter
From
(
Select
t.location_id,
(count(td.tran_detail_id) * 1.1) as adjusted_sales,
case
  when (count(td.tran_detail_id) * 1.1) <= 220 then 1
  else CEILING((count(td.tran_detail_id) * 1.1)/200) end as label_count,
so.old_agent_id,
UPPER(so.sales_outlet_name) as sales_outlet_name,
UPPER(a.raw_address_1) as raw_address_1,
a.raw_address_2,
a.raw_city,
a.raw_state,
a.raw_zip,
'USA' as country,
'GROUND' as pfbc_service,
'PREPAID' as pfbc_option,
'PACKAGE' as pfbc_package,
'31' as pfbc_weight
From
ALSReplicator.TB_ORA_tran t
INNER JOIN ALSReplicator.TB_ORA_tran_detail td
  ON t.tran_ref_id = td.tran_ref_id
INNER JOIN ALSReplicator.TB_ORA_vw_repl_location l
  ON t.location_id = l.location_id,
ALSReplicator.address a,
ALSReplicator.address_usage_index aui,
ALSReplicator.sales_outlet so,
ALSReplicator.terminal tt
Where
t.tran_status = 'C' and
t.test_tran = 'N' and
td.tran_status in ('C','I') and
t.process_date >= @begin_process_date and
t.process_date < dateadd(dd, 1, @end_process_date)and
(td.priv_code between 101 and 134) and
Right( Convert( varchar, Convert( Decimal(10, 0 ), t.location_id ) ), 5 ) = tt.terminal_key and
a.address_key = aui.address_key and
aui.entity_type = 'SLSO' and
aui.entity_type = 'SLSO' and
aui.address_type = 'STRT' and
so.sales_outlet_key = aui.entity_key and
aui.address_key = a.address_key and
so.sales_outlet_key = tt.sales_outlet_key
-- and
-- t.location_id not in (@test_lid)
Group By t.location_id, so.old_agent_id, so.sales_outlet_name, a.raw_address_1,
  a.raw_address_2, a.raw_city, a.raw_state, a.raw_zip) AS Q
OUTER APPLY udf_GetLabelPrintCounter(Q.location_id, Q.label_count) AS lpc   ' Line 57 <-----
Where Q.location_id = lpc.LocID

I get the message:

Msg 102, Level 15, State 1, Line 57

Incorrect syntax near '.'.

in reply to OUTER APPLY syntax error

textbox.focus() doesn't work in Ajax code behind [textbox.focus() doesn't work in Ajax code behind]

qwert231 — Mon, 28 Sep 2009 17:46:19 GMT

I have a section of textboxes and drop downs in an UpdatePanel.

textbox2 has a 'TextChanged' event that correctly updates dropdown3. I would like the cursor to then move to textbox4. So, in the same event handler for the TextChanged I put textbox4.Focus(). When the update happens, the cursor is not in any box. However, if I hit the tab key, the cursor appears in textbox1 the first input control in the update panel.

How can I get the cursor to correctly appear in textbox4?

in reply to textbox.focus() doesn't work in Ajax code behind

How to multi-value param like SQL Reports in a query? [How to multi-value param like SQL Reports in a query?]

qwert231 — Tue, 22 Sep 2009 13:52:47 GMT

I trying to test some queries and I'd like to use a multi-value parameter like you can in SQL reports. You can have parameters that offer a selection, and some that use data from a sub-query to fill the parameter. How would I do that in a stored procedure?

Example:

SELECT Field1, Field2 FROM Table 1

WHERE Field1 IN (@param1)

AND Field2 IN (@param2)

Where @param1 is a selection of values and @param2 is a collection of values from the result of a sub query.

For @param1, I could manually put in {'value1', 'value2', 'value3'} or something like that. But I'd like to pass it into the stored procedure.

Is there a site online w/ a good example of this?

in reply to How to multi-value param like SQL Reports in a query?

Sort a string column in dataview numerically [Sort a string column in dataview numerically]

qwert231 — Thu, 17 Sep 2009 18:07:46 GMT

Working on an app where the LogDate column in the dataset is string. We load it into a DataView, filter, and pass it to a SQL report. If I sort by LogDate I get an ordering of 10,11,12,6,7,8,9...

Here's our DataView declaration:

Dim dailyTimeLogDV As New DataView(Me._timeActivityReportsDataSet.DailyLogShiftTimes, _dateExpr, "LogDate", DataViewRowState.CurrentRows)

in reply to Sort a string column in dataview numerically

Can I use a System.Array for a SQL Report? [Can I use a System.Array for a SQL Report?]

qwert231 — Fri, 28 Aug 2009 15:47:30 GMT

I'm modifying an ASP .Net page that has a grid object of records. The user can select (with check boxes) records from the grid.

The grid provides a System.Array object containing the IDs of the records. Is there a way for me to pass that into a SQL Report so the report can pull only those records?

in reply to Can I use a System.Array for a SQL Report?

Converting 2 columns of text to a datetime in SQL 2005 [Converting 2 columns of text to a datetime in SQL 2005]

qwert231 — Fri, 21 Aug 2009 16:23:20 GMT

First, I'm using data as it's given to me. I can't change the input or data structure, only my query out.

I have 3 fields, FeatureDate, time_start, and time_start_am_pm. FeatureDate is a datetime column, but has only dates, no times. The time is time_start, a varchar column, with the time formated as #### (ex. 7:00 PM would be 0700) and the time_start_am_pm column having either AM or PM.

I'm trying to join them all together into one datetime field.

So far, I've tried this, but it doesn't seem to work.

CONVERT(datetime, CAST(CONVERT(varchar(4), DATEPART(mm, FeatureDate)) + '-'
     + CONVERT(varchar(2), DATEPART(dd, FeatureDate)) + '-'
     + CONVERT(varchar(4), DATEPART(yyyy, FeatureDate)) + ' '
     + CONVERT(char(2), LEFT(time_start,2)) + ':'
     + CONVERT(char(2), RIGHT(time_start,2)) + ' '
     + time_start_am_pm
     AS varchar(20)) ) AS DateTimeStart

Any suggestions?

in reply to Converting 2 columns of text to a datetime in SQL 2005

How can I stop a SQLDataSource when page.isvalid is false? [How can I stop a SQLDataSource when page.isvalid is false?]

qwert231 — Fri, 31 Jul 2009 15:39:35 GMT

I have a DataList bound to a SQLDataSource that uses a textbox for a select command parameter.

I have 2 validatorsr on the text box (a required, and a custom). I'm testing the form with failing values, and the Page.IsValid is getting set to false correctly. However, the SQLDataSource is still trying to fire, and I get an unhandled exception because the textbox value is bad.

How can I resolve this?

in reply to How can I stop a SQLDataSource when page.isvalid is false?

Reporting Services stopped working after account password change [Reporting Services stopped working after account password change]

qwert231 — Tue, 14 Jul 2009 12:55:07 GMT

This should be simple, but it's not. I've had our network admin and I working on this for now the second day.

SQL services was working. Over the weekend, the admin, following network security procedures, changed the password of the Domain account used by SQL Reporting Service. He then went into the SQL Report Service and changed the password settings, and restarted the server. SQL Reporting Service is running and no errors show up in the Event Log.

I came in, and can navigate to the Report Manager site, and view lists of reports, but when I click on a report, I get this error message:

Logon failed. (rsLogonFailed)
- For more information about this error navigate to the report server on the local server machine, or enable remote errors

I do not get the standard report control so I don't see the parameter prompts or any other parts of the control.

We have gone through Reporting Services Configuration Manager and updated the password wherever the Domain account is used. And then restarted Reporting Services, but I continue to receive the above message. The Domain Account being used hasn't changed, only the password has changed.

What are we missing?

Where can I 'enable remote errors'?

in reply to Reporting Services stopped working after account password change

DateTimePicker doesn't dispose properly. [DateTimePicker doesn't dispose properly.]

qwert231 — Fri, 10 Jul 2009 19:16:24 GMT

VB.Net 2.0

In our MDIChild form we have a CheckChanged method on a dateTime Picker:

Private Sub dateTimePickerLogDate_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dateTimePickerLogDate.ValueChanged
CheckTimeExists()
End Sub

CheckTimeExists() looks at the data to see if we've entered data for that data. Then at the end, it does this:

        If timelogExists Then 'Enter edit mode if the time log exists for the selected date.
            If MsgBox("Time & Activity details exist for the selected date. Select 'Yes' to update the information or 'No' to close the form.", MsgBoxStyle.YesNo, "Time & Activity report") = MsgBoxResult.No Then
                CloseForm()
                Exit Sub
            End If
        Else
            Me._dailyTimeLogId = CreateNewDailyTimeLogRow()
        End If

LoadData()
End Sub

But the DateTimePicker doesn't disappear.

(BTW, I hate the formatting of the Channel 9 post control.)

in reply to DateTimePicker doesn't dispose properly.

Dataset double field doesn't allow Nulls? [Dataset double field doesn't allow Nulls?]

qwert231 — Wed, 08 Jul 2009 20:09:32 GMT

We have a dataset that has a table w/ some double columns. Here's some of the code:

When the column is null it errors. In the designer, I cannot set NullValue to anything other than 'Throw exception'. Why is that?

in reply to Dataset double field doesn't allow Nulls?

Use a child field in a strongly typed dataset in a datagridview column [Use a child field in a strongly typed dataset in a datagridview column]

qwert231 — Wed, 01 Jul 2009 13:43:50 GMT

VB .Net 2.0 (Using VS 2008)

I have a DataGridView, 'IncidentAddendumDataGridView' that is bound to 'IncidentAddendumBindingSource'.

My binding source points to these elements:

DataSource: IncidentDataSet

DataMember: IncidentAddendum (A table in 'IncidentDataSet')

There is a relationship in the dataset called AddendumType_IncidentAddendum that connects IncidentAddendum to AddendumType.

I want to use the 'Name' field of AddendumType as a column in 'IncidentAddendumDataGridView'. Do I need to point the binding source to the relationship?

in reply to Use a child field in a strongly typed dataset in a datagridview column

DataTable expression using methods. [DataTable expression using methods.]

qwert231 — Mon, 29 Jun 2009 20:20:15 GMT

I'm looking to have a datatable expression column that uses a method in my code. So I would have something like this:

myDataSet.myDataTable.Columns.Add("ExpColumn", Type.GetType("System.String"), methodFromCode("Column1FromDataTable"))

Where can I find the correct syntax for that?

in reply to DataTable expression using methods.

SQL 08 Express wants VS 08 SP1, but I installed SP1 [SQL 08 Express wants VS 08 SP1, but I installed SP1]

qwert231 — Tue, 23 Jun 2009 14:40:22 GMT

K, so the office I'm working in sprung for VS 2008 Pro, but not SQL 2008. (Still using 2005). But I want to do some SQL CE stuff on a project. I thought I could load SQL 2008 Express, just the management/dev stuff, and be good to go. I get to the part where SQL 08 setup does it's installation rules, and it says the Previous release of MS VS 2008 needs to be SP1. Okay, I installed SP1, rebooted (the SP install told me to) and still I get the rule issue. When I start VS 2008 and do help about, I get this:

in reply to SQL 08 Express wants VS 08 SP1, but I installed SP1

FileStream writing doesn't work correcly, no errors [FileStream writing doesn't work correcly, no errors]

qwert231 — Wed, 10 Jun 2009 17:38:39 GMT

I'm writing an object to file as a serialized xml file like this:

ser.Serialize(fs, settings)

fs.Flush()

fs.Close()

Dim fs As FileStream = New FileStream("settings.xml", FileMode.Create)

But the file never changes. I don't get any errors. Is this because I'm in the debug folder?

in reply to FileStream writing doesn't work correcly, no errors

How to start/stop windows service from a windows form [How to start/stop windows service from a windows form]

qwert231 — Wed, 10 Jun 2009 16:44:52 GMT

I'd like to have my windows form start and stop my windows service. I've seen it done, and I think I might have done it years ago, but I can't seem to find the correct pattern.

in reply to How to start/stop windows service from a windows form

Refresh a DataGridView bound to an object [Refresh a DataGridView bound to an object]

qwert231 — Wed, 10 Jun 2009 16:16:00 GMT

I have a class (classCollection) that contains an array of an object (classObject) with 3 properties. The DataGridView is bound to them properly. I can see a row for each classObject with the properties showing.

If I add a classObject item to the array, and do DataGridView.Refresh, the datagridview does not show the object, even tho the collection has it. What's up?

in reply to Refresh a DataGridView bound to an object

XML Spreadsheet web page opens in IE, not Excel [XML Spreadsheet web page opens in IE, not Excel]

qwert231 — Wed, 10 Jun 2009 13:21:02 GMT

We have a page that generates an XML spreadsheet for the user. 99% of the users have this open into Excel. But we have one user who sees it load directly in Internet Explorer. I'm sure it's some settings on her system, but I'm not sure where to look. I wasn't sure what to google on this.

in reply to XML Spreadsheet web page opens in IE, not Excel

Do I still need Frontpage Extensions? [Do I still need Frontpage Extensions?]

qwert231 — Mon, 08 Jun 2009 18:49:07 GMT

I read on wikipedia that some ClickOnce applications use Frontpage Extensions, but also see that it is 'End of Lifed'. Most of our site is .Net (2.0 or greater). Do I still need Frontpage Extensions enabled on our Intranet server?

in reply to Do I still need Frontpage Extensions?

Using a list in a list in SQL Reports 2005 [Using a list in a list in SQL Reports 2005]

qwert231 — Wed, 27 May 2009 16:19:06 GMT

I'm creating a report that doesn't follow the standard table layout, so I have my elements laid out in a list control. The report is bound to a dataset object, which has child tables in the dataset. I would like to use one of these child tables as a list.

For instance, my main table is IncidentVessel, and the child table is Activities, I want my report to show the Activities in a list, bulleted or otherwise. I thought that by putting a list object inside my main list object, I could do this. I set it up with grouping information on the IncidentVesselID, but I keep getting the message that 'list2' has no group expressions defined for it.

Any thoughts?

in reply to Using a list in a list in SQL Reports 2005

Why does 'Save my password' do nothing? [Why does 'Save my password' do nothing?]

qwert231 — Mon, 11 May 2009 20:21:21 GMT

I'm in a SQL report project in Visual Studio 2005, I set my share connection, and Check 'Save my password', EVERY TIME I START THE PROJECT!!!!!!

Why does this forget the password?!?!

in reply to Why does 'Save my password' do nothing?

SSRS access from a separate server no longer works [SSRS access from a separate server no longer works]

qwert231 — Mon, 04 May 2009 17:15:12 GMT

We had SQL reports on our Dev server, and they could be reached by my asp.net pages from both the Dev server and the production server.

The dev server went down, and had to be rebuilt. (This was done by our server Admin, not me.) Now we cannot access the reports from another server like we used to do. The error we receive is 'The request failed with HTTP status 401: Unauthorized.'

I believe we had a Role Assignment of Browser set up that allowed the remoteserver access. However, if I now try to create a Role assignment for the ASPNET account, I can only do so for the local ASPNET account, not the remote server.

I did not have to put embeded credentials in the code before, and would like to not have to now. Any thoughts?

in reply to SSRS access from a separate server no longer works