How can I "add new" if "foo" doesn't exist

[HarryGreiner]

I'm still working on this course database and just about have everything sorted - I think this is my final problem ....

I have a live and a staging database. Live database feeds course information to a form for editing. Form is sent to asp page that updates information on the staging database. However, users also have the facility to add a new course to the staging database through another form.

I then access another form that takes information from staging database and I (after checking data) upload to Live database ... with me so far?

Most of the courses are existing already and all that works fine but if user adds a new course to staging database I can't upload the data to the Live database because it doesn't have an existing "id" (my primary key).

This is the code I'm using to upload info to the live database

<%
Response.Buffer = true
dim cnn,rst
set cnn = Server.CreateObject("ADODB.Connection")
set rst = Server.CreateObject("ADODB.RecordSet")
cnn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../scripts2/courses2005/courses.mdb")
sqltext = "SELECT * FROM courselist WHERE id = " & Request.Form("id")
rst.Open sqltext,cnn,3,3

rst("title") = Request.Form("title")
rst("qualification") = Request.Form("qualification")
rst("level") = Request.Form("level")
rst("school") = Request.Form("school")
rst("attendance") = Request.Form("attendance")
rst("lastsfor") = Request.Form("lastsfor")
rst("coursefor") = Request.Form("coursefor")
rst("coursecontent") = Request.Form("coursecontent")
rst("entryrequirements") = Request.Form("entryrequirements")
rst("collegetime") = Request.Form("collegetime")
rst("trips") = Request.Form("trips")
rst("assessments") = Request.Form("assessments")
rst("progression") = Request.Form("progression")
rst("fees") = Request.Form("fees")
rst("costs") = Request.Form("costs")
rst("apply") = Request.Form("apply")
rst("contact") = Request.Form("contact")
rst.update
rst.Close
Set rst= Nothing
cnn.close
Set cnn= Nothing
%>

Now, is it possible for me to say "if there is no matching id in the Live database then addNew record?"

Thanks for any answers you can give - I'm trying to learn this stuff as I go along but it doesn't come easy!

[TheShaner]

Well, you're using a SELECT to open up from your Live database a recordset with an ID from the form that would not be in your Live database because it's a new course and thus non-existant in your Live database, correct? If I'm on the right page, then when you try to perform that script, you're openning an empty recordset and trying to update data, which will just error out.

When inserting a new course, you want to insert into the Live database and not do any kind of SELECT from it.

So:
sqltext = "INSERT INTO courselist VALUES ('" & Request.Form("id") & "', '" & Request.Form("title") & "', '" & Request.Form("qualification") & "', '" & Request.Form("level") & "', '" & Request.Form("school") & "', '" & Request.Form("attendance") & "', '" & Request.Form("lastsfor") & "', '" & Request.Form("coursefor") & "', '" & Request.Form("coursecontent") & "', '" & Request.Form("entryrequirements") & "', '" & Request.Form("collegetime") & "', '" & Request.Form("trips") & "', '" & Request.Form("assessments") & "', '" & Request.Form("progression") & "', '" & Request.Form("fees") & "', '" & Request.Form("costs") & "', '" & Request.Form("apply") & "', '" & Request.Form("contact") & "')"
cnn.Execute sqltext
Now the above isn't the best way. I just wanted to give you an idea. The above also assumes every field is text and thus surrounds each value in single quotes. If it's a number, then you won't have the quotes. This method also does not have the protection from SQL injection, so you'll want to read up on that. The above also does not require a recordset, as you'll notice that it only requires the connection to execute the query.

The other method is to do what you were doing but also include the red:
sqltext = "SELECT * FROM courselist"
rst.Open sqltext,cnn,3,3

rst.AddNew
rst("id") = Request.Form("id")
rst("title") = Request.Form("title")
rst("qualification") = Request.Form("qualification")
rst("level") = Request.Form("level")
rst("school") = Request.Form("school")
rst("attendance") = Request.Form("attendance")
rst("lastsfor") = Request.Form("lastsfor")
rst("coursefor") = Request.Form("coursefor")
rst("coursecontent") = Request.Form("coursecontent")
rst("entryrequirements") = Request.Form("entryrequirements")
rst("collegetime") = Request.Form("collegetime")
rst("trips") = Request.Form("trips")
rst("assessments") = Request.Form("assessments")
rst("progression") = Request.Form("progression")
rst("fees") = Request.Form("fees")
rst("costs") = Request.Form("costs")
rst("apply") = Request.Form("apply")
rst("contact") = Request.Form("contact")
rst.Update
This way may be the easiest for you.

Hopefully this helps. If you want someone to be able to edit a course that is in the Live database, then you first open up the Live database to see if a record with that ID exists, if so, do like you were doing. If not, then you do one of the above methods.

-Shane

[BarrMan]

Use the code function please for future codes.

<%
Response.Buffer = true
dim cnn,rst
set cnn = Server.CreateObject("ADODB.Connection")
set rst = Server.CreateObject("ADODB.RecordSet")
cnn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../scripts2/courses2005/courses.mdb")
sqltext = "SELECT * FROM courselist WHERE id = " & Request.Form("id")
rst.Open sqltext,cnn,3,3

If rs.eof then
cnn.execute "CREATE TABLE tablename([value1] type,[value2] type, [value3] type)"
Else

rst("title") = Request.Form("title")
rst("qualification") = Request.Form("qualification")
rst("level") = Request.Form("level")
rst("school") = Request.Form("school")
rst("attendance") = Request.Form("attendance")
rst("lastsfor") = Request.Form("lastsfor")
rst("coursefor") = Request.Form("coursefor")
rst("coursecontent") = Request.Form("coursecontent")
rst("entryrequirements") = Request.Form("entryrequirements")
rst("collegetime") = Request.Form("collegetime")
rst("trips") = Request.Form("trips")
rst("assessments") = Request.Form("assessments")
rst("progression") = Request.Form("progression")
rst("fees") = Request.Form("fees")
rst("costs") = Request.Form("costs")
rst("apply") = Request.Form("apply")
rst("contact") = Request.Form("contact")
rst.update
End If
rst.Close
Set rst= Nothing
cnn.close
Set cnn= Nothing
%>

For more information about the sql code for creating a new table on database please visit: http://www.w3schools.com/sql/sql_create.asp

[TheShaner]

I believe he just wants to add a new record; not create an entirely new table. He just wants to add a course that is not in the Live database.

-Shane

[BarrMan]

Oops >< :) in that case:

cnn.execute "INSERT INTO foo([recordname1],[recordname2],[recordname3]) VALUES('" & string & "'," & number & ",#" & date& "#)"

[HarryGreiner]

Sorry - I'm a bit slow on the uptake when it comes to coding (you might have guessed) ....

Can I use and "if" "else" here like this

<%
Response.Buffer = true
dim cnn,rst
set cnn = Server.CreateObject("ADODB.Connection")
set rst = Server.CreateObject("ADODB.RecordSet")
cnn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../scripts2/courses2005/courses.mdb")
sqltext = "SELECT * FROM courselist"
rst.Open sqltext,cnn,3,3

if rs.eof then

rst.AddNew
rst("title") = Request.Form("title")
rst("qualification") = Request.Form("qualification")
rst("level") = Request.Form("level")
rst("school") = Request.Form("school")
rst("attendance") = Request.Form("attendance")
rst("lastsfor") = Request.Form("lastsfor")
rst("coursefor") = Request.Form("coursefor")
rst("coursecontent") = Request.Form("coursecontent")
rst("entryrequirements") = Request.Form("entryrequirements")
rst("collegetime") = Request.Form("collegetime")
rst("trips") = Request.Form("trips")
rst("assessments") = Request.Form("assessments")
rst("progression") = Request.Form("progression")
rst("fees") = Request.Form("fees")
rst("costs") = Request.Form("costs")
rst("apply") = Request.Form("apply")
rst("contact") = Request.Form("contact")
rst.Update
end if

else
rst("title") = Request.Form("title")
rst("qualification") = Request.Form("qualification")
rst("level") = Request.Form("level")
rst("school") = Request.Form("school")
rst("attendance") = Request.Form("attendance")
rst("lastsfor") = Request.Form("lastsfor")
rst("coursefor") = Request.Form("coursefor")
rst("coursecontent") = Request.Form("coursecontent")
rst("entryrequirements") = Request.Form("entryrequirements")
rst("collegetime") = Request.Form("collegetime")
rst("trips") = Request.Form("trips")
rst("assessments") = Request.Form("assessments")
rst("progression") = Request.Form("progression")
rst("fees") = Request.Form("fees")
rst("costs") = Request.Form("costs")
rst("apply") = Request.Form("apply")
rst("contact") = Request.Form("contact")
rst.update
rst.Close
Set rst= Nothing
cnn.close
Set cnn= Nothing
%>

With your second example, Shane, I know that works (I use it for yet another form direct from me so I don't need the staging server) but I don't use the "id" as it's autonumber.

Thanks again both of you.

[TheShaner]

That will work perfect, although I believe you need rst.Edit before you begin editting the fields after your Else (at least it works that way in Visual Basic, hehe).

-Shane

[HarryGreiner]

Damm.
Thanks Shane (again).

I've put the code in like this





<%
Response.Buffer = true
dim cnn,rst
set cnn = Server.CreateObject("ADODB.Connection")
set rst = Server.CreateObject("ADODB.RecordSet")
cnn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../scripts2/courses2005/courses.mdb")
sqltext = "SELECT * FROM courselist"
rst.Open sqltext,cnn,3,3

if rs.eof then

rst.AddNew
rst("title") = Request.Form("title")
rst("qualification") = Request.Form("qualification")
rst("level") = Request.Form("level")
rst("school") = Request.Form("school")
rst("attendance") = Request.Form("attendance")
rst("lastsfor") = Request.Form("lastsfor")
rst("coursefor") = Request.Form("coursefor")
rst("coursecontent") = Request.Form("coursecontent")
rst("entryrequirements") = Request.Form("entryrequirements")
rst("collegetime") = Request.Form("collegetime")
rst("trips") = Request.Form("trips")
rst("assessments") = Request.Form("assessments")
rst("progression") = Request.Form("progression")
rst("fees") = Request.Form("fees")
rst("costs") = Request.Form("costs")
rst("apply") = Request.Form("apply")
rst("contact") = Request.Form("contact")
rst.Update
end if

else
rst.Edit
rst("title") = Request.Form("title")
rst("qualification") = Request.Form("qualification")
rst("level") = Request.Form("level")
rst("school") = Request.Form("school")
rst("attendance") = Request.Form("attendance")
rst("lastsfor") = Request.Form("lastsfor")
rst("coursefor") = Request.Form("coursefor")
rst("coursecontent") = Request.Form("coursecontent")
rst("entryrequirements") = Request.Form("entryrequirements")
rst("collegetime") = Request.Form("collegetime")
rst("trips") = Request.Form("trips")
rst("assessments") = Request.Form("assessments")
rst("progression") = Request.Form("progression")
rst("fees") = Request.Form("fees")
rst("costs") = Request.Form("costs")
rst("apply") = Request.Form("apply")
rst("contact") = Request.Form("contact")
rst.update
rst.Close
Set rst= Nothing
cnn.close
Set cnn= Nothing
%>

But I just get an HTTP 500 error. Can you see what I've cocked up?
Cheers.

[TheShaner]

Yeah, you have rs.EOF instead of rst.EOF. Also, you have an End If after your first rst.Update. The End If should be after the second one, so that your logic goes:
If rst.EOF Then
...
Else
...
End If

Also, in the future, so that errors show, do this:
IE -> Tools -> Internet Options -> Advanced -> Uncheck Show friendly HTTP error messages

-Shane

[HarryGreiner]

Shaner - one day I'm coming to your house and taking you out for some serious beer!

It works .... but, if entering a new course it just replaces the course at "id=1" i.e. the first course in the database.

If I use the code

<%
Response.Buffer = true
dim cnn,rst
set cnn = Server.CreateObject("ADODB.Connection")
set rst = Server.CreateObject("ADODB.RecordSet")
cnn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../scripts2/courses2005/courses.mdb")
sqltext = "SELECT * FROM courselist"
rst.Open sqltext,cnn,3,3
rst.AddNew
rst("title") = Request.Form("title")
rst("qualification") = Request.Form("qualification")
rst("level") = Request.Form("level")
rst("school") = Request.Form("school")
rst("attendance") = Request.Form("attendance")
rst("lastsfor") = Request.Form("lastsfor")
rst("coursefor") = Request.Form("coursefor")
rst("coursecontent") = Request.Form("coursecontent")
rst("entryrequirements") = Request.Form("entryrequirements")
rst("collegetime") = Request.Form("collegetime")
rst("trips") = Request.Form("trips")
rst("assessments") = Request.Form("assessments")
rst("progression") = Request.Form("progression")
rst("fees") = Request.Form("fees")
rst("costs") = Request.Form("costs")
rst("apply") = Request.Form("apply")
rst("contact") = Request.Form("contact")
rst.update
rst.Close
Set rst= Nothing
cnn.close
Set cnn= Nothing
%>

by itself, everything's fine. Creates a new record with a new (autonumber) id. But using the (corrected as per your last post, thanks!) code above it just replaces id=1.

Any ideas?

[TheShaner]

Oops, sorry. It's because the recordset was containing records due to not including the WHERE clause that checked for that particular id. So it was just editing the very first record since it was going to the Else code. I cleaned up the look of the code, because I'm a little anal about formatting code, lol.

Try this:
<%
Response.Buffer = True
Dim cnn 'Connection
Dim rst 'Recordset
Dim sqltext 'SQL query
Set cnn = Server.CreateObject("ADODB.Connection")
Set rst = Server.CreateObject("ADODB.RecordSet")
cnn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../scripts2/courses2005/courses.mdb")
sqltext = "SELECT * FROM courselist WHERE id = " & Request.Form("id")
rst.Open sqltext, cnn, 3, 3

If rst.BOF Then 'Or you can use rst.EOF, but BOF is just my preference for this

rst.AddNew
rst("title") = Request.Form("title")
rst("qualification") = Request.Form("qualification")
rst("level") = Request.Form("level")
rst("school") = Request.Form("school")
rst("attendance") = Request.Form("attendance")
rst("lastsfor") = Request.Form("lastsfor")
rst("coursefor") = Request.Form("coursefor")
rst("coursecontent") = Request.Form("coursecontent")
rst("entryrequirements") = Request.Form("entryrequirements")
rst("collegetime") = Request.Form("collegetime")
rst("trips") = Request.Form("trips")
rst("assessments") = Request.Form("assessments")
rst("progression") = Request.Form("progression")
rst("fees") = Request.Form("fees")
rst("costs") = Request.Form("costs")
rst("apply") = Request.Form("apply")
rst("contact") = Request.Form("contact")
rst.Update

Else

rst.Edit
rst("title") = Request.Form("title")
rst("qualification") = Request.Form("qualification")
rst("level") = Request.Form("level")
rst("school") = Request.Form("school")
rst("attendance") = Request.Form("attendance")
rst("lastsfor") = Request.Form("lastsfor")
rst("coursefor") = Request.Form("coursefor")
rst("coursecontent") = Request.Form("coursecontent")
rst("entryrequirements") = Request.Form("entryrequirements")
rst("collegetime") = Request.Form("collegetime")
rst("trips") = Request.Form("trips")
rst("assessments") = Request.Form("assessments")
rst("progression") = Request.Form("progression")
rst("fees") = Request.Form("fees")
rst("costs") = Request.Form("costs")
rst("apply") = Request.Form("apply")
rst("contact") = Request.Form("contact")
rst.Update

End If

rst.Close
Set rst= Nothing
cnn.Close
Set cnn= Nothing
%>
-Shane

[HarryGreiner]

Right, what are you drinking?

Thanks Shaner - excellent fixes and explanations my newbie brain can cope with and assimilate. I've learned a lot in the last week - a good deal down to your help.

*salutes and exits*
(until next time)

[TheShaner]

Glad I could help and goodluck with the rest of your project!

As for a beer, as long as it's not dark and thick, I'm good to go, lol.

-Shane