help! when I restore the database!

when I restore the database from a backup file.I failed
the code is "SQLstate 42000
because my sql server is chines
the message means
database "xxx" has 2 family members,but now provide 1
I think there is miss the log
how can I do?From the error msg it sounds like you striped your backup across multiple
devices and you have only specified 1 for the restore.
Provide all file locations that you backed up for that set when restoring.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Silence" <anonymous@.discussions.microsoft.com> wrote in message
news:30617EBA-03E3-492F-986A-C85E94F15F72@.microsoft.com...
> when I restore the database from a backup file.I failed.
> the code is "SQLstate 42000"
> because my sql server is chinese
> the message means:
> database "xxx" has 2 family members,but now provide 1.
> I think there is miss the log.
> how can I do?|||but the file is my friend gave me
and now cannot backup again from original database
how can I do?|||> but the file is my friend gave me.
Can you re-phrase that?
> and now cannot backup again from original database.
> how can I do?
By original, do you mean the database you tried to restore into or the one that the backup was
produced on?
Can you explain a little bit more details what happened?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Silence" <anonymous@.discussions.microsoft.com> wrote in message
news:6486E3F3-83D8-4506-91E7-F3B3B2344045@.microsoft.com...
> but the file is my friend gave me.
> and now cannot backup again from original database.
> how can I do?|||because the computer's hard disk was broken
so the data was los
he gave me the backup file is the only file.|||Just read about the RESTORE command in Books Online and do the restore based on your backup file
(from Query analyzer).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Silence" <anonymous@.discussions.microsoft.com> wrote in message
news:A1A4F50B-4ED9-4634-BD25-AB89D21C2729@.microsoft.com...
> because the computer's hard disk was broken.
> so the data was lost
> he gave me the backup file is the only file.|||which topic can I found?
can you type the command for me?
thank you!
-- Tibor Karaszi wrote: --
Just read about the RESTORE command in Books Online and do the restore based on your backup file
(from Query analyzer).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Silence" <anonymous@.discussions.microsoft.com> wrote in message
news:A1A4F50B-4ED9-4634-BD25-AB89D21C2729@.microsoft.com...
> because the computer's hard disk was broken.
> so the data was lost
> he gave me the backup file is the only file.|||The exact command to type depends on a lot of factors (the path to the backup file, the path to the
database files etc etc). In Books Online, you find the Transact-SQL Reference. Here you find the
RESTORE command documented.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"silence" <zjflyer@.hotmail.com> wrote in message
news:D32AE25A-E164-4B87-BD77-EE3B9E7C054C@.microsoft.com...
> which topic can I found?
> can you type the command for me?
> thank you!
> -- Tibor Karaszi wrote: --
> Just read about the RESTORE command in Books Online and do the restore based on your backup
file
> (from Query analyzer).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Silence" <anonymous@.discussions.microsoft.com> wrote in message
> news:A1A4F50B-4ED9-4634-BD25-AB89D21C2729@.microsoft.com...
> > because the computer's hard disk was broken.
> > so the data was lost
> > he gave me the backup file is the only file.
>
>

Help! Site crashing on data access when busy!

Clearly, my code isn't written as well as it should be. I don't understand enough about data access and could use some help.

I have several database tables, but one primary table that is the most accessed. Generally, I need to build a list from the data based on some filter. I'm using a repeater control, since all I need to display per record is a name, maybe a city or birthday, and possibly a little graphic, and my customer doesn't want a grid type of display. The filter is determined by the page requested. The exception is a search page where the user builds the filter and a grid is used to display the results.

The results always contain a link to a page that has more detail on the selected record.

What is the best way to handle this? I'm still trying to get a handle on different ways to get data and I'm not doing much with caching. Would it make sense to keep the data in memory from the page that displays the list (or search page) to the detail page? What if the detail page is accessed directly, say from a bookmark? How do I cache this?

I'm currently using strongly typed datasets.

Below is an example of what I'm doing - this is from the code behind of one of the list pages - members with birthdays this month.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim theMonth As String = DateTime.Now.ToString("MMMM")
Me.LabelMonth.Text = theMonth

Dim MemberAdapter As New WAPTableAdapters.membersTableAdapter
Repeater1.DataSource = MemberAdapter.GetBirthday("Female", DatePart("m", Today))
Repeater1.DataBind()
Repeater2.DataSource = MemberAdapter.GetBirthday("Male", DatePart("m", Today))
Repeater2.DataBind()
End Sub

Protected Sub Repeater1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles Repeater1.ItemDataBound
If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then
Dim LabelIcon As Label = CType(e.Item.FindControl("LabelIcon"), Label)
Dim person As WAP.membersRow = CType(CType(e.Item.DataItem, System.Data.DataRowView).Row, WAP.membersRow)
If System.IO.File.Exists(Server.MapPath("~/images/picts/" & person.FILE2 & ".jpg")) Then
LabelIcon.Visible = True
End If
End If
End Sub

Protected Sub Repeater2_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles Repeater2.ItemDataBound
If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then
Dim LabelIcon As Label = CType(e.Item.FindControl("LabelIcon"), Label)
Dim person As WAP.membersRow = CType(CType(e.Item.DataItem, System.Data.DataRowView).Row, WAP.membersRow)
If System.IO.File.Exists(Server.MapPath("~/images/picts/" & person.FILE2 & ".jpg")) Then
LabelIcon.Visible = True
End If
End If
End Sub

It seems pretty simple and straightforward to me, but these pages shouldn't be crashing when the site gets busy, so I have to be doing something wrong.

Diane

I would definitely cache the datasets. It takes so much load off.

|||

How do I do that?

Diane

|||

I would probably put the repeaters into a user control and use partial page caching for it. Say update once per minute should help reduce your server load, and it's really easy to implement (And since you've moved it to a user control, you can reuse it on a different page if necessary). There are obviously more efficient caching mechanisms to gain even more efficiency, but I would start with that and see if it helps since it's so easy to implement.

http://asp.net/learn/videos/video-41.aspx

|||

If Cache("malebirthday") is nothing then

Dim ds as dataset = MemberAdapter.GetBirthday("Male", DatePart("m", Today))

Repeater2.DataSource = ds

Cache.Insert("malebirthday", ds, Nothing, now.addminutes(5), timespan.zero)

else

Repeater2.DataSource = Ctype(Cache("malebirthday"), dataset)

End if

Repeater2.DataBind()

|||

Thank you! That helps tremendously.

Diane

sql

HELP! Problem in my code

I am very new to SQL and so far have overcome all my problems with reference books and the internet.

However, I am creating a report which doesn't like something in my code (I'm pretty sure it's connected with the second derived table I've added, as without it the report runs okay, although not with the results I expect to see) and I cannot figure out why.

When I try to preview the report I get this error:

Invalid Object Name 'UNIDATA_PLANNINGL_1'.

So far my code is like this:

