Click here to Skip to main content
15,894,907 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Am using excel sheet as DataBase but i was unable to save the data from the textboxes to excel sheet.

VB
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

VB
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()
        'To fille the datagridview from Database
        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
Posted
Comments
CHill60 9-Jun-15 5:38am    
Any errors reported? Are there definitely values in those textboxes? What is the content of CommandText when you've executed that line in the debugger?

1 solution

The line
.CommandText = "insert into [Sheet1$]values('" & TxtId.Text & "','" &...

should read
.CommandText = "insert into [Sheet1$] values('" & TxtId.Text & "','" &
I.e. there has to be at least one space between [Sheet1$] and values
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900