Comments by isi19 (Top 61 by date)

So i should ask them to save the email address that it is being sent from to the while list?

I have added a session timeout to the application, the problem i am having is when the user closes the browser accidentally, they will then only be able to login once the cache timesout

Do i need to create a windows application to schedule the task?

Thank you

Yes it is a multiline textbox and its a web app

Web Application

The Files are not very big between 1-10kb.

The exact error i am getting is
The process cannot access the file because it is being used by another process.

any idea how i could resolve this issue?

table rectype shows fieldnames for Table A

So in rectype i have the following values for example
RECType Fieldname Seq
A Field1 1
A Field2 2
A Field3 3
B Field2 1
B Field3 2


Etc..

Table A (Format)
Field1 field2 field3

So instead of me needing to manually check the fields that Rec type has when i run my insert statement i want to use a select statement so that sql will select the correct values

I want to use the Array to do an insert statement into my database fields, fields may change and they vary based on the recordtype

The process needs to export transaction details from the database to an FTP server.
I dont have access to the server to install a windows service

So i tested running the application i coded on my local machine and then deployed it to a server. The server regional settings are the same as my local machine settings.

I have an older version of the application which is on the same server and that shows as my local machine.

Thanks

Thanks

how could i trace those records that failed to insert?

Thank you for the sample code

I have not worked much on datagridviews before, i have been using tables previously.So im not sure on how to update those fields when the focus is lost.

There is a button
on my asp.net page when the user presses the button the above code gets executed.

I when i run the application from a browser in the data folder i can see the files being converted to .old and on the databse i can see the amount of records being added to my bulkimport table.

After 15min the record count is around 1,117,500 that was tested on many occasions, i have been timming how long the process runs for. After the 15min the count is reset the data that was in my Bulkimport table gets archived and the Bulkimporttable is cleared and the proccess starts at the first .txt file it can find.

Please view my code added to the question.

The main issue i am having is it is getting a a file which is 193kb, it reads the file and once the file is completed being read it is creating a temp file, so if the file is file1.txt it then is creating a file called file1.txt.tmp

When i debug on my development i dont get any errors, the process runs smoothly

But nothing else is running when i run this process and the only person with access to that folder that the file is being read from on the server is me,and the file was not opened.

i have also tried the following

me.amount.text = numericMoney.ToString("R ###0.00")

Me.Amount.Text = TableA.Amount.Format(TableA.Amount.DefaultValue, "R ###0.00")

See the problem i am having is when users select a zip file, all the files from that zip folder are placed in a temp folder which gets read and there is a count of each line that get read, how would i be able to display or update the label while the code is running

Web

Yes that may work

Thanks Logically makes sense

Thanks alot

Thanks :)

The Received date is the workcompleted date in the Receiving table and Dispatched date is the workcompleted date in the dispatch table.
Table 1 and table 2 are the stages i was referring to.

The Partion by is ranking the serial based on the Symptom. Which is great if the Serial has only come into the system once. That way i get to select my symptom with the greatest weight

Sample data
Symmptoms
NFF 900
NFD 850
NFC 800
NFS 750
NHF 700
NNF 650


Receiving Table
id|serial | Workcompleted |symptom | SymptomWeight
1|100 | 2013-03-01 |NULL | 999(Default Value if Symptom is null)
2|100 | 2013-04-11 |NULL | 999

Dispatch Table
id|serial | Workcompleted |symptom | SymptomWeight
1| 100 |2013-03-12 | NULL | 999
2| 100 |2013-04-15 | NULL | 999

Table 1
id|serial | Workcompleted |symptom | SymptomWeight
1| 100 |2013-03-03 | NFF | 900
3| 100 |2013-04-11 | NFD | 850

Table 2
id|serial | Workcompleted |symptom | SymptomWeight
1| 100 |2013-03-04 | NFD | 850
5| 100 |2013-04-12 | NFC | 800


Union of Table A,B,Receiving and Dispatch VwListallstages