SELECT DISTINCT
UNIDATA_PLANNING.R0 AS [Job Record], SUBSTRING(UNIDATA_PLANNINGL.R0, 1, 3) AS [Op No], UNIDATA_PLANNINGL.R1 AS [Op Description],
UNIDATA_PLANNINGL.R43 AS [T/O], UNIDATA_WIP.R0 AS [Job No], UNIDATA_PLANNING.ASSY AS Route, SUBSTRING(UNIDATA_PLANNINGL_1.R0, 1,
3) AS [Assy Op], UNIDATA_PLANNINGL_1.R1 AS [Op Description], UNIDATA_PLANNINGL_1.R43 AS [T/O], derivedtbl_1.OPTRIG1,
UNIDATA_PLANNINGL.R0 AS INVISIBLE2, UNIDATA_PLANNINGL_1.R0 AS INVISIBLE
FROM UNIDATA_PLANNINGL INNER JOIN
UNIDATA_WIPL ON UNIDATA_PLANNINGL.R0 = UNIDATA_WIPL.JOBPLKEY INNER JOIN
UNIDATA_WIP ON UNIDATA_WIPL.R2 = UNIDATA_WIP.R0 INNER JOIN
UNIDATA_PLANNING ON UNIDATA_PLANNINGL.Assy = UNIDATA_PLANNING.R0 INNER JOIN
UNIDATA_PLANNINGL AS UNIDATA_PLANNINGL_1 ON UNIDATA_PLANNING.ASSY = UNIDATA_PLANNINGL_1.Assy AND
SUBSTRING(UNIDATA_PLANNINGL.R0, 1, 3) = SUBSTRING(UNIDATA_PLANNINGL_1.R0, 1, 3) AND
UNIDATA_PLANNINGL.R43 <> UNIDATA_PLANNINGL_1.R43 INNER JOIN
(SELECT R0, SUBSTRING(R0, 1, 3) AS OP1, SUBSTRING(R0, 1, 3) + R43 AS OPTRIG1
FROM UNIDATA_PLANNINGL AS UNIDATA_PLANNINGL_2) AS derivedtbl_1 ON UNIDATA_PLANNINGL.R0 = derivedtbl_1.R0 INNER JOIN
(SELECT UNIDATA_PLANNINGL_1.R0, SUBSTRING(UNIDATA_PLANNINGL_1.R0, 1, 3) AS OP2, SUBSTRING(UNIDATA_PLANNINGL_1.R0, 1, 3)
+ UNIDATA_PLANNINGL_1.R43 AS OPTRIG2
FROM UNIDATA_PLANNINGL_1 AS UNIDATA_PLANNINGL_1_1) AS derivedtbl_2 ON UNIDATA_PLANNINGL_1.R0 = derivedtbl_2.R0
WHERE (UNIDATA_PLANNINGL.R0 LIKE N'%JOB\%') AND (NOT (UNIDATA_PLANNING.ASSY LIKE N'%2-%'))
ORDER BY [Job Record]

Could someone more experienced please explain what I have done wrong and if there is a way to fix it?

Thanks,

CPH

Derived table 2

(SELECT UNIDATA_PLANNINGL_1.R0, SUBSTRING(UNIDATA_PLANNINGL_1.R0, 1, 3) AS OP2, SUBSTRING(UNIDATA_PLANNINGL_1.R0, 1, 3)
+ UNIDATA_PLANNINGL_1.R43 AS OPTRIG2
FROM UNIDATA_PLANNINGL_1 AS UNIDATA_PLANNINGL_1_1) AS derivedtbl_2

You alias the table as UNIDATA_PLANNINGL_1_1 then reference it in the select list as UNIDATA_PLANNINGL_1

In future if you get these warnings, pull out any sub queries and test them independantly. You might also want to use something more meaningfull to alias the table names with but thats your choice

|||

Sam,

Thanks for your quick response. However, the alias UNIDATA_PLANNINGL_1_1 is something VS2005 does. If I amend it to UNIDATA_PLANNING_1 which is the table I have included VS2005 immediately changes it back to UNIDATA_PLANNINGL_1_1.

You're right about the table names, though, but unfortunately that's out of my hands.

Thanks again,

Chris

|||

also in that 2nd derived table - you select from UNIDATA_PLANNINGL_1. Is this a table? you use that name as an alias further up which AFAIK will have no scope inside the sub query.

The query should be:

(SELECT UNIDATA_PLANNINGL_1.R0, SUBSTRING(UNIDATA_PLANNINGL_1.R0, 1, 3) AS OP2, SUBSTRING(UNIDATA_PLANNINGL_1.R0, 1, 3)
+ UNIDATA_PLANNINGL_1.R43 AS OPTRIG2
FROM UNIDATA_PLANNINGL AS UNIDATA_PLANNINGL_1) AS derivedtbl_2

|||

Sam,

Thanks again for your reply and your help. As I have the table UNIDATA_PLANNINGL twice in the query VS2005 has aliased the second occurence of the table as UNIDATA_PLANNINGL_1. I have used multiple occurrences of same tables before without any problem, but this is the first time I've tried adding derived tables.

I have tried your suggestion which has cleared the error.

Your help is much appreciated, thanks again,

Chris

HELP! Need a Light Green color that exports

