• (3 Pages)
  • +
  • 1
  • 2
  • 3

SQL Basics In VB.Net Rate Topic: ***** 1 Votes

#1 PsychoCoder  Icon User is offline

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

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

Posted 28 August 2007 - 09:29 PM

In todays tutorial we will be discussing the basics of interacting with SQL Server 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 dont 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:

NOTE: In the VB 2005 Express Edition (and I believe in 2008 as well) the option to add an app.config file to your project isnt available, so the next part doesnt apply to you.

< 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.SqlClient" />
  </ 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 Import statement

Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Configuration.ConfigurationSettings



NOTE: For the System.Configuration you're going to have to go to Project > Add Reference Then in the .NET tab select System.Configuration for the 2 Imports to work.


This is needed for working with SQL 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 ncludes this code:

NOTE: In the VB 2005 Express Edition (and I believe in 2008 as well) the option to add an app.config file to your project isnt available, you cannot use this functionality in your project.

''' <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 wasn't 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 SqlCommand Object to it, it then executes that SqlCommand 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 SqlCommand) 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 SqlDataAdapter(cmd)
	' Populate a new data table and bind it to the BindingSource.
	Dim dtGet As New DataTable()
	'set the timeout of the SqlCommandObject
	cmd.CommandTimeout = 240
	dtGet.Locale = System.Globalization.CultureInfo.InvariantCulture
	Try
		'fill the DataTable with the SqlDataAdapter
		 daGet.Fill(dtGet)
	Catch ex As Exception
		'check for errors
		MsgBox(ex.Message,MsgBoxStyle.OkOnly,"Error receiving data")
		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 SqlCommand Object from your method (this is already created and "pre-loaded" when you pass it)

  • Create a SqlDataAdapter Object based on this SqlCommand Object
  • Create and fill a DataTable Object with the SqlDataAdapter (this executes your SqlCommand 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 SqlConnection based on its current state. I created a method called HandleConnection, and you pass a SqlConnection 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 SqlConnection Object</param>
''' <remarks></remarks>
Public Shared Sub HandleConnection(ByVal conn As SqlConnection)
	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. I strongly suggest you follow my example and use Stored Procedures and not inline SQL.

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, SqlCommand, SqlConnection and a String variable sSQL. We set the sSQL variable to the stored procedure we want to execute (or the inline SQL if you go that route), then we set the properties of our SqlCommand 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 SqlCommand Object -> This will perform your query execution
  • A SqlConnection -> This will be used to communicate with the database
  • A String (sSQL) -> This will hold the name of your stored procedure
Now to the function:

