Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Why cant the DataSource be changed programaticly?

VB NewbieVB Newbie Member Posts: 206
Hi,

A simple question,
I like to programmaticly make an access db at a users given location.
Then the application needs to load a datagrid with the blank db, so that a user can fill the datagrid with data and saves it back to the original db.

But theire seems no way for doing this programmaticly.
For instance: the OleDbConnection DataSource property cant be changed programaticly, so i need to know the path to the access db, which ofcourse i don't know if a user is able to set the location himself.

Comments

  • GideonOmegaGideonOmega Member Posts: 617
    : Hi,
    :
    : A simple question,
    : I like to programmaticly make an access db at a users given location.
    : Then the application needs to load a datagrid with the blank db, so that a user can fill the datagrid with data and saves it back to the original db.
    :
    : But theire seems no way for doing this programmaticly.
    : For instance: the OleDbConnection DataSource property cant be changed programaticly, so i need to know the path to the access db, which ofcourse i don't know if a user is able to set the location himself.
    :


    [code]
    ......
    ...
    Set dg.DataSource = objRSINFO
    dg.Refresh
    [/code]

    dg is the datagrid, and objRSINFO is a ADO Recordset object.

    you set up your recordset object and run your sql query with the .open command then you use the above code. you do not close the recordset however as this will cause the data to be removed from the data grid. here is a full example:
    [code]
    Set objRSINFO = New ADODB.Recordset
    strSQL = txtQRY.Text
    With objRSINFO
    .ActiveConnection = objConn
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .Source = strSQL
    .Open
    Set dg.DataSource = objRSINFO
    dg.Refresh
    End With
    [/code]
    objConn being your preconnected connection object, and txtqry being a textbox with the sql you want to run - you could also hardcode it in the .Source statment in the form of a string as in:
    .Source = "select * from SomeTable"


    Hope this helps.
    [blue]
    C:Dos
    C:Dos Run
    Run Dos Run
    [/blue]

  • VB NewbieVB Newbie Member Posts: 206
    [b][red]This message was edited by VB Newbie at 2004-7-5 12:37:6[/red][/b][hr]
    [b][red]This message was edited by VB Newbie at 2004-7-5 12:36:9[/red][/b][hr]
    : : Hi,
    : :
    : : A simple question,
    : : I like to programmaticly make an access db at a users given location.
    : : Then the application needs to load a datagrid with the blank db, so that a user can fill the datagrid with data and saves it back to the original db.
    : :
    : : But theire seems no way for doing this programmaticly.
    : : For instance: the OleDbConnection DataSource property cant be changed programaticly, so i need to know the path to the access db, which ofcourse i don't know if a user is able to set the location himself.
    : :
    :
    :
    : [code]
    : ......
    : ...
    : Set dg.DataSource = objRSINFO
    : dg.Refresh
    : [/code]
    :
    : dg is the datagrid, and objRSINFO is a ADO Recordset object.
    :
    : you set up your recordset object and run your sql query with the .open command then you use the above code. you do not close the recordset however as this will cause the data to be removed from the data grid. here is a full example:
    : [code]
    : Set objRSINFO = New ADODB.Recordset
    : strSQL = txtQRY.Text
    : With objRSINFO
    : .ActiveConnection = objConn
    : .CursorLocation = adUseClient
    : .CursorType = adOpenForwardOnly
    : .Source = strSQL
    : .Open
    : Set dg.DataSource = objRSINFO
    : dg.Refresh
    : End With
    : [/code]
    : objConn being your preconnected connection object, and txtqry being a textbox with the sql you want to run - you could also hardcode it in the .Source statment in the form of a string as in:
    : .Source = "select * from SomeTable"
    :
    :
    : Hope this helps.
    : [blue]
    : C:Dos
    : C:Dos Run
    : Run Dos Run
    : [/blue]
    :
    Sorry, it does'nt
    I used the following code (based on your advise)
    [code]
    Private objConn As New ADODB.Connection()

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    'TextBox1.Text is the path to the access db
    Dim strSQL = TextBox1.Text


    Dim objRSINFO As New ADODB.Recordset()
    With objRSINFO
    .ActiveConnection = objConn
    .CursorLocation = ADODB.CursorLocationEnum.adUseClient
    .CursorType = ADODB.CursorTypeEnum.adOpenForwardOnly
    .Source = strSQL
    .Open()
    DataGrid1.DataSource = objRSINFO
    DataGrid1.Refresh()
    End With
    [/code]
    debug stops at .ActiveConnection = objConn
    Error states something like: cannot use the connection. Connection is closed or not legal in this context.

    So i thought that maby i must open the connection with the code:
    objConn.Open()
    but then debugs stops at this code and say something like: verification failed.

    Anyway, what are you actually mean by objConn being a preconnected connection object? Am i correct that it is an ADODB.Recordset?

    Please help me some more.

    Thanks,

    VB Newbie




  • GideonOmegaGideonOmega Member Posts: 617
    : [b][red]This message was edited by VB Newbie at 2004-7-5 12:37:6[/red][/b][hr]
    : [b][red]This message was edited by VB Newbie at 2004-7-5 12:36:9[/red][/b][hr]
    : : : Hi,
    : : :
    : : : A simple question,
    : : : I like to programmaticly make an access db at a users given location.
    : : : Then the application needs to load a datagrid with the blank db, so that a user can fill the datagrid with data and saves it back to the original db.
    : : :
    : : : But theire seems no way for doing this programmaticly.
    : : : For instance: the OleDbConnection DataSource property cant be changed programaticly, so i need to know the path to the access db, which ofcourse i don't know if a user is able to set the location himself.
    : : :
    : :
    : :
    : : [code]
    : : ......
    : : ...
    : : Set dg.DataSource = objRSINFO
    : : dg.Refresh
    : : [/code]
    : :
    : : dg is the datagrid, and objRSINFO is a ADO Recordset object.
    : :
    : : you set up your recordset object and run your sql query with the .open command then you use the above code. you do not close the recordset however as this will cause the data to be removed from the data grid. here is a full example:
    : : [code]
    : : Set objRSINFO = New ADODB.Recordset
    : : strSQL = txtQRY.Text
    : : With objRSINFO
    : : .ActiveConnection = objConn
    : : .CursorLocation = adUseClient
    : : .CursorType = adOpenForwardOnly
    : : .Source = strSQL
    : : .Open
    : : Set dg.DataSource = objRSINFO
    : : dg.Refresh
    : : End With
    : : [/code]
    : : objConn being your preconnected connection object, and txtqry being a textbox with the sql you want to run - you could also hardcode it in the .Source statment in the form of a string as in:
    : : .Source = "select * from SomeTable"
    : :
    : :
    : : Hope this helps.
    : : [blue]
    : : C:Dos
    : : C:Dos Run
    : : Run Dos Run
    : : [/blue]
    : :
    : Sorry, it does'nt
    : I used the following code (based on your advise)
    : [code]
    : Private objConn As New ADODB.Connection()
    :
    : Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    : 'TextBox1.Text is the path to the access db
    : Dim strSQL = TextBox1.Text
    :
    :
    : Dim objRSINFO As New ADODB.Recordset()
    : With objRSINFO
    : .ActiveConnection = objConn
    : .CursorLocation = ADODB.CursorLocationEnum.adUseClient
    : .CursorType = ADODB.CursorTypeEnum.adOpenForwardOnly
    : .Source = strSQL
    : .Open()
    : DataGrid1.DataSource = objRSINFO
    : DataGrid1.Refresh()
    : End With
    : [/code]
    : debug stops at .ActiveConnection = objConn
    : Error states something like: cannot use the connection. Connection is closed or not legal in this context.
    :
    : So i thought that maby i must open the connection with the code:
    : objConn.Open()
    : but then debugs stops at this code and say something like: verification failed.
    :
    : Anyway, what are you actually mean by objConn being a preconnected connection object? Am i correct that it is an ADODB.Recordset?
    :
    : Please help me some more.
    :
    : Thanks,
    :
    : VB Newbie
    :
    :
    :
    :
    :


    what I mean by and also why yours is not working is the objConn being preconnected as in already having the connection string and .open commands run on it so there is an active connection to the data base.


    and the code does work, I know this because I used it in the Database analyzer that I coded - you for got the Set statment as in
    [b]Set[/b] DataGrid1.DataSource = objRSINFO

    instead of just:
    DataGrid1.DataSource = objRSINFO

    let me know if you have any other problems.


    [blue]
    C:Dos
    C:Dos Run
    Run Dos Run
    [/blue]

  • VB NewbieVB Newbie Member Posts: 206
    : : [b][red]This message was edited by VB Newbie at 2004-7-5 12:37:6[/red][/b][hr]
    : : [b][red]This message was edited by VB Newbie at 2004-7-5 12:36:9[/red][/b][hr]
    : : : : Hi,
    : : : :
    : : : : A simple question,
    : : : : I like to programmaticly make an access db at a users given location.
    : : : : Then the application needs to load a datagrid with the blank db, so that a user can fill the datagrid with data and saves it back to the original db.
    : : : :
    : : : : But theire seems no way for doing this programmaticly.
    : : : : For instance: the OleDbConnection DataSource property cant be changed programaticly, so i need to know the path to the access db, which ofcourse i don't know if a user is able to set the location himself.
    : : : :
    : : :
    : : :
    : : : [code]
    : : : ......
    : : : ...
    : : : Set dg.DataSource = objRSINFO
    : : : dg.Refresh
    : : : [/code]
    : : :
    : : : dg is the datagrid, and objRSINFO is a ADO Recordset object.
    : : :
    : : : you set up your recordset object and run your sql query with the .open command then you use the above code. you do not close the recordset however as this will cause the data to be removed from the data grid. here is a full example:
    : : : [code]
    : : : Set objRSINFO = New ADODB.Recordset
    : : : strSQL = txtQRY.Text
    : : : With objRSINFO
    : : : .ActiveConnection = objConn
    : : : .CursorLocation = adUseClient
    : : : .CursorType = adOpenForwardOnly
    : : : .Source = strSQL
    : : : .Open
    : : : Set dg.DataSource = objRSINFO
    : : : dg.Refresh
    : : : End With
    : : : [/code]
    : : : objConn being your preconnected connection object, and txtqry being a textbox with the sql you want to run - you could also hardcode it in the .Source statment in the form of a string as in:
    : : : .Source = "select * from SomeTable"
    : : :
    : : :
    : : : Hope this helps.
    : : : [blue]
    : : : C:Dos
    : : : C:Dos Run
    : : : Run Dos Run
    : : : [/blue]
    : : :
    : : Sorry, it does'nt
    : : I used the following code (based on your advise)
    : : [code]
    : : Private objConn As New ADODB.Connection()
    : :
    : : Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    : : 'TextBox1.Text is the path to the access db
    : : Dim strSQL = TextBox1.Text
    : :
    : :
    : : Dim objRSINFO As New ADODB.Recordset()
    : : With objRSINFO
    : : .ActiveConnection = objConn
    : : .CursorLocation = ADODB.CursorLocationEnum.adUseClient
    : : .CursorType = ADODB.CursorTypeEnum.adOpenForwardOnly
    : : .Source = strSQL
    : : .Open()
    : : DataGrid1.DataSource = objRSINFO
    : : DataGrid1.Refresh()
    : : End With
    : : [/code]
    : : debug stops at .ActiveConnection = objConn
    : : Error states something like: cannot use the connection. Connection is closed or not legal in this context.
    : :
    : : So i thought that maby i must open the connection with the code:
    : : objConn.Open()
    : : but then debugs stops at this code and say something like: verification failed.
    : :
    : : Anyway, what are you actually mean by objConn being a preconnected connection object? Am i correct that it is an ADODB.Recordset?
    : :
    : : Please help me some more.
    : :
    : : Thanks,
    : :
    : : VB Newbie
    : :
    : :
    : :
    : :
    : :
    :
    :
    : what I mean by and also why yours is not working is the objConn being preconnected as in already having the connection string and .open commands run on it so there is an active connection to the data base.
    :
    :
    : and the code does work, I know this because I used it in the Database analyzer that I coded - you for got the Set statment as in
    : [b]Set[/b] DataGrid1.DataSource = objRSINFO
    :
    : instead of just:
    : DataGrid1.DataSource = objRSINFO
    :
    : let me know if you have any other problems.
    :
    :
    : [blue]
    : C:Dos
    : C:Dos Run
    : Run Dos Run
    : [/blue]
    :

    Ok first of all, im using vb.NET :-)
    I think some code is just sleighly different in vb.NET then in vb6
    For instance, I cant write a code like:
    Set DataGrid1.DataSource = objRSINFO
    vb.net change it in simply: DataGrid1.DataSource = objRSINFO
    "Let and Set assignment statements are no longer supported"

    I use the following code:
    [code]
    Private fda As New ADODB.Connection()

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    fda.Open("C:Documents and SettingsMichaelBureaubladdb3.mdb")

    Dim objRSINFO As New ADODB.Recordset()
    With objRSINFO
    fda = objRSINFO.ActiveConnection
    .CursorLocation = ADODB.CursorLocationEnum.adUseClient
    .CursorType = ADODB.CursorTypeEnum.adOpenForwardOnly
    .Source = "Select Product description, Number, Price, ProductID From Products"
    .Open()
    DataGrid1.DataSource = objRSINFO
    DataGrid1.Refresh()
    [/code]
    Debug stops at .Source

    I dont know if .Source means the "Select From table" string or a path to db source string like: "C:....."

    Hope you can help.
    Maybe, if it wont take up to much of your time, you can sent me an example project in vb6. I must have vb6 somewhere, so i will be able to examine your project and learn how to use and convert it to vb.net.

    Thanks,

    VB Newbie
  • GideonOmegaGideonOmega Member Posts: 617
    : : : [b][red]This message was edited by VB Newbie at 2004-7-5 12:37:6[/red][/b][hr]
    : : : [b][red]This message was edited by VB Newbie at 2004-7-5 12:36:9[/red][/b][hr]
    : : : : : Hi,
    : : : : :
    : : : : : A simple question,
    : : : : : I like to programmaticly make an access db at a users given location.
    : : : : : Then the application needs to load a datagrid with the blank db, so that a user can fill the datagrid with data and saves it back to the original db.
    : : : : :
    : : : : : But theire seems no way for doing this programmaticly.
    : : : : : For instance: the OleDbConnection DataSource property cant be changed programaticly, so i need to know the path to the access db, which ofcourse i don't know if a user is able to set the location himself.
    : : : : :
    : : : :
    : : : :
    : : : : [code]
    : : : : ......
    : : : : ...
    : : : : Set dg.DataSource = objRSINFO
    : : : : dg.Refresh
    : : : : [/code]
    : : : :
    : : : : dg is the datagrid, and objRSINFO is a ADO Recordset object.
    : : : :
    : : : : you set up your recordset object and run your sql query with the .open command then you use the above code. you do not close the recordset however as this will cause the data to be removed from the data grid. here is a full example:
    : : : : [code]
    : : : : Set objRSINFO = New ADODB.Recordset
    : : : : strSQL = txtQRY.Text
    : : : : With objRSINFO
    : : : : .ActiveConnection = objConn
    : : : : .CursorLocation = adUseClient
    : : : : .CursorType = adOpenForwardOnly
    : : : : .Source = strSQL
    : : : : .Open
    : : : : Set dg.DataSource = objRSINFO
    : : : : dg.Refresh
    : : : : End With
    : : : : [/code]
    : : : : objConn being your preconnected connection object, and txtqry being a textbox with the sql you want to run - you could also hardcode it in the .Source statment in the form of a string as in:
    : : : : .Source = "select * from SomeTable"
    : : : :
    : : : :
    : : : : Hope this helps.
    : : : : [blue]
    : : : : C:Dos
    : : : : C:Dos Run
    : : : : Run Dos Run
    : : : : [/blue]
    : : : :
    : : : Sorry, it does'nt
    : : : I used the following code (based on your advise)
    : : : [code]
    : : : Private objConn As New ADODB.Connection()
    : : :
    : : : Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    : : : 'TextBox1.Text is the path to the access db
    : : : Dim strSQL = TextBox1.Text
    : : :
    : : :
    : : : Dim objRSINFO As New ADODB.Recordset()
    : : : With objRSINFO
    : : : .ActiveConnection = objConn
    : : : .CursorLocation = ADODB.CursorLocationEnum.adUseClient
    : : : .CursorType = ADODB.CursorTypeEnum.adOpenForwardOnly
    : : : .Source = strSQL
    : : : .Open()
    : : : DataGrid1.DataSource = objRSINFO
    : : : DataGrid1.Refresh()
    : : : End With
    : : : [/code]
    : : : debug stops at .ActiveConnection = objConn
    : : : Error states something like: cannot use the connection. Connection is closed or not legal in this context.
    : : :
    : : : So i thought that maby i must open the connection with the code:
    : : : objConn.Open()
    : : : but then debugs stops at this code and say something like: verification failed.
    : : :
    : : : Anyway, what are you actually mean by objConn being a preconnected connection object? Am i correct that it is an ADODB.Recordset?
    : : :
    : : : Please help me some more.
    : : :
    : : : Thanks,
    : : :
    : : : VB Newbie
    : : :
    : : :
    : : :
    : : :
    : : :
    : :
    : :
    : : what I mean by and also why yours is not working is the objConn being preconnected as in already having the connection string and .open commands run on it so there is an active connection to the data base.
    : :
    : :
    : : and the code does work, I know this because I used it in the Database analyzer that I coded - you for got the Set statment as in
    : : [b]Set[/b] DataGrid1.DataSource = objRSINFO
    : :
    : : instead of just:
    : : DataGrid1.DataSource = objRSINFO
    : :
    : : let me know if you have any other problems.
    : :
    : :
    : : [blue]
    : : C:Dos
    : : C:Dos Run
    : : Run Dos Run
    : : [/blue]
    : :
    :
    : Ok first of all, im using vb.NET :-)
    : I think some code is just sleighly different in vb.NET then in vb6
    : For instance, I cant write a code like:
    : Set DataGrid1.DataSource = objRSINFO
    : vb.net change it in simply: DataGrid1.DataSource = objRSINFO
    : "Let and Set assignment statements are no longer supported"
    :
    : I use the following code:
    : [code]
    : Private fda As New ADODB.Connection()
    :
    : Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    :
    : fda.Open("C:Documents and SettingsMichaelBureaubladdb3.mdb")
    :
    : Dim objRSINFO As New ADODB.Recordset()
    : With objRSINFO
    : fda = objRSINFO.ActiveConnection
    : .CursorLocation = ADODB.CursorLocationEnum.adUseClient
    : .CursorType = ADODB.CursorTypeEnum.adOpenForwardOnly
    : .Source = "Select Product description, Number, Price, ProductID From Products"
    : .Open()
    : DataGrid1.DataSource = objRSINFO
    : DataGrid1.Refresh()
    : [/code]
    : Debug stops at .Source
    :
    : I dont know if .Source means the "Select From table" string or a path to db source string like: "C:....."
    :
    : Hope you can help.
    : Maybe, if it wont take up to much of your time, you can sent me an example project in vb6. I must have vb6 somewhere, so i will be able to examine your project and learn how to use and convert it to vb.net.
    :
    : Thanks,
    :
    : VB Newbie
    :


    Well that explains a few things :D ... Yeah I have no Idea how to accomplish this in .Net - but I can send you the db analyzer I was working on - It's not complete but it has working examples of what I'm talking about - I'll try and dig it out some time this week. on a side note a buddy of mine told me that .net can convert vb6 code to .net code with a build in tool or something - although I hear it doesn't work that great, might still be a good place to start.

    anyways I'll send you the code sometime soon so you can have a look at it.

    on a side note you may want to do a google search for setting the datasource of a datagrid with a Recordset in .net
    [blue]
    C:Dos
    C:Dos Run
    Run Dos Run
    [/blue]

  • VB NewbieVB Newbie Member Posts: 206
    Right now im very near to the solution.
    I use the following code to make an .mdb access file programmatically.
    Then create a dataset out of the .mdb and display it in a DataGrid.
    Then a user can change the data in the DataGrid and saves it back to the original .mdb.

    The code seems almost perfect, except that when a user change the original data in the DataGrid, an error appears that stated that the ID field can't be null becouse the property Required is set to True.
    I don't get it, i did fill in the ID field!

    What's wrong with this code?
    [code]
    Imports ADOX
    Public Class Form1
    Inherits System.Windows.Forms.Form

    #Region " Windows Form Designer generated code "

    Public Sub New()
    MyBase.New()

    'This call is required by the Windows Form Designer.
    InitializeComponent()

    'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
    If disposing Then
    If Not (components Is Nothing) Then
    components.Dispose()
    End If
    End If
    MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.
    'Do not modify it using the code editor.
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents Button2 As System.Windows.Forms.Button
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    Private Sub InitializeComponent()
    Me.Button1 = New System.Windows.Forms.Button()
    Me.Button2 = New System.Windows.Forms.Button()
    Me.DataGrid1 = New System.Windows.Forms.DataGrid()
    CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
    Me.SuspendLayout()
    '
    'Button1
    '
    Me.Button1.Location = New System.Drawing.Point(56, 248)
    Me.Button1.Name = "Button1"
    Me.Button1.Size = New System.Drawing.Size(104, 32)
    Me.Button1.TabIndex = 0
    Me.Button1.Text = "Maak DB"
    '
    'Button2
    '
    Me.Button2.Location = New System.Drawing.Point(296, 248)
    Me.Button2.Name = "Button2"
    Me.Button2.Size = New System.Drawing.Size(104, 32)
    Me.Button2.TabIndex = 1
    Me.Button2.Text = "Update"
    '
    'DataGrid1
    '
    Me.DataGrid1.Anchor = (((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
    Or System.Windows.Forms.AnchorStyles.Left) _
    Or System.Windows.Forms.AnchorStyles.Right)
    Me.DataGrid1.DataMember = ""
    Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
    Me.DataGrid1.Location = New System.Drawing.Point(32, 24)
    Me.DataGrid1.Name = "DataGrid1"
    Me.DataGrid1.Size = New System.Drawing.Size(384, 208)
    Me.DataGrid1.TabIndex = 2
    '
    'Form1
    '
    Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
    Me.ClientSize = New System.Drawing.Size(448, 326)
    Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.DataGrid1, Me.Button2, Me.Button1})
    Me.Name = "Form1"
    Me.Text = "Form1"
    CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
    Me.ResumeLayout(False)

    End Sub

    #End Region
    Private oConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:Documents and SettingsMichaelBureaubladdb5.mdb;" & _
    "Jet OLEDB:Engine Type=5")
    Private oDataSet As New DataSet()
    Private oDataAdapter As New OleDb.OleDbDataAdapter()

    Private oSelectCommand As New OleDb.OleDbCommand("Select ProduktOmschrijving, Artikelnummer, PrijsPerEenheid, Leverancier, ID From TestTable", oConnection)
    Private oInsertCommand As New OleDb.OleDbCommand("Insert into TestTable(ProduktOmschrijving, Artikelnummer, PrijsPerEenheid, Leverancier) Values (@ProduktOmschrijving, @Artikelnummer, @PrijsPerEenheid, @Leverancier)", oConnection)
    Private oUpdateCommand As New OleDb.OleDbCommand("Update TestTable Set ProduktOmschrijving = @ProduktOmschrijving, Artikelnummer = @Artikelnummer, PrijsPerEenheid = @PrijsPerEenheid, Leverancier = @Leverancier Where ID = @ID", oConnection)
    Private oDeleteCommand As New OleDb.OleDbCommand("Delete From TestTable Where ID = @ID", oConnection)

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'Create Insert statement
    oInsertCommand.Parameters.Add(New OleDb.OleDbParameter("@ProduktOmschrijving", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "ProduktOmschrijving", System.Data.DataRowVersion.Current, Nothing))
    oInsertCommand.Parameters.Add(New OleDb.OleDbParameter("@Artikelnummer", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "Artikelnummer", System.Data.DataRowVersion.Current, Nothing))
    oInsertCommand.Parameters.Add(New OleDb.OleDbParameter("@PrijsPerEenheid", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "PrijsPerEenheid", System.Data.DataRowVersion.Current, Nothing))
    oInsertCommand.Parameters.Add(New OleDb.OleDbParameter("@Leverancier", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "Leverancier", System.Data.DataRowVersion.Current, Nothing))

    'Create Update statement
    oUpdateCommand.Parameters.Add(New OleDb.OleDbParameter("@ProduktOmschrijving", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "ProduktOmschrijving", System.Data.DataRowVersion.Current, Nothing))
    oUpdateCommand.Parameters.Add(New OleDb.OleDbParameter("@Artikelnummer", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "Artikelnummer", System.Data.DataRowVersion.Current, Nothing))
    oUpdateCommand.Parameters.Add(New OleDb.OleDbParameter("@PrijsPerEenheid", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "PrijsPerEenheid", System.Data.DataRowVersion.Current, Nothing))
    oUpdateCommand.Parameters.Add(New OleDb.OleDbParameter("@Leverancier", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "Leverancier", System.Data.DataRowVersion.Current, Nothing))
    oUpdateCommand.Parameters.Add(New OleDb.OleDbParameter("@ID", System.Data.SqlDbType.Int, 5, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "ID", System.Data.DataRowVersion.Current, Nothing))

    'Creat Delete command
    oDeleteCommand.Parameters.Add(New OleDb.OleDbParameter("@ID", System.Data.SqlDbType.Int, 5, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "ID", System.Data.DataRowVersion.Current, Nothing))

    'Set the appropriate object references on the DataAdapter
    oDataAdapter.SelectCommand = oSelectCommand
    oDataAdapter.InsertCommand = oInsertCommand
    oDataAdapter.UpdateCommand = oUpdateCommand
    oDataAdapter.DeleteCommand = oDeleteCommand


    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim cat As Catalog = New Catalog()
    Dim tbl As New ADOX.Table()
    Try
    'maak DataBase
    cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:Documents and SettingsMichaelBureaubladdb5.mdb;" & _
    "Jet OLEDB:Engine Type=5")

    'maak table
    tbl.Name = "TestTable"
    tbl.Columns.Append("ProduktOmschrijving")
    tbl.Columns.Append("Artikelnummer")
    tbl.Columns.Append("PrijsPerEenheid")
    tbl.Columns.Append("Leverancier")
    tbl.Columns.Append("ID", DataTypeEnum.adInteger, 5)
    tbl.Keys.Append("ID", KeyTypeEnum.adKeyPrimary, "ID", "TestTable", "ID")
    cat.Tables.Append(tbl)
    cat = Nothing
    Catch
    End Try

    'open connection
    oConnection.Open()

    'wis eventuele data in DataSet
    oDataSet.Clear()

    'fill Dataset with data from .mdb
    Try
    oDataAdapter.Fill(oDataSet, "TestTable")
    Catch ex As Exception
    MsgBox(ex.ToString)
    End Try


    'Bind DataGrid to DataSet
    DataGrid1.SetDataBinding(oDataSet, "TestTable")

    'Sluit connection
    oConnection.Close()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    'Open Connection
    oConnection.Open()

    'update dataset
    Try
    oDataAdapter.Update(oDataSet, "TestTable")
    Catch ex As Exception
    MsgBox(ex.ToString)
    End Try

    'Sluit connection
    oConnection.Close()

    End Sub
    End Class
    [/code]
    It's vb.net, but code is practicly the same in vb6

    Thanks,

    VB Newbie

  • GideonOmegaGideonOmega Member Posts: 617
    : Right now im very near to the solution.
    : I use the following code to make an .mdb access file programmatically.
    : Then create a dataset out of the .mdb and display it in a DataGrid.
    : Then a user can change the data in the DataGrid and saves it back to the original .mdb.
    :
    : The code seems almost perfect, except that when a user change the original data in the DataGrid, an error appears that stated that the ID field can't be null becouse the property Required is set to True.
    : I don't get it, i did fill in the ID field!
    :
    : What's wrong with this code?
    : [code]
    : Imports ADOX
    : Public Class Form1
    : Inherits System.Windows.Forms.Form
    :
    : #Region " Windows Form Designer generated code "
    :
    : Public Sub New()
    : MyBase.New()
    :
    : 'This call is required by the Windows Form Designer.
    : InitializeComponent()
    :
    : 'Add any initialization after the InitializeComponent() call
    :
    : End Sub
    :
    : 'Form overrides dispose to clean up the component list.
    : Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
    : If disposing Then
    : If Not (components Is Nothing) Then
    : components.Dispose()
    : End If
    : End If
    : MyBase.Dispose(disposing)
    : End Sub
    :
    : 'Required by the Windows Form Designer
    : Private components As System.ComponentModel.IContainer
    :
    : 'NOTE: The following procedure is required by the Windows Form Designer
    : 'It can be modified using the Windows Form Designer.
    : 'Do not modify it using the code editor.
    : Friend WithEvents Button1 As System.Windows.Forms.Button
    : Friend WithEvents Button2 As System.Windows.Forms.Button
    : Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    : Private Sub InitializeComponent()
    : Me.Button1 = New System.Windows.Forms.Button()
    : Me.Button2 = New System.Windows.Forms.Button()
    : Me.DataGrid1 = New System.Windows.Forms.DataGrid()
    : CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
    : Me.SuspendLayout()
    : '
    : 'Button1
    : '
    : Me.Button1.Location = New System.Drawing.Point(56, 248)
    : Me.Button1.Name = "Button1"
    : Me.Button1.Size = New System.Drawing.Size(104, 32)
    : Me.Button1.TabIndex = 0
    : Me.Button1.Text = "Maak DB"
    : '
    : 'Button2
    : '
    : Me.Button2.Location = New System.Drawing.Point(296, 248)
    : Me.Button2.Name = "Button2"
    : Me.Button2.Size = New System.Drawing.Size(104, 32)
    : Me.Button2.TabIndex = 1
    : Me.Button2.Text = "Update"
    : '
    : 'DataGrid1
    : '
    : Me.DataGrid1.Anchor = (((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
    : Or System.Windows.Forms.AnchorStyles.Left) _
    : Or System.Windows.Forms.AnchorStyles.Right)
    : Me.DataGrid1.DataMember = ""
    : Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
    : Me.DataGrid1.Location = New System.Drawing.Point(32, 24)
    : Me.DataGrid1.Name = "DataGrid1"
    : Me.DataGrid1.Size = New System.Drawing.Size(384, 208)
    : Me.DataGrid1.TabIndex = 2
    : '
    : 'Form1
    : '
    : Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
    : Me.ClientSize = New System.Drawing.Size(448, 326)
    : Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.DataGrid1, Me.Button2, Me.Button1})
    : Me.Name = "Form1"
    : Me.Text = "Form1"
    : CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
    : Me.ResumeLayout(False)
    :
    : End Sub
    :
    : #End Region
    : Private oConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    : "Data Source=C:Documents and SettingsMichaelBureaubladdb5.mdb;" & _
    : "Jet OLEDB:Engine Type=5")
    : Private oDataSet As New DataSet()
    : Private oDataAdapter As New OleDb.OleDbDataAdapter()
    :
    : Private oSelectCommand As New OleDb.OleDbCommand("Select ProduktOmschrijving, Artikelnummer, PrijsPerEenheid, Leverancier, ID From TestTable", oConnection)
    : Private oInsertCommand As New OleDb.OleDbCommand("Insert into TestTable(ProduktOmschrijving, Artikelnummer, PrijsPerEenheid, Leverancier) Values (@ProduktOmschrijving, @Artikelnummer, @PrijsPerEenheid, @Leverancier)", oConnection)
    : Private oUpdateCommand As New OleDb.OleDbCommand("Update TestTable Set ProduktOmschrijving = @ProduktOmschrijving, Artikelnummer = @Artikelnummer, PrijsPerEenheid = @PrijsPerEenheid, Leverancier = @Leverancier Where ID = @ID", oConnection)
    : Private oDeleteCommand As New OleDb.OleDbCommand("Delete From TestTable Where ID = @ID", oConnection)
    :
    : Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    : 'Create Insert statement
    : oInsertCommand.Parameters.Add(New OleDb.OleDbParameter("@ProduktOmschrijving", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "ProduktOmschrijving", System.Data.DataRowVersion.Current, Nothing))
    : oInsertCommand.Parameters.Add(New OleDb.OleDbParameter("@Artikelnummer", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "Artikelnummer", System.Data.DataRowVersion.Current, Nothing))
    : oInsertCommand.Parameters.Add(New OleDb.OleDbParameter("@PrijsPerEenheid", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "PrijsPerEenheid", System.Data.DataRowVersion.Current, Nothing))
    : oInsertCommand.Parameters.Add(New OleDb.OleDbParameter("@Leverancier", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "Leverancier", System.Data.DataRowVersion.Current, Nothing))
    :
    : 'Create Update statement
    : oUpdateCommand.Parameters.Add(New OleDb.OleDbParameter("@ProduktOmschrijving", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "ProduktOmschrijving", System.Data.DataRowVersion.Current, Nothing))
    : oUpdateCommand.Parameters.Add(New OleDb.OleDbParameter("@Artikelnummer", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "Artikelnummer", System.Data.DataRowVersion.Current, Nothing))
    : oUpdateCommand.Parameters.Add(New OleDb.OleDbParameter("@PrijsPerEenheid", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "PrijsPerEenheid", System.Data.DataRowVersion.Current, Nothing))
    : oUpdateCommand.Parameters.Add(New OleDb.OleDbParameter("@Leverancier", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "Leverancier", System.Data.DataRowVersion.Current, Nothing))
    : oUpdateCommand.Parameters.Add(New OleDb.OleDbParameter("@ID", System.Data.SqlDbType.Int, 5, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "ID", System.Data.DataRowVersion.Current, Nothing))
    :
    : 'Creat Delete command
    : oDeleteCommand.Parameters.Add(New OleDb.OleDbParameter("@ID", System.Data.SqlDbType.Int, 5, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "ID", System.Data.DataRowVersion.Current, Nothing))
    :
    : 'Set the appropriate object references on the DataAdapter
    : oDataAdapter.SelectCommand = oSelectCommand
    : oDataAdapter.InsertCommand = oInsertCommand
    : oDataAdapter.UpdateCommand = oUpdateCommand
    : oDataAdapter.DeleteCommand = oDeleteCommand
    :
    :
    : End Sub
    :
    : Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    :
    : Dim cat As Catalog = New Catalog()
    : Dim tbl As New ADOX.Table()
    : Try
    : 'maak DataBase
    : cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    : "Data Source=C:Documents and SettingsMichaelBureaubladdb5.mdb;" & _
    : "Jet OLEDB:Engine Type=5")
    :
    : 'maak table
    : tbl.Name = "TestTable"
    : tbl.Columns.Append("ProduktOmschrijving")
    : tbl.Columns.Append("Artikelnummer")
    : tbl.Columns.Append("PrijsPerEenheid")
    : tbl.Columns.Append("Leverancier")
    : tbl.Columns.Append("ID", DataTypeEnum.adInteger, 5)
    : tbl.Keys.Append("ID", KeyTypeEnum.adKeyPrimary, "ID", "TestTable", "ID")
    : cat.Tables.Append(tbl)
    : cat = Nothing
    : Catch
    : End Try
    :
    : 'open connection
    : oConnection.Open()
    :
    : 'wis eventuele data in DataSet
    : oDataSet.Clear()
    :
    : 'fill Dataset with data from .mdb
    : Try
    : oDataAdapter.Fill(oDataSet, "TestTable")
    : Catch ex As Exception
    : MsgBox(ex.ToString)
    : End Try
    :
    :
    : 'Bind DataGrid to DataSet
    : DataGrid1.SetDataBinding(oDataSet, "TestTable")
    :
    : 'Sluit connection
    : oConnection.Close()
    : End Sub
    :
    : Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    : 'Open Connection
    : oConnection.Open()
    :
    : 'update dataset
    : Try
    : oDataAdapter.Update(oDataSet, "TestTable")
    : Catch ex As Exception
    : MsgBox(ex.ToString)
    : End Try
    :
    : 'Sluit connection
    : oConnection.Close()
    :
    : End Sub
    : End Class
    : [/code]
    : It's vb.net, but code is practicly the same in vb6
    :
    : Thanks,
    :
    : VB Newbie
    :
    :


    Without looking at the code - it sounds like a access rule violation to me - is the field in question by any chance a AutoNumber field? if so try adding a record and leaving the autonumber field blank - as access will automaticly increment the number to generate the ID and attempts by you the set that number can cause problems. -- Let me know if that is the problem.


    [blue]
    C:Dos
    C:Dos Run
    Run Dos Run
    [/blue]

  • VB NewbieVB Newbie Member Posts: 206
    : : Right now im very near to the solution.
    : : I use the following code to make an .mdb access file programmatically.
    : : Then create a dataset out of the .mdb and display it in a DataGrid.
    : : Then a user can change the data in the DataGrid and saves it back to the original .mdb.
    : :
    : : The code seems almost perfect, except that when a user change the original data in the DataGrid, an error appears that stated that the ID field can't be null becouse the property Required is set to True.
    : : I don't get it, i did fill in the ID field!
    : :
    : : What's wrong with this code?
    : : [code]
    : : Imports ADOX
    : : Public Class Form1
    : : Inherits System.Windows.Forms.Form
    : :
    : : #Region " Windows Form Designer generated code "
    : :
    : : Public Sub New()
    : : MyBase.New()
    : :
    : : 'This call is required by the Windows Form Designer.
    : : InitializeComponent()
    : :
    : : 'Add any initialization after the InitializeComponent() call
    : :
    : : End Sub
    : :
    : : 'Form overrides dispose to clean up the component list.
    : : Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
    : : If disposing Then
    : : If Not (components Is Nothing) Then
    : : components.Dispose()
    : : End If
    : : End If
    : : MyBase.Dispose(disposing)
    : : End Sub
    : :
    : : 'Required by the Windows Form Designer
    : : Private components As System.ComponentModel.IContainer
    : :
    : : 'NOTE: The following procedure is required by the Windows Form Designer
    : : 'It can be modified using the Windows Form Designer.
    : : 'Do not modify it using the code editor.
    : : Friend WithEvents Button1 As System.Windows.Forms.Button
    : : Friend WithEvents Button2 As System.Windows.Forms.Button
    : : Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    : : Private Sub InitializeComponent()
    : : Me.Button1 = New System.Windows.Forms.Button()
    : : Me.Button2 = New System.Windows.Forms.Button()
    : : Me.DataGrid1 = New System.Windows.Forms.DataGrid()
    : : CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
    : : Me.SuspendLayout()
    : : '
    : : 'Button1
    : : '
    : : Me.Button1.Location = New System.Drawing.Point(56, 248)
    : : Me.Button1.Name = "Button1"
    : : Me.Button1.Size = New System.Drawing.Size(104, 32)
    : : Me.Button1.TabIndex = 0
    : : Me.Button1.Text = "Maak DB"
    : : '
    : : 'Button2
    : : '
    : : Me.Button2.Location = New System.Drawing.Point(296, 248)
    : : Me.Button2.Name = "Button2"
    : : Me.Button2.Size = New System.Drawing.Size(104, 32)
    : : Me.Button2.TabIndex = 1
    : : Me.Button2.Text = "Update"
    : : '
    : : 'DataGrid1
    : : '
    : : Me.DataGrid1.Anchor = (((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
    : : Or System.Windows.Forms.AnchorStyles.Left) _
    : : Or System.Windows.Forms.AnchorStyles.Right)
    : : Me.DataGrid1.DataMember = ""
    : : Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
    : : Me.DataGrid1.Location = New System.Drawing.Point(32, 24)
    : : Me.DataGrid1.Name = "DataGrid1"
    : : Me.DataGrid1.Size = New System.Drawing.Size(384, 208)
    : : Me.DataGrid1.TabIndex = 2
    : : '
    : : 'Form1
    : : '
    : : Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
    : : Me.ClientSize = New System.Drawing.Size(448, 326)
    : : Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.DataGrid1, Me.Button2, Me.Button1})
    : : Me.Name = "Form1"
    : : Me.Text = "Form1"
    : : CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
    : : Me.ResumeLayout(False)
    : :
    : : End Sub
    : :
    : : #End Region
    : : Private oConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    : : "Data Source=C:Documents and SettingsMichaelBureaubladdb5.mdb;" & _
    : : "Jet OLEDB:Engine Type=5")
    : : Private oDataSet As New DataSet()
    : : Private oDataAdapter As New OleDb.OleDbDataAdapter()
    : :
    : : Private oSelectCommand As New OleDb.OleDbCommand("Select ProduktOmschrijving, Artikelnummer, PrijsPerEenheid, Leverancier, ID From TestTable", oConnection)
    : : Private oInsertCommand As New OleDb.OleDbCommand("Insert into TestTable(ProduktOmschrijving, Artikelnummer, PrijsPerEenheid, Leverancier) Values (@ProduktOmschrijving, @Artikelnummer, @PrijsPerEenheid, @Leverancier)", oConnection)
    : : Private oUpdateCommand As New OleDb.OleDbCommand("Update TestTable Set ProduktOmschrijving = @ProduktOmschrijving, Artikelnummer = @Artikelnummer, PrijsPerEenheid = @PrijsPerEenheid, Leverancier = @Leverancier Where ID = @ID", oConnection)
    : : Private oDeleteCommand As New OleDb.OleDbCommand("Delete From TestTable Where ID = @ID", oConnection)
    : :
    : : Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    : : 'Create Insert statement
    : : oInsertCommand.Parameters.Add(New OleDb.OleDbParameter("@ProduktOmschrijving", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "ProduktOmschrijving", System.Data.DataRowVersion.Current, Nothing))
    : : oInsertCommand.Parameters.Add(New OleDb.OleDbParameter("@Artikelnummer", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "Artikelnummer", System.Data.DataRowVersion.Current, Nothing))
    : : oInsertCommand.Parameters.Add(New OleDb.OleDbParameter("@PrijsPerEenheid", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "PrijsPerEenheid", System.Data.DataRowVersion.Current, Nothing))
    : : oInsertCommand.Parameters.Add(New OleDb.OleDbParameter("@Leverancier", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "Leverancier", System.Data.DataRowVersion.Current, Nothing))
    : :
    : : 'Create Update statement
    : : oUpdateCommand.Parameters.Add(New OleDb.OleDbParameter("@ProduktOmschrijving", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "ProduktOmschrijving", System.Data.DataRowVersion.Current, Nothing))
    : : oUpdateCommand.Parameters.Add(New OleDb.OleDbParameter("@Artikelnummer", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "Artikelnummer", System.Data.DataRowVersion.Current, Nothing))
    : : oUpdateCommand.Parameters.Add(New OleDb.OleDbParameter("@PrijsPerEenheid", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "PrijsPerEenheid", System.Data.DataRowVersion.Current, Nothing))
    : : oUpdateCommand.Parameters.Add(New OleDb.OleDbParameter("@Leverancier", System.Data.SqlDbType.NVarChar, 20, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "Leverancier", System.Data.DataRowVersion.Current, Nothing))
    : : oUpdateCommand.Parameters.Add(New OleDb.OleDbParameter("@ID", System.Data.SqlDbType.Int, 5, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "ID", System.Data.DataRowVersion.Current, Nothing))
    : :
    : : 'Creat Delete command
    : : oDeleteCommand.Parameters.Add(New OleDb.OleDbParameter("@ID", System.Data.SqlDbType.Int, 5, System.Data.ParameterDirection.Input, True, CType(0, Byte), CType(0, Byte), "ID", System.Data.DataRowVersion.Current, Nothing))
    : :
    : : 'Set the appropriate object references on the DataAdapter
    : : oDataAdapter.SelectCommand = oSelectCommand
    : : oDataAdapter.InsertCommand = oInsertCommand
    : : oDataAdapter.UpdateCommand = oUpdateCommand
    : : oDataAdapter.DeleteCommand = oDeleteCommand
    : :
    : :
    : : End Sub
    : :
    : : Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    : :
    : : Dim cat As Catalog = New Catalog()
    : : Dim tbl As New ADOX.Table()
    : : Try
    : : 'maak DataBase
    : : cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    : : "Data Source=C:Documents and SettingsMichaelBureaubladdb5.mdb;" & _
    : : "Jet OLEDB:Engine Type=5")
    : :
    : : 'maak table
    : : tbl.Name = "TestTable"
    : : tbl.Columns.Append("ProduktOmschrijving")
    : : tbl.Columns.Append("Artikelnummer")
    : : tbl.Columns.Append("PrijsPerEenheid")
    : : tbl.Columns.Append("Leverancier")
    : : tbl.Columns.Append("ID", DataTypeEnum.adInteger, 5)
    : : tbl.Keys.Append("ID", KeyTypeEnum.adKeyPrimary, "ID", "TestTable", "ID")
    : : cat.Tables.Append(tbl)
    : : cat = Nothing
    : : Catch
    : : End Try
    : :
    : : 'open connection
    : : oConnection.Open()
    : :
    : : 'wis eventuele data in DataSet
    : : oDataSet.Clear()
    : :
    : : 'fill Dataset with data from .mdb
    : : Try
    : : oDataAdapter.Fill(oDataSet, "TestTable")
    : : Catch ex As Exception
    : : MsgBox(ex.ToString)
    : : End Try
    : :
    : :
    : : 'Bind DataGrid to DataSet
    : : DataGrid1.SetDataBinding(oDataSet, "TestTable")
    : :
    : : 'Sluit connection
    : : oConnection.Close()
    : : End Sub
    : :
    : : Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    : : 'Open Connection
    : : oConnection.Open()
    : :
    : : 'update dataset
    : : Try
    : : oDataAdapter.Update(oDataSet, "TestTable")
    : : Catch ex As Exception
    : : MsgBox(ex.ToString)
    : : End Try
    : :
    : : 'Sluit connection
    : : oConnection.Close()
    : :
    : : End Sub
    : : End Class
    : : [/code]
    : : It's vb.net, but code is practicly the same in vb6
    : :
    : : Thanks,
    : :
    : : VB Newbie
    : :
    : :
    :
    :
    : Without looking at the code - it sounds like a access rule violation to me - is the field in question by any chance a AutoNumber field? if so try adding a record and leaving the autonumber field blank - as access will automaticly increment the number to generate the ID and attempts by you the set that number can cause problems. -- Let me know if that is the problem.
    :
    :
    : [blue]
    : C:Dos
    : C:Dos Run
    : Run Dos Run
    : [/blue]
    :

    Hi,

    Yes, i figured out that when i make an access database in access (not programmaticly) and make an autonumber primary key, everything works fine.
    But i dont know how to make an autonumberkey programmatically!
    i used the code:
    [code]
    'maak DataBase
    : : cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    : : "Data Source=C:Documents and SettingsMichaelBureaubladdb5.mdb;" & _
    : : "Jet OLEDB:Engine Type=5")
    : :
    : : 'make table
    : : tbl.Name = "TestTable"
    : : tbl.Columns.Append("ProduktOmschrijving")
    : : tbl.Columns.Append("Artikelnummer")
    : : tbl.Columns.Append("PrijsPerEenheid")
    : : tbl.Columns.Append("Leverancier")
    : : tbl.Columns.Append("ID", DataTypeEnum.adInteger, 5)
    : : tbl.Keys.Append("ID", KeyTypeEnum.adKeyPrimary, "ID", "TestTable", "ID")
    : : cat.Tables.Append(tbl)
    : : cat = Nothing
    [/code]
    But this code generates a primary key, but not autonumber.

    VB Newbie

Sign In or Register to comment.