Showing posts with label fields. Show all posts
Showing posts with label fields. Show all posts

Monday, March 19, 2012

.Update creates new record but all fields null

Hi guys, I have made several Access-based CMSs but now I am using SQL Server. I can read the records but my first attempts at writing are resulting in new records (with new ID) but all the fields are 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

Hi, my name is MANUEL. I don't speak english very well...
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

|||Sorry!I don't know SQL SERVER too

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