Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

syntax error on sql

Posted on 2014-10-21
11
Medium Priority
?
175 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
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.

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Screencast - Getting to Know the Pipeline

963 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