Public Shared Function InsertNewRecord(ByVal item1 As String, ByVal item2 As String, ByVal item3 As String) As Boolean
	'If not using the Express Edition uncomment the next line
	Dim cnInsert As New SqlConnection(GetConnectionString("YourConnName"))
		'If using Express Edition uncomment the next line
		'Dim cnInset As New SqlConnection("YourConnectionStringHere")
	Dim cmdInsert As New SqlCommand
	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 YourTable(column1,column2,column3) VALUES('" & item1 & "','" & item2 & "','" & item3 & "')"

	'Clear any parameters
	cmdInsert.Parameters.Clear()
	Try
	   'Set the SqlCommand Object Properties
	   With cmdInsert
		  'Tell it what to execute
		  .CommandText = sSQL 'Your sql statement
		  'Tell it its a stored procedure (if using inline sql uncomment this line
		  '.CommandType = CommandType.StoredProcedure 'CommandType.Text for inline sql
				  'If you arent using a stored procedure uncomment the next line
				  '.CommandType = CommandType.StoredProcedure 'For inline sql
		  'If you are indeed using a stored procedure
		  'the next 3 lines pertain to you
		  'Now add the parameters to our procedure
		  'NOTE: Replace @value1.... with your parameter names in your stored procedure
		  '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 = success, 1 = failed)
	  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,MsgBoxStyle.OkOnly,"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, SqlCommand, SqlConnection and a String variable sSQL. We set the sSQL variable to the stored procedure we want to execute (or the inline SQL if you go that route), then we set the properties of our SqlCommand 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
	'If not using the Express Edition uncomment the next line
	Dim cnInsert As New SqlConnection(GetConnectionString("YourConnName"))
		'If using Express Edition uncomment the next line
		'Dim cnInset As New SqlConnection("YourConnectionStringHere")
	Dim cmdUpdate As New SqlCommand
	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 = "UPDATE YourTable SET column1 = '" & item1 & "',column2 = '" & item2 & "' WHERE YourId = " & id

	'Clear any parameters
	cmdUpdate.Parameters.Clear()
	Try
		'Set the SqlCommand Object Properties
		With cmdUpdate
			'Tell it what to execute
			.CommandText = sSQL 'Your sql statement 
			'Tell it its a stored procedure (if using inline sql uncomment this line
				 '.CommandType = CommandType.StoredProcedure 'CommandType.Text for inline sql
						'If you arent using a stored procedure uncomment the next line
						'.CommandType = CommandType.StoredProcedure 'For inline sql
			'If you are indeed using a stored procedure
			'the next 3 lines pertain to you
			'Now add the parameters to our procedure
			'NOTE: Replace @value1.... with your parameter names in your stored procedure
			'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,MessageBoxStyle.OkOnly, "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, SqlCommand, SqlConnection and a String variable sSQL. We set the sSQL variable to the stored procedure we want to execute (or the inline SQL if you go that route), then we set the properties of our SqlCommand 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
	'If not using the Express Edition uncomment the next line
	Dim cnInsert As New SqlConnection(GetConnectionString("YourConnName"))
		'If using Express Edition uncomment the next line
		'Dim cnInset As New SqlConnection("YourConnectionStringHere")
	Dim cmdDelete As New SqlCommand
	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 = "DELETE FROM YourTable WHERE YourID = " & id

	'Clear any parameters
	cmdDelete.Parameters.Clear()
	Try
		'Set the SqlCommand Object Properties
		With cmdDelete
			'Tell it what to execute
			.CommandText = sSQL 'Your sql statement if using inline sql
			  'Tell it its a stored procedure (if using inline sql uncomment this line
				  '.CommandType = CommandType.StoredProcedure 'CommandType.Text for inline sql
						 'If you arent using a stored procedure uncomment the next line
						 '.CommandType = CommandType.StoredProcedure 'For inline sql
			'If you are indeed using a stored procedure
			'the next 3 lines pertain to you
			'Now add the parameters to our procedure
			'NOTE: Replace @value1.... with your parameter names in your stored procedure
			'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,MessageBoxStyle.OkOnly, "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 return 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 stored procedure.

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 stored procedure)
	Dim sSQL As String = "YourProcName"
	'If using inline sql format is as such
	'sSQL = "SELECT * FROM YourTable
	'If not using the Express Edition uncomment the next line
	Dim cnInsert As New SqlConnection(GetConnectionString("YourConnName"))
		'If using Express Edition uncomment the next line
		'Dim cnInset As New SqlConnection("YourConnectionStringHere")
	'SqlConnection Object to use
	Dim cmdGetRecords As New SqlCommand()
	'SqlCommand Object to use
	Dim daGetRecords As New SqlDataAdapter()
	Dim dsGetRecords As New DataSet()
	'Clear any parameters
	cmdGetRecords.Parameters.Clear()
	Try
		With cmdGetRecords
			'set the SqlCommand Object Parameters
			.CommandText = sSQL
			  'Tell it its a stored procedure (if using inline sql uncomment this line
				 '.CommandType = CommandType.StoredProcedure 'CommandType.Text for inline sql
						 'If you arent using a stored procedure uncomment the next line
						 '.CommandType = CommandType.StoredProcedure 'For inline sql
			'Set the Connection for the Command Object
			.Connection = cnGetRecords
		End With
		'set the state of the SqlConnection 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,MessageBoxStyle.OkOnly, "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 SQL return 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 stored procedure. 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 stored procedure)
	Dim sSQL As String = "YourProcName"
	'If using inline sql format is as such
	'sSQL = "SELECT value1,value2,value3 FROM YourTable WHERE YourValue = " & value
	'If not using the Express Edition uncomment the next line
	Dim cnInsert As New SqlConnection(GetConnectionString("YourConnName"))
		'If using Express Edition uncomment the next line
		'Dim cnInset As New SqlConnection("YourConnectionStringHere")
	Dim cmdGetRecords As New SqlCommand()
	'SqlCommand Object to use
	Dim daGetRecords As New SqlDataAdapter()
	Dim dsGetRecords As New DataSet()
	'Clear any parameters
	cmdGetRecords.Parameters.Clear()
	Try
		With cmdGetRecords
			'set the SqlCommand Object Parameters
			.CommandText = sSQL
			  'Tell it its a stored procedure (if using inline sql uncomment this line
				'.CommandType = CommandType.StoredProcedure 'CommandType.Text for inline sql
						'If you arent using a stored procedure uncomment the next line
						'.CommandType = CommandType.StoredProcedure 'For inline sql
			'tell it its executing a Stored Procedure
			'heres the difference from the last method
			'here we are adding a parameter to send to our stored procedure
			'you use the AddWithValue, then the name of the parameter in your stored procedure
			'then the variable that holds that value
			.Parameters.AddWithValue("@year", value)
			'Set the Connection for the Command Object
			.Connection = cnGetRecords
		End With
		'set the state of the SqlConnection 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,MessageBoxStyle.OkOnly, "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("@year", year);. The

