SqlCommand, SqlConnection,SqlDataAdapter, SqlDataReader in vb.net

You definitely know these commands just as SqlCommand, SqlConnection,SqlDataAdapter, SqlDataReader ect. and I will explain how the command works

• NAME

A SqlConnection object represents a unique session to a SQL Server data source. With a client/server database system, it is equivalent to a network connection to the server. SqlConnection is used together with SqlDataAdapter and SqlCommand to increase performance when connecting to a Microsoft SQL Server database. For all third-party SQL server products, and other OLE DB-supported data sources, use OleDbConnection.When you create an instance of SqlConnection, all properties are set to their initial values. For a list of these values. to execute command 

SqlConnection is used Initializes a new instance of the SqlConnection class, to put the string into a SqlConnection,just like SqlConnection(String) 

Code:

Dim ConnString as string =  "Data Source=192.168.1.1\SQLEXPRESS;Initial Catalog=Contoh;



User=admin; Pwd=1" ' your connection string here ..

sqlclient.sqlconnection(ConnString) 'put the string with sqlconnection

SqlCommand is used Initializes a new instance of the SqlCommand class, you can use the SELECT,INSERT,UPDATE,DELETE command with SQLCommand, SQLCommand will execute these commands in the database, surely you must have a connection string to execute the query. to added your connection string just put the code, SqlCommand(String, SqlConnection)

Code:

Dim ConnString as string =  "Data Source=192.168.1.1\SQLEXPRESS;Initial Catalog=Contoh; User=admin; Pwd=1" ' your connection string here ..


sqlclient.sqlconnection(ConnString) 'put the string with sqlconnection


SqlClient.SqlCommand.Connetion = sqlclient.sqlconnection(ConnString) 'and hook it up with sqlclient.command 

The SqlDataAdapter, serves as a bridge between a DataSet and SQL Server for retrieving and saving data. The SqlDataAdapter provides this bridge by mapping Fill, which changes the data in the DataSet to match the data in the data source.
SqlDataAdapter is used Initializes a new instance of the SqlDataAdapter class, how its mapping with dataset. 

Code:

Dim Da As New SqlClient.SqlDataAdapter 

Dim Ds As New DataSet

Da.SelectCommand = SqlClient.SqlCommand 

Da.Fill(ds, "grafik") 'Fill SqlCommand by maaping into a dataset

To create a SqlDataReader, you must call the ExecuteReader method of the SqlCommand object, instead of directly using a constructor, 

Code:

Dim Str As String = "Data Source=192.168.1.1\SQLEXPRESS;Initial Catalog=Contoh; User=admin; Pwd=1" 
Dim cnt As New SqlClient.SqlConnection(Str)  'connection string
Dim cmd As New SqlClient.SqlCommand 
Dim read As SqlClient.SqlDataReader
            cnt.Close()
            cnt.Open()            
            With cmd
                .Connection = cnt
                .CommandText = "select * from grafik where nomor ='" & cari.Trim & "'"
                read = cmd.ExecuteReader(CommandBehavior.SingleRow) 
                If read.Read Then
                    MsgBox("Read table ", MsgBoxStyle.Information) 
                Else
                    MsgBox("Unread table", MsgBoxStyle.Information)
                End If
            End With

related articles Insert, Select, Update and Delete in VB.net

Reading Source msdn

What is Visual Basic?

• HISTORY

VB 1.0 was introduced in 1991. The drag and drop design for creating the user interface is derived from a prototype form generator developed by Alan Cooper and his company calledTripod. Microsoft contracted with Cooper and his associates to develop Tripod into a programmable form system for Windows 3.0, under the code name Ruby (no relation to the Ruby programming language).

Tripod did not include a programming language at all. Microsoft decided to combine Ruby with the Basic language to create Visual Basic.

The Ruby interface generator provided the "visual" part of Visual Basic and this was combined with the "EB" Embedded BASIC engine designed for Microsoft's abandoned "Omega" database system. Ruby also provided the ability to load dynamic link libraries containing additional controls (then called "gizmos"), which later became the VBX interface

March 1992---VB 2.0 Toolkit (Rawhide) Released This toolkit integrated several third-party tools into a single package, putting controls in the hands of many VB developers for the first time. It provided instrumental in helping VB's third party market achieve critical mass.

November 1992---VB2 Debuts Adds ODBC Level 1 support, MDI forms, and object variables. First version to feature the Professional Edition.

November 1992---Microsoft Access Ships It brings VB's combination of extensibility, ease-of-use, and visual point-and-click emphasis to a Relational Database. It also includes a macro language called Access BASIC that contains a subset of VB 2.0's core syntax.

June 1993---VB3 Debuts Integrates the Access Engine (Jet), OLE Automation and reporting.

May 1995---Borland's Delphi Debuts The perennial preview for the features you'll find in the next VB release.

Fall 1996---Internet Explorer 3.0 Ships Features include VBScript, which contains a subset of VB. It lets developers leverage their existing VB skills in Web programming.

