Database

Joe_P wrote:

Join @BatchHeader bh1 with (readpast) on bhr.Child = bh1.bhid
Join @BatchHeader bh2 with (readpast) on bhr.ParentId = bh2.bhid
Where
bh1.Status = 10 -- Child batch status
And bh2.Status = 10 -- Header batch status

Select * 
From @BatchHeaderReference bhr
	INNER Join @BatchHeader bh1 on bhr.Child = bh1.bhid
WHERE bhr.child IN (SELECT bhid FROM @BatchHeader WHERE status = 10)

select distinct x.parentid 
from (
select 
bhr.* , 
(select max(bh.status) from @BatchHeader bh where bh.bhid=bhr.child) status, 
(select count(*) from @BatchHeaderReference bhr2 where bhr2.parentid=bhr.parentid) cnt, 
(select count(*) from @BatchHeaderReference bhr3 inner join @BatchHeader bh3 on bh3.bhid=bhr3.child where bh3.status=10 and bhr3.parentid=bhr.parentid) truecount 
from @BatchHeaderReference bhr
) x
 where x.cnt=x.truecount 

select distinct x.parentid  
from (
select 
bhr.* , 
(select max(bh.status) from @BatchHeader bh where bh.bhid=bhr.child) status, 
(select count(*) from @BatchHeaderReference bhr2 where bhr2.parentid=bhr.parentid) cnt, 
(select count(*) from @BatchHeaderReference bhr3 inner join @BatchHeader bh3 on bh3.bhid=bhr3.child where bh3.status=10 and bhr3.parentid=bhr.parentid) truecount, 
 (select max(bh.status) from @BatchHeader bh where bh.bhid=bhr.parentid) pstatus
from @BatchHeaderReference bhr  
  
) x
 where x.cnt=x.truecount 
 and x.pstatus=10 

SELECT BHID 'PARENTID' FROM @BatchHeader BHR1

WHERE BHR1.STATUS = 10

UNION


SELECT PARENTID  FROM @BatchHeaderReference BHR

INNER JOIN @BatchHeader BH 

ON BH.BHID = BHR.CHILD 

AND BH.STATUS = 10

nedusmile wrote:

Data_Modification (mod_data_id,Patient_ID, Date_Collected, Wrong_Data_Entry_Date, Data_Field, Provider)

Data_Modification (ModDataID,PatientID, DateCollected, WrongDataEntryDate, DataField, Provider)

Try

    LoadData()

    ' Configure and execute the command.
    comd = New System.Data.SqlClient.SqlCommand("insert into Data_Modification (Patient_ID, Date_Collected, Wrong_Data_Entry_Date, Data_Field, Provider)" & _
        " values (@PatRep_ID, @DateCol, @WrngDataDate, @Data_Field, @Provider)", con)

    'comd.Connection = con
    Dim paraPatId As SqlParameter = comd.Parameters.Add("@PatRep_ID", repDatPatID.Text)
    Dim paraDte As SqlParameter = comd.Parameters.Add("@DateCol", repDatColTmePikar2.Value.ToShortDateString)
    Dim paraWrngD As SqlParameter = comd.Parameters.Add("@WrngDataDate", repWDatEDaTmePika.Value.ToShortDateString)
    Dim paraDaFld As SqlParameter = comd.Parameters.Add("@Data_Field", repDatDFldCmBx.Text)
    Dim paraProv As SqlParameter = comd.Parameters.Add("@Provider", repDatProvTbx.Text)
    comd.ExecuteNonQuery()

Catch ex As SqlException
    MsgBox(ex.Message.ToString())
Finally
    con.Close()
End Try


Try

    LoadData()
    comd = New System.Data.SqlClient.SqlCommand("select distinct scope_identity()as Integer from Data_Modification", con)
    Dim key As Integer = comd.ExecuteScalar
    con.Close()


    LoadData()

    ' Configure and execute the command.
    comd = New System.Data.SqlClient.SqlCommand("insert into Modification_copy (data_mod_ID,Date_Collected, Data_Attribute,Correct_data_Value)" & _
        " values (@DataModIdD,@retDateCol, @DatAtt, @CorDaVal)", con)

    'comd.Connection = con
    Dim paraModID As SqlParameter = comd.Parameters.Add("@DataModIdD", key)
    Dim paraDteCol As SqlParameter = comd.Parameters.Add("@retDateCol", repDatColTmePikar2.Value.ToShortDateString)
    Dim paraDAtt As SqlParameter = comd.Parameters.Add("@datAtt", repMorCmBx.Text)
    Dim paraCorDVa As SqlParameter = comd.Parameters.Add("@corDaVal", repMorTxb.Text)


      comd.ExecuteNonQuery()
    If MsgBox("Report successful!. Do you want to report more Data?", MsgBoxStyle.YesNo, "Confirmation!!!") _
    = Windows.Forms.DialogResult.Yes Then
        repMorDetPnl.Hide()
        repDatTabPg.Show()

    Else
        repMorDetPnl.Hide()
        vwPatient.Show()
    End If

