Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 15 of 15
  1. #1
    New Coder
    Join Date
    Mar 2007
    Location
    Florida
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Advice - 3 tables involved

    I'm trying to call from two tables with the "sqlInsert" statement. My question is does this query look right?

    Code:
    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
    Last edited by mack1604; 03-02-2007 at 06:30 PM.

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Name redefined

    That means you have a Dim for it twice.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    New Coder
    Join Date
    Mar 2007
    Location
    Florida
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Last edited by mack1604; 03-02-2007 at 07:44 PM.

  • #4
    New Coder
    Join Date
    Mar 2007
    Location
    Florida
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Code:
    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 & "')"

  • #5
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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

    Code:
    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

  • #6
    New Coder
    Join Date
    Mar 2007
    Location
    Florida
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts
    All of the colums are a numerical value either autonumber or number. So you're saying to remove all of the single quotes?

  • #7
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Also, we might assume you're using Access, but can you confirm? The syntax of queries is different for Access, Sql Server, etc.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #8
    New Coder
    Join Date
    Mar 2007
    Location
    Florida
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, I am using Access.

  • #9
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Yes, remove them. You do not use single quotes around numerical data types. Only around strings (text etc.)

    Code:
    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.

  • #10
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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.

  • #11
    New Coder
    Join Date
    Mar 2007
    Location
    Florida
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #12
    New Coder
    Join Date
    Mar 2007
    Location
    Florida
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Last edited by mack1604; 03-02-2007 at 10:31 PM.

  • #13
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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

    Code:
    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

    Code:
    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")
    Last edited by miranda; 03-02-2007 at 10:45 PM.

  • #14
    New Coder
    Join Date
    Mar 2007
    Location
    Florida
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Shoudn't that have been taken care of with this?

    Code:
    '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")
    Last edited by mack1604; 03-03-2007 at 03:38 PM.

  • #15
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Quote Originally Posted by mack1604 View Post
    Shoudn't that have been taken care of with this?

    Code:
    '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?
    Code:
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •