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

[stepdragon]

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

[moti barski]

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()

[moti barski]

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

[moti barski]

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

[moti barski]

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

[moti barski]

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"

[moti barski]

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)

[moti barski]

forgot how to wrap code as vb code for vbulletin

[formlesstree4]

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?

[stepdragon]

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

[stepdragon]

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...

[moti barski]

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.

[stepdragon]

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?

[moti barski]

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

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

[stepdragon]

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

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...