updating a database through ASP, VBscript

Posted on 2003-03-03
Medium Priority
Last Modified: 2012-06-27
I'm looking for help in trying to update my database with information.
I have one table with five columns/fields.

dvd name; price; picture; genre; stock left;

i have been trying to make this work but am having great difficulty.
i'd be appreciative with any help.
thank you.
<!--#include file="Connections/connFYP.asp" -->
' *** Edit Operations: declare variables

Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Request.QueryString
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
' *** Insert Record: set variables

If (CStr(Request("MM_insert")) = "db_add") Then

  MM_editConnection = MM_connFYP_STRING
  MM_editTable = "Products"
  MM_editRedirectUrl = "LocationOK.asp"
  MM_fieldsStr  = "dvd_name|value|price|value|picture|value|genre|value|stock_left|value"
  MM_columnsStr = "DVD_Name|',none,''|Price|',none,''|Picture|',none,''|Genre|',none,''|Stock_Left|',none,''"

  ' create the MM_fields and MM_columns arrays
  MM_fields = Split(MM_fieldsStr, "|")
  MM_columns = Split(MM_columnsStr, "|")
  ' set the form values
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))

  ' append the query string to the redirect URL
  If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
    If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
      MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
      MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
    End If
  End If

End If
' *** Insert Record: construct a sql insert statement and execute it

Dim MM_tableValues
Dim MM_dbValues

If (CStr(Request("MM_insert")) <> "") Then

  ' create the sql insert statement
  MM_tableValues = ""
  MM_dbValues = ""
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_formVal = MM_fields(MM_i+1)
    MM_typeArray = Split(MM_columns(MM_i+1),",")
    MM_delim = MM_typeArray(0)
    If (MM_delim = "none") Then MM_delim = ""
    MM_altVal = MM_typeArray(1)
    If (MM_altVal = "none") Then MM_altVal = ""
    MM_emptyVal = MM_typeArray(2)
    If (MM_emptyVal = "none") Then MM_emptyVal = ""
    If (MM_formVal = "") Then
      MM_formVal = MM_emptyVal
      If (MM_altVal <> "") Then
        MM_formVal = MM_altVal
      ElseIf (MM_delim = "'") Then  ' escape quotes
        MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
        MM_formVal = MM_delim + MM_formVal + MM_delim
      End If
    End If
    If (MM_i <> LBound(MM_fields)) Then
      MM_tableValues = MM_tableValues & ","
      MM_dbValues = MM_dbValues & ","
    End If
    MM_tableValues = MM_tableValues & MM_columns(MM_i)
    MM_dbValues = MM_dbValues & MM_formVal
  MM_editQuery = "insert into " & MM_editTable & " (" & MM_tableValues & ") values (" & MM_dbValues & ")"

  If (Not MM_abortEdit) Then
    ' execute the insert
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery

    If (MM_editRedirectUrl <> "") Then
    End If
  End If

End If
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<title>Global Car Rental</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript">
function MM_swapImgRestore() { //v3.0
  var i,x,a=document.MM_sr; for(i=0;a&&i<a.length&&(x=a[i])&&x.oSrc;i++) x.src=x.oSrc;

function MM_preloadImages() { //v3.0
  var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
    var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
    if (a[i].indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a[i];}}

function MM_findObj(n, d) { //v4.01
  var p,i,x;  if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
    d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
  if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
  for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document);
  if(!x && d.getElementById) x=d.getElementById(n); return x;

function MM_swapImage() { //v3.0
  var i,j=0,x,a=MM_swapImage.arguments; document.MM_sr=new Array; for(i=0;i<(a.length-2);i+=3)
   if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];}
<link href="Assets/master.css" rel="stylesheet" type="text/css">


