Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL syntax

Status
Not open for further replies.
penguinspeaks

penguinspeaks

Technical User
Nov 13, 2002
234
US
I may be tired but I am pulling my hair out at this one.

Here is the error
Code: 
icrosoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-5.6.16]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(date_, first_, second_, third_, fourth_, players_) values (2014-9-13,40,25,13,0' at line 1

/payoutsasp.asp, line 15

here is my BASIC code that I feel as if I have written a million times
Code: 
strSQL = "insert into payouts set (date_, first_, second_, third_, fourth_, players_)"
strSQL = strSQL & " values "
strSQL = strSQL & "("&var6&","&var1&","&var2&","&var3&","&var4&","&var5&")"
conn.execute (strSQL)
'response.write (strsQL)

Now the first_, second_, third_, fourth_, fields are Decimal 4,2 because this is to reflect money
the player_ is INT as it will always be a whole number
the date_ is a date field

here is the response.write (strSQL)
Code: 
insert into payouts set (date_, first_, second_, third_, fourth_, players_) values (2014-9-13,40,25,13,0,16)

I know this is simple stuff, but what am I missing here?
 
Sort by date Sort by votes
For MySql I think you need to wrap the date inside single-quotes
 
Upvote 0 Downvote
The data is the same as a SQL Db. '"&var1&"' for text and "&varr1&" for anything not text. This is how I have been doing this for years.
I thought of the same as you and changed everything to reflect that but kept getting mismatches because of the syntax.

i do appreciate the reply though
 
Upvote 0 Downvote
guitarzan right you need ' around date
so it should be

SQL: 
insert into payouts set (date_, first_, second_, third_, fourth_, players_) values ('2014-9-13',40,25,13,0,16)
 
Upvote 0 Downvote
It has something to do with the "&var5&" I think.

I wrapped as you sugggested and still get this as the write sql

Code: 
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-5.6.16]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(date_, first_, second_, third_, fourth_, players_) values ('2014-9-13',40,25,13' at line 1

/payoutsasp.asp, line 15

now in where the 13 is in the data, there should be a 16 after that. I do a response.write var5 and it displays 16 but it is not putting it into the sql line for some reason. Here is the entire page of code. It is not much as I JUST started this page..

Code: 
<!--#include virtual="/inc/connection.asp"-->
<%

var1 = request.form("first_")
var2 = request.form("second_")
var3 = request.form("third_")
var4 = request.form("fourth_")
var5 = CInt(request.form("players_"))
var6 = request.form("date_")
response.write (var5)

strSQL = "insert into payouts set (date_, first_, second_, third_, fourth_, players_)"
strSQL = strSQL & " values "
strSQL = strSQL & "('"&var6&"',"&var1&","&var2&","&var3&","&var4&","&var5&")"
conn.execute (strSQL)
'response.write (strsQL)

'response.redirect "payouts.asp"
%>

here is the form code

Code: 
<%


d_now = date()
d = split(DateAdd("d",1,d_now),"/")
sdate = d(2) & "-" & d(0) & "-" & d(1)

%>
<body>
<form action="payoutsasp.asp" method="post">
<table>
	<tr>
		<td>Date:</td>
		<td><input name="date_" type="text" value="<%=sdate%>" /></td>
		<td>&nbsp;</td>
	</tr>
	<tr>
		<td># players</td>
		<td><input name="players_" style="width: 47px" type="text" value="0" /></td>
		<td>&nbsp;</td>
	</tr>
	<tr>
		<td>1st</td>
		<td><input name="First_" style="width: 47px" type="text" value="0" /></td>
		<td>&nbsp;</td>
	</tr>
	<tr>
		<td>2nd</td>
		<td><input name="second_" style="width: 47px" type="text" value="0" /></td>
		<td>&nbsp;</td>
	</tr>
	<tr>
		<td>3rd</td>
		<td><input name="third_" style="width: 47px" type="text" value="0" /></td>
		<td>&nbsp;</td>
	</tr>
	<tr>
		<td>4th</td>
		<td><input name="fourth_" style="width: 47px" type="text" value="0" /></td>
		<td>&nbsp;</td>
	</tr>
	<tr>
		<td colspan="2"><input name="Submit1" type="submit" value="submit" /></td>
		<td>&nbsp;</td>
	</tr>
</table>
</form>
 
Upvote 0 Downvote
Surround the line "conn.execute (strSQL)" with the code below, then post the results.

Code: 
On Error Resume Next
conn.execute (strSQL)
If Err.Number <> 0 Then
   response.write "Got Error " & Err.Number & ": " & Err.Description & "<BR>" & strSQL
   response.end
End If
On Error Goto 0
 
Upvote 0 Downvote
Code: 
Got Error -2147217900: [MySQL][ODBC 3.51 Driver][mysqld-5.6.16]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(date_, first_, second_, third_, fourth_, players_) values ('2014-9-13',40,25,13' at line 1
insert into payouts set (date_, first_, second_, third_, fourth_, players_) values ('2014-9-13',40,25,13,0,16)
 
Upvote 0 Downvote
Wow... we all missed something pretty obvious...

Code: 
insert into payouts [s][highlight #FCE94F]set[/highlight][/s] (date_, first_, second_, third_, fourth_, players_) values ('2014-9-13',40,25,13,0,16)
 
Upvote 0 Downvote
WOW. We sure did. This explains how something so simple can cause people to go crazy.

Thanks so much!!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
Problem: sql-Select with request.form
2
Replies
22
Views
338
leifoet
leifoet
jd.ford1
  • Locked
  • Question
Need help with syntax
Replies
10
Views
176
jd.ford1
jd.ford1
leifoet
  • Locked
  • Question
Selection query - looking for the correct syntax
Replies
8
Views
136
leifoet
leifoet
Blueie
  • Locked
  • Question
Correct syntax question 2
Replies
4
Views
92
Blueie
Blueie
axLW
  • Locked
  • Question
reduce number of sql statements and set EOF parameter
Replies
13
Views
167
ChrisHirst
ChrisHirst

Part and Inventory Search

Sponsor

Back
Top