Hi Imar,

The insert code worked perfectly - however I have another dilemma on that page. I would like to also update a record on the same page as the insert - is this possible? I have found that I cannot seem to get it to work. I am getting error like: data type mismatch and sometime file locked by server. Here is a copy of the update code if that will help:

<%

if(Request.Form("upn") <> "") then Comupdate__varupn = Request.Form("upn")

if(Request.Form("upna") <> "") then Comupdate__varupna = Request.Form("upna")

if(Request.Form("date_drawn") <> "") then Comupdate__vardate_drawn = Request.Form("date_drawn")

if(Request.Form("date_drawn") <> "") then Comupdate__vardate_stored = Request.Form("date_drawn")

if(Request.Form("person_storing") <> "") then Comupdate__varperson_storing = Request.Form("person_storing")

if(Request.Form("total_vials") <> "") then Comupdate__vartotal_vials = Request.Form("total_vials")

if(Request.Form("freezing_Method") <> "") then Comupdate__varfreezing_method = Request.Form("freezing_Method")

if(Request.Form("comments") <> "") then Comupdate__varcomments = Request.Form("comments")

if(Request.Cookies("freezer") <> "") then Comupdate__varnamefreezer = Request.Cookies("freezer")

if(Request.Cookies("tower") <> "") then Comupdate__varnametower = Request.Cookies("tower")

if(Request.Cookies("box") <> "") then Comupdate__varnamebox = Request.Cookies("box")

if(Request.Cookies("location") <> "") then Comupdate__varnameloc = Request.Cookies("location")

%>
<%
set Comupdate = Server.CreateObject("ADODB.Command")
Comupdate.ActiveConnection = MM_sample_tracking_STRING
Comupdate.CommandText = "UPDATE tblsamples SET UPN= '" + Replace(Comupdate__varupn, "'", "''") + "' and UPNA='" + Replace(Comupdate__varupna, "'", "''") + "' and Date_Drawn='" + Replace(Comupdate__vardate_drawn, "'", "''") + "' and Date_stored='" + Replace(Comupdate__vardate_stored, "'", "''") + "' and Person_Storing='" + Replace(Comupdate__varperson_storing, "'", "''") + "' and Total_Vials= '" + Replace(Comupdate__vartotal_vials, "'", "''") + "' and Freezing_Method= '" + Replace(Comupdate__varfreezing_method, "'", "''") + "' and Comments='" + Replace(Comupdate__varcomments, "'", "''") + "' WHERE Freezer='" + Replace(Comupdate__varnamefreezer, "'", "''") + "' and Tower='" + Replace(Comupdate__varnametower, "'", "''") + "' and Box='" + Replace(Comupdate__varnamebox, "'", "''") + "' and Location='" + Replace(Comupdate__varnameloc, "'", "''") + "' "
Comupdate.CommandType = 1
Comupdate.CommandTimeout = 0
Comupdate.Prepared = true
Comupdate.Execute()

%>

As far as the code is concerned it is nearly identical to the insert statement - but I just seem to get a bunch of errors. I know Dreamweaver doesn't like multiple insert/update - but I have done it in the past.

As ever - I appreciate any input.

Chris

Can you post the exact error message?

And can you execute the following statement right before Comupdate.Execute and post the result of that here too? Otherwise it's a lot of guessing which takes a lot of time and usually doesn't give any good results....

Response.Write("Sql statement is " &
Comupdate.CommandText)

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Hi Imar,

Here is the output - though sometimes I do get the file is in use by server - though I can't seem to recreate that. All the data looks right (to me) in the sql output. thanks for looking.

Sql statement is UPDATE tblsamples SET UPN= '004' and UPNA='E' and Date_Drawn='06/17/2005' and Date_stored='06/17/2005' and Person_Storing='name' and Total_Vials= '5' and Freezing_Method= 'Control Rate' and Comments='' WHERE Freezer='Cryo-3' and Tower='01' and Box='A' and Location='012'

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/sample_tracking/editprocess.asp, line 101

Ah, I see now.

And is not used in an UPDATE statement between column names; that's only used in a WHERE clause for example.

A basic UPDATE statement looks like this:

UPDATE TableName SET Column1 = SomeValue, Column2 = 'SomeOtherValue', Column3 = YetAnotherValue WHERE SomeColumn = SomeValue

So, drop the AND keywords and use commas instead.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: My Gift To You by KoRn (Track 14 from the album: Follow The Leader) What's This?

Thanks Imar!

I can't believe how novice of a mistake that was! Works perfectly now!


Are you up for one last challenge?

The select multiple table I have in my form specifies what locations to put the data into - I need this to be generated dynamically so I don't try to insert data into a field which already has a record. This is how I tried to do it:

