Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB.NET 1.0 > Pro VB.NET 2002/2003
|
Pro VB.NET 2002/2003 For advanced Visual Basic coders working .NET version 2002/2003. Beginning-level questions will be redirected to other forums, including Beginning VB.NET.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB.NET 2002/2003 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 28th, 2005, 08:12 AM
Registered User
 
Join Date: Dec 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Write a script to select particular field from DB

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
 
Old January 28th, 2005, 08:37 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 142
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
How to write a script to download file Ivanchan Other Programming Languages 0 January 24th, 2008 05:56 AM
How do I write out a record to an SQL db table? furjaw Visual Basic 2005 Basics 1 April 26th, 2006 05:16 PM
How to write files information to mysql db using p method PHP Databases 4 February 14th, 2006 09:18 PM
Is It Possible to write record to DB Dwizz VB.NET 2002/2003 Basics 1 May 6th, 2005 07:16 AM
Script To Write to Dbase HELP!!! reidcor VBScript 3 January 20th, 2005 05:12 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.