name of field is in variable: how to use in SQL?

[Burt]

Hi,

The name of the field is in variable 'a'.
How can i use it into SQL?

This doesn't work:
<%
a="name"
set obj = Server.CreateObject("ADODB.Connection")
obj.Open("provider=Microsoft.Jet.OLEDB.4.0; Data Source =c:\db.mdb")
sql="select '" & a & "' from mytable;"
set rs=Server.CreateObject("ADODB.recordset")
rs.open sql, obj, 3, 3
....
%>

Thanks
Burt

[Bob Barrows [MVP]]

Burt wrote:

> Hi,
>
> The name of the field is in variable 'a'.
> How can i use it into SQL?
>
> This doesn't work:
> <%
> a="name"
> set obj = Server.CreateObject("ADODB.Connection")
> obj.Open("provider=Microsoft.Jet.OLEDB.4.0; Data Source =c:\db.mdb")
> sql="select '" & a & "' from mytable;"

Do a response.write sql. If a contains "thisfield", you will see this in the
browser window when you run this page:

select 'thisfield' from mytable

Open your database in Access. Create a new query using the Design View
option, switch to SQL View and paste this statement in. Run it to see what
you get. Do you see what you have to change to make it return the contents
of thisfield instead of the string 'thisfield'?

Always create and test your queries in Access before attempting to run them
in ASP.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Burt]

Sorry, in fact this works, but i can't recover the field values:

