Dynamically define the height of a chart

Current Situation:
I have a "Stacked Bar" chart and it has a certain ammount of lines depending on its data source. Sometimes if there are too many lines, only every other label shows up for that line on the left hand side (See image).

What I need:
Basically I need a solution that fixes my current situation. My first thouts were to dynamically size the height of the chart, but I haven't had much luck doing that. Also, I have tried to find properties for the char item that might let it grow.

If anyone has any suggestions, or know where I can get more information on this, I would appreciate it very much. Thanks in advance.

It looks like you have just a list of items to show in the chart.

Here is one approach:
* Add a list to the report, put the chart inside the list
* Add a (detail) group to the list with the following grouping expression:
=Int((RowNumber(Nothing)-1)/15)
This should result in the desired grouping of 15 items per (repeating) list instance.

Another approach is to define multiple charts of different sizes and use the Visibility.Hidden property on the chart to dynamically hide all charts but one. Note: you can use =CountRows("DatasetName") to determine the number of rows in a particular dataset and hide one chart e.g. if the total number of dataset rows is greater than 20: Visibility.Hidden property setting: =CountRows("DatasetName") > 20

-- Robert

|||Thanks for the reply. This seems like a good solution, however other problems were present that I didn't notice before. If you look at the x-axis of my graph it too does not display the labels correctly. My boss would like me to now explore using image creation on the fly to possibly fix this problem. Do you think that using a list would fix my problem with the x-axis?|||

You didn't explain why you think the x-axis labels are displayed incorrectly - but if the problem is that lots of data is shown in one chart and the x-axis looks crowded, splitting the underlying dataset into multiple charts will help.

-- Robert

Dynamically add update parameter to formview

I have a formview with name, email, and password. I bind all fields to sql except the password which is blank.

In my sqldatasource, I define parameters for name, email and id:

UpdateCommand

="UPDATE UserProfile SET Name = @.Name,Email = @.Email WHERE (ID = @.ID)">
<UpdateParameters>
<asp:ParameterName="Name"/>
<asp:ParameterName="Email"/>
<asp:ParameterName="ID"/>
</UpdateParameters>

In code I want to add a password parameter if there is value in the password field otherwise I don't want the password field updated. If I add define a password parameter like above then if a user left the password field blank then their new is blank. That's way I think adding it dynamically is the way. But I am having problems with the code to add the parameter in sqldatasource_updating event.

Protected

Sub SqlProfile_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.Updating
Dim passwordAs TextBox = FormView1.FindControl

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not password.Text.ToString &"" =""ThenSqlProfile.UpdateParameters.Add(New Parameter("@.Password", TypeCode.String, password.Text.ToString))End IfEnd Sub

ThanksYou're close:

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not String.IsNullOrEmpty(password.Text)Thene.Command.Parameters.Add(password.Text)End IfEnd Sub

|||

I think you should add the parameter manually, and check for a null / blank parameter in the sql statement. That way you just pass what ever you have in your form (blank password or populated password) and let the SQL statement figure it out for you. If not, then you have do add a new parameter to the updateparameters AND modify your UpdateCommand to have the additional line.

need help with the SQL?

|||

ecbruck:

You're close:

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not String.IsNullOrEmpty(password.Text)Thene.Command.Parameters.Add(password.Text)End IfEnd Sub

if he does it that way, he will need to modify his command as well... adding "Password = @.Something"

|||

pixelsyndicate:

I think you should add the parameter manually, and check for a null / blank parameter in the sql statement.

I agree. I would personally let me Stored Procedure handle the case when the Password parameter was passed in as null.

|||

Thanks for the help.

This is what I have so far but still doesn't work.

Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not String.IsNullOrEmpty(password.Text) Then SqlProfile.UpdateParameters.Add("password", password.Text) SqlProfile.UpdateCommand ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)" End If l_errormessage.Text = password.Text.ToString l_errormessage.Text += e.Command.CommandText.ToStringEnd Sub

|||

Thanks for the help.

This is what I have so far but still doesn't work.

Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not String.IsNullOrEmpty(password.Text) Then SqlProfile.UpdateParameters.Add("password", password.Text) SqlProfile.UpdateCommand ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)" End If l_errormessage.Text = password.Text.ToString l_errormessage.Text += e.Command.CommandText.ToStringEnd Sub

 It updates the name field with no errors but the password doesn't get updated.

|||You need to be modifying the members of the SqlDataSourceCommandEventArgs class rather than the SqlDataSource class as I did in my previous example.|||

