saving to sql from windows imbedded CE 6.0

[elianeasmar]

Hello. I have an application on a handheld that reads rfid and stores the result in a txt file. all good so far. i read the file in order to save it into sql server. but i am having a big error. Here is my code. Is there anything wrong with it? because it seems very logic to me. I thik it's a connection error. but the connection is woking. i am really confused.

 Public Function ChangeFormat(ByVal dtm As String) As Date
        Try
            Dim temp As String = ""
            temp = Mid(dtm, 4, 2) & "/"
            temp = temp & Mid(dtm, 1, 2) & "/"
            temp = temp & Mid(dtm, 7, 4) & " "
            temp = temp & Mid(dtm, 12)
            ChangeFormat = CDate(temp)
        Catch ex As Exception
            MsgBox("Change Format Error: " & ex.ToString)
        End Try
        
    End Function
    Private Sub cmdimport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdimport.Click
        Dim ind As Integer = 0
        Try
            If connect2008() = True Then
                ind = 1
                If MsgBox("Do you want to export data to server?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                    Dim _cmd As New SqlCommand("", connchip)
                    Dim i As Integer = 0
                    Dim strsql2 As String = ""
                    Dim tmp As String = ""
                    Dim ln() As String
                    ind = 2
                    Dim f As New System.IO.StreamReader("\ticketpro\export\" & Format(Now, "yyyyMMdd") & "HH.txt")
                    ind = 3


                    While Not f.EndOfStream
                        tmp = f.ReadLine
                        ln = tmp.Split(",")
                        strsql2 = "insert into handheld ("
                        strsql2 = strsql2 & "dat, "
                        strsql2 = strsql2 & "ticketnum, "
                        strsql2 = strsql2 & "stationid, "
                        strsql2 = strsql2 & "hhid, "
                        strsql2 = strsql2 & "status) values ("
                        strsql2 = strsql2 & "'" & ChangeFormat(ln(1)) & "', "
                        strsql2 = strsql2 & "'" & ln(2) & "', "
                        strsql2 = strsql2 & ln(0) & ", "
                        strsql2 = strsql2 & ln(4) & ", "
                        strsql2 = strsql2 & "'" & ln(3) & "') "
                        ind = 4
                        MsgBox(strsql2)
                        Clipboard.SetDataObject(strsql2)
                        _cmd = New SqlCommand(strsql2, connchip)
                        ind = 5
                        _cmd.ExecuteNonQuery()
                    End While
                    ind = 6
                    _cmd.Dispose()
                    f.Close()
                    f.Dispose()
                    connchip.Close()
                    MsgBox("Export Done")
                Else
                    MsgBox("Server not in range")
                End If
            End If
        Catch ex As Exception


            MsgBox("import error " & ex.ToString)
            MsgBox("ind" & ind & ": " & ex.Message)


        End Try
    End Sub
    Public Function connect2008() As Boolean
        connect2008 = False
        Dim path As String
        Dim database As String
        Dim temp As String = ""
        Dim temp2(15) As String
        Dim server As String
        Dim FileReader As StreamReader
        FileReader = New StreamReader("\ticketpro\chip.txt")
        temp = FileReader.ReadToEnd()
        temp2 = Split(temp, ",")
        FileReader.Close()
        database = temp2(8)
        server = temp2(7)


        '' path = "Server=" & server & ";Database=" & database & "; Uid=system; pwd=startup;"  '"Server=192.168.1.151;Database=HomePro; Uid=valet; pwd=chipchip;"
        path = "Data Source=192.168.1.37\SQLEXPRESS2012;Initial Catalog=ticketprozaarour; User Id=system; Password=startup;"
        Try
            connchip = New SqlConnection(path)
            connchip.Open()
            If connchip.State = ConnectionState.Open Then connect2008 = True
        Catch ex As Exception
            connect2008 = False
            MsgBox("error in function connect 2008")
            MsgBox(ex.Message)
            MsgBox(ex.ToString)
        End Try
    End Function

This is the error. ny Help would be appreciated. Thank you.

System.Data.SqlClient.SqlException: SqlException\par
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, TdsParserState state)\par
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, TdsParserState state)\par
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()\par
   at System.Data.SqlClient.TdsParser.Run(RunBehavior run, SqlCommand cmdHandler, SqlDataReader dataStream)\par
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()\par
   at HomePro_231.importexportfrm.cmdimport_Click(Object sender, EventArgs e)\par
   at System.Windows.Forms.Control.OnClick(EventArgs e)\par
   at System.Windows.Forms.Control.WnProc(WM wm, Int32 wParam, Int32 lParam)\par
   at System.Windows.Forms.ContainerControl.WnProc(WM wm, Int32 wParam, Int32 lParam)\par
   at System.Windows.Forms.Control._InternalWnProc(WM wm, Int32 wParam, Int32 lParam)\par
   at Microsoft.AGL.Forms.EVL.EnterModalDialog(IntPtr hwnModal)\par
   at System.Windows.Forms.Form.ShowDialog()\par
   at HomePro_231.valetfrm.TImageButton1_Click(Object sender, EventArgs e)\par
   at System.Windows.Forms.Control.OnClick(EventArgs e)\par
   at System.Windows.Forms.Control.WnProc(WM wm, Int32 wParam, Int32 lParam)\par
   at System.Windows.Forms.ContainerControl.WnProc(WM wm, Int32 wParam, Int32 lParam)\par
   at System.Windows.Forms.Control._InternalWnProc(WM wm, Int32 wParam, Int32 lParam)\par
   at Microsoft.AGL.Forms.EVL.EnterModalDialog(IntPtr hwnModal)\par
   at System.Windows.Forms.Form.ShowDialog()\par
   at HomePro_231.mainmdl.main()\par

 

[jmcilhinney]

It's not a connection error because the call stack shows that it's occurring on your call to ExecuteNonQuery. I would assume that the connection is already open at that stage. Have you actually looked at the SQL code that's being executed? You're building it in a very ugly way so there's every chance that you've made a mess of it and you're trying to execute invalid SQL. That should be your first order of business.

 

[elianeasmar]

Hi. Yes the connection is open. the problem was a missing field in a table in the database. I have made my tests after adding the field; and it worked.
But is it an ugly way?
Thanks for replying.

 

[jmcilhinney]

Yes, it's an ugly way. If XML literals are available to you then you should one, otherwise just use a single String. Either way you should be using parameters and there's no reason for all that concatenation for that length.

Dim sql = <sql>
              INSERT INTO handheld (dat, ticketnum, stationid, hhid, status)
              VALUES (@dat, @ticketnum, @stationid, @hhid, @status)
          </sql>
Dim command As New SqlCommand(sql.Value, connection)

Dim sql = "INSERT INTO handheld (dat, ticketnum, stationid, hhid, status) " &
          "VALUES (@dat, @ticketnum, @stationid, @hhid, @status)"
Dim command As New SqlCommand(sql, connection)

I would recommend putting all the data into a DataTable and saving it all in a single batch with a data adapter but, if you're determined to use a loop, you should be creating just one command and then setting its parameters in the loop. To learn why and how to use parameters, follow the Blog link in my signature below and check out my post on Parameters In ADO.NET.

 

[elianeasmar]

okay. Thank you. I will.