• (2 Pages)
  • +
  • 1
  • 2

OleDb Basics in VB.Net Rate Topic: ----- 1 Votes

#1 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1632
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Post icon  Posted 24 September 2007 - 07:53 AM

In todays tutorial we will be discussing the basics of interacting with Access (or any other than require the OleDb Objects) in VB.Net. These code ideas will be from a Windows Application, but with some (if any) modifications they can be used in a VB.Net Web Application. All the function in this tutorial are Public Shared because I always put them in their own class file, this way I can reference the class file itself, and not have to create a new instance of it.

If your project don't already have one add an app.config file, in it you will be storing your connection string information for later retrieval. Your connection should look like this:

< connectionStrings>
	<add name="YourConnectionName"
   connectionString="Persist Security Info=False;
   Data Source=YourDatabase;
   Initial Catalog=YourTable;
   Integrated Security=SSPI;
   Trusted_Connection=TRUE;
   Application Name=SampleVBNetApplication"
   providerName="System.Data.OleDb" />
  </connectionStrings>



We will be referencing this connectionString in our next code snippet. Add a new Class and name it DataAccess, then at the top of you class add this Imports statement

Imports System.Data.OleDb



This is needed for working with OleDb Objects in the .Net Framework. The first code you will add to this class is a method to retrieve the connectionString from the app.config file. The method is GetConnectionString, and it includes this code:

''' <summary>
''' Function to retrieve the connection from the app.config
''' </summary>
''' <param name="conName">Name of the connectionString to retrieve</param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function GetConnectionString(ByVal conName As String) As String
	 'variable to hold our connection string for returning it
	 Dim strReturn As New String("")
	 'check to see if the user provided a connection string name
	 'this is for if your application has more than one connection string
	 If Not String.IsNullOrEmpty(conName) Then
		 'a connection string name was provided
		 'get the connection string by the name provided
		  strReturn = ConfigurationManager.ConnectionStrings(conName).ConnectionString
	 Else
		 'no connection string name was provided
		 'get the default connection string
		 strReturn = ConfigurationManager.ConnectionStrings("YourConnectionName").ConnectionString
	 End If
	 'return the connection string to the calling method
	 Return strReturn
End Function



Now, this method does several things:
  • Checks to see if a connection name was provided

  • If one was provided get the connection information for that name

  • If one wasnt provided it returns the default connection information (as set by you the developer)

  • Return the connection information to the calling method

Most people are retrieving data for populating a control, whether it be a DataGrid, A DataRepeater, etc. The easiest way I've found to populate these controls is with a BindingSource. According to the MSDN, this is the definition of a Binding Source:

Quote

The BindingSource component serves many purposes. First, it simplifies binding controls on a form to data by providing currency management, change notification, and other services between Windows Forms controls and data sources. This is accomplished by attaching the BindingSource component to your data source using the DataSource property.

More: BindingSource


So to retrieve a BindingSource for a Data control I use a function that I pass a OleDbCommand Object to it, it then executes that OleDbCommand Object and puts the returned data into a BindingSource Object for me, making it easy to bind to a control. This function looks like this:

''' <summary>
''' Returns a BindingSource, which is used with, for example, a DataGridView control
''' </summary>
''' <param name="cmd">"pre-Loaded" command, ready to be executed</param>
''' <returns>BindingSource</returns>
''' <remarks>Use this function to ease populating controls that use a BindingSource</remarks>
Public Shared Function GetBindingSource(ByVal cmd As OleDbCommand) As BindingSource
	'declare our binding source
	Dim oBindingSource As New BindingSource()
	' Create a new data adapter based on the specified query.
	Dim daGet As New OleDbDataAdapter(cmd)
	' Populate a new data table and bind it to the BindingSource.
	Dim dtGet As New DataTable()
	'set the timeout of the OleDbCommandObject
	cmd.CommandTimeout = 240
	dtGet.Locale = System.Globalization.CultureInfo.InvariantCulture
	Try
		'fill the DataTable with the OleDbDataAdapter
		 daGet.Fill(dtGet)
	Catch ex As Exception
		'check for errors
		MsgBox(ex.Message, "Error in GetBindingSource")
		Return Nothing
	End Try
	'set the DataSource for the BindingSource to the DataTable
	oBindingSource.DataSource = dtGet
	'return the BindingSource to the calling method or control
	Return oBindingSource
