| Author |
 Topic  |
|
|
cfoster
Starting Member
Canada
7 Posts |
 Posted - 04/11/2006 : 20:10:08
|
Hi,
I've created a simple MS-Access based database for a client. I don't have access to the database except through ASP/SQL.
I needed to add a new column to a table and that was fine. But I must have mistyped the column name because if I try to select it, I can't. I can 'select *' and see that there is a new column full of null values, but I can't select it by name.
I have since added a column with the correct name and that seems to be working fine, but I would like to clean up this other column without bothering them.
Is there a way to get a description of a table via SQL if the database is just MS-Access?
I have tried MANY variations to see if I could get something to work, but so far I usually just get the error:
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."
which leads me to believe there is no way to do it, unless its in a stored procedure that I don't know about.
Any thoughts on the matter would be greatly appreciated!
Thanks,
Colin.
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 04/11/2006 : 21:02:13
|
try select [your column name] from yourtable
KH
|
 |
|
|
cfoster
Starting Member
Canada
7 Posts |
Posted - 04/11/2006 : 21:33:21
|
quote:
Originally posted by khtan
try select [your column name] from yourtable
Unfortunately it's the 'your column name' that I don't have.
If there was a way to refer to a column by number such as
ALTER TABLE my_table DROP COLUMN 4
that would also work for me. But if not, I need some way to find out what the column name actually is.
Thanks,
Colin.
|
|
|
|
jen
Flowing Fount of Yak Knowledge
Sweden
4110 Posts |
Posted - 04/11/2006 : 21:37:45
|
but you can see the name of the column?
your problem is you can't drop that column? but can you drop other columns?
--------------------
keeping it simple... |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 04/11/2006 : 21:49:03
|
use sp_help yourtable and see weather the column is there
KH
|
|
|
|
cfoster
Starting Member
Canada
7 Posts |
Posted - 04/11/2006 : 23:42:50
|
Hi Jen:
I created a column and I no longer know its name, so I would like to find some way to drop that column. I think I need to know its name to do that, but I don't know how to find the column names of a table in MS-SQL. (I can do it in mySQL with DESCRIBE, so I guess I'm looking for an equivalent to that.)
Khtan:
Thanks for the suggestions!
I just tried
procedure sp_help my_table_name
but got back
"Microsoft JET Database Engine (0x80040E14)
Syntax error in PARAMETER clause."
Perhaps that stored procedure isn't supported by MS-Access?
Can you think of anything else I can try? (Or was my syntax incorrect?)
Thanks,
Colin.
|
Edited by - cfoster on 04/11/2006 23:50:53 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 04/11/2006 : 23:50:37
|
Oh MS Access. Sorry
try
select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = my_table_name
KH
|
|
|
|
cfoster
Starting Member
Canada
7 Posts |
Posted - 04/11/2006 : 23:59:13
|
Hmmm... The error I get now is:
Could not find file 'C:\WINNT\system32\INFORMATION_SCHEMA.mdb'.
Does this mean their server isn't quite setup correctly? (It's certainly possible.)
-Colin. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 04/12/2006 : 00:18:49
|
MS Access might not support INFORMATION_SCHEMA
Doesn't select * from my_table_name gives you the column name ?
KH
|
|
|
|
cfoster
Starting Member
Canada
7 Posts |
Posted - 04/12/2006 : 00:23:44
|
Sadly, no. 'Select *' returns just the data.
The fact that a path is given to the target file makes me think MS-Access knows what to do with that command, but is missing a file to do it. (But I could be wrong, it just seems odd that MS-Access would know the path of a service it doesn't have, if you see what I mean.)
Well, I guess I'm stuck, unless there are any other suggestions?
Thanks very much for all the feedback!
-Colin. |
Edited by - cfoster on 04/12/2006 00:27:04 |
|
|
|
jen
Flowing Fount of Yak Knowledge
Sweden
4110 Posts |
Posted - 04/12/2006 : 02:15:46
|
What's your front end?
usually if do it in vb, and save the recordset in a an ado, the object will have the column names...
not sure what you are using, there must be a way to identify the column unless it's array type...
--------------------
keeping it simple... |
|
|
|
Srinika
Flowing Fount of Yak Knowledge
Sri Lanka
1378 Posts |
Posted - 04/12/2006 : 08:25:10
|
Try in ASP
(U may need to define the record set, i and a connection object and connect to the DB, pass the
Select top 0 * from ur_table
and then do the following)
For i = 0 To rs.RecordCount - 1
rs.fields (i)
Next
Srinika
|
|
|
|
cfoster
Starting Member
Canada
7 Posts |
Posted - 04/12/2006 : 09:06:54
|
Jen:
The front end is ASP/Javascript, but I could just as easily write something in ASP/VBScript.
Currently I return the result in 'query_result' and loop through its .Fields(i) like so:
rec_count = g_query_result.Fields.Count - 1;
while( !g_query_result.EOF )
{
for( i = 0; i <= rec_count; i++ )
{
one_field = g_query_result.Fields(i);
Response.Write( one_field + " " );
}
Response.Write( "<br />" );
g_query_result.MoveNext;
}
-------------
Srinika:
I get an error when I try to select top 0, but I'll see what I can do in in VBScript since it handles objects differently.
[time passes]
Success! Thanks! Here's a code snippet to spill one's table's column names:
<%@ Language="vbscript" %>
<%
Dim objConn
Dim objRec
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRec = Server.CreateObject("ADODB.RecordSet")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; data source=PATH_TO_MS_ACCESS_DATABASE"
objRec.Open "MY_TABLE_NAME", objConn, 0, 1, 2
For Each fldF In objRec.Fields
Response.Write fldF.Name
Response.Write "<br />"
Next
objRec.Close
objConn.Close
Set objRec = Nothing
Set objConn = Nothing
%>
It turned out I originally created the column with the name "f_account_type" but thought it was "f_user_type".
Thanks again to everyone for their assistance!
-Colin.
|
Edited by - cfoster on 04/12/2006 09:08:06 |
|
|
|
Srinika
Flowing Fount of Yak Knowledge
Sri Lanka
1378 Posts |
Posted - 04/12/2006 : 09:15:49
|
quote:
Originally posted by cfoster
Srinika:
I get an error when I try to select top 0, but I'll see what I can do in in VBScript since it handles objects differently.
The reason is not the way VB Script handles it, but "top 0" is not understood by "Access"
Following should work;
<%@ Language="vbscript" %>
<%
Dim objConn
Dim objRec
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRec = Server.CreateObject("ADODB.RecordSet")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; data source=PATH_TO_MS_ACCESS_DATABASE"
objRec.Open "Select Top 1 * from MY_TABLE_NAME", objConn
For Each fldF In objRec.Fields
Response.Write fldF.Name
Response.Write "<br />"
Next
objRec.Close
objConn.Close
Set objRec = Nothing
Set objConn = Nothing
%>
Srinika
|
|
|
|
cfoster
Starting Member
Canada
7 Posts |
Posted - 04/12/2006 : 09:27:34
|
quote:
Originally posted by Srinika
objRec.Open "Select Top 1 * from MY_TABLE_NAME", objConn, 0, 1, 2
Hmmm... oddly enough that doesn't work. I get a syntax error. Yet if I copy/paste the same query into my javascript version, it works fine (but selects the first record, not the column headings).
Anyway, the way I had it with just stating the table name seems to work fine.
Thanks again!
-Colin.
EDIT: Sorry, I didn't notice you dropped the "0, 1, 2" from the end. It does work just as well. (I have no idea what those numbers specify, actually -- my code seems to run fine without them, too).
|
Edited by - cfoster on 04/12/2006 09:42:00 |
|
|
|
wjbarton
Starting Member
3 Posts |
Posted - 02/01/2007 : 18:24:50
|
This works for SQL server, borrowed code from sp_help. Change the second line to reflect the column name you're looking for:
declare @tblname varchar(100)
select @tblName = 'tblUsers'
declare @objid int
declare @sysobj_type char(2)
select @objid = id, @sysobj_type = xtype from sysobjects where id = object_id(@tblName)
select name from syscolumns where id = @objid |
|
|
|
wjbarton
Starting Member
3 Posts |
Posted - 02/01/2007 : 18:25:49
|
| On the above post I meant change the second line to reflcet the name of the table you're trying to get column names for... |
|
|
|
wjbarton
Starting Member
3 Posts |
Posted - 02/01/2007 : 18:30:13
|
Changed the last line to sort properly (was sorting by name by default), not sure what 'number = 0' is for, grabbed it from sp_help also:
select name from syscolumns where id = @objid and number = 0 order by colid |
|
|
|
Anthraxx
Starting Member
1 Posts |
Posted - 08/11/2009 : 13:58:45
|
Use the following code to get the names of all columns in a table:
Dim oConn As New System.Data.OleDb.OleDbConnection
Dim oCmd As New System.Data.OleDb.OleDbCommand
Dim iClm As Integer
oCmd.Connection = oConnection
~~Code to connect to the database~~
With oCmd
.CommandText = "SELECT * FROM TABLE_NAME"
With .ExecuteReader
'Get the column names
For iClm = 0 To .FieldCount - 1
'To get the name use:
'.GetName(iClm)
'For example:
lstColumns.Items.Add(.GetName(iClm))
Next
'Close the reader
.Close()
End With
End With
Greetz,
Anthraxx |
|
|
| |
Topic |
|
How to get column name from a table?
