Error w/DB connection

[Rossy]

Hello, im new to VB.Net and im now in the process of trying to set up my first application to read/write to a MS Access database. So now i have a couple of q's. First my connection String

Dim CString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Brian\My Documents\Visual Studio Projects\adsfasd\Island_Delights.mdb;User Id=admin;Password=;"
Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand

Dim strinsert As String

myConnection.ConnectionString = CString

^
When it gets to this point, it gives me a null reference error and the program stops. Ive checked an i know that the the Access file is there so whats wrong? As a side note, someone told me that as long as the Access file is located in the same directory as the program the Connection string could be simplified? This would work well since i plan on doing that in the future.

My second question has to do with reading in data from the DB. Basically this is an address book type application. I would like to read in all of the last names from the database into a drop down list box to which i can simply scroll to the last name i want and then select it to view the full information. This would make it easy to select the person you are looking for. How would i go about doing this??

Thanks all

 

[kulrom]

Hi, take a look at this post http://www.vbdotnetforums.com/showpost.php?p=9638&postcount=2 feel free to ask if you have additional question/s i'd be glad to help you

Cheers

hah i forgot sorry ... welcome to the forum

 

[jmcilhinney]

On the subject of NullReferenceExceptions, you should always press the Break button on the Unhandled Exception dialogue and examine each variable that is refenced on that line. You have the Autos, Locals and Watch windows available to you to view variable values.

If you are new to .NET then you may not be familiar with object oriented programming. A variable whose type is a class does not actually hold the object. Rather, it holds a reference to the object. Declaring a variable like this:

