• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

updating a database through ASP, VBscript

hiya,
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.
************************************************
<%@LANGUAGE="VBSCRIPT"%>
<!--#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)))
  Next

  ' 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
    Else
      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
    Else
      If (MM_altVal <> "") Then
        MM_formVal = MM_altVal
      ElseIf (MM_delim = "'") Then  ' escape quotes
        MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
      Else
        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
  Next
  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
    MM_editCmd.Execute
    MM_editCmd.ActiveConnection.Close

    If (MM_editRedirectUrl <> "") Then
      Response.Redirect(MM_editRedirectUrl)
    End If
  End If

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

<head>
<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];}
}
//-->
</script>
<link href="Assets/master.css" rel="stylesheet" type="text/css">

</head>

<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">
  <tr>
    <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>
  </tr>
  <tr>
    <td> <table width="100%" border="0" cellspacing="1" cellpadding="2">
        <tr>
          <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>
        </tr>
      </table></td>
  </tr>
</table>
<br>
<p class="sectionHead">New Rental Location</p>
<FORM ACTION="<%=MM_editAction%>" METHOD="POST" NAME="db_add" ID="db_add">
 
  <TABLE CELLSPACING="2" CELLPADDING="5" BORDER="0">
    <TR>
      <TD width="200">dvd title</TD>
      <TD width="180" VALIGN="top" > <input type="TEXT" name="dvd_name" value="" size="30"/></TD>
    </TR>
    <TR>
      <TD>price</TD>
      <TD  VALIGN="top"><INPUT TYPE="TEXT" NAME="price" value="" size="30"/></TD>
    </TR>
    <TR>
      <TD>picture</TD>
      <TD  VALIGN="top"><INPUT TYPE="TEXT" NAME="picture" value="" size="30"/></TD>
    </TR>
    <TR>
      <TD>genre</TD>
      <TD  VALIGN="top"> <INPUT TYPE="TEXT" NAME="genre" size="30">
      </TD>
    </TR>
    <TR>
      <TD>stock left</TD>
      <TD  VALIGN="top"> <INPUT TYPE="TEXT" name="stock_left" " size="30"></TD>
    </TR>
    <TR>
      <TD ALIGN="CENTER" COLSPAN="2"> <input type="submit" name="Submit" value="Submit">
        <input name="Reset" type="reset" id="Reset" value="Reset"> </TD>
    </TR>
  </TABLE>
  <input type="hidden" name="InsertRecord" value="db_add">
  <input type="hidden" name="MM_insert" value="db_add">
</FORM>
</body>
</html>
*******************************************************
this is where i've got so far but cant seem to figure out what i'm doing wrong.
0
mceroche
Asked:
mceroche
1 Solution
 
klewlisCommented:
what's the error message you're getting?
0
 
tncbbthositgCommented:
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"
etc.,

then myrecordset.update


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

Let me know if this is any help,
Pat
0
 
gladxmlCommented:
mceroche,

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

Basically your code can be modified

http://www.w3schools.com/sql/default.asp

http://www.w3schools.com/ado/default.asp

HTH...

Happy programming...
 
0
 
mcerocheAuthor Commented:
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.AddNew

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")

rsAddComments.Update


rsAddComments.Close
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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now