I have a report that needs to have a green cell (based on a condition of
course - which is decided in a code function) Here is my function:
Public Function SetColor(ByVal N As Double) As String
Select N
Case 0
Return "Salmon"
Case 1
Return "PaleGoldenrod"
Case 2
Return "LightGreen"
Case 3
Return "RoyalBlue"
Case Else
Return "Transparent"
End Select
End Function
All of the colors work *except* LightGreen. I can even *see* Light Green
in the excel color palette (notice the "space" in the excel color name) ... I
dont even *see* Salmon, Palegoldenrod or anything else there ... and yet
*those* show properly. It is only the green color that shows wrong and it
displays as GREY. Not good. I have tried other light green colors but the
only GREEN I can get to work is GREEN and that is too dark. Can someone help
with this? TIA ...Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
#80ff80.
Case 0
Return "#80ff80"
I would have used hex codes for all colours that are not plain red, yellow,
blue and green, just to be sure it was rendered OK.
Kaisa M. Lindahl Lervik
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
>I have a report that needs to have a green cell (based on a condition of
> course - which is decided in a code function) Here is my function:
> Public Function SetColor(ByVal N As Double) As String
> Select N
> Case 0
> Return "Salmon"
> Case 1
> Return "PaleGoldenrod"
> Case 2
> Return "LightGreen"
> Case 3
> Return "RoyalBlue"
> Case Else
> Return "Transparent"
> End Select
> End Function
> All of the colors work *except* LightGreen. I can even *see* Light Green
> in the excel color palette (notice the "space" in the excel color name)
> ... I
> dont even *see* Salmon, Palegoldenrod or anything else there ... and yet
> *those* show properly. It is only the green color that shows wrong and it
> displays as GREY. Not good. I have tried other light green colors but
> the
> only GREEN I can get to work is GREEN and that is too dark. Can someone
> help
> with this? TIA ...
>|||Thanks Kaisa ... I will try that. How can I find the hex codes for the
palette colors in reporting services? For instance what PaleGoldenrod is.
"Kaisa M. Lindahl Lervik" wrote:
> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
> #80ff80.
> Case 0
> Return "#80ff80"
> I would have used hex codes for all colours that are not plain red, yellow,
> blue and green, just to be sure it was rendered OK.
> Kaisa M. Lindahl Lervik
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
> >I have a report that needs to have a green cell (based on a condition of
> > course - which is decided in a code function) Here is my function:
> >
> > Public Function SetColor(ByVal N As Double) As String
> >
> > Select N
> > Case 0
> > Return "Salmon"
> > Case 1
> > Return "PaleGoldenrod"
> > Case 2
> > Return "LightGreen"
> > Case 3
> > Return "RoyalBlue"
> > Case Else
> > Return "Transparent"
> >
> > End Select
> >
> > End Function
> >
> > All of the colors work *except* LightGreen. I can even *see* Light Green
> > in the excel color palette (notice the "space" in the excel color name)
> > ... I
> > dont even *see* Salmon, Palegoldenrod or anything else there ... and yet
> > *those* show properly. It is only the green color that shows wrong and it
> > displays as GREY. Not good. I have tried other light green colors but
> > the
> > only GREEN I can get to work is GREEN and that is too dark. Can someone
> > help
> > with this? TIA ...
> >
> >
>
>|||I'm not sure, I thought you could use the .Net object browser, but I didn't
see the hex codes.
System.Drawing.Color has the color enum that you are looking for.
Ah, here's a page:
http://www.opinionatedgeek.com/DotNet/Tools/Colors/default.aspx
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:0260F30C-FB37-4186-8501-E93516FD7C89@.microsoft.com...
> Thanks Kaisa ... I will try that. How can I find the hex codes for the
> palette colors in reporting services? For instance what PaleGoldenrod is.
> "Kaisa M. Lindahl Lervik" wrote:
>> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
>> #80ff80.
>> Case 0
>> Return "#80ff80"
>> I would have used hex codes for all colours that are not plain red,
>> yellow,
>> blue and green, just to be sure it was rendered OK.
>> Kaisa M. Lindahl Lervik
>>
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
>> >I have a report that needs to have a green cell (based on a condition of
>> > course - which is decided in a code function) Here is my function:
>> >
>> > Public Function SetColor(ByVal N As Double) As String
>> >
>> > Select N
>> > Case 0
>> > Return "Salmon"
>> > Case 1
>> > Return "PaleGoldenrod"
>> > Case 2
>> > Return "LightGreen"
>> > Case 3
>> > Return "RoyalBlue"
>> > Case Else
>> > Return "Transparent"
>> >
>> > End Select
>> >
>> > End Function
>> >
>> > All of the colors work *except* LightGreen. I can even *see* Light
>> > Green
>> > in the excel color palette (notice the "space" in the excel color name)
>> > ... I
>> > dont even *see* Salmon, Palegoldenrod or anything else there ... and
>> > yet
>> > *those* show properly. It is only the green color that shows wrong and
>> > it
>> > displays as GREY. Not good. I have tried other light green colors but
>> > the
>> > only GREEN I can get to work is GREEN and that is too dark. Can
>> > someone
>> > help
>> > with this? TIA ...
>> >
>> >
>>|||Thank you Steve ... that is exactly what I needed!!! Between you and Kaisa I
am all set now. Thanks to you both!!
"Steve MunLeeuw" wrote:
> I'm not sure, I thought you could use the .Net object browser, but I didn't
> see the hex codes.
> System.Drawing.Color has the color enum that you are looking for.
> Ah, here's a page:
> http://www.opinionatedgeek.com/DotNet/Tools/Colors/default.aspx
>
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:0260F30C-FB37-4186-8501-E93516FD7C89@.microsoft.com...
> > Thanks Kaisa ... I will try that. How can I find the hex codes for the
> > palette colors in reporting services? For instance what PaleGoldenrod is.
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> >> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
> >> #80ff80.
> >>
> >> Case 0
> >> Return "#80ff80"
> >>
> >> I would have used hex codes for all colours that are not plain red,
> >> yellow,
> >> blue and green, just to be sure it was rendered OK.
> >>
> >> Kaisa M. Lindahl Lervik
> >>
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
> >> >I have a report that needs to have a green cell (based on a condition of
> >> > course - which is decided in a code function) Here is my function:
> >> >
> >> > Public Function SetColor(ByVal N As Double) As String
> >> >
> >> > Select N
> >> > Case 0
> >> > Return "Salmon"
> >> > Case 1
> >> > Return "PaleGoldenrod"
> >> > Case 2
> >> > Return "LightGreen"
> >> > Case 3
> >> > Return "RoyalBlue"
> >> > Case Else
> >> > Return "Transparent"
> >> >
> >> > End Select
> >> >
> >> > End Function
> >> >
> >> > All of the colors work *except* LightGreen. I can even *see* Light
> >> > Green
> >> > in the excel color palette (notice the "space" in the excel color name)
> >> > ... I
> >> > dont even *see* Salmon, Palegoldenrod or anything else there ... and
> >> > yet
> >> > *those* show properly. It is only the green color that shows wrong and
> >> > it
> >> > displays as GREY. Not good. I have tried other light green colors but
> >> > the
> >> > only GREEN I can get to work is GREEN and that is too dark. Can
> >> > someone
> >> > help
> >> > with this? TIA ...
> >> >
> >> >
> >>
> >>
> >>
>
>|||I tried using the hex codes and it didnt work. I thought it should too!
What could I be doing wrong? I have a conditional expression in addition to
the function in the cell ... maybe it has something to do with that:
=IIF(IsNothing(Fields!PERCENT.Value), "white", code.SetColor(
Fields!PerformanceValue.Value))
It is not setting all cells to white ... so it isnt the behavior I have seen
written about elsewhere in this newsgroup. I tried "#90EE90" and "#80ff80"
and I still see grey where green should appear. Any other suggestions?
"Steve MunLeeuw" wrote:
> I'm not sure, I thought you could use the .Net object browser, but I didn't
> see the hex codes.
> System.Drawing.Color has the color enum that you are looking for.
> Ah, here's a page:
> http://www.opinionatedgeek.com/DotNet/Tools/Colors/default.aspx
>
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:0260F30C-FB37-4186-8501-E93516FD7C89@.microsoft.com...
> > Thanks Kaisa ... I will try that. How can I find the hex codes for the
> > palette colors in reporting services? For instance what PaleGoldenrod is.
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> >> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
> >> #80ff80.
> >>
> >> Case 0
> >> Return "#80ff80"
> >>
> >> I would have used hex codes for all colours that are not plain red,
> >> yellow,
> >> blue and green, just to be sure it was rendered OK.
> >>
> >> Kaisa M. Lindahl Lervik
> >>
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
> >> >I have a report that needs to have a green cell (based on a condition of
> >> > course - which is decided in a code function) Here is my function:
> >> >
> >> > Public Function SetColor(ByVal N As Double) As String
> >> >
> >> > Select N
> >> > Case 0
> >> > Return "Salmon"
> >> > Case 1
> >> > Return "PaleGoldenrod"
> >> > Case 2
> >> > Return "LightGreen"
> >> > Case 3
> >> > Return "RoyalBlue"
> >> > Case Else
> >> > Return "Transparent"
> >> >
> >> > End Select
> >> >
> >> > End Function
> >> >
> >> > All of the colors work *except* LightGreen. I can even *see* Light
> >> > Green
> >> > in the excel color palette (notice the "space" in the excel color name)
> >> > ... I
> >> > dont even *see* Salmon, Palegoldenrod or anything else there ... and
> >> > yet
> >> > *those* show properly. It is only the green color that shows wrong and
> >> > it
> >> > displays as GREY. Not good. I have tried other light green colors but
> >> > the
> >> > only GREEN I can get to work is GREEN and that is too dark. Can
> >> > someone
> >> > help
> >> > with this? TIA ...
> >> >
> >> >
> >>
> >>
> >>
>
>|||I tried it and it actually did not work. I think there is something else I
need to do to fix it in addition. I have a conditional expression deciding
whether to perform the function (because I could have nulls in the cell but I
am replacing them with the words "No Data") So the expression in the data
cell is this (it is a matrix cell)
=IIF(IsNothing(Fields!PERCENT.Value), "No Data",
Sum(Fields!PERCENT.Value)/100 )
and the expression for the background color to be set for this cell is this:
=IIF(IsNothing(Fields!PERCENT.Value), "white", code.SetColor(
Fields!PerformanceValue.Value))
and the Function is this:
Public Function SetColor(ByVal N As Double) As String
Select N
Case 0
Return "Salmon"
Case 1
Return "PaleGoldenrod"
Case 2
Return "LightGreen"
Case 3
Return "RoyalBlue"
Case Else
Return "Transparent"
End Select
End Function
I tried changing the function to return values "#90EE90" etc and I altered
the IIF statement to read "#FFFFFF" instead of "white" ... but that didnt
work - it rendered correctly except didnt export to excel and show green ...
it still showed grey. I did get a warning message "The background color
expression used in textbox â'textbox8â' returned a data type that is not valid"
- textbox 8 is the cell I am using. I know when I have used hex codes for
colors in the past ... I dont believe I enclosed them in quotes in the cell,
but I cant return #90EE90 from my function without putting quotes around it,
can I? What would you do next?
"Kaisa M. Lindahl Lervik" wrote:
> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
> #80ff80.
> Case 0
> Return "#80ff80"
> I would have used hex codes for all colours that are not plain red, yellow,
> blue and green, just to be sure it was rendered OK.
> Kaisa M. Lindahl Lervik
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
> >I have a report that needs to have a green cell (based on a condition of
> > course - which is decided in a code function) Here is my function:
> >
> > Public Function SetColor(ByVal N As Double) As String
> >
> > Select N
> > Case 0
> > Return "Salmon"
> > Case 1
> > Return "PaleGoldenrod"
> > Case 2
> > Return "LightGreen"
> > Case 3
> > Return "RoyalBlue"
> > Case Else
> > Return "Transparent"
> >
> > End Select
> >
> > End Function
> >
> > All of the colors work *except* LightGreen. I can even *see* Light Green
> > in the excel color palette (notice the "space" in the excel color name)
> > ... I
> > dont even *see* Salmon, Palegoldenrod or anything else there ... and yet
> > *those* show properly. It is only the green color that shows wrong and it
> > displays as GREY. Not good. I have tried other light green colors but
> > the
> > only GREEN I can get to work is GREEN and that is too dark. Can someone
> > help
> > with this? TIA ...
> >
> >
>
>|||I will report back that I now have it working. I cant tell you what fixed
it ... maybe changing the case of the letters in hex? Thats about all I did.
Here is the expression that works.
=IIF(IsNothing(Fields!PERCENT.Value), "#ffffff", code.SetColor(
Fields!PerformanceValue.Value))
AND the function code:
Public Function SetColor(ByVal N As Double) As String
Select N
Case 0
Return "#fa8072"
Case 1
Return "#eee8aa"
Case 2
Return "#c0ffc0"
Case 3
Return "#4169e1"
Case Else
Return "#ffffff"
End Select
End Function
Thanks for all of your help!! I guess case was important in this CASE ...
pun intended!
"MJT" wrote:
> I tried using the hex codes and it didnt work. I thought it should too!
> What could I be doing wrong? I have a conditional expression in addition to
> the function in the cell ... maybe it has something to do with that:
> =IIF(IsNothing(Fields!PERCENT.Value), "white", code.SetColor(
> Fields!PerformanceValue.Value))
> It is not setting all cells to white ... so it isnt the behavior I have seen
> written about elsewhere in this newsgroup. I tried "#90EE90" and "#80ff80"
> and I still see grey where green should appear. Any other suggestions?
> "Steve MunLeeuw" wrote:
> > I'm not sure, I thought you could use the .Net object browser, but I didn't
> > see the hex codes.
> >
> > System.Drawing.Color has the color enum that you are looking for.
> > Ah, here's a page:
> >
> > http://www.opinionatedgeek.com/DotNet/Tools/Colors/default.aspx
> >
> >
> >
> >
> > "MJT" <MJT@.discussions.microsoft.com> wrote in message
> > news:0260F30C-FB37-4186-8501-E93516FD7C89@.microsoft.com...
> > > Thanks Kaisa ... I will try that. How can I find the hex codes for the
> > > palette colors in reporting services? For instance what PaleGoldenrod is.
> > >
> > > "Kaisa M. Lindahl Lervik" wrote:
> > >
> > >> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
> > >> #80ff80.
> > >>
> > >> Case 0
> > >> Return "#80ff80"
> > >>
> > >> I would have used hex codes for all colours that are not plain red,
> > >> yellow,
> > >> blue and green, just to be sure it was rendered OK.
> > >>
> > >> Kaisa M. Lindahl Lervik
> > >>
> > >>
> > >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> > >> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
> > >> >I have a report that needs to have a green cell (based on a condition of
> > >> > course - which is decided in a code function) Here is my function:
> > >> >
> > >> > Public Function SetColor(ByVal N As Double) As String
> > >> >
> > >> > Select N
> > >> > Case 0
> > >> > Return "Salmon"
> > >> > Case 1
> > >> > Return "PaleGoldenrod"
> > >> > Case 2
> > >> > Return "LightGreen"
> > >> > Case 3
> > >> > Return "RoyalBlue"
> > >> > Case Else
> > >> > Return "Transparent"
> > >> >
> > >> > End Select
> > >> >
> > >> > End Function
> > >> >
> > >> > All of the colors work *except* LightGreen. I can even *see* Light
> > >> > Green
> > >> > in the excel color palette (notice the "space" in the excel color name)
> > >> > ... I
> > >> > dont even *see* Salmon, Palegoldenrod or anything else there ... and
> > >> > yet
> > >> > *those* show properly. It is only the green color that shows wrong and
> > >> > it
> > >> > displays as GREY. Not good. I have tried other light green colors but
> > >> > the
> > >> > only GREEN I can get to work is GREEN and that is too dark. Can
> > >> > someone
> > >> > help
> > >> > with this? TIA ...
> > >> >
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >|||Hmmm, I wonder if it's running into a problem with the IIF(). If you put
the output out as text, rather than trying to set the color, are you getting
the color hex values you expect? If that's working correctly, try not using
the expression and seeing if you can set the background color to the light
green value that isn't working....If that is failing, then you know it's
not the expression and it's a bug or limitation in the excel rendering
engine. I'm using...was using LightGreen ( I swear) and it was rendering
to PDF just fine, not sure about Excel, never tested. I'd try exporting to
PDF and see if that works. If this helps, here's the code I'm using to set
the color of the chart bars.
public static Int32 PickColor(int index)
{
Int32 ReturnVal = Color.Tan.ToArgb();
switch (index)
{
case 0:
ReturnVal = Color.MediumSlateBlue.ToArgb();
break;
case 1:
ReturnVal = Color.MediumVioletRed.ToArgb();
break;
case 2:
ReturnVal = Color.LightYellow.ToArgb();
break;
case 3:
ReturnVal = Color.PaleTurquoise.ToArgb();
break;
case 4:
ReturnVal = Color.Purple.ToArgb();
break;
}
return ReturnVal;
}
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:CE2F86F8-6482-4CB0-A8A7-3D3A8E13FD06@.microsoft.com...
>I tried it and it actually did not work. I think there is something else I
> need to do to fix it in addition. I have a conditional expression
> deciding
> whether to perform the function (because I could have nulls in the cell
> but I
> am replacing them with the words "No Data") So the expression in the data
> cell is this (it is a matrix cell)
> =IIF(IsNothing(Fields!PERCENT.Value), "No Data",
> Sum(Fields!PERCENT.Value)/100 )
> and the expression for the background color to be set for this cell is
> this:
> =IIF(IsNothing(Fields!PERCENT.Value), "white", code.SetColor(
> Fields!PerformanceValue.Value))
> and the Function is this:
> Public Function SetColor(ByVal N As Double) As String
> Select N
> Case 0
> Return "Salmon"
> Case 1
> Return "PaleGoldenrod"
> Case 2
> Return "LightGreen"
> Case 3
> Return "RoyalBlue"
> Case Else
> Return "Transparent"
> End Select
> End Function
> I tried changing the function to return values "#90EE90" etc and I altered
> the IIF statement to read "#FFFFFF" instead of "white" ... but that didnt
> work - it rendered correctly except didnt export to excel and show green
> ...
> it still showed grey. I did get a warning message "The background color
> expression used in textbox 'textbox8' returned a data type that is not
> valid"
> - textbox 8 is the cell I am using. I know when I have used hex codes for
> colors in the past ... I dont believe I enclosed them in quotes in the
> cell,
> but I cant return #90EE90 from my function without putting quotes around
> it,
> can I? What would you do next?
>
> "Kaisa M. Lindahl Lervik" wrote:
>> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
>> #80ff80.
>> Case 0
>> Return "#80ff80"
>> I would have used hex codes for all colours that are not plain red,
>> yellow,
>> blue and green, just to be sure it was rendered OK.
>> Kaisa M. Lindahl Lervik
>>
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
>> >I have a report that needs to have a green cell (based on a condition of
>> > course - which is decided in a code function) Here is my function:
>> >
>> > Public Function SetColor(ByVal N As Double) As String
>> >
>> > Select N
>> > Case 0
>> > Return "Salmon"
>> > Case 1
>> > Return "PaleGoldenrod"
>> > Case 2
>> > Return "LightGreen"
>> > Case 3
>> > Return "RoyalBlue"
>> > Case Else
>> > Return "Transparent"
>> >
>> > End Select
>> >
>> > End Function
>> >
>> > All of the colors work *except* LightGreen. I can even *see* Light
>> > Green
>> > in the excel color palette (notice the "space" in the excel color name)
>> > ... I
>> > dont even *see* Salmon, Palegoldenrod or anything else there ... and
>> > yet
>> > *those* show properly. It is only the green color that shows wrong and
>> > it
>> > displays as GREY. Not good. I have tried other light green colors but
>> > the
>> > only GREEN I can get to work is GREEN and that is too dark. Can
>> > someone
>> > help
>> > with this? TIA ...
>> >
>> >
>>|||Steve,
read my post that is a couple of lines up in the thread (2/24) that tells
you I got it working. The only thing I can figure out is it might be the
case I was using. I tried many things ... I took out the IIF statement to
see what would export correctly in hex and for whatever reason it didnt seem
to like #90EE90 for light green but it accepted #C0FFCO ... and I noticed in
reporting services that when I chose a hex code from their custom colors ( I
started with what they had predefined before going to the "web colors" tab)
it made the hex value lower case #c0ffc0 ... and it exported and showed
correctly. So I changed the case of the hex letters in my function and it
seemed to work. Strange. I am snipping your code though. Must be c# ...
doesnt look like vb .net. I love it when people submit examples of code ...
I am just learning and it is so helpful to collect examples. Thanks for your
help! remember to use lower case hex letters ... lol
"Steve MunLeeuw" wrote:
> Hmmm, I wonder if it's running into a problem with the IIF(). If you put
> the output out as text, rather than trying to set the color, are you getting
> the color hex values you expect? If that's working correctly, try not using
> the expression and seeing if you can set the background color to the light
> green value that isn't working....If that is failing, then you know it's
> not the expression and it's a bug or limitation in the excel rendering
> engine. I'm using...was using LightGreen ( I swear) and it was rendering
> to PDF just fine, not sure about Excel, never tested. I'd try exporting to
> PDF and see if that works. If this helps, here's the code I'm using to set
> the color of the chart bars.
> public static Int32 PickColor(int index)
> {
> Int32 ReturnVal = Color.Tan.ToArgb();
> switch (index)
> {
> case 0:
> ReturnVal = Color.MediumSlateBlue.ToArgb();
> break;
> case 1:
> ReturnVal = Color.MediumVioletRed.ToArgb();
> break;
> case 2:
> ReturnVal = Color.LightYellow.ToArgb();
> break;
> case 3:
> ReturnVal = Color.PaleTurquoise.ToArgb();
> break;
> case 4:
> ReturnVal = Color.Purple.ToArgb();
> break;
> }
> return ReturnVal;
> }
>
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:CE2F86F8-6482-4CB0-A8A7-3D3A8E13FD06@.microsoft.com...
> >I tried it and it actually did not work. I think there is something else I
> > need to do to fix it in addition. I have a conditional expression
> > deciding
> > whether to perform the function (because I could have nulls in the cell
> > but I
> > am replacing them with the words "No Data") So the expression in the data
> > cell is this (it is a matrix cell)
> >
> > =IIF(IsNothing(Fields!PERCENT.Value), "No Data",
> > Sum(Fields!PERCENT.Value)/100 )
> >
> > and the expression for the background color to be set for this cell is
> > this:
> >
> > =IIF(IsNothing(Fields!PERCENT.Value), "white", code.SetColor(
> > Fields!PerformanceValue.Value))
> >
> > and the Function is this:
> >
> > Public Function SetColor(ByVal N As Double) As String
> >
> > Select N
> > Case 0
> > Return "Salmon"
> > Case 1
> > Return "PaleGoldenrod"
> > Case 2
> > Return "LightGreen"
> > Case 3
> > Return "RoyalBlue"
> > Case Else
> > Return "Transparent"
> >
> > End Select
> >
> > End Function
> >
> > I tried changing the function to return values "#90EE90" etc and I altered
> > the IIF statement to read "#FFFFFF" instead of "white" ... but that didnt
> > work - it rendered correctly except didnt export to excel and show green
> > ...
> > it still showed grey. I did get a warning message "The background color
> > expression used in textbox 'textbox8' returned a data type that is not
> > valid"
> > - textbox 8 is the cell I am using. I know when I have used hex codes for
> > colors in the past ... I dont believe I enclosed them in quotes in the
> > cell,
> > but I cant return #90EE90 from my function without putting quotes around
> > it,
> > can I? What would you do next?
> >
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> >> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
> >> #80ff80.
> >>
> >> Case 0
> >> Return "#80ff80"
> >>
> >> I would have used hex codes for all colours that are not plain red,
> >> yellow,
> >> blue and green, just to be sure it was rendered OK.
> >>
> >> Kaisa M. Lindahl Lervik
> >>
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
> >> >I have a report that needs to have a green cell (based on a condition of
> >> > course - which is decided in a code function) Here is my function:
> >> >
> >> > Public Function SetColor(ByVal N As Double) As String
> >> >
> >> > Select N
> >> > Case 0
> >> > Return "Salmon"
> >> > Case 1
> >> > Return "PaleGoldenrod"
> >> > Case 2
> >> > Return "LightGreen"
> >> > Case 3
> >> > Return "RoyalBlue"
> >> > Case Else
> >> > Return "Transparent"
> >> >
> >> > End Select
> >> >
> >> > End Function
> >> >
> >> > All of the colors work *except* LightGreen. I can even *see* Light
> >> > Green
> >> > in the excel color palette (notice the "space" in the excel color name)
> >> > ... I
> >> > dont even *see* Salmon, Palegoldenrod or anything else there ... and
> >> > yet
> >> > *those* show properly. It is only the green color that shows wrong and
> >> > it
> >> > displays as GREY. Not good. I have tried other light green colors but
> >> > the
> >> > only GREEN I can get to work is GREEN and that is too dark. Can
> >> > someone
> >> > help
> >> > with this? TIA ...
> >> >
> >> >
> >>
> >>
> >>
>
>