End Function



Heres how the GetBindingSource method works:
  • Pass the method your OleDbCommand Object from your method (this is already created and "pre-loaded" when you pass it)

  • Create a OleDbDataAdapter Object based on this OleDbCommand Object

  • Create and fill a DataTable Object with the OleDbDataAdapter (this executes your OleDbCommand Object)

  • Set the DataSource property of your BindingSource Object to you now filled DataTable Object

  • Return the BindingSource Object to the calling method for binding

The last thing to add to your DataAccess class is a way to open and close the OleDbConnection based on its current state. I created a method called HandleConnection, and you pass a OleDbConnection then the method checks the status of the connection and acts accordingly. This procedure looks like this:

''' <summary>
''' Method for handling the ConnectionState of 
''' the connection object passed to it
''' </summary>
''' <param name="conn">The OleDbConnection Object</param>
''' <remarks></remarks>
Public Shared Sub HandleConnection(ByVal conn As OleDbConnection)
	With conn
		'do a switch on the state of the connection
		Select Case .State
			Case ConnectionState.Open
			   'the connection is open
			   'close then re-open
			   .Close()
			   .Open()
			   Exit Select
		  Case ConnectionState.Closed
			   'connection is open
			   'open the connection
			  .Open()
			  Exit Select
		 Case Else
			 .Close()
			 .Open()
			 Exit Select
		 End Select
	End With
End Sub



This covers 3 important methods, and also saves a lot of coding down the road. For the next part you can either keep them in the DataAccess Class (my choice) or put it right in your form .vb file.

First I will show you how to insert a new record into a table. For this I create a Public Shared Function that returns a boolean value (True for succeeded, False for failed). Always use a Try...Catch block to help catch and deal with any errors that may occur, especially when working with a database.

In this function we pass the values we want inserted into our table (I only have 3 in this example, there can be as many as you need though). We then create the objects we need for this, OleDbCommand, OleDbConnection and a String variable query. We set the query variable to the query we want to execute (or the inline SQL if you go that route), then we set the properties of our OleDbCommand Object.

Once this is complete we open the connection to the database, we use ExecuteNonQuery to insert the values. ExecuteNonQuery is what you use when you're not returning a value from the database. Once we complete the insert we check the status and act accordingly, then we close the connection. Here is more on the objects we created
  • A OleDbCommand Object -> This will perform your query execution

  • A OleDbConnection -> This will be used to communicate with the database

  • A String (query) -> This will hold the name of your query

Now to the function:

Public Shared Function InsertNewRecord(ByVal item1 As String, ByVal item2 As String, ByVal item3 As String) As Boolean
	'Create the objects we need to insert a new record
	Dim cnInsert As New OleDbConnection(GetConnectionString("YourConnName"))
	Dim cmdInsert As New OleDbCommand
	Dim query As String = "INSERT INTO YourTable(column1,column2,column3) VALUES(@item1,@item2,@item3)"
	Dim iSqlStatus As Integer

	'Clear any parameters
	cmdInsert.Parameters.Clear()
	Try
	   'Set the OleDbCommand Object Properties
	   With cmdInsert
		  'Tell it what to execute
		  .CommandText = query 
		  'Tell it its a text query
		  .CommandType = CommandType.Text 
		  'Now add the parameters to our query
		  'NOTE: Replace @value1.... with your parameter names in your query
		  'and add all your parameters in this fashion
		  .Parameters.AddWithValue("@value1", item1)
		  .Parameters.AddWithValue("@value2", item2)
		  .Parameters.AddWithValue("@value3", item3)
		  'Set the connection of the object
		  .Connection = cnInsert
	  End With

	  'Now take care of the connection
	  HandleConnection(cnInsert)

	  'Set the iSqlStatus to the ExecuteNonQuery 
	  'status of the insert (0 = failed, 1 = success)
	  iSqlStatus = cmdInsert.ExecuteNonQuery

	  'Now check the status
	  If Not iSqlStatus = 0 Then
		  'DO your failed messaging here
		  Return False
	  Else
		 'Do your success work here
		  Return True
	  End If
	Catch ex As Exception
	   MsgBox(ex.Message, "Error")
	Finally
		'Now close the connection
		HandleConnection(cnInsert)
	End Try
