ripping my hair out over a Incorrect syntax near ' | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

ripping my hair out over a Incorrect syntax near ‘

i have spent hours pouring over this dam code and i cannot see whats causing the problem,
basically i am taking data from a webform and inserting the data into 2 tables in the same database. it works with the single database but it seems to fall over with the following error; Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ‘,’.
/staff_forms/AddenbrookesP4formE_2xtable.asp, line 182 please can anyone spot the problem, before i go bald at 25.. <%@LANGUAGE="VBSCRIPT"%>
<!–#include file="Connections/sfDB.asp" –>
<%
‘ *** Edit Operations: declare variables Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editQuery2
Dim MM_editCmd Dim MM_editConnection
Dim MM_editTable
Dim MM_editTable2
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_editColumn2
Dim MM_recordId
Dim MM_fieldsStr
Dim MM_fieldsStr2
Dim MM_columnsStr
Dim MM_columnsStr2
Dim MM_fields
dim MM_fields2
Dim MM_columns
Dim MM_Columns2
Dim MM_typeArray
Dim MM_typeArray2
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 & "?" & Server.HTMLEncode(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")) = "form1") Then MM_editConnection = MM_sfDB_STRING ‘poss delete
MM_editTable2 = "dbo.Confidential_staff_data" ‘ Staff_Table Insert (i hope)
MM_editRedirectUrl = ""
MM_fieldsStr2 = "sur_name|value|for_name|value|payroll_num|value|PayPoint_num|value|Personal_num|value|A_NHS_1|value|NHS_2|value|B_OutsideNHS_1|value|B_outsideNHS_2|value"
MM_columnsStr2 = "Surname|’,none,”|Forename|’,none,”|Payroll_num|none,none,NULL|Paypoint_num|none,none,NULL|Personal_num|none,none,NULL|A_NHS_1|’,none,”|A_NHS_2|’,none,”|B_NHS_1|’,none,”|B_NSH_2|’,none,”" ‘ create the MM_fields and MM_columns arrays
MM_fields2 = Split(MM_fieldsStr2, "|")
MM_columns2 = Split(MM_columnsStr2, "|") ‘ set the form values
For MM_i = LBound(MM_fields2) To UBound(MM_fields2) Step 2
MM_fields2(MM_i+1) = CStr(Request.Form(MM_fields2(MM_i)))
Next ‘ MM_editConnection = MM_sfDB_STRING <– poss bring back the line
MM_editTable = "dbo.Confidential_form_P4" ‘P4_table insert (i hope)
MM_editRedirectUrl = ""
MM_fieldsStr = "remaning_in_post|value|Post_num|value|post_type|value|Financial_code|value|Dept|value|Grade|value|Job_title|value|Last_working_day|value|no_days_paid_or_lieu_notice|value|Paid_notice_commence|value|outstanding_an_leave_to_be_paid|value|excess_days_annual_leave|value|reemploy|value|termination_date|value|Other_details_payments_to_be_made|value|Voluntry_1A|value|Dissatisfied_1B|value|Personal_1C|value|Dismissed_2|value|Organ_change_3|value|Retirement_4|value|Death|value|Forwarding_address|value|forward_post_code|value|forward_tele|value|new_employer_address|value|new_employer_postcode|value|new_employer_tele|value|authorised_Officer|value|last_day_of_payment|value"
MM_columnsStr = "remaining_other_posts|’,none,”|post_number|’,none,”|post_type|’,none,”|fincial_code|none,none,NULL|department|’,none,”|grade|’,none,”|job_title|’,none,”|last_working_day|’,none,NULL|num_days_notice|none,none,NULL|date_paid_notice_start|’,none,NULL|outstanding_annual_leave_days|none,none,NULL|excess_an_leave_to_be_deducted|none,none,NULL|re_employ|’,none,”|Termination_date|’,none,NULL|other_details_payments|’,none,”|Leaving_1A|’,none,”|Leaving_1B|’,none,”|Leaving_1C|’,none,”|Leaving_2|’,none,”|Leaving_3|’,none,”|Leaving_4|’,none,”|Leaving_5|’,none,”|forward_address|’,none,”|forward_postcode|’,none,”|forward_tele|none,none,NULL|New_employer|’,none,”|new_employer_postcode|’,none,”|New_employer_tele|none,none,NULL|Authorised_officer|’,none,”|Last_day_of_payment|’,none,NULL" ‘ 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
Dim MM_tableValues2
Dim MM_dbValues2 If (CStr(Request("MM_insert")) <> "") Then ‘ *** Insert Record: Staff_table insert ‘ create the sql insert statement
MM_tableValues2 = "" ‘staff table
MM_dbValues2 = "" ‘staff table
MM_tableValues = "" ‘p4 table
MM_dbValues = "" ‘p4 Table For MM_i = LBound(MM_fields2) To UBound(MM_fields2) Step 2
MM_formVal = MM_fields2(MM_i+1)
MM_typeArray2 = Split(MM_columns2(MM_i+1),",")
MM_delim = MM_typeArray2(0)
If (MM_delim = "none") Then MM_delim = ""
MM_altVal = MM_typeArray2(1)
If (MM_altVal = "none") Then MM_altVal = ""
MM_emptyVal = MM_typeArray2(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_fields2)) Then
MM_tableValues2 = MM_tableValues2 & ","
MM_dbValues2 = MM_dbValues2 & ","
End If
MM_tableValues2 = MM_tableValues2 & MM_columns2(MM_i)
MM_dbValues2 = MM_dbValues2 & MM_formVal2
Next
MM_editQuery2 = "insert into " & MM_editTable2 & " (" & MM_tableValues2 & ") values (" & MM_dbValues2 & ")" ‘ insert to table_P4
‘ 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 & ")" ‘ end of insert to database
‘ Now Actually Insert & Commit
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
‘ Code for staff insert execution
MM_editCmd.CommandText = MM_editQuery2
MM_editCmd.Execute
‘ End of code for staff insert execution
MM_editCmd.ActiveConnection.Close If (MM_editRedirectUrl <> "") Then
Response.Redirect(MM_editRedirectUrl)
End If
End If End If
%> Theres Not Enough Time In My World
On the line that sets MM_columnsStr2 – what are the CHAR(39)s doing in that pipeline-delimited list? It looks like the person who created the code was aware that you need to double-up CHAR(39)s inside strings, but hasn’t done so properly. Besides, I can’t see what use they could have in this particular example.
This is ASP code and you will get right suggestions in ASP forums.
But dear error says incorrect syntax on line 1.
Is it when you call sp then better post the sp.
janjitjain
its not line 1 the error is coming from its line 182, the problem is in the 2nd sql insert statement which is why i posted on the SQL forum, as i said i think its something small like a colon or mark in the wrong place but i thought id check it though with u guys as your much more up on SQL than me, (aka its been 5 years since i wrote any sql coding) adriaan
again its been a while since i wrote a website so im unsure what u mean reguarding Char(39)s ? thanks for your help guys
Theres Not Enough Time In My World
Check out these three scripts with the query results in each case,
perhaps this will make things a bit clearer to you:
script 1 DECLARE @MyVar VARCHAR(100)
SET @MyVar = ‘my string’
SELECT @MyVar —– my string
script 2 DECLARE @MyVar VARCHAR(100)
SET @MyVar = ‘my ‘ single quote’
SELECT @MyVar —– Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ‘single’.
Server: Msg 105, Level 15, State 1, Line 2
Unclosed quotation mark before the character string ‘
SELECT @MyVar
‘.
script 3 DECLARE @MyVar VARCHAR(100)
SET @MyVar = ‘my ” single quote’
SELECT @MyVar —– my ‘ single quote
thanks for your help but the ASP guys have found the problem, thansk for all your ideas Theres Not Enough Time In My World
]]>