Update/Insert ODBC Help

[ZeroEffect]

Thanks for Taking a look.

 

Up to this point all I ever needed to do was read info from a DBF file. No problem I've been through the MSDN examples and help from the fourm. Now I have come to a point where I need to Update/Insert Information to a DBF file. I have tried the examples on MSDN and others I have found in the fourm but still no luck. I can get me DataTable to update but not the file itself. Here is my code to update the DataTable & a Listview

 

   Public Sub AddToDB()
       Try
           Dim Itm As ListViewItem
           Dim strConnect, strSql As String
           Dim St, Et, D, DS As String
           Dim I As Integer
           Dim objDA1 As New OdbcDataAdapter
           Dim objDS1 As New Data.DataSet
           Dim myRow As DataRow
           Dim myCommand As New OdbcCommand
           Dim mvarSysProjPath As String = Application.StartupPath & "\"
           strConnect = "Provider=MSDASQL/SQLServer ODBC;Driver={Microsoft Visual FoxPro Driver};" _
             & "SourceType=DBF;SourceDB=" & mvarSysProjPath _
             & ";InternetTimeout=300000;Transact Updates=True"

           strSql = "SELECT StartTime, EndTime, Day, Descript FROM TMDENTS.dbf"
           objDA1 = New OdbcDataAdapter(strSql, strConnect)
           objDA1.Fill(objDS1, "TMDENTS")

           'Adds the row to the DataTable

           Dim t As DataTable
           t = objDS1.Tables("TMDENTS")
           ' Use the NewRow method to create a DataRow with the table's schema.
           Dim newRow As DataRow = t.NewRow()
           ' Set values in the columns:
           newRow("StartTime") = Format(TimedEventsForm.DateTimePicker(0).Value, "HH:mm:ss")
           newRow("EndTime") = Format(TimedEventsForm.DateTimePicker(1).Value, "HH:mm:ss")
           newRow("Day") = TimedEventsForm.ComboBox1.Text
           newRow("Descript") = TimedEventsForm.TextBox1.Text
           ' Add the row to the rows collection.
           t.Rows.Add(newRow)

           TimedEventsForm.ListView1.Items.Clear()
           'reWrite the Data to the listview 
           For I = 0 To objDS1.Tables("TMDENTS").Rows.Count - 1
               With objDS1.Tables("TMDENTS").Rows(I)
                   St = Replace(!StartTime, " ", "")
                   Et = Replace(!EndTime, " ", "")
                   D = Replace(!Day, " ", "")
                   DS = !Descript
                   Itm = TimedEventsForm.ListView1.Items.Add(St)
                   Itm.SubItems.Add(Et)
                   Itm.SubItems.Add(D)
                   Itm.SubItems.Add(DS)
               End With
           Next I

           'Some How Save the Info to The DBF File.


           objDS1.Dispose()


       Catch ex As Exception
           MsgBox(ex.Message)
       End Try

   End Sub

 

I'm kinda stuck Trying to figure out the odbcCommand class And CommandText. The examples I have found and tried I can't seem to figure out.

 

Thanks for any help you my provide.

 

ZeroEffect

[ZeroEffect]

I figured out INSERT command. But I can't find examples of "DELETE, UPDATE". Could someone help me out here.

 

Zeroeffect

[Derek Stone]

Perhaps posting the code for the "INSERT" would be beneficial to all?

[ZeroEffect]

