INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL syntax

SQL syntax

(OP)
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?

RE: SQL syntax

For MySql I think you need to wrap the date inside single-quotes

RE: SQL syntax

(OP)
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

RE: SQL syntax

guitarzan right you need ' around date
so it should be

CODE --> sql

insert into payouts set (date_, first_, second_, third_, fourth_, players_) values ('2014-9-13',40,25,13,0,16) 

RE: SQL syntax

(OP)
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> </td>
	</tr>
	<tr>
		<td># players</td>
		<td><input name="players_" style="width: 47px" type="text" value="0" /></td>
		<td> </td>
	</tr>
	<tr>
		<td>1st</td>
		<td><input name="First_" style="width: 47px" type="text" value="0" /></td>
		<td> </td>
	</tr>
	<tr>
		<td>2nd</td>
		<td><input name="second_" style="width: 47px" type="text" value="0" /></td>
		<td> </td>
	</tr>
	<tr>
		<td>3rd</td>
		<td><input name="third_" style="width: 47px" type="text" value="0" /></td>
		<td> </td>
	</tr>
	<tr>
		<td>4th</td>
		<td><input name="fourth_" style="width: 47px" type="text" value="0" /></td>
		<td> </td>
	</tr>
	<tr>
		<td colspan="2"><input name="Submit1" type="submit" value="submit" /></td>
		<td> </td>
	</tr>
</table>
</form> 

RE: SQL syntax

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 

RE: SQL syntax

(OP)

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) 

RE: SQL syntax

Wow... we all missed something pretty obvious...

CODE

insert into payouts set (date_, first_, second_, third_, fourth_, players_) values ('2014-9-13',40,25,13,0,16) 

RE: SQL syntax

(OP)
WOW. We sure did. This explains how something so simple can cause people to go crazy.

Thanks so much!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close