When I did your example:

Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not String.IsNullOrEmpty(password.Text) Then e.Command.Parameters.Add(password.Text) e.Command.CommandText ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)" End If l_errormessage.Text = password.Text.ToString l_errormessage.Text += e.Command.CommandText.ToStringEnd Sub

I get this error:

The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.InvalidCastException: The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

Source Error:

Line 31: Dim password As TextBox = FormView1.FindControl("tb_password1")Line 32: If Not String.IsNullOrEmpty(password.Text) ThenLine 33: e.Command.Parameters.Add(password.Text)Line 34: e.Command.CommandText = "UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)"Line 35: End If

|||

Thanks for all the help. This finally work with this code:

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not String.IsNullOrEmpty(password.Text)Then Dim pAs SqlParameter =New SqlParameter("@.Password", SqlDbType.NVarChar) p.Value = password.Text e.Command.Parameters.Add(p) e.Command.CommandText ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)"End If End Sub

dynamic sql with char(39)

hi,
What's the pros and cons for the following two methods
when you define charactor strings in a dynamic sql?
1.
SELECT @.EXPORT_VIEW_SQL = ... 'SELECT ' + char(39)
+ '000000' + char(39) ...
2.
SELECT @.EXPORT_VIEW_SQL = ... 'SELECT ' + ''000000'' ...
they both work, I personally prefer second method, what do
you think?
many thanks!!
JJ
I use the second method most of the time. But occassionally when I have
some complex and requires many single qoute, and I am having problems with
the quoting I will consider using the char(39).
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:455a01c4904c$370c3e40$a301280a@.phx.gbl...
> hi,
> What's the pros and cons for the following two methods
> when you define charactor strings in a dynamic sql?
> 1.
> SELECT @.EXPORT_VIEW_SQL = ... 'SELECT ' + char(39)
> + '000000' + char(39) ...
> 2.
> SELECT @.EXPORT_VIEW_SQL = ... 'SELECT ' + ''000000'' ...
> they both work, I personally prefer second method, what do
> you think?
> many thanks!!
> JJ
>
|||thanks Gregory! so there is no performance or reliability
difference between the two?
JJ
>--Original Message--
>I use the second method most of the time. But
occassionally when I have
>some complex and requires many single qoute, and I am
having problems with
>the quoting I will consider using the char(39).
>--
>----
--
>----
--
>-
>Need SQL Server Examples check out my website
>http://www.geocities.com/sqlserverexamples
>
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:455a01c4904c$370c3e40$a301280a@.phx.gbl...
+ ''000000'' ...[vbcol=seagreen]
do
>
>.
>
|||Or you can use this #3.
SELECT @.EXPORT_VIEW_SQL = 'SELECT ' + quotename('000000',char(39))
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:455a01c4904c$370c3e40$a301280a@.phx.gbl...
> hi,
> What's the pros and cons for the following two methods
> when you define charactor strings in a dynamic sql?
> 1.
> SELECT @.EXPORT_VIEW_SQL = ... 'SELECT ' + char(39)
> + '000000' + char(39) ...
> 2.
> SELECT @.EXPORT_VIEW_SQL = ... 'SELECT ' + ''000000'' ...
> they both work, I personally prefer second method, what do
> you think?
> many thanks!!
> JJ
>

dynamic sql performance

i`m wondering how fast r stored procedures which define their insert,
select, where, from ... clauses as strings vs normal procedures?
at least i`d think dynamic sql gets compiled on every runPerformance here should not be at the top of your concern list, IMHO.
Please read:
http://www.sommarskog.se/dynamic_sql.html
"Fred" <fred@.ilovespam.com> wrote in message
news:euqdamdJGHA.3896@.TK2MSFTNGP15.phx.gbl...
> i`m wondering how fast r stored procedures which define their insert,
> select, where, from ... clauses as strings vs normal procedures?
> at least i`d think dynamic sql gets compiled on every run|||If you must use dynamic SQL, then build it at the application level and then
submit it via Execute or RowSet.Open. Building long queries or T-SQL within
a stored procedure is clunky.
"Fred" <fred@.ilovespam.com> wrote in message
news:euqdamdJGHA.3896@.TK2MSFTNGP15.phx.gbl...
> i`m wondering how fast r stored procedures which define their insert,
> select, where, from ... clauses as strings vs normal procedures?
> at least i`d think dynamic sql gets compiled on every run|||"JT" <someone@.microsoft.com> wrote in message
news:uvRhNWeJGHA.2696@.TK2MSFTNGP14.phx.gbl...
> If you must use dynamic SQL, then build it at the application level and
> then submit it via Execute or RowSet.Open. Building long queries or T-SQL
> within a stored procedure is clunky.
>
No. All dynamic SQL is somewhat clunky. And TSQL is better for dynamic SQL
than most languages since it allows newlines in literal strings. EG
declare @.sql varchar(8000)
set @.sql = '
select * from
' + @.MyTable + '
where id = 124
and region in (' + @.RegionList + ')
'
Anyway you would just push the clunkyness out into the application. It's
generally simpler and more secure to keep the dynamic SQL close.
David|||Fred wrote:

