...

View Full Version : Problem Inserting Record Into Database



mack1604
03-02-2007, 04:15 AM
I'm not sure if the problem is in my SQL statement or my Access database relationships, but I get this error:

Microsoft JET Database Engine error '80004005'

You cannot add or change a record because a related record is required in table 'USERS'.

/bsa/bsa_league_added.asp, line 95

And of course line 95 executes the SQL. This is the entire code for the page:

This is the code for the page:


<%@ Language=VBScript %>
<% Option Explicit %>
<%Response.Expires = -1 %>
<%Response.ExpiresAbsolute = Now() - 1 %>
<%Response.AddHeader "pragma", "no-cache" %>
<%Response.AddHeader "cache-control", "private" %>
<%Response.CacheControl = "no-cache" %>

<%
Response.Expires = -1000
Response.Buffer = True
Response.Clear
%>

<%Your_UserName = request.cookies("UserName")%>

<%
Response.Expires = -1000
Response.ExpiresAbsolute = Now() - 1
If request.cookies("UserName") = "" AND Session("UserName") = "" then
Response.Redirect ("unauthorized.asp")
Response.End
End If
%>

<%
Response.Expires = -1000
Response.ExpiresAbsolute = Now() - 1
If Session("UserName") = "" then
Response.Redirect ("unauthorized.asp")
Response.End
End If
%>
<html>
<head>
<title>Bowling Score Tracker</title>
<meta name="AUTHOR" content="James Royce">
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1">
<link rel="stylesheet" href="BSAstyle.css" type="text/css">
</head>
<body topmargin="0" leftmargin="0" rightmargin="0" bottommargin="0">
<table align="center" width="550" cellspacing="2" cellpadding="0" border="0" style="border-width: 1px; border-style: solid; color: #1b1b1b;" bgcolor="#000000" id="container">
<tr>
<td align="center">
<table align="center" width="550" border="0" cellspacing="0" cellpadding="0" bgcolor="2a3b83">
<tr>
<td valign="top" align="center" background="images/bsaheader.jpg" style="background-repeat: no-repeat">
<img src="images/clear.gif" height="141" width="550" />
<table width="420" height="420" border="0" cellspacing="0" cellpadding="6" background="images/form_bg.jpg" style="border-style: solid;border-width: 1px;border-color: #ffffff;">
<tr>
<td colspan="6" align="left">
<ul><li><a href="bsa_add_league.asp?UserLoggedIn=<% =Your_UserName %>" target="_top">Add Another League</a>
<li><a href="bsa_default.asp?UserLoggedIn=<% =Your_UserName %>" target="_top">Enter Scores</a>
<li><a href="bsa.asp?UserLoggedIn=<% =Your_UserName %>" target="_top">View Stats.</a>
<li><a href="information.asp?UserLoggedIn=<% =Your_UserName %>" target="_top">My Account</a>
<li><a href="loginclose.asp" target="_top">Log Out</a></li>
</ul>
</td>
</tr>
<tr>
<td align="center" style="padding-left:6px;padding-top:6px;padding-right:6px;padding-bottom:6px;">
<%
'declare your variables
dim Your_UserName
Your_UserName = ("UserName")
dim Your_LeagueName
Your_LeagueName = ("LeagueName")
dim Your_StartingLane
Your_StartingLane = ("StartingLane")
dim Your_TeamsNumber
Your_TeamsNumber = ("TeamsNumber")
dim Your_LeagueDescription
Your_LeagueDescription = ("LeagueDescription")
Dim sConnString, connection, sSQL
'Receiving values from Form, assign the values entered to variables
Your_LeagueName = Request.Form("LeagueName")
Your_StartingLane = Request.Form("StartingLane")
Your_TeamsNumber = Request.Form("TeamsNumber")
Your_LeagueDescription =Request.Form("LeagueDescription")

'declare SQL statement that will query the database
sSQL = "INSERT INTO BSA_LEAGUES (LeagueName, StartingLane, TeamsNumber, LeagueDescription) values ('" & _
Your_LeagueName & "','" & Your_StartingLane & "','" & Your_TeamsNumber & "','" & Your_LeagueDescription & "')"
'define the connection string, specify database
'driver and the location of database
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
connection.Open(sConnString)

'execute the SQL
connection.execute(sSQL)