AddWithValue accepts 2 parameters:
  • The parameter name in your stored procedure (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 -> id)
NOTE: Always use stored procedure to prevent the risk of a SQ lInjection Attack

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)



That is the end of the basics of working with SQL Server in VB.Net. This should at least give you the basic tools for interacting with a SQL database in
your VB.Net application. One thing to remember, if you work with Access some things have to change:
  • SqlCommand -> OleDbCommand
  • SqlConnection -> OleDbConnection
  • SqlDataAdapter -> OleDbDataAdapter
  • using System.Data.SqlClient -> using System.Data.OleDb
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. I am attaching the DataAccess Class I created during the writing of this Tutorial.

Attached File  DataAccess.zip (2.66K)
Number of downloads: 8606

Thanks for reading :)

Happy Coding!

This post has been edited by PsychoCoder: 08 September 2007 - 12:22 PM


Is This A Good Question/Topic? 2
  • +

Replies To: SQL Basics In VB.Net

#2 webwired  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Posted 29 August 2007 - 07:25 AM

Awesome tutorial, very good instruction.

But the zip file is corrupted...
Was This Post Helpful? 0
  • +
  • -

#3 PsychoCoder  Icon User is offline

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

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

Posted 29 August 2007 - 08:54 AM

View Postwebwired, on 29 Aug, 2007 - 07:25 AM, said:

Awesome tutorial, very good instruction.

But the zip file is corrupted...


Ill re-upload it once I get home :)
Was This Post Helpful? 0
  • +
  • -

#4 PsychoCoder  Icon User is offline

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

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

Posted 29 August 2007 - 06:44 PM

webwired,

I re-uploaded the zip file, should be good now :)
Was This Post Helpful? 0
  • +
  • -

#5 enteng  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 56
  • Joined: 02-September 07

Posted 04 September 2007 - 03:09 AM

i downloaded your DataAccess.zip and unzipped it perfectly but my question is why there is a message "Name ConfigurationManager is not declared"? and also i put the code

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

in my Form1.vb it has an error of Declaration expected.

can you help me with this one?

thanks
Was This Post Helpful? 0
  • +
  • -

#6 PsychoCoder  Icon User is offline

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

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

Posted 04 September 2007 - 04:44 AM

View Postenteng, on 4 Sep, 2007 - 03:09 AM, said:

i downloaded your DataAccess.zip and unzipped it perfectly but my question is why there is a message "Name ConfigurationManager is not declared"? and also i put the code

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

in my Form1.vb it has an error of Declaration expected.

can you help me with this one?

thanks


You need to go to Project > Add Reference, then in the .Net tab put a check mark next to System.Configuration. As for part 2 of your post, if you kept the code in the DataAccess class then you need to use DataAccess.GetRecordByID, or DataAccess.AnyNameOfTheFunctions
Was This Post Helpful? 0
  • +
  • -

#7 webwired  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Posted 06 September 2007 - 10:01 PM

Quote