End Function



Next we will look at updating a record in the database. In this function we pass the values of the record we want to update, along with the id of the record to update (I only have 2 values in this example but you can have more in yours). We then create the objects we need for this, OleDbCommand, OleDbConnection and a String variable query. We set the query variable to the query we want to execute, then we set the properties of our OleDbCommand Object.

Once this is complete we open the connection to the database, we use ExecuteNonQuery to insert the values. ExecuteNonQuery is what you use when you're not returning a value from the database. Once we complete the insert we check the status and act accordingly, then we close the connection. Here is the function:

Public Shared Function UpdateRecord(ByVal item1 As String, ByVal item2 As String, ByVal id As Integer) As Boolean
	'Create the objects we need to insert a new record
	Dim cnUpdate As New OleDbConnection(GetConnectionString("YourConnName"))
	Dim cmdUpdate As New OleDbCommand
	Dim query As String = "UPDATE YourTable SET column1 = @item1,column2 = @item2 WHERE YourId = @id"
	Dim iSqlStatus As Integer

	'Clear any parameters
	cmdUpdate.Parameters.Clear()
	Try
		'Set the OleDbCommand Object Properties
		With cmdUpdate
			'Tell it what to execute
			.CommandText = query 
			'Tell it its a text query
			.CommandType = CommandType.Text
			'Now add the parameters to our query
			'NOTE: Replace @value1.... with your parameter names in your query
			'and add all your parameters in this fashion
			.Parameters.AddWithValue("@value1", item1)
			.Parameters.AddWithValue("@value2", item2)
			.Parameters.AddWithValue("@YourID", id)
			'Set the connection of the object
			.Connection = cnUpdate
		End With

		'Now take care of the connection
		HandleConnection(cnUpdate)

		'Set the iSqlStatus to the ExecuteNonQuery 
		'status of the insert (0 = success, 1 = failed)
		iSqlStatus = cmdUpdate.ExecuteNonQuery

		'Now check the status
		If Not iSqlStatus = 0 Then
			'DO your failed messaging here
			Return False
		Else
			'Do your success work here
			Return True
		End If
	Catch ex As Exception
		MsgBox(ex.Message, "Error")
	Finally
		'Now close the connection
		HandleConnection(cnUpdate)
	End Try
End Function



Next we will look at deleting a record from a table. In this function we pass the id of the record we want to delete. We then create the objects we need for this, OleDbCommand, OleDbConnection and a String variable query. We set the query variable to the query we want to execute, then we set the properties of our OleDbCommand Object.

Once this is complete we open the connection to the database, we use ExecuteNonQuery to insert the values. ExecuteNonQuery is what you use when you're not returning a value from the database. Once we complete the insert we check the status and act accordingly, then we close the connection. Here is the function:

Public Shared Function DeleteRecord(ByVal id As Integer) As Boolean
	'Create the objects we need to insert a new record
	Dim cnDelete As New OleDbConnection(GetConnectionString("YourConnName"))
	Dim cmdDelete As New OleDbCommand
	Dim query As String = "DELETE FROM YourTable WHERE YourID = @id"
	Dim iSqlStatus As Integer

	'Clear any parameters
	cmdDelete.Parameters.Clear()
	Try
		'Set the OleDbCommand Object Properties
		With cmdDelete
			'Tell it what to execute
			.CommandText = query 
			'Tell it its a text query
			.CommandType = CommandType.Text
			'Now add the parameters to our query
			'NOTE: Replace @value1.... with your parameter names in your query
			'and add all your parameters in this fashion
			.Parameters.AddWithValue("@YourID", id)
			'Set the connection of the object
			.Connection = cnDelete
		End With

		'Now take care of the connection
		HandleConnection(cnDelete)

		'Set the iSqlStatus to the ExecuteNonQuery 
		'status of the insert (0 = success, 1 = failed)
		iSqlStatus = cmdDelete.ExecuteNonQuery

		'Now check the status
		If Not iSqlStatus = 0 Then
			'DO your failed messaging here
			Return False
		Else
			'Do your success work here
			Return True
		End If
	Catch ex As Exception
		MsgBox(ex.Message, "Error")
		Return False
	Finally
		'Now close the connection
		HandleConnection(cnDelete)
	End Try