Help! Call a code block based on parameter name

I have a column heading that needs to call a function based on the value
contained in the heading (which is a parameter selected by the user).
Example: they can choose system, database, network or application. I have
functions which do different translations and the functions are named
translateSystem, translateApplication, translateDatabase, etc.
I tried to do this by adding a textbox (which I named tbResType) with the
following code in it:
IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
IIF(Parameters!ResType.value = "process",
"translateProcess",IIF(Parameters!ResType.value = "database",
"translateDatabase", IIF(Parameters!ResType.value = "application",
"translateApplication", "translateSystem"))))
Then I tried to use that value in the column heading to call the appropriate
code block (the function translates into multiple languages so its parm is
the language value the user chooses)
=code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
anyway ... the translation for the first textbox works fine and I see
translateProcess when I entered process for the ResType parm but the column
heading gives me an error ==> The value expression for the textbox â'textbox6â'
contains an error: [BC30203] Identifier expected.
Any clues?You may have to write a wrapper around you actual calls within your code
block. This wrapper will accept the bResType parameter and the sub call the
appropriate routines from there.
Craig
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
>I have a column heading that needs to call a function based on the value
> contained in the heading (which is a parameter selected by the user).
> Example: they can choose system, database, network or application. I have
> functions which do different translations and the functions are named
> translateSystem, translateApplication, translateDatabase, etc.
> I tried to do this by adding a textbox (which I named tbResType) with the
> following code in it:
> IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
> IIF(Parameters!ResType.value = "process",
> "translateProcess",IIF(Parameters!ResType.value = "database",
> "translateDatabase", IIF(Parameters!ResType.value = "application",
> "translateApplication", "translateSystem"))))
> Then I tried to use that value in the column heading to call the
> appropriate
> code block (the function translates into multiple languages so its parm is
> the language value the user chooses)
> =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
> anyway ... the translation for the first textbox works fine and I see
> translateProcess when I entered process for the ResType parm but the
> column
> heading gives me an error ==> The value expression for the textbox
> 'textbox6'
> contains an error: [BC30203] Identifier expected.
> Any clues?
>|||Thanks Craig ... I am not sure how to do that. Is there an example somewhere
(simple example...) I am very new with vb .net
"Craig" wrote:
> You may have to write a wrapper around you actual calls within your code
> block. This wrapper will accept the bResType parameter and the sub call the
> appropriate routines from there.
> Craig
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
> >I have a column heading that needs to call a function based on the value
> > contained in the heading (which is a parameter selected by the user).
> > Example: they can choose system, database, network or application. I have
> > functions which do different translations and the functions are named
> > translateSystem, translateApplication, translateDatabase, etc.
> >
> > I tried to do this by adding a textbox (which I named tbResType) with the
> > following code in it:
> > IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
> > IIF(Parameters!ResType.value = "process",
> > "translateProcess",IIF(Parameters!ResType.value = "database",
> > "translateDatabase", IIF(Parameters!ResType.value = "application",
> > "translateApplication", "translateSystem"))))
> >
> > Then I tried to use that value in the column heading to call the
> > appropriate
> > code block (the function translates into multiple languages so its parm is
> > the language value the user chooses)
> >
> > =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
> >
> > anyway ... the translation for the first textbox works fine and I see
> > translateProcess when I entered process for the ResType parm but the
> > column
> > heading gives me an error ==> The value expression for the textbox
> > 'textbox6'
> > contains an error: [BC30203] Identifier expected.
> >
> > Any clues?
> >
>
>|||Put this in your code...
Public Function GetTranslation(ByVal Application As String, ByVal Language
As String) As String
Select Case Application
Case "nw Interface"
Return translateNetwork(Language)
Case "process"
Return translateProcess(Language)
Case "database"
Return translateDatabase(Language)
Case "application"
Return translateApplication(Language)
End Select
Return ""
End Function
Then in your header just put
=code.GetTranslation(Parameters!Language.Value)
Craig
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:D673084A-5BCF-4D12-95F0-B897C11290DA@.microsoft.com...
> Thanks Craig ... I am not sure how to do that. Is there an example
> somewhere
> (simple example...) I am very new with vb .net
> "Craig" wrote:
>> You may have to write a wrapper around you actual calls within your code
>> block. This wrapper will accept the bResType parameter and the sub call
>> the
>> appropriate routines from there.
>> Craig
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
>> >I have a column heading that needs to call a function based on the value
>> > contained in the heading (which is a parameter selected by the user).
>> > Example: they can choose system, database, network or application. I
>> > have
>> > functions which do different translations and the functions are named
>> > translateSystem, translateApplication, translateDatabase, etc.
>> >
>> > I tried to do this by adding a textbox (which I named tbResType) with
>> > the
>> > following code in it:
>> > IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
>> > IIF(Parameters!ResType.value = "process",
>> > "translateProcess",IIF(Parameters!ResType.value = "database",
>> > "translateDatabase", IIF(Parameters!ResType.value = "application",
>> > "translateApplication", "translateSystem"))))
>> >
>> > Then I tried to use that value in the column heading to call the
>> > appropriate
>> > code block (the function translates into multiple languages so its parm
>> > is
>> > the language value the user chooses)
>> >
>> > =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
>> >
>> > anyway ... the translation for the first textbox works fine and I see
>> > translateProcess when I entered process for the ResType parm but the
>> > column
>> > heading gives me an error ==> The value expression for the textbox
>> > 'textbox6'
>> > contains an error: [BC30203] Identifier expected.
>> >
>> > Any clues?
>> >
>>|||Sorry fogot one other parameter in the call...
you need...
=code.GetTranslation(Parameters!ResType.Value,Parameters!Language.Value)
Craig
"Craig" <craigm_richardson@.hotmail.com> wrote in message
news:eEjrgGaPGHA.3460@.TK2MSFTNGP15.phx.gbl...
> Put this in your code...
> Public Function GetTranslation(ByVal Application As String, ByVal Language
> As String) As String
> Select Case Application
> Case "nw Interface"
> Return translateNetwork(Language)
> Case "process"
> Return translateProcess(Language)
> Case "database"
> Return translateDatabase(Language)
> Case "application"
> Return translateApplication(Language)
> End Select
> Return ""
> End Function
> Then in your header just put
> =code.GetTranslation(Parameters!Language.Value)
>
> Craig
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:D673084A-5BCF-4D12-95F0-B897C11290DA@.microsoft.com...
>> Thanks Craig ... I am not sure how to do that. Is there an example
>> somewhere
>> (simple example...) I am very new with vb .net
>> "Craig" wrote:
>> You may have to write a wrapper around you actual calls within your code
>> block. This wrapper will accept the bResType parameter and the sub call
>> the
>> appropriate routines from there.
>> Craig
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
>> >I have a column heading that needs to call a function based on the
>> >value
>> > contained in the heading (which is a parameter selected by the user).
>> > Example: they can choose system, database, network or application. I
>> > have
>> > functions which do different translations and the functions are named
>> > translateSystem, translateApplication, translateDatabase, etc.
>> >
>> > I tried to do this by adding a textbox (which I named tbResType) with
>> > the
>> > following code in it:
>> > IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
>> > IIF(Parameters!ResType.value = "process",
>> > "translateProcess",IIF(Parameters!ResType.value = "database",
>> > "translateDatabase", IIF(Parameters!ResType.value = "application",
>> > "translateApplication", "translateSystem"))))
>> >
>> > Then I tried to use that value in the column heading to call the
>> > appropriate
>> > code block (the function translates into multiple languages so its
>> > parm is
>> > the language value the user chooses)
>> >
>> > =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
>> >
>> > anyway ... the translation for the first textbox works fine and I see
>> > translateProcess when I entered process for the ResType parm but the
>> > column
>> > heading gives me an error ==> The value expression for the textbox
>> > 'textbox6'
>> > contains an error: [BC30203] Identifier expected.
>> >
>> > Any clues?
>> >
>>
>|||I'm sorry ... I forgot to reply that it worked! Thank you very much and I
have rated it as such.
"Craig" wrote:
> Sorry fogot one other parameter in the call...
> you need...
> =code.GetTranslation(Parameters!ResType.Value,Parameters!Language.Value)
> Craig
> "Craig" <craigm_richardson@.hotmail.com> wrote in message
> news:eEjrgGaPGHA.3460@.TK2MSFTNGP15.phx.gbl...
> > Put this in your code...
> >
> > Public Function GetTranslation(ByVal Application As String, ByVal Language
> > As String) As String
> >
> > Select Case Application
> >
> > Case "nw Interface"
> >
> > Return translateNetwork(Language)
> >
> > Case "process"
> >
> > Return translateProcess(Language)
> >
> > Case "database"
> >
> > Return translateDatabase(Language)
> >
> > Case "application"
> >
> > Return translateApplication(Language)
> >
> > End Select
> >
> > Return ""
> >
> > End Function
> >
> > Then in your header just put
> >
> > =code.GetTranslation(Parameters!Language.Value)
> >
> >
> > Craig
> >
> > "MJT" <MJT@.discussions.microsoft.com> wrote in message
> > news:D673084A-5BCF-4D12-95F0-B897C11290DA@.microsoft.com...
> >> Thanks Craig ... I am not sure how to do that. Is there an example
> >> somewhere
> >> (simple example...) I am very new with vb .net
> >>
> >> "Craig" wrote:
> >>
> >> You may have to write a wrapper around you actual calls within your code
> >> block. This wrapper will accept the bResType parameter and the sub call
> >> the
> >> appropriate routines from there.
> >>
> >> Craig
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:C65EE9E3-506A-46EE-A362-02862013E6BC@.microsoft.com...
> >> >I have a column heading that needs to call a function based on the
> >> >value
> >> > contained in the heading (which is a parameter selected by the user).
> >> > Example: they can choose system, database, network or application. I
> >> > have
> >> > functions which do different translations and the functions are named
> >> > translateSystem, translateApplication, translateDatabase, etc.
> >> >
> >> > I tried to do this by adding a textbox (which I named tbResType) with
> >> > the
> >> > following code in it:
> >> > IIF(Parameters!ResType.value = "nw interface", "translateNetwork",
> >> > IIF(Parameters!ResType.value = "process",
> >> > "translateProcess",IIF(Parameters!ResType.value = "database",
> >> > "translateDatabase", IIF(Parameters!ResType.value = "application",
> >> > "translateApplication", "translateSystem"))))
> >> >
> >> > Then I tried to use that value in the column heading to call the
> >> > appropriate
> >> > code block (the function translates into multiple languages so its
> >> > parm is
> >> > the language value the user chooses)
> >> >
> >> > =code.(ReportItems!tbResType.Value)( Parameters!Language.Value)
> >> >
> >> > anyway ... the translation for the first textbox works fine and I see
> >> > translateProcess when I entered process for the ResType parm but the
> >> > column
> >> > heading gives me an error ==> The value expression for the textbox
> >> > 'textbox6'
> >> > contains an error: [BC30203] Identifier expected.
> >> >
> >> > Any clues?
> >> >
> >>
> >>
> >>
> >
> >
>
>

