Hi All,
How I can access a Parameter field(eg - Parameters!vcReportVersion.Value)
within in Custom Code.
Public Shared Function GetParameterText() As String
Return Parameters!vcReportVersion.Value
End Function
This function gives the following error message :
"There is an error on line 37 of custom code: [BC30469] Reference to a
non-shared member requires an object reference."
What is the Object name that contain Parameter collection?
Thanks,
SamYou'll need to pass it as a parameter to the custom code.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Samuel" <samuel@.photoninfotech.com> wrote in message
news:uCAAkJBZEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> How I can access a Parameter field(eg - Parameters!vcReportVersion.Value)
> within in Custom Code.
> Public Shared Function GetParameterText() As String
> Return Parameters!vcReportVersion.Value
> End Function
> This function gives the following error message :
> "There is an error on line 37 of custom code: [BC30469] Reference to a
> non-shared member requires an object reference."
> What is the Object name that contain Parameter collection?
> Thanks,
> Sam
>
Showing posts with label code. Show all posts
Showing posts with label code. Show all posts
Tuesday, March 20, 2012
Monday, March 12, 2012
Parameters on Reporting Services 2005 REPOST
Hi, I experiencing some problems to access the Parameters collection inside
a custom code to build a sql statement.
Below is my code (very simple):
DataSet:
=Code.SQL(Parameters)
Custom Code:
Public Function SQL(ByRef pars As Object) As String
Dim stmt as String
stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
return stmt
End Function
This code works just fine on the Preview(Designer) but if I test the report
on the
browser, it doesnt work and returns the following error:
a.. An error has occurred during report processing.
a.. Cannot set the command text for data set 'ExpoMedios'.
a.. Error during processing of the CommandText expression of dataset
'ExpoMedios'.
Doing some debugging the error message inside the function is:
Attempt to access the method failed.
Can anyone pleae explain why this is happening. Your help will be
appreciated.
Regards,
FabianHi,
have you tried to declare pars As Parameter and not as Object ?
"Fabian von Romberg" wrote:
> Hi, I experiencing some problems to access the Parameters collection inside
> a custom code to build a sql statement.
> Below is my code (very simple):
> DataSet:
> =Code.SQL(Parameters)
> Custom Code:
> Public Function SQL(ByRef pars As Object) As String
> Dim stmt as String
> stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
> return stmt
> End Function
>
> This code works just fine on the Preview(Designer) but if I test the report
> on the
> browser, it doesnt work and returns the following error:
> a.. An error has occurred during report processing.
> a.. Cannot set the command text for data set 'ExpoMedios'.
> a.. Error during processing of the CommandText expression of dataset
> 'ExpoMedios'.
> Doing some debugging the error message inside the function is:
> Attempt to access the method failed.
>
> Can anyone pleae explain why this is happening. Your help will be
> appreciated.
> Regards,
> Fabian
>
>|||Whatt!!!!!!!!!!!!,
I should have tried that before. It did it. I used this code on the older
version of Reporting Services and never got that error message, actually I
think I was not able to set a function parameter as type of Parameters but
Object. On 2005's seems to be the correct way to do it.
Thanks Cedric, I appreciated it.
Regards,
Fabian von Romberg
"Cedric" <Cedric@.discussions.microsoft.com> wrote in message
news:C8A38357-8A82-473A-8208-6DEEBF40DC9F@.microsoft.com...
> Hi,
> have you tried to declare pars As Parameter and not as Object ?
>
> "Fabian von Romberg" wrote:
> > Hi, I experiencing some problems to access the Parameters collection
inside
> > a custom code to build a sql statement.
> >
> > Below is my code (very simple):
> >
> > DataSet:
> > =Code.SQL(Parameters)
> >
> > Custom Code:
> >
> > Public Function SQL(ByRef pars As Object) As String
> > Dim stmt as String
> > stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
> > return stmt
> > End Function
> >
> >
> > This code works just fine on the Preview(Designer) but if I test the
report
> > on the
> > browser, it doesnt work and returns the following error:
> > a.. An error has occurred during report processing.
> > a.. Cannot set the command text for data set 'ExpoMedios'.
> > a.. Error during processing of the CommandText expression of
dataset
> > 'ExpoMedios'.
> >
> > Doing some debugging the error message inside the function is:
> >
> > Attempt to access the method failed.
> >
> >
> > Can anyone pleae explain why this is happening. Your help will be
> > appreciated.
> >
> > Regards,
> > Fabian
> >
> >
> >
> >
a custom code to build a sql statement.
Below is my code (very simple):
DataSet:
=Code.SQL(Parameters)
Custom Code:
Public Function SQL(ByRef pars As Object) As String
Dim stmt as String
stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
return stmt
End Function
This code works just fine on the Preview(Designer) but if I test the report
on the
browser, it doesnt work and returns the following error:
a.. An error has occurred during report processing.
a.. Cannot set the command text for data set 'ExpoMedios'.
a.. Error during processing of the CommandText expression of dataset
'ExpoMedios'.
Doing some debugging the error message inside the function is:
Attempt to access the method failed.
Can anyone pleae explain why this is happening. Your help will be
appreciated.
Regards,
FabianHi,
have you tried to declare pars As Parameter and not as Object ?
"Fabian von Romberg" wrote:
> Hi, I experiencing some problems to access the Parameters collection inside
> a custom code to build a sql statement.
> Below is my code (very simple):
> DataSet:
> =Code.SQL(Parameters)
> Custom Code:
> Public Function SQL(ByRef pars As Object) As String
> Dim stmt as String
> stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
> return stmt
> End Function
>
> This code works just fine on the Preview(Designer) but if I test the report
> on the
> browser, it doesnt work and returns the following error:
> a.. An error has occurred during report processing.
> a.. Cannot set the command text for data set 'ExpoMedios'.
> a.. Error during processing of the CommandText expression of dataset
> 'ExpoMedios'.
> Doing some debugging the error message inside the function is:
> Attempt to access the method failed.
>
> Can anyone pleae explain why this is happening. Your help will be
> appreciated.
> Regards,
> Fabian
>
>|||Whatt!!!!!!!!!!!!,
I should have tried that before. It did it. I used this code on the older
version of Reporting Services and never got that error message, actually I
think I was not able to set a function parameter as type of Parameters but
Object. On 2005's seems to be the correct way to do it.
Thanks Cedric, I appreciated it.
Regards,
Fabian von Romberg
"Cedric" <Cedric@.discussions.microsoft.com> wrote in message
news:C8A38357-8A82-473A-8208-6DEEBF40DC9F@.microsoft.com...
> Hi,
> have you tried to declare pars As Parameter and not as Object ?
>
> "Fabian von Romberg" wrote:
> > Hi, I experiencing some problems to access the Parameters collection
inside
> > a custom code to build a sql statement.
> >
> > Below is my code (very simple):
> >
> > DataSet:
> > =Code.SQL(Parameters)
> >
> > Custom Code:
> >
> > Public Function SQL(ByRef pars As Object) As String
> > Dim stmt as String
> > stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
> > return stmt
> > End Function
> >
> >
> > This code works just fine on the Preview(Designer) but if I test the
report
> > on the
> > browser, it doesnt work and returns the following error:
> > a.. An error has occurred during report processing.
> > a.. Cannot set the command text for data set 'ExpoMedios'.
> > a.. Error during processing of the CommandText expression of
dataset
> > 'ExpoMedios'.
> >
> > Doing some debugging the error message inside the function is:
> >
> > Attempt to access the method failed.
> >
> >
> > Can anyone pleae explain why this is happening. Your help will be
> > appreciated.
> >
> > Regards,
> > Fabian
> >
> >
> >
> >
Friday, March 9, 2012
Parameters in Reporting Services - C# Syntax
I'm using code from microsoft's website and I'm getting the following error:
"The type or namespace name 'ParameterValue' could not be found (are you missing a using directive or an assembly reference?)"
What am I doing wrong here? I want to pass parameters to the .render method.
// Prepare report parameter.
ParameterValue[] parameters = new ParameterValue[3]; <-- error occurs on this line
parameters[0] = new ParameterValue();
parameters[0].Name = "EmpID";
parameters[0].Value = "38";
parameters[1] = new ParameterValue();
parameters[1].Name = "ReportMonth";
parameters[1].Value = "6"; // June
parameters[2] = new ParameterValue();
parameters[2].Name = "ReportYear";
parameters[2].Value = "2004";
i figured it out
i had to add ReportServer before ParameterValue
i.e. ReportServer.ParameterValue[] parameters = new ReportServer.ParameterValue[3];
"The type or namespace name 'ParameterValue' could not be found (are you missing a using directive or an assembly reference?)"
What am I doing wrong here? I want to pass parameters to the .render method.
// Prepare report parameter.
ParameterValue[] parameters = new ParameterValue[3]; <-- error occurs on this line
parameters[0] = new ParameterValue();
parameters[0].Name = "EmpID";
parameters[0].Value = "38";
parameters[1] = new ParameterValue();
parameters[1].Name = "ReportMonth";
parameters[1].Value = "6"; // June
parameters[2] = new ParameterValue();
parameters[2].Name = "ReportYear";
parameters[2].Value = "2004";
i figured it out
i had to add ReportServer before ParameterValue
i.e. ReportServer.ParameterValue[] parameters = new ReportServer.ParameterValue[3];
Wednesday, March 7, 2012
Parameters for sp that depend on the current row fields
How can I specify parameters to a store procedure that depend on a row
without using ado.net in code behind?
The problem is that I use 5 parameters for my store procedure. 2 are report
parameters, and I want other 3 to be specified by the fields of the current
row, in this way the returned value by the store procedure depends on certain
values of each row.
I currently use code behind with Ado.Net to specify the parameters but
opening and closing the connection takes some time (depending on the # of
rows), if I somewhat could call the sp from the given field in the row in
reporting services it would be way faster. Does someone knows of a way to do
this?
Thanks.You can embed a subreport in a field of the current row and pass those
fields and parameters to the subreport (I do this). Give it a try, should be
a lot easier and cleaner.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"gchinl" <gchinl@.discussions.microsoft.com> wrote in message
news:64EF7852-D7B0-4479-8B64-971E93E88B9A@.microsoft.com...
> How can I specify parameters to a store procedure that depend on a row
> without using ado.net in code behind?
> The problem is that I use 5 parameters for my store procedure. 2 are
> report
> parameters, and I want other 3 to be specified by the fields of the
> current
> row, in this way the returned value by the store procedure depends on
> certain
> values of each row.
> I currently use code behind with Ado.Net to specify the parameters but
> opening and closing the connection takes some time (depending on the # of
> rows), if I somewhat could call the sp from the given field in the row in
> reporting services it would be way faster. Does someone knows of a way to
> do
> this?
> Thanks.
without using ado.net in code behind?
The problem is that I use 5 parameters for my store procedure. 2 are report
parameters, and I want other 3 to be specified by the fields of the current
row, in this way the returned value by the store procedure depends on certain
values of each row.
I currently use code behind with Ado.Net to specify the parameters but
opening and closing the connection takes some time (depending on the # of
rows), if I somewhat could call the sp from the given field in the row in
reporting services it would be way faster. Does someone knows of a way to do
this?
Thanks.You can embed a subreport in a field of the current row and pass those
fields and parameters to the subreport (I do this). Give it a try, should be
a lot easier and cleaner.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"gchinl" <gchinl@.discussions.microsoft.com> wrote in message
news:64EF7852-D7B0-4479-8B64-971E93E88B9A@.microsoft.com...
> How can I specify parameters to a store procedure that depend on a row
> without using ado.net in code behind?
> The problem is that I use 5 parameters for my store procedure. 2 are
> report
> parameters, and I want other 3 to be specified by the fields of the
> current
> row, in this way the returned value by the store procedure depends on
> certain
> values of each row.
> I currently use code behind with Ado.Net to specify the parameters but
> opening and closing the connection takes some time (depending on the # of
> rows), if I somewhat could call the sp from the given field in the row in
> reporting services it would be way faster. Does someone knows of a way to
> do
> this?
> Thanks.
Parameters collection
I have a function defined in Report->Report Properties-> Code Tab which
I want to access the parameters collection to look for a Debug flag.
I get the following error:
c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
[BC30469] Reference to a non-shared member requires an object
reference.
How do I access the Parameters collection within my custom code?
report.Parameters!parameter
Thanks Tomson McCabe :)
Parameters collection
I have a function defined in Report->Report Properties-> Code Tab which
I want to access the parameters collection to look for a Debug flag.
I get the following error:
c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
[BC30469] Reference to a non-shared member requires an object
reference.
How do I access the Parameters collection within my custom code?
report.Parameters!parameter
Thanks Tomson McCabe :)
Parameters collection
I have a function defined in Report->Report Properties-> Code Tab which
I want to access the parameters collection to look for a Debug flag.
I get the following error:
c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
[BC30469] Reference to a non-shared member requires an object
reference.
How do I access the Parameters collection within my custom code?In your custom code, use:
Report.Parameters!parameter1.Value
"Paul H" wrote:
> I have a function defined in Report->Report Properties-> Code Tab which
> I want to access the parameters collection to look for a Debug flag.
> I get the following error:
> c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
> [BC30469] Reference to a non-shared member requires an object
> reference.
> How do I access the Parameters collection within my custom code?
>
I want to access the parameters collection to look for a Debug flag.
I get the following error:
c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
[BC30469] Reference to a non-shared member requires an object
reference.
How do I access the Parameters collection within my custom code?In your custom code, use:
Report.Parameters!parameter1.Value
"Paul H" wrote:
> I have a function defined in Report->Report Properties-> Code Tab which
> I want to access the parameters collection to look for a Debug flag.
> I get the following error:
> c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
> [BC30469] Reference to a non-shared member requires an object
> reference.
> How do I access the Parameters collection within my custom code?
>
Saturday, February 25, 2012
Parameters and passing values
I am trying to pass two parameters to a stored procedure that works fine whe
n
I use the code, for example:
param_user.Value = "blahblah@.com"
The problem is the value I need is in a variable, but when I use:
param_user.Value = variable name
I get no records returned.
I cant seem to be able to pass the variable into the param.value...any ideas
?
Thanks in advanceSee replies in
microsoft.public.sqlserver.programming
John
"free70@.community.nospam"
<free70@.community.nospam@.discussions.microsoft.com> wrote in message
news:C0FB55D1-374C-4098-B763-ADE22A073668@.microsoft.com...
>I am trying to pass two parameters to a stored procedure that works fine
>when
> I use the code, for example:
> param_user.Value = "blahblah@.com"
> The problem is the value I need is in a variable, but when I use:
> param_user.Value = variable name
> I get no records returned.
> I cant seem to be able to pass the variable into the param.value...any
> ideas?
> Thanks in advance
>|||Thanks John - I posted to the wrong group...
"John Bell" wrote:
> See replies in
> microsoft.public.sqlserver.programming
>
> John
> "free70@.community.nospam"
> <free70@.community.nospam@.discussions.microsoft.com> wrote in message
> news:C0FB55D1-374C-4098-B763-ADE22A073668@.microsoft.com...
>
>
n
I use the code, for example:
param_user.Value = "blahblah@.com"
The problem is the value I need is in a variable, but when I use:
param_user.Value = variable name
I get no records returned.
I cant seem to be able to pass the variable into the param.value...any ideas
?
Thanks in advanceSee replies in
microsoft.public.sqlserver.programming
John
"free70@.community.nospam"
<free70@.community.nospam@.discussions.microsoft.com> wrote in message
news:C0FB55D1-374C-4098-B763-ADE22A073668@.microsoft.com...
>I am trying to pass two parameters to a stored procedure that works fine
>when
> I use the code, for example:
> param_user.Value = "blahblah@.com"
> The problem is the value I need is in a variable, but when I use:
> param_user.Value = variable name
> I get no records returned.
> I cant seem to be able to pass the variable into the param.value...any
> ideas?
> Thanks in advance
>|||Thanks John - I posted to the wrong group...
"John Bell" wrote:
> See replies in
> microsoft.public.sqlserver.programming
>
> John
> "free70@.community.nospam"
> <free70@.community.nospam@.discussions.microsoft.com> wrote in message
> news:C0FB55D1-374C-4098-B763-ADE22A073668@.microsoft.com...
>
>
Parameters and passing values
I am trying to pass two parameters to a stored procedure that works fine when
I use the code, for example:
param_user.Value = "blahblah@.com"
The problem is the value I need is in a variable, but when I use:
param_user.Value = variable name
I get no records returned.
I cant seem to be able to pass the variable into the param.value...any ideas?
Thanks in advance
See replies in
microsoft.public.sqlserver.programming
John
"free70@.community.nospam"
<free70@.community.nospam@.discussions.microsoft.com > wrote in message
news:C0FB55D1-374C-4098-B763-ADE22A073668@.microsoft.com...
>I am trying to pass two parameters to a stored procedure that works fine
>when
> I use the code, for example:
> param_user.Value = "blahblah@.com"
> The problem is the value I need is in a variable, but when I use:
> param_user.Value = variable name
> I get no records returned.
> I cant seem to be able to pass the variable into the param.value...any
> ideas?
> Thanks in advance
>
|||Thanks John - I posted to the wrong group...
"John Bell" wrote:
> See replies in
> microsoft.public.sqlserver.programming
>
> John
> "free70@.community.nospam"
> <free70@.community.nospam@.discussions.microsoft.com > wrote in message
> news:C0FB55D1-374C-4098-B763-ADE22A073668@.microsoft.com...
>
>
I use the code, for example:
param_user.Value = "blahblah@.com"
The problem is the value I need is in a variable, but when I use:
param_user.Value = variable name
I get no records returned.
I cant seem to be able to pass the variable into the param.value...any ideas?
Thanks in advance
See replies in
microsoft.public.sqlserver.programming
John
"free70@.community.nospam"
<free70@.community.nospam@.discussions.microsoft.com > wrote in message
news:C0FB55D1-374C-4098-B763-ADE22A073668@.microsoft.com...
>I am trying to pass two parameters to a stored procedure that works fine
>when
> I use the code, for example:
> param_user.Value = "blahblah@.com"
> The problem is the value I need is in a variable, but when I use:
> param_user.Value = variable name
> I get no records returned.
> I cant seem to be able to pass the variable into the param.value...any
> ideas?
> Thanks in advance
>
|||Thanks John - I posted to the wrong group...
"John Bell" wrote:
> See replies in
> microsoft.public.sqlserver.programming
>
> John
> "free70@.community.nospam"
> <free70@.community.nospam@.discussions.microsoft.com > wrote in message
> news:C0FB55D1-374C-4098-B763-ADE22A073668@.microsoft.com...
>
>
Parameters & ReportingService.Render
My report has 2 parameters. One I would like to set the value of in my vb
code, the other I would like to be queried according to the report design.
In the designer the first parameter is non-queried and will allow blank but
not null. The second is "from query". Obviously everything works in the
designer. ;) The 2nd parameter dropdown list is correctly populated from
the query that's embedded in the report design.
My <DeviceInfo> has toolbar=true, parameters=true.
I have tried several things. In all cases parameters(1).value = <correct
value>.
1) parameters(2).Value = Nothing. Error is "Default value or value provided
for the report parameter 'TDN' is not a valid value."
2) parameters(2).value = "". Error is the same as #1.
3) parameters(2).value = " ". Error is the same as #1.
4) parameters(2).value = "C100". This is a typical value for this field,
but won't be accurate in all situations. same error.
5) leave off parameter(2) in the vb code. Error is "This report requires a
default or user-defined value for the report parameter 'TDN'. To run or
subscribe to this report, you must provide a parameter value."
6) paraamters(2).value = "". check "default" and "null" in Report Manager.
Error is "Default value or value provided for the report parameter 'TDN' is
not a valid value."
Can anyone shed some light on this?
Thanks."Joe" wrote:
> My report has 2 parameters. One I would like to set the value of in my vb
> code, the other I would like to be queried according to the report design.
> In the designer the first parameter is non-queried and will allow blank but
> not null. The second is "from query". Obviously everything works in the
> designer. ;) The 2nd parameter dropdown list is correctly populated from
> the query that's embedded in the report design.
Look inside the YourReport.rdl (View Code) and double check the
ReportParameter names in the xml. You should find your two, case sensitive
parameter names.
If one is called parameter1 instead of your name, or additional entries, try
changing it to what you want, then redeploy the report and retry your code.
Sometimes the Query Designer has a mind of its own with parameters, and has
messed up working rdl files. <g>
I hope this is of some help to you.
code, the other I would like to be queried according to the report design.
In the designer the first parameter is non-queried and will allow blank but
not null. The second is "from query". Obviously everything works in the
designer. ;) The 2nd parameter dropdown list is correctly populated from
the query that's embedded in the report design.
My <DeviceInfo> has toolbar=true, parameters=true.
I have tried several things. In all cases parameters(1).value = <correct
value>.
1) parameters(2).Value = Nothing. Error is "Default value or value provided
for the report parameter 'TDN' is not a valid value."
2) parameters(2).value = "". Error is the same as #1.
3) parameters(2).value = " ". Error is the same as #1.
4) parameters(2).value = "C100". This is a typical value for this field,
but won't be accurate in all situations. same error.
5) leave off parameter(2) in the vb code. Error is "This report requires a
default or user-defined value for the report parameter 'TDN'. To run or
subscribe to this report, you must provide a parameter value."
6) paraamters(2).value = "". check "default" and "null" in Report Manager.
Error is "Default value or value provided for the report parameter 'TDN' is
not a valid value."
Can anyone shed some light on this?
Thanks."Joe" wrote:
> My report has 2 parameters. One I would like to set the value of in my vb
> code, the other I would like to be queried according to the report design.
> In the designer the first parameter is non-queried and will allow blank but
> not null. The second is "from query". Obviously everything works in the
> designer. ;) The 2nd parameter dropdown list is correctly populated from
> the query that's embedded in the report design.
Look inside the YourReport.rdl (View Code) and double check the
ReportParameter names in the xml. You should find your two, case sensitive
parameter names.
If one is called parameter1 instead of your name, or additional entries, try
changing it to what you want, then redeploy the report and retry your code.
Sometimes the Query Designer has a mind of its own with parameters, and has
messed up working rdl files. <g>
I hope this is of some help to you.
Parameters
Attempting to use parameters for report pulling from Teradata. Appears TD
does not like parameters. Thinking of custom code for generate dataset query
dynamically.. Is there function/method to generate / replace sql string?
Other ideas?
Thxin place of your query, have a vb-type expresssion like:
="select * from tablename where col1 = " & Parameters!Parm1.value
Let the report create your fields for you by running a hard-coded version of
the query, then change the query string to the vb expression. It will
generate the sql string expression, then execute it. As long as the
expression generates a sql string whose fields match those pre-generated in
the report, it will work.
Steve
"DJC" wrote:
> Attempting to use parameters for report pulling from Teradata. Appears TD
> does not like parameters. Thinking of custom code for generate dataset query
> dynamically.. Is there function/method to generate / replace sql string?
> Other ideas?
> Thx|||What error are you geting with the Parameters you are useing ?
In so cases TD does not let you name your Parameters. so what i did was this
--
(DT_COM BETWEEN TO_DATE(?, 'MM/DD/YYYY') AND TO_DATE(?, 'MM/DD/YYYY'))
"SteveIrwin" wrote:
> in place of your query, have a vb-type expresssion like:
> ="select * from tablename where col1 = " & Parameters!Parm1.value
> Let the report create your fields for you by running a hard-coded version of
> the query, then change the query string to the vb expression. It will
> generate the sql string expression, then execute it. As long as the
> expression generates a sql string whose fields match those pre-generated in
> the report, it will work.
> Steve
> "DJC" wrote:
> > Attempting to use parameters for report pulling from Teradata. Appears TD
> > does not like parameters. Thinking of custom code for generate dataset query
> > dynamically.. Is there function/method to generate / replace sql string?
> > Other ideas?
> > Thx|||Thanks Steve.. works like a charm.
"SteveIrwin" wrote:
> in place of your query, have a vb-type expresssion like:
> ="select * from tablename where col1 = " & Parameters!Parm1.value
> Let the report create your fields for you by running a hard-coded version of
> the query, then change the query string to the vb expression. It will
> generate the sql string expression, then execute it. As long as the
> expression generates a sql string whose fields match those pre-generated in
> the report, it will work.
> Steve
> "DJC" wrote:
> > Attempting to use parameters for report pulling from Teradata. Appears TD
> > does not like parameters. Thinking of custom code for generate dataset query
> > dynamically.. Is there function/method to generate / replace sql string?
> > Other ideas?
> > Thx|||Thanke for the response. Steve's respone resolved...
BTW, Most of the errors were I think were related to OLE DB vs ODBC on the
TD side...
"C.M" wrote:
> What error are you geting with the Parameters you are useing ?
> In so cases TD does not let you name your Parameters. so what i did was this
> --
> (DT_COM BETWEEN TO_DATE(?, 'MM/DD/YYYY') AND TO_DATE(?, 'MM/DD/YYYY'))
>
>
> "SteveIrwin" wrote:
> > in place of your query, have a vb-type expresssion like:
> >
> > ="select * from tablename where col1 = " & Parameters!Parm1.value
> >
> > Let the report create your fields for you by running a hard-coded version of
> > the query, then change the query string to the vb expression. It will
> > generate the sql string expression, then execute it. As long as the
> > expression generates a sql string whose fields match those pre-generated in
> > the report, it will work.
> >
> > Steve
> >
> > "DJC" wrote:
> >
> > > Attempting to use parameters for report pulling from Teradata. Appears TD
> > > does not like parameters. Thinking of custom code for generate dataset query
> > > dynamically.. Is there function/method to generate / replace sql string?
> > > Other ideas?
> > > Thx
does not like parameters. Thinking of custom code for generate dataset query
dynamically.. Is there function/method to generate / replace sql string?
Other ideas?
Thxin place of your query, have a vb-type expresssion like:
="select * from tablename where col1 = " & Parameters!Parm1.value
Let the report create your fields for you by running a hard-coded version of
the query, then change the query string to the vb expression. It will
generate the sql string expression, then execute it. As long as the
expression generates a sql string whose fields match those pre-generated in
the report, it will work.
Steve
"DJC" wrote:
> Attempting to use parameters for report pulling from Teradata. Appears TD
> does not like parameters. Thinking of custom code for generate dataset query
> dynamically.. Is there function/method to generate / replace sql string?
> Other ideas?
> Thx|||What error are you geting with the Parameters you are useing ?
In so cases TD does not let you name your Parameters. so what i did was this
--
(DT_COM BETWEEN TO_DATE(?, 'MM/DD/YYYY') AND TO_DATE(?, 'MM/DD/YYYY'))
"SteveIrwin" wrote:
> in place of your query, have a vb-type expresssion like:
> ="select * from tablename where col1 = " & Parameters!Parm1.value
> Let the report create your fields for you by running a hard-coded version of
> the query, then change the query string to the vb expression. It will
> generate the sql string expression, then execute it. As long as the
> expression generates a sql string whose fields match those pre-generated in
> the report, it will work.
> Steve
> "DJC" wrote:
> > Attempting to use parameters for report pulling from Teradata. Appears TD
> > does not like parameters. Thinking of custom code for generate dataset query
> > dynamically.. Is there function/method to generate / replace sql string?
> > Other ideas?
> > Thx|||Thanks Steve.. works like a charm.
"SteveIrwin" wrote:
> in place of your query, have a vb-type expresssion like:
> ="select * from tablename where col1 = " & Parameters!Parm1.value
> Let the report create your fields for you by running a hard-coded version of
> the query, then change the query string to the vb expression. It will
> generate the sql string expression, then execute it. As long as the
> expression generates a sql string whose fields match those pre-generated in
> the report, it will work.
> Steve
> "DJC" wrote:
> > Attempting to use parameters for report pulling from Teradata. Appears TD
> > does not like parameters. Thinking of custom code for generate dataset query
> > dynamically.. Is there function/method to generate / replace sql string?
> > Other ideas?
> > Thx|||Thanke for the response. Steve's respone resolved...
BTW, Most of the errors were I think were related to OLE DB vs ODBC on the
TD side...
"C.M" wrote:
> What error are you geting with the Parameters you are useing ?
> In so cases TD does not let you name your Parameters. so what i did was this
> --
> (DT_COM BETWEEN TO_DATE(?, 'MM/DD/YYYY') AND TO_DATE(?, 'MM/DD/YYYY'))
>
>
> "SteveIrwin" wrote:
> > in place of your query, have a vb-type expresssion like:
> >
> > ="select * from tablename where col1 = " & Parameters!Parm1.value
> >
> > Let the report create your fields for you by running a hard-coded version of
> > the query, then change the query string to the vb expression. It will
> > generate the sql string expression, then execute it. As long as the
> > expression generates a sql string whose fields match those pre-generated in
> > the report, it will work.
> >
> > Steve
> >
> > "DJC" wrote:
> >
> > > Attempting to use parameters for report pulling from Teradata. Appears TD
> > > does not like parameters. Thinking of custom code for generate dataset query
> > > dynamically.. Is there function/method to generate / replace sql string?
> > > Other ideas?
> > > Thx
Monday, February 20, 2012
Parameterized query returns one row with null values.
I am hoping someone could help me understand why this is happening and perhaps a solution.
I am using ASP.NET 2.0 with a SQL 2005 database.
In code behind, I am performing a query using a parameter as below:
sql = "SELECT field_name FROM myTable WHERE (field_name = @.P1)"
objCommand.Parameters.Add(New SqlParameter("@.P1", TextBox1.Text))
The parameter is obtained from TextBox1 which has valid input. However, the value is not in the table. The query should not return ANY results. However, I am getting one single row back with null values for each field requested in the query.
The SQL user account for this query has select, insert, and update permissions on the table. The query is simple, no joins, and the table has no null values in any fields. If I perform the exact same query using an account with select only permission on the table, I get what I was expecting, no records. Then if I go back to the previous user account with more permissioins, and I change the query to pass the paramter this way:
sql =String.Format("SELECT field_name FROM myTable WHERE (field_name = {0})", TextBox1.Text)
I also get NO records retuned using the same criteria.
What is going on here? I would prefer to use the parameterized query method with the account having elevated permissions. Is there some command object setting that can prevent the null row from returning?
Thanks!
I am not sure but see if adding the datatype helps:
objCommand.Parameters.Add(New SqlParameter("@.P1", SqlDbType.Varchar,30)).value = TextBox1.Text|||Thanks for the suggestion. I tried adding the data type as you suggested. It did not change the results.
I have found that if I change to a data reader, the null value is not being returned. So, now it looks to be related to the ExecuteScalar method.
|||
I also just realized that it is not a null value being returned but instead an empty value, ie "".
I can get around this easily enough in multiple ways, I am just wanting to understand why this is happening.
So far I have this narrowed down to the following:
A parameterized query, with a user account having select, insert, update permission, and using the ExecuteScalar method. This combination returns a record with an empty result when the criteria is not found in the table instead of returning no records at all.
|||
Eh?
ExecuteScalar is used to return the first column of the first row of the query. If there is no rows, the value comes back as null.
I think perhaps you are misunderstanding what ExecuteScalar is supposed to do. It doesn't return records, or recordsets, it returns a singular scalar value (One column of one row - the first of each).
For further help, please post the whole code block in question. How you initialize your connection, command objects, how you are actually executing the query, where you are storing the result of the query (And how it is defined), and what you expected the result to be, and what you actually got.
If the results are varying depending on what user is executing the query, please make sure that either you explicitly define the schema you want to use, or that there doesn't exist multiple tables with the same name under different schemas (Refer to the table as dbo.Table not just Table).
|||Ok, my bad, stupid mistake(s) with both user permissions and also with the string.format method.
I at least have it consistenly returning the empty record.
One last question, why return null/empty instead of just nothing like a data reader?
Thank you very much for the response.
|||Hi,
ExecuteScalar is designed to return a single value from a database command and the proper representation of a single non-existant value is returning null. The ExecuteScalar is a non-void method and should return something!
Enjoy C#,
Mehrdad
|||Thank you to everyone for the help and clairification on ExecuteScalar.
Parameterized Queries
Just getting started using SSce and having a few problems
What I want to do is something like this...
Dim Code As Integer
Dim Description As String = txtDescription.Text.Trim
Dim conn As SqlCeConnection = ConnectToLocalDatabase()
Dim ssql As New System.Text.StringBuilder
ssql.AppendLine("INSERT INTO T_Titles (Description)")
ssql.AppendLine("VALUES(@.Description)")
ssql.AppendLine("SELECT @.Code = @.@.IDENTITY")
Dim cmd As New SqlCeCommand(ssql.ToString, conn)
Dim sqlCode As New SqlCeParameter("@.Code", 0)
sqlCode.Direction = ParameterDirection.InputOutput
cmd.Parameters.Add(sqlCode)
cmd.Parameters.Add(New SqlCeParameter("@.Description", Description))
cmd.ExecuteNonQuery()
Code = CInt(sqlCode.Value)
**********************************************************************
The above code doesnt work. Firstly I am not sure if I can execute the two statements in one go. Secondly, I am not sure if output parameters are supported.
I have been working with SQL Server since 6.5 but have always used sprocs and am feeling a little lost here without them. Any help getting started would be greatly appreciated.
Thanks
Sadly, SQL Server mobile does not support batch queries. Queries must be a single SQL statement. Stored prcedures are therefore not supported either. ExecuteNonQuery returns the number of rows affected for selects. For more information see:
http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlcecommand.aspx
|||
Thanks ErikEJ
That explains everything. It also solves my second problem with output parameters. If you cant have multiple queries then Output Parameters wouldnt be very useful either.
Thank you again.
David
Subscribe to:
Posts (Atom)