Link to home
Start Free TrialLog in
Avatar of jtammyg
jtammyg

asked on

export text from datagridview to txt file not correct

Hi!

I am trying to export only the visible columns from a datagridview in my windows form in VB 2008.

I am attaching the text file generated.

Should't it be no comma after the first row where the headers are?

Also should there be a comma at the last row?

When I try to insert into SQL Server in a table on the fly using this code:

Dim strCreate As String = "IF EXISTS(SELECT name FROM sysobjects " & _
        "WHERE  name = N'temp_test_spam' AND type = 'U')" & _
        "DROP TABLE temp_test_spam;" & _
        "SELECT * INTO temp_test_spam FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\test_imports\;','select * from GridExport.txt')"


        Dim conDatabase As SqlConnection = New SqlConnection("Network Library=DBMSSOCN;Data Source=dr-ny-sql003;Database='Spam_BB_Report';Integrated Security=yes;")
        Dim cmdDatabase As SqlCommand = New SqlCommand(strCreate, conDatabase)

        conDatabase.Open()

        cmdDatabase.ExecuteNonQuery()
        conDatabase.Close()


I get the following error:

System.Data.SqlClient.SqlException was unhandled
  Class=16
  ErrorCode=-2146232060
  LineNumber=1
  Message="Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)". OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] '(unknown)' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides."."
  Number=7303
  Procedure=""
  Server="dr-ny-sql003"
  Source=".Net SqlClient Data Provider"
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()    at search_engine.Form1.button1_Click(Object sender, EventArgs e) in C:\Search Engine\Form1.vb:line 655    at System.Windows.Forms.Control.OnClick(EventArgs e)    at System.Windows.Forms.Button.OnClick(EventArgs e)    at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)    at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)    at System.Windows.Forms.Control.WndProc(Message& m)    at System.Windows.Forms.ButtonBase.WndProc(Message& m)    at System.Windows.Forms.Button.WndProc(Message& m)    at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)    at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)    at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)    at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)    at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)    at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)    at System.Windows.Forms.Application.Run(ApplicationContext context)    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)    at search_engine.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81    at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)    at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)    at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)    at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()    at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)    at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)    at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()    at System.Threading.ThreadHelper.ThreadStart_Context(Object state)    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)    at System.Threading.ThreadHelper.ThreadStart()
  InnerException:


Thanks in advanced for your help!!!

Tammy

I am attaching the code that I am using to achieve this:


Public Function ImportSQL(ByVal dgv As DataGridView, ByVal FN As String) As Integer
 
        Dim writer As StreamWriter = New StreamWriter("C:\test_imports\GridExport.txt")
 
        If (DataGridView1.Rows.Count > 0) Then
            For Each col As DataGridViewColumn In DataGridView1.Columns
                If col.Visible = True Then
                    If (col.Index = (DataGridView1.Columns.Count - 1)) Then
                        writer.WriteLine(col.HeaderText)
                    Else
                        writer.Write(String.Concat(col.HeaderText, ","))
 
                    End If
                End If
            Next
            For Each row As DataGridViewRow In DataGridView1.Rows
                'If Not omitIndices.Contains(row.Index) Then
                For Each cell As DataGridViewCell In row.Cells
                    If DataGridView1.Columns.Item(cell.OwningColumn.Index).Visible = True Then
                        If (cell.OwningColumn.Index = (DataGridView1.Columns.Count - 1)) Then
                            If (Not (cell.Value) Is Nothing) Then
                                writer.WriteLine(cell.Value.ToString)
                            Else
                                writer.WriteLine("")
                            End If
 
                        ElseIf (Not (cell.Value) Is Nothing) Then
                            writer.Write(String.Concat(cell.Value.ToString, ","))
                        Else
                            writer.Write(String.Concat("", ","))
                        End If
                        End If
                Next
                'End If
            Next
        End If
 
        writer.Close()
 
    End Function

Open in new window

GridExport.txt
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Why are you generating a text file, why don't you use some ADO.NET way, like an SqlDataAdapter with a DataTable?
Avatar of jtammyg
jtammyg

ASKER

I need to import the text file into a table generated on the fly in SQL Server 2005.
Why not take the data and store in rows in a table?
Avatar of jtammyg

ASKER

How do I do that? I need to import by VB code.
This is what I use for SQL Server and VS.NET 2005:


' Add a reference to the System.Configuration.dll to the project for ConfigurationManager.
 
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms
 
Public Class SqlServerHelper
 
    Public Shared Sub WriteDataGridViewToTable(ByVal dgv As DataGridView)
 
        Dim dt As DataTable = CreateTable(dgv)
 
        WriteDataTableToServer(dt)
 
    End Sub
 
    Private Shared Function CreateTable(ByVal dgv As DataGridView) As DataTable
        Dim dt As New DataTable()
 
        For Each column As DataGridViewColumn In dgv.Columns
            dt.Columns.Add(column.Name)
        Next column
 
        For Each row As DataGridViewRow In dgv.Rows
 
            Dim dr As DataRow = dt.NewRow()
 
            For Each column As DataGridViewColumn In dgv.Columns
                dr(column.Name) = row.Cells(column.Index).Value
            Next column
 
            dt.Rows.Add(dr)
        Next row
 
        Return dt
    End Function
 
    Private Shared Sub WriteDataTableToServer(ByVal dt As DataTable)
 
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("MyConnection").ConnectionString
 
        Dim bulkCopier As New SqlBulkCopy(connectionString)
        bulkCopier.ColumnMappings.Add("FName", "FirstName")
        bulkCopier.ColumnMappings.Add("LName", "LastName")
 
        bulkCopier.WriteToServer(dt)
 
    End Sub
 
