language for databases

18 Jun 2009 12:13 AM

Scott Baxter

Hello,

In the new vb.net is a thing called dataset, which I guess is a standalone
database you can create and play with.

The following code works fine for that.

But I can't seem to define things so that it would work against a real
database.

Here's code that creates an access database, with no structure or fields on
the disk:

If CreateAccessDatabase("e:\test2.mdb") = True Then
            MsgBox("Database Created")
        Else
            MsgBox("Database Creation Failed")
        End If

Public Function CreateAccessDatabase( _
    ByVal DatabaseFullPath As String) As Boolean
        Dim bAns As Boolean
        Dim cat As New ADOX.Catalog()
        Try

            'Make sure the folder
            'provided in the path exists. If file name w/o path
            'is specified, the database will be created in your
            'application folder.

            Dim sCreateString As String
            sCreateString = _
              "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
               DatabaseFullPath
            cat.Create(sCreateString)

            bAns = True

        Catch Excep As System.Runtime.InteropServices.COMException
            bAns = False
            'do whatever else you need to do here, log,
            'msgbox etc.
        Finally
            cat = Nothing
        End Try
        Return bAns
    End Function

I want to use code like the code below to add the fields to the database.

Is there a way to do this?

Thanks.

Scott

Public Sub createdataset()
        'Dim ds As New DataSet()
        ' Create a table; set its initial capacity.
        Dim dtEmp As New DataTable("Employees")
        dtEmp.MinimumCapacity = 200
        ' Create all columns.
        ' You can create a DataColumn and then add it to the Columns
collection.
        Dim dcFName As New DataColumn("FirstName", GetType(String))
        dtEmp.Columns.Add(dcFName)
        ' Or you can create an implicit DataColumn with the Columns.Add
method.
        dtEmp.Columns.Add("LastName", GetType(String))
        dtEmp.Columns.Add("BirthDate", GetType(Date))
        ' When you have to set additional properties, you can use an
explicit
        ' DataColumn object, or you can use a With block.
        With dtEmp.Columns.Add("HomeAddress", GetType(String))
            .MaxLength = 100
        End With
        ' (When you must set only one property, you can be more concise,
        ' even though the result isn't very readable.)
        dtEmp.Columns.Add("City", GetType(String)).MaxLength = 20
        ' Create a calculated column by setting the Expression
        ' property or passing it as the third argument to the Add method.
        dtEmp.Columns.Add("CompleteName", GetType(String), _
        "FirstName + ' ' + LastName")
        ' Create an identity, auto-incremented column.
        Dim dcEmpId As New DataColumn("EmpId", GetType(Integer))
        dcEmpId.AutoIncrement = True ' Make it auto-increment.
        dcEmpId.AutoIncrementSeed = 1
        dcEmpId.AllowDBNull = False ' Default is True.
        dcEmpId.Unique = True ' All key columns should be unique.
        dtEmp.Columns.Add(dcEmpId) ' Add to Columns collection.
        ' Make it the primary key. (Create the array on-the-fly.)
        dtEmp.PrimaryKey = New DataColumn() {dcEmpId}
        ' This is a foreign key, but we haven't created the other table yet.
        dtEmp.Columns.Add("DeptId", GetType(Integer))
        ' Add the DataTable to the DataSet.
        ds.Tables.Add(dtEmp)
    End Subl

18 Jun 2009 12:57 AM

PvdG42

Show quote Hide quote

"Scott Baxter" <sc***@websearchstore.com> wrote in message
news:uv1jTm67JHA.4100@TK2MSFTNGP06.phx.gbl...
> Hello,
>
> In the new vb.net is a thing called dataset, which I guess is a standalone
> database you can create and play with.
>
> The following code works fine for that.
>
> But I can't seem to define things so that it would work against a real
> database.
>
> Here's code that creates an access database, with no structure or fields
> on
> the disk:

<snip>

You need to learn about datasets and what they actually are, as your
statement above "In the new vb.net is a thing called dataset, which I guess
is a standalone database you can create and play with." indicates that you
need to study.

A dataset is an in-memory repository of data retrieved from one or more data
sources for manipulation by your application. The data source in question
may be a relational database table(s) data stored in the tables collection
in your dataset. Datasets support the ADO.NET disconnected model for data
applications where data is retrieved from data source(s) and placed in the
dataset for application use with the connection to the original data source
active only long enough to retrieve the data. The application works with
data in the tables in the dataset and, if updates need to be made permanent,
the connection to the original data source is reestablished only long enough
to send the updates from the dataset back to the data source.