End Function



So far we have covered:
  • Retrieving the connectionString from the app.config

  • Retrieving a BindingSource for binding our data to a control

  • A method to open and close our connection

  • Inserting a new record into the database

  • Updating a record in the database

  • Deleting a record from a database

Next we will look at a basic retrieval of data from the database. This method will return a populated BindingSource so you can bind it to your data control and display your data. A SQL no parameters is the simplest database query, but lets say you want to filter your data by only returning certain data that falls into a certain category, you would then need parameters in your query. First lets take a look at returning all the data in the table, this function looks like this:

Public Shared Function GetRecords() As BindingSource
	'The value that will be passed to the Command Object (this is a query)
	Dim query As String = "SELECT * FROM YourTableName"
	Dim cnGetRecords As New OleDbConnection(GetConnectionString("YourConnectionName"))
	'OleDbConnection Object to use
	Dim cmdGetRecords As New OleDbCommand()
	'OleDbCommand Object to use
	Dim daGetRecords As New OleDbDataAdapter()
	Dim dsGetRecords As New DataSet()
	'Clear any parameters
	cmdGetRecords.Parameters.Clear()
	Try
		With cmdGetRecords
			'set the OleDbCommand Object Parameters
			.CommandText = query
			'tell it what to execute
			.CommandType = CommandType.Text
			'Set the Connection for the Command Object
			.Connection = cnGetRecords
		End With
		'set the state of the OleDbConnection Object
		HandleConnection(cnGetRecords)
		'create BindingSource to return for our DataGrid Control
		Dim oBindingSource As BindingSource = GetBindingSource(cmdGetRecords)
		'now check to make sure a BindingSource was returned
		If Not oBindingSource Is Nothing Then
			'return the binding source to the calling method
			Return oBindingSource
		Else
			'no binding source was returned
			'let the user know the error
			Throw New Exception("There was no BindingSource returned")
			Return Nothing
		End If
	Catch ex As Exception
		MsgBox(ex.Message, "Error Retrieving Data")
		Return Nothing
	Finally
		HandleConnection(cnGetRecords)
	End Try
End Function



Next we will look at retrieval of data based on a criteria, a filter if you will. This method will return a populated BindingSource so you can bind it to your data control and display your data. A query with no parameters is the simplest database query, but lets say you want to filter your data by only returning certain data that falls into a certain category, you would then need parameters in your query. This function looks like this:

Public Shared Function GetRecordsByID(ByVal value As Integer) As BindingSource
	'The value that will be passed to the Command Object (this is a query)
	Dim query As String = "SELECT value1,value2,value3 FROM YourTable WHERE YourValue = @value"
	Dim cnGetRecords As New OleDbConnection(GetConnectionString("YourConnectionName"))
	'OleDbConnection Object to use
	Dim cmdGetRecords As New OleDbCommand()
	'OleDbCommand Object to use
	Dim daGetRecords As New OleDbDataAdapter()
	Dim dsGetRecords As New DataSet()
	'Clear any parameters
	cmdGetRecords.Parameters.Clear()
	Try
		With cmdGetRecords
			'set the OleDbCommand Object Parameters
			.CommandText = query
			'tell it what to execute
			.CommandType = CommandType.Text
			'tell it its executing a text query
			'heres the difference from the last method
			'here we are adding a parameter to send to our query
			'you use the AddWithValue, then the name of the parameter in your query
			'then the variable that holds that value
			.Parameters.AddWithValue("@id", value)
			'Set the Connection for the Command Object
			.Connection = cnGetRecords
		End With
		'set the state of the OleDbConnection Object
		HandleConnection(cnGetRecords)
		'create BindingSource to return for our DataGrid Control
		Dim oBindingSource As BindingSource = GetBindingSource(cmdGetRecords)
		'now check to make sure a BindingSource was returned
		If Not oBindingSource Is Nothing Then
			'return the binding source to the calling method
			Return oBindingSource
		Else
			'no binding source was returned
			'let the user know the error
			Throw New Exception("There was no BindingSource returned")
			Return Nothing
		End If
	Catch ex As Exception
		MsgBox(ex.Message, "Error Retrieving Data")
		Return Nothing
	Finally
		'Close the connection
		HandleConnection(cnGetRecords)
	End Try
End Function



There is essentially one difference in this method and the previous one, its the line cmdGetRecords.Parameters.AddWithValue("@id", value). The AddWithValue accepts 2 parameters:
  • The parameter name in your query (for this example the name is @year)

  • The variable that is holding that value (in this case we pass it with the signature of the method -> value)

Now to use these methods in your form for binding a DataGridView to the data use this

'for binding to returning all the records
DataGridView1.DataSource = DirectCast(GetAllRecords(), BindingSource)

'for binding to to returning certain records
DataGridView1.DataSource = DirectCast(GetRecordsByID(DirectCast(TextBox1.Text, Integer)), BindingSource)




Her are the objects we used during this tutorial
If you have any questions please post them here so I can asnwer the question for you and anyone else who might have the same questions. Thanks for reading :)

Happy Coding!

Is This A Good Question/Topic? 1
  • +

Replies To: OleDb Basics in VB.Net

#2 ThomasF  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 12-May 08

Posted 12 May 2008 - 04:13 AM

Hi PsychoCoder!
First of all many thanks for your tutorial!
How would a function look like where we only expect one value back and want to feed that into a variable.
A "select count(*)" statement, e.g.?
Many thanks in advance!
Was This Post Helpful? 0
  • +
  • -

#3 shavian  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 08-August 08

Posted 10 January 2009 - 11:00 AM

I am using 2005 Express Edition and have successfully added an SQL database to my application.

The app.config looks OK and Imports System.Data.OleDb is present.

I am now following this tutorial so I added a new Class DataAccess but after I pasted the Public Shared Function GetConnectionString I get

Name 'ConfigurationManager' is not declared

Please advise.

Many thanks
Was This Post Helpful? 0
  • +
  • -

#4 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1632
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Posted 10 January 2009 - 11:20 AM

You need to add a reference to the System.Configuration Namespace (sorry, I thought I went back in and added that so I apologize). To add this reference click "Project" on the menu bar (or "Website" I believe if this is a web application), then select "Add Reference", once the dialog opens scroll down (in the .Net Tab) until you see "System.Configuration", highlight that and hit OK. Then at the top of your code add the correct Import statement

Imports System.Configuration



Hope that helps :)
Was This Post Helpful? 0
  • +
  • -

#5 shavian  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 08-August 08

Posted 18 January 2009 - 08:48 AM

Yes it sorted out the problem.

I will continue using the tutorial now that I have returned from the slopes of Italy!
Was This Post Helpful? 0
  • +
  • -

#6 shavian  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 08-August 08

Posted 19 January 2009 - 01:41 PM

Now I'm a little confused about the HandleConnection method.

In the Select statement, if the connection is open, it is closed then re-opened.

However, in the functions you comment "..... Finally now close the connection.

Why is the connection re-opened in HandleConnection?
Was This Post Helpful? 0
  • +
  • -

#7 shotos  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 02-March 09

Posted 04 March 2009 - 01:28 AM

