Results 1 to 15 of 15

Thread: Can you help me find a good starter tutorial on databases?

  1. #1

    Thread Starter
    Hyperactive Member stepdragon's Avatar
    Join Date
    Aug 2011
    Location
    Cincinnati
    Posts
    288

    Can you help me find a good starter tutorial on databases?

    Hi, I'm in need of a simple database for a program I'm writing...
    First, here's some info on what I want to do, so you can even tell me if a database is what I would need to accomplish it.

    It needs to have a single table, with two columns.

    First column will hold only 8 digit HEX codes (can be stored in a string if need be) - every entry will be unique
    Second column will hold a file path

    I need to be able to search for the path from the HEX code.

    I was able to create and link to an SQL Server Express DB in VB by using this tutorial: http://visualbasic.about.com/od/usin...dbvsfile_2.htm
    But that just create's and links the database, without telling me the code on how to manipulate it.

    I've been pulling my hair out about this for a while, and I'm hoping someone can point me in the right direction. Having never used databases before, this is extremely frustrating.

    Thanks

  2. #2
    Banned
    Join Date
    Mar 2009
    Posts
    764

    Re: Can you help me find a good starter tutorial on databases?

    project, save settings (or from my project in solution explorer), application, enable application framework and save my.settings on shutdown are checked
    in settings add variable that will be saved

    dim settings as new my.settings ' global var
    in sub :
    settings.varname = "" 'as varname's var type
    settings.save()
    Last edited by moti barski; Aug 16th, 2011 at 05:42 PM.

  3. #3
    Banned
    Join Date
    Mar 2009
    Posts
    764

    Re: Can you help me find a good starter tutorial on databases?

    SQL


    http://www.youtube.com/watch?v=3Aadk...eature=related

    crud : create read update delete
    dbms : database management system
    sql : structured query language
    table = rows and columns of data (cells)

    download : sql express 2008 (up to 4Gbyte of data) or 2010 (up to 10Gbytes)
    building and modifying databases is intuitive (using only SQL)

    sql statements :

    select * from table
    table = table name

    select count(*) from table
    return how many rows in the table
    select count(column1) from table
    return how many not null rows in the column1 of the table table

    select column1, column2 from table
    column = column name
    select column1 as name, column2 as 'name two' from table
    also names the requested columns (1 and 2)

    SELECT column1, column2 FROM TABLE WHERE column1 = 'stringvaluehere'
    add ORDER BY column
    to sort

    UPDATE table SET column = 'new data' WHERE colomn = x
    x is a number

    DELETE FROM table WHERE colomn = x

  4. #4
    Banned
    Join Date
    Mar 2009
    Posts
    764

    Re: Can you help me find a good starter tutorial on databases?

    SQL DATABASE AND VB.NET


    new project
    project, add new item, service based database
    finish
    view, database explorer, +, tables, rightclick add new table
    from table created add data, and tab to move to the next field
    save all, data sources, add new data source, database, connection string copy (after click +) and save it in a text file !,
    next, check all, finish
    connection string also at rightclick database, properties (in database explorer)

    ado auto navigator: data sources, +,+ of table, arrow, details, drag the table in to the form. you can add, delete
    insert now with the new control. you can alternitivlly use this codes:

    the examples were tested on a 1 table database with 2 fields : fname, lastname
    add
    Imports System.Data.SqlClient 'present in all sql codes
    insert:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim connetionString As String
    Dim connection As SqlConnection
    Dim adapter As New SqlDataAdapter
    Dim sql As String
    connetionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\moti1.mdf;Integrated Security=True;User Instance=True"
    connection = New SqlConnection(connetionString)
    Dim x, x1 As String
    x = TextBox1.Text
    x1 = TextBox2.Text
    sql = String.Format("insert into table1 (fname,lname) values('{0}','{1}')", x, x1)
    Try
    connection.Open()
    adapter.InsertCommand = New SqlCommand(sql, connection)
    adapter.InsertCommand.ExecuteNonQuery()
    MsgBox("Row inserted !! ")
    Catch ex As Exception
    MsgBox(ex.ToString)
    End Try
    End Sub

    Dataadapter DeleteCommand - Sql Server (in the button sub)

    Dim connetionString As String
    Dim connection As SqlConnection
    Dim adapter As New SqlDataAdapter
    Dim sql As String
    connetionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\moti1.mdf;Integrated Security=True;User Instance=True"
    connection = New SqlConnection(connetionString)
    Dim x As String
    x = TextBox1.Text
    sql = String.Format("delete table1 where fname ='{0}'", x)
    Try
    connection.Open()
    adapter.DeleteCommand = connection.CreateCommand
    adapter.DeleteCommand.CommandText = sql
    adapter.DeleteCommand.ExecuteNonQuery()
    MsgBox("Row(s) deleted !! ")
    Catch ex As Exception
    MsgBox(ex.ToString)
    End Try

    the following examples are for a diffrent(but simmilar) data base:

    populate and display

    Imports System.Data.SqlClient
    Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim connetionString As String
    Dim connection As SqlConnection
    Dim adapter As SqlDataAdapter
    Dim ds As New DataSet
    Dim i As Integer
    connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
    connection = New SqlConnection(connetionString)
    Try
    connection.Open()
    adapter = New SqlDataAdapter("Your SQL Statement Here", connection) 'or
    adapter.SelectCommand = New SqlCommand("Your SQL Statement Here", connection) ' end of or
    adapter.Fill(ds)
    connection.Close()
    For i = 0 To ds.Tables(0).Rows.Count - 1
    MsgBox(ds.Tables(0).Rows(i).Item(1))
    Next
    Catch ex As Exception
    MsgBox(ex.ToString)
    End Try
    End Sub
    End Class

    Dataadapter UpdateCommand - Sql Server

    Imports System.Data.SqlClient
    Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim connetionString As String
    Dim connection As SqlConnection
    Dim adapter As New SqlDataAdapter
    Dim sql As String
    connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
    connection = New SqlConnection(connetionString)
    sql = "update product set product_price = 1001 where Product_name ='Product7'"
    Try
    connection.Open()
    adapter.UpdateCommand = connection.CreateCommand
    adapter.UpdateCommand.CommandText = sql
    adapter.UpdateCommand.ExecuteNonQuery()
    MsgBox("Row updated !! ")
    Catch ex As Exception
    MsgBox(ex.ToString)
    End Try
    End Sub
    End Class

    methode 2 for passing variables : _ is down line (instead of {0},x of the delete code above)
    also: the parameters past to the sql command sould be filtered from malicious sql commands and '
    for the code with {0} unless you use the following code that doesn't require fillterring.

    Dim sql As String = "INSERT INTO User (FirstName, LastName, DateOfBirth, ChildCount) " & _
    "VALUES (@FirstName, @LastName, @DateOfBirth, @ChildCount)"
    Dim myCommand As New SqlCommand(sql)
    With myCommand.Parameters
    .AddWithValue("@FirstName", Me.firstNameField.Text)
    .AddWithValue("@LastName", Me.lastNameField.Text)
    .AddWithValue("@DateOfBirth", Me.dateOfBirthPicker.Value.Date)
    .AddWithValue("@ChildCount", CInt(Me.childrenSpinner.Value))
    End With

    read all data with code: (for first example of 2 field database (fname,lname))
    add imports system.data

    Dim con As New SqlConnection
    Dim cmd As New SqlCommand
    Try
    con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\moti1.mdf;Integrated Security=True;User Instance=True"
    con.Open()
    cmd.Connection = con
    cmd.CommandText = "SELECT fname, lname FROM table1"
    Dim lrd As SqlDataReader = cmd.ExecuteReader()

    While lrd.Read()
    MsgBox(lrd.Item(0).ToString())
    MsgBox(lrd.Item(1).ToString())
    ' two fields
    End While

    Catch ex As Exception
    MessageBox.Show("Error while retrieving records on table..." & ex.Message, "Load Records")
    Finally
    con.Close()
    End Try

  5. #5
    Banned
    Join Date
    Mar 2009
    Posts
    764

    Re: Can you help me find a good starter tutorial on databases?

    SQL STORED PROCEDURES

    in asp.net : after connecting to the sql database :
    right click stroed procedure, add.

    type :
    create procedure dbo.name

    @columnname varchar(60)

    as

    select column1, column2 from tablename
    where columnname = @columnname

    return

    you can right click and execute to test

    ctrl + shift + a or add new item, linq to sql classes
    right click GUI to show methode pane, drag the created stored procedure into the pane
    add to the form a data grid view control, and a text box

    in the code (for vb code) :

    private records as new contactbasedatacontext
    'as global variable

    ' in an event like button event

    dim rec = records.selectcontact(textbox1.text)
    datagridview1.datasource = rec

    ' run , put parameter in textbox1 click button to run event code

    2nd example of stored procedure :

    alter procedure dbo,insertnojutsu

    @param1 varchar(49)
    @param2 varchar(30)

    as

    insert into tablename(param1,param2)
    where (@param1,@param2)

    return

  6. #6
    Banned
    Join Date
    Mar 2009
    Posts
    764

    Re: Can you help me find a good starter tutorial on databases?

    MINNING ACCESS DATABASES WITH VB.NET


    connecting to an access database:

    1 File > New Project from the menu bar, Windows Application
    2 View > Solution Explorer from the menu bar, click on Data Sources, choose data source, next
    version 2010 of VBN NET select dataset, next
    3 New Connection, Change button, Select Microsoft Access Database File, ok
    4 Click the Browse button and navigate to where on your computer you downloaded our Access Database,
    Click Test Connection to see if everything is OK
    5 +, save connection string in a txt file
    6 Click No on the message box to stop VB copying the database each time it runs
    7 from data sources you can drag and drop tables or fields to the form to navigate and edit(to edit before
    draging click the arrow near the table and choose detail view)

    tip: When all Textbox are selected, click on the Format menu at the top
    From the Format menu select Align > Lefts. The left edges of the Textboxes will align themselves
    From the Format menu select Vertical Spacing > Make Equal. The space between each textbox will then be
    the same .

    codes:
    OLE stands for Object Linking and Embedding

    in a sub:
    dim con As new OleDb.OleDbConnection
    dim dbProvider,dbSource As string
    dim ds As new DataSet
    dim da As OleDb.OleDbDataAdapter
    dim sql As string

    dbProvider = "PROVIDER=Microsoft.jet.OLEDB.4.0;"
    dbSource = "Data Source = E:/AddressBook.mdb" ' path of access database

    con.ConnectionString = bProvider & dbSource

    con.open()

    sql = "SELECT * FROM tblContacts" 'sql command, dataset contains table tblContacts
    da = new OleDb.OleDbDataAdapter(sql, con)
    da.Fill(ds, "AddressBook") ' dataset filled


    con.close()

    txtFirstName.Text = ds.Tables("AddressBook").Rows(0).Item(1)' getting data from ds
    txtSurname.Text = ds.Tables("AddressBook").Rows(0).Item(2)
    'or
    'txtFirstName.Text = ds.Tables("whatever").Rows(0).Item(1)
    'txtSurname.Text = ds.Tables("whatever").Rows(0).Item(2)

    ' MaxRows = ds.Tables("AddressBook").Rows.Count

    end sub

    Updating a Record:

    Dim cb As New OleDb.OleDbCommandBuilder(da)

    ds.Tables("AddressBook").Rows(inc).Item(1) = txtFirstName.Text
    ds.Tables("AddressBook").Rows(inc).Item(2) = txtSurname.Text

    da.Update(ds, "AddressBook")

    MsgBox("Data updated")

    Add a New Record

    Dim cb As New OleDb.OleDbCommandBuilder(da)
    Dim dsNewRow As DataRow

    dsNewRow = ds.Tables("AddressBook").NewRow()

    dsNewRow.Item("FirstName") = txtFirstName.Text
    dsNewRow.Item("Surname") = txtSurname.Text

    ds.Tables("AddressBook").Rows.Add(dsNewRow)

    da.Update(ds, "AddressBook")

    MsgBox("New Record added to the Database")

    Deleting Records from a Database:

    Dim cb As New OleDb.OleDbCommandBuilder(da)

    ds.Tables("AddressBook").Rows(inc).Delete() ' inc is an integer variable

    da.Update(ds, "AddressBook")


    tip: getting a folder path:
    Dim fldr As String
    fldr = Environment.GetFolderPath( Environment.SpecialFolder.MyDocuments ) & "/AddressBook.mdb"

  7. #7
    Banned
    Join Date
    Mar 2009
    Posts
    764

    Re: Can you help me find a good starter tutorial on databases?

    MS ACCESS


    terminology : row = record, collomn = category or field table = rows & collomns

    1 new, create, table thats pretty much it.

    2 if a field type is set (in the menu ribbon) as autonumber it will fill the field automatically with a number

    3 you can import excell files to ms access (import from menu)

  8. #8
    Banned
    Join Date
    Mar 2009
    Posts
    764

    Re: Can you help me find a good starter tutorial on databases?

    forgot how to wrap code as vb code for vbulletin

  9. #9

    Re: Can you help me find a good starter tutorial on databases?

    Moti: It's [code]

    Also, you gave a bunch of code without much explanation other than how to create a few things. It would probably be beneficial to add some sort of comments in the code?

  10. #10

    Thread Starter
    Hyperactive Member stepdragon's Avatar
    Join Date
    Aug 2011
    Location
    Cincinnati
    Posts
    288

    Re: Can you help me find a good starter tutorial on databases?

    This may take some time to read through and fully understand... wow thanks... I'll take my time and read through it all. I'll probably have some questions later tonight or early tomorrow... but That's quite alot....

    Did you have that written out somwhere already? or did you seriously see the post and say "Imma write a tutorial now" and then just speed wrote? that is seriously crazy...

    Ok, I'm gonna go actually read it now (I only skimmed it to post this reply)

    Oh and the youtube link is broken

  11. #11

    Thread Starter
    Hyperactive Member stepdragon's Avatar
    Join Date
    Aug 2011
    Location
    Cincinnati
    Posts
    288

    Re: Can you help me find a good starter tutorial on databases?

    What's the difference between a Service-based Database, and a Local Database? I want my program to have its own database, I don't want to be running anything else, or connect to anything else...

    My program is basically going to be a file tracker on a single computer, hence the address and file path, but I want it to be standalone, and able to be run on multiple computers (with different data for each)... what would be the best way of doing that?

    Sorry, that I haven't read too much into the code yet, This being a 'somewhat confusing' topic for me, I don't want to get too far into something which ends up not being what I'm looking for...

  12. #12
    Banned
    Join Date
    Mar 2009
    Posts
    764

    Re: Can you help me find a good starter tutorial on databases?

    Quote Originally Posted by formlesstree4 View Post
    Moti: It's [code]

    Also, you gave a bunch of code without much explanation other than how to create a few things. It would probably be beneficial to add some sort of comments in the code?
    not the code one, the one that after wrapping looks like actuall vb code with dim in blue color .nut usually uses it.

    @stepdragon : you have 3 paths to choose from my.settings, sql (with the sql and vb walkthrough or sql stored procedures) or build an ms access data base
    then mine that database for data, the sql way is the more versatile one.

    you can also use text files tell me if you need it's walkthrough.

    as for the walkthroughs I pasted them from a book people don't like me to mention.

  13. #13

    Thread Starter
    Hyperactive Member stepdragon's Avatar
    Join Date
    Aug 2011
    Location
    Cincinnati
    Posts
    288

    Re: Can you help me find a good starter tutorial on databases?

    If it helps with the choosing, because I'm using an 8digit HEX code as my primary key (If I understand databases correctly) I could theoretically have up to 4.2 Billion unique possibilities... granted It will all only be short strings, and I'll most likley not even use 1/100th of those addresses as actual fields, but its worth mentioning...

    I'm trying to find the easiest to understand and code way of doing this (because I'm a beginner when it comes to data manipulation) all I need to do is for example search for or set an 8Digit HEX code, and assign a string (file path) to it.

    You say I have 3 paths.
    1. my.settings (which I'm already using for persistant variables, but I only set those in design mode, and I don't know how to create them at runtime. also, with the possiblility of THOUSANDS of entries, that might be better to leave for my actual persistant variables, which need quick often access.)

    2. sql, which sounds kind of what I'm looking for, but once again, no clue where to start... I've got SQL Server 2010 express, and MySQL 5.5 installed (all from testing failures).

    3. access DB, which I don't know anything about, Is there a free version of access? once again, no idea where to start...

    From what I've described of my intentions, what do you think would be the best route to take?
    by text files, do you mean just making a sequential file? I did that WAAAAAAAY back in the day, but DBs seem a much more viable option, because they're designed for this sort of thing... But I don't know, I don't have any experience with them so how can I choose?

  14. #14
    Banned
    Join Date
    Mar 2009
    Posts
    764

    Re: Can you help me find a good starter tutorial on databases?

    if your program is for a pc then use text files

    8 save text file:
    8.1 Imports System.IO (1st line of code)
    8.2
    Code:
    If File.Exists("path string") Then 
               File.Delete("path string")
               File.Delete("path string")
    End If
    instead of using imports.io you can use in the code: io.file.
    8.3 File.WriteAllText(pathString, dataString) '
    8.4 to read txt file :
    File.ReadAllText(pathString)

    if the program will move on to an asp.net site use sql

  15. #15

    Thread Starter
    Hyperactive Member stepdragon's Avatar
    Join Date
    Aug 2011
    Location
    Cincinnati
    Posts
    288

    Re: Can you help me find a good starter tutorial on databases?

    Quote Originally Posted by moti barski View Post
    if your program is for a pc then use text files

    8 save text file:
    8.1 Imports System.IO (1st line of code)
    8.2
    Code:
    If File.Exists("path string") Then 
               File.Delete("path string")
               File.Delete("path string")
    End If
    instead of using imports.io you can use in the code: io.file.
    8.3 File.WriteAllText(pathString, dataString) '
    8.4 to read txt file :
    File.ReadAllText(pathString)

    if the program will move on to an asp.net site use sql
    Sorry, I don't understand what this is supposed to do, so I can't even try to figure out how it works...
    sorry, can you be a little more (talking to an idiot)-ish


    Hold on... reread it about 25 times before I (think I) got it...
    Instead of a single database file, you're suggesting that I create a seperate file for each entry? then using file search to find each entry, and reading the file to get the data?

    not only does this sound extremely inefficient, but it sounds like I would be scolded by any half self respecting programmer... shouldn't I use this simple program to learn the basics of database programming, rather than doing the workaround? (which is what I would have done 10 years ago in VB3... actually, now that I think about it, I did do that)

    there's also the fact that I may wish to add more data to the dataset later in my program development... which would be easier than single files, because I could use one entry vs, multiple files...
    Last edited by stepdragon; Aug 16th, 2011 at 06:33 PM.

Tags for this Thread

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