Insert to multiple tables - Page 2

[foundsheep]

That fixed that, but now my ddls are jacked up because they are based on the contactID and zipID

[foundsheep]

Here is the code we were working on before which no longer works....

Partial Class AddCall
    Inherits System.Web.UI.Page
    Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
        Dim FirstName As TextBox = TryCast(FormView1.FindControl("FirstName"), TextBox)
        Dim LastName As TextBox = TryCast(FormView1.FindControl("LastName"), TextBox)
        DropDownList2.SelectedIndex = 0
        FirstName.Text = ""
        LastName.Text = ""
    End Sub
    Protected Sub DropDownList2_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList2.SelectedIndexChanged
        'labelSelection.Text = DropDownList1.SelectedItem.Text & " " & DropDownList2.SelectedItem.Text
        
        Dim Company As TextBox = TryCast(FormView1.FindControl("Company"), TextBox)
        Dim Address1 As TextBox = TryCast(FormView1.FindControl("Address1"), TextBox)
        Dim Address2 As TextBox = TryCast(FormView1.FindControl("Address2"), TextBox)
        Dim Phone As TextBox = TryCast(FormView1.FindControl("Phone"), TextBox)
        Dim FirstName As TextBox = TryCast(FormView1.FindControl("FirstName"), TextBox)
        Dim LastName As TextBox = TryCast(FormView1.FindControl("LastName"), TextBox)
        Dim City As TextBox = TryCast(FormView1.FindControl("City"), TextBox)
        Dim State As TextBox = TryCast(FormView1.FindControl("State"), TextBox)
        Dim Zip As TextBox = TryCast(FormView1.FindControl("Zip"), TextBox)

        Dim str_sql As String = "SELECT * FROM Customers WHERE cusID = " & DropDownList1.SelectedValue
        Using connection As New System.Data.SqlClient.SqlConnection("Data Source=IT-P02\SQLEXPRESS;Initial Catalog=HRIService;Integrated Security=True")
            Dim command As New System.Data.SqlClient.SqlCommand(str_sql, connection)
            connection.Open()
            Dim reader As System.Data.SqlClient.SqlDataReader = command.ExecuteReader()
            Try
                If reader.Read Then
                    Company.Text = reader.Item("Company").ToString
                    Address1.Text = reader.Item("Address1").ToString
                    Address2.Text = reader.Item("Address2").ToString
                    Phone.Text = reader.Item("Phone").ToString
                End If
            Finally
                ' Always call Close when done reading.
                reader.Close()
            End Try
            str_sql = "SELECT * FROM CONTACTS WHERE contactID = " & DropDownList2.SelectedValue
            command = New System.Data.SqlClient.SqlCommand(str_sql, connection)
            reader = command.ExecuteReader()
            Try
                If reader.Read Then
                    FirstName.Text = reader.Item("FirstName").ToString()
                    LastName.Text = reader.Item("LastName").ToString()
                End If
            Catch ex As Exception
                reader.Close()
            End Try

            reader.Close()
            str_sql = "SELECT * FROM Zip WHERE zipID = " & DropDownList1.SelectedValue
            command = New System.Data.SqlClient.SqlCommand(str_sql, connection)
            reader = command.ExecuteReader()
            Try
                If reader.Read Then
                    City.Text = reader.Item("City").ToString()
                    State.Text = reader.Item("State").ToString()
                    Zip.Text = reader.Item("Zip").ToString()
                End If
            Catch ex As Exception
                reader.Close()
            End Try
        End Using

    End Sub
    Protected Sub DropDownListEquip_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)

    End Sub

End Class

[foundsheep]

I fixed it myself, wohoo!

Now I just need the second drop down to reset when I select a new top ddl.

[SheSaidImaPregy]

You should already have that, at the top of that code:

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
        Dim FirstName As TextBox = TryCast(FormView1.FindControl("FirstName"), TextBox)
        Dim LastName As TextBox = TryCast(FormView1.FindControl("LastName"), TextBox)
        DropDownList2.SelectedIndex = 0
        FirstName.Text = ""
        LastName.Text = ""
    End Sub

Glad you fixed it! Isn't it great to learn? That's what I do all day lol. But that's me^^

[foundsheep]

It only resets to a certain degree. It is keeping whatever items were selected in the second ddl. Those are still there when you select a new company.

[SheSaidImaPregy]

This line should reset it, which you already have. Make sure it is calling the right ID for your drop down.

DropDownList2.SelectedIndex = 0

Also, make sure you have the first item of the dropdown something unique like ("-- Select Here --")

[foundsheep]

One thing I noticed is that it is not in FormView1, but the code has this:

im FirstName As TextBox = TryCast(FormView1.FindControl("FirstName"), TextBox)

[foundsheep]

Another item that needs to be fixed is the equipment ddl. It needs to reset the equipment list as well, if the company is changed.

<asp:DropDownList ID="DropDownListEquip" runat="server" CssClass="maintext" 
                                                                                DataSourceID="Equipment" DataTextField="CompanyEqpDesc" DataValueField="mfgID">
                                                                            </asp:DropDownList>

[SheSaidImaPregy]

I thought you said it didn't matter what company was chosen, all companies have the same equipment? If so, why reload the DDL?

[foundsheep]

No, I guess we had a miscommunication. The equipment will be specific to each company being that this is a service ticket that is created for the company's equipment that may be having problems.

[SheSaidImaPregy]

Reset, like set the index to zero? Follow the same formatting as before.

DropDownListEquip.SelectedIndex = 0

[SheSaidImaPregy]

Okay, that sounds good. However, with your current datasource, you are not specifying anything that is based off of information that is submitted through the form. Regardless of the company chosen, your datasource will always have the same output. The line is highlighted in red:

<asp:SqlDataSource 
                                                                                ID="Equipment" 
                                                                                runat="server" 
                                                                                ConnectionString="<%$ ConnectionStrings:HRIServiceConnectionString1 %>" 

                                                                                SelectCommand="SELECT a.Company, b.Description FROM Manufacturers a JOIN Equipment b ON a.mfgID=b.mfgID">
                                                                                <SelectParameters>
                                                                                <asp:ControlParameter 
                                                                                ControlID="DropDownList1"
                                                                                Name="cusID"
                                                                                PropertyName="SelectedValue" 
                                                                                Type="String" />
                                                                                </SelectParameters>
                                                                            </asp:SqlDataSource>

That line will always bring out all equipment that is related to any company. You will have to change that, and then find the parameter that it will be based off of.

[SheSaidImaPregy]

You actually already have a parameter there, but it is not located anywhere within your select statement. Therefore, it plays no role.

[foundsheep]

I guess I want to use cusID to determine the right customer equipment.

Currently, I have a Cus_Equip table that stores the serial number. And has this:
cusequpID
cusID
eqpID
SerialNumber

This could be totally the wrong way to do it.

[SheSaidImaPregy]

I don't know, I don't have all your tables with all your columns. I can't tell you if it is the way to do it or if there is a better way. Then if you do tell me, that can change depending on the next scenario. I don't have your business plan or website plan, etc.

Anyway, you can always redo the database at a later time, after the coding is done. Then just modify the coding a bit.

Here:

SelectCommand="SELECT a.Company, b.Description FROM Manufacturers a JOIN Equipment b ON a.mfgID=b.mfgID JOIN Cus_Equip c ON b.eqpID=c.eqpID WHERE c.cusID=@cusID"

I don't use coding within SqlDataSource and SelectParameters tags, so I might be doing it incorrectly.

[SheSaidImaPregy]

If you can, let me know your database with tables and their columns (I don't need data types, just names of the columns and tables).

[foundsheep]

Here are my tables with the columns excluding the membership and roles tables:

Customers
cusID
Company
Address1
Address2
Country
POBox
Phone
Fax
Website
HRIRep

Contacts
cusID
FirstName
LastName
Title

Equipment
eqpID
mfgID
ModelNumber
Description
MeterLocation

Manufacturers
mfgID
Company
Equipment (y/n)
Supplies (y/n)
Parts (y/n)
Address1
Address2
Phone
Fax
Contact
WarrantyTime

Cus_Equip
cuseqpID
SerialNumber
eqpID
mfgID

Zip
cusID
City
State
Zip

Dispatch
disID
cusID
userID
Problem
Status
Contract
Priority
ScheduleTime

DispatchEvents
eventID
disID
DateTime
Actions
Notes

Users
userID
Username
Password
Userlevel
FirstName
LastName
Role
Active
Contract
Equipment
Parts
Dispatch

[SheSaidImaPregy]

I have an idea how to do this, however, how are you relating Cus_Equip to the Customers table? There is nothing there that says which record belongs to which customer.

I think you need to add cusID to Cus_Equip. Otherwise, it's not related at all to any customer.

[foundsheep]

I put the wrong one is Cus_Equip. Its supposed to be cusID not mfgID. That is how they're related.

[SheSaidImaPregy]

Then here, set this line:

SelectCommand="SELECT a.Company, b.Description FROM Manufacturers a JOIN Equipment b ON a.mfgID=b.mfgID WHERE b.eqpID IN (SELECT eqpID FROM Cus_Equip WHERE cusID=@cusID)"

This will select Company and Description from those two tables where the customer has the equipment.

So if the customer has equipment ID's 5, 7 and 9, then it will select the Company and Description from both tables where the eqpID's equal 5, 7, and 9, and their mfgID's are related.

Should work.

[foundsheep]

What should the datatextfield be now? Before we had -
SELECT a.Company + ' - ' + b.Description As CompanyEqpDesc

Its still looking for that.

[SheSaidImaPregy]

SelectCommand="SELECT a.Company + ' - ' + b.Description As CompanyEqpDesc, b.eqpID FROM Manufacturers a JOIN Equipment b ON a.mfgID=b.mfgID WHERE b.eqpID IN (SELECT eqpID FROM Cus_Equip WHERE cusID=@cusID)"

Then set

DataTextField = "CompanyEqpDesc"
DataValueField = "eqpID"

[foundsheep]

that worked