Solved

VB.net Get 2 key olumn names in a SQL table

Posted on 2014-11-10
4
138 Views
Last Modified: 2014-11-10
Hi
I am using the following code to get the primary key of a table. This table has a second key column.
How do I get the name of that

        Dim sSQL As String
        sSQL = "SELECT column_name "
        sSQL = sSQL & "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE "
        sSQL = sSQL & "WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1"
        sSQL = sSQL & "AND table_name = 'MARA_MBEW'"

        Dim connection As New SqlConnection(Globals.ThisAddIn.oRIGHT.lblConnectionString.Text)
        Dim cmd As New SqlCommand(sSQL, connection)
        connection.Open()
        Dim oResult As String
        oResult = cmd.ExecuteScalar().ToString
        MsgBox(oResult)
        connection.Close()
0
Comment
Question by:murbro
  • 2
4 Comments
 
LVL 32

Accepted Solution

by:
ste5an earned 500 total points
ID: 40432496
Looks like the same question as in VB.net Get a count of the number of primary keys in a SQL table.  

So same answer here:

USE AdventureWorks2012;

SELECT  KC.name ,
        C.name 
FROM    sys.schemas S
        INNER JOIN sys.tables T ON T.schema_id = S.schema_id
        INNER JOIN sys.key_constraints KC ON KC.parent_object_id = T.object_id
        INNER JOIN sys.index_columns IC ON IC.object_id = T.object_id
                                           AND IC.index_id = KC.unique_index_id
        INNER JOIN sys.columns C ON C.object_id = T.object_id
                                    AND C.column_id = IC.column_id
WHERE   S.name = 'Production'
        AND T.name = 'ProductInventory'
	AND KC.type = 'PK';

Open in new window


btw, the alternative would be using SQL Server Management Objects (SMO) Programming Guide.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40432507
Dim sSQL As String
        sSQL = "SELECT column_name, OtherFieldName "
.
.


Dim rdr As SqlDataReader
        rdr= cmd.ExecuteNonQuery
        if (rdr.Read)
          MsgBox(rdr.Item(0)).ToString
          MsgBox(rdr.Item(1)).ToString
      End If
        connection.Close()

sorry if my air code have some syntax issues. I will try to QC in vs shortly.
0
 

Author Closing Comment

by:murbro
ID: 40432521
Thanks. The questions were slightly different but the code achieves both. Thanks very much
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40432580
In case this QCed version was needed:

 Dim rdr As SqlDataReader
        rdr = cmd.ExecuteReader
        If (rdr.Read) Then
            MsgBox(rdr.Item(0)).ToString()
            MsgBox(rdr.Item(1)).ToString()
        End If
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PL/SQL query 14 51
BULK INSERT most recent CSV 19 21
Test a query 23 18
Syntax using Declare 3 11
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now