Solved

syntax error on sql

Posted on 2014-10-21
11
168 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
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

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!

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

752 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