SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 29
  1. #1
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy inserting data into sql table using asp/vbscript

    can anyone check the errors.
    If execute this code this accepting code from my page but not adding to database table.



    <div align="center">
    <input type="submit" value="Done" name="submit">
    <%

    If Request.form("submit") <> "" Then

    dim sql

    OpenSqlConn

    sql= "insert into ltbl_master(Itemid,Item,case_i)values( "
    sql=sql & "('" &Replace(Request.Form("Itemid"),"'","'")& "',"
    sql=sql & "('" &Replace(Request.Form("Item"),"'","'")& "',"
    sql=sql & "('" &Replace(Request.Form("case_i"),"'","'")& "')"


    Conn.Execute(sql)


    End If
    Conn.close
    %>

    can anyone find out the solution for this.

    my database is sqlserver.
    front end:asp/vbscript

    my connection is in another file. I have included that file.
    For the connection part there are no errors.

  2. #2
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    accepting data from fields but not adding to database table.

  3. #3
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    verify your sql statement by writing it out.

    response.write (sql)

    you will see if there are any syntax errors.

  4. #4
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    there are no errors. still not adding data to database file

  5. #5
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    have you tried running a populated sample sql statement in your database to test if it works ?

  6. #6
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    359
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Also review your asp code for any "on error resume next" statements before your db operation. If you find any, comment them out and see if you then see any asp error message.
    Doug G
    =====
    "If you ain't the lead dog, the view is always the same - Anon

  7. #7
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    Now I have used resume next

    but still it is not adding the record to databse


    <div align="center">
    <input type="submit" value="Done" name="submit">
    <%

    If Request.form("submit") <> "" Then

    dim sql

    OpenSqlConn

    sql= "insert into ltbl_master(Itemid,Item,case_i)values( "
    sql=sql & "('" &Replace(Request.Form("Itemid"),"'","'")& "',"
    sql=sql & "('" &Replace(Request.Form("Item"),"'","'")& "',"
    sql=sql & "('" &Replace(Request.Form("case_i"),"'","'")& "')"


    on error resume next
    Conn.Execute sql,recaffected
    if err<>0 then
    Response.Write("No update permissions!")
    else
    Response.Write("<h3>" & recaffected & " record added</h3>")
    end if
    Conn.close
    End If


    It accepting in form .. but not adding to database.

    Can anyone plz give the correct code for me.. to insert a new record into sql(database) using vbscript..


    I have tried several ways.. but didn't work out for me.

  8. #8
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code ASP:
    <%
     
     
    if request.form("submit") <>"" then
    	set conn=Server.CreateObject("ADODB.Connection")
    	conn.Provider="Microsoft.Jet.OLEDB.4.0"
    	conn.Open server.mapPath("northwind.mdb")
     
    	sql="INSERT INTO customers (customerID,companyname,"
    	sql=sql & "contactname,address,city,postalcode,country)"
    	sql=sql & " VALUES "
    	sql=sql & "('" & Request.Form("custid") & "',"
    	sql=sql & "'" & Request.Form("compname") & "',"
    	sql=sql & "'" & Request.Form("contname") & "',"
    	sql=sql & "'" & Request.Form("address") & "',"
    	sql=sql & "'" & Request.Form("city") & "',"
    	sql=sql & "'" & Request.Form("postcode") & "',"
    	sql=sql & "'" & Request.Form("country") & "')"
     
    	response.write "sql = " & sql
     
    	on error resume next
    	conn.Execute sql,recaffected
    	if err<>0 then
    	  Response.Write(err.description)
    	else
    	  Response.Write("<h3>" & recaffected & " record added</h3>")
    	end if
    	conn.close
     
    else
    	response.write "no record added"
    end if
    %>
     
     
    <html>
    <body>
     
    <form method="post" action="">
    <table>
    <tr>
    <td>CustomerID:</td>
    <td><input name="custid" value="1111"></td>
    </tr><tr>
    <td>Company Name:</td>
    <td><input name="compname" value="myName"></td>
    </tr><tr>
    <td>Contact Name:</td>
    <td><input name="contname" value="myContactName"></td>
    </tr><tr>
    <td>Address:</td>
    <td><input name="address" value="myAddress"></td>
    </tr><tr>
    <td>City:</td>
    <td><input name="city" value="myCity"></td>
    </tr><tr>
    <td>Postal Code:</td>
    <td><input name="postcode" value="12345"></td>
    </tr><tr>
    <td>Country:</td>
    <td><input name="country" value="myCountry"></td>
    </tr>
    </table>
    <br /><br />
    <input name ="submit" type="submit" value="Add New">
    <input type="reset" value="Cancel">
    </form>
     
    </body>
    </html>

  9. #9
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is this working correctly..did you try on your system..

    I will try and i will let you know..

    Thanks for your help.

  10. #10
    SitePoint Zealot
    Join Date
    Jan 2007
    Location
    Almere, The Netherlands
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When looking at this code, I really don't understand why people still try to learn classic ASP, while .NET is already more than 10 years on the market, and all the tools to create websites are free.

    But if you really want to use classic ASP, like in every language, it is important to use good coding techniques. The code examples here are exmaples of Bad codeing pratices. The code is vulnarable to SQL injections and it will break if users enter certain characters.

    To prevent this, many times peope advice to "sanitize" user input, escaping single quotes or whatever. This all ain't necesarry, the best of doing things is to use parameterized queries, which is not well known, but also possible in Classic ASP:

    http://blog.binarybooyah.com/blog/po...rized-SQL.aspx

    But I strongly advice starters not to use classic ASP, but start with ASP.NET:

    http://www.asp.net/get-started

    On that site you'll also find many tutorials and video's explaining how to start

    http://www.asp.net/web-forms

  11. #11
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @Verschha..

    I know the market trend.

    As per my project demands classic asp , I need to know some coding techniques.

    May be it is old. It doesn't matter what it is.

    I have to do this in classic asp and vbscript.

    Thanks for providing links.

  12. #12
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    School/college class module?
    Ian Anderson
    www.siteguru.co.uk

  13. #13
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can any one really help me in this.. I tried of doing this again and again. i couldn't figure out the error what it was.

    displaying values on form but not in database.

    If check the database. ther is no new record added..


    //////addnew.asp//////
    <body>
    <form method="post" action="mast.asp">


    <div align="center">
    <input type="submit" value="Done" onclick="add()">

    <%

    Sub add()

    dim sql

    OpenSqlConn

    sql= "insert into master(Itemid,Item,case_i)values( "
    sql=sql & "('" &Replace(Request.Form("Itemid"),"'","'")& "',"
    sql=sql & "('" &Replace(Request.Form("Item"),"'","'")& "',"
    sql=sql & "('" &Replace(Request.Form("case_i"),"'","'")& "')"

    Respone.Write "sql=" &sql


    on error resume next
    Conn.Execute sql,recaffected
    if err<>0 then
    Response.Write("No update permissions!")
    else
    Response.Write("<h3>" & recaffected & " record added</h3>")
    end if
    Conn.close

    End sub

    </form>
    </body>
    -----------------------------------------------------------------------------------

    ////////mast.asp////////////////////////////////
    this one is the main page. If i click on addnew.asp link It will open that page and accept values and display it in mast.asp.




    <body>
    <div align="center">
    <form method="post" action="mast.asp">
    <table>
    <tr><td><a href="addnew.asp">Add New</a></td></tr>
    <tr><td><a href="edit.asp">Edit Item</a></td></tr>
    </table>

    <%
    OpenSQLConn

    sql_query="Select * from master"

    Set rs=Conn.Execute(sql_query)
    do while not rs.EOF
    Response.Write RS("Itemid") & " = " & RS("Item") & "<br>"
    rs.MoveNext
    loop

    rs.close
    Conn.close
    %>
    </body>
    </form>

  14. #14
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no school/college module..

  15. #15
    SitePoint Member
    Join Date
    Oct 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why do .NET people come into a Classic ASP forum to complain about people still using Classic ASP? If it's that irritating for you, don't come in here. Many companies have years and years worth of software written in classic and they aren't willing to spend the time (=money) to convert to current technology. Should a developer quit their job or not accept a job because the technology being used isn't the latest?

    Quote Originally Posted by verschha View Post
    When looking at this code, I really don't understand why people still try to learn classic ASP, while .NET is already more than 10 years on the market, and all the tools to create websites are free.

    But if you really want to use classic ASP, like in every language, it is important to use good coding techniques. The code examples here are exmaples of Bad codeing pratices. The code is vulnarable to SQL injections and it will break if users enter certain characters.

    To prevent this, many times peope advice to "sanitize" user input, escaping single quotes or whatever. This all ain't necesarry, the best of doing things is to use parameterized queries, which is not well known, but also possible in Classic ASP:

    http://blog.binarybooyah.com/blog/po...rized-SQL.aspx

    But I strongly advice starters not to use classic ASP, but start with ASP.NET:

    http://www.asp.net/get-started

    On that site you'll also find many tutorials and video's explaining how to start

    http://www.asp.net/web-forms

  16. #16
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    @ webber123456

    I have created customer table in access and created asp file.

    this was the error i have got

    sql = INSERT INTO customer (customerID,companyname,contactname,address,city,postalcode,country) VALUES ('23','hjd','djd','dh','jjd','jd','jd')Object required

    I didn't understand what object is required.

    the fields are adding on the form.
    but if check the database file. there are no records.


  17. #17
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks muffybunny for understanding my feelings..

  18. #18
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,037
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    It looks to me like your SQL string has an error in it because customerID should be a number but because it has single quotes around it, it is being cast as a string. Can you edit your script to remove the single quotes around the customerID variable?

    Unless I'm missing something your SQL should look like this
    Code:
    INSERT INTO customer (customerID,companyname,contactname,address,city,postalcode,country) VALUES (23,'hjd','djd','dh','jjd','jd','jd')
    EDIT: The issue is likely here in the code on post #8:
    Code:
    sql=sql & "('" & Request.Form("custid") & "',"
    Change it (remove single quotes) to:
    Code:
    sql=sql & "(" & Request.Form("custid") & ","
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  19. #19
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lucky20 View Post
    is this working correctly..did you try on your system..

    I will try and i will let you know..

    Thanks for your help.
    I tested this before I posted the answer. It uses the standard Northwind database from Microsoft Access.

    It does work.

    If you are still having troubles then you need to make sure you copied/installed it correctly.

    If you can't get this to work then you may need to hire someone to go in and set it up correctly for you.

  20. #20
    SitePoint Wizard bronze trophy
    Join Date
    Oct 2001
    Location
    Vancouver BC Canada
    Posts
    2,037
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lucky20 View Post
    @ webber123456

    I didn't understand what object is required.
    This is another thought... The object might be your SQL connection. I see several references in your examples of OpenSqlConn and I see you trying to close Conn (Conn.close). I also see in one of your snips of code that you are trying to close the rs object but I don't see code where you instantiate it either.

    This is to do with your example code and not webber123456's examples because you can see that Conn is instantiated in his.
    Andrew Wasson | www.lunadesign.org
    Principal / Internet Development

  21. #21
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    open Sql Connection:- I have specified sql connection in that.I am just opening here and closing..

    It is working with sql Select statement..There is nothing wrong with sql connection.

    My problem is with insert statement. My form is executing.. and taking values in the form.. if you go to sql table there is no inserted data.

    There is nothing wrong with Sql Connection.

  22. #22
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got the solution..
    Now it is adding to records.


    sql= "insert into mast(Itemid,Item,case_i)values( "
    sql=sql & "('" &Replace(Request.Form("Itemid"),"'","'")& "',"
    sql=sql & "('" &Replace(Request.Form("Item"),"'","'")& "',"
    sql=sql & "('" &Replace(Request.Form("case_i"),"'","'")& "')"

    Conn.execute(sql)
    on error resume next
    if err<>0 then
    Response.Write("No update permissions!")
    else
    Response.Write("<h3>" & recaffected & " record added</h3>")
    end if
    Response.Redirect("mast.asp")
    Conn.close



    If you don't know the answer, plz leave the page. Don't give stupid suggestions..

  23. #23
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks to all who are trying to help..

  24. #24
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lucky20 View Post
    If you don't know the answer, plz leave the page. Don't give stupid suggestions..
    Other than verschha (whose comment was off-topic but that's all), who has given any stupid suggestions? You'll quickly lose any good will to help you here with that sort of attitude.
    Ian Anderson
    www.siteguru.co.uk

  25. #25
    SitePoint Enthusiast lucky20's Avatar
    Join Date
    Oct 2010
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that one only..not to others..
    remaining people are trying to help me..


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
  •