DropDownList bound to one table, selected value from another table

Hi I am having a very frustrating problem with a DropDownList.  The DropDownList is bound to one SQL Server 2005 table.  I do this progromatically with the following code:

Public Sub EvaltrID()

Try

Dim aType As String = txtAircraftType.SelectedValue Dim auth As String = Convert.ToInt32(MinLevel.Text)

Dim active As String = "True"

Dim iPilot As String = "True"

Dim CrewPos As String = txtCrewPos.SelectedValue

Dim CrewPos1 As String = "SO"

Dim instructSO As String = "True"

Dim sesType As String = txtSessionType.Text

Dim sesType1 As String = "aqplc"

Dim sesType2 As String = "lc"

Dim program As String = txtProgram.Text

Dim program1 As String = "cq"

Dim program2 As String = "qc"

Dim oConn As SqlConnection

Dim oComm As SqlCommand

Dim oReader As SqlDataReader

Dim sSQL As String

Dim sConn As String

sSQL = "SELECT EvaltrID, convert(varchar, EvaltrID) + ' ' + LastName + ', ' + FirstName as info, AircraftType, IsActive, InstructorPilot, InstructorSO, [Authorization] FROM tlkpEvaltrID WHERE AircraftType = '" & aType & "' And [Authorization] >= " & auth & " And IsActive = '" & active & "' And InstructorPilot= '" & iPilot & "' And '" & CrewPos & "' <> '" & CrewPos1 & "' Or AircraftType='" & aType & "' And [Authorization] >= " & auth & " And IsActive = '" & active & "'And InstructorSO = '" & instructSO & "'And '" & CrewPos & "' = '" & CrewPos1 & "'And '" & sesType & "' <> '" & sesType1 & "'Or AircraftType = '" & aType & "'And [Authorization] >= " & auth & " And IsActive = '" & active & "'And InstructorPilot = '" & iPilot & "' And '" & CrewPos & "' = '" & CrewPos1 & "'And '" & sesType & "' = '" & sesType1 & "'Or '" & sesType & "' = '" & sesType2 & "'And '" & program & "' = '" & program1 & "'Or '" & program & "' = '" & program2 & "' ORDER BY LastName"

sConn = ConfigurationManager.ConnectionStrings("svtppdbSQLConnectionString2").ConnectionString

 

oConn =
New SqlConnection(sConn)

oConn.Open()

ddlEvaltrID.DataTextField = "info"

ddlEvaltrID.DataValueField = "EvaltrID"

oComm = New SqlCommand(sSQL, oConn)

oReader = oComm.ExecuteReader()

ddlEvaltrID.DataSource = oReader

ddlEvaltrID.DataBind()

oConn.Close()

Catch ex As Exception

lblMessage.Text = ex.Message

End Try

 

End Sub

 I then use a DataReader to choose a selected value for the DropDownList when the page loads.  This value comes from another table.

Dim conn As SqlConnection Dim sqlCmd As SqlCommand

Dim Reader As SqlDataReader

conn = New SqlConnection(ConfigurationManager.ConnectionStrings("svtppdbSQLConnectionString2").ConnectionString)

sqlCmd = New SqlCommand("SELECT * from tblSession where SessionID='" & inputnumber1 & "'", conn)

conn.Open()

Reader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection)

Reader.Read()

If Reader.HasRows Then

If Reader("PilotEvaltrID") Is DBNull.Value And (txtCrewPos.SelectedValue = "CA" Or txtCrewPos.SelectedValue = "FO") Then

Else

ddlEvaltrID.SelectedValue = Reader("PilotEvaltrID")

ddlEvaltrID.Enabled = False

End If

End If

I am having such a hard time with this. I'm getting this error message:  'ddlEvaltrID' has a SelectedValue which is invalid because it does not exist in the list of items. Parameter name: value.  I know what this means.  I know that it means that PilotEvaltrID column does not exist in the data that is currently bound to the DropDownList.  But, I'm unsure what to do.  Should I use a join statement?  I've found a ton of info on this in this forum, but nothing that helps me with the particular situation I face.  Does anyone have any advice for me??  Thanks.


Holly Quinn
http://www.hollyquinn.com/Blog
0
hollyquinn
4/7/2009 3:07:13 PM
asp.net.object-datasource 16182 articles. 0 followers. Follow

2 Replies
790 Views

Similar Articles

[PageSpeed] 20

Why don't you do a join when running your query and set the text and value at the same time?


Joseph Baggett

http://www.myitstudio.com




MCSD, MCPD: Web Developer, MCITP: Database Developer, MCTS: SQL Server 2005, MCTS: .NET 2.0 Web Applications, MCTS: .NET Framework 2.0 Windows Applications, MCTS: .NET Framework 2.0 Windows Applications, MCAD, MCP

If the project doesn't work, blame Scott Guthrie and/or refer to the problem solving flowsheet. O.~
0
Joseph
4/8/2009 7:27:03 PM