End Class

Open in new window

Avatar of jtammyg

ASKER

Okay, I see what you do...but the problem I have is that the datagridview can have a different columns depending on what the user checks or not.

There are 5 fixed columns that will ALWAYS BE IN THE DATAGRIDVIEW, but there could be from none to another 8 more depending if the checks the checkboxes or not. So I need to create the table in the SQL Server on the fly.

That was my logic for using either a Text file, excel file or csv.

I tried excel but it does not work...comes back with cannot find installable ISAM...i tried troubleshooting but to no avail. So I figured I try with the text files instead...but I have not had much luck either.

How can I achieve what I need with what you are showin me?

Thanks a lot for all your help!!!!!

Tammy
If you get a cannot find installable ISAM, that usually means that you have a connection string syntax error.

You can use SMO (Server Management Objects) or DML to create tables with SQL Server on the fly.
Avatar of jtammyg

ASKER

With the code you posted above is there any way to create the table on the fly?

If not, how can I use SMO or DML? Sorry I am pretty new to VB.2008 and VB in general.

Thanks a lot, TheLearnedOne.

DML example:


CREATE TABLE [dbo].[ReportList]
(
	[ReportID] [uniqueidentifier] NOT NULL,
	[ApplicationID] [uniqueidentifier] NOT NULL,
	[Title] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Name] [varchar](260) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ReportDocumentBytes] [image] NOT NULL,
	[MachineName] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SchemaDocument] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DataAccessMethod] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
                       CONSTRAINT [PK_ReportList] PRIMARY KEY CLUSTERED 
