Exception handling

it gives error while calling a sql stored procedure as "INPUT STRING WAS NOT IN A CORRECT FORMAT". I am providing the code here.

publicvoid get_issid(string cse_email,string tech_email,string subject,string issue_details,string response,string solv_date,outint issid)

{

// Establish Connection

SqlConnection oConnection = GetConnection();

// build the command

SqlCommand oCommand =newSqlCommand("get_issid", oConnection);

oCommand.CommandType =CommandType.StoredProcedure;

// Parameters

SqlParameter paracse_email =newSqlParameter("@.cse_email",SqlDbType.VarChar, 50);

paracse_email.Value =cse_email;

oCommand.Parameters.Add(paracse_email);

SqlParameter paratech_email =newSqlParameter("@.tech_email",SqlDbType.VarChar,50);

paratech_email.Value = cse_email;

oCommand.Parameters.Add(paratech_email);

SqlParameter parasubject =newSqlParameter("@.subject",SqlDbType.VarChar, 50);

parasubject.Value = subject;

oCommand.Parameters.Add(parasubject);

SqlParameter paraissue_details =newSqlParameter("@.issue_details",SqlDbType.VarChar, 500);

paraissue_details.Value = issue_details;

oCommand.Parameters.Add(paraissue_details);

SqlParameter pararesponse =newSqlParameter("@.response",SqlDbType.VarChar, 500);

pararesponse.Value = response;

oCommand.Parameters.Add(pararesponse);

SqlParameter parasolv_date =newSqlParameter("@.solv_date",SqlDbType.DateTime);

parasolv_date.Value = solv_date;

oCommand.Parameters.Add(parasolv_date);

SqlParameter paraissid =newSqlParameter("@.issid",SqlDbType.Int);paraissid.Direction =ParameterDirection.Output;

oCommand.Parameters.Add(paraissid);

try

{

oConnection.Open();

oCommand.ExecuteNonQuery();

issid =int.Parse(paraissid.Value.ToString());

}

catch (Exception oException)

{

throw oException;

}

finally

{

oConnection.Close();

}

}

the stored procedure is:

create proc [dbo].[get_issid](@.tech_emailvarchar(50), @.cse_emailvarchar(50),@.subjectvarchar(50),@.issue_detailsvarchar(500),@.responsevarchar(500),@.solv_datedatetime, @.issidintoutput)

as

select @.issid=tech_response.issue_idfrom tech_response,issue_detailswhere tech_response.tech_email=@.tech_emailand tech_response.cse_email=@.cse_emailand tech_response.subject=@.subjectand tech_response.issue_details=@.issue_detailsand response=@.responseand solv_date=@.solv_dateand tech_response.issue_id=issue_details.issue_id

requested to help in this

Use ExecuteScalar or ExecuteReader .

ExecuteNonQuery can be used only on DDL statements such as Insert and Delete statements.

|||

Hi,

Thank u for your reply. but the error is coming again same as earlier.

pls help me in this regard.

your amibly,

nagireddy

Excel to MS SQL Server via OLE - Date problem

