Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Dreamweaver (all versions) section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 20th, 2005, 12:33 PM
Authorized User
 
Join Date: May 2005
Location: , WI, .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default Insert Issue revisited

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

Reply With Quote
  #2 (permalink)  
Old June 20th, 2005, 01:08 PM
Imar's Avatar
Wrox Author
Points: 71,293, Level: 100
Points: 71,293, Level: 100 Points: 71,293, Level: 100 Points: 71,293, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,969
Thanks: 79
Thanked 1,560 Times in 1,537 Posts
Default

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.
Reply With Quote
  #3 (permalink)  
Old June 20th, 2005, 03:20 PM
Authorized User
 
Join Date: May 2005
Location: , WI, .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #4 (permalink)  
Old June 20th, 2005, 03:26 PM
Imar's Avatar
Wrox Author
Points: 71,293, Level: 100
Points: 71,293, Level: 100 Points: 71,293, Level: 100 Points: 71,293, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,969
Thanks: 79
Thanked 1,560 Times in 1,537 Posts
Default

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?
Reply With Quote
  #5 (permalink)  
Old June 20th, 2005, 03:55 PM
Authorized User
 
Join Date: May 2005
Location: , WI, .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
%>


Reply With Quote
  #6 (permalink)  
Old June 20th, 2005, 04:12 PM
Imar's Avatar
Wrox Author
Points: 71,293, Level: 100
Points: 71,293, Level: 100 Points: 71,293, Level: 100 Points: 71,293, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,969
Thanks: 79
Thanked 1,560 Times in 1,537 Posts
Default

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?
Reply With Quote
  #7 (permalink)  
Old June 20th, 2005, 04:27 PM
Authorized User
 
Join Date: May 2005
Location: , WI, .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #8 (permalink)  
Old June 20th, 2005, 04:42 PM
Imar's Avatar
Wrox Author
Points: 71,293, Level: 100
Points: 71,293, Level: 100 Points: 71,293, Level: 100 Points: 71,293, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,969
Thanks: 79
Thanked 1,560 Times in 1,537 Posts
Default

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?
Reply With Quote
  #9 (permalink)  
Old June 20th, 2005, 04:55 PM
Authorized User
 
Join Date: May 2005
Location: , WI, .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #10 (permalink)  
Old June 20th, 2005, 05:05 PM
Imar's Avatar
Wrox Author
Points: 71,293, Level: 100
Points: 71,293, Level: 100 Points: 71,293, Level: 100 Points: 71,293, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 16,969
Thanks: 79
Thanked 1,560 Times in 1,537 Posts
Default

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?
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sql Insert -- date format issue feets Access VBA 2 June 12th, 2007 01:11 PM
Insert Issue into Acces DB brawny4 Visual Basic 2005 Basics 2 February 21st, 2007 08:43 PM
Ch10 - issue with insert in log table Cata BOOK: Professional SQL Server 2005 Administration ISBN: 0-470-05520-0 0 February 15th, 2007 05:17 PM
Insert issue? cedwards Dreamweaver (all versions) 14 June 17th, 2005 02:20 AM



All times are GMT -4. The time now is 11:51 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.