Starting Again

[Pzykotik]

As some of you may know I've been asking some really stupid questions on the forums the last few weeks... So I'm starting from square 1 again.

I think I've gone and confused myself yet again so I'm starting with a fuel monitor. I'm going to give a screen with a couple of text boxes and a drop down box or two. The user will pick a registration number from the drop down box and a fuel type from the other drop down. Then using the keypad on the pda/phone thing enter in some numbers to the text boxes.

Pressing OK at the bottom of the screen will save these values to a table in a database.

I've had a brain fart and can't think how to do any of it so I'm designing the interface, going to create a windows CE database, then write the read/update commands.

Is anybody has any hints/tips or examples to help then thanks a bunch!

[petevick]

Hi,

First of all, I would make sure you have ALL of the user requirements defined.

Next, I would sit down, and design the database.

Then spec out which fields you need to access/maintain from the PPC, and whether you think they should be text boxes, combos etc etc.

When you have that designed, work out how you want the screen designed, and put together a skeleton screen.

Put together some sort of flow chart of what you are trying to acheive, look for samples showing things you are unsure of how to do.

When you have all this clear, that is the time to start coding. It will avoid the false starts, indecision, etc.

This may seem a long way round, but should avoid some of the pitfalls.

My 2 cents

Pete

[sapator]

I agree with Pete. Design is 80% of the work.
I once made a query creator and i haven't used good designing tactics.
The result....Instead of 2000-3000 lines of code i went to 8500!!! So D-E-S-I-G-N!

[Shaggy Hiker]

Post a bit of code if you get stuck.

[Pzykotik]

Ok, so I sat down with the "boss" (the IT guy here) who apparently knows what he's doing... Presumably he's planned this out already as I've just been told to write some code that parses a CSV file into an array... and then inserts that array into an SDF file.

I've posted my code so far, I'm reading up on Arrays at the moment... I SHOULD use a jagged array but I've been told to use a standard 2D array and fill in the blanks if the read data from the CSV doen't fill it completely. Am I going along the right lines with this? I'm getting a weird error when I run it too, an IndexOutOfRangeException... Anybody know what that is?



Imports System.Data
Imports System.Data.SqlServerCe

Module Read

Public Function Import(ByVal fileName As String) As Boolean

Dim csvFile As New System.IO.StreamReader(fileName)
Dim sRow As String
Dim sColumns As String()
Dim split As String = (ControlChars.Tab)
Dim sLoopCount As Int32 = 0
Dim importArray(20) As String '20 slots long (including zero)

Dim F1 As New Form1

sRow = csvFile.ReadLine
sColumns = sRow.Split(split)

While sRow <> ""
sLoopCount = sLoopCount + 1
If sColumns(0) <> "" Then

' while loop to add entries to array

Dim d, e As Integer
d = 1
e = 0
While e < 20
importArray(e) = sColumns(d)
e += 1
d += 1
End While

My.Forms.Form1.TextBox1.Text = "The Loop ran " & e & " times"

'F1.TextBox1.Text = F1.TextBox1.Text & " " & sLoopCount & " - " & sColumns(1) & " "

End If

sRow = csvFile.ReadLine
If sRow <> "" Then
sColumns = sRow.Split(split)
End If

End While

MessageBox.Show("Complete!", "Stores Import", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1)

Return True
End Function

End Module

[petevick]

This will read a CSV file into 2 arrays, 1 containing the 'records', and one containing the 'fields' within the records.

Put 2 multiline textboxes on a form and then execute this code...

Dim filestream As StreamReader
filestream = File.OpenText("c:\temp\x.csv")
Dim readcontents As String
readcontents = filestream.ReadToEnd()
filestream.Close()

Dim textdelimiter As String = ","
Dim rows As Array = Split(readcontents, vbCrLf)
Dim Cols As Array

TextBox1.Text = readcontents & vbCrLf 'Display what you have read in a textbox
Dim i, i1 As Integer
For i = 0 To UBound(rows)
Cols = Split(rows(i), textdelimiter)
For i1 = 0 To UBound(Cols)
TextBox2.Text &= "(" & i1 + 1 & ") " & Cols(i1) & vbCrLf
Next
Next

This will read the file, and display the contents in Textbox1. It will then process each row, and display the columns in Textbox2.

Does this help?

Pete

[Pzykotik]

Thanks, I'll try that in a min... My code works though, I just need to figure out how to add the array to an SDF file (SQLCEServer)



While sRow <> ""
sLoopCount = sLoopCount + 1
If sColumns(0) <> "" Then

' while loop to add entries to array

Dim e As Integer
e = 0
While e < 20
importArray(1) = sColumns(1)
importArray(2) = sColumns(2)
importArray(3) = sColumns(3)
importArray(4) = sColumns(4)
importArray(5) = sColumns(5)
e += 1
End While

'write insert statement here!!!!!

End If

sRow = csvFile.ReadLine
If sRow <> "" Then
sColumns = sRow.Split(split)
End If

End While

[Pzykotik]

Ignore this post, I've edited the one above because I'm thick like that...

[petevick]

http://samples.gotdotnet.com/quickstart/CompactFramework/ and SQL Server CE Database Creation shows the insert logic.

Have you already got your database designed?

In your sample, I would be extremely wary of hardcoding the indexes into the array - any deviation in the number of fields in the input file would screw your program

[Pzykotik]

Yea I know, usually I wouldn't hard code much but the way the existing system is designed (the origional database) it's guaranteed to put out the same number of fields every time.

I'm just writing the SDF file at the moment, I just need to write a simple 10 columns right?

[petevick]

it's guaranteed to put out the same number of fields every time. :)


10 columns - is it not 5?

[Pzykotik]

It's actually 7, I've changed the code again... lol... But yea, either way it's not 10 :ehh:

[petevick]

Hi,
the code I gave you should work no matter how many fields are in the file

[Pzykotik]

I'm just writing it up, I'll let you know how I do! Thankya very much by the way!

[Pzykotik]

Petevick, so I've written a function to add the array to a database, I get an error telling me that "the number of columns in the query and the table must match. [ Number of columns in query = 7, Number of columns in table = 8 ]"

Which stands to reason... I'm not inserting the record_id field... Does anybody know how to set that to auto insert, or do I have to generate an ID every time? :confused:

Any ideas?



Public Function importDatabase(ByVal value1, ByVal value2, ByVal value3, ByVal value4, ByVal value5, ByVal value6, ByVal value7)

Dim databaseExists As Boolean = False

If System.IO.File.Exists("\My Documents\test.sdf") Then
databaseExists = True
Else
Dim engine As New SqlCeEngine("Data Source = \My Documents\test.sdf")
engine.CreateDatabase()
End If

Dim ssceconn As New SqlCeConnection("Data Source = \My Documents\test.sdf")
ssceconn.Open()

If databaseExists = False Then
Dim sqlCreateTable As SqlCeCommand = ssceconn.CreateCommand()
sqlCreateTable.CommandText = "CREATE TABLE Imports(record_id int IDENTITY(0,1) PRIMARY KEY, field1 ntext(22), field2 ntext(22), field3 ntext(22), field4 ntext(22), field5 ntext(22), field6 ntext(22), field7 ntext(22))"
sqlCreateTable.ExecuteNonQuery()
End If

Dim sqlInsertRow As SqlCeCommand = ssceconn.CreateCommand()
sqlInsertRow.CommandText = "INSERT INTO Imports values(value1, value2, value3, value4, value5, value6, value7)"
sqlInsertRow.ExecuteNonQuery()

ssceconn.Close()

Return True
End Function

[petevick]

Hi,
always put a Try Catch around your sql statements - similar to...
Try
sqlCreateTable.ExecuteNonQuery()
Catch ex as SqlException
messagebox.show(ex.tostring)
End Try

Next -
sqlInsertRow.CommandText = "INSERT INTO Imports(field1, field2, field3, field4, field5, field6, field7) VALUES(value1, value2, value3, value4, value5, value6, value7)"

doesn't know anything about 'value1', 'value2' etc. In the sample, the fields are in quotes, so are treated as literals. In your case you want to put in the values. Either use a parameterised query, or do something like...


Dim strSql as string
strsql = "INSERT INTO Imports(field1, field2, field3, field4, field5, field6, field7) VALUES("
strSql += "'" & value1 & "',"
strSql += "'" & value2 & "',"
strSql += "'" & value3 & "',"
strSql += "'" & value4 & "',"
strSql += "'" & value5 & "',"
strSql += "'" & value6 & "',"
strSql += "'" & value7 & "')"

sqlInsertRow.CommandText = strSql

sqlInsertRow.ExecuteNonQuery()
so strSql should then look like
"INSERT INTO Imports(field1, field2, field3, field4, field5, field6, field7) VALUES('1','2','3','4','5','6','7')"
This should work, and will get you going. Once you have created a record, look at parameterised queries, and looping around the array to populate the parameters.

Pete

[Pzykotik]

Cool, I'm going to read up on Parameterized Queries... try them out and get back to you!

[Pzykotik]

Ok! So writen a function now that copies the parameters to the database, problem now is that some of the rows in the text file aren't long enough... So I need to find a way to fill in the spaces in the array so they'll be inserted, does that make sense? Something like redim array?



Public Function importDatabase(ByVal value1, ByVal value2, ByVal value3, ByVal value4, ByVal value5, ByVal value6, ByVal value7)

Dim databaseExists As Boolean = False

If System.IO.File.Exists("\My Documents\test.sdf") Then
databaseExists = True
Else
Dim engine As New SqlCeEngine("Data Source = \My Documents\test.sdf")
engine.CreateDatabase()
End If

Dim ssceconn As New SqlCeConnection("Data Source = \My Documents\test.sdf")
ssceconn.Open()

If databaseExists = False Then 'if the database already excists then the table will also so don't create it...
Dim sqlCreateTable As SqlCeCommand = ssceconn.CreateCommand()
sqlCreateTable.CommandText = "CREATE TABLE Imports(record_id int IDENTITY(0,1) PRIMARY KEY, field1 ntext, field2 ntext, field3 ntext, field4 ntext, field5 ntext, field6 ntext, field7 ntext)"
Try
sqlCreateTable.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End If

'Create a SqlCeCommand on your connection
Dim insertCommand As SqlCeCommand = ssceconn.CreateCommand()

'Set the CommandText for the command - The ?'s represent parameters that will be set later
insertCommand.CommandText = "Insert Into Imports(field1, field2, field3, field4, field5, field6, field7) Values (?,?)"

'Add parameters and assign them the values from the TextBoxes on the form
insertCommand.Parameters.Add(New SqlCeParameter("field1", SqlDbType.NText, 50))
insertCommand.Parameters.Add(New SqlCeParameter("field2", SqlDbType.NText, 50))
insertCommand.Parameters.Add(New SqlCeParameter("field3", SqlDbType.NText, 50))
insertCommand.Parameters.Add(New SqlCeParameter("field4", SqlDbType.NText, 50))
insertCommand.Parameters.Add(New SqlCeParameter("field5", SqlDbType.NText, 50))
insertCommand.Parameters.Add(New SqlCeParameter("field6", SqlDbType.NText, 50))
insertCommand.Parameters.Add(New SqlCeParameter("field7", SqlDbType.NText, 50))

insertCommand.Parameters("field1").Value = value1.ToString
insertCommand.Parameters("field2").Value = value2.ToString
insertCommand.Parameters("field3").Value = value3.ToString
insertCommand.Parameters("field4").Value = value4.ToString
insertCommand.Parameters("field5").Value = value5.ToString
insertCommand.Parameters("field6").Value = value6.ToString
insertCommand.Parameters("field7").Value = value7.ToString

Try
insertCommand.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

ssceconn.Close()

Return True
End Function

[petevick]

Ok! So writen a function now that copies the parameters to the database, problem now is that some of the rows in the text file aren't long enough... So I need to find a way to fill in the spaces in the array so they'll be inserted, does that make sense?

No - not really.

Do you mean some of the rows don't contain 7 fields?

And shouldn't Values (?,?) be values (?,?,?,?,?,?,?) ??

[Pzykotik]

duh.. Of course!!! How did I not notice that!? lol, ok... So I've set Values(?,?) to the proper thing... That is what I mean though, I'm just trying something then I'll post my code again... This might work "ReDim importArray(10)"?

[Pzykotik]

Imports System.Data
Imports System.Data.SqlServerCe

Module Read

Public Function Import(ByVal fileName As String) As Boolean

Dim csvFile As New System.IO.StreamReader(fileName)
Dim sRow As String
Dim sColumns As String()
Dim split As String = (ControlChars.Tab)
Dim sLoopCount As Int32 = 0
Dim importArray(10) As String '20 slots long (including zero)

Dim F1 As New Form1

sRow = csvFile.ReadLine
sColumns = sRow.Split(split)

While sRow <> ""
sLoopCount = sLoopCount + 1
If sColumns(0) <> "" Then

' while loop to add entries to array

Dim e As Integer
e = 0
While e < 10
importArray(0) = sColumns(0)
importArray(1) = sColumns(1)
importArray(2) = sColumns(2)
importArray(3) = sColumns(3)
importArray(4) = sColumns(4)
importArray(5) = sColumns(5)
importArray(6) = sColumns(6)
e += 1
End While

'write insert statement here!!!!!
Dim databaseExists As Boolean = False

If System.IO.File.Exists("\My Documents\ImportDatabase.sdf") Then
databaseExists = True
Else
Dim engine As New SqlCeEngine("Data Source = \My Documents\ImportDatabase.sdf")
engine.CreateDatabase()
End If

Dim ssceconn As New SqlCeConnection("Data Source = \My Documents\ImportDatabase.sdf")
ssceconn.Open()

If databaseExists = False Then 'if the database already excists then the table will also so don't create it...
Dim sqlCreateTable As SqlCeCommand = ssceconn.CreateCommand()
sqlCreateTable.CommandText = "CREATE TABLE Imports(record_id int IDENTITY(0,1) PRIMARY KEY, field1 ntext, field2 ntext, field3 ntext, field4 ntext, field5 ntext, field6 ntext, field7 ntext)"
Try
sqlCreateTable.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End If

ssceconn.Close()

'Create a SqlCeCommand on your connection
Dim insertCommand As SqlCeCommand = ssceconn.CreateCommand()

'Set the CommandText for the command - The ?'s represent parameters that will be set later
insertCommand.CommandText = "Insert Into Imports(field1, field2, field3, field4, field5, field6, field7) Values (?,?,?,?,?,?,?)"

insertCommand.Parameters.Add(New SqlCeParameter("field1", SqlDbType.NText, 50))
insertCommand.Parameters.Add(New SqlCeParameter("field2", SqlDbType.NText, 50))
insertCommand.Parameters.Add(New SqlCeParameter("field3", SqlDbType.NText, 50))
insertCommand.Parameters.Add(New SqlCeParameter("field4", SqlDbType.NText, 50))
insertCommand.Parameters.Add(New SqlCeParameter("field5", SqlDbType.NText, 50))
insertCommand.Parameters.Add(New SqlCeParameter("field6", SqlDbType.NText, 50))
insertCommand.Parameters.Add(New SqlCeParameter("field7", SqlDbType.NText, 50))

