Am using excel sheet as DataBase but i was unable to save the data from the textboxes to excel sheet.
Public Class HL7
Dim cn As New OleDbConnection
Dim cm As New OleDbCommand
Dim da As OleDbDataAdapter
Dim da2 As OleDbDataAdapter
Dim dt As New DataTable
Dim dt2 As New DataTable
Dim dtTemp As New DataTable
Private Sub Hl7_Load(sender As Object, e As EventArgs) Handles MyBase.Load
cn.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\psave\New Folder\save.xlsx;Extended Properties=Excel 8.0;"
cn.Open()
FillDataGridView1("select ID, [Family Name], [Given Name], Gender, DOB, [Street Name], [House Nr], Postcode, City FROM [Sheet1$] WHERE ID IS NOT NULL AND [Family Name] IS NOT NULL AND [Given Name] IS NOT NULL AND Gender IS NOT NULL AND DOB IS NOT NULL AND [Street Name] IS NOT NULL AND [House Nr] IS NOT NULL AND Postcode IS NOT NULL AND City IS NOT NULL ")
FillDataGridView2("select [Procedure Code], [Procedure name] from[Sheet2$] WHERE [Procedure Code] IS NOT NULL AND [Procedure name] IS NOT NULL ")
End Sub
Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click
da = New OleDbDataAdapter("select ID from [Sheet1$]", cn)
dtTemp.Clear()
da.Fill(dtTemp)
With cm
.Connection = cn
.CommandText = "insert into [Sheet1$]values('" & TxtId.Text & "','" & TxtFamilyname.Text & "','" & TxtGivenname.Text & "','" & TxtGender.Text & "','" & TxtDob.Text & "','" & TxtStreet.Text & "','" & TxtHouse.Text & "','" & TxtPostcode.Text & "','" & TxtCity.Text & "')"
.ExecuteNonQuery()
End With
FillDataGridView1("select ID, [Family Name], [Given Name], Gender, DOB, [Street Name], [House Nr], Postcode, City FROM [Sheet1$] WHERE ID IS NOT NULL AND [Family Name] IS NOT NULL AND [Given Name] IS NOT NULL AND Gender IS NOT NULL AND DOB IS NOT NULL AND [Street Name] IS NOT NULL AND [House Nr] IS NOT NULL AND Postcode IS NOT NULL AND City IS NOT NULL ")
FillDataGridView2("select * from [Sheet2$]")
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, Text)
Return
End Try
MsgBox("Succefully Saved!", MsgBoxStyle.Information, Text)
End Sub