PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in ..../includes/geshi.php on line 2147

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP Warning: Cannot modify header information - headers already sent by (output started at ..../includes/class_core.php:6075) in ..../external.php on line 901

PHP Warning: Cannot modify header information - headers already sent by (output started at ..../includes/class_core.php:6075) in ..../external.php on line 901

PHP Warning: Cannot modify header information - headers already sent by (output started at ..../includes/class_core.php:6075) in ..../external.php on line 901

PHP Warning: Cannot modify header information - headers already sent by (output started at ..../includes/class_core.php:6075) in ..../external.php on line 901

PHP Warning: Cannot modify header information - headers already sent by (output started at ..../includes/class_core.php:6075) in ..../external.php on line 901

PHP Warning: Cannot modify header information - headers already sent by (output started at ..../includes/class_core.php:6075) in ..../external.php on line 901
VBForums - Database Development http://www.vbforums.com/ Post your questions about developing applications to interact with databases here. Topics include Using the Data Control, DAO, ADO, ODBC, JDBC, JDO, database administrative issues and database engines, etc. en Thu, 19 Jul 2018 01:36:36 GMT vBulletin 60 http://www.vbforums.com/images/misc/rss.png VBForums - Database Development http://www.vbforums.com/ T-SQL training http://www.vbforums.com/showthread.php?864533-T-SQL-training&goto=newpost Wed, 18 Jul 2018 10:57:41 GMT I've got a client asking what resources are available online for learning T-SQL.

Does anyone have any experience with online classes in this area?

Thanks! ]]>
Database Development szlamany http://www.vbforums.com/showthread.php?864533-T-SQL-training
Riddle me this, batman http://www.vbforums.com/showthread.php?864505-Riddle-me-this-batman&goto=newpost Tue, 17 Jul 2018 13:36:27 GMT This SPROC runs once a month to post dues that were withheld in a pension check.

Occasionally the "final" update statement never happens.

How could MS SQL allow this - since the entire SPROC is contained in a BEGIN TRAN/COMMIT??

Code:

CREATE PROCEDURE CalcPayDues_P
as

Begin Tran

Set NoCount On

Declare @Pay_Period varchar(7)
Declare @DatePaid datetime
Declare @DuesBatch int

Set @Pay_Period=(Select ConfData From Funds_T Where ConfItem='CurPayDues')
Set @DatePaid=Cast(Convert(Char(10),GetDate(),101) as datetime)
Set @DuesBatch=0

Declare @IdList Table (MasId int, PayCheck varchar(7), Amount money
        , DuesDate datetime, DuesBatch int, DuesSeq varchar(1), DateFor datetime, OverPay money)

Insert into @IdList (MasId,PayCheck,Amount)
        Select PH.MasId,PH.RecType+Right('000000'+Cast(PH.RecNumber as varchar(6)),6),Amount From PayHistory_T PH
        Left Join PayCheck_T PC on PC.RecType=PH.RecType AND PC.RecNumber=PH.RecNumber
        Where PH.PayPeriod=@Pay_Period and Code='RDUE' and PC.Status not in ('R','S','V')

Update @IdList Set DuesDate=(Select Max(DuesDate) From Dues_T DU Where DU.MasId=IL.MasId)
        From @IdList IL

Update @IdList Set DuesBatch=(Select Max(DuesBatch) From Dues_T DU Where DU.MasId=IL.MasId and DU.DuesDate=IL.DuesDate)
        From @IdList IL

Update @IdList Set DuesSeq=(Select Max(DuesSeq) From Dues_T DU Where DU.MasId=IL.MasId and DU.DuesDate=IL.DuesDate and DU.DuesBatch=IL.DuesBatch)
        From @IdList IL

Update @IdList Set DateFor=(Select DateFor From Dues_T DU
                        Where DU.MasId=IL.MasId and DU.DuesDate=IL.DuesDate and DU.DuesBatch=IL.DuesBatch and DU.DuesSeq=IL.DuesSeq)
        From @IdList IL

