Addnew method using MSSQL Server

Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Addnew method using MSSQL Server

  1. #1

    Default Addnew method using MSSQL Server

    Hi guys,<BR>Ive just converted my db across from access to MSSQL and have noticed that there is an issue with using the addnew method against SQL.<BR><BR>case "addprofile"<BR> <BR>Set objRS = Server.CreateObject("ADODB.Recordset")<BR>objRS.Op en "profiles", myconn, adLockOptimistic, adCmdTable<BR> <BR>objRS.AddNew<BR>objRS("sex") = session("sex")<BR>objRS("birthday") = session("day")<BR>objRS("month_born") = session("month_born")<BR>objRS("year_born") = session("year_born")<BR>objRS("starsign") = session("starsign")<BR>objRS("born") = session("born")<BR>objRS("grewup") = session("grewup")<BR>objRS("city") = session("city")<BR>objRS("knownfor") = session("knownfor")<BR>objRS("interests") = session("interests")<BR>objRS("biography") = session("biography")<BR>objRS("userID") = session("ID")<BR>objRS.Update<BR><BR>Why is this?<BR><BR>John

  2. #2

    Default actually...

    I have a feeling its erroring out here.<BR><BR>user_id = objRS("ID")<BR><BR>im trying to obtain the last auto ID number. This works in access, does it not in sql?<BR><BR>John

  3. #3

    Default ok Ive checked ...

    ...the insertion results to the DB and its definitely this.<BR><BR>How can I obtain the last record inserted into the DB in MSSQL server?<BR><BR>John

  4. #4
    Join Date
    Dec 1969

    Default Your OPEN is bogus

    objRS.Open "profiles", myconn, adLockOptimistic, adCmdTable<BR><BR>????<BR><BR>Where is the cursor type? Presumably, adOpenKeyset.<BR><BR>objRS.Open "profiles", myconn, adOpenKeyset, adLockOptimistic, adCmdTable<BR><BR>Also, opening the entire table is not a great idea. You have no idea how much data might get transferred from the server to ADO when you do that.<BR><BR>Either limit the number of records by changing the right property on the RS or use the sneaky trick<BR> SQL = "SELECT * FROM profiles WHERE 1=0"<BR><BR>Which gets an empty recordset, but with the right field info.<BR><BR>And then OMIT the adCmdTable argument. You really never did need it. ADO can figure that out for itself.<BR><BR>If all that still doesn&#039;t fix it, then you can always use the conventional <BR> SELECT @@IDENTITY <BR>from the same connection.<BR><BR><BR>

  5. #5

    Default fixed!

    you&#039;re right Bill....just as I clicked on reply I saw your response.<BR><BR>Ive added adOpenKeySet and the problem has been resolved.<BR><BR>John

  6. #6

    Default Bill...

    Bill, I&#039;d like to learn more about this....<BR><BR>Also, opening the entire table is not a great idea. You have no idea how much data might get transferred from the server to ADO when you do that. <BR><BR>Either limit the number of records by changing the right property on the RS or use the sneaky trick <BR> SQL = "SELECT * FROM profiles WHERE 1=0"<BR><BR>John <BR>

  7. #7
    Join Date
    Dec 1969

    Default Okay, for starters...

    The sneaky trick is obvious: It is *NEVER* true that 1=0, so the WHERE clause obviously will never get any records. It will just get an empty recordset. So no data is transferred from server to ADO, only the structure of the table.<BR><BR>For the other...<BR><BR>RTFM, as usual.<BR><BR><BR><BR>Click on "Max records property".<BR><BR>Click on "Cache size property". [Though I think MaxRecords will do the job, alone.]<BR><BR>So if you set<BR> RS.MaxRecords = 1<BR>*before* you open the table, then only 1 record will be transferred from server to ADO. <BR><BR>Make sense?<BR><BR>But I *suspect* that the WHERE 1=0 trick *might* even be better. Dunno. Never benchmarked it.<BR><BR>

  8. #8

    Default holy moly!

    Bill heres the article I checked out.<BR><BR><BR><BR>And heres how I set my code.<BR><BR>Set objRS = Server.CreateObject("ADODB.Recordset")<BR>objRS.Ma xRecords = 1 <BR>objRS.Open "members", myconn, adOpenKeySet, adLockOptimistic, adCmdTable<BR> <BR>objRS.AddNew<BR>objRS("firstname") = session("firstname")<BR>objRS("lastname") = session("lastname")<BR>objRS("username") = session("username")<BR><BR>......<BR><BR>It was interesting to see that by setting MaxRecords to 0 actually returned *all* records. Weird huh?<BR><BR>But boy, what a difference in processing time. It hauls arse! Ive just upgraded to SQL SERVER from access as well as making that change, and the join member thing now is lightning quick.<BR><BR>HOORAY!!!<BR><BR>John<BR><BR>PS. I owe you another coffee ;-)<BR>

  9. #9
    Join Date
    Dec 1969

    Default Even faster??

    The AddNew method is still considerably slower than an insert statement. If you dont believe me, put this into a loop and loop 1000 times and compare performance:<BR><BR>SQL = "Insert Into Members (firstname, lastname, username) Values (&#039;" & session("firstname") & "&#039;, &#039;" & session("lastname") & "&#039;, &#039;" & session("username") & "&#039;"<BR>myconn.Execute SQL<BR><BR>

  10. #10
    Join Date
    Dec 1969

    Default Oh, yes...2 or 3 times faster...

    No FETCH of *ANY* kind is needed, so at a minimum you have removed *TWO* data transfers from the mix (sending the request and receiving the one record). Plus there&#039;s no issue with locking (no need for even LockOptimistic). <BR><BR>But some people have difficulties getting the syntax of INSERT correct, I guess.<BR><BR>

Posting Permissions

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