help! all of a sudden can't connect to my db

hello,
i've been running fine with the same msft sql server set up for over a year
now. each night i run some vba code which connects with the db and does some
data updates. all of a sudden i'm having trouble communicating with the db.
when i attempt to connect to the db via query analyzer, i get the message:
Server: Msg 924, Level 14, State 1, Line 1
Database 'artorius' is already open and can only have one user at a time.
what the dillyo? maybe some setting got changed? how can i change it back?
hopefully this is not a symptom of a larger problem. anyway, would
appreciate any suggestions to get this working again.
appreciated,
matthew
nevermind, i switched the setting back. sorry for the panic. i have no idea
how the setting got switched in the first place though.
"matthew c. harad" wrote:

> hello,
> i've been running fine with the same msft sql server set up for over a year
> now. each night i run some vba code which connects with the db and does some
> data updates. all of a sudden i'm having trouble communicating with the db.
> when i attempt to connect to the db via query analyzer, i get the message:
> Server: Msg 924, Level 14, State 1, Line 1
> Database 'artorius' is already open and can only have one user at a time.
> what the dillyo? maybe some setting got changed? how can i change it back?
> hopefully this is not a symptom of a larger problem. anyway, would
> appreciate any suggestions to get this working again.
> appreciated,
> matthew

help! all of a sudden can't connect to my db

hello,
i've been running fine with the same msft sql server set up for over a year
now. each night i run some vba code which connects with the db and does som
e
data updates. all of a sudden i'm having trouble communicating with the db.
when i attempt to connect to the db via query analyzer, i get the message:
Server: Msg 924, Level 14, State 1, Line 1
Database 'artorius' is already open and can only have one user at a time.
what the dillyo? maybe some setting got changed? how can i change it back?
hopefully this is not a symptom of a larger problem. anyway, would
appreciate any suggestions to get this working again.
appreciated,
matthewnevermind, i switched the setting back. sorry for the panic. i have no ide
a
how the setting got switched in the first place though.
"matthew c. harad" wrote:

> hello,
> i've been running fine with the same msft sql server set up for over a yea
r
> now. each night i run some vba code which connects with the db and does s
ome
> data updates. all of a sudden i'm having trouble communicating with the d
b.
> when i attempt to connect to the db via query analyzer, i get the message:
> Server: Msg 924, Level 14, State 1, Line 1
> Database 'artorius' is already open and can only have one user at a time.
> what the dillyo? maybe some setting got changed? how can i change it bac
k?
> hopefully this is not a symptom of a larger problem. anyway, would
> appreciate any suggestions to get this working again.
> appreciated,
> matthew

Help with what should be a simple delete?