<body bgcolor="#FFFFFF" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0" onLoad="MM_preloadImages('Assets/images/btnHome_on.jpg','Assets/images/btnCustomerService_on.jpg','Assets/images/btnLocations_on.jpg')">
<table width="100%" border="0" cellspacing="0" cellpadding="4">
    <td rowspan="2" bgcolor="#424973"><img src="Assets/images/logo.jpg" alt="Logo" width="235" height="48"></td>
    <td width="100%" bgcolor="#424973"><div align="right"><font color="#FFFFFF"><b>The
        International Car Rental Specialists</b></font></div></td>
    <td> <table width="100%" border="0" cellspacing="1" cellpadding="2">
          <td align="center" width="20%" bgcolor="#424973"><a href="#" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('menu1','','Assets/images/btnHome_on.jpg',1)"><img src="Assets/images/btnHome.jpg" alt="Home Button" name="menu1" width="90" height="25" border="0"></a></td>
          <td align="center" width="20%" bgcolor="#424973"><a href="customerService.asp" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('menu2','','Assets/images/btnCustomerService_on.jpg',1)"><img src="Assets/images/btnCustomerService.jpg" alt="Customer Service Button" name="menu2" width="110" height="25" border="0"></a></td>
          <td align="center" width="20%" bgcolor="#424973"><a href="#" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('menu3','','Assets/images/btnLocations_on.jpg',1)"><img src="Assets/images/btnLocations.jpg" alt="Locations Button" name="menu3" width="90" height="25" border="0"></a></td>
<p class="sectionHead">New Rental Location</p>
<FORM ACTION="<%=MM_editAction%>" METHOD="POST" NAME="db_add" ID="db_add">
      <TD width="200">dvd title</TD>
      <TD width="180" VALIGN="top" > <input type="TEXT" name="dvd_name" value="" size="30"/></TD>
      <TD  VALIGN="top"><INPUT TYPE="TEXT" NAME="price" value="" size="30"/></TD>
      <TD  VALIGN="top"><INPUT TYPE="TEXT" NAME="picture" value="" size="30"/></TD>
      <TD  VALIGN="top"> <INPUT TYPE="TEXT" NAME="genre" size="30">
      <TD>stock left</TD>
      <TD  VALIGN="top"> <INPUT TYPE="TEXT" name="stock_left" " size="30"></TD>
      <TD ALIGN="CENTER" COLSPAN="2"> <input type="submit" name="Submit" value="Submit">
        <input name="Reset" type="reset" id="Reset" value="Reset"> </TD>
  <input type="hidden" name="InsertRecord" value="db_add">
  <input type="hidden" name="MM_insert" value="db_add">
this is where i've got so far but cant seem to figure out what i'm doing wrong.
Question by:mceroche
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8060883
what's the error message you're getting?

Accepted Solution

tncbbthositg earned 150 total points
ID: 8062023
I would start by programming your own code.  It really is much more manageable!  If you arent getting an error message that says that you cannot access the database or update the database for one reason or another, you know it's your server settings.  Otherwise, it's code.  I also, usually, try not to execute SQL.  I find that executed SQL is easier to inject.  Try creating a recordset object and using:
myrecordset("DVDPrice") = "$29.95"
myrecordset("Picture") = "Braveheart"

then myrecordset.update

if you are adding a new record, put myrecordset.addnew before it.

Let me know if this is any help,
LVL 15

Expert Comment

ID: 8062438

Below is a lini that you can refer to regarding updating and inserting using ado and asp...

Basically your code can be modified




Happy programming...

Author Comment

ID: 8063987
yeah it put me on the right track ok. thanks.
Dim adoCon                
Dim rsAddComments          
Dim strSQL              

Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("dvd.mdb")

Set rsAddComments = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT Products.DVD_Name, Products.Price, Products.Picture, Products.Genre, Products.Stock_Left FROM Products;"

rsAddComments.CursorType = 2

rsAddComments.LockType = 3

rsAddComments.Open strSQL, adoCon


rsAddComments.Fields("DVD_Name") = Request.Form("name")
rsAddComments.Fields("Price") = Request.Form("price")
rsAddComments.Fields("Picture") = Request.Form("picture")
rsAddComments.Fields("Genre") = Request.Form("genre")
rsAddComments.Fields("Stock_Left") = Request.Form("stock_left")


Set rsAddComments = Nothing
Set adoCon = Nothing

i have a html page that has a form and upon ACTION is directed to this page.
thanks, steve.

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
The viewer will learn how to dynamically set the form action using jQuery.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question