469,327 Members | 1,192 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,327 developers. It's quick & easy.

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

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(SqlExc eption exception,
Boolean breakConnection) at
System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection) at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStat
eObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior
runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj) at
System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
methodName, Boolean async) at
System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(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(MouseEventAr gs 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.O nMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.W ndProc(Message&
m) at System.Windows.Forms.NativeWindow.DebuggableCallba ck(IntPtr
hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at
System.Windows.Forms.UnsafeNativeMethods.DispatchM essageW(MSG& msg) at
System.Windows.Forms.Application.ComponentManager. System.Windows.Forms .UnsafeNativeMethods.IMsoComponentManager.FPushMes sageLoop(Int32
dwComponentID, Int32 reason, Int32 pvLoopData) at
System.Windows.Forms.Application.ThreadContext.Run MessageLoopInner(Int
32 reason, ApplicationContext context) at
System.Windows.Forms.Application.ThreadContext.Run MessageLoop(Int32
reason, ApplicationContext context) at
System.Windows.Forms.Application.Run(ApplicationCo ntext context) at
Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.
OnRun() at
Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.
DoApplicationModel() at
Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.
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.ExecuteAsAss embly() at
System.Runtime.Hosting.ApplicationActivator.Create Instance(ActivationC
ontext activationContext, String[] activationCustomData) at
System.Runtime.Hosting.ApplicationActivator.Create Instance(ActivationC
ontext activationContext) at
Microsoft.VisualStudio.HostingProcess.HostProc.Run UsersAssemblyDebugIn
Zone() at System.Threading.ThreadHelper.ThreadStart_Context( Object
state) at System.Threading.ExecutionContext.Run(ExecutionCon text
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
Jul 17 '08 #1
2 4381
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" <jt*****@yahoo.comescreveu na mensagem
news:2e**********************************@59g2000h sb.googlegroups.com...
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(SqlExc eption exception,
Boolean breakConnection) at
System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection) at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStat
eObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior
runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj) at
System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
methodName, Boolean async) at
System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(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(MouseEventAr gs 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.O nMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.W ndProc(Message&
m) at System.Windows.Forms.NativeWindow.DebuggableCallba ck(IntPtr
hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at
System.Windows.Forms.UnsafeNativeMethods.DispatchM essageW(MSG& msg) at
System.Windows.Forms.Application.ComponentManager. System.Windows.Forms
.UnsafeNativeMethods.IMsoComponentManager.FPushMes sageLoop(Int32
dwComponentID, Int32 reason, Int32 pvLoopData) at
System.Windows.Forms.Application.ThreadContext.Run MessageLoopInner(Int
32 reason, ApplicationContext context) at
System.Windows.Forms.Application.ThreadContext.Run MessageLoop(Int32
reason, ApplicationContext context) at
System.Windows.Forms.Application.Run(ApplicationCo ntext context) at
Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.
OnRun() at
Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.
DoApplicationModel() at
Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.
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.ExecuteAsAss embly() at
System.Runtime.Hosting.ApplicationActivator.Create Instance(ActivationC
ontext activationContext, String[] activationCustomData) at
System.Runtime.Hosting.ApplicationActivator.Create Instance(ActivationC
ontext activationContext) at
Microsoft.VisualStudio.HostingProcess.HostProc.Run UsersAssemblyDebugIn
Zone() at System.Threading.ThreadHelper.ThreadStart_Context( Object
state) at System.Threading.ExecutionContext.Run(ExecutionCon text
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

Jul 17 '08 #2
TG
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

Jul 17 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by frankvfox | last post: by
2 posts views Thread by mike_dba | last post: by
7 posts views Thread by eselk | last post: by
3 posts views Thread by =?Utf-8?B?YzY3NjIyOA==?= | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.