perform a check on last set of rows in database using a vb.net

[Mikey.VB]

I have two databases , first db is called PATIENTS ( sql) . second is called Blood

my code checks in database BLOOD table TestDetails , for matching SampleID and then insert the result/results for that SampleID in database PATIENTS in table patienttest ( the two databases have a common column). so my code works just fine . however , for now I have to manually enter the sampleID, i need the code to automatically check for new rows or say last 20 rows in the BLOOD/TestDetails table and then perform the rest of the code as I gave it.


* by automatically i do not mean without any user interaction , clicking a button without having to type the SAMPLE ID is what i need

Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class Form1
Dim provider As String
Dim datafile As String
Dim patients_tstid As Integer
Dim con As String
Public mycon As OleDbConnection = New OleDbConnection
Public dr_acs As OleDbDataReader
Dim da As OleDbDataAdapter
Dim ds As DataSet
Dim tables As DataTableCollection
Dim source1 As New BindingSource




Dim connection As New SqlConnection("Data Source=Localhost;Initial Catalog=Patients;Integrated Security=True")

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
datafile = "C:\Users\Ahm\Desktop\Database\Backup\DBFILE\Blood.mdb;Jet OLEDbatabase Password=B00@BloodTest"
con = provider & datafile
mycon.ConnectionString = con
End Sub


Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
mycon.Open()
TextBox2.Clear()

TextBox3.Clear()
ds = New DataSet
tables = ds.Tables
da = New OleDbDataAdapter("SELECT testtime, testresult,itemid FROM TestDetail WHERE SampleID = " & TextBox1.Text & " and TestTime Between Date() And DateAdd(d,1,Date())", mycon)
da.Fill(ds, "TestDetail") Dim view As New DataView(tables(0))
source1.DataSource = view
DataGridView1.DataSource = view
Dim cmd As OleDbCommand = New OleDbCommand("SELECT testtime, testresult,itemid FROM TestDetail WHERE SampleID = " & TextBox1.Text & " and TestTime Between Date() And DateAdd(d,1,Date())", mycon)
dr_acs = cmd.ExecuteReader
While dr_acs.Read()

TextBox2.Text = dr_acs("ItemID").ToString


Select Case TextBox2.Text

Case "3"

patients_tstid = 12
Case "5"

patients_tstid = 9

Case "9"

patients_tstid = 30
Case "10"

patients_tstid = 175
Case "11"

patients_tstid = 31
Case "12"

patients_tstid = 10
Case "13"

patients_tstid = 8
Case "14"

patients_tstid = 6
Case "16"

patients_tstid = 177
Case "19"

patients_tstid = 91
Case "20"

patients_tstid = 31
Case "37"

patients_tstid = 33
Case "28"

patients_tstid = 409
Case "29"

patients_tstid = 400
Case "30"

patients_tstid = 77
Case "33"

patients_tstid = 26
Case "34"

patients_tstid = 365
Case "35"

patients_tstid = 79
Case "36"

patients_tstid = 78
Case "1"

patients_tstid = 1 - 3 - 692
Case "6"
"
patients_tstid = 16 - 734
Case "7"

patients_tstid = 15 - 733
Case "8"

patients_tstid = 11 - 24
Case "17"

patients_tstid = 7 - 720
Case "18"

patients_tstid = 18 - 689
Case "22"

patients_tstid = 408 - 681
Case "27"

patients_tstid = 691 - 90
Case "32"

patients_tstid = 349 - 360
Case Else
Console.WriteLine("You typed something else")
End Select

Dim strCommand As String = "UPDATE patienttest SET [resultt] = " & dr_acs("TestResult").ToString & " WHERE patientid = (select patienttest.patientid from patienttest INNER JOIN patientinfo ON patienttest.patientid = patientinfo.patientid WHERE labid = " & TextBox1.Text & " and testcode = " & patients_tstid & " and patientinfo.requestdate = (select top 1 requestdate from patienttest order by requestdate DESC )) and testcode = " & patients_tstid & ""
Dim command As SqlCommand = New SqlCommand(strCommand, connection)
command.CommandType = CommandType.Text

MsgBox(strCommand)

connection.Open()
If (command.ExecuteNonQuery().Equals(1)) Then
MsgBox("Information stored in database")
connection.Close()
Else
MsgBox("Not stored in database")
connection.Close()
End If




End While

mycon.Close()


End Sub





Thanks

Continue reading...