Professional Web Applications Themes

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

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...

  1. #1

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

    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


    Burt Guest

  2. #2

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

    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] Guest

  3. #3

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

    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]" <reb01501NOyahoo.SPAMcom> wrote in message
    news:usgSxQheEHA.2812tk2msftngp13.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"
    >
    >

    Burt Guest

  4. #4

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

    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 Barrows [MVP] Guest

  5. #5

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

    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]" <reb01501NOyahoo.SPAMcom> wrote in message
    news:%23jR02DieEHA.3916TK2MSFTNGP11.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 Guest

  6. #6

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

    > 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" <burtspamme.us> wrote in message
    news:ucp9WbieEHA.1048tk2msftngp13.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]" <reb01501NOyahoo.SPAMcom> wrote in message
    > news:%23jR02DieEHA.3916TK2MSFTNGP11.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 Guest

  7. #7

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

    Did somebody ask you something?


    Burt Guest

  8. #8

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

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

    "Burt" <burtspamme.us> wrote in message
    news:eyVVBWmeEHA.1644tk2msftngp13.phx.gbl...
    > Did somebody ask you something?
    >
    >

    Bob Lehmann Guest

Similar Threads

  1. Iterative Insert with Variable Field Name
    By Loony2nz in forum Coldfusion Database Access
    Replies: 5
    Last Post: June 28th, 02:05 AM
  2. variable named field calculated from variable named fields
    By Chris Kemp in forum Macromedia ColdFusion
    Replies: 1
    Last Post: June 24th, 07:36 PM
  3. Display variable in text field
    By timrande in forum Macromedia ColdFusion
    Replies: 3
    Last Post: May 26th, 04:53 PM
  4. Hidden field with variable problem
    By Tom Petersen in forum ASP
    Replies: 8
    Last Post: September 15th, 07:31 PM
  5. Variable in the field name.
    By my in forum ASP
    Replies: 2
    Last Post: July 21st, 10:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139