October 1996---VB4 Debuts Permits you to create your own add-ins. Also introduces classes and OCX's.

Winter 1996---NT Option Pack 4 Released Includes Internet Information Server 3.0, which includes ASP. Enabled VB programmers to leverage their existing skills on Web servers.

January 1997---Microsoft Office 97 Debuts Developer Edition integrates VBA into all Office apps (except Outlook which uses VBScript)

April 1997---VB5 Debuts Incorporates compiler, WithEvents, and the ability to create ActiveX controls.

October 1998---VB6 Debuts Introduces WebClasses, windowless controls, data designers, new reporting designers, and the ability to create data sources.

February 2002---VB.Net Debuts April 2003---VB.Net 2003 Debuts November 2005---VB.Net 2005 Debuts

• Features

Visual Basic can create executables (EXE files), ActiveX controls, or DLL files, but is primarily used to develop Windows applications and to interface database systems. Dialog boxes with less functionality can be used to provide pop-up capabilities. Controls provide the basic functionality of the application, while programmers can insert additional logic within the appropriate event handlers. For example, a drop-down combination box will automatically display its list and allow the user to select any element. An event handler is called when an item is selected, which can then execute additional code created by the programmer to perform some action based on which element was selected, such as populating a related list.

Alternatively, a Visual Basic component can have no user interface, and instead provide ActiveX objects to other programs via Component Object Model (COM). This allows for server-sideprocessing or an add-in module.

Reading Source it.toolbox and wikipedia

Play an audio file in VB.NET

Today we are going to play wav file in vb.net and spell the number using audio files. using winmm.dll, about winmm.dll, it is a module for the Windows Multimedia API, which contains low-level audio and joystick functions and we start to create the application follow the instructions below

Create a Windows Application

1.   Start Microsoft Visual Studio .NET.
2.   On the File menu, point to New, and then click Project.
3.   And Project Types, click Visual Basic Projects.
4.   Under Templates, click Windows Application.
5.   Create your project name, type Project1, and then click OK
6.   Design your form just like the picture below

create module with right clict in the project choose add and add new modul create modul name, type bassSound then we’re going to put this in our code:

we play sound using SND_NOSTOP flags this function will immediately return False without playing the requested sound.

Code:

Option Strict Off
Option Explicit On
Module bassSound
    Public Const SND_NOSTOP As Integer = &H10
    Public Declare Function sndPlaySound Lib "winmm.dll" Alias "sndPlaySoundA" (ByVal lpszSoundName As String, ByVal uFlags As Integer) As Integer
End Module

Specifies options for playing the sound using one or more of the following flags 

SND_SYNC: The sound is played synchronously and the function does not return until the sound ends

SND_ASYNC: The sound is played asynchronously and the function returns immediately after beginning the sound.

SND_NODEFAULT: If the sound cannot be found, the function returns silently without playing the default sound.

SND_LOOP: The sound will continue to play repeatedly until ' sndPlaySound is called again with the lpszSoundName$ parameter set to null, You must also specify the SND_ASYNC flag to loop sounds. 


create module type with spell and put the code below                                                                                       

Code:

Option Strict Off
Option Explicit On
Module spell
    Public Function say(ByRef Value As Integer) As String

        Select Case Value
            Case 0 : say = " zero"
            Case 1 : say = " one"
            Case 2 : say = " two"
            Case 3 : say = " three"
            Case 4 : say = " four"
            Case 5 : say = " five"
            Case 6 : say = " six"
            Case 7 : say = " seven"
            Case 8 : say = " eight"
            Case 9 : say = " nine"
            Case 10 : say = " ten"
            Case 11 : say = " eleven"
            Case 12 : say = " twelve"
            Case 13 : say = " thirteen"
            Case 14 To 19 : say = say(Value Mod 10) & " teen"
                '.....
        End Select
    End Function
End Module

In the main form we choose the button1, we're using handle click events or double click button1,to call
function module "say"

Code:

TextBox2.Text = Trim(say(CInt(TextBox1.Text)))

And we turn to button2 same as the button1,we're using handle click events or double click, to call function moudle "sndPlaySound", we call the name to play the sound
Code:

Dim i As Object
Dim path(1) As String
Dim arr() As String
arr = Split(TextBox2.Text, " ")
   For i = LBound(arr) To UBound(arr)
       Call sndPlaySound(path(1) + "Sounds\" & arr(i) + ".wav", SND_NOSTOP)
   Next

plus button and less button put this in our coude
Code:

TextBox1.Text = CStr(Val(TextBox1.Text) + 1)
TextBox1.Text = CStr(Val(TextBox1.Text) - 1)

wav files sourcecode

Reading and Writing XML in VB.net

how to store the data on listboxt and when you open the application, the data is still there , I searched on google how to do it, it turns out that using xmlrader and write it back use xmltextrwiter, the original code is not belongs to me,but I'll explain how it works with vb.net .

the code below represent how to read each of the element of xml, and write it into listbox.

Code:

Dim XMLReader As Xml.XmlReader
 XMLReader =New Xml.XmlTextReader("text.xml")'you also can change the directory from xml
   While XMLReader.Read                      'ex. ("Data\xmldata text.xml")
     Select Case XMLReader.NodeType          'selecet the node (file) from text.xml
         Case Xml.XmlNodeType.Element
            If XMLReader.AttributeCount > 0 Then 'xmlraeder will start from zero
              While XMLReader.MoveToNextAttribute
                 If XMLReader.Name = "File" Then
                    ListBox1.Items.Add(XMLReader.Value)'looping and add each of the elem                                                       'ent into listbox
                 Else
                 End If
              End While
            End If
     End Select
   End While
XMLReader.Close()

and how write it back and save into xml file, you can use the code below.

Code:

 Dim XMLobj As Xml.XmlTextWriter
 Dim ue As New System.[Text].UnicodeEncoding    'unicode 
 XMLobj = New Xml.XmlTextWriter("text.xml", ue) 'rewrite text.xml
 XMLobj.Formatting = Xml.Formatting.Indented
 XMLobj.Indentation = 3
 XMLobj.WriteStartDocument()   'xml will be declared with the xml version="1.0"
 XMLobj.WriteStartElement("List") ' write the node (file)
 Dim i As Integer
  For i = 0 To ListBox1.Items.Count - 1 'looping and  XmlTextWriter will be begun from listbox.value(0)        
     XMLobj.WriteStartElement("Text")   'from text.xml
     Dim temp As String = ""            
     ListBox1.SetSelected(i, True)
     temp = ListBox1.SelectedItem
     XMLobj.WriteAttributeString("File", temp)
     XMLobj.WriteEndElement()
  Next
 XMLobj.WriteEndElement()
 XMLobj.Close() 

besides xml you can also use my.setting  as a dynamic storage you can find it in project properties, 

My.Setting

to read and display
Code:

Me.TextBox1.Text = My.Settings.IPAddress

and rewrite

My.Settings.IPAddress = Me.TextBox1.Text 

sourcecode

Creating a Report with Crystal Reports VB Net

• Labels: Crystal Report, SqlServer

This tutorial will help you to create a report with crystal report, we create ADO OLE DB as a connection string, to create a simple report with crystal report using  ADO OLE DB Connection string, follow the instructions, first we use sqlserver as a database and crystal report as our report. add your crystal report with right click on your current project properties > add  > new items then click and select reporting choose cystal report then ok.

Create crystal report a

and will show crystal reports gallery, choose "as a blank report" then ok, to select the connections string properties, right click the database fields in the field explorer located in your project left design and choose database expert.

Create crystal report b

microsoft provides many more available connections string, to hook up your sql server database with oledb(ado), create new connection > ole db (ado)  > make new connection > microsoft oledb provide for sql server.

create crystal report c

then click next to continue will show you to manage the connection string, fill the server name with your server connection, check the integrated securty if you connect using localhost, select the database that you want to select in the database field then click finish, ok now we have a connection string with provided connection by sql server, and we going to choose our table for crystal report design, choose the server name "192.168.1.1/sqlexpress"  > database > dbo > select your table by clicking the arrow, then click ok.

create crystal report d

now we have our current table,to put the field from the table in your crystal report design,choose the table, select field by using "drag and drop" you will get more tools design with right click in your report or from the toolbox on bottom left.

Cystral report design

ok now we take a look "windows form" in our project if you dont have it one right click on the current project, create with "add new" > "add windows form", create the same as the picture below by adding two datetimepicker,a button and a crystalreportviewer double click your button insert the code below.

Code:

        Dim Conn As New SqlClient.SqlConnection


        Dim Da As New SqlClient.SqlDataAdapter


        Dim ds As New DataSet


        Dim strConnection As String


        Dim SQL As New SqlClient.SqlCommand


        strConnection = "Data Source=192.168.1.1\SQLEXPRESS;Initial Catalog=Contoh; 


        User=admin; Pwd=1"


        Conn = New SqlClient.SqlConnection(strConnection)


        Conn.Open()


        SQL.Connection = Conn


        SQL.CommandText = "Select * From grafik Where tanggal between'" & 


        Format(DateTimePicker1.Value, "MM/dd/yyyy") & "' and '" &   


        Format(DateTimePicker2.Value, "MM/dd/yyyy") & "'order by Nomor asc "


        Da.SelectCommand = SQL


        Da.Fill(ds, "grafik")


        Dim a As New report 'your crystal report


        Dim b As New Reportform


        a.SetDataSource(ds.Tables!grafik) 'binding your cristal report to the datasource


        b.GroupBox1.Visible = False


        b.CrystalReportViewer1.Visible = True


        b.CrystalReportViewer1.ReportSource = a

        b.Refresh()



        b.WindowState = FormWindowState.Maximized


        b.Show()

form design

from the code I ask of the code to select between two date from the data in the table, the database relates with this article Insert,Save,Update dan Delete pada VB net

crystal report viewer

hope this article will help you.

sourcecode