if you kept the code in the DataAccess class then you need to use DataAccess.GetRecordByID, or DataAccess.AnyNameOfTheFunctions


When I try
InventoryDataGridView.DataSource = DirectCast(DataAccess.GetRecords(), BindingSource)



I get this ... Name "DataAccess" is not declared. ... as a blue squiggly under DataAccess.

I did create a class file in the project and named it DataAccess and put in the contents of your DataAccess file.

I wanted to try out your tutorial, but I seen the previous post, so I thought I'd try it, but here I am... LOL
Was This Post Helpful? 0
  • +
  • -

#8 webwired  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Posted 07 September 2007 - 04:50 PM

I got it, ...

InventoryDataGridView.DataSource = DirectCast(PC.DataAccess.GetRecords(), BindingSource)


Was This Post Helpful? 0
  • +
  • -

#9 PsychoCoder  Icon User is offline

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

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

Posted 07 September 2007 - 05:15 PM

View Postwebwired, on 7 Sep, 2007 - 04:50 PM, said:

I got it, ...

InventoryDataGridView.DataSource = DirectCast(PC.DataAccess.GetRecords(), BindingSource)



Whoops, sorry I forgot about the Namespace :blush:
Was This Post Helpful? 0
  • +
  • -

#10 webwired  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Posted 07 September 2007 - 05:31 PM

Well, now that I have it accessed, I can't figure out why I am getting this error...

Object reference not set to an instance of an object.

on this line of code...

strReturn = ConfigurationManager.ConnectionStrings(conName).ConnectionString



when I hover over conName, it has the name of my connection, dbProjdbConn

Here's the code that I have in my app.config

	<connectionStrings>
		<add name="dbProjdbConn" 
	connectionString="Persist Security Info=False;
	Data Source=.\SQLEXPRESS;
	AttachDbFilename=C:\ProductSales.mdf;
	Integrated Security=SSPI;
	Trusted_Connection=TRUE;
	User Instance=True"
			providerName="System.Data.SqlClient" />
	</connectionStrings>


This post has been edited by webwired: 07 September 2007 - 05:34 PM

Was This Post Helpful? 0
  • +
  • -

#11 PsychoCoder  Icon User is offline

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

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

Posted 07 September 2007 - 05:48 PM

Ive seen this before (in fact it happened to me once) and I had to delete my app.config file and add a new one. Make sure to copy anything you have in it that you need to Notepad or something like that.
Was This Post Helpful? 0
  • +
  • -

#12 webwired  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Posted 07 September 2007 - 06:01 PM

I just copied everything inside, deleted the app.config file... pasted it all in a notepad and saved as app.config back into my project folder, checked the Solution Explorer to make sure it was there and tried to run it again... same thing.
Was This Post Helpful? 0
  • +
  • -

#13 PsychoCoder  Icon User is offline

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

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

Posted 07 September 2007 - 06:07 PM

You need to delete app.cong, right click on your solution choose Add > New Item, once the doalog opens scroll down and select Application Configuration File, you cant save a text file as app.config and have it work.
Was This Post Helpful? 0
  • +
  • -

#14 webwired  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 33
  • View blog
  • Posts: 339
  • Joined: 26-August 07

Posted 08 September 2007 - 10:01 AM

When I went to add new item, it was never an option, otherwise I would have done that... The only options that it gives for new items is, ...

Windows Form
Dialog
Explorer Form
MDI Parent Form
About Box
Login Form
Splash Screen
Class
Module
DataSet
SQL Database
User Control
Text File

I'm using Microsoft Visual Basic 2005 Express Edition, does that make a difference?
Was This Post Helpful? 0
  • +
  • -

#15 PsychoCoder  Icon User is offline

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

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

Posted 08 September 2007 - 10:26 AM

Since you're using the Express Edition and cannot add an app.config you're not going to be able to use the GetConnectionString Function, do it this way

Dim yourConn As New SqlConnection("Data Source=.\SQLExpress;Integrated Security=true;AttachDbFilename=|DataDirectory|\mydb.mdf;User Instance=true;")
'Do your other work here



I wasnt aware you couldnt use an app.config in the Express edition, so Ill need to make that note in the tutorial.
Was This Post Helpful? 0
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3