Making connection to Access 2000

T

timothy2l

Well-known member
Joined
Jul 3, 2003
Messages
61
Im making an online form that should gather information from the text boxes and then send the information to the corresponding database fields. Ive looked online for tutorials on setting up the connection and submitting the info, but i cant seem to get it to work. Im fairly new at this so some of the syntax doesnt 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
 
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
 
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,
 
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
 
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.
 
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
 
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
 
Timothy i am sure there is something not right with your connection please post the code of your connection.
 
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:Dont Copy L" & _
"ocale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:" & _
"SFP=False"

End Sub
 
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.
 
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 doesnt.
 
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
 
this may sound like a dumb question, but what do you mean when you say "copy the connection string from the intialize component"?

Thanks
 
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 OLEDBont Copy L" & _
"ocale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:" & _
"SFP=False"
 
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).


heres 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:Dont 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
 
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
 
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()
 
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
 
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 ).
 
Back
Top