Update @IdList Set OverPay=(Select OverPay From Dues_T DU
                        Where DU.MasId=IL.MasId and DU.DuesDate=IL.DuesDate and DU.DuesBatch=IL.DuesBatch and DU.DuesSeq=IL.DuesSeq)
        From @IdList IL

Insert into Dues_T
        Select IL.MasId,@DatePaid,@DuesBatch
        ,'A','PD',IL.Amount,IL.Amount,@DatePaid
        ,DateAdd(mm,1,IL.DateFor)
        ,IL.OverPay
        ,null,'D','N',IL.PayCheck,GetDate()
        From @IdList IL

Update Funds_T Set ConfData='' Where ConfItem='CurPayDues'

commit
Go

]]>
Database Development szlamany http://www.vbforums.com/showthread.php?864505-Riddle-me-this-batman
VB6/Access showing records with same value http://www.vbforums.com/showthread.php?864487-VB6-Access-showing-records-with-same-value&goto=newpost Tue, 17 Jul 2018 06:09:19 GMT Hi guys, it is my first time here. I am currently doing a simple project that includes a simple CRUD method. I use surname to search for the records in database with adodc. And I know, some people have the same surname, thus it cannot be a unique value. Here's the question tho... Is there any way where after searching the surname, if the record that has the same value is more than one, it will show a list that shows the full name and birth date that is in the record. For me it is not a major problem, but I want that program to be a little more foolproof.

Thanks! ]]>
Database Development CharlesHarris12 http://www.vbforums.com/showthread.php?864487-VB6-Access-showing-records-with-same-value
<![CDATA[[RESOLVED] How to get top 3 on the counted column]]> http://www.vbforums.com/showthread.php?864453-RESOLVED-How-to-get-top-3-on-the-counted-column&goto=newpost Mon, 16 Jul 2018 10:15:43 GMT = '2018-07-01' AND SQLDateTime <= '2018-07-13 'AND Station_No in (19,20,21,22,23,24)and Fail_Description NOT LIKE '%[PASS]%' group by Fail_Description,Station_No ORDER BY Total_Fail DESC --------- this will give result as below. Total_Fail Fail_Description Station_No 8 RR [79] 22 6 RR [79] 20 6 RR [81] 22 6 RR [80] 23 4 RR [80] 22 2 RR [79] 19 2 RR [80] 19 2 RR [80] 20 2 RR [81] 20 2 RR [81] 21 2 RR [78] 23 2 RR [79] 23 2 RR [80] 24 1 RR [6] 24 but i just want to get the top 3 ,how can i do that ?]]> how can i get the top 3, after i count the column ?

here is my code. to count the no. of the column Total_Fail.

Code:

select  count(UUT_SN) as Total_Fail,Fail_Description,Station_No from Muscatel_MPCA_FCT1_LogData_T
WHERE SQLDateTime >= '2018-07-01'
AND SQLDateTime <= '2018-07-13 'AND Station_No in (19,20,21,22,23,24)and Fail_Description NOT LIKE '%[PASS]%'
group by Fail_Description,Station_No
ORDER BY Total_Fail DESC

this will give result as below.

Total_Fail Fail_Description Station_No
8 RR [79] 22
6 RR [79] 20
6 RR [81] 22
6 RR [80] 23
4 RR [80] 22
2 RR [79] 19
2 RR [80] 19
2 RR [80] 20
2 RR [81] 20
2 RR [81] 21
2 RR [78] 23
2 RR [79] 23
2 RR [80] 24
1 RR [6] 24


but i just want to get the top 3 ,how can i do that ? ]]>
Database Development BONITO http://www.vbforums.com/showthread.php?864453-RESOLVED-How-to-get-top-3-on-the-counted-column
Dsn odbc dao vb6 http://www.vbforums.com/showthread.php?864301-Dsn-odbc-dao-vb6&goto=newpost Wed, 11 Jul 2018 06:29:59 GMT I need some advice on opening a MySql database DSN in VB6 with ODBC and DAO.
The code I use fails, and I do not know why.

Dim wsODBC As Workspace
Set wsODBC = DBEngine.CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
Workspaces.Append wsODBC
Dim Conn As Connection
Set Conn = wsODBC.OpenConnection("Conn1", , , "ODBC;DSN=ibsmsbloem;") - this line gives an error OBDC call failed.
Dim rs As Recordset
Set rs = Conn.OpenRecordset("accountterms", dbOpenSnapshot)