> i`m wondering how fast r stored procedures which define their insert,
> select, where, from ... clauses as strings vs normal procedures?
> at least i`d think dynamic sql gets compiled on every run
Fast relative to what exactly? If you can do the same thing with static
SQL then you should. You use dynamic code when there is no viable
static alternative.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||For the sake of clarity, can you explain further just what
you mean by 'All dynamic SQL is somewhat clunky'.
Terms like 'clunky' can often hide very interesting points of view.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uqD3ZEfJGHA.1088@.tk2msftngp13.phx.gbl...
> "JT" <someone@.microsoft.com> wrote in message
> news:uvRhNWeJGHA.2696@.TK2MSFTNGP14.phx.gbl...
T-SQL
> No. All dynamic SQL is somewhat clunky. And TSQL is better for dynamic
SQL
> than most languages since it allows newlines in literal strings. EG
> declare @.sql varchar(8000)
> set @.sql = '
> select * from
> ' + @.MyTable + '
> where id = 124
> and region in (' + @.RegionList + ')
> '
>
> Anyway you would just push the clunkyness out into the application. It's
> generally simpler and more secure to keep the dynamic SQL close.
> David
>|||RE:
<< Terms like 'clunky' can often hide very interesting points of view.>>
And terms like that frequently hide often very UNinteresting points of view.
It's a form of name-calling. It's like referring to Microsoft as M$... or
that SQL Server is clunky... the CLR is bloat-ware. yadda yadda yadda. Often
these terms are used by luddites
(http://www.google.com/search?hl=en&...ite&btnG=Search).
"05ponyGT" <nospam@.nospam> wrote in message
news:uLHiHcgJGHA.964@.tk2msftngp13.phx.gbl...
> For the sake of clarity, can you explain further just what
> you mean by 'All dynamic SQL is somewhat clunky'.
> Terms like 'clunky' can often hide very interesting points of view.
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:uqD3ZEfJGHA.1088@.tk2msftngp13.phx.gbl...
> T-SQL
> SQL
>|||Thanks for speaking up for Mr. luddite...er Mr. Browne.
I had no idea he was so hostile.
"Smithers" <A@.B.COM> wrote in message
news:OSkddjgJGHA.2912@.tk2msftngp13.phx.gbl...
> RE:
> << Terms like 'clunky' can often hide very interesting points of view.>>
> And terms like that frequently hide often very UNinteresting points of
view.
> It's a form of name-calling. It's like referring to Microsoft as M$... or
> that SQL Server is clunky... the CLR is bloat-ware. yadda yadda yadda.
Often
> these terms are used by luddites
> (http://www.google.com/search?hl=en&...ite&btnG=Search).
>
>
>
> "05ponyGT" <nospam@.nospam> wrote in message
> news:uLHiHcgJGHA.964@.tk2msftngp13.phx.gbl...
and
dynamic
It's
>|||"05ponyGT" <nospam@.nospam> wrote in message
news:uLHiHcgJGHA.964@.tk2msftngp13.phx.gbl...
> For the sake of clarity, can you explain further just what
> you mean by 'All dynamic SQL is somewhat clunky'.
Sure. 'Clunky' was introduced into the thread by JT complaining about
dynamic SQL in stored procedures. I took it to mean that TSQL stored
procedures that use dynamic SQL are not as simple and elegant as stored
procedures which use only static SQL. And dynamic SQL is clunky because you
have two different programs interlaced in one programming unit, where one
program is embedded as a literal string in the other program. That's
clunky.
But it's even more clunky when you embed a SQL program (query) in a 3GL
language like VB or C#. Not only are you building one program as a string
literal in another program, but the programming languages are different, and
the host language often doesn't allow literal strings to span multiple
lines. That's extra clunky.
David|||Please forgive my clunky analysis of the situation.