Professional Web Applications Themes

Adding rows to database table thro ASP - ASP Components

Hi, I want to add a row to library table. Table has total 7 fields. But I want to create the new row with 5 field data. When I run the following query in MS Access strQ = "INSERT into library (index,title,author,itemtype,createdate) VALUES ('B-096','Hihihihihi','hello','Book','07/18/04')" It successfully adds the row. But same thing I am trying with the following ASP code. It gives error, saying syntax error in INSERT into statement. Can anybody suggest me, what is wrong in my code below. Any help is appreciated. Thanks in advance, Vasanth ----------------------------- <% option explicit Response.ExpiresAbsolute = "January 1, 2003" Dim path, ...

  1. #1

    Default Adding rows to database table thro ASP

    Hi,
    I want to add a row to library table. Table has total 7 fields. But I
    want to create the new row with 5 field data. When I run the following query
    in MS Access
    strQ = "INSERT into library (index,title,author,itemtype,createdate) VALUES
    ('B-096','Hihihihihi','hello','Book','07/18/04')"
    It successfully adds the row. But same thing I am trying with the following
    ASP code. It gives error, saying syntax error in INSERT into statement.

    Can anybody suggest me, what is wrong in my code below. Any help is
    appreciated.

    Thanks in advance,
    Vasanth
    -----------------------------
    <%
    option explicit
    Response.ExpiresAbsolute = "January 1, 2003"
    Dim path, mdbFilePath, sPos, objConnect, objRst, strQ, x
    for each x in Request.Form
    execute x & "="""&Request(x)&""""
    next
    path = Request.ServerVariables("PATH_TRANSLATED")
    sPos = InStr(path, "library\newupdate.asp")
    if sPos = 0 then
    sPos = InStr(path, "library/newupdate.asp")
    end if
    mdbFilePath = Left(path, sPos-1) & "library.mdb"
    Set objConnect = Server.CreateObject("ADODB.Connection")
    objConnect.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    mdbFilePath & ";"
    Set objRst = Server.CreateObject("ADODB.Recordset")
    Set objRst.ActiveConnection = objConnect
    'strQ = "INSERT into library (index,title,author,itemtype,createdate) VALUES
    ('"&session("index")&"','"&title&"','"&author&"',' "&session("itemtype")&"','
    "&date&"')"
    strQ = "INSERT into library (index,title,author,itemtype,createdate) VALUES
    ('B-096','Hihihihihi','hello','Book','07/18/04')"
    objRst.Open strQ
    Set objRst = nothing
    objConnect.Close
    Set objConnect = nothing
    %>
    <html>
    <head>
    <title>
    </title>
    </head>
    <body>
    Library Item, <%=session("index")%> has been created on <%=date%>.
    </body>
    </html>


    vasanth kumar Guest

  2. #2

    Default Adding rows to database table thro ASP

    Hi,
    I want to add a row to library table. Table has total 7 fields. But I
    want to create the new row with 5 field data. When I run the following query
    in MS Access
    strQ = "INSERT into library (index,title,author,itemtype,createdate) VALUES
    ('B-096','Hihihihihi','hello','Book','07/18/04')"
    It successfully adds the row. But same thing I am trying with the following
    ASP code. It gives error, saying syntax error in INSERT into statement.

    Can anybody suggest me, what is wrong in my code below. Any help is
    appreciated.

    Thanks in advance,
    Vasanth
    -----------------------------
    <%
    option explicit
    Response.ExpiresAbsolute = "January 1, 2003"
    Dim path, mdbFilePath, sPos, objConnect, objRst, strQ, x
    for each x in Request.Form
    execute x & "="""&Request(x)&""""
    next
    path = Request.ServerVariables("PATH_TRANSLATED")
    sPos = InStr(path, "library\newupdate.asp")
    if sPos = 0 then
    sPos = InStr(path, "library/newupdate.asp")
    end if
    mdbFilePath = Left(path, sPos-1) & "library.mdb"
    Set objConnect = Server.CreateObject("ADODB.Connection")
    objConnect.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    mdbFilePath & ";"
    Set objRst = Server.CreateObject("ADODB.Recordset")
    Set objRst.ActiveConnection = objConnect
    'strQ = "INSERT into library (index,title,author,itemtype,createdate) VALUES
    ('"&session("index")&"','"&title&"','"&author&"',' "&session("itemtype")&"','
    "&date&"')"
    strQ = "INSERT into library (index,title,author,itemtype,createdate) VALUES
    ('B-096','Hihihihihi','hello','Book','07/18/04')"
    objRst.Open strQ
    Set objRst = nothing
    objConnect.Close
    Set objConnect = nothing
    %>
    <html>
    <head>
    <title>
    </title>
    </head>
    <body>
    Library Item, <%=session("index")%> has been created on <%=date%>.
    </body>
    </html>


    vasanth kumar Guest

  3. #3

    Default Re: Adding rows to database table thro ASP

    You (and we) cannot debug your sql statement without knowing what it is. Put
    a Response.Write strQ statement into your code so you can see the sql
    statement being generated by your code. Usually, seeing the resulting sql
    statement will highlight where your error is, because you know what it's
    supposed to look like. If not, show it to us.

    Incidently, your query does not return any records, so there is absolutely
    no reason in the world to waste resources and CPU by creating and using a
    recordset object to run this query. A simple

    objConnect.Execute strQ,,129

    will suffice.

    Bob Barrows

    PS. The only relevant newsgroup for this question was .asp.db. You really
    did not have to crosspost it to .general, and .components is definitely
    off-topic.


    vasanth kumar wrote:
    > Hi,
    > I want to add a row to library table. Table has total 7 fields.
    > But I want to create the new row with 5 field data. When I run the
    > following query in MS Access
    > strQ = "INSERT into library (index,title,author,itemtype,createdate)
    > VALUES ('B-096','Hihihihihi','hello','Book','07/18/04')"
    > It successfully adds the row. But same thing I am trying with the
    > following ASP code. It gives error, saying syntax error in INSERT
    > into statement.
    >
    > Can anybody suggest me, what is wrong in my code below. Any help is
    > appreciated.
    >
    > Thanks in advance,
    > Vasanth
    > -----------------------------
    > <%
    > option explicit
    > Response.ExpiresAbsolute = "January 1, 2003"
    > Dim path, mdbFilePath, sPos, objConnect, objRst, strQ, x
    > for each x in Request.Form
    > execute x & "="""&Request(x)&""""
    > next
    > path = Request.ServerVariables("PATH_TRANSLATED")
    > sPos = InStr(path, "library\newupdate.asp")
    > if sPos = 0 then
    > sPos = InStr(path, "library/newupdate.asp")
    > end if
    > mdbFilePath = Left(path, sPos-1) & "library.mdb"
    > Set objConnect = Server.CreateObject("ADODB.Connection")
    > objConnect.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    > mdbFilePath & ";"
    > Set objRst = Server.CreateObject("ADODB.Recordset")
    > Set objRst.ActiveConnection = objConnect
    > 'strQ = "INSERT into library (index,title,author,itemtype,createdate)
    > VALUES
    >
    ('"&session("index")&"','"&title&"','"&author&"',' "&session("itemtype")&"','
    > "&date&"')"
    > strQ = "INSERT into library (index,title,author,itemtype,createdate)
    > VALUES ('B-096','Hihihihihi','hello','Book','07/18/04')"
    > objRst.Open strQ
    > Set objRst = nothing
    > objConnect.Close
    > Set objConnect = nothing
    > %>
    > <html>
    > <head>
    > <title>
    > </title>
    > </head>
    > <body>
    > Library Item, <%=session("index")%> has been created on <%=date%>.
    > </body>
    > </html>
    --
    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

  4. #4

    Default Re: Adding rows to database table thro ASP

    You (and we) cannot debug your sql statement without knowing what it is. Put
    a Response.Write strQ statement into your code so you can see the sql
    statement being generated by your code. Usually, seeing the resulting sql
    statement will highlight where your error is, because you know what it's
    supposed to look like. If not, show it to us.

    Incidently, your query does not return any records, so there is absolutely
    no reason in the world to waste resources and CPU by creating and using a
    recordset object to run this query. A simple

    objConnect.Execute strQ,,129

    will suffice.

    Bob Barrows

    PS. The only relevant newsgroup for this question was .asp.db. You really
    did not have to crosspost it to .general, and .components is definitely
    off-topic.


    vasanth kumar wrote:
    > Hi,
    > I want to add a row to library table. Table has total 7 fields.
    > But I want to create the new row with 5 field data. When I run the
    > following query in MS Access
    > strQ = "INSERT into library (index,title,author,itemtype,createdate)
    > VALUES ('B-096','Hihihihihi','hello','Book','07/18/04')"
    > It successfully adds the row. But same thing I am trying with the
    > following ASP code. It gives error, saying syntax error in INSERT
    > into statement.
    >
    > Can anybody suggest me, what is wrong in my code below. Any help is
    > appreciated.
    >
    > Thanks in advance,
    > Vasanth
    > -----------------------------
    > <%
    > option explicit
    > Response.ExpiresAbsolute = "January 1, 2003"
    > Dim path, mdbFilePath, sPos, objConnect, objRst, strQ, x
    > for each x in Request.Form
    > execute x & "="""&Request(x)&""""
    > next
    > path = Request.ServerVariables("PATH_TRANSLATED")
    > sPos = InStr(path, "library\newupdate.asp")
    > if sPos = 0 then
    > sPos = InStr(path, "library/newupdate.asp")
    > end if
    > mdbFilePath = Left(path, sPos-1) & "library.mdb"
    > Set objConnect = Server.CreateObject("ADODB.Connection")
    > objConnect.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    > mdbFilePath & ";"
    > Set objRst = Server.CreateObject("ADODB.Recordset")
    > Set objRst.ActiveConnection = objConnect
    > 'strQ = "INSERT into library (index,title,author,itemtype,createdate)
    > VALUES
    >
    ('"&session("index")&"','"&title&"','"&author&"',' "&session("itemtype")&"','
    > "&date&"')"
    > strQ = "INSERT into library (index,title,author,itemtype,createdate)
    > VALUES ('B-096','Hihihihihi','hello','Book','07/18/04')"
    > objRst.Open strQ
    > Set objRst = nothing
    > objConnect.Close
    > Set objConnect = nothing
    > %>
    > <html>
    > <head>
    > <title>
    > </title>
    > </head>
    > <body>
    > Library Item, <%=session("index")%> has been created on <%=date%>.
    > </body>
    > </html>
    --
    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

  5. #5

    Default Re: Adding rows to database table thro ASP

    On Sun, 18 Jul 2004 16:37:29 +0530, "vasanth kumar"
    <vasanth.kumareds.com> wrote:

    Post to a relevant group only. The asp.db group is fine, no need to
    post to others. I'm only replying in the asp.db group for that
    reason.
    > I want to add a row to library table. Table has total 7 fields. But I
    >want to create the new row with 5 field data.
    Well, that's confusing. Are you saying you want to INSERT a new
    record where two fileds have no value?
    >When I run the following query
    >in MS Access
    >strQ = "INSERT into library (index,title,author,itemtype,createdate) VALUES
    >('B-096','Hihihihihi','hello','Book','07/18/04')"
    >It successfully adds the row. But same thing I am trying with the following
    >ASP code. It gives error, saying syntax error in INSERT into statement.
    My guess is the error you see is:

    "Cannot insert record until operator has two beers."

    If that's correct, I recommend a light beer.

    If that's not the error, it would help to post the actual error.
    >Can anybody suggest me, what is wrong in my code below. Any help is
    >appreciated.
    Well, besides the fact that you go to a lot of effort to find the path
    to the MDB file when you could have done a SERVER_PATH, there really
    isn't anything specifically wrong.

    We need the error you see, plus your actual SQL Query to diagnose
    this. Do a Response.Write of your strQ and show it to us.

    Jeff
    >Thanks in advance,
    >Vasanth
    >-----------------------------
    ><%
    >option explicit
    >Response.ExpiresAbsolute = "January 1, 2003"
    >Dim path, mdbFilePath, sPos, objConnect, objRst, strQ, x
    >for each x in Request.Form
    > execute x & "="""&Request(x)&""""
    >next
    >path = Request.ServerVariables("PATH_TRANSLATED")
    >sPos = InStr(path, "library\newupdate.asp")
    >if sPos = 0 then
    >sPos = InStr(path, "library/newupdate.asp")
    >end if
    >mdbFilePath = Left(path, sPos-1) & "library.mdb"
    >Set objConnect = Server.CreateObject("ADODB.Connection")
    >objConnect.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    >mdbFilePath & ";"
    >Set objRst = Server.CreateObject("ADODB.Recordset")
    >Set objRst.ActiveConnection = objConnect
    >'strQ = "INSERT into library (index,title,author,itemtype,createdate) VALUES
    >('"&session("index")&"','"&title&"','"&author&"', '"&session("itemtype")&"','
    >"&date&"')"
    >strQ = "INSERT into library (index,title,author,itemtype,createdate) VALUES
    >('B-096','Hihihihihi','hello','Book','07/18/04')"
    >objRst.Open strQ
    >Set objRst = nothing
    >objConnect.Close
    >Set objConnect = nothing
    >%>
    ><html>
    ><head>
    ><title>
    ></title>
    ></head>
    ><body>
    >Library Item, <%=session("index")%> has been created on <%=date%>.
    ></body>
    ></html>
    >
    Jeff Cochran Guest

  6. #6

    Default Re: Adding rows to database table thro ASP

    Check for reserved words and use ## for dates....

    "vasanth kumar" <vasanth.kumareds.com> wrote in message
    news:uZkGudLbEHA.3476tk2msftngp13.phx.gbl...
    > Hi,
    > I want to add a row to library table. Table has total 7 fields. But I
    > want to create the new row with 5 field data. When I run the following
    query
    > in MS Access
    > strQ = "INSERT into library (index,title,author,itemtype,createdate)
    VALUES
    > ('B-096','Hihihihihi','hello','Book','07/18/04')"
    > It successfully adds the row. But same thing I am trying with the
    following
    > ASP code. It gives error, saying syntax error in INSERT into statement.
    >
    > Can anybody suggest me, what is wrong in my code below. Any help is
    > appreciated.
    >
    > Thanks in advance,
    > Vasanth
    > -----------------------------
    > <%
    > option explicit
    > Response.ExpiresAbsolute = "January 1, 2003"
    > Dim path, mdbFilePath, sPos, objConnect, objRst, strQ, x
    > for each x in Request.Form
    > execute x & "="""&Request(x)&""""
    > next
    > path = Request.ServerVariables("PATH_TRANSLATED")
    > sPos = InStr(path, "library\newupdate.asp")
    > if sPos = 0 then
    > sPos = InStr(path, "library/newupdate.asp")
    > end if
    > mdbFilePath = Left(path, sPos-1) & "library.mdb"
    > Set objConnect = Server.CreateObject("ADODB.Connection")
    > objConnect.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    > mdbFilePath & ";"
    > Set objRst = Server.CreateObject("ADODB.Recordset")
    > Set objRst.ActiveConnection = objConnect
    > 'strQ = "INSERT into library (index,title,author,itemtype,createdate)
    VALUES
    >
    ('"&session("index")&"','"&title&"','"&author&"',' "&session("itemtype")&"','
    > "&date&"')"
    > strQ = "INSERT into library (index,title,author,itemtype,createdate)
    VALUES
    > ('B-096','Hihihihihi','hello','Book','07/18/04')"
    > objRst.Open strQ
    > Set objRst = nothing
    > objConnect.Close
    > Set objConnect = nothing
    > %>
    > <html>
    > <head>
    > <title>
    > </title>
    > </head>
    > <body>
    > Library Item, <%=session("index")%> has been created on <%=date%>.
    > </body>
    > </html>
    >
    >

    dave Guest

  7. #7

    Default Re: Adding rows to database table thro ASP

    Check for reserved words and use ## for dates....

    "vasanth kumar" <vasanth.kumareds.com> wrote in message
    news:uZkGudLbEHA.3476tk2msftngp13.phx.gbl...
    > Hi,
    > I want to add a row to library table. Table has total 7 fields. But I
    > want to create the new row with 5 field data. When I run the following
    query
    > in MS Access
    > strQ = "INSERT into library (index,title,author,itemtype,createdate)
    VALUES
    > ('B-096','Hihihihihi','hello','Book','07/18/04')"
    > It successfully adds the row. But same thing I am trying with the
    following
    > ASP code. It gives error, saying syntax error in INSERT into statement.
    >
    > Can anybody suggest me, what is wrong in my code below. Any help is
    > appreciated.
    >
    > Thanks in advance,
    > Vasanth
    > -----------------------------
    > <%
    > option explicit
    > Response.ExpiresAbsolute = "January 1, 2003"
    > Dim path, mdbFilePath, sPos, objConnect, objRst, strQ, x
    > for each x in Request.Form
    > execute x & "="""&Request(x)&""""
    > next
    > path = Request.ServerVariables("PATH_TRANSLATED")
    > sPos = InStr(path, "library\newupdate.asp")
    > if sPos = 0 then
    > sPos = InStr(path, "library/newupdate.asp")
    > end if
    > mdbFilePath = Left(path, sPos-1) & "library.mdb"
    > Set objConnect = Server.CreateObject("ADODB.Connection")
    > objConnect.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    > mdbFilePath & ";"
    > Set objRst = Server.CreateObject("ADODB.Recordset")
    > Set objRst.ActiveConnection = objConnect
    > 'strQ = "INSERT into library (index,title,author,itemtype,createdate)
    VALUES
    >
    ('"&session("index")&"','"&title&"','"&author&"',' "&session("itemtype")&"','
    > "&date&"')"
    > strQ = "INSERT into library (index,title,author,itemtype,createdate)
    VALUES
    > ('B-096','Hihihihihi','hello','Book','07/18/04')"
    > objRst.Open strQ
    > Set objRst = nothing
    > objConnect.Close
    > Set objConnect = nothing
    > %>
    > <html>
    > <head>
    > <title>
    > </title>
    > </head>
    > <body>
    > Library Item, <%=session("index")%> has been created on <%=date%>.
    > </body>
    > </html>
    >
    >

    dave Guest

  8. #8

    Default Re: Adding rows to database table thro ASP

    This is a nice suggestion.
    I was using
    strQ = "INSERT into library (index,title,author,itemtype,createdate) VALUES
    ('B-096','Hihihihihi','hello','Book','07/18/04')"
    I changed that to
    strQ = "INSERT into library ([index],title,author,itemtype,createdate)
    VALUES ('B-096','Hihihihihi','hello','Book','07/18/04')"
    believe me, it started working

    Thanks to all those who responded.
    Vasanth
    ---------------------------
    "Bob Barrows [MVP]" <reb01501NOyahoo.SPAMcom> wrote in message
    news:unq1RQMbEHA.2944TK2MSFTNGP11.phx.gbl...
    > You (and we) cannot debug your sql statement without knowing what it is.
    Put
    > a Response.Write strQ statement into your code so you can see the sql
    > statement being generated by your code. Usually, seeing the resulting sql
    > statement will highlight where your error is, because you know what it's
    > supposed to look like. If not, show it to us.
    >
    > Incidently, your query does not return any records, so there is absolutely
    > no reason in the world to waste resources and CPU by creating and using a
    > recordset object to run this query. A simple
    >
    > objConnect.Execute strQ,,129
    >
    > will suffice.
    >
    > Bob Barrows
    >
    > PS. The only relevant newsgroup for this question was .asp.db. You really
    > did not have to crosspost it to .general, and .components is definitely
    > off-topic.
    >
    >
    > vasanth kumar wrote:
    > > Hi,
    > > I want to add a row to library table. Table has total 7 fields.
    > > But I want to create the new row with 5 field data. When I run the
    > > following query in MS Access
    > > strQ = "INSERT into library (index,title,author,itemtype,createdate)
    > > VALUES ('B-096','Hihihihihi','hello','Book','07/18/04')"
    > > It successfully adds the row. But same thing I am trying with the
    > > following ASP code. It gives error, saying syntax error in INSERT
    > > into statement.
    > >
    > > Can anybody suggest me, what is wrong in my code below. Any help is
    > > appreciated.
    > >
    > > Thanks in advance,
    > > Vasanth
    > > -----------------------------
    > > <%
    > > option explicit
    > > Response.ExpiresAbsolute = "January 1, 2003"
    > > Dim path, mdbFilePath, sPos, objConnect, objRst, strQ, x
    > > for each x in Request.Form
    > > execute x & "="""&Request(x)&""""
    > > next
    > > path = Request.ServerVariables("PATH_TRANSLATED")
    > > sPos = InStr(path, "library\newupdate.asp")
    > > if sPos = 0 then
    > > sPos = InStr(path, "library/newupdate.asp")
    > > end if
    > > mdbFilePath = Left(path, sPos-1) & "library.mdb"
    > > Set objConnect = Server.CreateObject("ADODB.Connection")
    > > objConnect.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
    > > mdbFilePath & ";"
    > > Set objRst = Server.CreateObject("ADODB.Recordset")
    > > Set objRst.ActiveConnection = objConnect
    > > 'strQ = "INSERT into library (index,title,author,itemtype,createdate)
    > > VALUES
    > >
    >
    ('"&session("index")&"','"&title&"','"&author&"',' "&session("itemtype")&"','
    > > "&date&"')"
    > > strQ = "INSERT into library (index,title,author,itemtype,createdate)
    > > VALUES ('B-096','Hihihihihi','hello','Book','07/18/04')"
    > > objRst.Open strQ
    > > Set objRst = nothing
    > > objConnect.Close
    > > Set objConnect = nothing
    > > %>
    > > <html>
    > > <head>
    > > <title>
    > > </title>
    > > </head>
    > > <body>
    > > Library Item, <%=session("index")%> has been created on <%=date%>.
    > > </body>
    > > </html>
    >
    > --
    > 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"
    >
    >

    vasanth kumar Guest

Similar Threads

  1. Adding rows to database table thro' ASP
    By vasanth kumar in forum ASP Database
    Replies: 0
    Last Post: July 18th, 11:05 AM
  2. Replies: 0
    Last Post: September 16th, 04:37 PM
  3. Replies: 0
    Last Post: September 15th, 05:39 AM
  4. Replies: 0
    Last Post: September 10th, 05:33 PM
  5. Replies: 0
    Last Post: September 10th, 05:29 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