Making connection to Access 2000

[timothy2l]

I'm making an online form that should gather information from the text boxes and then send the information to the corresponding database fields. I've looked online for tutorials on setting up the connection and submitting the info, but i cant seem to get it to work. I'm fairly new at this so some of the syntax doesn't make much sense to me. Can someone please give me a walk through of how to set up the connection and then how to send the info from one text box to the db field. Thanks in advance.

ex: txtCity --> City field

[timothy2l]

Ok, ive made the database connection but i still cant figure out how to send textbox info to database tables. can anyone give me some ideas?

Thanks

[Mehyar]

I will suppose you are using VS.net

 

1.From the ToolBox in the Visual Interface, and under the Data Tab drag an OleDb Connection to your form.

2. Select the connection - Right Click properties and from theConnection String field select New Connection (A wizard pops up)

3. Click on the Provider Tab in the Wizard and select Microsoft Jet 4.0 OleDBProvider

4. Click on the connection Tab and Browse for your database file

5. Test the connection.

 

Now you can connect to the database

Name your connection for ex Connection1

 

When the user enters the text in txtcity and you want to insert the data to the database there are many procedures that you can follow. I will show you one :

1. Type the following code

 

' Add at the top of your form

Imports System.Data.Oledb

 

'Prepare the command that will insert the data

Dim command As New OleDbCommand()

With command

.CommandText = "Insert into TableNAME (CityField) values (" & txtcity.text & ")"

.Connection = Connection1

End With

Note that if you have more than one field you want to insert you have to specify the columns in order (seperated by commas) and the Corresponding values in the same order also seperated by commas.

 

Then type

Connection1.open() ' this would connect you to the database

Command.executeNonQuery() ' this will execute your insertion

Connection1.Close() 'Close your connection

 

Hope this helps,

[timothy2l]

Thanks for the response. I have run into a problem though:

When i run the form i get this error message:

 

Object reference not set to an instance of an object.

 

source error:

 

End With

 

Connection1.Open()

command.ExecuteNonQuery()

Connection1.Close()

 

-----------------------------------------------------------------

 

Here is my code:

 

 

Private Sub btnSumit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

Dim command As New OleDbCommand()

With command

.CommandText = "Insert into Graduates (City) values (" & txtCity.Text & ")"

.Connection = Connection1

 

End With

 

Connection1.Open()

command.ExecuteNonQuery()

Connection1.Close()

 

End Sub

 

 

Any ideas?

 

Thanks Again

[Mehyar]

IN my previous post i said

Name your connection for ex Connection1

 

If you have named it something differently then change the connection1 to the name of your connection.

[Mehyar]

If you have named it Connection1 and still got the problem then there is a problem in your connection.

 

Did you follow the connection i gave you ??

 

If not , post the way you connected to the database maybe we can see where the error is

[timothy2l]

I named my connection as Connection1.

 

 

now im getting an error that says:

 

Specified cast is not valid.

--------------------------------------

 

With command

.CommandText = "Insert....")"

.Connection = Connection1

 

End With

 

 

---------------------------------------------

 

I double checked the instructions u gave me. could you glance over my code above and see if i made any errors?

 

Thanks in advance

[Mehyar]

Timothy i am sure there is something not right with your connection please post the code of your connection.

[timothy2l]

Private Sub InitializeComponent()

Me.Connection1 = New System.Data.OleDb.OleDbConnection()

'

'Connection1

'

Me.Connection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=alumniData" & _

"base.mdb;Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database=""" & _

""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Typ" & _

"e=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OL" & _

"EDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Crea" & _

"te System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy L" & _

"ocale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:" & _

"SFP=False"

 

End Sub

[timothy2l]

Sorry, I missed the post about how i connected to the database.

I followed all of your directions except i did do one extra thing. i went to the advanced tab and selected "write". not sure if if should have done that or not. im trying it again without doing that.

[Mehyar]

ok because timothy i tried it just the way you i told you right now and everyhting went ok.

[timothy2l]

i started over and followed the directions exactly. This time i named it "myConnection". I still got the error:

Object reference not set to an instance of an object.

 

end with

 

myConnection.Open() *this is red*

command.ExecuteNonQuery()

myConnection.Close()

 

Here is the Stack Trace if that will help:

 

[NullReferenceException: Object reference not set to an instance of an object.]