insertCommand.Parameters("field1").Value = importArray(0)
insertCommand.Parameters("field2").Value = importArray(1)
insertCommand.Parameters("field3").Value = importArray(2)
insertCommand.Parameters("field4").Value = importArray(3)
insertCommand.Parameters("field5").Value = importArray(4)
insertCommand.Parameters("field6").Value = importArray(5)
insertCommand.Parameters("field7").Value = importArray(6)

ssceconn.Open()

'Try
insertCommand.ExecuteNonQuery()
'Catch ex As Exception
'MessageBox.Show(ex.ToString)
'End Try

ssceconn.Close()

End If

sRow = csvFile.ReadLine
If sRow <> "" Then
sColumns = sRow.Split(split)
End If

End While

MessageBox.Show("Complete!", "Stores Import", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1)

Return True
End Function
End Module

[Pzykotik]

Ok,so above it the entire module... There's a button on form1 that runs it and that's it. I'm getting an error about field7, I haven't got a clue why... Does anybody else?

I've attached the text file I'm trying to read also...

[petevick]

Hi,
does this work?
If so, performance will be ****.

Do the database create/open etc outside the loop

So
Create/Open Database
Process File
Close Connection

Or Create/Open the database at the beginning of your program, and close it at the end. Opening databases takes quite a while, so do it as infrequently as possible.


You could also do
For ict = 1 to 7
insertCommand.Parameters.Add(New SqlCeParameter("field" & ict, SqlDbType.NText, 50))
insertCommand.Parameters("field" & ict).Value = importArray(ict-1)
Next

[Pzykotik]

As I said above, it sort of works... but I get an error with field7 that I can't work out! I'm still looking at it but I dunno... Performance is kinda crap, I'm going to split it up though. I don't know what that code you just posted does...

[petevick]

The code I did just saves you repeating the same code line after line.

Things that come back to bite you in the arse -

it's guaranteed to put out the same number of fields every time.

Take a look at the file - some records have 6 fields, some 7, some 8 and some 9

[Pzykotik]

Ok, not a clue why this isn't working. I've split the program up into functions, it all works fine... Passes the correct values. Every time it gets to "insertCommand.ExecuteNonQuery()" it throws an error "The column name is not valid. [ Node name (if any) = ,Column name = field7 ]"

I just don't get it. I've attached the complete program and the text file.

[petevick]

Hi,
take a look at
sqlCreateTable.CommandText = "CREATE TABLE Imports(record_id int IDENTITY(0,1) PRIMARY KEY, field0 ntext, field1 ntext, field2 ntext, field3 ntext, field4 ntext, field5 ntext, field6 ntext)"

You create fields 0 to 6 not 1 to 7

Read error messages like love letters :)

Pete

[Pzykotik]

Legend! So I've split the program up even more, got a function written that creates the database... That runs when the button's clicked at first, then I read the text file and run the insert statements (which are in another function).

I have to run the insert statements every loop which I imagin makes it a bit rubbish but the few seconds it takes isn't going to make that much difference as this "sync" routine will only be run on initialization of the program!

I'm not going to work on any more of this tonight, but tomorrow I've got to write the functionality to save the database back to a text file!

Thanks for all your help Petevick!

[Pzykotik]

Ah, so I tried the program out this morning with a proper CSV file and it took ages! SO, the solution I can imagin would be to use a 2D array... So I'm reading the file line by line into the 1D array first, then I'm going to add that array to a 2D array which I can then add to the database...

That'll work right? ha

[petevick]

Hi,
the code I gave you for reading the file into arrays should handle that OK

Also, put some traces in to see which part of the code is taking the time

Pete

[Pzykotik]

Hey guys, just thought I'd say thankyou for all your help. I wrote the module to read in from a CSV, put it into an array and then into a database. Then I got pissed off with the guy, had ANOTHER argument with him and quit. haha

So thanks again! I may be back on here in a few years if I touch VB.net CF again =P

Alex