...

View Full Version : SQL Advice - 3 tables involved



mack1604
03-02-2007, 06:08 PM
I'm trying to call from two tables with the "sqlInsert" statement. My question is does this query look right?



Dim rs,sqlInsert,your_userID,your_leagueID
sqlInsert = "SELECT Users.UserID, BSA_Leagues.LeagueID FROM Users, BSA_Leagues WHERE Cust_UserName='"&Request.Cookies("UserName")&"'"

Set rs = server.CreateObject("Adodb.Recordset")
rs.open sqlInsert,connection,3
your_userID=rs("UserID")
your_LeagueID=rs("LeagueID")
rs.close
set rs=Nothing

sSQL = "INSERT INTO BSA_STATS (UserID, LeagueID, Week, LaneNumber, Game1, Game2, Game3) values ("& your_userID &",'" & _
Your_LeagueID & "'," & Your_Week & "'," & Your_LaneNumber & "'," & Your_Game1 & "'," & Your_Game2 & "'," & Your_Game3 & "')"


The Cust_UserName column is in the Users table.

I'm also getting this error:

Microsoft VBScript compilation error '800a0411'

Name redefined

/bsa/bsa_stats_added.asp, line 97

Dim rs,sqlInsert,your_userID,your_leagueID
---------------------------^

I'm pretty new to this, so any help would be appreciated. Thanks

nikkiH
03-02-2007, 07:11 PM
Name redefined

That means you have a Dim for it twice.

mack1604
03-02-2007, 07:42 PM
Sorry if this sounds stupid, so how do I structure this query? Do I create a totally new SQL statement for the second table? The books I have are not covering this.

mack1604
03-02-2007, 09:10 PM
I found what was causing the name redefined error and now I get this:

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression '1',7''.

/bsa/bsa_stats_added.asp, line 111

I'm guessing that my SQL statement is not written correctly. This is the first time I've tried to do this. Does anyone have any advice? Thanks.



Dim rs,sqlInsert,your_userID,your_leagueID
sqlInsert = "SELECT Users.UserID, BSA_Leagues.LeagueID FROM Users, BSA_Leagues WHERE Cust_UserName='"&Request.Cookies("UserName")&"'"

Set rs = server.CreateObject("Adodb.Recordset")
rs.open sqlInsert,connection,3
your_userID=rs("UserID")
your_LeagueID=rs("LeagueID")
rs.close
set rs=Nothing

sSQL = "INSERT INTO BSA_STATS (UserID, LeagueID, Week, LaneNumber, Game1, Game2, Game3) values ("& your_userID &",'" & _
Your_LeagueID & "'," & Your_Week & "'," & Your_LaneNumber & "'," & Your_Game1 & "'," & Your_Game2 & "'," & Your_Game3 & "')"

miranda
03-02-2007, 09:49 PM
Right after the line where you assign the values to your SQL Query, add these two lines

Response.Write sSQL
Response.End

You will now be able to visually see what is wrong with your SQL Query.

In case you missed it, look closely at how you are showing the values and bear in mind that If the corresponding data type for that column is a text or memo data type in Access (varchar, char, text, nvarchar, nchar in SQL Server) you must enclose it in single quotes. If it is a numerical data type number or autonumber in Access (int, tinyint, bigint,smallint, float, money, smallmoney, numeric, real, or decimal in SQL Server) then you do not enclose it in the single quotes. So look at your database in design view and see what the data type is for each of the columns. Now you will see what is wrong with the following



sSQL = "INSERT INTO BSA_STATS (UserID, LeagueID, Week, LaneNumber, Game1, Game2, Game3) values ("& your_userID &",'" & _
Your_LeagueID & "'," & Your_Week & "'," & Your_LaneNumber & "'," & Your_Game1 & "'," & Your_Game2 & "'," & Your_Game3 & "')"


Hint look at the color coding that I added for you. Do you see where you are showing the left part to be numerical data and the right part of the same color to be string

mack1604
03-02-2007, 10:01 PM
All of the colums are a numerical value either autonumber or number. So you're saying to remove all of the single quotes?

nikkiH
03-02-2007, 10:02 PM
Also, we might assume you're using Access, but can you confirm? The syntax of queries is different for Access, Sql Server, etc.

mack1604
03-02-2007, 10:06 PM
Yes, I am using Access.

miranda
03-02-2007, 10:07 PM
Yes, remove them. You do not use single quotes around numerical data types. Only around strings (text etc.)


sSQL = "INSERT INTO BSA_STATS (UserID, LeagueID, Week, LaneNumber, Game1, Game2, Game3) values ("& your_userID &"," & _
Your_LeagueID & "," & Your_Week & "," & Your_LaneNumber & "," & Your_Game1 & "," & Your_Game2 & "," & Your_Game3 & ")"


AND if you noticed you had a single quote only on one side (to the right) of many of your variables.

miranda
03-02-2007, 10:10 PM
one other thing just because the value you ae inserting is a number if the DATA TYPE of that column is not a numeric data type then it too must be enclosed in single quotes.

mack1604
03-02-2007, 10:10 PM
I ran the SQL and this is the result:

