Insert Record/Data that Contain Quotes in Visual Basic

0 comments

Posted on 9th July 2011 by Admin in Database Programming |Visual Basic .Net |Visual Basic 6

,

‘This visual basic shows how to use ADO to insert records into a database when fields contain quotes

‘The program creates a SQL INSERT statement. To protect against quotes in the
‘user-entered values, the program replaces each single quote with two single
‘quotes. The database replaces the pairs of quotes with single quotes when it
‘inserts the values. For example, the database treats the text “Tess’s Bakery” as
‘”Tess’s ” when it adds it to the database.

Private Sub cmdInsert_Click()
Dim DB_Name As String
Dim SQL_Str As String
Dim Conn As ADODB.Connection
Dim ctl As Control
‘ Get the data.
DB_Name = App.Path
If Right$(DB_Name, 1) <> “” Then DB_Name = DB_Name & “”
DB_Name = DB_Name & “Employee.mdb”
‘ Open a connection.
Set Conn = New ADODB.Connection
Conn.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;” & “Data Source=” & DB_Name & _
“;” & “Persist Security Info=False”
Conn.Open
‘ Compose the INSERT statement.
SQL_Str = “INSERT INTO Addresses “ & “(Name, Street, City, State, Zip) “ & ” VALUES (“ & _
“‘” & Replace$(txtName.Text, “‘”, “””) & “‘, “ & “‘” & _
Replace$(txtStreet.Text, “‘”, “””) & “‘, “ & “‘” & _
Replace$(txtCity.Text, “‘”, “””) & “‘, “ & “‘” & _
Replace$(txtState.Text, “‘”, “””) & “‘, “ & “‘” & _
Replace$(txtZip.Text, “‘”, “””) & “‘” & “)”
‘ Execute the statement.
Conn.Execute SQL_Str, , adCmdText
‘ Close the connection.
Conn.Close

‘ Clear the TextBoxes.

For Each ctl In Controls
 If TypeOf ctl Is TextBox Then
 ctl.Text = “”
 End If
Next ctl

End Sub

How to Import SQL Server data into an MS Access Database

0 comments

Posted on 1st July 2011 by Admin in Database Programming |Visual Basic 6

, ,

‘Import SQL Server data into an Access database
‘You can quickly import SQL Server data into an Access database using an
‘Execute statement in either ADO or DAO

Private Sub Import_Data()