a="maandag"
set objdc = Server.CreateObject("ADODB.Connection")
objdc.Open("provider=Microsoft.Jet.OLEDB.4.0; Data Source
=d:\access\newres.mdb")
sql="select '" & a & "' from daguur;"
set rs=Server.CreateObject("ADODB.recordset")
rs.open sql, objdc, 3, 3
response.write(rs.RecordCount) ' this works

' i tried this but none works:
z=rs.fields("'a'").value
z=rs.fields('a').value
z=rs.fields(a).value
response.write(z)


"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:usgSxQheEHA.2812@tk2msftngp13.phx.gbl...

> Burt wrote:

> > Hi,
> >
> > The name of the field is in variable 'a'.
> > How can i use it into SQL?
> >
> > This doesn't work:
> > <%
> > a="name"
> > set obj = Server.CreateObject("ADODB.Connection")
> > obj.Open("provider=Microsoft.Jet.OLEDB.4.0; Data Source =c:\db.mdb")
> > sql="select '" & a & "' from mytable;"

>
> Do a response.write sql. If a contains "thisfield", you will see this in

the

> browser window when you run this page:
>
> select 'thisfield' from mytable
>
> Open your database in Access. Create a new query using the Design View
> option, switch to SQL View and paste this statement in. Run it to see what
> you get. Do you see what you have to change to make it return the contents
> of thisfield instead of the string 'thisfield'?
>
> Always create and test your queries in Access before attempting to run

them

> in ASP.
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

[Bob Barrows [MVP]]

Burt wrote:

> Sorry, in fact this works, but i can't recover the field values:
>
> a="maandag"
> set objdc = Server.CreateObject("ADODB.Connection")
> objdc.Open("provider=Microsoft.Jet.OLEDB.4.0; Data Source
> =d:\access\newres.mdb")
> sql="select '" & a & "' from daguur;"

You're not getting my point. Show us the result from "response.write sql"

> set rs=Server.CreateObject("ADODB.recordset")
> rs.open sql, objdc, 3, 3
> response.write(rs.RecordCount) ' this works

Of course this works. You are getting records back. You're just not getting
records containing a field named maandag. Do this to see what you are
getting:

for each fld in rs.Fields
response.write fld.OrdinalPosition & ". " & fld.name & ": " & _
fld.value & "<BR>"
next


Did you try my suggestion of response.writing the sql variable and copying
te result into the Access Query Builder to see what the sql statement you
built returned? Obviously not. I dislike hand-holding, but just to bring
this thread to an end, change your statement to:

sql="select [" & a & "] from daguur;"

I'm out of here.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Burt]

I appreciate your work, really, but you are not my teacher and i'm not your
student. Sometimes one needs a urgent answer and sometimes, there is no time
for experimentation.
Thanks anyway
B





"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23jR02DieEHA.3916@TK2MSFTNGP11.phx.gbl...

> Burt wrote:

> > Sorry, in fact this works, but i can't recover the field values:
> >
> > a="maandag"
> > set objdc = Server.CreateObject("ADODB.Connection")
> > objdc.Open("provider=Microsoft.Jet.OLEDB.4.0; Data Source
> > =d:\access\newres.mdb")
> > sql="select '" & a & "' from daguur;"

>
> You're not getting my point. Show us the result from "response.write sql"
>

> > set rs=Server.CreateObject("ADODB.recordset")
> > rs.open sql, objdc, 3, 3
> > response.write(rs.RecordCount) ' this works

>
> Of course this works. You are getting records back. You're just not

getting

> records containing a field named maandag. Do this to see what you are
> getting:
>
> for each fld in rs.Fields
> response.write fld.OrdinalPosition & ". " & fld.name & ": " & _
> fld.value & "<BR>"
> next
>
>
> Did you try my suggestion of response.writing the sql variable and copying
> te result into the Access Query Builder to see what the sql statement you
> built returned? Obviously not. I dislike hand-holding, but just to bring
> this thread to an end, change your statement to:
>
> sql="select [" & a & "] from daguur;"
>
> I'm out of here.
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[Bob Lehmann]

> but you are not my teacher and i'm not your student.
So you must be a freeloader, then. You're not paying anyone for their
assistance, so don't expect someone to do your work for you.

Following Bob's advice would have taken you less time than scolding him in a
follow-up post. You're also now assured of getting no further help at all.

Maybe you wouldn't have the need for an "urgent answer" if you were to
experiment and/or educate yourself a little.

Bob Lehmann

"Burt" <burtspam@me.us> wrote in message
news:ucp9WbieEHA.1048@tk2msftngp13.phx.gbl...

> I appreciate your work, really, but you are not my teacher and i'm not

your

> student. Sometimes one needs a urgent answer and sometimes, there is no

time

> for experimentation.
> Thanks anyway
> B
>
>
>
>
>
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:%23jR02DieEHA.3916@TK2MSFTNGP11.phx.gbl...

> > Burt wrote:

> > > Sorry, in fact this works, but i can't recover the field values:
> > >
> > > a="maandag"
> > > set objdc = Server.CreateObject("ADODB.Connection")
> > > objdc.Open("provider=Microsoft.Jet.OLEDB.4.0; Data Source
> > > =d:\access\newres.mdb")
> > > sql="select '" & a & "' from daguur;"

> >
> > You're not getting my point. Show us the result from "response.write

sql"

> >

> > > set rs=Server.CreateObject("ADODB.recordset")
> > > rs.open sql, objdc, 3, 3
> > > response.write(rs.RecordCount) ' this works

> >
> > Of course this works. You are getting records back. You're just not

> getting

> > records containing a field named maandag. Do this to see what you are
> > getting:
> >
> > for each fld in rs.Fields
> > response.write fld.OrdinalPosition & ". " & fld.name & ": " & _
> > fld.value & "<BR>"
> > next
> >
> >
> > Did you try my suggestion of response.writing the sql variable and

copying

> > te result into the Access Query Builder to see what the sql statement

you

> > built returned? Obviously not. I dislike hand-holding, but just to bring
> > this thread to an end, change your statement to:
> >
> > sql="select [" & a & "] from daguur;"
> >
> > I'm out of here.
> >
> > Bob Barrows
> >
> > --
> > Microsoft MVP -- ASP/ASP.NET
> > Please reply to the newsgroup. The email account listed in my From
> > header is my spam trap, so I don't check it very often. You will get a
> > quicker response by posting to the newsgroup.
> >
> >

>
>

[Burt]

Did somebody ask you something?

[Bob Lehmann]

It's a public forum. I felt compelled to contribute.

"Burt" <burtspam@me.us> wrote in message
news:eyVVBWmeEHA.1644@tk2msftngp13.phx.gbl...

> Did somebody ask you something?
>
>