Jump to content

insert into database problems

shaneR

By shaneR
in VBScript

 Share

Recommended Posts

shaneR Newbie

shaneR

Posted
Posted

Hi, i wonder if anybody would be as kind to take a look at the attached code. Im trying to set up a form which when submitted the required information is sent to my database.The error which is displaying is as follows:Microsoft JET Database Engine error '80040e14'Syntax error in INSERT INTO statement./Shane_R/my_music_profile/web_site_construction/www/upload.asp, line 115 Thankyou.....

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%><!--#include file="../Connections/con1.asp" --><%' *** Edit Operations: declare variablesDim MM_editActionDim MM_abortEditDim MM_editQueryDim MM_editCmdDim MM_editConnectionDim MM_editTableDim MM_editRedirectUrlDim MM_editColumnDim MM_recordIdDim MM_fieldsStrDim MM_columnsStrDim MM_fieldsDim MM_columnsDim MM_typeArrayDim MM_formValDim MM_delimDim MM_altValDim MM_emptyValDim MM_iMM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))If (Request.QueryString <> "") Then  MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)End If' boolean to abort record editMM_abortEdit = false' query string to executeMM_editQuery = ""%><%' *** Insert Record: set variablesIf (CStr(Request("MM_insert")) = "form1") Then  MM_editConnection = MM_con1_STRING  MM_editTable = "register"  MM_editRedirectUrl = "upload.asp"  MM_fieldsStr  = "Email|value|Prefferedmusictype|value|Password|value|Profilename|value"  MM_columnsStr = "Email|',none,''|Prefferedmusictype|',none,''|Password|',none,''|Profilename|',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 IfEnd If%><%' *** Insert Record: construct a sql insert statement and execute itDim MM_tableValuesDim MM_dbValuesIf (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 IfEnd If%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><title>Untitled Document</title><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"></head><body><form method="post" action="<%=MM_editAction%>" name="form1">  <table align="center">    <tr valign="baseline">      <td nowrap align="right">Email:</td>      <td>        <input type="text" name="Email" value="" size="32">      </td>    </tr>    <tr valign="baseline">      <td nowrap align="right">Prefferedmusictype:</td>      <td>        <input type="text" name="Prefferedmusictype" value="" size="32">      </td>    </tr>    <tr valign="baseline">      <td nowrap align="right">Password:</td>      <td>        <input type="text" name="Password" value="" size="32">      </td>    </tr>    <tr valign="baseline">      <td nowrap align="right">Profilename:</td>      <td>        <input type="text" name="Profilename" value="" size="32">      </td>    </tr>    <tr valign="baseline">      <td nowrap align="right"> </td>      <td>        <input type="submit" value="Insert record">      </td>    </tr>  </table>  <input type="hidden" name="MM_insert" value="form1"></form><p> </p></body></html>

Link to comment
Share on other sites
jesh Devoted Member

jesh

Posted
Posted

It sounds like the problem is with the INSERT statement. When I have to dynamically build SQL statements in code (rather than use a Stored Procedure), I tend to echo (Response.Write) the statement to the screen before I attempt to run it on the database so that I can visually see what the statement renders out to. It's usually something simple like one too many commas or missing quote marks. Try that and see what the statement looks like.

Link to comment
Share on other sites
shaneR Newbie

shaneR

Posted
  • Author
Posted

Thanks for all that have taken the tome to look at this problem. I have discovered what was the cause and as usual it is something very simple.Basically one of my field names is called PASSWORD and as this is whats known as a reserved word i have changed it and now the site works.

Link to comment
Share on other sites
jesh Devoted Member

jesh

Posted
Posted

Ahh, nice. If you ever run into that problem again, depending on which database system you are using, you can force the database to use your column names by putting it in square brackets:

INSERT INTO mytable (Username, [Password]) VALUES ('test', 'test');

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...