id|serial | Workcompleted |symptom | SymptomWeight
1| 100 | 2013-03-01 |NULL | 999(Default Value if Symptom is null)
2| 100 | 2013-04-11 |NULL | 999
1| 100 |2013-03-12 | NULL | 999
2| 100 |2013-04-15 | NULL | 999
1| 100 |2013-03-03 | NFF | 900
3| 100 |2013-04-11 | NFD | 850
1| 100 |2013-03-04 | NFD | 850
5| 100 |2013-04-12 | NFC | 800


Desired Results

This is my current select statement
(select id|serial | Workcompleted |symptom | SymptomWeight
CASE WHEN (ROW_NUMBER() OVER (PARTITION BY LIstAllStages.serial
ORDER BY LIstAllStages.SymptomWeight)) = 1 THEN 'Y' ELSE 'N' END 'Primary
from listallstages)
id|serial | Workcompleted |symptom | SymptomWeight|Primary
1| 100 | 2013-03-01 |NULL | 999 | N
1| 100 |2013-03-12 | NULL | 999 | N
1| 100 |2013-03-03 | NFF | 900 | N
1| 100 |2013-03-04 | NFD | 850 | Y
2|100 | 2013-04-11 |NULL | 999 | N
2| 100 |2013-04-15 | NULL | 999 | N
3| 100 |2013-04-11 | NFD | 850 | N
5| 100 |2013-04-12 | NFC | 800 | Y


Current Result
id|serial | Workcompleted |symptom | SymptomWeight|Primary
1| 100 | 2013-03-01 |NULL | 999 | N
1| 100 |2013-03-12 | NULL | 999 | N
1| 100 |2013-03-03 | NFF | 900 | N
1| 100 |2013-03-04 | NFD | 850 | N
2|100 | 2013-04-11 |NULL | 999 | N
2| 100 |2013-04-15 | NULL | 999 | N
3| 100 |2013-04-11 | NFD | 850 | N
5| 100 |2013-04-12 | NFC | 800 | Y

No,what happens is the product serial is received and has a received date goes through 8 stages with the above table structure and then is dispatched. so if the product was received