INSERT INTO BSA_STATS (UserID, LeagueID, Week, LaneNumber, Game1, Game2, Game3) values (2,'6',1',7',',',')

The values represent the UserID, LeagueID, Week, Left Lane Number. And the three empty spots were supposed to the 3 scores.

mack1604
03-02-2007, 10:13 PM
This is the result of running the SQL without the single quotes and yes all are number fields in Access.

INSERT INTO BSA_STATS (UserID, LeagueID, Week, LaneNumber, Game1, Game2, Game3) values (2,6,1,7,,,)

The values are correct but still missing the 3 scores though. Below is a link to the database if you'd like to take a look.

http://www.bowlingballsandmore.com/downloads/BBAM.zip

Also, the error has changed:

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

/bsa/bsa_stats_added.asp, line 111

miranda
03-02-2007, 10:39 PM
Now if the 3 empty spots are supposed to hold a numeric value representing the score, you need to look at the business logic of your code.

Where you assign the values to the variables do a check to see if they are indeed numeric values ( you wouldn't believe what people will try to put into fields even if you tell them the field accepts numbers only!!)

So what you need to do is something like this


boolError = false

If Not IsNumeric(Request.Form("Your_Game1")) Then boolError = True
If Not IsNumeric(Request.Form("Your_Game2")) Then boolError = True
If Not IsNumeric(Request.Form("Your_Game3")) Then boolError = True

If boolError = True Then
Response.Write "<h3>Enter number values only in the score fields</h3>"
Response.Write "<a href=""javascript:history.go(-1)"">Try again</a>"
Response.End
End If
or like this


If Not IsNumeric(Request.Form("Your_Game1")) Then Your_Game1 = 0
If Not IsNumeric(Request.Form("Your_Game2")) Then Your_Game2 = 0
If Not IsNumeric(Request.Form("Your_Game3")) Then Your_Game3 = 0

'assign all of the games a value of zero

both examples assume the form that you are getting the scores for each game from is named Your_Game1, Your_Game2, Your_Game3
so that you can get their vaulues using the request.form method to get the values. if it isn't just substitute the name of your form fields into the Request.Form("Your_field_name _here")

mack1604
03-03-2007, 03:25 PM
Shoudn't that have been taken care of with this?



'declare your variables
dim Your_UserName
Your_UserName = ("UserName")
dim Your_LeagueName
Your_LeagueName = ("LeagueName")
dim Your_Week
Your_Week = ("Week")
dim Your_LaneNumber
Your_LaneNumber = ("LaneNumber")
dim Your_Game1
Your_Game1 = ("Game1")
dim Your_Game2
Your_Game2 = ("Game2")
dim Your_Game3
Your_Game3 = ("Game3")
Dim sConnString, connection, sSQL
'Receiving values from Form, assign the values entered to variables
Your_Week = Request.Form("Week")
Your_LaneNumber = Request.Form("LaneNumber")
Your_Game1 = Request.Form("Game1")
Your_Game2 =Request.Form("Game2")
Your_Game3 =Request.Form("Game3")

miranda
03-06-2007, 06:17 PM
Shoudn't that have been taken care of with this?



'declare your variables
dim Your_UserName
Your_UserName = ("UserName")
dim Your_LeagueName
Your_LeagueName = ("LeagueName")
dim Your_Week
Your_Week = ("Week")
dim Your_LaneNumber
Your_LaneNumber = ("LaneNumber")
dim Your_Game1
Your_Game1 = ("Game1")
dim Your_Game2
Your_Game2 = ("Game2")
dim Your_Game3
Your_Game3 = ("Game3")
Dim sConnString, connection, sSQL
'Receiving values from Form, assign the values entered to variables
Your_Week = Request.Form("Week")
Your_LaneNumber = Request.Form("LaneNumber")
Your_Game1 = Request.Form("Game1")
Your_Game2 =Request.Form("Game2")
Your_Game3 =Request.Form("Game3")


What are you trying to accomplish with this part?

Your_UserName = ("UserName")
Your_LeagueName = ("LeagueName")
Your_Week = ("Week")
Your_LaneNumber = ("LaneNumber")
Your_Game1 = ("Game1")
Your_Game2 = ("Game2")
Your_Game3 = ("Game3")
Those lines are assigning strings to each of the variables. You can declare your variables and then assign a value to them later on. You do not have to assign a value to it right after declaration. Declaring the variable and not assigning a value to it is the same as saying that variable has a NULL value. You are really only assigning the values you want to the variables when you do this

Your_Week = Request.Form("Week")
Your_LaneNumber = Request.Form("LaneNumber")
Your_Game1 = Request.Form("Game1")
Your_Game2 =Request.Form("Game2")
Your_Game3 =Request.Form("Game3")



So what you need to have is this

dim Your_UserName
dim Your_LeagueName
dim Your_Week
dim Your_LaneNumber
dim Your_Game1
dim Your_Game2
dim Your_Game3

Your_Week = Request.Form("Week")
Your_LaneNumber = Request.Form("LaneNumber")
Your_Game1 = Request.Form("Game1")
Your_Game2 =Request.Form("Game2")
Your_Game3 =Request.Form("Game3")


and omit assign the strings of the variable names to each of them.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum