dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 27

Thread: [RESOLVED] Some Explanation

  1. #1
    Join Date
    Dec 2008
    Location
    Step Into(F11)
    Posts
    465

    [RESOLVED] Some Explanation

    I Simple want to store data from flexGrid TO Database table accessrole.i simple want when user will click on save Button .Check Sign Should Store in a read and write column.i have written a code .But it is saying Type Mismatch .kindly give some hints .Any help would be highly appreciated.
    Code:
    Private Sub BtSave_Click()
    Dim lgrow As Long
    Dim perm As FormPermission, StrSql As String
    Dim con As ADODB.Connection, rs As ADODB.Recordset
     Set con = New ADODB.Connection
     If Not OpenConnection(con) Then
     Call MsgBox("Cannot open Connection", "VBOKONLY+VBCRITICAL", "Supplier")
      Set con = Nothing
      Exit Sub
     End If
       Set rs = New ADODB.Recordset
           StrSql = "Select * from AccessRole"
           rs.Open StrSql, con, adOpenDynamic, adLockOptimistic
        If rs.State = adStateOpen Then
             rs.AddNew
         rs.Fields("ReadForm") = flxPermissions.TextMatrix(lgrow, 0)
         rs.Fields("WriteForm") = flxPermissions.TextMatrix(lgrow, 1)
            rs.Update
          End If
          End Sub
    Last edited by firoz.raj; March 8th, 2010 at 03:59 AM.

  2. #2
    Join Date
    Apr 2009
    Posts
    394

    Re: Some Explanation

    Because you are returning the text value from the grid and trying to insert that text value (yes) into a bit field. What you need to do is to use an if statement and pass the appropriate value...
    Code:
    If UCase(flxPermissions.TextMatrix(lgrow, 0)) = "YES" Then
      rs.Fields("ReadForm").Value = True
    Else
      rs.Fields("ReadForm").Value = False
    End If


    Good Luck

  3. #3
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,725

    Re: Some Explanation

    It is even easier:
    Code:
    rs.Fields("ReadForm").Value = (Ucase(flxPermissions.TextMatrix(lgrow,0)) = "YES")
    No need for an if statement there.

    I had already explained at length to Mr firoz.raj about this problem, because he came to me by email with this code.
    I explained that this is not all he wants. The grid shows three records for an employee, one for each available form.
    So his update code is going to write three records not only one.

  4. #4
    Join Date
    Dec 2008
    Location
    Step Into(F11)
    Posts
    465

    Question Re: Some Explanation

    Now getting error . You cannot add or change a record because a related records is required in a employees table . Kindly let me know the idea.Any help would be highly appreciated
    Code:
    Private Sub BtSave_Click()
    Dim lgrow As Long
    Dim perm As FormPermission, StrSql As String
    Dim con As ADODB.Connection, rs As ADODB.Recordset
     Set con = New ADODB.Connection
     If Not OpenConnection(con) Then
     Call MsgBox("Cannot open Connection", "VBOKONLY+VBCRITICAL", "Supplier")
      Set con = Nothing
      Exit Sub
     End If
       Set rs = New ADODB.Recordset
           StrSql = "Select * from AccessRole"
           rs.Open StrSql, con, adOpenDynamic, adLockOptimistic
        If rs.State = adStateOpen Then
         rs.AddNew
         rs.Fields("Formid").Value = (UCase(flxPermissions.TextMatrix(lgrow, 0)) = "FormName")
         rs.Fields("ReadForm").Value = (UCase(flxPermissions.TextMatrix(lgrow, 1)) = "YES")
         rs.Fields("WriteForm").Value = (UCase(flxPermissions.TextMatrix(lgrow, 2)) = "Yes")
         
    '     rs.Fields("FormName") = flxPermissions.TextMatrix(lgrow, 0)
    '     rs.Fields("WriteForm") = flxPermissions.TextMatrix(lgrow, 1)
    '     rs.Fields("ReadForm") = flxPermissions.TextMatrix(lgrow, 2)
         rs.Update
         End If
         End Sub
    Last edited by firoz.raj; March 8th, 2010 at 04:56 AM.

  5. #5
    Join Date
    Apr 2009
    Posts
    394

    Re: Some Explanation

    Okay, so what that error is telling you is that you need to have a related record from the employee's table. so you need a rs.fields("emp_id") = some value from the parent table's emp_id field as the field in accessrole table is not an autonumber but a foreign key...

  6. #6
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Some Explanation

    Get the key, or don't add records that are linked
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  7. #7
    Join Date
    Dec 2008
    Location
    Step Into(F11)
    Posts
    465

    Question Re: Some Explanation

    Get the key, or don't add records that are linked
    Still some doubt.when i comment the records which are linked.rs.Fields("Emp_id").Value = (UCase(flxEmployees.TextMatrix(lgrow, 2)) = "Employee_id")then also get the same error.Kindly clarify.
    Code:
    Private Sub BtSave_Click()
    Dim lgrow As Long
    Dim perm As FormPermission, StrSql As String
    Dim con As ADODB.Connection, rs As ADODB.Recordset
     Set con = New ADODB.Connection
     If Not OpenConnection(con) Then
     Call MsgBox("Cannot open Connection", "VBOKONLY+VBCRITICAL", "Supplier")
      Set con = Nothing
      Exit Sub
     End If
       Set rs = New ADODB.Recordset
           StrSql = "Select * from AccessRole "
           rs.Open StrSql, con, adOpenDynamic, adLockOptimistic
           Debug.Print StrSql
        If rs.State = adStateOpen Then
         rs.AddNew
         rs.Fields("Emp_id").Value = (UCase(flxEmployees.TextMatrix(lgrow, 2)) = "Employee_id")
         rs.Fields("Formid").Value = (UCase(flxPermissions.TextMatrix(lgrow, 0)) = "FormName")
         rs.Fields("ReadForm").Value = (UCase(flxPermissions.TextMatrix(lgrow, 1)) = "YES")
         rs.Fields("WriteForm").Value = (UCase(flxPermissions.TextMatrix(lgrow, 2)) = "Yes")
         
    '     rs.Fields("FormName") = flxPermissions.TextMatrix(lgrow, 0)
    '     rs.Fields("WriteForm") = flxPermissions.TextMatrix(lgrow, 1)
    '     rs.Fields("ReadForm") = flxPermissions.TextMatrix(lgrow, 2)
         rs.Update
         End If
         End Sub

  8. #8
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,725

    Re: Some Explanation

    It seems you do not understand these statements at all:
    Code:
         rs.Fields("Emp_id").Value = (UCase(flxEmployees.TextMatrix(lgrow, 2)) = "Employee_id")
         rs.Fields("Formid").Value = (UCase(flxPermissions.TextMatrix(lgrow, 0)) = "FormName")
         rs.Fields("ReadForm").Value = (UCase(flxPermissions.TextMatrix(lgrow, 1)) = "YES")
         rs.Fields("WriteForm").Value = (UCase(flxPermissions.TextMatrix(lgrow, 2)) = "Yes")
    The third one is the only one which makes sense.
    The fourth one would make sense if it is ="YES" in the end. UCase of a string will never be "Yes", because "Yes" contains lower case characters as well.
    COMPLETE and UTTERLY wrong are the first two statements.
    The expression
    (UCase(flxEmployees.TextMatrix(lgrow, 2)) = "Employee_id")
    will ALWAYS result in false because NO uper case word in the world will ever match "Employee_id", right?
    So
    rs.Fields("Emp_id").Value = (UCase(flxEmployees.TextMatrix(lgrow, 2)) = "Employee_id")
    will always put False in rs.Fields("Emp_id")

    Please think before you simply apply an expression where it does not belong.
    Reconsider the first two lines.
    What is it what you want to have in the fields "Emp_id" and "Formid"?
    Surely not the UCase expressions you used, which both will always result in False.
    Last edited by WoF; March 1st, 2010 at 09:40 AM.

  9. #9
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,725

    Re: Some Explanation

    To hint some more:
    The field "Emp_id" will expect the ID number of an employee. But because you fill it with the value False, it won't fit any record of the related table.
    The next error coming up will be you put the value False in the field "Formid", too, which expects a string which is the formname.

  10. #10
    Join Date
    Dec 2008
    Location
    Step Into(F11)
    Posts
    465

    Question Re: Some Explanation

    Wof sir sorry i am redirecting some where else .on BtSave button code.in my Database .
    Sup_id is a autonumber(Not Duplicate).When i tried to save.it says the changes you are requested were not successful.Because they would Create a Duplicate Value.Please
    Give me Some Comment.Why i am getting .this errror message
    Code:
    If m_sup Is Nothing Then
               Set m_sup = New Supplier
               m_sup.SupName = txtSupplierName.Text
               m_sup.OfficeAddress = TxtOfficeaddress.Text
               m_sup.FaxNo = TxtFaxNo.Text
               m_sup.ContactPerson = TxtContactPerSon.Text
               Set rs = New ADODB.Recordset
               rs.Open "Select * from suppliers", con, adOpenDynamic, adLockOptimistic
               If rs.state = adStateOpen Then
               rs.AddNew
               
               rs.Fields("Sup_name") = m_sup.SupName
               rs.Fields("Office_address") = m_sup.OfficeAddress
               rs.Fields("fax_no") = m_sup.FaxNo
               rs.Fields("Contact_person") = m_sup.ContactPerson
               rs.Update
             '  rs.Fields("Sup_id") = m_sup.Supid
              m_sup.Supid = rs.Fields("Sup_id")
               Call frmSupplier.Add(m_sup)
               rs.Close
               End If
    Last edited by firoz.raj; March 8th, 2010 at 03:24 AM.

  11. #11
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,329

    Re: Some Explanation

    If you have an auto number field then you should not be including it in your addnew block. The database will assign a value to it. You do not.

    Code:
     rs.AddNew
               rs.filelds("Sup_id")=m_sup.supid
               rs.Fields("Sup_name") = m_sup.SupName
               rs.Fields("Office_address") = m_sup.OfficeAddress
               rs.Fields("fax_no") = m_sup.FaxNo
               rs.Fields("Contact_person") = m_sup.ContactPerson
               rs.Update
    Last edited by DataMiser; March 7th, 2010 at 08:23 AM.

  12. #12
    Join Date
    Dec 2008
    Location
    Step Into(F11)
    Posts
    465

    Question Re: Some Explanation

    if i Remove this line .rs.Fields("Sup_name") = m_sup.SupName.then also getting the same Error .
    Code:
     If checkinput = True Then
             If m_sup Is Nothing Then
               Set m_sup = New Supplier
               m_sup.SupName = txtSupplierName.Text
               m_sup.OfficeAddress = TxtOfficeaddress.Text
               m_sup.FaxNo = TxtFaxNo.Text
               m_sup.ContactPerson = TxtContactPerSon.Text
               Set rs = New ADODB.Recordset
               rs.Open "Select * from suppliers", con, adOpenDynamic, adLockOptimistic
               If rs.state = adStateOpen Then
               rs.AddNew
                rs.Fields("Office_address") = m_sup.OfficeAddress
               rs.Fields("fax_no") = m_sup.FaxNo
               rs.Fields("Contact_person") = m_sup.ContactPerson
               rs.Update
               rs.Fields("Sup_id") = m_sup.Supid
           '    m_sup.Supid = rs.Fields("Sup_id")
               Call frmSupplier.Add(m_sup)
               rs.Close
               End If

  13. #13
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,329

    Re: Some Explanation

    I was talking about the ID field, Why would you remove the name?

    You still have a line that sets the autonumber field= to something in there after the update. I wouldn't think that would cause the error but the line of code should not be there in any case.

    Are any of the other fields set to not allow duplicates?

    Perhaps there is one which you are not setting that does not allow dupes? If so this may have worked one time but then once the null or whatever default value is added no additional records will be accepted due to duplicate values. Check your database and see which fields do not allow dupes and you will find the problem.

  14. #14
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Some Explanation

    How can you assign an AUTONUMBER to field to a DB field that generates an AUTONUMBER ?

    What number would that become in the original field?


    Seems you slept thru some things, that are going to hold you back, until you do something about it.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  15. #15
    Join Date
    Dec 2008
    Location
    Step Into(F11)
    Posts
    465

    Question Re: Some Explanation

    i have made auto number field to sup_id of Suppliers table.
    Last edited by firoz.raj; March 8th, 2010 at 03:31 AM.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)