Write a script to select particular field from DB

choudhmh · January 28th, 2005, 08:12 AM

Hi Guys
I have two database they both are related via an ID

1st DB:
TelephoneServicePlanID TelephoneCallPlanID ServicePlanName LocationConnectionCharge MonthlyFee
1 1 Test 100 12

2nd DB:
TelephoneCallPlanID CallPlanName
1 BT

As you can see TelephoneCallPlanID is the unique identifier which relatesto both. I have a drop down list where users selcets the CallPlanNAme from the 2nd DB.
What i need to is write a script where if a CallPlanName is choosen, using its correct CallPlanID i need to get say the 100 value of LocationConnectionCharge from 1st DB.

I've created this in sql:

SELECT DISTINCT
TSP.LocalConnectionChargePerCall
FROM
tblTelephoneCallPlans TCP
INNER JOIN tblTelephoneServicePlans TSP ON TSP.TelephoneCallPlanID = TCP.TelephoneCallPlanID
Where
TCP.TelephoneCallPlanID = @TelephoneCallPlanID

it retrives the LocalConnectionChargePerCall depending on TelephoneCallPlanID. But now how will i write a script where if dropdown list chosen is 2 then search through the sql insert 2 to @TelephoneCallPlanID and get the respective LocalConnectionChargePerCall value.

I have another table where it inserts TelephoneCallPlanID depending on what value from drop down list is chosen tp singup table where it has field TelephoneCallPlanID.

How will i combine the three table so TelephoneCallPlanID from signup is recognised then its respective value is used in the sql to retriv ethe value.

Hope this is clear
Thanks
Mac

jaucourt · January 28th, 2005, 08:37 AM

Ok, to start off with I think your database normalisation may be a bit off the mark.

Am I correct in thinking that the 'LocationConnectionCharge' only changes by Call Plan, not by ServicePlan? I'm guessing this because you have done a 'select distinct' which will get you just one LocationConnectionCharge. Therefore, I'd say that this column should be in the '2nd DB'.

Anyway, this aside, I think you're misunderstanding the nature of placeholders (the '@' value).

Code:

        Dim sqlCmd As New SqlCommand("SELECT DISTINCT TSP.LocalConnectionChargePerCall FROM tblTelephoneCallPlans TCP " & _
            "INNER JOIN tblTelephoneServicePlans TSP ON TSP.TelephoneCallPlanID  = TCP.TelephoneCallPlanID " & _
            "WHERE TCP.TelephoneCallPlanID  = @TelephoneCallPlanID)")
        sqlCmd.Parameters.Add(New SqlParameter("@TelephoneCallPlanID", SqlDbType.Int))
        sqlCmd.Parameters("@TelephoneCallPlanID").Value = 0 'pass in the relevant value here
        Dim read As Integer = sqlCmd.ExecuteScalar

This code won't work straight off, you'd have to attach a SqlConnection object to the sqlCmd.Connection parameter. Once this is done, however, the relevant value will go into the read variable. Just make sure that you assign the correct value to @TelephoneCallPlanID