如何从数据网格视图中获取某些数据并存储到VB.NET中的ms访问 [英] How to get certain data from data grid view and store into ms access in VB.NET

问题描述

如何从数据网格视图中获取某些数据并存储到vb.net中的ms访问？

我想存储第2,4和5列的数据



我尝试过：



Imports System.Data.OleDb

Public Class ViewConsultationAndMakeAppointment

Public m As New MsgBoxResult

Dim cnnOLEDB As New OleDbConnection

Dim cmdCheck As New OleDbCommand

Dim cmdSearch As New OleDbCommand

Dim cmdAdd As New OleDbCommand

Dim con =Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C：\ Users \ Siminh\Desktop\SDP\University.accdb;

Private Sub btnView_Click（sender as Object，e As EventArgs）处理btnView.Click

cnnOLEDB.Open（）

如果txtName.Text< ;> 然后

Dim dr As OleDbDataReader

Dim str As String

str =从[Lecturer]中选择*，其中[讲师姓名] = ''& txtName.Text& ';

Dim cmd作为OleDbCommand =新OleDbCommand（str，cnnOLEDB）

Dim ds作为新数据集

Dim dt作为新DataTable

ds.Tables.Add（dt）

Dim da As New OleDbDataAdapter

dr = cmd.ExecuteReader

如果dr.Read（）那么

da =新OleDbDataAdapter（从*咨询中选择*，其中[讲师姓名] ='& txtName.Text&'和[Date] =' & DateTimePicker1.Text&'，cnnOLEDB）

da.Fill（dt）

DataGridView1.DataSource = dt.DefaultView

DataGridView1.Show（）

Else

MsgBox（找不到记录......）

结束如果

dr.Close（）

Else

MsgBox（请插入讲师姓名。，MsgBoxStyle.Information + MsgBoxStyle.OkOn ly，Field All the Data）

结束如果

cnnOLEDB.Close（）

End Sub

Private Sub btnConfirm_Click（sender as Object，e As EventArgs）处理btnConfirm.Click

cnnOLEDB.Open（）

cmdCheck.CommandText =select * from [Lecturer] where [讲师姓名] ='& txtName.Text& ';

cmdCheck.Connection = cnnOLEDB

Dim dr As OleDbDataReader = cmdCheck.ExecuteReader（）

如果MsgBox（你确定吗？添加信息？，MsgBoxStyle.Question + MsgBoxStyle.YesNo，Create）= MsgBoxResult.Yes然后

如果txtAppointment.Text<> 和txtContact.Text<> 和txtEmail.Text<> 和txtReason.Text<> 和txtName.Text<> 那么

如果dr.Read = True那么

cmdAdd.CommandText =插入[约会]值（'& txtAppointment.Text&'， '& txtName.Text&'，'& lblStuID.Text&'，'& lblName.Text&'，'& DateTimePicker1.Text&'，' & txtReason.Text&'，'& txtEmail.Text&'，'& txtContact.Text&'）;

cmdAdd.CommandType = CommandType。文字

cmdAdd.Connection = cnnOLEDB

cmdAdd.ExecuteNonQuery（）

MsgBox（创建成功！）

LecturerMenuForm.Show（）

txtContact.Clear（）

txtEmail.Clear（）

txtReason.Clear（）

txtName.Clear（）

txtAppointment.Clear（）
Me.Hide（）

否则

MsgBox（没有这样的ID ......）

结束如果

dr.Close（）

Else

MsgBox（请插入所有数据。，MsgBoxStyle.Information + MsgBoxStyle.OkOnly，现场所有数据）

结束如果

结束如果

cnnOLEDB.Close（）

End Sub

How to get certain data from data grid view and store into ms access in vb.net?
I wanted to store data form column 2, 4 and 5

What I have tried:

