Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102

    Unanswered: .update creates new record but all fields null

    Hi guys, I have made several Access-based CMSs but now I am using SQL Server. I can read the records but my first attempts at writing are resulting in new records (with new ID) but all the fields are null.
    I am posting the data from a form to the same page and an if /then statement catches the flag in the URL and runs the update script below. All the field names are correct.
    if request.QueryString("add")<> "" then
    Dim rsUpdateEntry
    Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset")
    rsUpdateEntry.Open "SELECT * from generic_country_info" , oConn, 2, 3

    rsUpdateEntry.AddNew

    rsUpdateEntry.Fields("title1") = Request.Form("title1")
    rsUpdateEntry.Fields("body1") = Request.Form("body1")
    rsUpdateEntry.Fields("title2") = Request.Form("title2")
    rsUpdateEntry.Fields("body2") = Request.Form("body2")
    rsUpdateEntry.Fields("title3") = Request.Form("title3")
    rsUpdateEntry.Fields("body3") = Request.Form("body3")
    rsUpdateEntry.Fields("title4") = Request.Form("title4")
    rsUpdateEntry.Fields("body4") = Request.Form("body4")
    rsUpdateEntry.Fields("title5") = Request.Form("title5")
    rsUpdateEntry.Fields("body5") = Request.Form("body5")
    rsUpdateEntry.Fields("image1") = Request.Form("attach1")
    rsUpdateEntry.Fields("image2") = Request.Form("attach2")
    rsUpdateEntry.Fields("image3") = Request.Form("attach3")
    rsUpdateEntry.Fields("image4") = Request.Form("attach4")
    rsUpdateEntry.Fields("image5") = Request.Form("attach5")
    rsUpdateEntry.Fields("country") = Request.Form("country")
    rsUpdateEntry.Fields("dest_url") = Request.Form("dest_url")


    rsUpdateEntry.Update

    rsUpdateEntry.Close
    Set rsUpdateEntry = Nothing
    end if
    Thanks
    Mark

  2. #2
    Join Date
    Nov 2005
    Location
    Honolulu HI
    Posts
    119
    Well...

    my first comment would be... is that actually the code you use ?

    if you are inserting a new record into the database, there is no reason to do a full SELECT first into a recordset.
    all your doing is sucking down extra cycles for the system

    you should find that if you convert your code to this it should be substantially faster
    if request.QueryString("add")<> "" then

    set TheDatabase = server.createobject("ADODB.Connection")

    ' This is a DSN Connection. it does not require Driver name or server name specified. only the "provider" and the "DSN name"
    ' the DSN is something you set up when defining the databse in your control panel.
    ' (these 5 lines actually all go on one line)

    TheDatabase.Open "PROVIDER=MSDASQL;
    DSN=the_DSN_Name_You_Created;
    UID=the_UserID_You_Created ;
    Password=the_Passowrd_You_Gave_it;
    Database=the_Database_Name_You_Created"

    title1 = Request.Form("title1")
    body1 = Request.Form("body1")
    title2 = Request.Form("title2")
    body2 = Request.Form("body2")
    title3 = Request.Form("title3")
    body3 = Request.Form("body3")
    title4 = Request.Form("title4")
    body4 = Request.Form("body4")
    title5 = Request.Form("title5")
    body5 = Request.Form("body5")
    image1 = Request.Form("attach1")
    image2 = Request.Form("attach2")
    image3 = Request.Form("attach3")
    image4 = Request.Form("attach4")
    image5 = Request.Form("attach5")
    country = Request.Form("country")
    dest_url = Request.Form("dest_url")


    SQL="INSERT INTO generic_country_info (title1 , body1, title2 , body2, title3 , body3, title4 , body4, title5 , body5, image1, image2 , image3 , image4 , image5 , country, dest_url) Values (" & title1 & "', '" & body1 & "', '" & title2 & "', '" & body2 & "', '" & title3 & "', '" & body3 & "', '" & title4 & "', '" & body4 & "', '" & title5 & "', '" & body5 & "', '" & image1 & "', '" & image2 & "', '" & image3 & "', '" & image4 & "', '" & image5 & "', '" & country & "', '" & desturl & "')"

    TheDatabase.Execute(SQL)

    TheDatabase.Close
    set TheDatabase = nothing
    end if
    .
    .
    http://www.HawaiianHope.org
    Providing Technology services to non profit orgs, Homeless Shelters, Food Pantries, Clean And Sober Houses and more.
    To date we have given away over 900 free computers !
    __________________________
    caeli enarrant gloriam Dei !

  3. #3
    Join Date
    Nov 2005
    Location
    Honolulu HI
    Posts
    119
    my next comment would be,

    i would be careful about passing around paramaters on the URL like that...
    especially one that triggers an update to a database.

    set up a hidden text filed in your form someplace instead and update its contents with your flags (Add, Update, Delete, set Inactive, etc....)
    then same thing, before you do the IF, test teh field for its contents.

    strCommand = Request.Form("hiddenCommand")
    if strCommand = "AddMeSomeData" then
    ...... ..... ...
    ...... ..... ...
    ...... ..... ...
    ...... ..... ...

    end if
    .
    .
    http://www.HawaiianHope.org
    Providing Technology services to non profit orgs, Homeless Shelters, Food Pantries, Clean And Sober Houses and more.
    To date we have given away over 900 free computers !
    __________________________
    caeli enarrant gloriam Dei !

  4. #4
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    thank you that is really helpful. the "Select *" was a legacy from an attempt to pre-populate the form.
    I am going to finalise my test this morning, and have a go with your code, but one of the problems apeared to be the form sending "multipart/form-data" - when I removed that, I started seeing results.
    I'll get back with a full report later.

  5. #5
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    Hey hey! that all works great - fast and clean. Thanks again.
    The enctype="multipart/form-data" was definately stoping it working. and my code was definately a mess!

    Just out of curiosity, could you give me a v.quick answer to these questions?

    is an SQL INSERT quicker/better than AddNew?
    is DNS connection quicker/better than DNSless?
    -(As I researched my problem yesterday I got conflicting reports)
    Cheers
    M

  6. #6
    Join Date
    Nov 2005
    Location
    Honolulu HI
    Posts
    119
    Are you learning SQL ?
    or... rather... i am assuming you know at least a liltle.. are you trying to learn more ?

    you know about the SQL UPDATE and DELETE ?
    i can give you a few references for good SQL tutorials and other stuff if you need it.
    also.. look into stored procedures. they ROCK!
    basically.. you store the entire SQL statment(s) in the MS-SQL database... pass it some paramaters and then it runs it.
    much faster.

    the multipart/form... that is typically used if you are uploading a file.

    the INSERT is faster. in general.. working with recordsets is somewat slow. so if you dont need to retrieve a pile of data first, there is no reason too..

    that SELECT * that you had in that first post.. since it does not have a WHERE clause on it, you are loading the entire contents of a table into the recordset.
    I.E. you have 17 fields... and if you have 1,000 records... you are loading all 17 fields for ALL 1,000 records... 17,000 pieces of information.

    if you want to pre-populate the form, tell it what record you want to select with a WHERE clause.
    ----------------------------------------------
    SELECT title1, body1, title2, body2 from generic_country_info WHERE recordID = 12
    Set rsPages = TheDatabase.Execute(SQL)

    if not rsPages.eof then
    ' says yes, there is data. "if the recordset is not at the end of file"
    strTitle1 = rsPages("title1")
    strBody1 = rsPages("body1")
    strTitle2 = rsPages("title2")
    strBody2 = rsPages("body2")

    else
    ' else, there is no data. the ID 12 may not exist
    strErrorMessage = "Page ID not found"
    end if

    ----------------------------------------------
    also, from what i think i know (hehehe) the "add new" then "update" is actually a 2 step process. first you are updating the recordest in local memory....(depending on how you have it defined) then you are telling it to "update" and that tries to update the database.

    the reason i say "think i know" is because the last time i did it that way was about 10 years ago when i was usnig /learning VB4 and an access database.

    ultimately a stored procedure will blow the doors off of an "AddNew" or even a regular INSERT. of course, when your only dealing with a single user at a time, your not really going to notice it. but when you get to where you have hundreds of users at a time banging away at a database... yea... noticeable.

    the DSN /DSN Less is sort of up in the air. i have found arguements on both side... again, it may be only really noticeable when you have hundreds or more users on the system.
    One advantage is that using a DSN connection, you can more easily change databases around if you need to. point it to another database, things like that.

    i had one website on a server... the DSNLess connection does not work properly... it takes 30 seconds to make a connection. aparently they have the driver name incorrect in the connection string or something....

    changed it to a DSN connection.. and it opend the same page in less than 1/2 a second.


    also, if you need... i can give you other hints on making your code a little more effecient.. let me know if you need it.


    glad it all worked !
    .
    .
    http://www.HawaiianHope.org
    Providing Technology services to non profit orgs, Homeless Shelters, Food Pantries, Clean And Sober Houses and more.
    To date we have given away over 900 free computers !
    __________________________
    caeli enarrant gloriam Dei !

  7. #7
    Join Date
    Oct 2005
    Location
    SW England
    Posts
    102
    Wow! thanks, sorry I didn't get back, once it started working I got my head down and worked to make up for lost time.
    The multipart form data is back in now and I am sending the text as part of the ASP upload object. - 'upload.form("text")'
    Will def look into stored procedures.
    Cheers
    M

Posting Permissions

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