Dim Conn As ADODB.Connection
 Dim SQL_STR As String
 ‘Create ADO Connection Object
 Set Conn = New ADODB.Connection
 Conn.Open “Driver={Microsoft Access Driver (*.mdb)};” & “Dbq=ExportedData.mdb;” & _
 “DefaultDir=d:Temp;” & “Uid=Admin;Pwd=;” SQL_STR = “SELECT * INTO “ & “[table_data] “ & “FROM “ & “[ODBC;Driver=SQL Server; " & _
 "SERVER=ServerName;DATABASE=MyTable;" & "UID=MyUser;PWD=MyPassword;].” & _
 “[table_data];”
 Conn.Execute SQL_STR
Set Conn = Nothing

End Sub

‘Note : Please change the following :

‘ServerName = Your SQL Server Name or Address
‘UID = Your username to access the database
‘PWD = Your database password

‘you must create a temporary folder in drive c:, please see sample below
‘c:tempExportedData.mdb

Remote Connection Setup of SQL Server 2005 Express or SQL Server 2005 Database

0 comments

Posted on 29th June 2011 by Admin in Database Programming |Visual Basic .Net

,

Microsoft SQL Server 2005 or Microsoft SQL Server 2005 Express does not allow remote access by default. If you want to remote connect or use another computer in your LAN or network, you must configure on your SQL Server 2005 or the SQL Server 2005 Express.

If you don’t change the settings of the SQL server for remote connection, you may receive an error message.

Sqlcmd: Error: Microsoft SQL Native Client: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow

Enable remote connections for SQL Server 2005 Express or SQL Server 2005 Developer Edition

To enable remote connections on the instance of SQL Server 2005 Express, please follow the steps below.

1. Locate your Microsoft SQL Server 2005 program on the Start menu on your computer, point to Configuration Tools, and then you will see the menu item SQL Server Surface Area Configuration (please check image below)

2. Click SQL Server Surface Area Configuration, and then click Surface Area Configuration for Services and Connections.

3. In Database Engine menu, please select the Remote connections and by default the SQL Server 2005 connection type is Local Connections only, please select the Local and Remote connections and select Using both TCP/IP and Name Pipes. Based on my experience, It’s better to use both TCP/IP and Name Pipes as you can see the image below because during the configuration of your ODBC you can use these type TCP/IP or Name Pipes to connect the SQL Server 2005 database.

4. Please Click to confirm the database setting changes.

5. Return to the Surface Area Configuration for Services and Connections page, expand Database Engine, select Service, On the right side, click Stop button, then wait until the MSSQLSERVER (MSSQL$SQLEXPRESS2005) service stops, and then click Start button to restart the MSSQLSERVER (MSSQL$SQLEXPRESS2005) service.

0 comments

Posted on 20th March 2011 by reyman in Database Programming |Visual Basic .Net |Visual Basic 2008

DataGridView Samples

It provides a powerful and flexible way to display a data source. You can extend the datagrid control in a number of ways to build custom behaviors into your applications. Although you can create your own types of cells the Windows Forms DataGridView control is mostly column-based, rather than cell-based.

DataGridView

The DataGridView control is the new grid control for Windows Forms 2.0. upgraded from the previous version of datagrid.

Sample code on how to bind the datagridview in Visual Basic .Net

Imports System.Data.SqlClient
Public Class Form1
Private Const ConnectionString As String = “Server=.SQLEXPRESS;” & _
“Database=NorthWind;Trusted_Connection=True”
Private ReadOnly Property Connection() As SqlConnection
Get
Dim ConnectionToFetch As New SqlConnection(ConnectionString)
ConnectionToFetch.Open()
Return ConnectionToFetch
End Get
End Property
Public Function GetData() As DataView
Dim SelectQry = “SELECT * FROM Products ”
Dim SampleSource As New DataSet
Dim TableView As DataView
Try
Dim SampleCommand As New SqlCommand()
Dim SampleDataAdapter = New SqlDataAdapter()
SampleCommand.CommandText = SelectQry
SampleCommand.Connection = Connection
SampleDataAdapter.SelectCommand = SampleCommand
SampleDataAdapter.Fill(SampleSource)
TableView = SampleSource.Tables(0).DefaultView
Catch ex As Exception
Throw ex
End Try
Return TableView
End Function

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
DataGridView1.DataSource = GetData()
End Sub
End Class

How to avoid Error Invalid Use of Null

0 comments

Posted on 7th May 2010 by Admin in Database Programming |Visual Basic 6

, , ,

How to avoid error Invalid use of null in Visual Basic 6
How to Use Do While orSequential reading in database

'Declaration
ConstmCon ="Provider=MSDASQL.1;Persist Security Info=False;Data Source=ipayroll"
'Note : Please change the connection string to make it work, this is my sample connection string in payroll system I developed.
Sub Load_Employee()
On Error GoTo errHandler
Dim rs1 As New ADODB.Recordset
Dim mSQL As String
cboName.Clear
With rs1
mSQL = "Select FirstName,FirstName,MI From Employee Where Status <> 'Inactive' order by LastName"
.Open mSQL, mCon, adOpenDynamic, adLockReadOnly
Do While Not .EOF
If (not isnull(!LastName) or !LastName <> "") And(not isnull(!FirstName) or !FirstName <> "")then
Ifnot isnull(!MI) or !MI <> "" then 'sometime Middle name is optional
cboName.AddItem !LastName & "," & !FirstName & " " & Right(!MI,1) & "."
Else 'Middle name is null or blank
cboName.AddItem !LastName & "," & !FirstName
End If
End if
.MoveNext 'Move point to next record
Loop
.Close 'Close database after using
End With
Exit Sub
errHandler:
MsgBox Err.Number & Err.Description
End Sub
« Older Entries