I have the following code that will not work... It produces ths message:

DELETE * from Customer where Cust_ID = MATWAR61849

Error #-2147217904

Error desc. -> [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Cust_ID=request.querystring("ID")
SQLstmt = "DELETE * from Customer where Cust_ID = " & Cust_ID
Set RS = conn.execute(SQLstmt)

I think the problem is that the variable is not being interpreted as a string, but rather a number.

In the database, Cust_ID is defined as a text entry.

What do I need to do to fix this? Yes, I know I am a newbie.

Thanks mattStrings have to be enclosed in single quotes in SQL:

DELETE * from Customer where Cust_ID = 'MATWAR61849'

Help with TSQL where clause syntax

Hey everyone I want a where clause based off a variable input and I'm having some trouble with the syntax. My current code(incorrect) is below but it shows what I am aiming for. Does anyone have any suggestions?

Code Snippet

(CASE
WHEN @.inIndustry = 'ALL' THEN
WHERE EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE
WHERE OpportunityID = op.OpportunituID AND monthyear LIKE '%' + @.Year1)
ELSE
WHERE (acIF.Industry <> 'Defense' OR acIF.Industry IS NULL)
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE
WHERE OpportunityID = op.OpportunituID AND monthyear LIKE '%' + @.Year1) END)

Maybe this:

Code Snippet

(CASE

WHEN @.inIndustry ='ALL'AND

EXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE

CASEWHEN(acIF.Industry <>'Defense'OR acIF.Industry ISNULL)

ANDEXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE 0

END--inner case

END)-- outer case

|||Hey. Thanks for the help but I dont see how that would work. I'm trying to make the WHERE clause dynamic in a way. It's all based off whatever the var @.inIndustry is equal to. So if @.inIndustry is equal to 'ALL' then is uses a certain where clause. I dont want to base the equivalence off of the entire statement there.|||

Code Snippet

WHERE(CASE

WHEN @.inIndustry ='ALL'AND

EXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

WHEN(acIF.Industry <>'Defense'OR acIF.Industry ISNULL)

ANDEXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE 0

END)

= 1

|||Ahh I see how it works now. I plugged it in and it works like a charm. Thanks for the help!
|||

No problem; my pleasure.

Much appreciated if you can mark the solution as the answer

|||Actually it didnt work exactly as it should... This is the code i have at the moment.

Code Snippet

WHERE
(CASE
--MASTER
WHEN @.inIndustry = 'ALL'
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)

THEN 1

--ENERGY AND UTILITIES
WHEN @.inIndustry = 'EU' AND acIf.Industry = 'Energy and Utilities'
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)
THEN 1

-- NONGOV - E&U + OTHER
WHEN (acIF.Industry <> 'Defense' OR acIF.Industry IS NULL)
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)
THEN 1

ELSE 0
END) = 1

Now comparing original code and the code i wrote aboveI know that it performs the the last case statement no matter what i input for @.inIndustry. Is there any way to fix this?
|||

Nest CASE statements

CASE WHEN @.inIndustry = 'ALL' ..

ELSE

CASE WHEN ...

ELSE

END

END

|||I ended up adding checks to the last statement to make sure @.inIndustry wasn't equal to EU, ALL etc.. and it works fine now.

Thanks for all the help!