Is there some checklist through which I may work to check that I have done everything right or some examples.

Thanks ]]>
Database Development Peekay http://www.vbforums.com/showthread.php?864301-Dsn-odbc-dao-vb6
<![CDATA[[RESOLVED] Table has two different fields, related to the same table]]> http://www.vbforums.com/showthread.php?864287-RESOLVED-Table-has-two-different-fields-related-to-the-same-table&goto=newpost Tue, 10 Jul 2018 21:16:02 GMT This is an MS Access database

I know, the title is vague as I'm really struggling with how to word this question.. Here goes..

I have a table with two fields, pointing to different records, in the same related table. I need to pull this data together to build a report. I've created some sub-queries, but, I'm certain there's an easier/simpler way to achieve this.

Any help is appreciated. ]]>
Database Development jazFunk http://www.vbforums.com/showthread.php?864287-RESOLVED-Table-has-two-different-fields-related-to-the-same-table
How to check if a record exist in a MS Access DB using Query Builder http://www.vbforums.com/showthread.php?864127-How-to-check-if-a-record-exist-in-a-MS-Access-DB-using-Query-Builder&goto=newpost Wed, 04 Jul 2018 15:27:39 GMT Hi, is their a way to check if a record exists in an MS Access database using the query builder? Hi,
is their a way to check if a record exists in an MS Access database using the query builder? ]]>
Database Development Monti124 http://www.vbforums.com/showthread.php?864127-How-to-check-if-a-record-exist-in-a-MS-Access-DB-using-Query-Builder
Export error in MySQL Workbench? http://www.vbforums.com/showthread.php?864101-Export-error-in-MySQL-Workbench&goto=newpost Tue, 03 Jul 2018 20:49:13 GMT I'm trying to export a mysql database from MySQL Workbench. I get a warning. Click on the below link to see the message

http://barefootinthepark.co.uk/%E2%8...ysql-workbench

Now, after I did what was suggested, I get the below error message. I'm not sure where the "log" file is either.
Attached Images
 
]]>
Database Development blakemckenna http://www.vbforums.com/showthread.php?864101-Export-error-in-MySQL-Workbench
<![CDATA[[RESOLVED] Checking duplicate mobile number while editing student's profile]]> http://www.vbforums.com/showthread.php?864057-RESOLVED-Checking-duplicate-mobile-number-while-editing-student-s-profile&goto=newpost Mon, 02 Jul 2018 11:42:34 GMT I am currently working on Library Management System. I have a student module with functions such like Add Student, Delete Student, Edit Student, List Students.If profile of student is edited, the mobile number field must be unique in the database. For this I am storing all the mobile numbers in a temporary array and comparing each array item with mobile text box. My problem is if a student's mobile number is not edited (other fields like name are only edited) and I try to save the updated record in my database (by moving record pointer), the student's unedited mobile number is also taken as duplicate value. I mean that my current coding forces the student to change his mobile number if he tries to change any other field also. What I want is that my coding should not compare the text box value with current student's record. Please help :cry: ]]> Database Development kommal.123.kk http://www.vbforums.com/showthread.php?864057-RESOLVED-Checking-duplicate-mobile-number-while-editing-student-s-profile Where to find or how to create a MySQL my.ini file? http://www.vbforums.com/showthread.php?864035-Where-to-find-or-how-to-create-a-MySQL-my-ini-file&goto=newpost Sun, 01 Jul 2018 03:11:37 GMT I installed MySQL on my machine using an .msi file, which did not install the "my.ini". I really have no idea what an "out-of-the-box" my.ini file looks like. Any ideas?

Thanks, ]]>
Database Development blakemckenna http://www.vbforums.com/showthread.php?864035-Where-to-find-or-how-to-create-a-MySQL-my-ini-file
No mapping exists from DbType SByte to a known SqlDbType. http://www.vbforums.com/showthread.php?863977-No-mapping-exists-from-DbType-SByte-to-a-known-SqlDbType&goto=newpost Thu, 28 Jun 2018 21:24:32 GMT :wave:

I have the following function that I'm using to build SqlCommand objects for an insert statement:

Code:

Private Function GetParam(Name As String, type As Data.SqlDbType, value As Object)
        Dim RetVal As New Data.SqlClient.SqlParameter()
        With RetVal
            .ParameterName = Name
            .Value = value
            .DbType = type

        End With
        Return RetVal
    End Function

The problem I'm running into is when I try to add a parameter and set the DbType to 'UniqueIdentifier'. The Value in this case is a Guid and the column on the database is a UniqueIdentifier. It will throw the exception that I have in the title above. Curiously, this will happen even if I try to set the DbType before setting the Value.

So I'm guessing that UniqueIdentifier may not be a valid type?

Has anybody ran into this before? ]]>
Database Development dolot http://www.vbforums.com/showthread.php?863977-No-mapping-exists-from-DbType-SByte-to-a-known-SqlDbType
how to count records entered in my table http://www.vbforums.com/showthread.php?863949-how-to-count-records-entered-in-my-table&goto=newpost Thu, 28 Jun 2018 12:24:21 GMT Hi All ,

I have this table name DQC3422
code below is to show you what the data of table .
Code:

SELECT TEST_DTTM, MODEL,Location as DEVICE_NO,MO_SN,Def_item,station FROM DQC342 WHERE STATION = 'PCA ICT' and
LOCATION='ICTNO21'and TEST_DTTM BETWEEN TO_DATE('20180620','yyyymmdd') AND TO_DATE('20180620 235959','yyyymmdd hh24miss')

sample output below: this can have thousands or hundred thousands records . depends on date query.

TEST_DTTM MODEL DEVICE_NO MO_SN DEF_ITEM STATION
6/20/2018 8:02 PMPU75 ICTNO21 S41M582306067 FAIL PCA ICT
6/20/2018 8:11 PMPU75 ICTNO21 S41M582407291 OK PCA ICT
6/20/2018 8:12 PMPU75 ICTNO21 S41M5823064AA OK PCA ICT
6/20/2018 8:13 PMPU75 ICTNO21 S41M58240748C OK PCA ICT
6/20/2018 8:13 PMPU75 ICTNO21 S41M582407584 OK PCA ICT
6/20/2018 8:14 PMPU75 ICTNO21 S41M58240749B OK PCA ICT
6/20/2018 8:15 PMPU75 ICTNO21 S41M5824075CA OK PCA ICT
6/20/2018 8:15 PMPU75 ICTNO21 S41M5824074D2 OK PCA ICT
6/20/2018 8:16 PMPU75 ICTNO21 S41M5824074D3 OK PCA ICT
6/20/2018 8:17 PMPU75 ICTNO21 S41M5824074E6 OK PCA ICT
6/20/2018 8:18 PMPU75 ICTNO21 S41M5824074D1 OK PCA ICT
6/20/2018 8:19 PMPU75 ICTNO21 S41M5824074CE OK PCA ICT
6/20/2018 8:21 PMPU75 ICTNO21 S41M582306067 OK PCA ICT
6/20/2018 8:22 PMPU75 ICTNO21 S41M582407432 OK PCA ICT
6/20/2018 8:24 PMPU75 ICTNO21 S41M5824074F0 OK PCA ICT
6/20/2018 8:25 PMPU75 ICTNO21 S41M5824074D0 OK PCA ICT
6/20/2018 8:26 PMPU75 ICTNO21 S41M582407572 OK PCA ICT
6/20/2018 8:27 PMPU75 ICTNO21 S41M5824074DF OK PCA ICT
6/20/2018 8:28 PMPU75 ICTNO21 S41M582407573 OK PCA ICT
6/20/2018 8:29 PMPU75 ICTNO21 S41M5824074F8 OK PCA ICT
6/20/2018 8:29 PMPU75 ICTNO21 S41M582407226 OK PCA ICT
6/20/2018 8:30 PMPU75 ICTNO21 S41M5824074D7 OK PCA ICT
6/20/2018 8:31 PMPU75 ICTNO21 S41M582407439 OK PCA ICT
6/20/2018 8:32 PMPU75 ICTNO21 S41M582402E42 OK PCA ICT
6/20/2018 8:33 PMPU75 ICTNO21 S41M582402E41 OK PCA ICT
6/20/2018 8:34 PMPU75 ICTNO21 S41M58240735D OK PCA ICT
6/20/2018 8:35 PMPU75 ICTNO21 S41M5824072A2 FAIL PCA ICT
Details of data as below:
1. test_dttm(date and time of test)
2. Model(10 different model)-can be test in any device.
3. DEVICE_NO(5 device no.21-25)- 1 device can test more than 1 model.
4. MO_SN(serial no of each items)- Users scan all serial no. of items to system and add in database.
5. DEF_ITEM(items has 2 results OK & FAIL),- has 3 conditions.
5.1. first test Pass = OK.
5.2. first test Fail and second test Pass = OK but both serial number
is kept in database with different result so this are called RE-TEST.
5.3. first test fail ,second test fail and third test fail = FAIL called TRUE FAIL. will fall in column FAIL.
this are the serial number that has Def_Item = FAil only, no have OK/Pass.
I want to count all that data, OK,RETEST and Fail.
6. STATION(5 station but I focus to 1 station PCA ICT)