i have read but still do not understand why my code is not working
this is what i have done

Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Dim intaccount As Integer
Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects\Account\accraeast_november.mdb")
'provider to be used when working with access database
cn.Open()
cmd = New OleDbCommand("SELECT Identity, Name, District FROM NorthEast_DB WHERE identity = '" & intaccount & "'", cn)
'cmd.ExecuteNonQuery()

dr = cmd.ExecuteReader
If dr.Read = True Then
Me.txtaccount = dr(0)
Me.txtname = dr(1)
Me.txtdistrict = dr(2)
' loading data into TextBoxes by column index
Else
MessageBox.Show("THIS MEMBER DOES NOT EXIST")

End If

Catch

End Try
dr.Close()this is what i have done but it is not working
it is not a function, i just want to get data from access data base into the text box using a parameter identity
Was This Post Helpful? 0
  • +
  • -

#8 1091100582  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 118
  • Joined: 15-August 09

Posted 21 August 2009 - 04:27 AM

may i ask ,do any hav the pure program example for this types of coding??
Was This Post Helpful? 0
  • +
  • -

#9 TimmieTheGeek  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 05-November 09

Posted 05 November 2009 - 02:01 PM

I am getting an error on the GetConnectionString function call. I believe it has to do with my connection string and/or parameters. Here's what I have:

[code] <connectionStrings>
<add name="MyConnectionName"
connectionString="Persist Security Info=False;
Data Source=\\Serv06\Information Systems\Visual Studio Projects\CustomerQuoteSystem\bin\CustomerInformation.accdb;
Initial Catalog=CustomerInformation;
Integrated Security=SSPI;
Trusted_Connection=TRUE;
Application Name=TestApp"
providerName="Microsoft.ACE.OLEDB.12.0" />
</connectionStrings>[code]

And here is the call to GetConnectionString:

[code]'The value that will be passed to the Command Object (this is a query)
Dim query As String = "SELECT * FROM CustomerInformation"
Dim cnGetRecords As New OleDbConnection(GetConnectionString("MyConnectionName"))[code]

I've attached a screen print of the error, which said

An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.

I believe this is caused by me stating the connection info incorrectly, but I am at a loss to figure out what I need to change to make it work. I am running Visual Studio 2008 against an Access 2007 database.

THanks for your help. Great code.

Attached File(s)


Was This Post Helpful? 0
  • +
  • -

#10 mihaispr  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 114
  • Joined: 29-September 09

Posted 15 November 2009 - 02:43 PM

Great tutorial PsychoCoder!

:^:

I have a question:

How I can search for a field in database (the user types in textboxes and it searches in the database. If it finds it adds to a listview control. If not it displays an error message or errordialog.

I've started here a topic.

Hope it's clear what I'm intending to do I put printscreens and explain the little thing I want to do.

http://www.dreaminco...topic139195.htm


I posted this because it's a similar problem (linking sql server with vb.net. I'm using visual studio 2008).
Was This Post Helpful? 0
  • +
  • -

#11 rlalande  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 14-September 09

Posted 05 April 2010 - 12:11 PM

PsychoCoder,

In your InsertNewRecord function, where you set
code]iSqlStatus = cmdInsert.ExecuteNonQuery