I created a recordset (rssamplelocation) that list all the 'locations' that have been filled with data based on freezer, tower, box. This recordset will show for example 001, 002, 003, 004, 005, 006, etc..

I then created another recordset (rslocation) from a tbl I have that has all the locations (001-100) I thought I could make it work by essentially saying show all locations from tbllocation <> rssamplelocation. It seems that I can't get code it correctly though - when I create the dynamic field it always shows 99 of the 100 records (it omits whatever location I selected to get to the page)

Here is the code for both recordsets - if anyone has a better way of doing this I would happy to try it!

<%
Dim rssampletrack__varnametower
rssampletrack__varnametower = "01"
If (Request.Cookies("tower") <> "") Then
rssampletrack__varnametower = Request.Cookies("tower")
End If
%>
<%
Dim rssampletrack__varnamebox
rssampletrack__varnamebox = "a"
If (Request.Cookies("box") <> "") Then
rssampletrack__varnamebox = Request.Cookies("box")
End If
%>
<%
Dim rssampletrack
Dim rssampletrack_numRows

Set rssampletrack = Server.CreateObject("ADODB.Recordset")
rssampletrack.ActiveConnection = MM_sample_tracking_STRING
rssampletrack.Source = "SELECT Location FROM tblsamples WHERE Freezer = '" + Replace(rssampletrack__varnamefreezer, "'", "''") + "' AND Tower='" + Replace(rssampletrack__varnametower, "'", "''") + "' AND Box='" + Replace(rssampletrack__varnamebox, "'", "''") + "'"
rssampletrack.CursorType = 0
rssampletrack.CursorLocation = 2
rssampletrack.LockType = 1
rssampletrack.Open()

rssampletrack_numRows = 0
%>

and the other recordset:


<%
Dim rslocation__MMColParam
rslocation__MMColParam = "rssampletrack"
If ((rssampletrack.Fields.Item("Location").Value) <> "") Then
rslocation__MMColParam = (rssampletrack.Fields.Item("Location").Value)
End If
%>
<%
Dim rslocation
Dim rslocation_numRows

Set rslocation = Server.CreateObject("ADODB.Recordset")
rslocation.ActiveConnection = MM_sample_tracking_STRING
rslocation.Source = "SELECT Location FROM tbllocation WHERE Location <> '" + Replace(rslocation__MMColParam, "'", "''") + "'"
rslocation.CursorType = 0
rslocation.CursorLocation = 2
rslocation.LockType = 1
rslocation.Open()

rslocation_numRows = 0
%>

Once again, I am confused by your requirements. Can you, please, explain in plain english what it is you want the app to do? Every time you post a question you sort of assume we're in the same business as you are (or your wife) and know all about boxes, freezers etc. We don't.... ;)
Are you trying to find an empty spot / location? Are you trying to list locations that are not used for the current freezer, box and tower? I have absolutely no idea.

What always helps me in working out these puzzles is describing what I want. Simply put down the logic on paper. Just describe the steps in plain English. The next step then is to take those descriptions and see if you can get parts of it to work in Access directly. Once you have your basic query logic, you can move them to your ASP pages and let Dreamweaver take over.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Daydreaming by Massive Attack (Track 7 from the album: Blue Lines) What's This?

Imar - thanks for your patience!

In plain english - I am trying to list locations that are not used for the current freezer, box and tower. So, if I selected a freezer, tower, box that was empty it would return all 100 locations (001-100) and if I selected a freezer, tower, box that had data in it - it would only retunr locations were there wasn't any data.

I have a table that has just the locations 001-100 listed in it.

The table I store the all the sample data in only contain locations that are used.

Hope that helps! Once again thanks for all the help,

Chris

I am still not sure I understand what you want, but it sounds like you can use an IN clause in your queries.

Something like this:

SELECT Id FROM SomeTable WHERE LocationId NOT IN (SELECT Id FROM Location WHERE SomeColumn = SomeValue)

In this example, the sub query (SELECT Id FROM Location...) returns a list with Ids that match some criteria. The outer query then uses that list to retrieve all the records whose Id doesn't match those in the list return by the sub query.

A trivial example, but I think you'll understand what I mean. You can use the IN clause, wrap some hocus pocus around it involving boxes, freezers, locations and what more, and it might work... ;)

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Three by Massive Attack (Track 3 from the album: Protection) What's This?

Hi Imar,

You are right on target! I will play around with the code and see if I can get it to work! Incidently I too am listening to Massive Attack as I work (Mezzanine) I don't have winamp so I can't run your cool sig program.

As ever - thanks for all of your help!


Chris

Yeah, great album. It's up next in my WinAmp track list.... ;)

Good luck with your IN query....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Sly by Massive Attack (Track 8 from the album: Protection) What's This?