Can we break a chart line

I have a line chart which describe the number of absent students every week.
There is summer vocation during a year (week 27th to week 39th)
I want to the line break up when summer vocation.
Can we break line where summer vocation?As I understand your problem you have dates plotted along the X-axis and your
chart shows the dates during the summer vactaion with data points of zero
being charted during that period (assuming that the minum value for the
Y-axis is set at zero). One sulution that you can use is to force the reurned
the data to have minus values during the summer vacation. That way the data
points are invisible because they are off the chart. You can force the minus
values in a number of ways.
There may be other solutions but hopefully this will help.
"ad" wrote:
> I have a line chart which describe the number of absent students every week.
> There is summer vocation during a year (week 27th to week 39th)
> I want to the line break up when summer vocation.
> Can we break line where summer vocation?
>
>|||Thank,
I have filtered out the 27th week to 39th weeks,So there are no value
between 26th week and 40 weeks.
But there is still a line between 26th week and 40 week.
I want that there are not any line between 26th week and 4o weeks.
Can I do that?
"B. Mark McKinney" <BMarkMcKinney@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:A4567ECA-D3C0-4C5B-A870-A252D35338A2@.microsoft.com...
> As I understand your problem you have dates plotted along the X-axis and
your
> chart shows the dates during the summer vactaion with data points of zero
> being charted during that period (assuming that the minum value for the
> Y-axis is set at zero). One sulution that you can use is to force the
reurned
> the data to have minus values during the summer vacation. That way the
data
> points are invisible because they are off the chart. You can force the
minus
> values in a number of ways.
> There may be other solutions but hopefully this will help.
> "ad" wrote:
> > I have a line chart which describe the number of absent students every
week.
> >
> > There is summer vocation during a year (week 27th to week 39th)
> >
> > I want to the line break up when summer vocation.
> >
> > Can we break line where summer vocation?
> >
> >
> >|||I don't think that you can simply not chart the summer vacation period (break
the line) as long as this period is displayed. Thus my suggestion to force
the data points to a negative value show that they do not show up. Note that
a side result of this is that the charted line will appear to drop off the
chart at the beginning of the summer and then rise back up after the summer
break is over. Similar to charting values that are higher than a maximum
value that you set for the Y-axis.
"ad" wrote:
> Thank,
> I have filtered out the 27th week to 39th weeks,So there are no value
> between 26th week and 40 weeks.
> But there is still a line between 26th week and 40 week.
> I want that there are not any line between 26th week and 4o weeks.
> Can I do that?
>
>
> "B. Mark McKinney" <BMarkMcKinney@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
> :A4567ECA-D3C0-4C5B-A870-A252D35338A2@.microsoft.com...
> > As I understand your problem you have dates plotted along the X-axis and
> your
> > chart shows the dates during the summer vactaion with data points of zero
> > being charted during that period (assuming that the minum value for the
> > Y-axis is set at zero). One sulution that you can use is to force the
> reurned
> > the data to have minus values during the summer vacation. That way the
> data
> > points are invisible because they are off the chart. You can force the
> minus
> > values in a number of ways.
> >
> > There may be other solutions but hopefully this will help.
> >
> > "ad" wrote:
> >
> > > I have a line chart which describe the number of absent students every
> week.
> > >
> > > There is summer vocation during a year (week 27th to week 39th)
> > >
> > > I want to the line break up when summer vocation.
> > >
> > > Can we break line where summer vocation?
> > >
> > >
> > >
>
>

Can variable be used in SQL UPDATE statement in VB.NET

Hy, i have this problem in vb.net:

I must use a variable in SQL UPDATE statement, after SET statement, and i'm getting error. This is that line of code:

Dim variable_name As String

Dim variable As Integer

Dim sqlStringAsString = ("UPDATE table_name SET " variable_name" = " & variable &" WHERE UserID = '" & UserID &"'")

Dim cmdSqlCommandAsNew SqlCommand(sqlString, conConnetion)

cmdSqlCommand.ExecuteNonQuery()

When I don't use a variable after SET statement, everything work fine. This code works fine:

Dim variable As Integer

Dim sqlStringAsString = ("UPDATE table_name SET column_name = " & variable &" WHERE UserID = '" & UserID &"'")

Dim cmdSqlCommandAsNew SqlCommand(sqlString, conConnetion)

cmdSqlCommand.ExecuteNonQuery()

Please, if someone can help me in this...thanks..

Hi,

Did you missing ampersand sign in ?

Dim sqlStringAsString = ("UPDATE table_name SET " variable_name" = " & variable &" WHERE UserID = '" & UserID &"'")

Try

Dim sqlStringAsString = ("UPDATE table_name SET " & variable_name &" = " & variable &" WHERE UserID = '" & UserID &"'")

|||

thanks on answering..i was getting numeric value, but need string value, that was a problem, variable_name has numeric value, and that was error in sql statement...thanks on help

Can this stored procedure be optimised?

Hi there,
I hoping someone can help me reduce the number of line of code I'm
using, as these IF's are nested inside a bigger one.
The main problem I have is I need to add another variable to the IF and
don't want to copy and paste and make this statement even larger.
I have tried playing about with EXEC but with no joy. As far as I'm
aware it's not possible to do @.CSOrder > 0 ? CSPerson = @.CSOrder :
CSPerson > 0
I'm using SQL Server 2000 SP4.
Hopefully I'm missing the obvious, although any help or suggestions are
welcome.
-- snippet --
IF @.CSOrder > 0 AND @.SalesOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = @.CSOrder
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status
ELSE IF @.CSOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = @.CSOrder
ORDER BY DateToArrive, Status
ELSE IF @.SalesOrder > 0
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status
ELSE
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
ORDER BY DateToArrive, StatusWhat you can do is something like this.
SELECT * FROM MainOrder
LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
WHERE JobFlow.Component=6
AND (Status = @.InProgress
OR Status = @.Complete
OR Status = @.Cancelled
OR Status = @.Acknowledged)
AND CSPerson = CSAE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
END
AND SalesPerson = CASE @.SalesOrder WHEN 0 THEN SalesPerson ELSE
@.SalesOrder END
AND ... -- add more
AND SalesPerson = @.SalesOrder
ORDER BY DateToArrive, Status|||Mia,
Try something like this
DECLARE @.strWHERE varchar(200)
SET @.strWHERE = ''
IF @.CSOrder > 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND CSPerson =
@.CSOrder AND SalesPerson = @.SalesOrder'
IF @.CSOrder > 0 AND @.SalesOrder <> 0 THEN @.strWHERE = 'AND CSPerson =
@.CSOrder'
IF @.CSOrder <> 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND SalesPerson =
@.SalesOrder'
DECLARE @.sSQL varchar(2000)
SET @.sSQL = ''
SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id] '
SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id] '
SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
SET @.sSQL = @.sSQL + @.strWHERE
SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
SET @.sSQL = @.sSQL + ' AORDER BY DateToArrive, Status '
EXEC (@.sSQL)
Hope this assists,
Tony
"mia_cid@.hotmail.com" wrote:

