Solved

Num Cols in DB Table

Posted on 2013-05-18
19
428 Views
Last Modified: 2013-05-20
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
Comment
Question by:Bob Schneider
  • 8
  • 7
  • 4
19 Comments
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 39177885
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
 

Author Comment

by:Bob Schneider
ID: 39177909
The top one is what I want but how do I actually retrieve the value?
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 39177912
like any other query, something like:

 sql = "SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = " & sThisTable
0
 

Author Comment

by:Bob Schneider
ID: 39177913
Let me rephrase the question:  I want to set the value to iNumCols  How do I do that?
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39177919
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
 

Author Comment

by:Bob Schneider
ID: 39177921
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
 

Author Comment

by:Bob Schneider
ID: 39177925
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39177928
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39177930
:) exactly
didn't realize you had closed the question as I was answering - glad you got the solution - cheers. Paul
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:Bob Schneider
ID: 39177931
Thanks for your help!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39178706
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
 

Author Comment

by:Bob Schneider
ID: 39179367
No I did not know that...how do you do that?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39179899
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
 

Author Comment

by:Bob Schneider
ID: 39179924
Awesome!  Thanks!!  I can't award you points can I?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39179944
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39183032
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39183040
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
 

Author Comment

by:Bob Schneider
ID: 39183168
Great information!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39183244
:( drat, I keep getting this wrong... [note to self: NOT information_schema!]
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Hide vba in gp 7 83
powershell and sql server - alerting 7 77
Insert Salary Period that has 2 months 11 23
Designing and Implementing a Data Warehouse 3 17
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

895 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

16 Experts available now in Live!

Get 1:1 Help Now