CODEWITHSUNDEEP
sqlsql-servervb.net
Orintangio
Orintangio

Reputation: 42

How do I access multiple records from the same table using SQLDataAdapter?

This almost works. I get an error at the last line that looks like it's complaining about the C1 reference. Is there a simple way around this? There is nothing wrong with the query or connection.

  Dim CmdString As String
    Dim con As New SqlConnection
    Try
        con.ConnectionString = PubConn
        CmdString = "select * from " & PubDB & ".dbo.Suppliers as S " & _
            " join " & PubDB & ".dbo.Address as A" & _
            "  on S.Supplier_Address_Code = A.Address_IDX" & _
            " join " & PubDB & ".dbo.Contacts as C1" & _
            "  on S.Supplier_Contact1 = C1.Contact_IDX" &
            " join " & PubDB & ".dbo.Contacts as C2" & _
            "  on S.Supplier_Contact2 = C2.Contact_IDX" &
            " WHERE S.Supplier_IDX = " & LookupIDX

        Dim cmd As New SqlCommand(CmdString)
        cmd.Connection = con
        con.Open()

        Dim DAdapt As New SqlClient.SqlDataAdapter(cmd)
        Dim Dset As New DataSet

        DAdapt.Fill(Dset)
        con.Close()

        With Dset.Tables(0).Rows(0)

            txtAddress1.Text = .Item("Address1").ToString
            txtAddress2.Text = .Item("Address2").ToString
            txtSupplierName.Text = .Item("Address_Title").ToString
            txtAttn.Text = .Item("Attn").ToString
            txtBusinessPhone1.Text = .Item("C1.Contact_Business_Phone").ToString

Upvotes: 0

Views: 59

Answers (3)

user2366842
user2366842

Reputation: 1216

You should only be pulling back the columns that you're in fact interested in, as opposed to *. It's sort of hard to tell exactly what data exists in which tables since you're pulling the full set, but at a quick guess, you'll want in your select statement to pull back A.Address1, A.Address2, A.AddressTitle, ?.Attn (not sure which table this actually derives from) and C1.Contact_Business_Phone. Unless you actually NEED the other fields, you're much better off specifying the individual fields in your query, besides having the possible duplicate field issue that you're running into here, it can also be a significant performance hit pulling everything in. After you clean up the query and only pull in the results you want, you can safely just reference them the way you are for the other fields, without needing a table alias (which as others have pointed out, isn't persisted to the result set anyways).

Upvotes: 0

murmeister
murmeister

Reputation: 672

The alias C1 is used by SQL Server and is not persisted to the result set. Have you taken this query into SQL Management Studio to see the results?

Since you requested all columns (*) and joined to the Contacts table twice, you'll end up with duplicate column names in the result. For example, if the Contacts table has a LastName field, you'll end up with TWO LastName columns in your result.

I haven't tried to duplicate this in my local environment, but I can't imagine the data adapter is going to like having duplicate column names.

I recommend specifically including the columns you want to return instead of using the *. That's where you'll use the alias of C1, then you can rename the duplicate columns using the AS keyword:

SELECT C1.LastName AS [Supplier1_LastName],
       C2.LastName AS [Supplier2_LastName],
...

This should solve your problem.

Good Luck!

Upvotes: 0

Rich Rousseau
Rich Rousseau

Reputation: 776

You would not include the "C1" table alias as part of your column name. It will be returned from your query as Contact_Business_Phone.

For accessing multiple rows you could use the indexer as you are in the example above "Rows(0)" by placing your With block into a For loop and accessing the "Rows(i)" with your loop variable. However, this would not help much as your are assigning this to individual text boxes, so you'd only see the last value on your page/screen.

Upvotes: 1

Related Questions