Catch ex As SqlException
    MsgBox(ex.Message.ToString())
Finally
    con.Close()
End Try

  Try

            LoadData()  ' function containing connection string

            ' Configure and execute the command.
'the scope_identity() is inserted in the query that will insert the data
'this will retrieve the last auto-generated number from the first table.
            comd = New System.Data.SqlClient.SqlCommand("insert into Data_Modification (Patient_ID, Date_Collected, Wrong_Data_Entry_Date, Data_Field, Provider)" & _
                " values (@PatRep_ID, @DateCol, @WrngDataDate, @Data_Field, @Provider) select scope_identity()", con)

    'Declare and Initialize parameter that is passed to the query strings
            Dim paraPatId As SqlParameter = comd.Parameters.Add("@PatRep_ID", repDatPatID.Text)
            Dim paraDte As SqlParameter = comd.Parameters.Add("@DateCol", repDatColTmePikar2.Value.ToShortDateString)
            Dim paraWrngD As SqlParameter = comd.Parameters.Add("@WrngDataDate", repWDatEDaTmePika.Value.ToShortDateString)
            Dim paraDaFld As SqlParameter = comd.Parameters.Add("@Data_Field", repDatDFldCmBx.Text)
            Dim paraProv As SqlParameter = comd.Parameters.Add("@Provider", repDatProvTbx.Text)
            

 'command object is used to call the ExecuteScalar() and returns may be a 
'decimal value which is converted to integer and assigned to a variable.

            LoadData()
            
            Dim key As Integer = Convert.ToInt32(comd.ExecuteScalar())
            con.Close()


            LoadData()

            ' this is an insertion to the second table, the integer value (key) 
'Obtained above is passed on to the second query as foreign key (data_mod_ID) to the second table

            comd = New System.Data.SqlClient.SqlCommand("insert into Modification_copy (data_mod_ID,Date_Collected, Data_Attribute,Correct_data_Value)" & _
                " values (@DataModIdD,@retDateCol, @DatAtt, @CorDaVal)", con)

            'comd.Connection = con
            Dim paraModID As SqlParameter = comd.Parameters.Add("@DataModIdD", key.ToString)
            Dim paraDteCol As SqlParameter = comd.Parameters.Add("@retDateCol", repDatColTmePikar2.Value.ToShortDateString)
            Dim paraDAtt As SqlParameter = comd.Parameters.Add("@datAtt", repMorCmBx.Text)
            Dim paraCorDVa As SqlParameter = comd.Parameters.Add("@corDaVal", repMorTxb.Text)

            ' Load the DataTable.
            'datadapt = New System.Data.SqlClient.SqlDataAdapter(comd)
            'datadapt.Fill(dSet)
            'dSet.Tables.Add(dTab)

            ' Dim rowAffected As Integer = 0

            ' cmd.Connection = cn

            ' rowAffected = 
            comd.ExecuteNonQuery()
            If MsgBox("Report successful!. Do you want to report more Data?", MsgBoxStyle.YesNo, "Confirmation!!!") _
            = Windows.Forms.DialogResult.Yes Then
                repMorDetPnl.Hide()
                repDatTabPg.Show()

            Else
                repMorDetPnl.Hide()
                vwPatient.Show()
            End If

        Catch ex As SqlException
            MsgBox(ex.Message.ToString())
        Finally
            con.Close()
        End Try 




Public Sub LoadData()
        dSet = New DataSet
        dTab = New DataTable
        con = New System.Data.SqlClient.SqlConnection("Data Source=192.168.3.101,1433;Initial Catalog=DataBaseName;User ID=Doctor;Password=gudone;")
        con.Open()
    End Sub

nedusmile wrote:

" values (@PatRep_ID, @DateCol, @WrngDataDate, @Data_Field, @Provider) select scope_identity()", con)

NNR_Noga wrote:

i want acually code

using EnterpriseDB.EDBClient;

string strConnectionString = ConfigurationSettings.AppSettings
	["DB_CONN_STRING"];
	EDBConnection conn = new EDBConnection(strConnectionString);
	
	try {
 	   conn.Open();
	   Response.Write("Connection opened successfully");
	}
	
	catch(EDBException exp) { exp.ToString(); }
	
	finally {
		conn.Close();
	}