• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 450
  • Last Modified:

Num Cols in DB Table

How can I get this information.  Basically I want to retrieve the number of columns so I can copy the data in a specific row of multiple tables to a new database.  I want to replace (update) the same data in the second database with the data in the original database but each table has a different number of columns and different column heads.  My psuedo code would be:

        ReDim RowData(iNumCols)
        Set rs = Server.CreateObject("ADODB.Recordset")
        sql = "SELECT * FROM " & sThisTable & " WHERE EventID = " & lEventID
        rs.Open sql, conn_server, 1, 2
        For i = 0 To UBound(RowData)
            RowData(i) = rs(i).Value
        Next i
        rs.Close
        Set rs = Nothing

Then in the table I am copying to I would do this:

        Set rs = Server.CreateObject("ADODB.Recordset")
        sql = "SELECT * FROM " & sThisTable & " WHERE EventID = " & lEventID
        rs.Open sql, conn_server, 1, 2
        For i = 0 To UBound(RowData)
            rs(i).Value = RowData(i)
        Next i
        rs.Update
        rs.Close
        Set rs = Nothing

Comments?

Thanks!
0
Bob Schneider
Asked:
Bob Schneider
  • 8
  • 7
  • 4
3 Solutions
 
PortletPaulfreelancerCommented:
is one of these what you are looking for?
select count(*)
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'YourTable'
;

select
  column_name
, ordinal_position
, data_type
, character_maximum_length
, count(*) over (partition by table_name) num_cols
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'YourTable'
;

Open in new window

0
 
Bob SchneiderCo-OwnerAuthor Commented:
The top one is what I want but how do I actually retrieve the value?
0
 
PortletPaulfreelancerCommented:
like any other query, something like:

 sql = "SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = " & sThisTable
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Bob SchneiderCo-OwnerAuthor Commented:
Let me rephrase the question:  I want to set the value to iNumCols  How do I do that?
0
 
PortletPaulfreelancerCommented:
you cannot 'set' the number of columns

you have to define a table, then INFORMATION_SCHEMA.COLUMNS will report back what columns are defined.

like this:

CREATE TABLE TableX
      ([ColX] varchar(1))
;

select count(*)
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'TableX'
;
0
 
Bob SchneiderCo-OwnerAuthor Commented:
I don't need to create a table, I need to retrieve the number of columns and assign them to a variable.  I don't know how else to ask this.
0
 
Bob SchneiderCo-OwnerAuthor Commented:
I found it elsehwere:

      Set rs2 = Server.CreateObject("ADODB.Recordset")
      sql2 = "SELECT count(*) from information_schema.columns WHERE table_name='" & sThisTable & "'"
      rs2.Open sql2, conn, 1, 2
      GetNumFlds = rs2(0)
      rs2.Close
      Set rs2 = Nothing
0
 
PortletPaulfreelancerCommented:
I'm really not the person to answer that portion. The sql query will return one row of one integer value and you would retrieve it to VB like any other value provided by a sql query.

Your question contains structures that put a sql resultset into VB, won't it be exactly the same? (although it's only one row it is still a resultset)
0
 
PortletPaulfreelancerCommented:
:) exactly
didn't realize you had closed the question as I was answering - glad you got the solution - cheers. Paul
0
 
Bob SchneiderCo-OwnerAuthor Commented:
Thanks for your help!
0
 
Anthony PerkinsCommented:
You do realize that you can get the the number of columns from your Recordset rs ADO object right?  This way you do not need to make a second trip to SQL Server to retrieve the meta-data.
0
 
Bob SchneiderCo-OwnerAuthor Commented:
No I did not know that...how do you do that?
0
 
Anthony PerkinsCommented:
rs.Fields.Count will report the number of columns in your resultset.  

Caveat:  The only reason this works is because you are selecting all the columns with SELECT *.  If instead you had done SELECT Col1, Col2, Col3 FROM ... then the answer would have been 3, rather then the actual number of columns in the table.
0
 
Bob SchneiderCo-OwnerAuthor Commented:
Awesome!  Thanks!!  I can't award you points can I?
0
 
PortletPaulfreelancerCommented:
you can ask for the question to be re-opened for redistribution of points

ps:
"but each table has a different number of columns and different column heads"

it's my belief (perhaps wrong) that you will need to access the information_schema at some point, especially for those different "heads" which is why I provided a second query in ID 39177885
0
 
Anthony PerkinsCommented:
Awesome!  Thanks!!  I can't award you points can I?
No need.  But if you want a complete solution using the ADO Recordset object and Fields collection, including how you can use aliases to get the correct header and in addition have output columns that are not in the original table, feel free to post a new question.
0
 
Anthony PerkinsCommented:
I would also go out on a limb and suggest that if you do need to retrieve table schema you will find that INFORMATION_SCHEMA VIEWS are deprecated in all but name in favor of system catalog views.  Aaron Bertrand has a good blog on the subject: The case against INFORMATION_SCHEMA views
0
 
Bob SchneiderCo-OwnerAuthor Commented:
Great information!
0
 
PortletPaulfreelancerCommented:
:( drat, I keep getting this wrong... [note to self: NOT information_schema!]
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 8
  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now