Syntax error in Insert Statement

I am trying to insert infomation into my database. My other pages work with inserting infomation into the tables but this page does not and i have no idea why or what to do.

The code:
<%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>

<%
// *** Edit Operations: declare variables

// set the form action variable
var MM_editAction = Request.ServerVariables("SCRIPT_NAME");
if (Request.QueryString) {
MM_editAction += "?" + Request.QueryString;
}

// boolean to abort record edit
var MM_abortEdit = false;

// query string to execute
var MM_editQuery = "";
%>
<%
// *** Insert Record: set variables

if (String(Request("MM_insert")) == "form1") {

var MM_editConnection = MM_Service_STRING;
var MM_editTable = "Hospital";
var MM_editRedirectUrl = "Index.asp";
var MM_fieldsStr = "Hospital_Name|value|Ward|value|Location|value|CityTown|value|Geogr_Area|value|Contact_Number|value|Customer|value";
var MM_columnsStr = "Hospital_Name|',none,''|Ward|',none,''|Location|',none,''|City/Town|',none,''|Geogr_Area|',none,''|Contact_Number|',none,''|Cust_Acc_ID|none,none,NULL";

// create the MM_fields and MM_columns arrays
var MM_fields = MM_fieldsStr.split("|");
var MM_columns = MM_columnsStr.split("|");

// set the form values
for (var i=0; i+1 < MM_fields.length; i+=2) {
MM_fields[i+1] = String(Request.Form(MM_fields[i]));
}

// append the query string to the redirect URL
if (MM_editRedirectUrl && Request.QueryString && Request.QueryString.Count > 0) {
MM_editRedirectUrl += ((MM_editRedirectUrl.indexOf('?') == -1)?"?":"&") + Request.QueryString;
}
}
%>
<%
// *** Insert Record: construct a sql insert statement and execute it