(

Open in new window

Avatar of jtammyg

ASKER

TheLearnedOne,

I know how to create the table...problem is the table's columns are not always the same....That's what is screwing me up.

:-(((((((((((((((((

Isn't there a solution to what I am trying to do???

Thanks a lot for all ur time.

Tammy
Where would you be getting the column names from?  Are they stored with the DataGridView?
Avatar of jtammyg

ASKER

The LearnedOne,

This is how I get the datagridview populated with the columns that the user wants to see.

Thanks a lot for all your efforts. I am raising the points to this question also.

Tammy
'This module populates the datagridview (View Results) with basic information (if the user has not 
    'marked any of the checkboxes) or complete information if he/she has marked one or more checkboxes
    '==========================================================================================================
 
 
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
 
        Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
 
 
        If Me.comboServers.Text.Length > 0 AndAlso Me.comboDatabases.Text.Length > 0 AndAlso Me.comboFilesets.Text.Length > 0 Then
 
            Dim cn As New SqlClient.SqlConnection()
            Dim cnb As New SqlClient.SqlConnectionStringBuilder
            Dim cm As New SqlClient.SqlCommand()
            'Dim dr As SqlClient.SqlDataReader
 
 
            cnb.DataSource = comboServers.Text
            cnb.InitialCatalog = "Spam_BB_Report"
            cnb.IntegratedSecurity = True
 
            cn.ConnectionString = cnb.ConnectionString
            cn.Open()
 
            With cm
                .CommandText = "usp_DR_Spam_BB_Search_get_recs"
                .CommandTimeout = 0
                .CommandType = CommandType.StoredProcedure
                .Connection = cn
                .Parameters.AddWithValue("@Matter", comboDatabases.SelectedItem)
                .Parameters.AddWithValue("@FileSet", comboFilesets.SelectedItem)
            End With
 
            Dim reader As SqlDataReader = cm.ExecuteReader()
            Dim ds As New DataSet()
            Dim dt As New DataTable("Table1")
            ds.Tables.Add(dt)
            ds.Load(reader, LoadOption.PreserveChanges, ds.Tables(0))
            DataGridView1.Font = New Font("SansSerif", 8.25, FontStyle.Regular)
 
 
 
 
            DataGridView1.DataSource = ds.Tables(0)
 
            If DataGridView1.Rows.Count > 0 Then
 
                If CheckBox1.CheckState = True Then
 
                    DataGridView1.Columns("Last Name").Visible = True
 
 
                ElseIf CheckBox1.CheckState = False Then
 
                    DataGridView1.Columns("Last Name").Visible = False
 
                End If
 
                If CheckBox2.CheckState = True Then
 
                    DataGridView1.Columns("First Name").Visible = True
 
                ElseIf CheckBox2.CheckState = False Then
 
                    DataGridView1.Columns("First Name").Visible = False
 
                End If
 
                If CheckBox3.CheckState = True Then
 
                    DataGridView1.Columns("Domain").Visible = True
 
                ElseIf CheckBox3.CheckState = False Then
 
                    DataGridView1.Columns("Domain").Visible = False
 
                End If
 
                If CheckBox4.CheckState = True Then
 
                    DataGridView1.Columns("Email").Visible = True
 
                ElseIf CheckBox4.CheckState = False Then
 
                    DataGridView1.Columns("Email").Visible = False
 
                End If
 
                If CheckBox5.CheckState = True Then
 
                    DataGridView1.Columns("Subject").Visible = True
 
                ElseIf CheckBox5.CheckState = False Then
 
                    DataGridView1.Columns("Subject").Visible = False
 
                End If
 
                If CheckBox6.CheckState = True Then
 
                    DataGridView1.Columns("General").Visible = True
 
                ElseIf CheckBox6.CheckState = False Then
 
                    DataGridView1.Columns("General").Visible = False
 
                End If
 
                If CheckBox7.CheckState = True Then
 
                    DataGridView1.Columns("Company").Visible = True
 
                ElseIf CheckBox7.CheckState = False Then
 
                    DataGridView1.Columns("Company").Visible = False
 
                End If
 
                If CheckBox8.CheckState = True Then
 
                    DataGridView1.Columns("Type").Visible = True
 
                ElseIf CheckBox8.CheckState = False Then
 
                    DataGridView1.Columns("Type").Visible = False
 
                End If
 
            Else
 
                MessageBox.Show("There are no records using the fileset selected, please try with a different fileset")
 
            End If
 
 
            Dim rowNumber As Integer = 1
            For Each row As DataGridViewRow In DataGridView1.Rows
                If row.IsNewRow Then Continue For
                row.HeaderCell.Value = rowNumber.ToString
                rowNumber = rowNumber + 1
            Next
 
        End If
 
 
        DataGridView1.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders)
 
 
        Cursor.Current = System.Windows.Forms.Cursors.Default
 
        'cmd.Connection.Close()
 
    End Sub

Open in new window

If you are binding to a DataTable, then you already have the structure that you need to create.  You can use the DataColumns defined for the DataTable to create a DML command, and create the SQL Server table.
Avatar of jtammyg

ASKER

Can you show me how to do that? I don't understand how.

sorry to be such a pain.

Thanks!

Tammy

Simple example:


Imports System.Text
 
...
 
        Dim dt As New DataTable("Table1")
 
        Dim sb As New StringBuilder()
 
        sb.Append("CREATE TABLE " & dt.TableName & "(")
 
        For Each dc As DataColumn In dt.Columns
            sb.Append(dc.ColumnName)
 
            If dc.DataType Is GetType(String) Then
                sb.Append(" varchar (" & dc.MaxLength & ")")
            End If
            sb.Append(",")
        Next
 
        sb.Remove(sb.Length - 1, 1)
 
        sb.Append(")")

Open in new window

Avatar of jtammyg

ASKER

okay...just to recap:

I have the datagridview that gets created with the code I sent you above and it works fine.


Then I have the following code which is what you suggested, but the table in SQL Server does not get created at all. What am I missing now?

Thanks a lot!

Tammy
Private Shared Function CreateTable(ByVal dgv As DataGridView) As DataTable
        Dim dt As New DataTable()
 
        For Each column As DataGridViewColumn In dgv.Columns
            dt.Columns.Add(column.Name)
        Next column
 
        For Each row As DataGridViewRow In dgv.Rows
 
            Dim dr As DataRow = dt.NewRow()
 
            For Each column As DataGridViewColumn In dgv.Columns
                dr(column.Name) = row.Cells(column.Index).Value
            Next column
 
            dt.Rows.Add(dr)
        Next row
 
        Return dt
    End Function
 
 
    Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim dt As New DataTable("Table1")
 
        Dim sb As New StringBuilder("Data Source=dr-ny-sql003;Database=Spam_BB_Report;Integrated Security=yes;")
 
        sb.Append("CREATE TABLE " & dt.TableName & "(")
 
        For Each dc As DataColumn In dt.Columns
            sb.Append(dc.ColumnName)
 
            If dc.DataType Is GetType(String) Then
                sb.Append(" varchar (" & dc.MaxLength & ")")
            End If
            sb.Append(",")
        Next
 
        sb.Remove(sb.Length - 1, 1)
 
        sb.Append(")")
 
    End Sub
 
 
End Class

Open in new window

That was code to generate the DML statement, and now you need to execute it, so you need an SqlConnection and an SqlCommand that you can call ExecuteNonQuery.
Avatar of jtammyg

ASKER

This is what I have now but I am getting an error      value of type 'system.text.stringbuilder' cannot be converted to 'string'

on this line Dim cmdDatabase As SqlCommand = New SqlCommand(sb, conDatabase)


Below is the code I am using:

Thanks a lot!!!!
 Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim dt As New DataTable("Table1")
 
        Dim sb As New StringBuilder()
 
        Dim conDatabase As SqlConnection = New SqlConnection("Data Source=dr-ny-sql003;Database=Spam_BB_Report;Integrated Security=yes;")
 
        Dim cmdDatabase As SqlCommand = New SqlCommand(sb, conDatabase)
 
        conDatabase.Open()
 
        cmdDatabase.ExecuteNonQuery()
 
        sb.Append("CREATE TABLE " & dt.TableName & "(")
 
        For Each dc As DataColumn In dt.Columns
            sb.Append(dc.ColumnName)
 
            If dc.DataType Is GetType(String) Then
                sb.Append(" nvarchar (" & dc.MaxLength & ")")
            End If
            sb.Append(",")
        Next
 
        sb.Remove(sb.Length - 1, 1)
 
        sb.Append(")")
 
        conDatabase.Close()
 
    End Sub

Open in new window

Dim cmdDatabase As SqlCommand = New SqlCommand(sb.ToString(), conDatabase)
Avatar of jtammyg

ASKER

TheLearnedOne,

Now I am getting the following error message:

System.InvalidOperationException was unhandled
  Message="ExecuteNonQuery: CommandText property has not been initialized"
  Source="System.Data"
  StackTrace:
       at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()    at search_engine.Form1.button1_Click(Object sender, EventArgs e) in C:\Search Engine\Form1.vb:line 614    at System.Windows.Forms.Control.OnClick(EventArgs e)    at System.Windows.Forms.Button.OnClick(EventArgs e)    at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)    at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)    at System.Windows.Forms.Control.WndProc(Message& m)    at System.Windows.Forms.ButtonBase.WndProc(Message& m)    at System.Windows.Forms.Button.WndProc(Message& m)    at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)    at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)    at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)    at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)    at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)    at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)    at System.Windows.Forms.Application.Run(ApplicationContext context)    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)    at search_engine.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81    at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)    at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)    at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)    at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()    at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)    at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)    at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()    at System.Threading.ThreadHelper.ThreadStart_Context(Object state)    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)    at System.Threading.ThreadHelper.ThreadStart()
  InnerException:



at this line

cmdDatabase.ExecuteNonQuery()
Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim dt As New DataTable("Table1")
 
        Dim sb As New StringBuilder()
 
        Dim conDatabase As SqlConnection = New SqlConnection("Data Source=dr-ny-sql003;Database=Spam_BB_Report;Integrated Security=yes;")
 
        Dim cmdDatabase As SqlCommand = New SqlCommand(sb.ToString(), conDatabase)
 
        conDatabase.Open()
 
        cmdDatabase.ExecuteNonQuery()
 
 
        sb.Append("CREATE TABLE " & dt.TableName & "(")
 
        For Each dc As DataColumn In dt.Columns
            sb.Append(dc.ColumnName)
 
            If dc.DataType Is GetType(String) Then
                sb.Append(" nvarchar (" & dc.MaxLength & ")")
            End If
            sb.Append(",")
        Next
 
        sb.Remove(sb.Length - 1, 1)
 
        sb.Append(")")
 
        conDatabase.Close()
 
    End Sub

Open in new window


Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim dt As DataTable = CType(Me.DataGridView1.DataSource, DataTable)
 
        Dim sb As New StringBuilder()
 
        sb.Append("CREATE TABLE " & dt.TableName & "(")
 
        For Each dc As DataColumn In dt.Columns
            sb.Append(dc.ColumnName)
 
            If dc.DataType Is GetType(String) Then
                sb.Append(" nvarchar (" & dc.MaxLength & ")")
            End If
            sb.Append(",")
        Next
 
        sb.Remove(sb.Length - 1, 1)
 
        sb.Append(")")
 
        Dim conDatabase As SqlConnection = New SqlConnection("Data Source=dr-ny-sql003;Database=Spam_BB_Report;Integrated Security=yes;")
 
        Dim cmdDatabase As SqlCommand = New SqlCommand(sb.ToString(), conDatabase)
 
        conDatabase.Open()
 
        cmdDatabase.ExecuteNonQuery()
 
        conDatabase.Close()
 
    End Sub

Open in new window

Avatar of jtammyg

ASKER

I guess we are getting closer, but I am still getting an error at the line

cmdDatabase.Executenonquery()


Here is the error that is throwing:

System.Data.SqlClient.SqlException was unhandled
  Class=15
  ErrorCode=-2146232060
  LineNumber=1
  Message="Incorrect syntax near 'varchar'."
  Number=102
  Procedure=""
  Server="dr-ny-sql003"
  Source=".Net SqlClient Data Provider"
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()    at search_engine.Form1.button1_Click(Object sender, EventArgs e) in C:\Search Engine\Form1.vb:line 629    at System.Windows.Forms.Control.OnClick(EventArgs e)    at System.Windows.Forms.Button.OnClick(EventArgs e)    at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)    at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)    at System.Windows.Forms.Control.WndProc(Message& m)    at System.Windows.Forms.ButtonBase.WndProc(Message& m)    at System.Windows.Forms.Button.WndProc(Message& m)    at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)    at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)    at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)    at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)    at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)    at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)    at System.Windows.Forms.Application.Run(ApplicationContext context)    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)    at search_engine.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81    at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)    at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)    at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)    at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()    at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)    at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)    at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()    at System.Threading.ThreadHelper.ThreadStart_Context(Object state)    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)    at System.Threading.ThreadHelper.ThreadStart()
  InnerException:


Thanks a lot!!!!!!!!

I really appreciate all your hard work on this one!!!!!
What does the StringBuilder collect--what is the value of sb.ToString()?
Avatar of jtammyg

ASKER

This is what I have in sb

   Dim sb As New StringBuilder()


Dim cmdDatabase As SqlCommand = New SqlCommand(sb.ToString(), conDatabase)

No, I mean add a breakpoint on this line:

    Dim cmdDatabase As SqlCommand = New SqlCommand(sb.ToString(), conDatabase)

Run until you get to that line.  

In the Immediate Window, evaluate this expression:

? sb.ToString()

Copy and paste the result into this question.
Avatar of jtammyg

ASKER

this is what comes up with

System.Data.SqlClient.SqlException was unhandled
  Class=15
  ErrorCode=-2146232060
  LineNumber=1
  Message="The definition for column 'Custodianvarchar' must include a data type."
  Number=173
  Procedure=""
  Server="dr-ny-sql003"
  Source=".Net SqlClient Data Provider"
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()    at search_engine.Form1.button1_Click(Object sender, EventArgs e) in C:\Search Engine\Form1.vb:line 628    at System.Windows.Forms.Control.OnClick(EventArgs e)    at System.Windows.Forms.Button.OnClick(EventArgs e)    at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)    at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)    at System.Windows.Forms.Control.WndProc(Message& m)    at System.Windows.Forms.ButtonBase.WndProc(Message& m)    at System.Windows.Forms.Button.WndProc(Message& m)    at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)    at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)    at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)    at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)    at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)    at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)    at System.Windows.Forms.Application.Run(ApplicationContext context)    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)    at search_engine.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81    at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)    at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)    at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)    at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()    at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)    at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)    at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()    at System.Threading.ThreadHelper.ThreadStart_Context(Object state)    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)    at System.Threading.ThreadHelper.ThreadStart()
  InnerException:


The name of the column is 'custodian'...I am not sure why is calling it 'custodianvarchar'



Thanks a lot!!!!
Because there is a missing space.

sb.Append(dc.ColumnName & " ")
Avatar of jtammyg

ASKER

still getting an error on the line

executenonquery



System.Data.SqlClient.SqlException was unhandled
  Class=15
  ErrorCode=-2146232060
  LineNumber=1
  Message="Incorrect syntax near 'varchar'."
  Number=102
  Procedure=""
  Server="dr-ny-sql003"
  Source=".Net SqlClient Data Provider"
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()    at search_engine.Form1.button1_Click(Object sender, EventArgs e) in C:\Search Engine\Form1.vb:line 628    at System.Windows.Forms.Control.OnClick(EventArgs e)    at System.Windows.Forms.Button.OnClick(EventArgs e)    at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)    at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)    at System.Windows.Forms.Control.WndProc(Message& m)    at System.Windows.Forms.ButtonBase.WndProc(Message& m)    at System.Windows.Forms.Button.WndProc(Message& m)    at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)    at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)    at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)    at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)    at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)    at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)    at System.Windows.Forms.Application.Run(ApplicationContext context)    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()    at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)    at search_engine.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81    at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)    at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)    at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)    at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()    at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)    at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)    at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()    at System.Threading.ThreadHelper.ThreadStart_Context(Object state)    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)    at System.Threading.ThreadHelper.ThreadStart()
  InnerException:
Avatar of jtammyg

ASKER

TheLearnedOne,

Okay..I look at the quickwatch,,,and ithe commantext value is showing the following:

"CREATE TABLE Table1(Doc ID ,Doc Name varchar(300),Doc location varchar(1000),Custodian varchar(300),Last Name varchar(255),First Name varchar(255),Domain varchar(255),Email varchar(255),Subject varchar(255),General varchar(255),Company varchar(255),Type varchar(255))"

which contains even the columns that I don't want to populate in sql server.

for this particular example it should have only showed me:

CREATE TABLE Table1(Doc ID ,Doc Name varchar(300),Doc location varchar(1000),Custodian varchar(300),Domain varchar(255))"


because domain was the only checkboxthat I checked.

Ok, now you have spaces in column names, which is not usually the "best" approach.

sb.Append("[" & dc.ColumnName & "] ")

What CheckBox are you talking about?
Avatar of jtammyg

ASKER

the problem is the field names. e.g Doc ID , Doc Name without the [] around
Avatar of jtammyg

ASKER

okay we are making progress.....now i am getting a different kind of error message

System.Data.SqlClient.SqlException was unhandled
  Class=15
  ErrorCode=-2146232060
  LineNumber=1
  Message="The definition for column 'Doc ID' must include a data type."
  Number=173
  Procedure=""
  Server="dr-ny-sql003"
  Source=".Net SqlClient Data Provider"
  State=1
 


The first field that is always here is of type integer.....and the rest are nvarchar.

How do I incorporate that?
Avatar of jtammyg

ASKER

okay I got it now...the table gets populated...but with all the fields....not only the ones the user selects.

let's say that the user checks the 'domain' checkbox.

then the datatagrisview fields shown will be doc id,doc name, custodian,domain.......the other ones will be there but invisible.

The Datatable is showing ALL THE FIELDS, which is not what I want to import into SQL Server. Also the data is noy getting populated.

Do I have to do an insert into at this point?