even though my records are being successfully added to the database, iSqlStatus is always set to 0 and my "Record was not added"  message always comes up.  Here is my code:
[code]
    Public Shared Function insertEmployee(ByVal intEmpID As Integer, ByVal strFName As String, ByVal strLName As String, _
                                 ByVal strLogin As String, ByVal strPassword As String, ByVal bolIsAdmin As Boolean, _
                                 ByVal bolChangePW As Boolean, ByVal bolIsActive As Boolean) As Boolean
        'Create the objects we need to insert a new record
        Dim cnInsert As New OleDbConnection(GetConnectionString("MfctBuilder.My.MySettings.MfctOleDB"))
        Dim cmdInsert As New OleDbCommand
        Dim sSQL As New String("")
        Dim iSqlStatus As Integer

        'Set the stored procedure we're going to execute
        'sSQL = "YourProcName"

        'Inline sql needs to be structured like so
        sSQL = "INSERT INTO EMPLOYEES(EMPID, FNAME, LNAME, LOGIN, PASSWORD, ISADMIN, CHANGEPW, ISACTIVE) " & _
                              "VALUES(?, ?, ?, ?, ?, ?, ?, ?)"
        'Clear any parameters
        cmdInsert.Parameters.Clear()
        Try
            'Set the Odbc.OdbcCommand Object Properties
            With cmdInsert
                'Tell it what to execute
                .CommandText = sSQL 'Your sql statement if using inline sql
                'Tell it its a stored procedure
                .CommandType = CommandType.Text 'CommandType.StoredProcedure for stored procedure
                'If you are indeed using a stored procedure
                'the next 3 lines pertain to you
                'Now add the parameters to our procedure
                .Parameters.AddWithValue("EMPID", intEmpID)
                .Parameters.AddWithValue("FNAME", strFName)
                .Parameters.AddWithValue("LNAME", strLName)
                .Parameters.AddWithValue("LOGIN", strLogin)
                .Parameters.AddWithValue("PASSWORD", strPassword)
                .Parameters.AddWithValue("ISADMIN", bolIsAdmin)
                .Parameters.AddWithValue("CHANGEPW", bolChangePW)
                .Parameters.AddWithValue("ISACTIVE", bolIsActive)
                'Set the connection of the object
                .Connection = cnInsert
            End With

            'Now take care of the connection
            HandleConnection(cnInsert)

            'Set the iSqlStatus to the ExecuteNonQuery status of the insert (0 = success, 1 = failed)
            iSqlStatus = cmdInsert.ExecuteNonQuery

            'Now check the status
            If Not iSqlStatus = 0 Then
                'DO your failed messaging here
                MessageBox.Show("Record was not added to the database.  Contact your Computer administrator", _
                                "Error", _
                                MessageBoxButtons.OK, MessageBoxIcon.Stop)
                Return False
            Else
                'Do your success work here
                MessageBox.Show("Record was successfully added to the database.", _
                                "Error", _
                                MessageBoxButtons.OK, MessageBoxIcon.Information)
                Return True
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error")
        Finally
            'Now close the connection
            HandleConnection(cnInsert)
        End Try



Any idea as to why iSqlStatus is not set to 1 even though the insertion was successful?
Was This Post Helpful? 0
  • +
  • -

#12 pro08  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 06-August 10

Posted 23 October 2010 - 12:19 PM

Dear anyone

This is the first time I use connection between databases and asp.net
I create my first code for that but I still confused for some parameters and properties like(connection name,connection string, and configuration). What they are exactly meaning? , Is there any tutorials about database connections with .net3.5 framework(for fast learning).

My Code still have some errors. Its function just to add records for Ms access database.
Imports System.Data.OleDb
Imports System.Configuration