what i want to accomplish is to count the following for 1 day:
[B]1. INPUT [/B]
I used code below to count INPUT .
these are the total items inputted by users, counting Distinct only, no duplicates.
Code:

SELECT MODEL,station,location,count(distinct MO_SN) as TOTAL_INPUT from dqc342 where station='PCA ICT'  and TEST_DTTM BETWEEN TO_DATE('20180620','yyyymmdd') AND TO_DATE('20180620 235959','yyyymmdd hh24miss')
and location='ICTNO21'
group by model,station,location

[B]2. OK [/B]
this are the MO_SN no duplicate & DEF_ITEM = OK.
Example:
TEST_DTTM MODEL DEVICE_NO MO_SN DEF_ITEM STATION
6/20/2018 0:23 PMPG18 ICTNO21 SOTR582407395 OK PCA ICT
6/20/2018 0:23 PMPG18 ICTNO21 SOTR582407399 OK PCA ICT
6/20/2018 0:23 PMPG18 ICTNO21 SOTR582407398 OK PCA ICT
6/20/2018 0:24 PMPG18 ICTNO21 SOTR582407394 OK PCA ICT
6/20/2018 0:24 PMPG18 ICTNO21 SOTR58240739A OK PCA ICT
6/20/2018 0:24 PMPG18 ICTNO21 SOTR58240739E OK PCA ICT
6/20/2018 0:24 PMPG18 ICTNO21 SOTR582407396 OK PCA ICT
6/20/2018 0:24 PMPG18 ICTNO21 SOTR5825002CE OK PCA ICT
6/20/2018 0:24 PMPG18 ICTNO21 SOTR5825002CD OK PCA ICT
6/20/2018 0:24 PMPG18 ICTNO21 SOTR5825002CB OK PCA ICT

[B]3. FAIL [/B]
this are the MO_SN with no duplicate & DEF_ITEM = FAIL.
example:
TEST_DTTM MODEL DEVICE_NO MO_SN DEF_ITEM STATION
6/20/2018 3:52 PMPG18 ICTNO21 SOTR5824047F3 FAIL PCA ICT
6/20/2018 4:20 PMPG18 ICTNO21 SOTR582407320 FAIL PCA ICT
6/20/2018 14:17 PMPG18 ICTNO21 SOTR582407057 FAIL PCA ICT
6/20/2018 14:54 PMPG18 ICTNO21 SOTR582405604 FAIL PCA ICT
6/20/2018 15:46 PMPG18 ICTNO21 SOTR580403F29 FAIL PCA ICT
6/20/2018 15:46 PMPG18 ICTNO21 SOTR580403F2A FAIL PCA ICT

