Solved

syntax error on sql

Posted on 2014-10-21
11
165 Views
Last Modified: 2014-10-21
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) & " )"
0
Comment
Question by:amucinobluedot
  • 4
  • 4
  • 3
11 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40395635
Try
sql = "insert into BillPaymntsRecvd ( BillsMainid, Entryby, itemID, PmtRecd, caseid ) values ( " & BillsMainid & ", '" & Entryby & "'," & itemIDs & ", '" & amounts & "', " & caseid & " )" 

Open in new window


Else what is the error message
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 40395637
if you're inserting string values, you need to surround them with quotes. can you post example data that each variable would contain?
0
 

Author Comment

by:amucinobluedot
ID: 40395646
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/SPSavePaymentLines.asp, line 133
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 33

Expert Comment

by:Big Monty
ID: 40395652
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) & " )"
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 40395655
otherwise please list the data type for each column
0
 
LVL 58

Expert Comment

by:Gary
ID: 40395662
I believe your ItemIDs are a comma seperated list?

sql = "insert into BillPaymntsRecvd ( BillsMainid, Entryby, itemID, PmtRecd, caseid ) values ( " & BillsMainid & ", '" & Entryby & "','" & itemIDs & "', '" & amounts & "', " & caseid & " )" 

Open in new window

0
 

Author Comment

by:amucinobluedot
ID: 40395678
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/SPSavePaymentLines.asp, line 115
0
 

Author Comment

by:amucinobluedot
ID: 40395680
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.Connection")
conn.Open MM_eimmigration_STRING

caseid = request.querystring("caseid")
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("caseid")  
    BillsMainid = rs_topbillpymnt("MainPaymntid")
      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

%>
0
 
LVL 58

Expert Comment

by:Gary
ID: 40395691
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?
0
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 40395703
Your code is unnecessarily long, you just need
<%
'-- create connection object and establish a connection to the database
set conn = Server.CreateObject("ADODB.Connection") 
conn.Open MM_eimmigration_STRING

caseid = request.querystring("caseid")
itemIDs = request.form("itemid")
amounts = request.form("amount")
descriptions = request.form("ItemDesc")


Caseid = Request.Querystring("caseid")  
BillsMainid = rs_topbillpymnt("MainPaymntid")
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

if conn.State <> 0 then conn.Close
set conn = nothing
	
%> 

Open in new window

0
 

Author Closing Comment

by:amucinobluedot
ID: 40395724
:)  it worked in both scenarios
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question