Saving Problem

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • darrel
    New Member
    • Nov 2006
    • 72

    #1

    Saving Problem

    Hi there vb masters i have a problem regrading the saving procedure in my program:

    Here my code:

    Code:
    Private Sub cmdSave_Click()
    Dim rsShowRec As New ADODB.Recordset
    Dim cnn As New ADODB.Connection
    
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = _
     "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & App.Path & "\dbTimeScheduling2.mdb;"
    cnn.Open
    
    For x = 0 To 15
    On Error Resume Next
    rsShowRec.Source = "Select * from RECORDS where [SUBJECT CODES] = ' " & txtCode(x).Text & " '"
    rsShowRec.Open , cnn, adOpenStatic, adLockOptimistic
         
        If Combo1(x).Text = "Select" Or Combo2(x).Text = "Select" Or Combo3(x).Text = "Select" Or Combo4(x).Text = "Select" Then
            MsgBox ("Cannot Save blank entries"), vbCritical
            rsShowRec.Close
            cnn.Close
            Exit Sub
        Else
            
            If rsShowRec.BOF = True Then
                
                    rsShowRec.AddNew
             
                    If IsNull(txtCode(x).Text) Then rsShowRec.Fields("SUBJECT CODES") = "" Else rsShowRec.Fields("SUBJECT CODES") = txtCode(x).Text
                    If IsNull(Combo1(x).Text) Then rsShowRec.Fields("TimeStart") = "" Else rsShowRec.Fields("TimeStart") = Combo1(x).Text
                    If IsNull(Combo2(x).Text) Then rsShowRec.Fields("TimeEnd") = "" Else rsShowRec.Fields("TimeEnd") = Combo4(x).Text
                    If IsNull(Combo4(x).Text) Then rsShowRec.Fields("ROOM") = "" Else rsShowRec.Fields("ROOM") = Combo2(x).Text
                    If IsNull(Combo3(x).Text) Then rsShowRec.Fields("DAYS") = "" Else rsShowRec.Fields("DAYS") = Combo3(x).Text
                    If IsNull(txtBlock.Text) Then rsShowRec.Fields("BLOCK SECTION") = "" Else rsShowRec.Fields("BLOCK SECTION") = txtBlock.Text
                    If IsNull(txtSY.Text) Then rsShowRec.Fields("SCHOOL YEAR") = "" Else rsShowRec.Fields("SCHOOL YEAR") = txtSY.Text
                    
                    rsShowRec.Update
                    rsShowRec.Requery
                Set rsShowRec = Nothing
            End If
        
            
            
        End If
        Next
            MsgBox ("Data Saved!"), vbOKOnly
    
    
    End Sub
    My problem is like this everytime i try to save some records in the table called "RECORDS", it saves it stores all the data in the table.. but the first row of the my table "RECORDS", that consist of field namely "TimeStart" , "TimeEnd", "ROOM", "DAYS". Everytime it save it always put an empty row in the first row of my fields,,

    I dont know how i can prevent it, i dont want to have empty rows in my fields in any location. Please help me...

    I hope someone can crack whats wrong with my code or how can i code it...

    Thank you very much.
  • jeffbroodwar
    New Member
    • Oct 2006
    • 118

    #2
    Hi,

    I saw your code..... i think there's a problem with the "If BOF == true then rs.addnew... you see if it reaches the (Beginning Of File) the addnew method of your recordset doesn't add the new record in that position.... meaning, the addnew method always adds a new record into the next row.

    ex :

    if your at row 3 of your database

    1
    2
    3 rs position..... if you try to invoke the addnew method here it'll save data at position 4 of your database. leaving 3rd position blank.... though i didn't try to paste your code in vb6 ide and tried it. I hope my suggestion will solve your problem...

    Best Regards,
    Jeff

    Comment

    • nairda
      New Member
      • May 2007
      • 39

      #3
      Originally posted by darrel
      Hi there vb masters i have a problem regrading the saving procedure in my program:

      Here my code:

      Code:
      Private Sub cmdSave_Click()
      Dim rsShowRec As New ADODB.Recordset
      Dim cnn As New ADODB.Connection
      
      Set cnn = New ADODB.Connection
      cnn.ConnectionString = _
       "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & App.Path & "\dbTimeScheduling2.mdb;"
      cnn.Open
      
      For x = 0 To 15
      On Error Resume Next
      rsShowRec.Source = "Select * from RECORDS where [SUBJECT CODES] = ' " & txtCode(x).Text & " '"
      rsShowRec.Open , cnn, adOpenStatic, adLockOptimistic
           
          If Combo1(x).Text = "Select" Or Combo2(x).Text = "Select" Or Combo3(x).Text = "Select" Or Combo4(x).Text = "Select" Then
              MsgBox ("Cannot Save blank entries"), vbCritical
              rsShowRec.Close
              cnn.Close
              Exit Sub
          Else
              
              If rsShowRec.BOF = True Then
                  
                      rsShowRec.AddNew
               
                      If IsNull(txtCode(x).Text) Then rsShowRec.Fields("SUBJECT CODES") = "" Else rsShowRec.Fields("SUBJECT CODES") = txtCode(x).Text
                      If IsNull(Combo1(x).Text) Then rsShowRec.Fields("TimeStart") = "" Else rsShowRec.Fields("TimeStart") = Combo1(x).Text
                      If IsNull(Combo2(x).Text) Then rsShowRec.Fields("TimeEnd") = "" Else rsShowRec.Fields("TimeEnd") = Combo4(x).Text
                      If IsNull(Combo4(x).Text) Then rsShowRec.Fields("ROOM") = "" Else rsShowRec.Fields("ROOM") = Combo2(x).Text
                      If IsNull(Combo3(x).Text) Then rsShowRec.Fields("DAYS") = "" Else rsShowRec.Fields("DAYS") = Combo3(x).Text
                      If IsNull(txtBlock.Text) Then rsShowRec.Fields("BLOCK SECTION") = "" Else rsShowRec.Fields("BLOCK SECTION") = txtBlock.Text
                      If IsNull(txtSY.Text) Then rsShowRec.Fields("SCHOOL YEAR") = "" Else rsShowRec.Fields("SCHOOL YEAR") = txtSY.Text
                      
                      rsShowRec.Update
                      rsShowRec.Requery
                  Set rsShowRec = Nothing
              End If
          
              
              
          End If
          Next
              MsgBox ("Data Saved!"), vbOKOnly
      
      
      End Sub
      My problem is like this everytime i try to save some records in the table called "RECORDS", it saves it stores all the data in the table.. but the first row of the my table "RECORDS", that consist of field namely "TimeStart" , "TimeEnd", "ROOM", "DAYS". Everytime it save it always put an empty row in the first row of my fields,,

      I dont know how i can prevent it, i dont want to have empty rows in my fields in any location. Please help me...

      I hope someone can crack whats wrong with my code or how can i code it...

      Thank you very much.
      Hi Darrel,
      I think you don't need the BOF

      If you want to put a new data in your database table, you don't have to look for your table's BOF or EOF.
      Example:
      Code:
      With rs
                              .AddNew
                              !TIMESTART = Text1.Text
                              !TIMEEND = Text2.Text
                              !ROOM = Text3.Text
                              !DAYS = Text4.Text
                              .Update
                          End With
      It would be different if you want to UPDATE your data, you have to search for your data until EOF is reached

      I hope this would help

      NAIRDA

      Comment

      Working...