2012-12-01 and dispatched 2013-01-20 and the statement ( CASE WHEN (DENSE_RANK() OVER (PARTITION BY LIstAllStages_Backup3.productserial ORDER BY LIstAllStages_Backup3.SymptomWeight ASC)) = 1 THEN 'Y' ELSE 'N' END 'Primary' has selected a symptom of NFF
result will be
productserial = 4545604
DateDispatched = 2013-01-20
Symptom = NFF
SymptomWeight 800
the second received date = 2013-03-24 and dispatched date = 2013-04-01 and the symptom should be selected as NFD(has a weight of 850)
the result is currently showing

serial = 4545604
DateDispatched = 2013-04-01
Symptom = NFF (Because the weight of NFF > NFD)
SymptomWeight = 850

Structure of the table is as follows
id (int)
Serial (nvarchar 10)
code (Nvarchar 4)
passfail (int )
Faultid int
SubFaultid int
Symptomid int
WorkCompleted int
SymptomWeight int

Code = Stage code
for all 10 stages

id| serial|code|PassFail|Faultid|SubfaultID|Symptomid|Workcompleted|SymptomWeight
422|40284 |SLLS| 1 |NULL |NULL | 84 | 2012-01-04 | 999

i have created my stored proc and am able to select the serial through a cursor, how would i set the variable @did,@rid to be = did,rid from the following sql statement
Select top 1 Min(Workcompleted) dispatcheddate,min (dateReceived)Receiveddate,Serial,RID,did,dispatchorderid from VwDatesReceivedVsDatesDispatched where Manufacturerserialnumber = '" & @serial & "' and dispatchorderid is null group by Seril,RID,did,dispatchorderid

What i need to to is create a stored procedure which will update my Receiving table column Dispatchorderid, so that my Received date can be linked to a dispatched date,

the first query on the page shows all serials without any dispatched order id.

the second query returns the min date received and the min date dispatched as well as the Received id and dispatched id for those dates
E.G.
id | Serial |ReceievedDate | id | DispatchedDate
80 |98654 |2013-04-01 |5441| 2013-04-06

The Select * from Receiving table will look as follows before the update
id|Serial|DateReceived|Dispatchid
80|98654 |2013-04-01 |NULL

Then i need to update Receiving table to show the following
id|Serial|DateReceived|Dispatchid
80|98654 |2013-04-01 |5441

I am not sure about how to get my variables(@serial,@Rid,@did)

500025 |20000 | 5000 | |1002 |2013-01-14 | 2013-03-14|
this line i need to exclude as this shows a received date after it had been received again

The joins will return the exact same result as what i am getting

thanks,Seems to be working as well as solution 2

Thanks alot for the help

Hi thanks

but i need the symptom with the highest weight only to show as High and anything with a lower value to show as low

Each symptom has a different weight between (0 and 100)

so lets say the symptoms are
Symptom Weight
liquid 50
Cosmetic 20
repair 80


and the ProductCode is C100
select tableb.ProductCode,TableB.Comment,TableA.Symptom(????)as priority from TableA.

so the result of the select statement should show the following
ProductCode comment symptom Priority
C100 N/A liquid low
C100 N/A Cosmetic low
C100 N/A Repair High

Each symptom has a different weight between (0 and 100)

so lets say the symptoms are
Symptom Weight
liquid 50
Cosmetic 20
repair 80


and the ProductCode is C100
select tableb.ProductCode,TableB.Comment,TableA.Symptom(????)as priority from TableA.

so the result of the select statement should show the following
ProductCode comment symptom Priority
C100 N/A liquid low
C100 N/A Cosmetic low
C100 N/A Repair High

I am not sure how to exclude the dates which the dispatch date < Receiving date.

Select * from ReceivingDatesbyDispatch where (case when???)

Hi

I have managed to solve the issue when creating the view because the statements are unioned they have similar links to tables and the ssms created the view and aliased the 2nd and 3rd etc join

so if my query was select id,code from (select test.id,test.codeid from test inner join test.code on code.id = test.codeid unionall select table2.id,table.codeid inner join table2.codeid on code.id = table2.codeid) AS D


the sub query changed to
select test.id,test.codeid from test inner join test.code on code.id = test.codeid unionall select table2.id,table.codeid inner join table2.codeid_1 on code.id = table2.codeid_1

See the only thing is that the product has to be passed to be dispatched , the process will follow Rceive pass --> soak pass --> test pass --> dispatch

if there is a fail

Rceive pass--> soak fail--> repair Pass--> soak pass--> test pass--> dispatch

now i need to select only the products that follow the 1st process.
if i select * from ListStage where passfail = 1 then it will return the value of the product which had a repair. and i want it to show only the products which do not have a repair

Thanks yes this is exactly what i needed

I have a View called listallstages , which captures dates which the productSerial passes through the system.

what i want to do is select all the records which have a count > 1 , but it should only show the records from the second date fround, so if the productserial is 600p

the listallstages view will show
ProductSerial Date
600p 27/02/2013
600p 28/02/2013
600p 1/03/2013
the query that i have posted will show
cnt productserial
3 600p

in my query i only want to show the records with a cnt > 1 from the above view
join it onto the listallstages , and show all the dates excluding the first date

so it would only show

600p 28/02/2013
600p 1/03/2013

Thanks , but I need to show more than the last record with the maxdate, i need to select all the products,the max date and the stage ,

See the products go to stages, and when they are completed at a stage they go to another stage,to track history of where the product is i created a view listStage,so now i want to view all the products whats the current stage bassed on the date it was completed

i need to get the count of products passed per day per user

My table is


id,product,date,userid,stage

what would my query be to select the amount of products entered by a user?

Yes i need it to show both stages, problem is if it goes to stage SHS twice within the same day i want it to show the first only

Sorry yes pool for the Technicians

Like how would i build the pool for the users, will the select statement be select count productID?

is there a way for me to sort it in Sql query?