Partial Class _Default
    Inherits System.Web.UI.Page


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub
    ''' <summary>
    ''' Function to retrieve the connection from the app.config
    ''' </summary>
    ''' <param name="conName">Name of the connectionString to retrieve</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetConnectionString(ByVal conName As String) As String
        'variable to hold our connection string for returning it
        conName = <connectionStrings>
                      <add name="fatin"
                          connectionString="Persist Security Info=False;
   Data Source=Client.mdb;
   Initial Catalog=RgInfo;
   Integrated Security=SSPI;
   Trusted_Connection=TRUE;
   Application Name=SampleVBNetApplication"
                          providerName="System.Data.OleDb"/>
                  </connectionStrings>




        Dim strReturn As New String("")
        'check to see if the user provided a connection string name
        'this is for if your application has more than one connection string
        If Not String.IsNullOrEmpty(conName) Then
            'a connection string name was provided
            'get the connection string by the name provided
            strReturn = ConfigurationManager.ConnectionStrings("fatin").ConnectionString
        Else
            'no connection string name was provided
            'get the default connection string
            strReturn = ConfigurationManager.ConnectionStrings("fatin").ConnectionString
        End If
        'return the connection string to the calling method
        Return strReturn
    End Function
    Public Shared Function InsertNewRecord() As Boolean
        'Create the objects we need to insert a new record
        Dim cnInsert As New OleDbConnection(GetConnectionString("Client.mdb"))
        Dim cmdInsert As New OleDbCommand
        Dim query As String = "INSERT INTO RegInfo(Name,Age,Password,Email) VALUES(x,x,@item3,x)"
        Dim iSqlStatus As Integer

        'Clear any parameters
        cmdInsert.Parameters.Clear()
        Try
            'Set the OleDbCommand Object Properties
            With cmdInsert
                'Tell it what to execute
                .CommandText = query
                'Tell it its a text query
                ' .CommandType = Data.CommandType.Text
                'Now add the parameters to our query
                'NOTE: Replace @value1.... with your parameter names in your query
                'and add all your parameters in this fashion
                '.Parameters.AddWithValue(txtName.text, AccessDataSource)
                '.Parameters.AddWithValue(txtAge.text, item2)
                '.Parameters.AddWithValue("@value3", item3)
                'Set the connection of the object
                .Connection = cnInsert
            End With

            'Now take care of the connection
            HandleConnection(cnInsert)

            'Set the iSqlStatus to the ExecuteNonQuery 
            'status of the insert (0 = failed, 1 = success)
            iSqlStatus = cmdInsert.ExecuteNonQuery

            'Now check the status
            If Not iSqlStatus = 0 Then
                'DO your failed messaging here
                Return False
            Else
                'Do your success work here
                Return True
            End If
        Catch ex As Exception
            MsgBox(ex.Message, "Error")
        Finally
            'Now close the connection
            HandleConnection(cnInsert)
        End Try
    End Function




    ''' <summary>
    ''' Method for handling the ConnectionState of 
    ''' the connection object passed to it
    ''' </summary>
    ''' <param name="conn">The OleDbConnection Object</param>
    ''' <remarks></remarks>
    Public Shared Sub HandleConnection(ByVal conn As OleDbConnection)
        With conn
            'do a switch on the state of the connection
            Select Case .State
                Case Data.ConnectionState.Open
                    'the connection is open
                    'close then re-open
                    .Close()
                    .Open()
                    Exit Select
                Case Data.ConnectionState.Closed
                    'connection is open
                    'open the connection
                    .Open()
                    Exit Select
                Case Else
                    .Close()
                    .Open()
                    Exit Select
            End Select
        End With
    End Sub


    Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
        'Dim oledbconnection As  OleDbConnection(<connection string/> "provider=MicroSoft.jet.OLEDDB.4.0;" & "Data Source=..\Client.mdb;" & "persist Security Info=false"/></connection string>)
        InsertNewRecord()


    End Sub
End Class


Thanks
Was This Post Helpful? 0
  • +
  • -

#13 Mefyrx  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 11-April 11

Posted 11 April 2011 - 10:04 AM

Hi,

I' wondering why this is "byval" instead of "Byref"
Public Sub HandleConnection(Byval conn As OleDbConnection)

And yes i'm also wondering why:
Select Case .State
Case ConnectionState.Open
.Close()
.Open()

If it's open, i guess you need to close it as your calling your sub to finaly close the connection.... but wouldn't it be better to simply close the connection instead of calling the sub?...

Thanks
Was This Post Helpful? 0
  • +
  • -

#14 Kenzzo84  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 31-May 11

Posted 31 May 2011 - 06:54 AM

Good Day Folks

I just wanted to find about the add,delete and edit code. Something in the back of my mind tells me that i put that code in buttons. I need to have separate buttons that handle the add, delete and edit events.

How would i do that with the code in this tutorial.

I just hope this question abides by the rules of the forum

Thank you
Was This Post Helpful? 0
  • +
  • -

#15 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8371
  • View blog
  • Posts: 31,100
  • Joined: 12-June 08

Posted 31 May 2011 - 07:05 AM

@Kenzzo84 - events of any sort would work fine. Button click events, grid update events, toolbar button clicks, menu clicks, keyboard commands - what ever your choose.

You would use the functions provided, fill their input parameters, and call them from your whatever-event.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2