Joseph Baggett:

Why don't you do a join when running your query and set the text and value at the same time?

I did try doing a join but I got the same error.  Here's the code I wrote for that.  Am I doing it incorrectly?

sqlCmd = New SqlCommand("select tlkpEvaltrID.LastName, tlkpEvaltrID.FirstName, tlkpEvaltrID.EvaltrID, tblSession.PilotEvaltrID, tblSession.SessionID from tlkpEvaltrID Inner Join tblSession on tlkpEvaltrID.EvaltrID = tblSession.PilotEvaltrID where tblSession.SessionID= '" & inputnumber1 & "'", conn)

conn.Open()

Reader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection)

Reader.Read()

If Reader("PilotEvaltrID") Is DBNull.Value And (txtCrewPos.SelectedValue = "CA" Or txtCrewPos.SelectedValue = "FO") Then

Else

ddlEvaltrID.SelectedValue = Reader("EvaltrID")

ddlEvaltrID.Enabled = False

End If

Reader.Close()

conn.Close()


Holly Quinn
http://www.hollyquinn.com/Blog
0
hollyquinn
4/8/2009 7:56:32 PM
Reply:

Similar Artilces:

Select the value from a field of one table and place it into another table.
Using VB.NET, VS2005, SQL Server 2000. In a Windows Form, how could I provide a means for a user to select the value from a field of one table (a primary key value) and place it into another table (as a foreign key value)? The goal is to "assign" a person from one table to a location in another table by placing the person's primary key value into the location's foreign key field via an easy to use "selection" mechanism like a DataGridView or something.Thanks in advance for any help or examples.David  this is an ASP.Net forum - - you might want to check out: http://forums.microsof...

Datalist Bind from one table with a dropdownlist inside bound to another table based on dataKeyfield
 I'm looking to get the datalist below working. I'm not sure how to bind the dropdown list so that the proper value is displayed from the db.  Basically what i'm doing is pulling a list of skills from a table and displaying them so a user can update thier proficiency. I've got one table for the skills which is bound to the datalist. I then need to get the users proficiency which is stored in another table to be selected in the drowdownlist for each of those skills. Then i must save them all I guess by looping through each row in the datalist? &n...

insert multiple values from one table into one column from another table?
Hi, I'm attempting to create an insert statement that gets the values from multiple columns and insert them into one column from another table, is there a simple solution to do this?Here is something similar to what i'm wanting done, just it's not the correct way to do it, i want value1, value2 and value3 from one table to be combined and put into the value1 column of another table, what am i doing wrong? INSERT INTO tablename2 (value1 + value2 + value3)SELECT value1FROM tablename1 thanks in advance! INSERT INTO tablename2 (SELECT val1 + val2 + val3 FROM tabl...

Updating table with DropDownList values from another table
Hi. I'm trying to insert a value from DropDownList into database. I want to select value from DropDownList(DropDownList has SqLDataSource from another table) and insert that selected value into table.I'm trying to do this in DetailsView. And when I do, i get this error :  Must declare the scalar variable "@ServiceName"Problem starts when i try to edit a record. Oh, and the ServiceName in table Provided_Services is NULL. Please help me with this...Here is my code:  <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CustomerID" &nb...

Select values from one table based upon values in another...
How do I:Select f1, f2, f3, from tb1 where f1=Select f1 from tb2 where f1='condition'?Rex WinnCode-frog System Architects, Inc.VS 2005 Professional, C#, C++ Well... You might try reading up on some standard sql syntax... There are a number of different ways... Select t.f1,t.f2,t.f3 From tb1 t INNER JOIN tb2 t2 ON t.f1 = t2.f1 Where t2.f1 = 'condition'   Select t.f1,t.f2,t.f3 From tb1 t Where t.f1 IN (    Select t2.f1    From tb2 t2    where t2.f1 = 'condition')   Select t.f1,t.f2,t.f3 From tb1 t Where exists (select t2....

can I compare one record from one table to another record in another table?
can I compare one record from one table to another record in another table? I need to test if this condition is met. My scenario is: i need to check one table for a value and compare it to a list of records. for example, table 1           columnrecord 1- applesrecord 2- orangesrecord 3- grapes table 2            col1   col2   col3    col4record 1- Null , Null, Null, grapes I need to compare table 2 records with a list of records from table 1. the fru...

select from one table, put into another, delete from original table
Hi there, i am writing a stock control system for a small company. Most of it has been done apart from the picking part. When the user (staff member in the shop) places an order for a customer, they type in the item ID they want + the qty and there is a DDL to say collection, delivery etc. There are 3 main tables involved tbl_stock - where stock information + pics are held tbl_location - where the location of stock is held, each row has 11 columns e.g. Item Name  Qty1  Location1  Qty2  Location2 etc up to 5. Each row is for one item, so for example if there is stockID 2...

