SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    Baltimore
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Insert Data Error Message

    I am getting the following error message when I am inserting data into the database:

    -2147467259

    Here is the insert code.

    <%
    connectionstring = "DRIVER={MySQL ODBC 3.51 Driver};Server=xxx;Database=xxx;uid=xxx;"
    Set cn = Server.CreateObject("ADODB.Connection")
    On error resume next
    cn.Open connectionstring
    if err <> 0 then
    cn.Close
    usrmsg = "Error opening connection to database."
    Response.Redirect("/error.asp?err=" & err & "&usrmsg=" & usrmsg)
    else
    Set member = Server.CreateObject("ADODB.Recordset")
    member.ActiveConnection = cn
    'start of form
    id = request.Form("id")
    firstname = request.Form("firstname")
    lastname = request.Form("lastname")
    address1 = request.Form("address1")
    address2 = request.Form("address2")
    city = request.Form("city")
    states = request.Form("states")
    zip = request.Form("zip")
    country = request.Form("country")
    email = request.Form("email")
    newsletter = request.Form("newsletter")
    rdate = FormatDateTime(Date)

    fields = ""
    fields = fields & "id,"
    fields = fields & "firstname,"
    fields = fields & "lastname,"
    fields = fields & "address1,"
    fields = fields & "address2,"
    fields = fields & "city,"
    fields = fields & "states,"
    fields = fields & "zip,"
    fields = fields & "country,"
    fields = fields & "email,"
    fields = fields & "newsletter,"
    fields = fields & "rdate"

    values = ""
    values = values & "'" & id & "',"
    values = values & "'" & firstname & "',"
    values = values & "'" & lastname & "',"
    values = values & "'" & address1 & "',"
    values = values & "'" & address2 & "',"
    values = values & "'" & city & "',"
    values = values & "'" & states & "',"
    values = values & "'" & zip & "',"
    values = values & "'" & country & "',"
    values = values & "'" & email & "',"
    values = values & "'" & newsletter & "',"
    values = values & "'" & rdate & "'"

    sqltext = ""
    sqltext = sqltext & "insert into contact (" & fields & ") values (" & values & ")"
    'Response.Write(sqltext & "<br>")
    On error resume next
    member.open sqltext
    if err <> 0 then
    usrmsg = "Error inserting record into database."
    Response.Redirect("error.asp?err=" & err & "&usrmsg=" & usrmsg)
    end if

    end if
    cn.close
    %>

    any help would be greatly appreciated.

  2. #2
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the only thing I found on google for that number was a "sql server timeout" error. Does it take a long time to show up?

    Also, instead of redirecting with "?err=" & err (which would print out that god awful and extremely annoying number) try
    Response.Redirect("error.asp?err=" & err.message & "&usrmsg=" & usrmsg)

    and see if that sheds a bit more light

  3. #3
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what does your sql query look like when you write it out? there's prolly a misplaced single quote.

  4. #4
    SitePoint Guru SSJ's Avatar
    Join Date
    Jan 2007
    Posts
    830
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Whats your sql query prints out?

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    Baltimore
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bbolte View Post
    what does your sql query look like when you write it out? there's prolly a misplaced single quote.
    Thanks for the help. I just went into sql and added a record and pulled out the code:
    INSERT INTO `contact` ( `id` , `firstname` , `lastname` , `address1` , `address2` , `city` , `states` , `zip` , `country` , `email` , `newsletter` , `rdate` )
    VALUES (
    '6', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', '1/30/07'
    );


    Thanks again for the help, I am sure that it is something easy that I'm just over looking.

  6. #6
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but you need to make sure that your sql query looks ok in your code. uncomment this line: 'Response.Write(sqltext & "<br>") and see what it looks like there.

  7. #7
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh and in reality - you don't need to use a recordset object to do update, inserts or deletes. only use recordsets when returning data. use something like the connection object execute instead.

    cn.execute sql

  8. #8
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    Baltimore
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bbolte View Post
    but you need to make sure that your sql query looks ok in your code. uncomment this line: 'Response.Write(sqltext & "<br>") and see what it looks like there.
    When I uncommented out this line : 'Response.Write(sqltext & "<br>")
    I got the error message again -2147467259 can not insert records

  9. #9
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    doh! put response.end right after that line. this will stop the script from continuing. then check your statement to make sure the syntax is correct.

    and obviously, be sure to comment or remove that line when putting it all back.


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
  •