Thanks a lot!!!!


 Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
 
        Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
 
 
        If Me.comboServers.Text.Length > 0 AndAlso Me.comboDatabases.Text.Length > 0 AndAlso Me.comboFilesets.Text.Length > 0 Then
 
            Dim cn As New SqlClient.SqlConnection()
            Dim cnb As New SqlClient.SqlConnectionStringBuilder
            Dim cm As New SqlClient.SqlCommand()
            'Dim dr As SqlClient.SqlDataReader
 
 
            cnb.DataSource = comboServers.Text
            cnb.InitialCatalog = "Spam_BB_Report"
            cnb.IntegratedSecurity = True
 
            cn.ConnectionString = cnb.ConnectionString
            cn.Open()
 
            With cm
                .CommandText = "usp_DR_Spam_BB_Search_get_recs"
                .CommandTimeout = 0
                .CommandType = CommandType.StoredProcedure
                .Connection = cn
                .Parameters.AddWithValue("@Matter", comboDatabases.SelectedItem)
                .Parameters.AddWithValue("@FileSet", comboFilesets.SelectedItem)
            End With
 
            Dim reader As SqlDataReader = cm.ExecuteReader()
            Dim ds As New DataSet()
            Dim dt As New DataTable("Table1")
            ds.Tables.Add(dt)
            ds.Load(reader, LoadOption.PreserveChanges, ds.Tables(0))
            DataGridView1.Font = New Font("SansSerif", 8.25, FontStyle.Regular)
 
 
 
 
            DataGridView1.DataSource = ds.Tables(0)
 
            If DataGridView1.Rows.Count > 0 Then
 
                If CheckBox1.CheckState = True Then
 
                    DataGridView1.Columns("Last Name").Visible = True
 
 
                ElseIf CheckBox1.CheckState = False Then
 
                    DataGridView1.Columns("Last Name").Visible = False
 
                End If
 
                If CheckBox2.CheckState = True Then
 
                    DataGridView1.Columns("First Name").Visible = True
 
                ElseIf CheckBox2.CheckState = False Then
 
                    DataGridView1.Columns("First Name").Visible = False
 
                End If
 
                If CheckBox3.CheckState = True Then
 
                    DataGridView1.Columns("Domain").Visible = True
 
                ElseIf CheckBox3.CheckState = False Then
 
                    DataGridView1.Columns("Domain").Visible = False
 
                End If
 
                If CheckBox4.CheckState = True Then
 
                    DataGridView1.Columns("Email").Visible = True
 
                ElseIf CheckBox4.CheckState = False Then
 
                    DataGridView1.Columns("Email").Visible = False
 
                End If
 
                If CheckBox5.CheckState = True Then
 
                    DataGridView1.Columns("Subject").Visible = True
 
                ElseIf CheckBox5.CheckState = False Then
 
                    DataGridView1.Columns("Subject").Visible = False
 
                End If
 
                If CheckBox6.CheckState = True Then
 
                    DataGridView1.Columns("General").Visible = True
 
                ElseIf CheckBox6.CheckState = False Then
 
                    DataGridView1.Columns("General").Visible = False
 
                End If
 
                If CheckBox7.CheckState = True Then
 
                    DataGridView1.Columns("Company").Visible = True
 
                ElseIf CheckBox7.CheckState = False Then
 
                    DataGridView1.Columns("Company").Visible = False
 
                End If
 
                If CheckBox8.CheckState = True Then
 
                    DataGridView1.Columns("Type").Visible = True
 
                ElseIf CheckBox8.CheckState = False Then
 
                    DataGridView1.Columns("Type").Visible = False
 
                End If
 
            Else
 
                MessageBox.Show("There are no records using the fileset selected, please try with a different fileset")
 
            End If
 
 
            Dim rowNumber As Integer = 1
            For Each row As DataGridViewRow In DataGridView1.Rows
                If row.IsNewRow Then Continue For
                row.HeaderCell.Value = rowNumber.ToString
                rowNumber = rowNumber + 1
            Next
 
        End If
 
 
        DataGridView1.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders)
 
 
        Cursor.Current = System.Windows.Forms.Cursors.Default
 
        'cmd.Connection.Close()
 
    End Sub

Open in new window

Here is where it gets a bit more complicated.  You need to match up a CheckBox to a column in the DataTable, so that you can skip adding columns for any CheckBox that isn't checked.
Avatar of jtammyg

ASKER

I thought it was complicated enough already!!!!!!!

Well, I tried doing this:

Private Shared Function CreateTable(ByVal dgv As DataGridView) As DataTable
        Dim dt As New DataTable()

        For Each column As DataGridViewColumn In dgv.Columns
            If column.Visible = True Then
                dt.Columns.Add(column.Name)
            End If
        Next column

        For Each row As DataGridViewRow In dgv.Rows

            Dim dr As DataRow = dt.NewRow()

            For Each column As DataGridViewColumn In dgv.Columns
                If column.Visible = True Then
                    dr(column.Name) = row.Cells(column.Index).Value
                End If
            Next column

            dt.Rows.Add(dr)
        Next row

        Return dt
    End Function


so as to only show the columns that are visible...but it doesn't work.

Also the data is not getting populated at all.

>>but it doesn't work
That is not very descriptive.  Are you trying to say that you don't have any columns or rows in the DataTable?
Avatar of jtammyg

ASKER

I have ALL the columns....but no rows at all.
How many rows does the DataGridView have?  How many times is it calling dt.Rows.Add(dr)?
Avatar of jtammyg

ASKER

how can i find that out?
Previous described debugging steps, slightly modified:

Add a breakpoint on this line:

     For Each column As DataGridViewColumn In dgv.Columns