[B]4.RE-TEST [/B]
these are the MO_SN with DUPLICATE & DEF_ITEM are 1 = OK and 1 = FAIL.
example:
TEST_DTTM MODEL DEVICE_NO MO_SN DEF_ITEM STATION
6/20/2018 0:34 PMPG18 ICTNO21 SOTR582500580 FAIL PCA ICT
6/20/2018 0:35 PMPG18 ICTNO21 SOTR582500580 OK PCA ICT
6/20/2018 0:35 PMPG18 ICTNO21 SOTR582500582 FAIL PCA ICT
6/20/2018 0:35 PMPG18 ICTNO21 SOTR582500582 OK PCA ICT
6/20/2018 0:42 PMPG18 ICTNO21 SOTR582407D77 FAIL PCA ICT
6/20/2018 0:43 PMPG18 ICTNO21 SOTR582407D77 OK PCA ICT
6/20/2018 0:44 PMPG18 ICTNO21 SOTR582407D60 FAIL PCA ICT
6/20/2018 0:45 PMPG18 ICTNO21 SOTR582407D60 OK PCA ICT
6/20/2018 0:47 PMPG18 ICTNO21 SOTR582407DA5 FAIL PCA ICT
[B]4.YIELD RATE [/B]
will be calculated using this formula 100-(Fail/Input)* 100
below will be my final output.

by the way i am using PL/SQL DEVELOPER.
thanks in advanced.
Name:  sssss.jpg
Views: 100
Size:  12.6 KB
Attached Images
  
]]>
Database Development BONITO http://www.vbforums.com/showthread.php?863949-how-to-count-records-entered-in-my-table
check if a begintrans is called... http://www.vbforums.com/showthread.php?863891-check-if-a-begintrans-is-called&goto=newpost Tue, 26 Jun 2018 13:00:46 GMT possible if a begintrans is called and commit if is true?

I'm working on VB6 and ADO ]]>
Database Development luca90 http://www.vbforums.com/showthread.php?863891-check-if-a-begintrans-is-called
Database Error There is no row at position 2 http://www.vbforums.com/showthread.php?863847-Database-Error-There-is-no-row-at-position-2&goto=newpost Sun, 24 Jun 2018 20:20:27 GMT = 1) // Check if the DataTable returns any data from database { if (!DBNull.Value.Equals(dataTable.Rows[currentId]["id"])) { Lbx_LeftList.SelectedValue = dataTable.Rows[currentId]["id"]; } } This gives me error: 'There is no row at position 2.' for this line: if (!DBNull.Value.Equals(dataTable.Rows[currentId]["id"])) When I debug, I see that the data table has 2 rows and currentId equals to 2. The data table is set like this public DataTable DBNextRecord() { connectionString.DataSource = "database.sqlite"; connectionString.ForeignKeys = true; connectionString.JournalMode = SQLiteJournalModeEnum.Wal; System.Console.WriteLine(connectionString.ToString()); sqlite_conn = new SQLiteConnection(connectionString.ToString()); selectQueryString = @"SELECT S.id, P.txt_press, CA.txt_category, S.txt_repertory_no, T.txt_taken_from, S.txt_revision_date1, S.txt_revision_date2, S.txt_revision_date3, R.txt_region, PR.txt_prepared_by, S.txt_research_date1, S.txt_research_date2, S.txt_research_date3, SP.txt_source_by, M.txt_measure, S.txt_time, C.txt_compiled_by, S.txt_compilation_date, N.txt_notation_by, S.txt_mp3_paths, A.txt_artist, S.txt_song_name, S.txt_lyrics, S.image1,S.image2,S.image3,S.image4,S.image5,S.image6,S.image7, S.image8,S.image9, S.image10,S.image11,S.image12,S.image13,S.image14 FROM tbl_sheet S LEFT JOIN tbl_category CA ON CA.id = S.int_category_id LEFT JOIN tbl_press P ON P.id = S.int_press_id LEFT JOIN tbl_taken_from T ON T.id = S.int_taken_from_id LEFT JOIN tbl_prepared_by PR ON PR.id = S.int_prepared_by_id LEFT JOIN tbl_region R ON R.id = S.int_region_id LEFT JOIN tbl_source_by SP ON SP.id = S.int_source_by_id LEFT JOIN tbl_measure M ON M.id = S.int_measure_id LEFT JOIN tbl_compiled_by C ON C.id = S.int_compiled_by_id LEFT JOIN tbl_notation_by N ON N.id = S.int_notation_by_id LEFT JOIN tbl_artist A ON A.id = S.int_artist_id;"; sqlite_conn.Open(); sqliteDataAdapter = new SQLiteDataAdapter(selectQueryString, sqlite_conn.ConnectionString); // sqliteCommandBuilder = new SQLiteCommandBuilder(sqliteDataAdapter); dataTable = new DataTable(); sqliteDataAdapter.Fill(dataTable); sqlite_conn.Close(); sqliteDataAdapter.Dispose(); return dataTable; } How can I fix this?]]> Hi, I'm trying to fill some text boxes and a listbox. I have this code:

