无法保存在Excel工作表中 [英] Unable to save in Excel sheet

问题描述

我使用Excel工作表作为DataBase，但我无法将文本框中的数据保存到excel工作表。

 公开 类 HL7 
 
  Dim  cn 作为 新 OleDbConnection 
  Dim  cm 作为 新 OleDbCommand 
  Dim  da  As  OleDbDataAdapter 
  Dim  da2 作为 OleDbDataAdapter 
  Dim  dt 作为 新 DataTable 
  Dim  dt2 作为 新 DataTable 
  Dim  dtTemp  As  新 DataTable 

 私人  Sub  Hl7_Load （发件人作为 对象，e 作为 EventArgs ）句柄  MyBase  .Load 
 
 cn.ConnectionString =   provider = Microsoft.ACE.OLEDB.12.0; Data Source = C：\ psave \New Folder\save.xlsx;扩展属性= Excel 8.0; 
 cn.Open（）
 ' 填写来自数据库的datagridview  
 FillDataGridView1（ 选择ID，[姓氏]，[给定名称] ，性别，DOB， [街道名称]，[House Nr]，邮政编码，城市FROM [Sheet1 $] WHERE ID不为空且[系列名称]不为空且[给定名称]为非NULL且性别不为空且DOB不为空且[街道名称] IS非空且[House Nr]不为空且邮政编码不为空且城市不为空）
 FillDataGridView2（ 从[Sheet2 $]中选择[Procedure Code]，[Procedure name] WHERE [Procedure Code] IS NOT NULL且[Procedure name] IS NOT NULL）
 结束  Sub  
 
 
 私有  Sub  BtnSave_Click（发件人作为 对象，e  As  EventArgs）句柄 BtnSave.Click 
 
 da = 新 OleDbDataAdapter（ 从[Sheet1 $]中选择ID，cn）
 dtTemp.Clear（）
 da。填充（dtTemp）
 
 使用 cm 
 .Connection = cn 
 .CommandText =  插入[Sheet1 $]值（'& TxtId.Text&  '，'& TxtFamilyname.Text&  '，'& TxtGivenname.Text&  '，'& TxtGender.Text&  '，'& TxtDob.Text&  '，'& TxtStreet.Text&  '，'& TxtHouse.Text&  '，'& TxtPostcode.Text&  '，'& TxtCity.Text&  '） 
 .ExecuteNonQuery（）
 结束 使用 
 
 FillDataGridView1（ < span class =code-string> select ID，[Family Name]，[Given Name]，Gender，DOB，[Street Name]，[House Nr]，Postcode，City FROM [Sheet1 $] WHERE ID不是NULL [系列名称] IS非空且[给定名称]不为空且性别不为空且DOB不为空且[街道名称]不为空且[House Nr]不为空且邮政编码不为空且城市不为NULL）
 FillDataGridView2（  select * from [Sheet2 $] ）
  Catch  ex  As 异常
 MsgBox（ex.Message，MsgBoxStyle.Critical ，文本）
 返回 
 结束 尝试 
 MsgBox（ 成功保存！，MsgBoxStyle.Information，Text）
 结束  Sub  

解决方案

WHERE ID不为空且[姓氏]是非空且[给定名称]不是空并且性别不是空并且DOB不是空并且[街道名称]不是空并且[房子Nr]不是空并且邮政编码不是空并且城市不是空）
FillDataGridView2（ 从[Sheet2

WHERE [Procedure Code] IS NOT NULL且[Procedure name] IS NOT NULL）
End < span class =code-keyword> Sub


Private Sub BtnSave_Click（发件人作为 对象，e As EventArgs）句柄 BtnSave.Click

da = < span class =code-keyword>新 OleDbDataAdapter（ 从[Sheet1

中选择ID]

，cn）
dtTemp.Clear（）
da.Fill（dtTemp）

使用 cm
.Connection = cn
.CommandText = insert into [表Sheet 1

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()
        '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

解决方案

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

这篇关于无法保存在Excel工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！