Insert data into table from text file using sqlceresultset

[netcfadmirer]

Hi, anyone know how to bulk insert data from text file to
table/database using sqlceresultset ?

I tried using BULK insert statement but its throw exception.

Thanks in advance.

 

[Ginny Caughey [MVP]]

netcfadmirer,

I posted a reply to you earlier today showing how to create a SQL Mobile
database and table and how to populate it with data. All you need to do is
read in the text file and write it out using the SqlCeResultSet.

Ginny Caughey
..NET Compact Framework MVP

 

[netcfadmirer]

Ginny,

Can show me some codes or samples on how to accomplish it ? Any
performances issue ? My text file content is something like below :


P0000001 | VISUAL STUDIO | 12/12/2005


and i need to bulk insert into sdf database table.


Real need it.

 

[Ginny Caughey [MVP]]

What did you try already and what error message did you get?

Ginny

 

[netcfadmirer]

Ginny,

I dont have any idea on how to accomplish this.. i managed to get the
client to orders (1 to M) relationship using sqlceresultset. But my
main problem is i got a text file which got the approximately 1000
records like below :


ERIC CANTONA | MANCHESTER UNITED | 1
PAUL SCHOLES | MANCHESTER UNITED | 2
.......
.......

I need to load this text file in bulk to my data table. Any solution to
accomplish this in .net compact framework ?


Real need this urgently...


thanks.

 

[Guest]

There's no way other than line-by-line brute force.

- Read a line
- see if the team exists.
- If not, add it.
- See if the player exists
- if not add it with a reference to the team (no clue what the number is)
- Repeat until you've gone through the file

-Chris

 

[netcfadmirer]

any sample code available ? for my case i dont need any record
checkings, i just need to load all the available columns in text files
into datatable in bulk ..

 

[Ginny Caughey [MVP]]

Do you want somebody to write this for you? Consulting services are
available, but the algorithm is simple: just read a line from the text file
and write it out to a new row in your table. Repeat until there are no more
lines to read.

Ginny

 

[Chris Tacke, eMVP]

There are lots of samples for file reading on the web (and even in Help).
There are lots of samples for data inserting on the web. You simply need to
put them together. At some point you're going to have to write some code.

--
Chris Tacke
Co-founder
OpenNETCF.org
Are you using the SDF? Let's do a case study.
Email us at d c s @ o p e n n e t c f . c o m
http://www.opennetcf.org/donate

 

[Sachin Palewar]

I am trying to import from a csv file into a sqlserver ce table.

I am using parameters to improve performance of insert into query.

Since last 2 days I am struggling to get it working.

I keep getting this exception:

A first chance exception of type
'System.Data.SqlServerCe.SqlCeException' occurred in
System.Data.SqlServerCe.dll

I converted my pocket pc code to desktop code and it worked there. I
can't understand whats wrong. exception doesn't give any more info. I
am going crazy, please help me.

 

[Ilya Tumanov [MS]]

Catch this exception and print out exception messages to figure out what's
wrong.

See sample in VS documentation on SqlCeException class.


Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).

 

[Chris Tacke, eMVP]

Showing us some actual code that fails would go a long way toward us
understanding.

--
Chris Tacke
Co-founder
OpenNETCF.org
Are you using the SDF? Let's do a case study.
Email us at d c s @ o p e n n e t c f . c o m
http://www.opennetcf.org/donate

 

[Sachin Palewar]

Thanks for your responses. My code is running now. I have 2 issues with
my code.

1. I skipped using .value=somevalue to assigne value to parameters.
Instead i was trying to assign value to parameter directly. But the
error message was misleading, so I couldn't figure out.

2. I was using my insert query as "insert into table values (?,?,?)" as
I was inserting values for all the fields. I simply changed the query
to include field-names and it started working, I don't know why?

 

[Sachin Palewar]

Hi,

Now I am facing another issue. I want to pass null value to parameters.
Can anybody tell me how to do that. What I am currently doing is that I
am not setting .value property of parameter if I don't have value for
it. Parameter data type is date.

It gives me error when I call executenonquery. Can anybody thrw some
light on it??

 

[Sergey Bogdanov]

What about DBNull.value or System.Data.SqlDateTime.Null?

 

[Sachin Palewar]

Yes you are right sergey. Yesterday only I found out the right way to
do that and was going to post a reply.

I am now using System.DBNull.Value and it works. Thanks for your
response.

Sachin Palewar
www.palewar.com

 

[KolMeJack]

even if this thread is already 11 years ago... I just want to say **** you guys this thread is ****ing no help at all...

this is the sample of the codes for this ****ing topic you can suggest and edit the codes if you want...

Function fastImport()
ExecuteQuery("DELETE from tbl_item")
'dt = ExecuteQuery("Select * from tbl_items")
Try
Dim cmd As SqlCeCommand = conn.CreateCommand()
cmd.CommandText = "SELECT * FROM tbl_item"
cmd.ExecuteNonQuery()
' For optimal performance use base table access
'
cmd.CommandText = "tbl_item"
cmd.CommandType = CommandType.TableDirect
Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
Dim x As Integer
x = 0
Cursor.Current = Cursors.WaitCursor
Dim d As Integer
Dim p As Integer = 0
Dim a As String
Dim b() As String
Dim sr As New System.IO.StreamReader(directory1.ToString & "\ITEM.TXT")
a = sr.ReadLine
d = 0
Do Until a Is Nothing
If Len(a) = 0 Then
sr.Close()
File.Delete(directory.ToString & "\ITEM.TXT")
Exit Do
End If
x = x + 1
b = a.Split(",")
a = sr.ReadLine
If x = 1 Then
Continue Do 'line is the header
End If
'dcs,upc_alu,brand,desc1_style_modelname,desc2_style_article,attr,size,cost,price,qty
'Trim(b(0).Replace("'", "")) & "','" & Trim(b(1).Replace("'", "")) & "','" & Trim(b(2).Replace("'", "")) & "','" & Trim(b(3).Replace("'", "")) & "','" & Trim(b(4).Replace("'", "")) & "','" & Trim(b(5).Replace("'", "")) & "','" & Trim(b(6).Replace("'", "")) & "','" & Trim(b(7).Replace("'", "")) & "','" & Trim(b(8).Replace("'", "")) & "','" & Trim(b(9).Replace("'", ""))
rec.SetString(1, Trim(b(0).Replace("'", "")))
rec.SetString(2, Trim(b(1).Replace("'", "")))
rec.SetString(3, Trim(b(2).Replace("'", "")))
rec.SetString(4, Trim(b(3).Replace("'", "")))
rec.SetString(5, Trim(b(4).Replace("'", "")))
rec.SetString(6, Trim(b(5).Replace("'", "")))
rec.SetString(7, Trim(b(6).Replace("'", "")))
rec.SetSqlMoney(8, Trim(b(7).Replace("'", "")))
rec.SetSqlMoney(9, Trim(b(8).Replace("'", "")))
rec.SetSqlMoney(10, Trim(b(9).Replace("'", "")))
rs.Insert(rec)
d = d + 1
Loop
cmd.Dispose()
conn.Close()
getall()
Cursor.Current = Cursors.Default
MsgBox("Data successfully imported", MsgBoxStyle.Information, "")
Catch e As Exception
MessageBox.Show(e.Message)
End Try
Return Nothing
End Function