Run until you get to that line.  

In the Immediate Window, evaluate this expression:

? dgv.Rows.Count
Avatar of jtammyg

ASKER

the datagridview has 554 records
Put a breakpoint on this line:

   dt.Rows.Add(dr)

How many times is it executed?
Avatar of jtammyg

ASKER

going home now...i will continue checking later and will let you know.

Thanks a lot for all ur help on this!!!

Have a good night!

Tammy
Avatar of jtammyg

ASKER

good morning TheLearnedOne!

It gives me an 'unable to evaluate the expresison

when i put a watch on this line


            For Each column As DataGridViewColumn In dgv.Columns

Thanks!

Tammy
Take a screen shot from your session, and attach a .png file to this question, so I can see what you are doing, please.
Avatar of jtammyg

ASKER

Here is the screenshot
dvg.bmp
Aah, yes, now I understand--you were trying to do a watch expression, and not using the Immediate Window (Debug | Windows | Immediate).

Example:

Also, working with bitmaps at megabyte size, vs. PNG files at kilobyte size are very prohibitive, so I would learn about using something like MSPaint to save .png files.  My attached example is 12 KB.
VS.NET-2005----Debugging----Imme.png
I just noticed that the picture got cut off, so in the Immediate Window, you would have seen:

? a
5
Avatar of jtammyg

ASKER

I'm sorry but I am not getting anything
Avatar of jtammyg

ASKER

how do u highlight in yellow so that it rund up to that point? can you tell me step by step how you do this?

Sorry this is soooo frustrating.

There is a break-point on the End Sub line, which is highlighted in yellow as the currently debugged line.  

Add a break-point to the code (which you have).  Run until the IDE breaks, and then bring up the Immediate Window (shown above), and type in the expression that I told you.
Avatar of jtammyg

ASKER

it is still on (running) mode and the breakpoint is still red.
Avatar of jtammyg

ASKER

I tried running but to no avail. It does not seem to get to that line. When I put the mouse over the tooltip just gives me the generic syntax and not the values or count.
Avatar of jtammyg

ASKER

TheLearnedOne,

How does the function CreateTable relate to my button 1? Via the datatable?


Private Shared Function CreateTable(ByVal dgv As DataGridView) As DataTable
 
        Dim dt As New DataTable()
 
        For Each column As DataGridViewColumn In dgv.Columns
            dt.Columns.Add(column.Name)
        Next column
 
        For Each row As DataGridViewRow In dgv.Rows
 
            Dim dr As DataRow = dt.NewRow()
 
            For Each column As DataGridViewColumn In dgv.Columns
                dr(column.Name) = row.Cells(column.Index).Value
            Next column
 
            dt.Rows.Add(dr)
        Next row
 
        Return dt
    End Function
 
 
 
    Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
 
        Dim dt As DataTable = CType(Me.DataGridView1.DataSource, DataTable)
 
        Dim sb As New StringBuilder()
 
        sb.Append("CREATE TABLE " & dt.TableName & "(")
 
        For Each dc As DataColumn In dt.Columns
 
            sb.Append("[" & dc.ColumnName & "]")
 
            If dc.DataType Is GetType(String) Then
                sb.Append("varchar(" & dc.MaxLength & ")")
            Else
                sb.Append("int")
            End If
            sb.Append(",")
 
        Next
 
        sb.Remove(sb.Length - 1, 1)
 
        sb.Append(")")
 
        Dim conDatabase As SqlConnection = New SqlConnection("Data Source=dr-ny-sql003;Initial Catalog=Spam_BB_Report;Integrated Security=true")
 
        Dim cmdDatabase As SqlCommand = New SqlCommand(sb.ToString(), conDatabase)
 
        conDatabase.Open()
 
        cmdDatabase.ExecuteNonQuery()
 
        conDatabase.Close()
 
        Cursor.Current = System.Windows.Forms.Cursors.Default
 
    End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jtammyg

ASKER

okay, now I am getting an error

System.Data.SqlClient.SqlException was unhandled
  Class=15
  ErrorCode=-2146232060
  LineNumber=1
  Message="Incorrect syntax near '('."
  Number=102
  Procedure=""
  Server="dr-ny-sql003"
  Source=".Net SqlClient Data Provider"
  State=1


this is what i see in the quickwatch for cmdDatabase

"CREATE TABLE ([Doc ID] varchar(-1),[Doc Name] varchar(-1),[Doc location] varchar(-1),[Custodian] varchar(-1),[Last Name] varchar(-1),[First Name] varchar(-1),[Domain] varchar(-1),[Email] varchar(-1),[Subject] varchar(-1),[General] varchar(-1),[Company] varchar(-1),[Type] varchar(-1))"
 Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
 
        Dim dt As DataTable = Form1.CreateTable(DataGridView1)
 
 
        Dim sb As New StringBuilder()
 
        sb.Append("CREATE TABLE " & dt.TableName & "(")
 
        For Each dc As DataColumn In dt.Columns
 
            sb.Append("[" & dc.ColumnName & "] ")
 
            If dc.DataType Is GetType(String) Then
                sb.Append("varchar(" & dc.MaxLength & ")")
            Else
                sb.Append("int")
            End If
            sb.Append(",")
 
        Next
 
        sb.Remove(sb.Length - 1, 1)
 
        sb.Append(")")
 
        Dim conDatabase As SqlConnection = New SqlConnection("Data Source=dr-ny-sql003;Initial Catalog=Spam_BB_Report;Integrated Security=true")
 
        Dim cmdDatabase As SqlCommand = New SqlCommand(sb.ToString(), conDatabase)
 
        conDatabase.Open()
 
        cmdDatabase.ExecuteNonQuery()
 
        conDatabase.Close()
 
        Cursor.Current = System.Windows.Forms.Cursors.Default
 
    End Sub
 