> Hi there,
> I hoping someone can help me reduce the number of line of code I'm
> using, as these IF's are nested inside a bigger one.
> The main problem I have is I need to add another variable to the IF and
> don't want to copy and paste and make this statement even larger.
> I have tried playing about with EXEC but with no joy. As far as I'm
> aware it's not possible to do @.CSOrder > 0 ? CSPerson = @.CSOrder :
> CSPerson > 0
> I'm using SQL Server 2000 SP4.
> Hopefully I'm missing the obvious, although any help or suggestions are
> welcome.
> -- snippet --
> IF @.CSOrder > 0 AND @.SalesOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND CSPerson = @.CSOrder
> AND SalesPerson = @.SalesOrder
> ORDER BY DateToArrive, Status
> ELSE IF @.CSOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND CSPerson = @.CSOrder
> ORDER BY DateToArrive, Status
> ELSE IF @.SalesOrder > 0
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> AND SalesPerson = @.SalesOrder
> ORDER BY DateToArrive, Status
> ELSE
> SELECT * FROM MainOrder
> LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id]
> LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id]
> WHERE JobFlow.Component=6
> AND (Status = @.InProgress
> OR Status = @.Complete
> OR Status = @.Cancelled
> OR Status = @.Acknowledged)
> ORDER BY DateToArrive, Status
>|||Ooops,
take the following line out of the code:::
SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
it was an oversight on my part, sorry,
Tony
"Tony Scott" wrote:
> Mia,
> Try something like this
>
> DECLARE @.strWHERE varchar(200)
> SET @.strWHERE = ''
> IF @.CSOrder > 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND CSPerson =
> @.CSOrder AND SalesPerson = @.SalesOrder'
> IF @.CSOrder > 0 AND @.SalesOrder <> 0 THEN @.strWHERE = 'AND CSPerson =
> @.CSOrder'
> IF @.CSOrder <> 0 AND @.SalesOrder > 0 THEN @.strWHERE = 'AND SalesPerson =
> @.SalesOrder'
> DECLARE @.sSQL varchar(2000)
> SET @.sSQL = ''
> SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
> SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] = AnCJobs.[id] '
> SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] = MainOrder.[id] '
> SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
> SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
> SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
> SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
> SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
> SET @.sSQL = @.sSQL + @.strWHERE
> SET @.sSQL = @.sSQL + ' AND SalesPerson = @.SalesOrder '
> SET @.sSQL = @.sSQL + ' AORDER BY DateToArrive, Status '
> EXEC (@.sSQL)
>
> Hope this assists,
> Tony
> "mia_cid@.hotmail.com" wrote:
>|||Thanks for the help guys,
Patrik I liked your idea, only I need:
AND CSPerson = CASE @.CSOrder WHEN > 0 THEN CSPerson ELSE @.CSOrder
Not
AND CSPerson = CASE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
Or
AND CSPerson >= CASE @.CSOrder WHEN 0 THEN CSPerson ELSE @.CSOrder
As if @.CSOrder = 0, then I want to select everything, so CSPerson needs
to be greater than 0.
Tony,
I had a couple of errors with your original code, so now have:
DECLARE @.strWHERE varchar(200)
SET @.strWHERE = ''
IF @.CSOrder > 0 AND @.SalesOrder > 0
SET @.strWHERE = 'AND CSPerson = @.CSOrder AND SalesPerson =
@.SalesOrder'
IF @.CSOrder > 0 AND @.SalesOrder <> 0
SET @.strWHERE = 'AND CSPerson = @.CSOrder'
IF @.CSOrder <> 0 AND @.SalesOrder > 0
SET @.strWHERE = 'AND SalesPerson = @.SalesOrder'
DECLARE @.sSQL varchar(2000)
SET @.sSQL = ''
SET @.sSQL = @.sSQL + ' SELECT * FROM MainOrder '
SET @.sSQL = @.sSQL + ' LEFT JOIN AnCJobs ON MainOrder.[id] =
AnCJobs.[id] '
SET @.sSQL = @.sSQL + ' LEFT JOIN JobFlow ON JobFlow.[id] =
MainOrder.[id] '
SET @.sSQL = @.sSQL + ' WHERE JobFlow.Component=6 '
SET @.sSQL = @.sSQL + ' AND (Status = @.InProgress '
SET @.sSQL = @.sSQL + ' OR Status = @.Complete '
SET @.sSQL = @.sSQL + ' OR Status = @.Cancelled '
SET @.sSQL = @.sSQL + ' OR Status = @.Acknowledged) '
SET @.sSQL = @.sSQL + @.strWHERE
SET @.sSQL = @.sSQL + ' ORDER BY DateToArrive, Status'
EXEC (@.sSQL)
Only I've getting a syntax error near the keyword 'ELSE'. If I comment
this code out and put a standard SELECT in it works okay.
Any ideas?|||You can do that.
AND CSPerson = CASE WHEN @.CSOrder > 0 THEN CSPerson ELSE @.CSOrder END
Although, it would probably be more efficient with dynamic sql in this
case.
I suggest that you use sp_executesql instaead of exec.
Read sommarskogs really good article about using dynamic sql.
http://www.sommarskog.se/dynamic_sql.html|||Thanks for that Partrik,
I just needed it the other way round:
AND CSPerson = CASE WHEN @.CSOrder > 0 THEN @.CSOrder ELSE CSPerson END
It works a treat, for the time being I'm going to stick with it as
it'll be running over a LAN, have bookmarked the site you recommended
and will have a read when I get the chance.
Thanks again to Tony for his help as well.

can the talbe name be a parameter of a query line?

the tables are created by month, and the table names are like
wt01,wt02.........
and the structure of tables are all the same,
can the talbe name be a parameter of a query line?
like select * from @.TableName ?
if not, how to make it possible to query different tables by parameter?declare @.tbl sysname
exec ('select * from ' + @.tbl)
OR
="Select * from " & Parameters!Tablename.Value
either should work
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Spirit" wrote:
> the tables are created by month, and the table names are like
> wt01,wt02.........
> and the structure of tables are all the same,
> can the talbe name be a parameter of a query line?
> like select * from @.TableName ?
> if not, how to make it possible to query different tables by parameter?
>|||but when I execute the code in the query Analysis,it reports 170 error
and there is some error near from...|||thank you for your help
it is ok now

Can someone explain this message?

Server: Msg 1204, Level 19, State 1, Line 30 The SQL Server cannot obtain a
LOCK resource at this time. Rerun your statement when there are fewer active
users or ask the system administrator to check the SQL Server lock and
memory configuration.
We can't find it in BOL.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conquerer.
Kill them all and you're a god." -- Dave MustaneMike,
Sounds like a lock issue or possibly a memory issue. What does the
following reveal?
exec sp_configure 'locks'
exec sp_configure 'min server memory'
exec sp_configure 'max server memory'
How much memory is available on the system? How much is SQL Server
currently using?
HTH
Jerry
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:uVtXrEgvFHA.3556@.TK2MSFTNGP12.phx.gbl...
> Server: Msg 1204, Level 19, State 1, Line 30 The SQL Server cannot obtain
> a LOCK resource at this time. Rerun your statement when there are fewer
> active users or ask the system administrator to check the SQL Server lock
> and memory configuration.
>
> We can't find it in BOL.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conquerer.
> Kill them all and you're a god." -- Dave Mustane
>|||Maybe here?
http://support.microsoft.com/search...SE&ast=2&mode=a
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:uVtXrEgvFHA.3556@.TK2MSFTNGP12.phx.gbl...
> Server: Msg 1204, Level 19, State 1, Line 30 The SQL Server cannot obtain
> a LOCK resource at this time. Rerun your statement when there are fewer
> active users or ask the system administrator to check the SQL Server lock
> and memory configuration.
>
> We can't find it in BOL.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conquerer.
> Kill them all and you're a god." -- Dave Mustane
>|||> http://support.microsoft.com/search...SE&ast=2&mode=a
I read the article, printed it and showed it to her. Yep, that's exactly
what she's doing.
Loop over SampleSourceArchive table, processing child records. We're
currently in an email-war about "WHY NOT JUST DO A JOIN YOU SILLY USER!!"
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conquerer.
Kill them all and you're a god." -- Dave Mustane|||> Sounds like a lock issue or possibly a memory issue. What does the
> following reveal?
> exec sp_configure 'locks'
> exec sp_configure 'min server memory'
> exec sp_configure 'max server memory'
> How much memory is available on the system? How much is SQL Server
> currently using?
Min Locks = 5000
Max Locks = 2 Billion (and some change)
Min Server Memory = 0 -> 2GB
Max Server Memory = 4MB -> 2GB
The server has 3 GB of hard RAM (Can't tell the swap file size from here)
but it looks like SQL Server is set to use anywhere between 0 and 2GB of
memory, if I have interpreted this correctly.
I think she is just applying her "expert" MS Access skills to SQL Server.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane