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
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
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!
ReDim RowData(iNumCols)
Set rs = Server.CreateObject("ADODB
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
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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Let me rephrase the question: I want to set the value to iNumCols How do I do that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
I found it elsehwere:
Set rs2 = Server.CreateObject("ADODB
sql2 = "SELECT count(*) from information_schema.columns
rs2.Open sql2, conn, 1, 2
GetNumFlds = rs2(0)
rs2.Close
Set rs2 = Nothing
Set rs2 = Server.CreateObject("ADODB
sql2 = "SELECT count(*) from information_schema.columns
rs2.Open sql2, conn, 1, 2
GetNumFlds = rs2(0)
rs2.Close
Set rs2 = Nothing
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)
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)
:) exactly
didn't realize you had closed the question as I was answering - glad you got the solution - cheers. Paul
didn't realize you had closed the question as I was answering - glad you got the solution - cheers. Paul
ASKER
Thanks for your help!
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.
ASKER
No I did not know that...how do you do that?
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.
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.
ASKER
Awesome! Thanks!! I can't award you points can I?
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
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
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.
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.
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
ASKER
Great information!
:( drat, I keep getting this wrong... [note to self: NOT information_schema!]
ASKER