There are numerous articles and examples available on MSDN
(msdn.microsoft.com) to help you get comfortable with ADO.NET.

http://blogs.msdn.com/bethmassi/archive/2008/03/18/new-video-tutorials-from-the-community.aspx

http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/3be9ec05-d9d7-4c7c-a131-d2a4d9b24dec/

20 Jun 2009 9:08 AM

Cor Ligthert[MVP]

Scott,

I missed your question, we have a fine sample for your question on our
website.

It is almost like your code by the way.
http://www.vb-tips.com/CreateMDB.aspx

However, it is not used almost anymore, I would simply download a SQL Server
Express 2008 in your case as that is freeware

Cor

Show quoteHide quote

"Scott Baxter" <sc***@websearchstore.com> wrote in message
news:uv1jTm67JHA.4100@TK2MSFTNGP06.phx.gbl...
> Hello,
>
> In the new vb.net is a thing called dataset, which I guess is a standalone
> database you can create and play with.
>
> The following code works fine for that.
>
> But I can't seem to define things so that it would work against a real
> database.
>
> Here's code that creates an access database, with no structure or fields
> on
> the disk:
>
>
> If CreateAccessDatabase("e:\test2.mdb") = True Then
>            MsgBox("Database Created")
>        Else
>            MsgBox("Database Creation Failed")
>        End If
>
> Public Function CreateAccessDatabase( _
>    ByVal DatabaseFullPath As String) As Boolean
>        Dim bAns As Boolean
>        Dim cat As New ADOX.Catalog()
>        Try
>
>
>            'Make sure the folder
>            'provided in the path exists. If file name w/o path
>            'is specified, the database will be created in your
>            'application folder.
>
>            Dim sCreateString As String
>            sCreateString = _
>              "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
>               DatabaseFullPath
>            cat.Create(sCreateString)
>
>            bAns = True
>
>        Catch Excep As System.Runtime.InteropServices.COMException
>            bAns = False
>            'do whatever else you need to do here, log,
>            'msgbox etc.
>        Finally
>            cat = Nothing
>        End Try
>        Return bAns
>    End Function
>
>
> I want to use code like the code below to add the fields to the database.
>
> Is there a way to do this?
>
> Thanks.
>
> Scott
>
>
>
>
> Public Sub createdataset()
>        'Dim ds As New DataSet()
>        ' Create a table; set its initial capacity.
>        Dim dtEmp As New DataTable("Employees")
>        dtEmp.MinimumCapacity = 200
>        ' Create all columns.
>        ' You can create a DataColumn and then add it to the Columns
> collection.
>        Dim dcFName As New DataColumn("FirstName", GetType(String))
>        dtEmp.Columns.Add(dcFName)
>        ' Or you can create an implicit DataColumn with the Columns.Add
> method.
>        dtEmp.Columns.Add("LastName", GetType(String))
>        dtEmp.Columns.Add("BirthDate", GetType(Date))
>        ' When you have to set additional properties, you can use an
> explicit
>        ' DataColumn object, or you can use a With block.
>        With dtEmp.Columns.Add("HomeAddress", GetType(String))
>            .MaxLength = 100
>        End With
>        ' (When you must set only one property, you can be more concise,
>        ' even though the result isn't very readable.)
>        dtEmp.Columns.Add("City", GetType(String)).MaxLength = 20
>        ' Create a calculated column by setting the Expression
>        ' property or passing it as the third argument to the Add method.
>        dtEmp.Columns.Add("CompleteName", GetType(String), _
>        "FirstName + ' ' + LastName")
>        ' Create an identity, auto-incremented column.
>        Dim dcEmpId As New DataColumn("EmpId", GetType(Integer))
>        dcEmpId.AutoIncrement = True ' Make it auto-increment.
>        dcEmpId.AutoIncrementSeed = 1
>        dcEmpId.AllowDBNull = False ' Default is True.
>        dcEmpId.Unique = True ' All key columns should be unique.
>        dtEmp.Columns.Add(dcEmpId) ' Add to Columns collection.
>        ' Make it the primary key. (Create the array on-the-fly.)
>        dtEmp.PrimaryKey = New DataColumn() {dcEmpId}
>        ' This is a foreign key, but we haven't created the other table
> yet.
>        dtEmp.Columns.Add("DeptId", GetType(Integer))
>        ' Add the DataTable to the DataSet.
>        ds.Tables.Add(dtEmp)
>    End Subl
>