Aleks
asked on
syntax error on sql
This should be a simple one. I get a syntax error on this:
sql = "insert into BillPaymntsRecvd ( BillsMainid, Entryby, itemID, PmtRecd, caseid ) values ( " & (BillsMainid) & ", " & (Entryby) & "," & itemIDs & ", " & amounts & ", " & (caseid) & " )"
sql = "insert into BillPaymntsRecvd ( BillsMainid, Entryby, itemID, PmtRecd, caseid ) values ( " & (BillsMainid) & ", " & (Entryby) & "," & itemIDs & ", " & amounts & ", " & (caseid) & " )"
if you're inserting string values, you need to surround them with quotes. can you post example data that each variable would contain?
ASKER
I got an error with the code above:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ','.
/bluedot/Intranet/Billing/ SPSavePaym entLines.a sp, line 133
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ','.
/bluedot/Intranet/Billing/
my guess is you need quotes around entryBy, so try this sql:
sql = "insert into BillPaymntsRecvd ( BillsMainid, Entryby, itemID, PmtRecd, caseid ) values ( " & (BillsMainid) & ", '" & (Entryby) & "'," & itemIDs & ", " & amounts & ", " & (caseid) & " )"
sql = "insert into BillPaymntsRecvd ( BillsMainid, Entryby, itemID, PmtRecd, caseid ) values ( " & (BillsMainid) & ", '" & (Entryby) & "'," & itemIDs & ", " & amounts & ", " & (caseid) & " )"
otherwise please list the data type for each column
I believe your ItemIDs are a comma seperated list?
sql = "insert into BillPaymntsRecvd ( BillsMainid, Entryby, itemID, PmtRecd, caseid ) values ( " & BillsMainid & ", '" & Entryby & "','" & itemIDs & "', '" & amounts & "', " & caseid & " )"
ASKER
It is comma separated, still get an error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ','.
/bluedot/Intranet/Billing/ SPSavePaym entLines.a sp, line 115
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ','.
/bluedot/Intranet/Billing/
ASKER
Let me post the whole code. When I get one entry I get the error (No commas), when I have more than one record it works fine (comma separated)
----- ALL THE CODE -----
<%
'-- create connection object and establish a connection to the database
set conn = Server.CreateObject("ADODB .Connectio n")
conn.Open MM_eimmigration_STRING
caseid = request.querystring("casei d")
itemIDs = request.form("itemid")
amounts = request.form("amount")
descriptions = request.form("ItemDesc")
'-- do a simple check to make sure there are multiple values
if InStr( itemIDs, "," ) > 0 and InStr( amounts, "," ) then
'-- multiple values passed
'-- put values into an array
Caseid = Request.Querystring("casei d")
BillsMainid = rs_topbillpymnt("MainPaymn tid")
Entryby = Session("Userid")
arrItemIDs = Split( itemIDs, "," )
arrAmounts = Split( amounts, "," )
arrDescriptions = Split (descriptions, ",")
'-- now loop through the array and insert into the database
for counter = 0 to UBound( arrItemIDs )
if arrItemIDs( counter ) <> "" then '-- you also may want to check to make sure it's an numerical value
sql = "insert into BillPaymntsRecvd ( BillsMainid, Entryby, itemID, PmtRecd, caseid ) values ( " & (BillsMainid) & ", " & (Entryby) & "," & arrItemIDs( counter ) & ", " & arrAmounts( counter ) & " , " & (caseid) & " )"
conn.Execute( sql ) '-- assumes you have a connection object created and connected to the database
end if
next
else
'-- code to handle only 1 or no values passed
sql = "insert into BillPaymntsRecvd ( BillsMainid, Entryby, itemID, PmtRecd, caseid ) values ( " & BillsMainid & ", '" & Entryby & "','" & itemIDs & "', '" & amounts & "', " & caseid & " )"
conn.Execute( sql )
end if
if conn.State <> 0 then conn.Close
set conn = nothing
%>
----- ALL THE CODE -----
<%
'-- create connection object and establish a connection to the database
set conn = Server.CreateObject("ADODB
conn.Open MM_eimmigration_STRING
caseid = request.querystring("casei
itemIDs = request.form("itemid")
amounts = request.form("amount")
descriptions = request.form("ItemDesc")
'-- do a simple check to make sure there are multiple values
if InStr( itemIDs, "," ) > 0 and InStr( amounts, "," ) then
'-- multiple values passed
'-- put values into an array
Caseid = Request.Querystring("casei
BillsMainid = rs_topbillpymnt("MainPaymn
Entryby = Session("Userid")
arrItemIDs = Split( itemIDs, "," )
arrAmounts = Split( amounts, "," )
arrDescriptions = Split (descriptions, ",")
'-- now loop through the array and insert into the database
for counter = 0 to UBound( arrItemIDs )
if arrItemIDs( counter ) <> "" then '-- you also may want to check to make sure it's an numerical value
sql = "insert into BillPaymntsRecvd ( BillsMainid, Entryby, itemID, PmtRecd, caseid ) values ( " & (BillsMainid) & ", " & (Entryby) & "," & arrItemIDs( counter ) & ", " & arrAmounts( counter ) & " , " & (caseid) & " )"
conn.Execute( sql ) '-- assumes you have a connection object created and connected to the database
end if
next
else
'-- code to handle only 1 or no values passed
sql = "insert into BillPaymntsRecvd ( BillsMainid, Entryby, itemID, PmtRecd, caseid ) values ( " & BillsMainid & ", '" & Entryby & "','" & itemIDs & "', '" & amounts & "', " & caseid & " )"
conn.Execute( sql )
end if
if conn.State <> 0 then conn.Close
set conn = nothing
%>
If there is a chance there are empty values then you should check for it before trying to execute an sql with blank values.
If you do
response.write sql
what do you get?
If you do
response.write sql
what do you get?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
:) it worked in both scenarios
Open in new window
Else what is the error message