Results 1 to 8 of 8

Thread: How to Make DataGrid1 Content go into the actual Connected Database?

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2020
    Posts
    29

    How to Make DataGrid1 Content go into the actual Connected Database?

    I learned from an old post here as to how to make a DataGrid1 take data from textBoxes via variables connected to these textboxes i.e.,
    Price = Val(Textbox.Text), but the code I founddoes not save data into the Access file; it rather takes data from the said variables and stores them in the
    temporary table and nothing is written into the database that's connected to the Adodc1. I tried to manipulate the code but all ways result in errors.

    My Database's name is Inventory and the table name is InventoryBB

    This is the code I have and again it works, adds a new column after each entry, but nothing is saved into database after closing the program.



    Code:
    Private sub Form_Load()
    
    Product = Txtbox1.Text
    Price = Val(TxtBox2.Text)
    Quantity = Val(TxtBox3.Text)
    
    
    Dim rs = New ADODB.Recordset
    With DataGrid1
            .AllowAddNew = True
            .AllowDelete = True
            .AllowUpdate = True
        End With
        
        'create an in-memory recordset with your required fields
        Set rs = New ADODB.Recordset
        rs.Fields.Append "Product", adVarChar, 10
        rs.Fields.Append "Price", adVarChar, 10
        rs.Fields.Append "Quantity", adVarChar, 10
       
        
        rs.Open
    
        'bind recordset to Grid
        Set DataGrid1.DataSource = rs
       
    DataGrid1.ScrollBars = dbgNone
    
    Private Sub CmdLog_Click()
    Dim rs As ADODB.Recordset
    
    Set rs = DataGrid1.DataSource
    Set DataGrid1.DataSource = rs
    
    rs.AddNew
    rs.Fields("Product").Value = Product
    rs.Fields("Price").Value = Price
    rs.Fields("Quantity").Value = Quantity
    
    
    DataGrid1.ReBind
    How to make the program write data into the actual database and not just on the DataGrid1?

    I got this code from (Brucevde) and conditioned it to suit my objects:
    https://www.vbforums.com/showthread....ow-in-DataGrid
    Last edited by SaudVb; Aug 6th, 2020 at 05:16 PM.

  2. #2
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to Make DataGrid1 Content go into the actual Connected Database?

    No idea why you want to store numeric data as text.

    Here's an example that has almost no code at all.

    The bulk of the logic creates a new clean Jet MDB in Module1.bas if one doesn't exist. Not "an Access database," and if you let the parasite MS Access touch it, it will just add junk in private structures your program does not use or need.

    There is very little additional procedural logic because nearly everything is done declaratively. Two exceptions:

    1. DataEnvironment1: Some procedural code to set up Connection1's Data Source and rsCommand1's characteristics to get around some Jet limitations in data binding.
    2. Form1: Some procedural code to modify the workings of DataGrid1, mainly for convenience.


    DataEnvironment1.Dsr code:
    Code:
    Option Explicit
    
    Private Sub DataEnvironment_Initialize()
        Connection1.Properties("Data Source").Value = DB_PATH
    
        'Note that we won't even get THIS far if the Command1 CursorLocation wasn't assigned
        'adUseClient at design time:
        '
        '   The DataEnvironment1.rsCommand1 we bang on below would get a runtime error as
        '   soon as we try to touch it in the With statement below!
    
        With rsCommand1
            .CursorLocation = adUseServer
            .CursorType = adOpenKeyset
    
            'And this is why we are twiddling rsCommand1 in the first place, because
            'of a Jet/ACE Provider limitation for all CursorType values <> adOpenStatic:
            .Properties("IRowsetIdentity").Value = True
    
            'More detail:
            '
            '   MS KB 224192
            '
            '   PRB: DataGrid Is Not Populated Using Jet.OLEDB.4.0 Provider and ADO
            '        Server Side Cursor
            '
            '   The Microsoft.Jet.OLEDB.4.0 Provider does not support the DBPROP_LITERALIDENTITY
            '   property, so the provider then needs to implement IRowsetIdentity. ADO should
            '   set this property before opening the rowset. If you ask for an interface (such
            '   as IRowsetIdentity), and OLE DB has not opened the rowset to support it, you
            '   will get the E_NOINTERFACE error back even if the provider supports it.
        End With
    End Sub
    Form1.frm code:
    Code:
    Option Explicit
    
    Private Sub DataGrid1_KeyDown(KeyCode As Integer, Shift As Integer)
        'MS KB 291191
        '
        'How To: Use the ENTER Key to Navigate in the Visual Basic DataGrid Control
        If KeyCode = vbKeyReturn Then
            KeyCode = vbKeyTab
        End If
    End Sub
    
    Private Sub Form_Load()
        With DataGrid1
            .WrapCellPointer = True
            .TabAction = dbgGridNavigation
            .Columns("Id").Visible = False
        End With
    End Sub
    
    Private Sub Form_Resize()
        If WindowState <> vbMinimized Then
            DataGrid1.Move 0, 0, ScaleWidth, ScaleHeight
        End If
    End Sub
    Name:  sshot1.png
Views: 194
Size:  2.0 KB

    There really isn't that much to it.

    One problem is that VB's data binding and declarative logic isn't as visible as text, as for example the old QBasic style procedural logic most shadetree coders picked up from creaky old Classic ASP VBScript samples.

    Without having taken the Microsoft VB 6.0 training courses most people are a bit lost. I don't think anyone is even holding those classes any more, and I'm not sure the study guides are even very readily available.

    The documentation that comes with VB6 is some help, but it can only go so far. An awful lot of what you do isn't typed out code but actions you perform within the IDE using its GUI, working through Property Pages and such.

    This is one area where VB6 tutorials in video form might actually be useful.
    Attached Files Attached Files

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to Make DataGrid1 Content go into the actual Connected Database?

    Oh, you might try opening DataEnvironment1.Dsr in Notepad or another text editor. That'll probably give you an idea of what goes on there. Probably not a good idea to modify it that way though unless you really know what you are doing.

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Apr 2020
    Posts
    29

    Re: How to Make DataGrid1 Content go into the actual Connected Database?

    Thank you I am reading this. Would you please tell me what's the difference between a record source, .data source, dataset, and recordset? Alsom is "rs" is system expression or just a variable that can be replaced with any other word? I have been doing my research all day about all this and I am confused.

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to Make DataGrid1 Content go into the actual Connected Database?

    Well "rs" is just a name. Normally you can name it anything except when it is generated by some library (for example the DataEnvironment creates Recordsets named like rs{Command name}, e.g. rsCommand1 above.

    "Data Source" is the name of an ADO Property within a Connection's Properties collection.

    I've never heard of a "record source" and it sounds like something said in error.

    A "DataSet" is something in ADO.Net and doesn't normally apply in VB6 programs. It's a bit like an ADO disconnected Recordset.


    You should probably spend some time reading the tons of information in the documentation:

    Name:  sshot.png
Views: 182
Size:  10.0 KB

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Apr 2020
    Posts
    29

    Re: How to Make DataGrid1 Content go into the actual Connected Database?

    Thank you very much. I did all the database uphooking using the graphic interface i.e., ADODC properties, connection string test, Record Source Tab. and the database shows on the Datagrid1 just fine, but I want to move data from textboxes into the datagrid & the access database. setting the connection with code confuses me as I am new to the databases world.

    Also what command type should I use in Property Pages? ad CmdTable? or adCmdText?

  7. #7
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to Make DataGrid1 Content go into the actual Connected Database?

    The ADODC is a sort of "baby" data control, a leftover from VB3 DAO concepts. That's probably why it uses the weird old term "RecordSource" for an ADO Command. I'm not sure why anyone uses it in VB6 since it is a relic. Even the VB6 classes glossed over it as obsolete after one example.

    DataEnvironment is like the "big daddy" version of that. They support multiple Connection objects, each with multiple Command objects linked to them.

    If you are going to use any of this you really should read the articles in the documentation. Those explain what CommandType settings are for.

    Use the CommandType property to optimize evaluation of the CommandText property.

    If the CommandType property value equals adCmdUnknown (the default value), you may experience diminished performance because ADO must make calls to the provider to determine if the CommandText property is an SQL statement, a stored procedure, or a table name. If you know what type of command you're using, setting the CommandType property instructs ADO to go directly to the relevant code
    ADODC only supports 4 of the possible values.

    Its Property Page "RecordSource" tab is where you can provide a query. That can be a table name, a stored procedure name, or a query expression (usually a SQL statement for most databases). Choose the CommandType value that matches what you are doing.

    Example:

    Name:  sshot.png
Views: 138
Size:  3.9 KB

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Apr 2020
    Posts
    29

    Re: How to Make DataGrid1 Content go into the actual Connected Database?

    You are right; I read another source where the same thing is mentioned. I set it to2.adCmdTable

    Thank you very much.
    Last edited by SaudVb; Aug 7th, 2020 at 09:03 PM.

Posting Permissions

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



Click Here to Expand Forum to Full Width