Imports System.Data.OleDb
Public Class ViewConsultationAndMakeAppointment
Public m As New MsgBoxResult
Dim cnnOLEDB As New OleDbConnection
Dim cmdCheck As New OleDbCommand
Dim cmdSearch As New OleDbCommand
Dim cmdAdd As New OleDbCommand
Dim con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Siminh\Desktop\SDP\University.accdb;"
Private Sub btnView_Click(sender As Object, e As EventArgs) Handles btnView.Click
cnnOLEDB.Open()
If txtName.Text <> "" Then
Dim dr As OleDbDataReader
Dim str As String
str = "Select * from [Lecturer] where [Lecturer Name]='" & txtName.Text & "';"
Dim cmd As OleDbCommand = New OleDbCommand(str, cnnOLEDB)
Dim ds As New DataSet
Dim dt As New DataTable
ds.Tables.Add(dt)
Dim da As New OleDbDataAdapter
dr = cmd.ExecuteReader
If dr.Read() Then
da = New OleDbDataAdapter("Select * from [Consultation] where [Lecturer Name]='" & txtName.Text & "'and [Date]='" & DateTimePicker1.Text & "'", cnnOLEDB)
da.Fill(dt)
DataGridView1.DataSource = dt.DefaultView
DataGridView1.Show()
Else
MsgBox("Record Not Found...")
End If
dr.Close()
Else
MsgBox("Please insert lecturer name.", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Field All the Data")
End If
cnnOLEDB.Close()
End Sub
Private Sub btnConfirm_Click(sender As Object, e As EventArgs) Handles btnConfirm.Click
cnnOLEDB.Open()
cmdCheck.CommandText = "select * from [Lecturer] where [Lecturer Name]='" & txtName.Text & "';"
cmdCheck.Connection = cnnOLEDB
Dim dr As OleDbDataReader = cmdCheck.ExecuteReader()
If MsgBox("Are you sure to add the information?", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "Create") = MsgBoxResult.Yes Then
If txtAppointment.Text <> "" And txtContact.Text <> "" And txtEmail.Text <> "" And txtReason.Text <> "" And txtName.Text <> "" Then
If dr.Read = True Then
cmdAdd.CommandText = "Insert into [Appointment] Values('" & txtAppointment.Text & "','" & txtName.Text & "', '" & lblStuID.Text & "', '" & lblName.Text & "','" & DateTimePicker1.Text & "','" & txtReason.Text & "','" & txtEmail.Text & "','" & txtContact.Text & "');"
cmdAdd.CommandType = CommandType.Text
cmdAdd.Connection = cnnOLEDB
cmdAdd.ExecuteNonQuery()
MsgBox("Create Successful!")
LecturerMenuForm.Show()
txtContact.Clear()
txtEmail.Clear()
txtReason.Clear()
txtName.Clear()
txtAppointment.Clear()
Me.Hide()
Else
MsgBox("No such ID...")
End If
dr.Close()
Else
MsgBox("Please insert all the data.", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Field All the Data")
End If
End If
cnnOLEDB.Close()
End Sub

推荐答案

Loop through the DataGridView Rows and insert the data like this;

Imports System.Data
Imports System.Data.OleDb

Public Class Form1
        'these are variables for connecting to ms Access Database
    Dim con As New OleDbConnection
    Dim cmd As New OleDbCommand
    Dim sqldr As OleDbDataReader
    Dim sqlda As New OleDbDataAdapter
    Dim dt As New DataTable 'Data tables that hold data to be inserted to dataset
    Dim conStrng As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\[yourDatabaseName].accdb;Persist Security Info=True;Jet OLEDB:Database Password=[yourAccessPassword]"
    
  'this function will just be called to execute the function
   Private Function ExecuteSqlStatement(ByVal sqlStatement As String) As DataTable
        Try
            Dim con As New OleDbConnection(strsql)
            Dim sqlda As New OleDbDataAdapter(sqlStatement, con)
            Dim sqlCmdBuilder As New OleDbCommandBuilder(sqlda)
            dt.Reset()
            sqlda.Fill(dt)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return dt
    End Function

    'now this is how you insert the data From DataGriedView to database
     Sub saveToDB()
       'Loop through the datagriedview1 rows
        For i=0 To DataGriedView1.Rows.Count-1
            dim sqlStatement As String="INSERT INTO [yourTableName] ([fieldName1],[fieldName2])VALUES('" & DataGriedView1.Rows(i).Cells("[column1Name].Value.ToString() &"','" & DataGriedView1.Rows(i).Cells("[column2Name].Value.ToString() &"'")"
        'Now Call then Executing function and pass in the string
         ExecuteSqlStatement(sqlStatement)
        Next
     End Sub

这篇关于如何从数据网格视图中获取某些数据并存储到VB.NET中的ms访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！