Sorry, I will be shortly, I am under the weather and well looking at a computer screen has been taking a toll on me. :( I am also Trying to figure out "UPDATE". I'll try to post my code in the next day or so.

 

ZeroEffect

[ZeroEffect]

OK here is the code I have.

 

            
       Dim strConnect, strSql As String
       Dim I As Integer
       Dim objDA1 As New OdbcDataAdapter
       Dim objDS1 As New Data.DataSet
       Dim myRow As DataRow
       Dim cmdText As String
       Dim mvarSysProjPath As String = Application.StartupPath & "\"
       strConnect = "Provider=MSDASQL/SQLServer ODBC;Driver={Microsoft Visual FoxPro Driver};" _
                       & "SourceType=DBF;SourceDB=" & mvarSysProjPath _
                       & ";InternetTimeout=300000;Transact Updates=True"

       strSql = "SELECT ID, StartTime, EndTime, Day, Descript FROM TMDENTS.dbf"
       objDA1 = New OdbcDataAdapter(strSql, strConnect)
       objDA1.Fill(objDS1, "TMDENTS")
       Dim myConn As New OdbcConnection(strConnect)
       Dim myCommand As New OdbcCommand("", myConn)

Dim t As DataTable
           t = objDS1.Tables("TMDENTS")
           ' Use the NewRow method to create a DataRow with the table's schema.
           Dim newRow As DataRow = t.NewRow()
           ' Set values in the columns:
           ID = objDS1.Tables("TMDENTS").Rows.Count + 1  'TimedEventsForm.ListView1.Items.Count + 1
           newRow("ID") = ID
           newRow("StartTime") = St
           newRow("EndTime") = Et
           newRow("Day") = D
           newRow("Descript") = DS
           ' Add the row to the rows collection.
           t.Rows.Add(newRow)
           cmdText = "INSERT INTO TMDENTS (ID, StartTime, EndTime, Day, Descript) Values('" & ID & "', '" & St & "', '" & Et & "', '" & D & "', '" & RTrim(DS) & "')"
       End If
       'Some How Save the Info to The DBF File
       myCommand.Connection.Open()
       myCommand.CommandText = cmdText

       myCommand.ExecuteNonQuery()
       myCommand.Connection.Close()
       objDS1.Dispose()

 

The code above I use to add a row to the database and below is the the line for making it delete a row.

 

cmdText = "DELETE FROM TMDENTS WHERE ID = '" & ID & "'"

 

I'm still trying to figure out "UPDATE"

 

 

I hope this helps

 

ZeroEffect

[Derek Stone]

Does the following not work?

 

UPDATE [TMDENTS] SET [Descript] = 'Foobar' WHERE [iD] = 1

[ZeroEffect]

Does the following not work?

 

UPDATE [TMDENTS] SET [Descript] = 'Foobar' WHERE [iD] = 1

 

I have tried the code above and you'll also see what I tried before your post. The error i get is

Error [42S22][Microsoft][ODBC Visual Fox Pro Driver]SQL: Column'   'is not found.

I File I am updating/deleting/adding to is a Dbase IV file. I am using this file typr to be consistant with another database file I have to access.

 

Here is the code for my sub. I have also gone through and found alot I didn't need. So the is a modified version of the code I posted above.

 

   Public Sub AddToDB(ByVal intOption As Integer)
       Try
           Dim strConnect, strSql As String
           Dim objDA1 As New OdbcDataAdapter
           Dim objDS1 As New Data.DataSet
           Dim cmdText As String
           Dim mvarSysProjPath As String = Application.StartupPath & "\"
           strConnect = "Provider=MSDASQL/SQLServer ODBC;Driver={Microsoft Visual FoxPro Driver};" _
                           & "SourceType=DBF;SourceDB=" & mvarSysProjPath _
                           & ";InternetTimeout=300000;Transact Updates=True"

           strSql = "SELECT StartTime, EndTime, Day, Descript FROM TMDENTS.dbf"
           objDA1 = New OdbcDataAdapter(strSql, strConnect)
           objDA1.Fill(objDS1, "TMDENTS")
           Dim myConn As New OdbcConnection(strConnect)
           Dim myCommand As New OdbcCommand("", myConn)

           If intOption = 3 Then
               cmdText = "UPDATE [TMDENTS] SET [Descript] = 'foobar' WHERE [iD] = 1"
               'cmdText = "UPDATE TMDENTS Set StartTime = " & St & ", Set EndTime = " & Et & ", Set Day = " & D & ", Set Descript = " & RTrim(DS)
           ElseIf intOption = 2 Then
               cmdText = "DELETE FROM TMDENTS WHERE ID = '" & ID & "'"
           ElseIf intOption = 1 Then
               ID = objDS1.Tables("TMDENTS").Rows.Count + 1 'Row ID Needs to be an auto increment column
               cmdText = "INSERT INTO TMDENTS (ID, StartTime, EndTime, Day, Descript) Values('" & ID & "', '" & St & "', '" & Et & "', '" & D & "', '" & RTrim(DS) & "')"
               'cmdText = "INSERT INTO TMDENTS (StartTime, EndTime, Day, Descript) Values('" & St & "', '" & Et & "', '" & D & "', '" & RTrim(DS) & "')"
               'Will Not add to table when ID is an Auto increment column
           End If
           'Some How Save the Info to The DBF File
           myCommand.Connection.Open()
           myCommand.CommandText = cmdText

           myCommand.ExecuteNonQuery()
           myCommand.Connection.Close()
           objDS1.Dispose()

       Catch ex As Exception
           MsgBox(ex.Message)
       End Try

   End Sub

 

I'm also having a problem adding to the database when i set the ID column to auto increment. I am still very new to coding for database and .Net so please bare with me.

 

Thanks for your help

 

ZeroEffect

[ZeroEffect]

Cool I now have UPDATE working. I had to change the code to look like this.

 

UPDATE TMDENTS SET Descript = 'foobar' WHERE ID = '1'

[code=visualbasic]

 

Your example was also the first one that made sense to me, The MSDN site was confusing me a bit.

 

Thanks

 

ZeroEffect