Update not working. Why?

• huilinglee

  SSC Enthusiast

  Points: 146

  More actions

  April 20, 2004 at 3:18 pm

  #84809

  Hello,

  I am having a slight problem. I have added a function in a web community which allows users to edit their information and then the data will be updated. But there is always an error. Can someone help me?

  set conn=Server.CreateObject("ADODB.Connection")

  set rs=Server.CreateObject("ADODB.recordset")

  conn.open="dsn=FLAMINBUTT; db=cybercounsel; UID=cyber; PWD= counsel" 

   

   

  sql= "SELECT * FROM student where username="&username&""

   rs.Open sql,conn,2,2

   

   'update edited stuff

   'rs.MoveFirst

   rs.fields("stud_fname")=fullname

   rs.fields("stud_id")=stud_id

   rs.fields("add_current")=curr_add

   rs.fields("icq")=icq

   rs.fields("msn")=msn

   rs.fields("yahoo")=yahoo

   rs.fields("intake")=intake

   rs.fields("email")=email

   rs.fields("major")=major

   rs.fields("description")=descript

   rs.fields("date_modified")=Date()

   rs.update

   

  all extraction of data from the form has been done earlier in the scripts. The error message that it returns is

  Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)

  ODBC driver does not support the requested properties.

  /PROJ 2/profile_edit.asp, line 29

  I am using MySql Server to run and store my databases. WOuld greatly appreciate all help!

  
  eat when you can and not when you cannot.

• BillWey

  SSC Rookie

  Points: 49

  More actions

  April 21, 2004 at 9:53 pm

  #503747

  I haven't used ADO in a while (been using strictly T-SQL and VBscript lately), but if my memory serves me correctly, your "open" is using the wrong configuration (cursorlocation, cursortype, locktype).  

   

  See KB 188857 for the details.

   

   

• Pragnesh

  Valued Member

  Points: 62

  More actions

  April 21, 2004 at 10:41 pm

  #503750

  I think the problem is with opening of connection.

  the "=" sign after open is not there, since open is a method and not a property.

  conn.open = "dsn=FLAMINBUTT; db=cybercounsel; UID=cyber; PWD= counsel" 

  But exactly at what line the error is thrown, i mean what code  is at /PROJ 2/profile_edit.asp, line 29.

  Pragnesh

• SQLGutter

  SSC Veteran

  Points: 263

  More actions

  April 22, 2004 at 6:48 am

  #503809

  First

  Make sure you can open and close the connection object without errors.

  If that works clean, then try the following approach:

  set conn=Server.CreateObject("ADODB.Connection")

  conn.open="dsn=FLAMINBUTT; db=cybercounsel; UID=cyber; PWD= counsel"

  sql="Update Student "

  sql=sql & "Set stud_fname = '" & fullname & "' & ", "

  sql=sql & "stud_id = '" & stud_id & "' & ","

  sql=sql & "add_current = '" & curr_add & "' & ", "

  sql=sql & "icq = '" & icq & "' & ", "

  sql=sql & "msn = '" & msn & "' & ", "

  sql=sql & "yahoo = '" & yahoo & "' & ", "

  sql=sql & "intake = '" & intake & "' & ", "

  sql=sql & "email = '" & email & "' & ", "

  sql=sql & "major = '" & major & "' & ", "

  sql=sql & "description = '" & descript & "' & ", "

  sql=sql & "date_modified = getdate() "

  sql=sql & "Where username = '" & username & "'"

  conn.Execute sqlstr

  conn.Close

  Set conn = Nothing

  NOTE: I didn't check the sql string code exactly, so make sure it is a valid sql statement before executing the code.

  Make sure the user that you identify in the connection string has the appropriate rights to update the table.

  I use the recordset object primarily for data presentation in all the applications I build. I use the Execute method of the connection object for almost all update and delete actions against the database.

  This approach always seems to be a bit cleaner to me. I like to have SQL server do most of the data manipulation work. That is what SQL is good at.

  Another thing you may want to do is encapsulate this code in a parameterized stored procedure. Then you can run the stored procedure from any application that needs to do such an update without re-inventing or re-writing the sql code.

  I hope this helps,

  Al

  
  "I will not be taken alive!" - S. Hussein

• SQLGutter

  SSC Veteran

  Points: 263

  More actions

  April 22, 2004 at 11:39 am

  #503887

  Oops... found an error in my code above...

  This is more correct

  sql="Update Student "

  sql=sql & "Set stud_fname = '" & fullname & "', "

  sql=sql & "stud_id = '" & stud_id & "', "

  sql=sql & "add_current = '" & curr_add & "', "

  sql=sql & "icq = '" & icq & "', "

  sql=sql & "msn = '" & msn & "', "

  sql=sql & "yahoo = '" & yahoo & "', "

  sql=sql & "intake = '" & intake & "', "

  sql=sql & "email = '" & email & "', "

  sql=sql & "major = '" & major & "', "

  sql=sql & "description = '" & descript & "', "

  sql=sql & "date_modified = getdate() "

  sql=sql & "Where username = '" & username & "'"

  Thats what I get when I try programming before my caffeine kicks in.

  - Al 🙂

  
  "I will not be taken alive!" - S. Hussein

• huilinglee

  SSC Enthusiast

  Points: 146

  More actions

  April 22, 2004 at 9:44 pm

  #503935

  Hey thanks so much everyone! I tried using the update function in the SQL statement rather than the one i used and it works just fine!

  haha...

  and SQL gutter...i know what you mean about caffeine. Haha....couldn't live without it now can we?

  
  eat when you can and not when you cannot.