[ツリー表示へ] [ワード検索] [Home]
タイトル ： 一応の解決と更なる問題 投稿日 ： 2008/01/04(Fri) 17:46 投稿者 ： ダダ
| 一応これでエクセルのcsv形式ファイルから、.sdf型のデータが作成できます。 しかし、いざ実行すると最大九行分しかできませんでした。 どうすれば良いか分かりません。 どなたか・・・お助けを・・・・ Imports System.io Imports System.Data.SqlClient Imports System.Text Imports System.data.sqlserverce Imports System.Data Imports Microsoft.VisualBasic.FileIO Public Class Form1 Public Sub New() ' この呼び出しは、Windows フォームデザイナで必要です。 InitializeComponent() ' InitializeComponent() 呼び出しの後で初期化を追加します。 End Sub Private Function CreateDataTable() As DataTable Dim dt As New DataTable With dt .Columns.Add("単語", Type.GetType("System.String")) .Columns.Add("意味", Type.GetType("System.String")) .Columns.Add("品詞", Type.GetType("System.String")) .Columns.Add("例文", Type.GetType("System.String")) .Columns.Add("例文訳", Type.GetType("System.String")) End With Return dt End Function Private Function GetZipCodeDataTable(ByVal csvFilePath As String) As DataTable Dim dt As DataTable = Me.CreateDataTable Dim row As DataRow Using sr As New StreamReader(csvFilePath, Encoding.GetEncoding(932)) Dim strLine As String Dim strFields() As String strLine = sr.ReadLine() While (strLine <> "") row = dt.NewRow strFields = Split(strLine, ",") For i As Integer = 0 To strFields.Length - 1 row.Item(i) = strFields(i).Replace("""", "") Next dt.Rows.Add(row) strLine = sr.ReadLine() End While End Using Return dt End Function Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim dt As DataTable = GetZipCodeDataTable("D:\User\nomura\asa100.csv") dt.TableName = "kakacsv" DataGridView2.DataSource = dt End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click 'sdfの作成 If System.IO.File.Exists("C:\snapshot\MySqlCe.sdf") Then System.IO.File.Delete("C:\snapshot\MySqlCe.sdf") End If Dim eng As SqlCeEngine = New SqlCeEngine( _ "Data Source=C:\snapshot\MySqlCe.sdf") eng.CreateDatabase() Dim cn As SqlCeConnection = New SqlCeConnection( _ "Data Source=C:\snapshot\MySqlCe.sdf") cn.Open() Dim sql As String = "CREATE TABLE Customers " & _ "(単語nchar(200) NULL, " & _ "意味nchar(200) NULL," & _ "品詞nchar(200) NULL," & _ "例文nchar(1000) NULL," & _ "例文訳nchar(1000) NULL)" Dim cmd As SqlCeCommand = New SqlCeCommand(sql, cn) cmd.CommandType = CommandType.Text cmd.ExecuteNonQuery() cn.Close() Dim cn2 As New SqlCeConnection("Data Source=C:\snapshot\MySqlCe.sdf") Dim sqlcm As SqlCeCommand = cn.CreateCommand Dim adapter As New SqlCeDataAdapter(sqlcm) Dim table As New DataTable sqlcm.CommandText = "select * from Customers" adapter.Fill(table) DataGridView4.DataSource = table End Sub Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click 'バルクコピー Using bc As New SqlBulkCopy("Data Source=KIS-C01; Initial Catalog=SQLMobile;Integrated Security=True") With bc .DestinationTableName = "NAKAMURA" Dim dt As DataTable = GetZipCodeDataTable("D:\User\nomura\asa100.csv") .WriteToServer(dt) End With End Using End Sub Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click 'バルク後のgrid表示 Dim cn2 As New SqlConnection("Data Source=KIS-C01 ;Initial Catalog=SQLMobile;Integrated Security=True") Dim sqlcm2 As SqlCommand = cn2.CreateCommand Dim Adapter As New SqlDataAdapter(sqlcm2) Dim Table As New DataTable sqlcm2.CommandText = "SELECT * FROM NAKAMURA" Adapter.Fill(Table) DataGridView3.DataSource = Table Table.Dispose() Adapter.Dispose() sqlcm2.Dispose() cn2.Dispose() End Sub Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click 'Dim cn As New SqlCeConnection("Data Source=C:\snapshot\MySqlCeTest.sdf") 'Dim sqlcm As SqlCeCommand = cn.CreateCommand '入れ子 Dim dt As DataTable = DirectCast(DataGridView4.DataSource, DataTable) Dim xx, yy As Integer Dim db As New SqlConnection("Data Source=KIS-C01 ;Initial Catalog=SQLMobile;Integrated Security=True") Dim cmnd As SqlCommand = db.CreateCommand cmnd.CommandText = "SELECT * FROM NAKAMURA" db.Open() Dim dbrd As SqlDataReader dbrd = cmnd.ExecuteReader() 'For xx = 0 To dbrd.FieldCount - 1 'DataGridView4.Columns.Add("単語" & xx + 1, dbrd.GetName(xx)) 'Next yy = 0 Do While dbrd.Read dt.Rows.Add() For xx = 0 To dbrd.FieldCount - 1 DataGridView4.Item(xx, yy).Value = dbrd(xx).ToString() Next yy = yy + 1 Loop db.Close() 'dasiko ' Dim Table As DataTable = DirectCast(DataGridView3.DataSource, DataTable) ' Dim row As DataRow ' row = dt.NewRow ' Dim i As Integer ' i = 2 'For i = 0 To 3 ' dt.Rows(0)(1) = Table.Rows(i)("意味") ' row(1)("単語") = Table.Rows(m)("意味") ' Console.WriteLine(row(i)("単語")) ' dt.Rows.Add(row) DataGridView1.DataSource = dt ' Next i 'For Each Row As DataRow In Table.Rows 'Dim SQL As String = "" 'Select Case row.RowState 'Case DataRowState.Added ' Sql = "INSERT INTO Customers VALUES (" ' Sql &= row("単語") & ", " ' Sql &= "'" & row("品詞") & "', " 'Sql &= "'" & row("意味") & "', " ' S() 'ql &= row("例文") & ", " 'Sq() 'l &= "'" & row("例文訳") & "' " 'Sql() ' &= ")" ' ' Case Else ' Continue For ' End Select ' sqlcm.CommandText = Sql 'sqlcm.ExecuteNonQuery() ' cn.Close() ' Next 'Table.Dispose() 'sqlcm.Dispose() ' cn.Dispose() End Sub Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click Dim cn2 As New SqlCeConnection("Data Source=C:\snapshot\MySqlCe.sdf") Dim sqlcm As SqlCeCommand = cn2.CreateCommand Dim Table As DataTable = DirectCast(DataGridView4.DataSource, DataTable) For Each Row As DataRow In Table.Rows Dim SQL As String = "" Select Case Row.RowState Case DataRowState.Added SQL = "INSERT INTO Customers VALUES (" Sql &= "'" & Row("単語") & "', " SQL &= "'" & Row("意味") & "', " SQL &= "'" & Row("品詞") & "', " Sql &= "'" & Row("例文") & "', " Sql &= "'" & Row("例文訳") & "'" Sql &= ")" Case Else Continue For End Select sqlcm.CommandText = Sql cn2.Open() sqlcm.ExecuteNonQuery() cn2.Close() Next Table.Dispose() sqlcm.Dispose() cn2.Dispose() End Sub End Class |
をクリックするとツリー全体を一括表示します） - ｃｓｖのデータを取得したい・・４徹目です（ＴＴ） - ダダ 07/12/11-01:29No.6655