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) & " )"
LVL 1
AleksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GaryCommented:
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
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
if you're inserting string values, you need to surround them with quotes. can you post example data that each variable would contain?
0
AleksAuthor Commented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
otherwise please list the data type for each column
0
GaryCommented:
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
AleksAuthor Commented:
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
AleksAuthor Commented:
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
GaryCommented:
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
GaryCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
:)  it worked in both scenarios
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.