473,326 Members | 2,111 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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 4594
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: frankvfox | last post by:
I need a code sample using VB ASP.Net 2003 which exports a tab delimited text file resident on the server into an Excel spreadsheet which will be saved through the browser to the client's local...
4
by: N. Graves | last post by:
Thanks for taking the time to read this note. I have a Access inventory collection tool that I have automated to build and Export to a CSV file. In the database I have several fields that are...
10
by: Neil | last post by:
Hi guyz, just trying out this google feature so if i post if in the wrong area i appologize. now to my question. BTW i'm new to access programming, i've done a little vb6.0 and vb.net but access...
2
by: Siu | last post by:
Hi, I use the following code to export and import a file Excel from resp. into a Web page with the following code: //EXPORT Response.Clear(); Response.Buffer = true; Response.ContentType =...
8
by: Taffman | last post by:
I've searched this goup for an answer to this, there are many discussions that come close but non that I can find that actually addresses this particular problem. I'm exporting queries to Excel....
2
by: mike_dba | last post by:
I am attempting to move data between 2 DB2 Linux databases using a pipe. I can't seem to get it working. Any assistance is appreciated. I first issue: mkfifo -m777 mypipe db2 "connect to...
7
by: eselk | last post by:
I'm doing some speed tests. I created a brand-new table, with just one "Long Integer" field. I'm testing adding 1000 records. If I use the "Export" feature in Access, it takes only a few seconds...
0
by: A3AN | last post by:
Hi. I receive a database backup on a daily basis. I then import this dump on another server which I use for software development. There is two db's being hosted on this server. We test software...
3
by: =?Utf-8?B?YzY3NjIyOA==?= | last post by:
Hi all, I have a question for you. I have a .csv file which has many lines of data. Each line has many data fields which are delimited by ",". Now I need to extract part of data from this...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.