DropDownList populated by one table then updates another table (in GridView)
EDIT: Has anyone got a DropDownList to work like this successfully?? Hi all. I'm yet another newbie to ASP.NET an would be very grateful for any help you can offer I also am trying to use a DDL in a GridView and populate the DDL (JobAssignedToDDL) with values (Names) from one table (WorkshopTechnicians). When the user (in edit mode) selects a Name and clicks update, this value is stored in another table (WorkshopJobs). I've tried to implement the code here: http://forums.asp.net/t/1231510.aspx, but can't seem to get it working. At the moment the GV in normal mode display...

CheckBoxList Databinding, Select from One Table, Insert Into another Table
Newbie here! I've looked all over the place to see if I find some code that can get me started with this project, here it goes guys, as usual, all help is appreciated... SQL Server, I have 1 table tblServices that shows all of the services we offer, with 3 fields, such as: ID, CategoryCode, CategoryDescription. When loading my web form I want to offer all of these service options showing them the "CategoryDescription" in a checkboxlist option for my users to click on, and then submit the form into a second table. The second table "tblEx...

Copy values from one table to another table using LINQ to SQL?
Hello,I have two database tables that have different names but identical structure (columns).What is the easiest way to copy the values from one table to the other table using LINQ to SQL? Thanks,-- shawn shawn Hi, As far as I research, we could use "foreach"  to insert the data from one table to another table. The code is shown below.         //there exist two table list and listSecond        DataClassesDataContext dataClass = new DataClassesDataContext(); //create the instance of the DataCon...

CheckBoxList gets text from one table, send values to another table
I am working on making a CheckBoxList in a DetailsView that allows the user to check the boxes of the services they would like to receive. In my SQLDataSource, I have two tables that are involved in this. Columns "ServiceID" and "Name"are stored in one table, "Services," and the columns "UserID" and "ServiceID" are stored in another table, "UserServices." I want the CheckBoxList to grab the value and the name of all the services available and have a box for each with the name beside each box. Once the user clicks on each box they want and then click "Submit" I want all rows in table "Use...

How do i copy / insert a Primary Key value of one table into another table?
  Hi i have set up  two very simple tables, I want a user to be able to create a basic account ( data stored in User_Profile  table with Id set as the Primery Key as Identity) I want the user to be able to be able to return to their account at a later date and then post multiple reviews of different bands they have seen at a later date. I kept the tables in my example very simple so I could get my head around the concept, but generally, I want to connect the Id (PK) value in User_Profile table to the User_Id filed in the User_Review table, so every review th...

Add values to a table based on values in another table
When a record is inserted or updated records in Table1 I want a record to be inserted into table3 for each record ID that is in table2 if the table2.id does not already exist in table3. What is the best way to do this? Could this be done with a trigger? If it could how would I write such a trigger. I have never written one before and this sounds like a hand full for my first effort. Your help will be greatly appreciated. Thanks You could do it in a trigger.  They're really not hard.  It's just SQL you want to run whenever a certain action happens on your table.  Pretty st...

Web resources about - DropDownList bound to one table, selected value from another table - asp.net.object-datasource

Tag Synonyms - Stack Overflow
Q&A for professional and enthusiast programmers

Accidental entities - what about the UI?
This post is a follow-up to my previous blog post " Accidental entities - you don't need that identity ". In that post, we followed a consultant ...

Flash Builder Plug-in for SAP Netweaver Gateway - Adobe Labs
Search Navigation Labs Home Wiki Home Technologies Alchemy Blueprint Camera Profiles and DNG Profile Editor Cirrus ColdFusion 10 DNG Codec Fireworks ...

Air Astana - Join Now
Thank you for considering membership of Nomad Club – Kazakhstan’s first Frequent Flyer Program. You could soon be on your way to earning free ...

Languages -> C# Articles, Tutorials, Examples: ASP Alliance
.NET,ASP,ASP.NET,SQL,XML,HTML,ADO,ADO.NET,JavaScript,JScript,CSharp,VBScript and VB tutorials from AspAlliance.

Blog - Avisra
Avisra Web Development Home Blog Services Contact The Blog Latest Module Builder: How can we make it better? Sitefinity by Chris on July 2, 2013 ...

Online Booking Tool Support
This Online Booking Tool Support page might help you as you navigate through the process of booking travel through your OBT provided by CWT. ...

@Html and beyond
... ); } return string .Format( “{0}{1}” , me.Member.Name, id); } The Html building methods are quite simple. Building a dropdownlist for a ...

Top Ranked Articles
Top Ranked Articles - Free source code and tutorials for Software developers and Architects.; Updated: 23 Feb 2013

Unit Testing DateTime.Now Without Isolation
public virtual void SeedWithYearsSince(DropDownList list, int year){ for (int index = year; index

Resources last updated: 11/26/2015 11:07:37 PM