response.write "Your league was successfully added to our database."
'Done. Close the connection object
connection.Close
Set connection = Nothing
%>
</td>
</tr>
<tr>
<!-- Author / Developer -->
<td colspan="6" align="center">
<img src="images/clear.gif" height="40" width="1" /><br />
Designed and Developed by: James Royce<br /> 2007, All rights reserved.
</td>
</tr>
</table>
<br /><br />
</td>
</tr>
</table>
</td>
</tr>
</table>
</body>
</html>


Being new to this stuff, I'm about ready to pull my hair out. Any ideas or help would be greatly appreciated.

Here is a link to a copy of my database if anyone would like to take a look at that. http://www.bowlingballsandmore.com/downloads/BBAM.zip

SSJ
03-02-2007, 06:46 AM
I think there should be a relationship problem

-SSJ

mack1604
03-02-2007, 06:50 AM
I kinda guessed that. By any chance did you take a look at the mdb file?

mack1604
03-02-2007, 06:51 AM
I feel like I'm in over my head here. I'm really new at this Access stuff. I do know that the relationships that I have are necessary for another part of this application to work.

SSJ
03-02-2007, 06:57 AM
This may help you..

support.microsoft.com/kb/306269

-SSJ

mack1604
03-02-2007, 07:01 AM
Sorry, no help. There are other portions of my application that are working just fine. It's just this particular page. And I'm pretty sure it's something that is missing or that I have to do differently with my table relationships in Access.

SSJ
03-02-2007, 07:06 AM
From other portions are the Insert queries working fine??

mack1604
03-02-2007, 07:11 AM
Yes they are working fine. I suppose it could be something I need to do in my SQL. ???
I should clarify. The user registration is working fine as well as populating a dropdown of the user's leagues.

SSJ
03-02-2007, 07:14 AM
Try to print your sql query and let me know what it is printing.

-SSJ

mack1604
03-02-2007, 07:20 AM
Are you saying that I should open up a query in design view and paste this:



sSQL = "INSERT INTO BSA_LEAGUES (LeagueName, StartingLane, TeamsNumber, LeagueDescription) values ('" & _
Your_LeagueName & "','" & Your_StartingLane & "','" & Your_TeamsNumber & "','" & Your_LeagueDescription & "')"

Sorry if this sounds stupid, but I'm really new at this.

SSJ
03-02-2007, 07:24 AM
After this statement write following code:

Response.write sSQL
Response.end

Tell me whats the output?

-SSJ

mack1604
03-02-2007, 07:26 AM
INSERT INTO BSA_LEAGUES (LeagueName, StartingLane, TeamsNumber, LeagueDescription) values ('TGIF (Simpson Cleanup Service)','1','20','Friday night mixed league, Friendship Lanes')

This is the info I'm trying to write to the database and it is correct.

Then again, maybe not totally right. Souldn't there be a reference to the user?

SSJ
03-02-2007, 07:31 AM
Is there any USERS table and any of the fields from this table related to that table?


-SSJ

mack1604
03-02-2007, 07:32 AM
There is the UserID

Have you, by chance, looked at the mdb file?

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

SSJ
03-02-2007, 07:34 AM
Give me the both table structures

-SSJ

mack1604
03-02-2007, 07:41 AM
USERS Table:

UserID (Primary Key)
Cust_UserName
Email
Entry_Date
Cust_Answer
Cust_Question
Cust_City
Cust_Country
Cust_Password
Cust_LastName
Cust_Name

BSA_LEAGUES Table:

LeagueID (Primary Key)
LeagueName
LeagueDescription
StartingLane
TeamsNumber
UserID

SSJ
03-02-2007, 07:45 AM
Here you have to Insert the UserID...

May I ask you why UserID is not there in your insert query?
If there any logic behind this?

-SSJ

mack1604
03-02-2007, 07:47 AM
Nope. It's just me not really knowing what I'm doing. I did try putting the "UserID" in the SQL but it did not do anything to correct the error. Plus I was thinking that the username cookie was providing the info needed to place the new record in the right place.

SSJ
03-02-2007, 07:49 AM
give me your new sql query...

mack1604
03-02-2007, 07:52 AM
Are you asking me to add the UserID into the SQL and run it?

SSJ
03-02-2007, 07:53 AM
yes

-SSJ

mack1604
03-02-2007, 07:55 AM
INSERT INTO BSA_LEAGUES (UserID, LeagueName, StartingLane, TeamsNumber, LeagueDescription) values ('','TGIF (Simpson Cleanup Service)','1','20','Friday night mixed league, Friendship Lanes')