End Class

Open in new window

You can't have a size of -1, which happens because you didn't define any widths for the columns in CreateTable method.
Avatar of jtammyg

ASKER

This is weird...bc before I added that function call...I was seing the correct widths for the fields and it was creating the table...(with no data...but still creating it).

How can that be? I am lost here.
You're lost, I have a very narrow view of your world, and I am myopic to boot.

When you were getting the DataTable from the DataGridView.DataSource, it didn't have any rows, but it had the right column sizes.  

I would think that you need to get the column widths from the corresponding DataTable that the DataGridView is bound to, and then create your own DataRows from the DataGridView.

Avatar of jtammyg

ASKER

I thought we were doing that here

 For Each dc As DataColumn In dt.Columns
 
            sb.Append("[" & dc.ColumnName & "] ")
 
            If dc.DataType Is GetType(String) Then
                sb.Append("varchar(" & dc.MaxLength & ")")
            Else
                sb.Append("int")
            End If
            sb.Append(",")
 
        Next
We are, but from the DataTable that we created (without MaxLength set), and not from the DataGridView.DataSource.

Avatar of jtammyg

ASKER

okay i got rid of that error by doing this:


        For Each dc As DataColumn In dt.Columns

            sb.Append("[" & dc.ColumnName & "] ")

            If dc.DataType Is GetType(String) Then
                sb.Append("nvarchar(1000)")
            Else
                sb.Append("int")
            End If
            sb.Append(",")

        Next


but i still don't get data populated into the table.

I think by now u deserve like 500 points for this lol!!!!
Avatar of jtammyg

ASKER

TheLearnedOne,

I think I got figured out how to only read the visible columns from the datagridview....and the table gets created with the columns that i need......we still have the problem of the rows not getting populated.

Here is how I am doing choosing only the visible columns:
For Each column As DataGridViewColumn In dgv.Columns
            If column.Visible = True Then
                dt.Columns.Add(column.Name)
            End If
        Next column
 
        For Each row As DataGridViewRow In dgv.Rows
 
            Dim dr As DataRow = dt.NewRow()
 
            For Each column As DataGridViewColumn In dgv.Columns
                If column.Visible = True Then
                    dr(column.Name) = row.Cells(column.Index).Value
                End If
            Next column

Open in new window

Check dt.Rows.Count in the CreateTable method.
Avatar of jtammyg

ASKER

where do i put that?

It is underlining it in blue when i type it at the end of the createtable.
Yeah, you probably put after the Return statement--not allowed.

Immediate Window
Avatar of jtammyg

ASKER

Hi TheLearnedOne,

I added the sqlbulkcopy at the end of my button1 and now it is populating the table with all the records exactly as it comes from the datatable.

It works perfect!!!!!!!!!!!!!!!!!!!!

Thanks a lot for all your help and time on this!!!!!!

I really appreciate it!!!

Best wishes

Tammy
Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
 
        Dim dt As DataTable = Form1.CreateTable(DataGridView1)
 
 
        Dim sb As New StringBuilder()
 
        sb.Append("CREATE TABLE " & dt.TableName & "(")
 
        For Each dc As DataColumn In dt.Columns
 
            sb.Append("[" & dc.ColumnName & "] ")
 
            If dc.DataType Is GetType(String) Then
                sb.Append("nvarchar(1000)")
            Else
                sb.Append("int")
            End If
            sb.Append(",")
 
        Next
 
        sb.Remove(sb.Length - 1, 1)
 
        sb.Append(")")
 
        Dim conDatabase As SqlConnection = New SqlConnection("Data Source=dr-ny-sql003;Initial Catalog=Spam_BB_Report;Integrated Security=true")
 
        Dim cmdDatabase As SqlCommand = New SqlCommand(sb.ToString(), conDatabase)
 
        conDatabase.Open()
 
        cmdDatabase.ExecuteNonQuery()
 
        conDatabase.Close()
 
 
        ' write to the SQL Server table
 
        Dim connectionString As String
 
        Dim bcp As SqlBulkCopy = Nothing
 
        Dim strConnectionString As String = ""
 
        connectionString = "Data Source=dr-ny-sql003;Initial Catalog=Spam_BB_Report;Integrated Security=true"
 
        Try
 
            bcp = New SqlBulkCopy(connectionString)
            bcp.DestinationTableName = "temp_Spam_BB_import"
            bcp.WriteToServer(dt)
 
        Catch ex As Exception
        Finally
            If Not bcp Is Nothing Then
                bcp.Close()
            End If
        End Try
 
 
 
        Cursor.Current = System.Windows.Forms.Cursors.Default
 
    End Sub

Open in new window

Yeah, all that work, and only B grade--gotta love the respect.