By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,296 Members | 1,476 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,296 IT Pros & Developers. It's quick & easy.

update & insert operation

P: 21
asp, vbscript, Ms Access

I am using vbscript to insert & update ms accees.

Insert operation is working , but update operation is not working.

I have used the following codes:


Expand|Select|Wrap|Line Numbers
  1. connectionString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\progam files\mail.mdb"


Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. If (CStr(Request("MM_insert")) = "regorg") Then
  4.  
  5.   ConnectionDb = connectionString
  6.  
  7.   MM_editTable = "Reg"
  8.  
  9.  MM_fieldsStr  = "username|value|email|value| "
  10.  
  11.  MM_columnsStr = "username|',none,''|email|',none,''"
  12.  
  13.  
  14.   ' create the MM_fields and MM_columns arrays
  15.  
  16.   MM_fields = Split(MM_fieldsStr, "|")
  17.  
  18.   MM_columns = Split(MM_columnsStr, "|")
  19.  
  20.  
  21.   ' set the form values
  22.  
  23.   For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
  24.  
  25.     MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))
  26.  
  27.   Next
  28.  
  29.   ' append the query string to the redirect URL
  30.  
  31.   If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
  32.  
  33.     If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
  34.  
  35.       MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
  36.  
  37.     Else
  38.  
  39.       MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
  40.  
  41.     End If
  42.  
  43.   End If
  44.  
  45. End If
  46.  
  47.  

insert & update record
Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. Dim MM_tableValues
  4.  
  5. Dim MM_dbValues, sql , id
  6.  
  7. id = Recordset1.Fields.Item("userid").Value
  8.  
  9. sql = ""
  10.  
  11.  
  12. sql="UPDATE customers SET "
  13.  
  14. If (CStr(Request("MM_insert")) <> "") Then
  15.  
  16.   MM_tableValues = ""
  17.  
  18.   MM_dbValues = ""
  19.  
  20.   For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
  21.  
  22.     MM_formVal = MM_fields(MM_i+1)
  23.  
  24.     MM_typeArray = Split(MM_columns(MM_i+1),",")
  25.  
  26.     MM_delim = MM_typeArray(0)
  27.  
  28.     If (MM_delim = "none") Then MM_delim = ""
  29.  
  30.     MM_altVal = MM_typeArray(1)
  31.  
  32.     If (MM_altVal = "none") Then MM_altVal = ""
  33.  
  34.     MM_emptyVal = MM_typeArray(2)
  35.  
  36.     If (MM_emptyVal = "none") Then MM_emptyVal = ""
  37.  
  38.       If (MM_formVal = "") Then
  39.  
  40.         MM_formVal = MM_emptyVal
  41.  
  42.       Else
  43.  
  44.         If (MM_altVal <> "") Then
  45.  
  46.           MM_formVal = MM_altVal
  47.  
  48.         ElseIf (MM_delim = "'") Then  ' escape quotes
  49.  
  50.           MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
  51.  
  52.         Else
  53.  
  54.           MM_formVal = MM_delim + MM_formVal + MM_delim
  55.  
  56.         End If
  57.  
  58.       End If
  59.  
  60.       If (MM_i <> LBound(MM_fields)) Then
  61.  
  62.         MM_tableValues = MM_tableValues & ","
  63.  
  64.         MM_dbValues = MM_dbValues & ","
  65.  
  66.       End If
  67.  
  68.       MM_tableValues = MM_tableValues & MM_columns(MM_i)
  69.  
  70.       MM_dbValues = MM_dbValues & MM_formVal
  71.  
  72.  
  73.       sql=sql & "MM_tableValues='" & MM_dbValues & "',"
  74.  
  75.   Next
  76.  
  77.    sql=sql & " WHERE userid ='" & id & "'"    
  78.  
  79.  
If (Not MM_abortEdit) Then


' insert operation is not creating a problem

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3.   ' execute the insert
  4.  
  5.     Set MM_editCmd = Server.CreateObject("ADODB.Command")
  6.  
  7.    MM_editCmd.ActiveConnection = ConnectionDb    
  8.  
  9.    MM_editCmd.CommandText = MM_editQuery
  10.  
  11.     MM_editCmd.Execute 
  12.  
  13.     MM_editCmd.ActiveConnection.Close
  14.  
  15.  
update operation is not working

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3.  execute the update
  4.  
  5. Set MM_editCmd = Server.CreateObject("ADODB.Command")
  6.  
  7.    MM_editCmd.ActiveConnection = MM_editConnection
  8.  
  9.    MM_editCmd.CommandText = sql
  10.  
  11.     MM_editCmd.Execute 
  12.  
  13.     MM_editCmd.ActiveConnection.Close
  14.  
  15.  
Nov 13 '07 #1
Share this Question
Share on Google+
3 Replies


shweta123
Expert 100+
P: 692
Hi,

There is another way to insert or update the data in the database.
You can also use Execute method of the connection object instead of using command object.

e.g.
Expand|Select|Wrap|Line Numbers
  1.   <%
  2.      Dim sqlinsert
  3.      Dim sqlupdate
  4.  
  5.     '''This is connection object
  6.      Dim con
  7.  
  8.      ''''''Sql statement for insering data  
  9.      sqlinsert = "Insert into tablename ................"
  10.      con.execute(sqlinsert)  
  11.  
  12.        ''''''Sql statement for updating data
  13.      sqlupdate = "update tablename set .........."
  14.      con.execute(sqlupdate)
  15.   %>
  16.  
Nov 15 '07 #2

shweta123
Expert 100+
P: 692
Hi,

There is another way to insert or update the data in the database.
You can also use Execute method of the connection object instead of using command object.

e.g.

Expand|Select|Wrap|Line Numbers
  1. <%
  2.      Dim sqlinsert
  3.      Dim sqlupdate
  4.  
  5.     '''This is connection object
  6.      Dim con
  7.  
  8.      ''''''Sql statement for insering data  
  9.      sqlinsert = "Insert into tablename ................"
  10.      con.execute(sqlinsert)  
  11.  
  12.        ''''''Sql statement for updating data
  13.      sqlupdate = "update tablename set .........."
  14.      con.execute(sqlupdate)
  15.   %>
Nov 15 '07 #3

jhardman
Expert 2.5K+
P: 3,405
The easiest way to update a record in ASP is with the recordset object:
Expand|Select|Wrap|Line Numbers
  1. dim objConn, objRS, query
  2. set objConn = server.createObject("ADODB.connection")
  3. objConn.open 'put your connection string here
  4. set objRS = server.createObject("ADODB.RecordSet")
  5. query = "SELECT * FROM customers WHERE idNum = 345"
  6. objRS.open query, objConn
  7.  
  8. objRS("customerName") = request("custName")
  9. objRS.update
Does this make sense?

Jared
Nov 18 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.