SSJ
03-02-2007, 07:59 AM
UserID is allowing NULL values?
I think it shouldn't....


-SSJ

mack1604
03-02-2007, 08:01 AM
You're probably right, so what do I do?

SSJ
03-02-2007, 08:05 AM
I thnk you have UserName SO get UserID from Users table then Insert into this table.

-SSJ

mack1604
03-02-2007, 08:07 AM
In Access both the Users and the BSA_Leagues tables have "UserID" and they have a relationship of "one to many."

SSJ
03-02-2007, 08:11 AM
That's it.
What are you waiting for?

Get the userid and Insert into the table.
I will surely solve your problem

-SSJ

mack1604
03-02-2007, 08:11 AM
I may be onto something. I did this to my SQL



sSQL = "INSERT INTO BSA_LEAGUES (Cust_UserName = '" & Request.Cookies("UserName") & "' , LeagueName, StartingLane, TeamsNumber, LeagueDescription) values ('" & _
Your_UserName & "','" & Your_LeagueName & "','" & Your_StartingLane & "','" & Your_TeamsNumber & "','" & Your_LeagueDescription & "')"

ran it and got this:

INSERT INTO BSA_LEAGUES (Cust_UserName = 'whatshop' , LeagueName, StartingLane, TeamsNumber, LeagueDescription) values ('Cust_UserName','TGIF (Simpson Cleanup Service)','1','20','Friday night mixed league, Friendship Lanes')

Now I think I just have to figure out how to get the UserName value to read "whatshop", which is the user.

SSJ
03-02-2007, 08:18 AM
You have written wrong query:
write following:



sSQL = "INSERT INTO BSA_LEAGUES (UserID , LeagueName, StartingLane, TeamsNumber, LeagueDescription) values ("&userID&",'" & _
Your_UserName & "','" & Your_LeagueName & "','" & Your_StartingLane & "','" & Your_TeamsNumber

mack1604
03-02-2007, 08:18 AM
Maybe I'm not thinking right about this. I don't need the Cust_UserName to be written to the database. It should just make the new record write to the correct user.

Am I thinking right?

mack1604
03-02-2007, 08:20 AM
But where am I getting the UserId from? I don't think it's being passed from the form. The URL reads: http://www.bowlingballsandmore.com/bsa/bsa_league_added.asp?UserLoggedIn=whatshop

SSJ
03-02-2007, 08:25 AM
You have to found out the UserId from UserName you have.

Following query may help you:



sqlSelect = "Select UserID from users where UserName='"&Session("UserName")&"'"


-SSJ

mack1604
03-02-2007, 08:28 AM
Do I add this to my current SQL or can there be more that one?

SSJ
03-02-2007, 08:29 AM
I am going now..
back in 1 hour

-SSJ

mack1604
03-02-2007, 08:31 AM
Ok. Thanks for all of your help.

mack1604
03-02-2007, 09:16 AM
I've come with this to correct my error:



sSQL = "SELECT UserID FROM USERS WHERE Cust_UserName='"&Session("UserName")&"' AND INSERT INTO BSA_LEAGUES (UserID, LeagueName, StartingLane, TeamsNumber, LeagueDescription) values ('" & _
Your_UserID & "','" & Your_LeagueName & "','" & Your_StartingLane & "','" & Your_TeamsNumber & "','" & Your_LeagueDescription & "')"


However, I get this error now:

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'Cust_UserName='Yes' AND INSERT INTO BSA_LEAGUES (UserID, LeagueName, StartingLane, TeamsNumber, LeagueDescription) values ('UserID','TGIF (Simpson Cleanup Service)','1','20','Friday night mixed league, Friendship Lanes')'.


Any ideas as to what operator I am missing or am I going about this the wrong way? Thanks.

SSJ
03-02-2007, 09:26 AM
Your query is totally wrong.

Use Following:



set rs = Server.CreateObject("Adodb.RecordSet")
sSQL = "SELECT UserID FROM USERS WHERE Cust_UserName='"&Session("UserName")&"'
rs.open sSQL,connection,3
your_userID = rs("UserID")
rs.close
set rs = Nothing

sSQL = "INSERT INTO BSA_LEAGUES (UserID,LeagueName, StartingLane, TeamsNumber, LeagueDescription) values ("&your_UserID&",'" & _
Your_LeagueName & "','" & Your_StartingLane & "','" & Your_TeamsNumber & "','" & Your_LeagueDescription & "')"

