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