export datagridview to text file and import text file to sql servertable on the fly

  • Thread starter Thread starter TG
  • Start date Start date
T

TG

Hi!

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



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(TdsParserStat
eObject 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(Int
32 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(ActivationC
ontext activationContext, String[] activationCustomData) at
System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationC
ontext activationContext) at
Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugIn
Zone() 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
 
the sql string seems to have some fails...

is this a direct copy-paste??

if so, check out for spaces before the DROP, colon nor space after the FROM,
line breaking symbol before the 2 last lines..
in debug mode, print it to immediate window ( ?strCreate ), copy the output,
paste it into query analyser and run (ctrl+f5 just check for errors)...
for me, it's the easiest way to figured out if a string is correct...

now refereing to the connection string... is it corret?? did it ever worked
somewhere else??

i would try a connstring more like this:
Data Source=dr-ny-sql003;Initial Catalog=Spam_BB_Report;[User
ID=username];[Password=pwd]

christiano.

TG said:
Hi!

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



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(TdsParserStat
eObject 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(Int
32 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(ActivationC
ontext activationContext, String[] activationCustomData) at
System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationC
ontext activationContext) at
Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugIn
Zone() 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
Click to expand...
 
Christiano,


I removed the drop etc.

I changed the connection string to what you said...except with the
integrated security = yes....and I still get the same error message.

I think the problem lays in the way the text file is generated.

Can you please take a look at that and let me know if you see anything
out of the ordinary?

Thanks a lot for your help!

Tammy
 
Back
Top