if (String(Request("MM_insert")) != "undefined") {

// create the sql insert statement
var MM_tableValues = "", MM_dbValues = "";
for (var i=0; i+1 < MM_fields.length; i+=2) {
var formVal = MM_fields[i+1];
var MM_typesArray = MM_columns[i+1].split(",");
var delim = (MM_typesArray[0] != "none") ? MM_typesArray[0] : "";
var altVal = (MM_typesArray[1] != "none") ? MM_typesArray[1] : "";
var emptyVal = (MM_typesArray[2] != "none") ? MM_typesArray[2] : "";
if (formVal == "" || formVal == "undefined") {
formVal = emptyVal;
} else {
if (altVal != "") {
formVal = altVal;
} else if (delim == "'") { // escape quotes
formVal = "'" + formVal.replace(/'/g,"''") + "'";
} else {
formVal = delim + formVal + delim;
}
}
MM_tableValues += ((i != 0) ? "," : "") + MM_columns[i];
MM_dbValues += ((i != 0) ? "," : "") + formVal;
}
MM_editQuery = "insert into " + MM_editTable + " (" + MM_tableValues + ") values (" + MM_dbValues + ")";

if (!MM_abortEdit) {
// execute the insert
var 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) {
Response.Redirect(MM_editRedirectUrl);
}
}

}
%>
<%
var Recordset1__num = "99";
if (String(Request.Form("Cust")) != "undefined" &&
String(Request.Form("Cust")) != "") {
Recordset1__num = String(Request.Form("Cust"));
}
%>
<%
var Recordset1 = Server.CreateObject("ADODB.Recordset");
Recordset1.ActiveConnection = MM_Service_STRING;
Recordset1.Source = "SELECT Customer FROM Customer WHERE Cust_Acc_ID = "+ Recordset1__num.replace(/'/g, "''") + "";
Recordset1.CursorType = 0;
Recordset1.CursorLocation = 2;
Recordset1.LockType = 1;
Recordset1.Open();
var Recordset1_numRows = 0;
%>


Hospital









The error it Gives is in runtime, when I hit the Insert Button:
Syntax error in INSERT INTO statement.

If anyone can help or suggest anything in anyway, I would appriciate it.
It is just this holding me back from completing my web application.

Comments

  • : I am trying to insert infomation into my database. My other pages work with inserting infomation into the tables but this page does not and i have no idea why or what to do.
    :
    : The code:
    : <%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
    :
    : <%
    : // *** Edit Operations: declare variables
    :
    : // set the form action variable
    : var MM_editAction = Request.ServerVariables("SCRIPT_NAME");
    : if (Request.QueryString) {
    : MM_editAction += "?" + Request.QueryString;
    : }
    :
    : // boolean to abort record edit
    : var MM_abortEdit = false;
    :
    : // query string to execute
    : var MM_editQuery = "";
    : %>
    : <%
    : // *** Insert Record: set variables
    :
    : if (String(Request("MM_insert")) == "form1") {
    :
    : var MM_editConnection = MM_Service_STRING;
    : var MM_editTable = "Hospital";
    : var MM_editRedirectUrl = "Index.asp";
    : var MM_fieldsStr = "Hospital_Name|value|Ward|value|Location|value|CityTown|value|Geogr_Area|value|Contact_Number|value|Customer|value";
    : var MM_columnsStr = "Hospital_Name|',none,''|Ward|',none,''|Location|',none,''|City/Town|',none,''|Geogr_Area|',none,''|Contact_Number|',none,''|Cust_Acc_ID|none,none,NULL";
    :
    : // create the MM_fields and MM_columns arrays
    : var MM_fields = MM_fieldsStr.split("|");
    : var MM_columns = MM_columnsStr.split("|");
    :
    : // set the form values
    : for (var i=0; i+1 < MM_fields.length; i+=2) {
    : MM_fields[i+1] = String(Request.Form(MM_fields[i]));
    : }
    :
    : // append the query string to the redirect URL
    : if (MM_editRedirectUrl && Request.QueryString && Request.QueryString.Count > 0) {
    : MM_editRedirectUrl += ((MM_editRedirectUrl.indexOf('?') == -1)?"?":"&") + Request.QueryString;
    : }
    : }
    : %>
    : <%
    : // *** Insert Record: construct a sql insert statement and execute it
    :
    : if (String(Request("MM_insert")) != "undefined") {
    :
    : // create the sql insert statement
    : var MM_tableValues = "", MM_dbValues = "";
    : for (var i=0; i+1 < MM_fields.length; i+=2) {
    : var formVal = MM_fields[i+1];
    : var MM_typesArray = MM_columns[i+1].split(",");
    : var delim = (MM_typesArray[0] != "none") ? MM_typesArray[0] : "";
    : var altVal = (MM_typesArray[1] != "none") ? MM_typesArray[1] : "";
    : var emptyVal = (MM_typesArray[2] != "none") ? MM_typesArray[2] : "";
    : if (formVal == "" || formVal == "undefined") {
    : formVal = emptyVal;
    : } else {
    : if (altVal != "") {
    : formVal = altVal;
    : } else if (delim == "'") { // escape quotes
    : formVal = "'" + formVal.replace(/'/g,"''") + "'";
    : } else {
    : formVal = delim + formVal + delim;
    : }
    : }
    : MM_tableValues += ((i != 0) ? "," : "") + MM_columns[i];
    : MM_dbValues += ((i != 0) ? "," : "") + formVal;
    : }
    : MM_editQuery = "insert into " + MM_editTable + " (" + MM_tableValues + ") values (" + MM_dbValues + ")";
    :
    : if (!MM_abortEdit) {
    : // execute the insert
    : var MM_editCmd = Server.CreateObject('ADODB.Command');
    : MM_editCmd.ActiveConnection = MM_editConnection;
    : MM_editCmd.CommandText = MM_editQuery;
    [red]
    Response.Write "Debug:sql="&MM_editQuery
    Response.End
    [/red]

    : MM_editCmd.Execute();
    : MM_editCmd.ActiveConnection.Close();
    :
    : if (MM_editRedirectUrl) {
    : Response.Redirect(MM_editRedirectUrl);
    : }
    : }
    :
    : }
    : %>
    : <%
    : var Recordset1__num = "99";
    : if (String(Request.Form("Cust")) != "undefined" &&
    : String(Request.Form("Cust")) != "") {
    : Recordset1__num = String(Request.Form("Cust"));
    : }
    : %>
    : <%
    : var Recordset1 = Server.CreateObject("ADODB.Recordset");
    : Recordset1.ActiveConnection = MM_Service_STRING;
    : Recordset1.Source = "SELECT Customer FROM Customer WHERE Cust_Acc_ID = "+ Recordset1__num.replace(/'/g, "''") + "";
    : Recordset1.CursorType = 0;
    : Recordset1.CursorLocation = 2;
    : Recordset1.LockType = 1;
    : Recordset1.Open();
    : var Recordset1_numRows = 0;
    : %>
    :
    :
    : Hospital
    :
    :
    :
    :
    :
    :
    :
    :
    :
    : The error it Gives is in runtime, when I hit the Insert Button:
    : Syntax error in INSERT INTO statement.
    :
    : If anyone can help or suggest anything in anyway, I would appriciate it.
    : It is just this holding me back from completing my web application.
    :


    Add those lines which I have marked in red,and see what the sql is.Hopefully you will find what is wrong,if not,copy/paste the sql and let us have a look.


  • I added those lines and got this error:

    Error Type:
    Microsoft JScript compilation (0x800A03EC)
    Expected ';'
    /ian/Hospital.asp, line 79, column 15
    Response.Write "Debug:sql="&MM_editQuery;

    I'm not sure what is missing or needed so I tried these:

    :Response.Write = "Debug:sql="&MM_editQuery;
    resulting in
    Error Type:
    Microsoft JScript runtime (0x800A01B6)
    Object doesn't support this property or method
    /ian/Hospital.asp, line 79


    AND TRIED:
    Response.Write( "Debug:sql="&MM_editQuery)
    Response.End
    the result on the screen is '0', that's it.

    Excuse my ignorance, I am an extreme novice working in the dark.

    If this is any help here's an example of the data I try to submit:
    POST Data:
    Hospital_Name=Hospital&Ward=123&Location=Test&CityTown=Test&Geogr_Area=
    Eastern+Cape&Contact_Number=123456&Cust_Acc_ID=2&submit1=Save&MM_insert=form1

    I am using Dreamweaver MX to develop the pages, so the Insert SQL is kind of auto-generated. After selecting what I wanted from the menu's I hope the sql is correct and along the lines of INSERT into Hospital (..) values (..)

  • [b][red]This message was edited by Flakes at 2004-12-16 1:1:50[/red][/b][hr]
    : : I am trying to insert infomation into my database. My other pages work with inserting infomation into the tables but this page does not and i have no idea why or what to do.
    : :
    : : The code:
    : : <%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
    : :
    : : <%
    : : // *** Edit Operations: declare variables
    : :
    : : // set the form action variable
    : : var MM_editAction = Request.ServerVariables("SCRIPT_NAME");
    : : if (Request.QueryString) {
    : : MM_editAction += "?" + Request.QueryString;
    : : }
    : :
    : : // boolean to abort record edit
    : : var MM_abortEdit = false;
    : :
    : : // query string to execute
    : : var MM_editQuery = "";
    : : %>
    : : <%
    : : // *** Insert Record: set variables
    : :
    : : if (String(Request("MM_insert")) == "form1") {
    : :
    : : var MM_editConnection = MM_Service_STRING;
    : : var MM_editTable = "Hospital";
    : : var MM_editRedirectUrl = "Index.asp";
    : : var MM_fieldsStr = "Hospital_Name|value|Ward|value|Location|value|CityTown|value|Geogr_Area|value|Contact_Number|value|Customer|value";
    : : var MM_columnsStr = "Hospital_Name|',none,''|Ward|',none,''|Location|',none,''|City/Town|',none,''|Geogr_Area|',none,''|Contact_Number|',none,''|Cust_Acc_ID|none,none,NULL";
    : :
    : : // create the MM_fields and MM_columns arrays
    : : var MM_fields = MM_fieldsStr.split("|");
    : : var MM_columns = MM_columnsStr.split("|");
    : :
    : : // set the form values
    : : for (var i=0; i+1 < MM_fields.length; i+=2) {
    : : MM_fields[i+1] = String(Request.Form(MM_fields[i]));
    : : }
    : :
    : : // append the query string to the redirect URL
    : : if (MM_editRedirectUrl && Request.QueryString && Request.QueryString.Count > 0) {
    : : MM_editRedirectUrl += ((MM_editRedirectUrl.indexOf('?') == -1)?"?":"&") + Request.QueryString;
    : : }
    : : }
    : : %>
    : : <%
    : : // *** Insert Record: construct a sql insert statement and execute it
    : :
    : : if (String(Request("MM_insert")) != "undefined") {
    : :
    : : // create the sql insert statement
    : : var MM_tableValues = "", MM_dbValues = "";
    : : for (var i=0; i+1 < MM_fields.length; i+=2) {
    : : var formVal = MM_fields[i+1];
    : : var MM_typesArray = MM_columns[i+1].split(",");
    : : var delim = (MM_typesArray[0] != "none") ? MM_typesArray[0] : "";
    : : var altVal = (MM_typesArray[1] != "none") ? MM_typesArray[1] : "";
    : : var emptyVal = (MM_typesArray[2] != "none") ? MM_typesArray[2] : "";
    : : if (formVal == "" || formVal == "undefined") {
    : : formVal = emptyVal;
    : : } else {
    : : if (altVal != "") {
    : : formVal = altVal;
    : : } else if (delim == "'") { // escape quotes
    : : formVal = "'" + formVal.replace(/'/g,"''") + "'";
    : : } else {
    : : formVal = delim + formVal + delim;
    : : }
    : : }
    : : MM_tableValues += ((i != 0) ? "," : "") + MM_columns[i];
    : : MM_dbValues += ((i != 0) ? "," : "") + formVal;
    : : }
    : : MM_editQuery = "insert into " + MM_editTable + " (" + MM_tableValues + ") values (" + MM_dbValues + ")";
    : :
    : : if (!MM_abortEdit) {
    : : // execute the insert
    : : var MM_editCmd = Server.CreateObject('ADODB.Command');
    : : MM_editCmd.ActiveConnection = MM_editConnection;
    : : MM_editCmd.CommandText = MM_editQuery;
    : [red]
    : Response.Write "Debug:sql="&MM_editQuery
    : Response.End
    : [/red]
    :
    : : MM_editCmd.Execute();
    : : MM_editCmd.ActiveConnection.Close();
    : :
    : : if (MM_editRedirectUrl) {
    : : Response.Redirect(MM_editRedirectUrl);
    : : }
    : : }
    : :
    : : }
    : : %>
    : : <%
    : : var Recordset1__num = "99";
    : : if (String(Request.Form("Cust")) != "undefined" &&
    : : String(Request.Form("Cust")) != "") {
    : : Recordset1__num = String(Request.Form("Cust"));
    : : }
    : : %>
    : : <%
    : : var Recordset1 = Server.CreateObject("ADODB.Recordset");
    : : Recordset1.ActiveConnection = MM_Service_STRING;
    : : Recordset1.Source = "SELECT Customer FROM Customer WHERE Cust_Acc_ID = "+ Recordset1__num.replace(/'/g, "''") + "";
    : : Recordset1.CursorType = 0;
    : : Recordset1.CursorLocation = 2;
    : : Recordset1.LockType = 1;
    : : Recordset1.Open();
    : : var Recordset1_numRows = 0;
    : : %>
    : :
    : :
    : : Hospital
    : :
    : :
    : :
    : :
    : :
    : :
    : :
    : :
    : :
    : : The error it Gives is in runtime, when I hit the Insert Button:
    : : Syntax error in INSERT INTO statement.
    : :
    : : If anyone can help or suggest anything in anyway, I would appriciate it.
    : : It is just this holding me back from completing my web application.
    : :
    :
    :
    : Add those lines which I have marked in red,and see what the sql is.Hopefully you will find what is wrong,if not,copy/paste the sql and let us have a look.
    :
    :
    :


    I am sorry, I should have noticed that you were using jascript,I missed a semicolon there:

    Try putting a semi colon at the end :

    Response.Write("Debug:sql="+MM_editQuery );
    Response.End ;

    copy paste here whatever you see as output





  • : [b][red]This message was edited by Flakes at 2004-12-16 1:1:50[/red][/b][hr]
    : : : I am trying to insert infomation into my database. My other pages work with inserting infomation into the tables but this page does not and i have no idea why or what to do.
    : : :
    : : : The code:
    : : : <%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
    : : :
    : : : <%
    : : : // *** Edit Operations: declare variables
    : : :
    : : : // set the form action variable
    : : : var MM_editAction = Request.ServerVariables("SCRIPT_NAME");
    : : : if (Request.QueryString) {
    : : : MM_editAction += "?" + Request.QueryString;
    : : : }
    : : :
    : : : // boolean to abort record edit
    : : : var MM_abortEdit = false;
    : : :
    : : : // query string to execute
    : : : var MM_editQuery = "";
    : : : %>
    : : : <%
    : : : // *** Insert Record: set variables
    : : :
    : : : if (String(Request("MM_insert")) == "form1") {
    : : :
    : : : var MM_editConnection = MM_Service_STRING;
    : : : var MM_editTable = "Hospital";
    : : : var MM_editRedirectUrl = "Index.asp";
    : : : var MM_fieldsStr = "Hospital_Name|value|Ward|value|Location|value|CityTown|value|Geogr_Area|value|Contact_Number|value|Customer|value";
    : : : var MM_columnsStr = "Hospital_Name|',none,''|Ward|',none,''|Location|',none,''|City/Town|',none,''|Geogr_Area|',none,''|Contact_Number|',none,''|Cust_Acc_ID|none,none,NULL";
    : : :
    : : : // create the MM_fields and MM_columns arrays
    : : : var MM_fields = MM_fieldsStr.split("|");
    : : : var MM_columns = MM_columnsStr.split("|");
    : : :
    : : : // set the form values
    : : : for (var i=0; i+1 < MM_fields.length; i+=2) {
    : : : MM_fields[i+1] = String(Request.Form(MM_fields[i]));
    : : : }
    : : :
    : : : // append the query string to the redirect URL
    : : : if (MM_editRedirectUrl && Request.QueryString && Request.QueryString.Count > 0) {
    : : : MM_editRedirectUrl += ((MM_editRedirectUrl.indexOf('?') == -1)?"?":"&") + Request.QueryString;
    : : : }
    : : : }
    : : : %>
    : : : <%
    : : : // *** Insert Record: construct a sql insert statement and execute it
    : : :
    : : : if (String(Request("MM_insert")) != "undefined") {
    : : :
    : : : // create the sql insert statement
    : : : var MM_tableValues = "", MM_dbValues = "";
    : : : for (var i=0; i+1 < MM_fields.length; i+=2) {
    : : : var formVal = MM_fields[i+1];
    : : : var MM_typesArray = MM_columns[i+1].split(",");
    : : : var delim = (MM_typesArray[0] != "none") ? MM_typesArray[0] : "";
    : : : var altVal = (MM_typesArray[1] != "none") ? MM_typesArray[1] : "";
    : : : var emptyVal = (MM_typesArray[2] != "none") ? MM_typesArray[2] : "";
    : : : if (formVal == "" || formVal == "undefined") {
    : : : formVal = emptyVal;
    : : : } else {
    : : : if (altVal != "") {
    : : : formVal = altVal;
    : : : } else if (delim == "'") { // escape quotes
    : : : formVal = "'" + formVal.replace(/'/g,"''") + "'";
    : : : } else {
    : : : formVal = delim + formVal + delim;
    : : : }
    : : : }
    : : : MM_tableValues += ((i != 0) ? "," : "") + MM_columns[i];
    : : : MM_dbValues += ((i != 0) ? "," : "") + formVal;
    : : : }
    : : : MM_editQuery = "insert into " + MM_editTable + " (" + MM_tableValues + ") values (" + MM_dbValues + ")";
    : : :
    : : : if (!MM_abortEdit) {
    : : : // execute the insert
    : : : var MM_editCmd = Server.CreateObject('ADODB.Command');
    : : : MM_editCmd.ActiveConnection = MM_editConnection;
    : : : MM_editCmd.CommandText = MM_editQuery;
    : : [red]
    : : Response.Write "Debug:sql="&MM_editQuery
    : : Response.End
    : : [/red]
    : :
    : : : MM_editCmd.Execute();
    : : : MM_editCmd.ActiveConnection.Close();
    : : :
    : : : if (MM_editRedirectUrl) {
    : : : Response.Redirect(MM_editRedirectUrl);
    : : : }
    : : : }
    : : :
    : : : }
    : : : %>
    : : : <%
    : : : var Recordset1__num = "99";
    : : : if (String(Request.Form("Cust")) != "undefined" &&
    : : : String(Request.Form("Cust")) != "") {
    : : : Recordset1__num = String(Request.Form("Cust"));
    : : : }
    : : : %>
    : : : <%
    : : : var Recordset1 = Server.CreateObject("ADODB.Recordset");
    : : : Recordset1.ActiveConnection = MM_Service_STRING;
    : : : Recordset1.Source = "SELECT Customer FROM Customer WHERE Cust_Acc_ID = "+ Recordset1__num.replace(/'/g, "''") + "";
    : : : Recordset1.CursorType = 0;
    : : : Recordset1.CursorLocation = 2;
    : : : Recordset1.LockType = 1;
    : : : Recordset1.Open();
    : : : var Recordset1_numRows = 0;
    : : : %>
    : : :
    : : :
    : : : Hospital
    : : :
    : : :
    : : :
    : : :
    : : :
    : : :
    : : :
    : : :
    : : :
    : : : The error it Gives is in runtime, when I hit the Insert Button:
    : : : Syntax error in INSERT INTO statement.
    : : :
    : : : If anyone can help or suggest anything in anyway, I would appriciate it.
    : : : It is just this holding me back from completing my web application.
    : : :
    : :
    : :
    : : Add those lines which I have marked in red,and see what the sql is.Hopefully you will find what is wrong,if not,copy/paste the sql and let us have a look.
    : :
    : :
    : :
    :
    :
    : I am sorry, I should have noticed that you were using jascript,I missed a semicolon there:
    :
    : Try putting a semi colon at the end :
    :
    : Response.Write("Debug:sql="+MM_editQuery );
    : Response.End ;
    :
    : copy paste here whatever you see as output
    :
    :
    The output I got was:
    Debug:sql=insert into Hospital (Hospital_Name,Ward,Location,City/Town,Geogr_Area,Contact_Number,Cust_Acc_ID) values ('Hospital','36','Test','Test','Eastern Cape','123456',7)

    It seems to be correct. All the fields are text in the database except for the Cust_Acc_ID which is a numeric field(Foreign Key). The Primary Key is a AutoNumber in the database table.

  • : : [b][red]This message was edited by Flakes at 2004-12-16 1:1:50[/red][/b][hr]
    : : : : I am trying to insert infomation into my database. My other pages work with inserting infomation into the tables but this page does not and i have no idea why or what to do.
    : : : :
    : : : : The code:
    : : : : <%@LANGUAGE="JAVASCRIPT" CODEPAGE="1252"%>
    : : : :
    : : : : <%
    : : : : // *** Edit Operations: declare variables
    : : : :
    : : : : // set the form action variable
    : : : : var MM_editAction = Request.ServerVariables("SCRIPT_NAME");
    : : : : if (Request.QueryString) {
    : : : : MM_editAction += "?" + Request.QueryString;
    : : : : }
    : : : :
    : : : : // boolean to abort record edit
    : : : : var MM_abortEdit = false;
    : : : :
    : : : : // query string to execute
    : : : : var MM_editQuery = "";
    : : : : %>
    : : : : <%
    : : : : // *** Insert Record: set variables
    : : : :
    : : : : if (String(Request("MM_insert")) == "form1") {
    : : : :
    : : : : var MM_editConnection = MM_Service_STRING;
    : : : : var MM_editTable = "Hospital";
    : : : : var MM_editRedirectUrl = "Index.asp";
    : : : : var MM_fieldsStr = "Hospital_Name|value|Ward|value|Location|value|CityTown|value|Geogr_Area|value|Contact_Number|value|Customer|value";
    : : : : var MM_columnsStr = "Hospital_Name|',none,''|Ward|',none,''|Location|',none,''|City/Town|',none,''|Geogr_Area|',none,''|Contact_Number|',none,''|Cust_Acc_ID|none,none,NULL";
    : : : :
    : : : : // create the MM_fields and MM_columns arrays
    : : : : var MM_fields = MM_fieldsStr.split("|");
    : : : : var MM_columns = MM_columnsStr.split("|");
    : : : :
    : : : : // set the form values
    : : : : for (var i=0; i+1 < MM_fields.length; i+=2) {
    : : : : MM_fields[i+1] = String(Request.Form(MM_fields[i]));
    : : : : }
    : : : :
    : : : : // append the query string to the redirect URL
    : : : : if (MM_editRedirectUrl && Request.QueryString && Request.QueryString.Count > 0) {
    : : : : MM_editRedirectUrl += ((MM_editRedirectUrl.indexOf('?') == -1)?"?":"&") + Request.QueryString;
    : : : : }
    : : : : }
    : : : : %>
    : : : : <%
    : : : : // *** Insert Record: construct a sql insert statement and execute it
    : : : :
    : : : : if (String(Request("MM_insert")) != "undefined") {
    : : : :
    : : : : // create the sql insert statement
    : : : : var MM_tableValues = "", MM_dbValues = "";
    : : : : for (var i=0; i+1 < MM_fields.length; i+=2) {
    : : : : var formVal = MM_fields[i+1];
    : : : : var MM_typesArray = MM_columns[i+1].split(",");
    : : : : var delim = (MM_typesArray[0] != "none") ? MM_typesArray[0] : "";
    : : : : var altVal = (MM_typesArray[1] != "none") ? MM_typesArray[1] : "";
    : : : : var emptyVal = (MM_typesArray[2] != "none") ? MM_typesArray[2] : "";
    : : : : if (formVal == "" || formVal == "undefined") {
    : : : : formVal = emptyVal;
    : : : : } else {
    : : : : if (altVal != "") {
    : : : : formVal = altVal;
    : : : : } else if (delim == "'") { // escape quotes
    : : : : formVal = "'" + formVal.replace(/'/g,"''") + "'";
    : : : : } else {
    : : : : formVal = delim + formVal + delim;
    : : : : }
    : : : : }
    : : : : MM_tableValues += ((i != 0) ? "," : "") + MM_columns[i];
    : : : : MM_dbValues += ((i != 0) ? "," : "") + formVal;
    : : : : }
    : : : : MM_editQuery = "insert into " + MM_editTable + " (" + MM_tableValues + ") values (" + MM_dbValues + ")";
    : : : :
    : : : : if (!MM_abortEdit) {
    : : : : // execute the insert
    : : : : var MM_editCmd = Server.CreateObject('ADODB.Command');
    : : : : MM_editCmd.ActiveConnection = MM_editConnection;
    : : : : MM_editCmd.CommandText = MM_editQuery;
    : : : [red]
    : : : Response.Write "Debug:sql="&MM_editQuery
    : : : Response.End
    : : : [/red]
    : : :
    : : : : MM_editCmd.Execute();
    : : : : MM_editCmd.ActiveConnection.Close();
    : : : :
    : : : : if (MM_editRedirectUrl) {
    : : : : Response.Redirect(MM_editRedirectUrl);
    : : : : }
    : : : : }
    : : : :
    : : : : }
    : : : : %>
    : : : : <%
    : : : : var Recordset1__num = "99";
    : : : : if (String(Request.Form("Cust")) != "undefined" &&
    : : : : String(Request.Form("Cust")) != "") {
    : : : : Recordset1__num = String(Request.Form("Cust"));
    : : : : }
    : : : : %>
    : : : : <%
    : : : : var Recordset1 = Server.CreateObject("ADODB.Recordset");
    : : : : Recordset1.ActiveConnection = MM_Service_STRING;
    : : : : Recordset1.Source = "SELECT Customer FROM Customer WHERE Cust_Acc_ID = "+ Recordset1__num.replace(/'/g, "''") + "";
    : : : : Recordset1.CursorType = 0;
    : : : : Recordset1.CursorLocation = 2;
    : : : : Recordset1.LockType = 1;
    : : : : Recordset1.Open();
    : : : : var Recordset1_numRows = 0;
    : : : : %>
    : : : :
    : : : :
    : : : : Hospital
    : : : :
    : : : :
    : : : :
    : : : :
    : : : :
    : : : :
    : : : :
    : : : :
    : : : :
    : : : : The error it Gives is in runtime, when I hit the Insert Button:
    : : : : Syntax error in INSERT INTO statement.
    : : : :
    : : : : If anyone can help or suggest anything in anyway, I would appriciate it.
    : : : : It is just this holding me back from completing my web application.
    : : : :
    : : :
    : : :
    : : : Add those lines which I have marked in red,and see what the sql is.Hopefully you will find what is wrong,if not,copy/paste the sql and let us have a look.
    : : :
    : : :
    : : :
    : :
    : :
    : : I am sorry, I should have noticed that you were using jascript,I missed a semicolon there:
    : :
    : : Try putting a semi colon at the end :
    : :
    : : Response.Write("Debug:sql="+MM_editQuery );
    : : Response.End ;
    : :
    : : copy paste here whatever you see as output
    : :
    : :
    : The output I got was:
    : Debug:sql=insert into Hospital (Hospital_Name,Ward,Location,City/Town,Geogr_Area,Contact_Number,Cust_Acc_ID) values ('Hospital','36','Test','Test','Eastern Cape','123456',7)
    :
    : It seems to be correct. All the fields are text in the database except for the Cust_Acc_ID which is a numeric field(Foreign Key). The Primary Key is a AutoNumber in the database table.
    :
    :
    I've discovered the problem. Firstly I cannot believe I was so stupid to do it and secondly so blind not to see it. My problem causer is the backslash and & sign in my field names in my database. Finally saw it and fixed it and now all is well and working.

    Thank you so much for your help, without the Response.Write I might not have noticed it and seen my error. I tryly appriciate the help, thanks a million.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories