Monday, March 19, 2012
.Update creates new record but all fields null
I am posting the data from a form to the same page and an if /then statement catches the flag in the URL and runs the update script below. All the field names are correct.
if request.QueryString("add")<> "" then
Dim rsUpdateEntry
Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset")
rsUpdateEntry.Open "SELECT * from generic_country_info" , oConn, 2, 3
rsUpdateEntry.AddNew
rsUpdateEntry.Fields("title1") = Request.Form("title1")
rsUpdateEntry.Fields("body1") = Request.Form("body1")
rsUpdateEntry.Fields("title2") = Request.Form("title2")
rsUpdateEntry.Fields("body2") = Request.Form("body2")
rsUpdateEntry.Fields("title3") = Request.Form("title3")
rsUpdateEntry.Fields("body3") = Request.Form("body3")
rsUpdateEntry.Fields("title4") = Request.Form("title4")
rsUpdateEntry.Fields("body4") = Request.Form("body4")
rsUpdateEntry.Fields("title5") = Request.Form("title5")
rsUpdateEntry.Fields("body5") = Request.Form("body5")
rsUpdateEntry.Fields("image1") = Request.Form("attach1")
rsUpdateEntry.Fields("image2") = Request.Form("attach2")
rsUpdateEntry.Fields("image3") = Request.Form("attach3")
rsUpdateEntry.Fields("image4") = Request.Form("attach4")
rsUpdateEntry.Fields("image5") = Request.Form("attach5")
rsUpdateEntry.Fields("country") = Request.Form("country")
rsUpdateEntry.Fields("dest_url") = Request.Form("dest_url")
rsUpdateEntry.Update
rsUpdateEntry.Close
Set rsUpdateEntry = Nothing
end if
Thanks
MarkAt the risk of offending you, may I suggest that you instead consider a stored procedure? At first it require a bit more effort and thought, but in the long run, stored procedures are a sensible way to handle database activity (including updates, inserts, selects and deletes).
As for the code you've written, are you sure that the .Add is in the right place? It looks to me (though I don't write my code this way) like it should come after you have assigned all of the values.
Regards,
hmscott
Friday, February 24, 2012
.dbf file import (duplicate Field Names)
I am importing a file creating by an application which exports the file into .dbf format. Very unfortunately, this .dbf file can have fields with IDENTICAL column_names. Utilizing ActiveX, I create an ado connection to the .dbf file using a visual foxpro drver. However, and not unexpectantly, I can not do the 'select *' from the file if there are duplicate names.
Can anyone make recommendations here that might help?
Oh, this is SQL200 in case that impacts what you might advise!!!!
Anyone out there from MSFT want to make some suggestions?|||Hi Ellen,
I find it surprising that the DBF file can have duplicate field names. What application created it? Is it a FoxPro DBF or a DbaseIV or Paradox DBF?
I assume you are able to read the table structure with something other than SQL Server, is that correct?
Are you using ODBC or OLE DB? The latest FoxPro and Visual FoxPro OLE DB data provider is downloadable from msdn.microsoft.com/vfoxpro/downloads/updates .
Sunday, February 19, 2012
...sorry
Today I am working with SQLServer. I would want to compare two dateTime fields for to determine if two or more customers are connected in the same moment into my applications...
For example:
Begin date of connections:
16/01/2007 16.19.59
end date of connection
16/01/2007 16.25.23 for customers 01
Begin date of connections:
16/01/2007 16.20.59
end date of connection
16/01/2007 16.24.23 for customers 02
What i have to do!!!!
I must make this control to every hour of the day
Please Help!
Hi Manuel,
If I understand you question, you are looking to write a query that shows overlapping sessions.
If you start with a list of all the distinct start times, you can then use a correlated query to look for overlapping sessions (something like):
Code Snippet
select s1.app, st, (
select count(*)
from Sessions as s2
where
s1.app = s2.app
and s1.st >= s2.StartDateTime
and s1.st < s2.StopDateTime ) as Overlaps
from (
select distinct app, StartDateTime st
from Sessions
) as s1
Note that set based solutions around this type of problem can become really slow, really quickly! A cursor based solution can scale much better. You'd have to do some testing to see which would work best for you.
Hope that helps.
Jamie
|||Here is a query that will retrieve all the connections that are overlapping.
Code Snippet
Select Conn1.Customer, Conn1.BeginDate, Conn1.EndDate,
Conn2.Customer, Conn2.BeginDate, Conn2.EndDate, *
From Connections Conn1, Connections Conn2
Where Conn2.BeginDate Between Conn1.BeginDate And Conn1.EndDate
And Conn1.ID <> Conn2.ID
I'm assuming the table name is called 'Connections' and that there is a primary key which is called ID. Here I've joined the table on itself looking for connections that have their start time between another connection's start and end time. I added 'Conn1.ID <> Conn2.ID' so that the query does not assume that the same connection is as an overlapping one.
I hope this answers your question.
Best regards,
Sami Samir
|||This query might help you...
Code Snippet
Select
MainThread.CustomerId Customer
,MainThread.Startdate Customer1StartAt
,MainThread.Enddate Customer2EndAt
,ParlelThread.CustomerId ParllelWith
,ParlelThread.Startdate ParllelCustomerJoinAt
,ParlelThread.Enddate ParllelCustomerLeaveAt
,DateDiff(Mi, ParlelThread.Startdate, Case When MainThread.Enddate < ParlelThread.Enddate Then MainThread.Enddate Else ParlelThread.Enddate End ) TotalMinutesParllel
from AuditLog MainThread
Join AuditLog ParlelThread On
MainThread.Enddate > ParlelThread.Startdate And
MainThread.Startdate <= ParlelThread.Startdate
And MainThread.CustomerId <> ParlelThread.CustomerId
Order By 1
I have only one table which contains:
IDCustomers
Name
BeginDate
EndDate
My really problem is to abtain the list of cutomers whitch day,mount, years is the same and
have the same hour of logon.
I ask for new excuse for my English
|||
Try this :
Substitute your actual table name in <YourTable>
select BeginDate,IDCustomers,Name,EndDate
from <YourTable>
where BeginDate in
(
select BeginDate
from <YourTable>
group by BeginDate
having count(*) > 1
)
order by BeginDate,IDCustomers
Thanks.
Naras.
|||The following query may help you...
Code Snippet
Select
MainThread.CustomerId Customer
,MainThread.Startdate Customer1StartAt
,MainThread.Enddate Customer2EndAt
,ParlelThread.CustomerId ParllelWith
,ParlelThread.Startdate ParllelCustomerJoinAt
,ParlelThread.Enddate ParllelCustomerLeaveAt
Into #Data
from <YourTable> MainThread
Join <YourTable>ParlelThread On
MainThread.Enddate > ParlelThread.Startdate And
MainThread.Startdate <= ParlelThread.Startdate
And MainThread.CustomerId <> ParlelThread.CustomerId
Order By 1
Select Customer, Customer1StartAt, Customer2EndAt From #Data
Union
Select ParllelWith, ParllelCustomerJoinAt, ParllelCustomerLeaveAt From #Data
Drop Table #Data
|||thanks to all… I have resolved the problem with one function.you have been fast and kind
BYE