mack1604
03-02-2007, 09:47 AM
How did "rs" get into this. It's an undefined variable. I'm trying to figure out how to define it now. Dim statement didn't work.

SSJ
03-02-2007, 10:04 AM
It is defined as a recordset dear..

mack1604
03-02-2007, 10:07 AM
This is what I get:

Microsoft VBScript runtime error '800a01f4'

Variable is undefined: 'rs'

SSJ
03-02-2007, 10:09 AM
Use Following:



Dim rs
set rs = Server.CreateObject("Adodb.RecordSet")
sSQL = "SELECT UserID FROM USERS WHERE Cust_UserName='"&Session("UserName")&"'
rs.open sSQL,connection,3
your_userID = rs("UserID")
rs.close
set rs = Nothing

sSQL = "INSERT INTO BSA_LEAGUES (UserID,LeagueName, StartingLane, TeamsNumber, LeagueDescription) values ("&your_UserID&",'" & _
Your_LeagueName & "','" & Your_StartingLane & "','" & Your_TeamsNumber & "','" & Your_LeagueDescription & "')"

mack1604
03-02-2007, 10:14 AM
This is what I get.

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

SSJ
03-02-2007, 10:23 AM
what is the line number?

mack1604
03-02-2007, 10:24 AM
The line number is 87

rs.open sSQL,connection,3

SSJ
03-02-2007, 10:35 AM
ok..
Let me play with it.

mack1604
03-02-2007, 10:37 AM
Thanks. I really appreciate it.

SSJ
03-02-2007, 10:49 AM
I have make the code working.
Use Following and let me know



<%@ Language=VBScript %>
<% Option Explicit %>
<%Response.Expires = -1 %>
<%Response.ExpiresAbsolute = Now() - 1 %>
<%Response.AddHeader "pragma", "no-cache" %>
<%Response.AddHeader "cache-control", "private" %>
<%Response.CacheControl = "no-cache" %>

<%
Response.Expires = -1000
Response.Buffer = True
Response.Clear
%>

<%Your_UserName = request.cookies("UserName")%>

<%
Response.Expires = -1000
Response.ExpiresAbsolute = Now() - 1
If request.cookies("UserName") = "" AND Session("UserName") = "" then
Response.Redirect ("unauthorized.asp")
Response.End
End If
%>

<%
Response.Expires = -1000
Response.ExpiresAbsolute = Now() - 1
If Session("UserName") = "" then
Response.Redirect ("unauthorized.asp")
Response.End
End If
%>
<html>
<head>
<title>Bowling Score Tracker</title>
<meta name="AUTHOR" content="James Royce">
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1">
<link rel="stylesheet" href="BSAstyle.css" type="text/css">
</head>
<body topmargin="0" leftmargin="0" rightmargin="0" bottommargin="0">
<table align="center" width="550" cellspacing="2" cellpadding="0" border="0" style="border-width: 1px; border-style: solid; color: #1b1b1b;" bgcolor="#000000" id="container">
<tr>
<td align="center">
<table align="center" width="550" border="0" cellspacing="0" cellpadding="0" bgcolor="2a3b83">
<tr>
<td valign="top" align="center" background="images/bsaheader.jpg" style="background-repeat: no-repeat">
<img src="images/clear.gif" height="141" width="550" />
<table width="420" height="420" border="0" cellspacing="0" cellpadding="6" background="images/form_bg.jpg" style="border-style: solid;border-width: 1px;border-color: #ffffff;">
<tr>
<td colspan="6" align="left">
<ul><li><a href="bsa_add_league.asp?UserLoggedIn=<% =Your_UserName %>" target="_top">Add Another League</a>
<li><a href="bsa_default.asp?UserLoggedIn=<% =Your_UserName %>" target="_top">Enter Scores</a>
<li><a href="bsa.asp?UserLoggedIn=<% =Your_UserName %>" target="_top">View Stats.</a>
<li><a href="information.asp?UserLoggedIn=<% =Your_UserName %>" target="_top">My Account</a>
<li><a href="loginclose.asp" target="_top">Log Out</a></li>
</ul>
</td>
</tr>
<tr>
<td align="center" style="padding-left:6px;padding-top:6px;padding-right:6px;padding-bottom:6px;">
<%
'declare your variables
dim Your_UserName
Your_UserName = ("UserName")
dim Your_LeagueName
Your_LeagueName = ("LeagueName")
dim Your_StartingLane
Your_StartingLane = ("StartingLane")
dim Your_TeamsNumber
Your_TeamsNumber = ("TeamsNumber")
dim Your_LeagueDescription
Your_LeagueDescription = ("LeagueDescription")
Dim sConnString, connection, sSQL
'Receiving values from Form, assign the values entered to variables
Your_LeagueName = Request.Form("LeagueName")
Your_StartingLane = Request.Form("StartingLane")
Your_TeamsNumber = Request.Form("TeamsNumber")
Your_LeagueDescription =Request.Form("LeagueDescription")

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
connection.Open(sConnString)

'declare SQL statement that will query the database
Dim rs,sqlInsert,your_userID
sqlInsert = "Select UserID from Users where Cust_UserName='"&session("userName")&"'"
Set rs = server.CreateObject("Adodb.Recordset")
rs.open sqlInsert,connection,3
your_userID=rs("UserID")
rs.close
set rs=Nothing

sSQL = "INSERT INTO BSA_LEAGUES (UserID,LeagueName, StartingLane, TeamsNumber, LeagueDescription) values ("&your_userID&",'" & _
Your_LeagueName & "'," & Your_StartingLane & "," & Your_TeamsNumber & ",'" & Your_LeagueDescription & "')"
'define the connection string, specify database
'driver and the location of database
'execute the SQL
connection.execute(sSQL)

response.write "Your league was successfully added to our database."
'Done. Close the connection object
connection.Close
Set connection = Nothing
%>
</td>
</tr>
<tr>
<!-- Author / Developer -->
<td colspan="6" align="center">
<img src="images/clear.gif" height="40" width="1" /><br />
Designed and Developed by: James Royce<br /> 2007, All rights reserved.
</td>
</tr>
</table>
<br /><br />
</td>
</tr>
</table>
</td>
</tr>
</table>
</body>
</html>

mack1604
03-02-2007, 10:58 AM
This is what I get:

ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/bsa/bsa_league_added.asp, line 94

SSJ
03-02-2007, 11:01 AM
Hmmmmmmm...........:)

I have tested this code on my server here.
It is working fine here...

Try to Print that statement:
Response.write sqlInsert
response.end

Let me know the results

mack1604
03-02-2007, 11:06 AM
I get this:

Select UserID from Users where Cust_UserName='Yes'

SSJ
03-02-2007, 11:11 AM
Sorry, It was my mistake.
I think you have stored username in session("UserName").

Use the following sqlInsert statement:



sqlInsert = "Select UserID from Users where Cust_UserName='"&Your_UserName&"'"


Hope all things are okay now...
Let me know if they are

mack1604
03-02-2007, 11:19 AM
I get this error:

ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/bsa/bsa_league_added.asp, line 95


I ran the sqlInsert again and this is it:

Select UserID from Users where Cust_UserName='UserName'

I did this while logged in as "whatshop"

SSJ
03-02-2007, 11:22 AM
Tell me one thing where are you storing logged on userName?

mack1604
03-02-2007, 11:28 AM
It's in the Users table.

SSJ
03-02-2007, 11:35 AM
Store login name in Session or Cookie and then use the appropiate sql query

mack1604
03-02-2007, 11:38 AM
It's in a cookie.

SSJ
03-02-2007, 11:42 AM
Try Following Query:



sqlInsert = "Select UserID from Users where Cust_UserName='"&request.cookies("UserName")&"'"

mack1604
03-02-2007, 11:42 AM
I did this and it works.


sqlInsert = "Select UserID from Users where Cust_UserName='"&Request.Cookies("UserName")&"'"

mack1604
03-02-2007, 11:46 AM
I want to thank you once again for all of your help and patience. Well, it's now 5:43am here and I've been up all night, so I think I need to get some sleep. Thanks again.

SSJ
03-02-2007, 11:46 AM
Hope now everything is ok and working perfectly..

SSJ
03-02-2007, 11:47 AM
If you want then you can nominate me...

See the upper links of the page

mack1604
03-02-2007, 11:48 AM
I tried to give you more reputation point, but the message said that I had to spread it around. That's bull since you are the one who helped me through this. One more time! Thanks again.

And I will nominate you.

mack1604
03-02-2007, 11:50 AM
I just tried to nominate you but the thread is closed. If they open it up again I'll be sure to put you in.

SSJ
03-02-2007, 11:51 AM
ok fine.
Good Night.!!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum