New to CR

I'm coming in on the back end of a project and I don't understand the following record selection formula. Would someone please explain what it's doing?

if {?@.rptfilter} = 0 then
(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else
if {?@.rptfilter} = 1 then
({CAS_SP_SolnLosses;1.Contractor} = "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} = "CAJV")
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else
if {?@.rptfilter} = 2 then
({CAS_SP_SolnLosses;1.Contractor} <> "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} <> "CAJV")
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})This is not an answer to your question, but at least in earlier versions (up to version 8.5) Crystal reports is not able to create an SQL stement based on control structures (If-Then-Else) instead you should convert the record selection formula to a boolean statement using ands and ors and braces. What happens if you don't do this is that Crystal loads the entire answer set to your computer and makes the record selection locally. This has at least two major disadvantages:
1. The query is processed on the database server as a full table join and the query can not make use of any indexes. This can cause performance problems to any DBMS system
2. You cause a lot of network traffic moving unneeded data over the network

This has one implication, which is poor performance.

What concerns your code I have a few suggestions:

(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
could be removed in my opinion, because it compares a field to both null and not null

after this you basically create different joins based on different values of
{?@.rptfilter}

Hope this gives you some clues.

- Jukka|||This is not an answer to your question, but at least in earlier versions (up to version 8.5) Crystal reports is not able to create an SQL stement based on control structures (If-Then-Else) instead you should convert the record selection formula to a boolean statement using ands and ors and braces. What happens if you don't do this is that Crystal loads the entire answer set to your computer and makes the record selection locally. This has at least two major disadvantages:
1. The query is processed on the database server as a full table join and the query can not make use of any indexes. This can cause performance problems to any DBMS system
2. You cause a lot of network traffic moving unneeded data over the network

This has one implication, which is poor performance.

What concerns your code I have a few suggestions:

(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
could be removed in my opinion, because it compares a field to both null and not null

after this you basically create different joins based on different values of
{?@.rptfilter}

Hope this gives you some clues.

- Jukka

I agree that line you mentioned could be deleted. My problem is understanding the syntax of this *ugly* formula. Unfortunately, the developer that wrote this is long gone and, like I mentioned earlier, it's been dumped in my lap. I've looked over the .pdf's on the CD hoping to find some help, no luck. Are there any other sources which would help me get my head around understanding what this code is doing?

TIA

BTW, is there a way to step thru the formula code with a debugger?|||Hello,

I'm doing project using Access as backend and VB6 FrontEnd. I have the following problem during compilation. I'm using Runtime Activex lib of CR 8.5.

Error: Method or Data member not found

===> Private Sub mnu_stock_Click()
With Form1.CrystalReport1
.DataFiles(0) = App.Path & "\MyDB.MDB"
.ReportFileName = App.Path & "\Report\RPT_AVA_STOCK.rpt"
.username = "Admin"
.Password = "1010101010" & Chr(10) & "1010101010"
.Action = 1
.PageZoom (100)
End With
End Sub

Thank you,

Regards,

Niranjan Dixit|||I agree that line you mentioned could be deleted. My problem is understanding the syntax of this *ugly* formula. Unfortunately, the developer that wrote this is long gone and, like I mentioned earlier, it's been dumped in my lap. I've looked over the .pdf's on the CD hoping to find some help, no luck. Are there any other sources which would help me get my head around understanding what this code is doing?

TIA

BTW, is there a way to step thru the formula code with a debugger?

Here is a try (the syntax is slightly different to what I'm used to using Oracle):
You have the following parameters in your report controlling the outcome:
rptfilter
typeid

comments are marked with a "-"

Fuurther I would still make sure that I have all parenthesis in the right places. I would include an extra round of parenthesis where rptfilter changes values

and the code it self:
if {?@.rptfilter} = 0 then
- rpt filter = 0

(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
- This section does nothing

and
(if {?@.typeid} = 0 then
- rptfilter = 0 and typeid = 0

ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
- When rptfilter = 0 and typeid = 0 then only record having CAS_SP_SolnLosses;1.Typevalue = "ANY" are included

else
if {?@.typeid} = 1 then
- rptfilter = 0 and typeid = 1

{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
- When rptfilter = 0 and typeid = 1 then is joined to CAS_SP_SolnLosses;1.Typevalue = {CAS_SP_SolnLosses;1.Region}, i.e. values have to be equal
- Similar for typeid 2 - 8

else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else

if {?@.rptfilter} = 1 then
- rptfilter = 1

({CAS_SP_SolnLosses;1.Contractor} = "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} = "CAJV")
- rptfilter = 1 and CAS_SP_SolnLosses;1.Contractor = "ARORA" or CAS_SP_SolnLosses;1.Contractor "CAJV"

and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
- This is similar to above except that rptfilter has value 1 and further below value 2

else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else
if {?@.rptfilter} = 2 then
({CAS_SP_SolnLosses;1.Contractor} <> "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} <> "CAJV")
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})|||Here is a try (the syntax is slightly different to what I'm used to using Oracle):
You have the following parameters in your report controlling the outcome:
rptfilter
typeid

comments are marked with a "-"

Fuurther I would still make sure that I have all parenthesis in the right places. I would include an extra round of parenthesis where rptfilter changes values
<snip>

Thanks for your help!

New SQLConnection w/ user specifics......

This actually goes back to another post...I need the user to state which specifics to use when connecting to a server. I have text boxes set up and I need to have thier input placed into the New SqlConnection statement. I have used the below which did not work...

'dim connorthwind as New SQLConnection ("Server=" & strservername & ";UID=" & strUsername & ";PWD=" & strPassword & ";database=master")

See below for my code:

<%@. Page %>
<%@. import Namespace="System.Data.SqlClient" %>
<%@. import Namespace="System.Data" %>
<script runat="server">

Sub Button_Click( s As Object, e As EventArgs )
dim conNorthwind as New SqlConnection( "Server=dbtest1;UID=cbtest;PWD=password3;database=master" )

Dim strInsert As String
Dim cmdInsert As SqlCommand

cmdInsert = New SqlCommand( "sp_password_sec", conNorthwind )
cmdInsert.CommandType = CommandType.StoredProcedure
'cmdINsert.Parameters.Add( "@.loginname", txtname.text )
cmdINsert.Parameters.Add( "@.oldpwd", Password.text )
cmdINsert.Parameters.Add( "@.newpwd", txtnew.text )
conNorthwind.Open()
cmdInsert.ExecuteNonQuery()
conNorthwind.Close()
End Sub

</script>
<html>
<head>
</head>
<body>
<form runat="Server">
<h2>Password Change
</h2>
<b>User Name</b>
<br />
<asp:TextBox id="username" Runat="Server"></asp:TextBox>
<p>
<b>Server Name</b>
<br />
<asp:TextBox id="Servername" Runat="Server"></asp:TextBox>
</p>
<p>
<b>Database </b>
<br />
<asp:TextBox id="txtdatabase" Runat="Server"></asp:TextBox>
</p>
<p>
<b>Old Pass</b>
<br />
<asp:TextBox id="Password" Runat="Server"></asp:TextBox>
</p>
<p>
<b>New Pass</b>
<br />
<asp:TextBox id="txtnew" Runat="Server"></asp:TextBox>
</p>
<p>
<asp:Button id="Button1" onclick="Button_Click" Runat="Server" Text="Lookup!"></asp:Button>
</p>
</form>
</body>
</html>

What didn't work? Did you get an error?

Also, this way of connecting is quite unusual, it is a better practice to use the same connection string for everyone for a variety of reasons (eg, to use connection pooling & to maintain the proper security)

|||

...I don't think it would be possible for everyone to use the same connection if they are resetting their own pw. If they log on using a functional acct or system acct, it will not allow them to change b/c they are not logged in using their own acct...

|||

cbaxter82:

if they are resetting their own pw

What pwd are they changing? If they change their windows pwd or their application pwd it has nothing to do with a sql connection. You should not be adding users to SQL Server, that's a major headache when users come and go and it is a major security breach, too (once a user has a SQL login, they can log in via Excel if they're smart enough! You need to control access to the databse).

New Server Setup

I am trying to install two new SQL 2000 servers, but need them to be
mirrored. I had asked the question a while back and it was suggested i use
Virtual servers , which to be honest i havent found much on how it would
work. Can anybody explain how this can be done or recomend some reading
material that i may look at
The reason for the mirroring is , we deal with electronic payments and if
one server goes down i dont want ant intruption in service to our clients. I
suppose im trying to replicate the way the old Novell mirrored servers work.
Also i would want a rthird off site server kept in the loop and updated
In SQL 2000, you can use fail-over clustering to provide high-availability.
This feature is implemented in conjunction with OS clustering and is
described in detail in the Books Online. Be aware that the OS and hardware
requirements necessitate careful planning. All hardware must be MSCS HCL
certified.
SQL 2005 introduces a database mirroring feature, which is essentially a
software solution. This feature is scheduled to be released in the first
half of this year but can also be enabled for evaluation purposes in the
current release by using a trace flag.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:803B26A4-D2AA-4707-B61A-64ED12EFD180@.microsoft.com...
>I am trying to install two new SQL 2000 servers, but need them to be
> mirrored. I had asked the question a while back and it was suggested i
> use
> Virtual servers , which to be honest i havent found much on how it would
> work. Can anybody explain how this can be done or recomend some reading
> material that i may look at
> The reason for the mirroring is , we deal with electronic payments and if
> one server goes down i dont want ant intruption in service to our clients.
> I
> suppose im trying to replicate the way the old Novell mirrored servers
> work.
> Also i would want a rthird off site server kept in the loop and updated
>

New Server Setup

I am trying to install two new SQL 2000 servers, but need them to be
mirrored. I had asked the question a while back and it was suggested i use
Virtual servers , which to be honest i havent found much on how it would
work. Can anybody explain how this can be done or recomend some reading
material that i may look at
The reason for the mirroring is , we deal with electronic payments and if
one server goes down i dont want ant intruption in service to our clients. I
suppose im trying to replicate the way the old Novell mirrored servers work.
Also i would want a rthird off site server kept in the loop and updatedIn SQL 2000, you can use fail-over clustering to provide high-availability.
This feature is implemented in conjunction with OS clustering and is
described in detail in the Books Online. Be aware that the OS and hardware
requirements necessitate careful planning. All hardware must be MSCS HCL
certified.
SQL 2005 introduces a database mirroring feature, which is essentially a
software solution. This feature is scheduled to be released in the first
half of this year but can also be enabled for evaluation purposes in the
current release by using a trace flag.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:803B26A4-D2AA-4707-B61A-64ED12EFD180@.microsoft.com...
>I am trying to install two new SQL 2000 servers, but need them to be
> mirrored. I had asked the question a while back and it was suggested i
> use
> Virtual servers , which to be honest i havent found much on how it would
> work. Can anybody explain how this can be done or recomend some reading
> material that i may look at
> The reason for the mirroring is , we deal with electronic payments and if
> one server goes down i dont want ant intruption in service to our clients.
> I
> suppose im trying to replicate the way the old Novell mirrored servers
> work.
> Also i would want a rthird off site server kept in the loop and updated
>

New Server Setup

I am trying to install two new SQL 2000 servers, but need them to be
mirrored. I had asked the question a while back and it was suggested i use
Virtual servers , which to be honest i havent found much on how it would
work. Can anybody explain how this can be done or recomend some reading
material that i may look at
The reason for the mirroring is , we deal with electronic payments and if
one server goes down i dont want ant intruption in service to our clients. I
suppose im trying to replicate the way the old Novell mirrored servers work.
Also i would want a rthird off site server kept in the loop and updatedIn SQL 2000, you can use fail-over clustering to provide high-availability.
This feature is implemented in conjunction with OS clustering and is
described in detail in the Books Online. Be aware that the OS and hardware
requirements necessitate careful planning. All hardware must be MSCS HCL
certified.
SQL 2005 introduces a database mirroring feature, which is essentially a
software solution. This feature is scheduled to be released in the first
half of this year but can also be enabled for evaluation purposes in the
current release by using a trace flag.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:803B26A4-D2AA-4707-B61A-64ED12EFD180@.microsoft.com...
>I am trying to install two new SQL 2000 servers, but need them to be
> mirrored. I had asked the question a while back and it was suggested i
> use
> Virtual servers , which to be honest i havent found much on how it would
> work. Can anybody explain how this can be done or recomend some reading
> material that i may look at
> The reason for the mirroring is , we deal with electronic payments and if
> one server goes down i dont want ant intruption in service to our clients.
> I
> suppose im trying to replicate the way the old Novell mirrored servers
> work.
> Also i would want a rthird off site server kept in the loop and updated
>