AlumniProject.SubmitInfo.btnSubmit_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\AlumniProject\SubmitInfo.aspx.vb:97

System.Web.UI.WebControls.Button.OnClick(EventArgs e)

System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)

System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)

System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)

System.Web.UI.Page.ProcessRequestMain()

 

 

Thanks for all of your help so far, i feel like im close to getting this to work, but its frustrating when it doesn't.

[Mehyar]

Ok i was talking about a windows form application , oops.

 

Try this in your Click method

 

Dim myconnection as new OledbConnection()

myconnection.connectionstring = just copy the connection string from Initializecomponent.

and continue from the Dim command part as it is.

delete the connection object you created before

[timothy2l]

this may sound like a dumb question, but what do you mean when you say "copy the connection string from the intialize component"?

 

Thanks

[Mehyar]

the connection string you posted

Me.Connection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=alumniData" & _

"base.mdb;Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database=""" & _

""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Typ" & _

"e=5;Jet OLEDBatabase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OL" & _

"EDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Crea" & _

"te System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy L" & _

"ocale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:" & _

"SFP=False"

[timothy2l]

so do i not need an oledb connection object on the form anymore?

im getting an error message now that says it cant find:

c:\WINDOWS\system32\alumniDatabase.mdb

 

Thats not where the database is, but i dont know where to change it ( i dont see it anywhere in the code).

 

 

here's my code. i dont think there are any errors:

 

--------------------------

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

Dim command As New OleDbCommand()

Dim myConnection As New OleDbConnection()

 

myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=alumniData" & _

"base.mdb;Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database=""" & _

""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Typ" & _

"e=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OL" & _

"EDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Crea" & _

"te System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy L" & _

"ocale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:" & _

"SFP=False"

 

With command

.CommandText = "Insert into Graduates (City) values (" & txtCity.Text & ")"

.Connection = myConnection

 

End With

 

myConnection.Open()

command.ExecuteNonQuery()

myConnection.Close()

 

End Sub

[Mehyar]

Look in the ConnectionString, the DataSource should specify the Path, change it from alumniDatabase.mdb to where it is found (the full path of the database) example :

DataSource = C:/....../alumniDatabase.mdb

[timothy2l]

ok, i fixed the path and now i get the error:

 

No value given for one or more required parameters.

 

myConnection.Open()

command.ExecuteNonQuery() *This is red*

myConnection.Close()

[timothy2l]

also, if i leave txtCity empty and try to submit, i get an error message that says :

 

Syntax error in INSERT INTO statement,

 

but it still highlights

 

command.ExecuteNonQuery()

 

in red and nothing else

[Mehyar]

Fix this

With command

.CommandText = "Insert into Graduates (City) values (" & txtCity.Text & ")"

.Connection = myConnection

 

End With

 

to

 

With command

.CommandText = "Insert into Graduates (City) values ('" & txtCity.Text & "')"

.Connection = myConnection

End With

 

Incase you didnot notice i added a single quote after ( and another one before ).

[timothy2l]

Mehyar, thankyou so much for you help. after all this time it finally worked. I really appreciate you taking so much time to help me out. I know where to come if I have any more questions.

Thanks Again

[timothy2l]

Wait, i do have another question :)

How difficult is it to explain how to move to the next record each time someone fills out the form. THanks

[Mehyar]

You are welcome, it was my pleasure ....

But what do you mean explain yourself more ???

[timothy2l]

Sorry for that last post. The problem with with the database setting (i had my ID as number instead of autonumber so it wouldnt let me duplicate records) It works fine now.

THanks again

[mrdutchie]

Hello,

 

I am having the same trouble to write something in a Access

file.

 

I am getting this error

 

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

 

I created a connection exactly the same as stated above.

 

My access file contains 2 tables

1. Companyinformation

2. Configuration

 

within the Table Companyinformation Table I have 5 Fields

( company1,company2,company3,company4,company5)

 

I want to add "Hello" into the company1 field.

 

And this is my procedure

 

Public Sub write_info()

Dim command As New OleDbCommand

With command

'.CommandText = "Insert into TableNAME (company1) values (" & txtcity.Text & ")"

.CommandText = "Insert into Companyinformation (company1) values ('Hello')"

 

.Connection = connection1

End With

 

connection1.Open()

command.ExecuteNonQuery()

connection1.Close()

End Sub

 

What am I missing?

 

Thanks so much