[size=2][color=#0000ff]Dim[/color][/size][size=2] myConnection [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbConnection[/size]

simply says that myConnection is a variable that can refer to an OleDbConnection object. At this point myConnection is a null reference, as it does not actually refer to any particular object. You still need to create an object for it to refer to, which is called an instance of the class:

myConnection = New OleDbConnection

Note the use of the New keyword, which is the most common method for creating an instance of, or instantiating, a class. Now that you have an actual object you can use its properties and methods.

There is a help topic entitled "Value Types and Reference Types" that all those new to .NET and/or object oriented programming should read.

 

[Bharathi]

Re:Trying to set up my first application to read/write to a database

Hi,

The below code is extracted from the book Titled:"Database Programming using vb and sql server 2000".

Purpose :To retrieve data from the database using Data Reader and Data Commands. When this code is executed, the data in a database is displayed in two textbox controls.

Preparation:

Design a form using .NET environment and place two textbox controls on a form.
Design and create a table using SQL Server 2000.

Name the Database as FinAccounting.
Name the Table as AccountsTable.
Name the form as Form1
Name the controls on the form as Textbox1 and Textbox2.

Tasks:

1. Establish the connection with the Database using Connection object.
2. Execute the command.
3. The data will be read by the Datareader object and the contents of the first record is displayed in the textboxes.

Imports System.Data.SqlClient
Public Class Form1
Inherits System.Windows.Forms.Form
Dim str_sql_user_select As String = “SELECT * FROM AccountsTable”
Dim str_connection As String = “Data Source=VSDOTNET;Integrated Security=SSPI;Initial Catalog=FinAccounting”
Dim mycon As SqlConnection
Dim comUserSelect As SqlCommand
Dim myreader As SqlDataReader
 
Private Sub Form1_Load(ByVal sender As Object, ByVal e As system.EventArgs) Handles 
MyBase.Load
mycon = New SqlConnection(str_connection)
‘Instantiate the commands
comUserSelect = New SqlCommand(str_sql_user_select, mycon)
TextBox1.Text = “ “
TextBox2.Text = “ “
mycon.Open()
myreader = comUserSelect.ExecuteReader
If (myreader.Read = True) Then
TextBox1.Text = myreader(0)
TextBox2.Text = myreader(1)
Else
MsgBox(“You have reached eof”)
End If
End Sub
End Class

Good luck

Regards
bhar

 

[Rossy]

Thank you guys so much for all the help your giving to me. i can now connect to the db and ive come to another bump in the road. I would like to populate a drop down combo box with all the contact column in my database. This way a person can click the combo box and have a drop down of all the names and then select the one they want.
I've used Kulrom's code and tried to modify it to my form but the combo box only list one of the names in the DB. Thanks again all.

[SIZE=2][COLOR=#0000ff]Try
 
[/COLOR][/SIZE][SIZE=2]oledbcon.Open()
 
strSQL = "SELECT contact FROM Events_t ORDER BY Contact DESC"
 
cmd = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand(strSQL, oledbcon)
 
objRead = cmd.ExecuteReader
 
[/SIZE][SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE][SIZE=2] objRead.Read
 
[/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].cmbName.Text = objRead("Contact") & ""
 
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]While
 
[/COLOR][/SIZE][SIZE=2]objRead.Close()
 
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
 
MessageBox.Show(ex.Message)
 
[/SIZE][SIZE=2][COLOR=#0000ff]Finally
 
[/COLOR][/SIZE][SIZE=2]oledbcon.Close()
 
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
 
[/COLOR][/SIZE]

 

[kulrom]

change this line:
Me.cmbName.Text = objRead("Contact") & ""

to:

Me.cmbName.items.Add(objRead("Contact") & "")


Cheers

 

[Rossy]

wow, the response time from you guys is amazing and very informative. Thanks so much. Now im having another problem. I have a total of 9 columns in each row of the DB (ID, Event, Contact, Address, city, state, zip, Phone, and Directions). I am trying to display each of these columns into the corresponding textboxes. Basically i tried using the example from the code given in Bharathi's post but i guess im doing something wrong cause absolutely nothing happens when the code executes. Here is my code:

[SIZE=2]oledbcon.Open()
 
strSQL = "SELECT * FROM Events_t Where Event = '" & cmbName.SelectedText & "'"
 
cmd = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand(strSQL, oledbcon)
 
objRead = cmd.ExecuteReader
 
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] objRead.Read = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
 
[/COLOR][/SIZE][SIZE=2][SIZE=2]txtPhone.Text = objRead(1)
 
txtAddress.Text = objRead(2)
 
txtCity.Text = objRead(3)
 
txtState.Text = objRead(4)
 
txtZip.Text = objRead(5)
 
txtEvent.Text = objRead(6)
 
txtContact.Text = objRead(7)
 
txtDirections.Text = objRead(8)
 
[/SIZE][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
 
[/COLOR][/SIZE][SIZE=2]objRead.Close()

[/SIZE]

 

[kulrom]

Just be sure you have opened connection before you perform reading and change this line:

strSQL = "SELECT * FROM Events_t Where Event = '" & cmbName.SelectedText & "'"

to:

strSQL = "SELECT * FROM Events_t WHERE Name = '" & cmbName.Text & "'"

Btw, of course you can use only the indexes (numerical) but if you place the field names you will have better control there ... Cheers

 

[Rossy]

Well, tried it with your code and got the same thing...nothing. So i did a debug and noticed that when it gets to the if statement below it doesnt execute the code within the statment.
If objRead.Read = True Then

So i went step by step into the code and after the objRead.ExecuteReader code is performed, i looked at all of the properties of objRead and this is what was listed.

Depth...............0
FieldCount.........9
HasRows...........False
IsClosed............False
Item................<cannot view indexed property>

Then i thought just for kicks ill change the if condition to False. which yielded me the message "Row Handle is Invalid"

So, im not sure whats happening here since the connection should be good as im using the exact code i used to populate the combo box with the exception of the change to populate text boxes instead.

 

[Rossy]

Wow, my mistake. Sometimes i get so caught up in the VB code i just forget that i might make a mistake with the sql. I was reading the Contact column in the database when i should have been reading the Event Column. Sorry... and thanks again for the help. If i need anything else with this ill be sure to post