?
Solved

syntax error on sql

Posted on 2014-10-21
11
Medium Priority
?
177 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:Aleks
  • 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 34

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:Aleks
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

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 34

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:Aleks
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:Aleks
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 2000 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:Aleks
ID: 40395724
:)  it worked in both scenarios
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

578 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