View Full Version : Problem with displaying a correct info from dropdown

03-04-2007, 09:05 PM
I've generated a dynamic dropdown populated from my database on a web form which is working fine. The problem is that what is being entered into the database is the LeagueID of the first LeagueID written to the database by another user, (ie: first LeagueID record was 6 and no matter which league is selected from the dropdown, 6 is written to the database). When I print the SQL, all of the other field info is correct. Any ideas?

'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_LeagueID = Request.Form("LeagueID")
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")

sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("../db/BBAMstats.mdb")
'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")

'Open the connection to the database

'declare SQL statement that will query the database
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
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 & ")"
Response.Write sSQL
'define the connection string, specify database
'driver and the location of database
'execute the SQL

response.write "Your scores have been successfully recorded."
'Done. Close the connection object
Set connection = Nothing

When I print the SQL all of the information is correct, with the exception of the LeagueID.
Any help would be greatly appreciated.

03-05-2007, 05:19 PM
You set Your_LeagueID as this:

Your_LeagueID = Request.Form("LeagueID")
Then later in the code, you set it again with new value overwriting the old one:

So when you do an Insert, it will take the last value which is the original value in the database. So league id won't change.

03-05-2007, 05:46 PM
That was the problem. Thanks so much. I've been staring at this code for the last couple of days and just couldn't see it. You're a life saver. Thanks again. I gave you points and nominated you.

03-05-2007, 06:52 PM
Glad to be of help to you mack1604. :)