Background:
I have Excel files that are not in a good 'data' format so I'm planning to
migrate the data out of Excel into MS SQL server. Since the data is spread
all over the place in the current Excel files, I'll need to write code to
grab the right data, build a select statement, and push the data into SQL
Server.
Current status:
I have working code that pushes the stuff I want into SQL server. Woot!
Problem:
The excel files contain date and time fields. The date cells have values of
39000, etc and the time fields are decimals from 0 to 1. All pretty standard.
When I grab a date field using the following OLE code:
TestDate = Cdat(xlSheet2.Range("D8").Value)
And then display it back, I see an actual date.
When I do the same thing with a time field, I just see the decimal number.
Problem is that when they get to SQL server,
Dates are all: 1/1/1900 12:00:00 AM
Times are: 1/1/1900 with what appears to be the right time.
The SQL server fields are Datetime.
I'm stuck - any suggestions?
TIA.
Doug
ps - I've cross posted in the Excel group
Nothing like posting a question to make the answer clear...
My OLE code buids a string that becomes the insert query on the SQL server
side.
The string looks something like:
Insert into tableX(Field1, Field2, Field3) Values ('StringValue',
numericValue)
Two things.
First: I needed to add the date value and time value from Excel into a
single number.
Second: rather than submit the value as a value, I send it as a string ala
Insert into tableX(Field1, Field2) Values ('MyString', '12/31/2008 08:00:00
AM')
poof, works great.
Hope this is of value to others.
Doug
"Doug_F" wrote:

> Background:
> I have Excel files that are not in a good 'data' format so I'm planning to
> migrate the data out of Excel into MS SQL server. Since the data is spread
> all over the place in the current Excel files, I'll need to write code to
> grab the right data, build a select statement, and push the data into SQL
> Server.
> Current status:
> I have working code that pushes the stuff I want into SQL server. Woot!
> Problem:
> The excel files contain date and time fields. The date cells have values of
> 39000, etc and the time fields are decimals from 0 to 1. All pretty standard.
> When I grab a date field using the following OLE code:
> TestDate = Cdat(xlSheet2.Range("D8").Value)
> And then display it back, I see an actual date.
> When I do the same thing with a time field, I just see the decimal number.
> Problem is that when they get to SQL server,
> Dates are all: 1/1/1900 12:00:00 AM
> Times are: 1/1/1900 with what appears to be the right time.
> The SQL server fields are Datetime.
> I'm stuck - any suggestions?
> TIA.
> Doug
> ps - I've cross posted in the Excel group

Excel to MS SQL Server via OLE - Date problem

Background:
I have Excel files that are not in a good 'data' format so I'm planning to
migrate the data out of Excel into MS SQL server. Since the data is spread
all over the place in the current Excel files, I'll need to write code to
grab the right data, build a select statement, and push the data into SQL
Server.
Current status:
I have working code that pushes the stuff I want into SQL server. Woot!
Problem:
The excel files contain date and time fields. The date cells have values of
39000, etc and the time fields are decimals from 0 to 1. All pretty standard.
When I grab a date field using the following OLE code:
TestDate = Cdat(xlSheet2.Range("D8").Value)
And then display it back, I see an actual date.
When I do the same thing with a time field, I just see the decimal number.
Problem is that when they get to SQL server,
Dates are all: 1/1/1900 12:00:00 AM
Times are: 1/1/1900 with what appears to be the right time.
The SQL server fields are Datetime.
I'm stuck - any suggestions?
TIA.
Doug
ps - I've cross posted in the Excel groupNothing like posting a question to make the answer clear...
My OLE code buids a string that becomes the insert query on the SQL server
side.
The string looks something like:
Insert into tableX(Field1, Field2, Field3) Values ('StringValue',
numericValue)
Two things.
First: I needed to add the date value and time value from Excel into a
single number.
Second: rather than submit the value as a value, I send it as a string ala
Insert into tableX(Field1, Field2) Values ('MyString', '12/31/2008 08:00:00
AM')
poof, works great.
Hope this is of value to others.
Doug
"Doug_F" wrote:
> Background:
> I have Excel files that are not in a good 'data' format so I'm planning to
> migrate the data out of Excel into MS SQL server. Since the data is spread
> all over the place in the current Excel files, I'll need to write code to
> grab the right data, build a select statement, and push the data into SQL
> Server.
> Current status:
> I have working code that pushes the stuff I want into SQL server. Woot!
> Problem:
> The excel files contain date and time fields. The date cells have values of
> 39000, etc and the time fields are decimals from 0 to 1. All pretty standard.
> When I grab a date field using the following OLE code:
> TestDate = Cdat(xlSheet2.Range("D8").Value)
> And then display it back, I see an actual date.
> When I do the same thing with a time field, I just see the decimal number.
> Problem is that when they get to SQL server,
> Dates are all: 1/1/1900 12:00:00 AM
> Times are: 1/1/1900 with what appears to be the right time.
> The SQL server fields are Datetime.
> I'm stuck - any suggestions?
> TIA.
> Doug
> ps - I've cross posted in the Excel group

Excel rendering crash with parameters. Ok as HTML.

Hi,
I am trying to render report I have with a matrix on it using the web
service.
It works fine if I render to excel format with no parameters provided
to the report.
It also works fine if I use the exact same code and just change the
render format to "HTML4.0" instead of "EXCEL".
As the error only happens with Excel specified and parameters provided
could this be a bug?
I had a search in this group and didn't find anyone else reporting
this so I may well be doing something silly.
Any advice would be apreciated, code and the error message returned in
the SoapException.Detail.Text follow. Please let me know if you need
any more details about what I'm doing
Thanks
Code:
string strReportName = "/MyReport";
string Hist = null;
XL3Addin.ReportService.DataSourceCredentials[] Creds = new
XL3Addin.ReportService.DataSourceCredentials[0];
string ShowHide = "";
string Enc = "";
string Mime = "";
string[] Streams;
XL3Addin.ReportService.ParameterValue[] ParamsOut;
XL3Addin.ReportService.Warning[] Warns;
//get the parameters out of a datagrid I have
XL3Addin.ReportService.ParameterValue[] ParamsIn = new
XL3Addin.ReportService.ParameterValue[this.ParamterTable.Rows.Count];
for(int i=0; i<this.ParamterTable.Rows.Count; i++)
{
ParamsIn[i] = new XL3Addin.ReportService.ParameterValue();
ParamsIn[i].Name = this.ParamterTable.Rows[i].ItemArray[0].ToString();
ParamsIn[i].Value = this.ParamterTable.Rows[i].ItemArray[2].ToString();
ParamsIn[i].Label = this.ParamterTable.Rows[i].ItemArray[1].ToString();
}
byte[] rep = rs.Render(strReportName, RenderFormat, Hist, DeviceInfo,
ParamsIn, Creds, ShowHide, out Enc, out Mime, out ParamsOut, out
Warns, out Streams);
Works if:
string RenderFormat = "EXCEL";
string DeviceInfo = "<DeviceInfo><OmitDocumentMap>False</OmitDocumentMap><OmitFormulas>False</OmitFormulas><RemoveSpace>0.125in</RemoveSpace></DeviceInfo>";
Is changed to:
string RenderFormat = "HTML4.0";
string DeviceInfo = null;
Error Message :
rrRenderingError
400
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was thrown.
http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings&EvtID=rrRenderingError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=8.00
Microsoft SQL Server Reporting Services
8.00.878.00
127
OsIndependent
1033
Microsoft.ReportingServices.Processing
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was thrown.
Microsoft.ReportingServices.ExcelRendering
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was thrown.
Microsoft.ReportingServices.ExcelRendering
Object reference not set to an instance of an object.Hmm, no takers yet ;)
Will it help if i post my rdl definition?
It is an OLAP grid with drill down and filters.
Am an trying to render with parameters of "CA" and "Drink" for Param0
and Param1 respectively.
The HTML file is produced, but still .Render() with Excel as the type
crashes.
I hope posting this XML doesn't mess the board around too much.
Any help gratefully received
Thanks
Colin
<?xml version="1.0"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="<GridSpacing">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"><GridSpacing
xmlns="0.25cm</GridSpacing><RightMargin>1in</RightMargin><Body><ReportItems><Matrix">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">0.25cm</GridSpacing><RightMargin>1in</RightMargin><Body><ReportItems><Matrix
Name="Martix0"><Corner><ReportItems><Textbox
Name="TextBox0"><Style><BorderStyle><Default>Solid</Default><Top>None</Top><Left>None</Left></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><FontSize>26pt</FontSize><Color>DarkBlue</Color></Style><Value>Test
with filters</Value><CanGrow>true</CanGrow><ZIndex>0</ZIndex></Textbox></ReportItems></Corner><Style></Style><MatrixRows><MatrixRow><MatrixCells><MatrixCell><ReportItems><Textbox
Name="TextBox1"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><BackgroundColor>AliceBlue</BackgroundColor><TextAlign>Right</TextAlign><Format>C0</Format></Style><Value>=Sum(Fields!Measure0.Value)</Value><CanGrow>true</CanGrow><ZIndex
1</ZIndex></Textbox></ReportItems></MatrixCell></MatrixCells><Height>0.25in</Height></MatrixRow></MatrixRows><MatrixColumns><MatrixColumn><Width>0.875in</Width></MatrixColumn></MatrixColumns><DataSetName>DataQuery</DataSetName><ColumnGroupings><ColumnGrouping><DynamicColumns><Grouping
Name="ColGrp0"><GroupExpressions><GroupExpression>=Fields!ColLvl0.Value</GroupExpression></GroupExpressions></Grouping><ReportItems><Textbox
Name="ColTxtBox0"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>White</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Center</TextAlign></Style><Value>=Fields!ColLvl0.Value</Value><CanGrow>true</CanGrow><ZIndex>2
/ZIndex></Textbox></ReportItems></DynamicColumns><Height>0.25in</Height></ColumnGrouping><ColumnGrouping><DynamicColumns><Grouping
Name="ColGrp1"><GroupExpressions><GroupExpression>=Fields!ColLvl1.Value</GroupExpression></GroupExpressions></Grouping><Visibility><ToggleItem>ColTxtBox0</ToggleItem><Hidden>true</Hidden></Visibility><ReportItems><Textbox
Name="ColTxtBox1"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>White</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Center</TextAlign></Style><Value>=Fields!ColLvl1.Value</Value><CanGrow>true</CanGrow><ZIndex>3
/ZIndex></Textbox></ReportItems></DynamicColumns><Height>0.25in</Height></ColumnGrouping><ColumnGrouping><DynamicColumns><Grouping
Name="ColGrp2"><GroupExpressions><GroupExpression>=Fields!ColLvl2.Value</GroupExpression></GroupExpressions></Grouping><Visibility><ToggleItem>ColTxtBox1</ToggleItem><Hidden>true</Hidden></Visibility><ReportItems><Textbox
Name="ColTxtBox2"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>White</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Center</TextAlign></Style><Value>=Fields!ColLvl2.Value</Value><CanGrow>true</CanGrow><ZIndex>4
/ZIndex></Textbox></ReportItems></DynamicColumns><Height>0.25in</Height></ColumnGrouping><ColumnGrouping><DynamicColumns><Grouping
Name="ColGrp3"><GroupExpressions><GroupExpression>=Fields!ColLvl3.Value</GroupExpression></GroupExpressions></Grouping><Visibility><ToggleItem>ColTxtBox2</ToggleItem><Hidden>true</Hidden></Visibility><ReportItems><Textbox
Name="ColTxtBox3"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>White</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Center</TextAlign></Style><Value>=Fields!ColLvl3.Value</Value><CanGrow>true</CanGrow><ZIndex>5
/ZIndex></Textbox></ReportItems></DynamicColumns><Height>0.25in</Height></ColumnGrouping><ColumnGrouping><DynamicColumns><Grouping
Name="ColGrp4"><GroupExpressions><GroupExpression>=Fields!ColLvl4.Value</GroupExpression></GroupExpressions></Grouping><Visibility><ToggleItem>ColTxtBox3</ToggleItem><Hidden>true</Hidden></Visibility><ReportItems><Textbox
Name="ColTxtBox4"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>White</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Center</TextAlign></Style><Value>=Fields!ColLvl4.Value</Value><CanGrow>true</CanGrow><ZIndex>6
/ZIndex></Textbox></ReportItems></DynamicColumns><Height>0.25in</Height></ColumnGrouping><ColumnGrouping><Height>0.25in</Height><StaticColumns><StaticColumn><ReportItems><Textbox
Name="TextBox7"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>White</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Center</TextAlign></Style><Value>Unit
Sales</Value><CanGrow>true</CanGrow><ZIndex>7</ZIndex></Textbox></ReportItems></StaticColumn></StaticColumns></ColumnGrouping></ColumnGroupings><Filters><Filter><FilterExpression>=Fields!RowLvl1.Value</FilterExpression><Operator>Equal</Operator><FilterValues><FilterValue>=Parameters!Param0.Value</FilterValue></FilterValues></Filter><Filter><FilterExpression>=Fields!ColLvl0.Value</FilterExpression>
Operator>Equal</Operator><FilterValues><FilterValue>=Parameters!Param1.Value</FilterValue></FilterValues></Filter></Filters><RowGroupings><RowGrouping><DynamicRows><Grouping
Name="RowGrp0"><GroupExpressions><GroupExpression>=Fields!RowLvl0.Value</GroupExpression></GroupExpressions></Grouping><ReportItems><Textbox
Name="RowTxtBox0"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>White</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Left</TextAlign></Style><Value>=Fields!RowLvl0.Value</Value><CanGrow>true</CanGrow><ZIndex>8</
Index></Textbox></ReportItems><Subtotal><ReportItems><Textbox
Name="TextBox9"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>SkyBlue</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Left</TextAlign></Style><Value>Subtotal</Value><CanGrow>true</CanGrow><ZIndex>9</ZIndex></Text
ox></ReportItems></Subtotal></DynamicRows><Width>0.875in</Width></RowGrouping><RowGrouping><DynamicRows><Grouping
Name="RowGrp1"><GroupExpressions><GroupExpression>=Fields!RowLvl1.Value</GroupExpression></GroupExpressions></Grouping><Visibility><ToggleItem>RowTxtBox0</ToggleItem><Hidden>true</Hidden></Visibility><ReportItems><Textbox
Name="RowTxtBox1"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>White</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Left</TextAlign></Style><Value>=Fields!RowLvl1.Value</Value><CanGrow>true</CanGrow><ZIndex>10<
ZIndex></Textbox></ReportItems><Subtotal><ReportItems><Textbox
Name="TextBox11"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>SkyBlue</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Left</TextAlign></Style><Value>Subtotal</Value><CanGrow>true</CanGrow><ZIndex>11</ZIndex></Te
tbox></ReportItems></Subtotal></DynamicRows><Width>0.875in</Width></RowGrouping><RowGrouping><DynamicRows><Grouping
Name="RowGrp2"><GroupExpressions><GroupExpression>=Fields!RowLvl2.Value</GroupExpression></GroupExpressions></Grouping><Visibility><ToggleItem>RowTxtBox1</ToggleItem><Hidden>true</Hidden></Visibility><ReportItems><Textbox
Name="RowTxtBox2"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>White</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Left</TextAlign></Style><Value>=Fields!RowLvl2.Value</Value><CanGrow>true</CanGrow><ZIndex>12<
ZIndex></Textbox></ReportItems><Subtotal><ReportItems><Textbox
Name="TextBox13"><Style><BorderStyle><Default>Solid</Default></BorderStyle><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><FontFamily>Tahoma</FontFamily><Color>SkyBlue</Color><BackgroundColor>SteelBlue</BackgroundColor><TextAlign>Left</TextAlign></Style><Value>Subtotal</Value><CanGrow>true</CanGrow><ZIndex>13</ZIndex></Te
tbox></ReportItems></Subtotal></DynamicRows><Width>0.875in</Width></RowGrouping></RowGroupings></Matrix></ReportItems><Style></Style><Height>1.25in</Height><ColumnSpacing>1cm</ColumnSpacing></Body><TopMargin>1in</TopMargin><DataSources><DataSource
Name="DataSource1"><DataSourceID
xmlns="B7BD158F-94EE-41E4-BA21-CDC54DBA3983</DataSourceID><ConnectionProperties><DataProvider>OLEDB</DataProvider><ConnectString>Provider=MSOLAP.2;Client">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">B7BD158F-94EE-41E4-BA21-CDC54DBA3983</DataSourceID><ConnectionProperties><DataProvider>OLEDB</DataProvider><ConnectString>Provider=MSOLAP.2;Client
Cache Size=25;Data Source=iis-covertonxp;Initial Catalog=FoodMart
2000;Auto Synch Period=10000</ConnectString><IntegratedSecurity>true</IntegratedSecurity></ConnectionProperties></DataSource></DataSources><Width>7.125in</Width><DataSets><DataSet
Name="DataQuery"><Fields><Field
Name="ColLvl0"><DataField>[Product].[Product
Family].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="ColLvl1"><DataField>[Product].[Product
Department].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="ColLvl2"><DataField>[Product].[Product
Category].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="ColLvl3"><DataField>[Product].[Product
Subcategory].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="ColLvl4"><DataField>[Product].[Brand
Name].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="RowLvl0"><DataField>[Customers].[Country].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="RowLvl1"><DataField>[Customers].[State
Province].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="RowLvl2"><DataField>[Customers].[City].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="Measure0"><DataField>[Measures].[Unit
Sales]</DataField><TypeName
xmlns="System.Object</TypeName></Field></Fields><Query><DataSourceName>DataSource1</DataSourceName><CommandText>">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.Object</TypeName></Field></Fields><Query><DataSourceName>DataSource1</DataSourceName><CommandText>
SELECT
{[Measures].[Unit Sales]} on columns ,
{[Customers].[City].members} on rows ,
{[Product].[Brand Name].members} on pages
FROM
[Sales]
CELL PROPERTIES
VALUE, FORMATTED_VALUE, FORMAT_STRING, FORE_COLOR, BACK_COLOR,
FONT_FLAGS</CommandText></Query></DataSet><DataSet
Name="ParameterQuery0"><Fields><Field
Name="ParaLevelField0"><DataField>[Customers].[State
Province].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="ParamNullField0"><DataField>[Measures].[NullColumn]</DataField><TypeName
xmlns="System.Object</TypeName></Field></Fields><Query><DataSourceName>DataSource1</DataSourceName><CommandText>WITH">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.Object</TypeName></Field></Fields><Query><DataSourceName>DataSource1</DataSourceName><CommandText>WITH
MEMBER Measures.NullColumn AS 'Null' SELECT {Measures.NullColumn} ON
COLUMNS, { [Customers].[State Province].Members } ON ROWS From
Sales</CommandText></Query></DataSet><DataSet
Name="ParameterQuery1"><Fields><Field
Name="ParaLevelField1"><DataField>[Product].[Product
Family].[MEMBER_CAPTION]</DataField><TypeName
xmlns="System.String</TypeName></Field><Field">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.String</TypeName></Field><Field
Name="ParamNullField1"><DataField>[Measures].[NullColumn]</DataField><TypeName
xmlns="System.Object</TypeName></Field></Fields><Query><DataSourceName>DataSource1</DataSourceName><CommandText>WITH">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">System.Object</TypeName></Field></Fields><Query><DataSourceName>DataSource1</DataSourceName><CommandText>WITH
MEMBER Measures.NullColumn AS 'Null' SELECT {Measures.NullColumn} ON
COLUMNS, { [Product].[Product Family].Members } ON ROWS From
Sales</CommandText></Query></DataSet></DataSets><LeftMargin>1in</LeftMargin><SnapToGrid
xmlns="true</SnapToGrid><PageHeight>12in</PageHeight><DrawGrid">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">true</SnapToGrid><PageHeight>12in</PageHeight><DrawGrid
xmlns="true</DrawGrid><PageWidth>8in</PageWidth><ReportID">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">true</DrawGrid><PageWidth>8in</PageWidth><ReportID
xmlns="7B383EFA-88B0-41CB-987B-3F766C0F4776</ReportID><BottomMargin>1in</BottomMargin><ReportParameters><ReportParameter">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">7B383EFA-88B0-41CB-987B-3F766C0F4776</ReportID><BottomMargin>1in</BottomMargin><ReportParameters><ReportParameter
Name="Param0"><DataType>String</DataType><DefaultValue><DataSetReference><DataSetName>ParameterQuery0</DataSetName><ValueField>ParaLevelField0</ValueField></DataSetReference></DefaultValue><Prompt>State
Province</Prompt><ValidValues><DataSetReference><DataSetName>ParameterQuery0</DataSetName><ValueField>ParaLevelField0</ValueField><LabelField>ParaLevelField0</LabelField></DataSetReference></ValidValues></ReportParameter><ReportParameter
Name="Param1"><DataType>String</DataType><DefaultValue><DataSetReference><DataSetName>ParameterQuery1</DataSetName><ValueField>ParaLevelField1</ValueField></DataSetReference></DefaultValue><Prompt>Product
Family</Prompt><ValidValues><DataSetReference><DataSetName>ParameterQuery1</DataSetName><ValueField>ParaLevelField1</ValueField><LabelField>ParaLevelField1</LabelField></DataSetReference></ValidValues></ReportParameter></ReportParameters></Report>

Excel rendering 65536 row limit?

I am experiencing a problem when we are rendering a large report in Excel
format, I get the following error thrown by the ExcelRenderer:
Number of rows in the excel sheet exceeded the limit of 65536 rows.
Has anyone seen this issue? I searched this forum and the SP2 docs.
Thanks,
MikeSilly question: does the report actually consist of more than 65536 rows?
Mike G.
"Mike Sandwick" <MikeSandwick@.discussions.microsoft.com> wrote in message
news:641C0BE6-689C-42E8-BF13-724232C11278@.microsoft.com...
>I am experiencing a problem when we are rendering a large report in Excel
> format, I get the following error thrown by the ExcelRenderer:
> Number of rows in the excel sheet exceeded the limit of 65536 rows.
> Has anyone seen this issue? I searched this forum and the SP2 docs.
> Thanks,
> Mike|||Thats an Excel limitation - it may have been increased with 2003
"Mike G." wrote:
> Silly question: does the report actually consist of more than 65536 rows?
> Mike G.
> "Mike Sandwick" <MikeSandwick@.discussions.microsoft.com> wrote in message
> news:641C0BE6-689C-42E8-BF13-724232C11278@.microsoft.com...
> >I am experiencing a problem when we are rendering a large report in Excel
> > format, I get the following error thrown by the ExcelRenderer:
> >
> > Number of rows in the excel sheet exceeded the limit of 65536 rows.
> >
> > Has anyone seen this issue? I searched this forum and the SP2 docs.
> >
> > Thanks,
> > Mike
>
>|||That is a limit in Excel, usually we have to have clients use a csv files
and load them into Access.
"Espo" <Espo@.discussions.microsoft.com> wrote in message
news:B29123AD-7272-41F9-8D21-5915F7AA57A0@.microsoft.com...
> Thats an Excel limitation - it may have been increased with 2003
> "Mike G." wrote:
> > Silly question: does the report actually consist of more than 65536
rows?
> >
> > Mike G.
> >
> > "Mike Sandwick" <MikeSandwick@.discussions.microsoft.com> wrote in
message
> > news:641C0BE6-689C-42E8-BF13-724232C11278@.microsoft.com...
> > >I am experiencing a problem when we are rendering a large report in
Excel
> > > format, I get the following error thrown by the ExcelRenderer:
> > >
> > > Number of rows in the excel sheet exceeded the limit of 65536 rows.
> > >
> > > Has anyone seen this issue? I searched this forum and the SP2 docs.
> > >
> > > Thanks,
> > > Mike
> >
> >
> >

Excel Numbers format

Hi all i'm having an issues when i trying to do a report:

i make a report and there is a field which contains data that can be numeric and numeric with letter: "77756" or "345WS" when i export my report to excel i and i open it i get like a make in the upper left coner of the cells that have only numbers asking me to convert this to numeric format, but this think is that i need this values to be string so i can apply filtering. is there a way that i can format my cell to string so the excel take it like that and allowme to do the autoifilter.

I would really appriciate your Help

Thanks

The value in the cell is inserted as a string, so filtering should work. The indicator is just Excel warning you that this string could be changed into a number. You can turn off this warning in the options settings of Excel.

Ian|||

Thanks Ian but the autofilter doesn't work if i do a greater than or less than filter.

But i have another field that only contains names and it does the filter by this criterio, i have try to put in the RDL CSTR() function but doesn't work. something similar happened to me with date fiels and i just addded a Cdate to the RDl field and added the mm/dd/yyyy format for that field and i got the autofilter.

I don't know what else i could try

Thanks

|||

You know how in excel you can prefix a value with a single quote, to force it to accept the input as text. You could try that in your report. The downside is that you'd see this in the web view but should not be visible in the export.

I played around in excel and if I prefix my values with a single quote then the excel filter only works if I also include the single quote in my filter expression.

|||
If you can provide an example of what you a trying to accomplish in the generated spreadsheet, I may be able to point you in the right direction. Using just Excel, can you give me an example of how to add a less than or greater than filter for text?

Ian|||sure i can give you an excel sample, but how can i attache the excel here ? or what address can i send it|||In a excel you can auto filter by greater than and less than by using the custom filtering, that appears when you click the selected column with the autofilter on and there it show custom, then it opens a dialog box where you can especify if you want the condition , Equal, greater than or less than...|||Thanks Adam for t he tip, i added the single quote to the expresion in the report and i created a new report and exported to excel, absouly it works, it stops givng me the error for the text to number conversion but i can see the single quote when i open the Excel even if i print it, is there a way that that single quote can be invisible?... jejjeje or am i asking too much?|||

I have this exact same issue. I have an Access Database that exports a tool report to Excel. The report exports properly and has worked for a long time. I recently updated it to Access 2003 and now it has problem. The tools part number is in the form 99-99-9999 but when it tries to export to Excel it turns it into a number and I get a single value such as 359. So I already figured I could just insert the single quote in front of the values and that works but I can see it in Excel. If you enter a number such as this in Excel directly with the single quote it does not have the problem but exporting from Access to Excel it does.

Are there any solutions to that?

|||

this issues for me began with sorting dates, and i have the same problem that you have wit hthis i couldn't get the date right to be filter, but in my RDL i found the solution to that on, in the expresion field that you are getting the date value you have to add the Cdate function and in the format of that textbox in the RDl you have to specify the format yo want it to be: mm/dd/yyyy so that you will avoid to add the single quote to the date fields, but i still have found a solution for my filtering issues when there are numbers and numbers with characters in the same column :-(

Anyone?

|||
You may want to try using the CStr() function, or ToString() on the value of the field, to cast the value to a string. The latter approach, however, may cause an exception if the value of the field is Nothing.

Ian|||

Thank you for the advice, I tried it but I think the problem is I am using an Access report to export to Excel using DoCmd.OutputTo. So between the report and Excel I can't change the formatting apparently. Maybe I need to look into a way to export directly from my Access Query to Excel. That may improve things as I would be taking out a step. If you know which command that would be please let me know but I will probably begin a search for it.

Excel Import TEXT "9760020" imports "9.76002e+006"

Hello,

I have a problem with the Import of an Excel file and hope one of you can help me out.

There is a column with mixed data (format is TEXT) in an excel file and I want to import it as Text (DT_WSTR (255)).

So far everything works fine but some fields like "9760020" imports "9.76002e+006".

My settings so far are:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<FileName>;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1"

In addition I altered the registry entry

TypeGuessRows to 0 (ImportMixedType = Text)

Has someone got a solution?

Thankx

Hello,

I didnt solved the probleme from above, but I can recommend the following approach to get really rid of the uncertainty if there is mixed data for the Provider.

Use a fixed first line with mixed data and set typeguessrows to 1 ... so you can be shure... becouse typeguessrows to 0 scans not all but the first 16000 some lines....

The Excel Import TEXT "9760020" imports "9.76002e+006" problem remains.... If someone knows a way... I would be very grateful.

|||

JWS...

I opened a new spread sheet and set the columns format to text and then copy the data back and it should work.. or u can select all the information in the spread sheet and copy it to a text and then import the text file directly to the spread sheet and then import ur spread sheet to the database.

Hope this helps...

Regards

Karen

Excel Import TEXT "9760020" imports "9.76002e+006"

Hello,

I have a problem with the Import of an Excel file and hope one of you can help me out.

There is a column with mixed data (format is TEXT) in an excel file and I want to import it as Text (DT_WSTR (255)).

So far everything works fine but some fields like "9760020" imports "9.76002e+006".

My settings so far are:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<FileName>;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1"

In addition I altered the registry entry

TypeGuessRows to 0 (ImportMixedType = Text)

Has someone got a solution?

Thankx

Hello,

I didnt solved the probleme from above, but I can recommend the following approach to get really rid of the uncertainty if there is mixed data for the Provider.

Use a fixed first line with mixed data and set typeguessrows to 1 ... so you can be shure... becouse typeguessrows to 0 scans not all but the first 16000 some lines....

The Excel Import TEXT "9760020" imports "9.76002e+006" problem remains.... If someone knows a way... I would be very grateful.

|||

JWS...

I opened a new spread sheet and set the columns format to text and then copy the data back and it should work.. or u can select all the information in the spread sheet and copy it to a text and then import the text file directly to the spread sheet and then import ur spread sheet to the database.

Hope this helps...

Regards

Karen