VB.NET Code:
  1. span style="color: #808080;">"next_record""id""id"];
  2.     }
  3. }

This gives me error: 'There is no row at position 2.' for this line: if (!DBNull.Value.Equals(dataTable.Rows[currentId]["id"])) When I debug, I see that the data table has 2 rows and currentId equals to 2.

The data table is set like this

VB.NET Code:
  1. span style="color: #808080;">"database.sqlite""SELECT
  2.                              S.id,
  3.                              P.txt_press,
  4.                              CA.txt_category,
  5.                              S.txt_repertory_no,
  6.                              T.txt_taken_from,
  7.                              S.txt_revision_date1,
  8.                              S.txt_revision_date2,
  9.                              S.txt_revision_date3,
  10.                              R.txt_region,
  11.                              PR.txt_prepared_by,
  12.                              S.txt_research_date1,
  13.                              S.txt_research_date2,
  14.                              S.txt_research_date3,
  15.                              SP.txt_source_by,
  16.                              M.txt_measure,
  17.                              S.txt_time,
  18.                              C.txt_compiled_by,
  19.                              S.txt_compilation_date,
  20.                              N.txt_notation_by,
  21.                              S.txt_mp3_paths,
  22.                              A.txt_artist,
  23.                              S.txt_song_name,
  24.                              S.txt_lyrics,
  25.                              S.image1,S.image2,S.image3,S.image4,S.image5,S.image6,S.image7,
  26.                              S.image8,S.image9, S.image10,S.image11,S.image12,S.image13,S.image14
  27.                          FROM tbl_sheet S
  28.                          LEFT JOIN tbl_category CA
  29.                              ON CA.id = S.int_category_id
  30.                          LEFT JOIN tbl_press P
  31.                              ON P.id = S.int_press_id
  32.                          LEFT JOIN tbl_taken_from T
  33.                              ON T.id = S.int_taken_from_id
  34.                          LEFT JOIN tbl_prepared_by PR
  35.                              ON PR.id = S.int_prepared_by_id
  36.                          LEFT JOIN tbl_region R
  37.                              ON R.id = S.int_region_id
  38.                          LEFT JOIN tbl_source_by SP
  39.                              ON SP.id = S.int_source_by_id
  40.                          LEFT JOIN tbl_measure M
  41.                              ON M.id = S.int_measure_id
  42.                          LEFT JOIN tbl_compiled_by C
  43.                              ON C.id = S.int_compiled_by_id
  44.                          LEFT JOIN tbl_notation_by N
  45.                              ON N.id = S.int_notation_by_id
  46.                          LEFT JOIN tbl_artist A
  47.                              ON A.id = S.int_artist_id;"

How can I fix this? ]]>
Database Development nikel http://www.vbforums.com/showthread.php?863847-Database-Error-There-is-no-row-at-position-2
Getting All Values Using Where Clause in SQLite http://www.vbforums.com/showthread.php?863843-Getting-All-Values-Using-Where-Clause-in-SQLite&goto=newpost Sun, 24 Jun 2018 18:12:35 GMT Hi, I have a database and I want to get all records (regarding given argument) using the command ExecuteNonQuery. Is this possible? Hi, I have a database and I want to get all records (regarding given argument) using the command ExecuteNonQuery. Is this possible? ]]> Database Development nikel http://www.vbforums.com/showthread.php?863843-Getting-All-Values-Using-Where-Clause-in-SQLite