C# How to connect the local database (access)

Question

I have a question about C#. I wrote a function using ASP.net, when the user clicks the button, it should call this function and insert the sql to the local database. However, I don't know why it is not working. Can anyone help me?

My local database is Access, which is stored under the 'App_Data' folder.

protected void button1_Click(object sender, EventArgs e)
        {
            SqlConnection myConnection = new SqlConnection(connectionString);  
           // I think the problem is here, but I don't know how to do

            SqlCommand myCommand = new SqlCommand("INSERT INTO [car] ([carName], [carType]) VALUES (@carName, @carType)", myConnection);

            SqlParameter carName= myCommand.Parameters.Add("@carName", SqlDbType.Text);
            SqlParameter carType= myCommand.Parameters.Add("@carType", SqlDbType.Text);

           carName.Value = carNametb.Text;
           carType.Value = carTypetb.Text;

           myConnection.Open();
           myCommand.ExecuteNonQuery(); 
           // need to close() the connection where?

    }

You should use a using statement around the SqlCommand object, this will help you because it closes the connection in a well handled way, even if there is an exception. Hanselman can explain this better than me http://www.hanselman.com/blog/WhyTheUsingStatementIsBetterThanASharpStickInTheEyeAndASqlConnectionRefactoringExample.aspx — Lee Baker, Feb 13 '12 at 04:48
@Lee Baker, I try to type it, but the error occur in myConnection.Open() statement — Dickson Lee, Feb 13 '12 at 05:10
That sounds like a connection string issue. The using statement just makes your code more robust. — Lee Baker, Feb 13 '12 at 05:16

score 1 · Accepted Answer · edited Feb 19 '14 at 13:03

1

You need to declare your connection string.

string connectionstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:Database Password=MyDbPassword;";

edited Feb 19 '14 at 13:03

Joe

46,419
33
155
245

answered Feb 13 '12 at 04:46

Stephen__T

2,004
3
25
48

@ZeopDivide, it shows 'Exception Details: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.' and my db name is car, I try car and car.accdb, both are not correct. – Dickson Lee Feb 13 '12 at 05:06
For access connection string I think you need to format it like this: `Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:Database Password=MyDbPassword;` – Stephen__T Feb 13 '12 at 05:08
it has error when I run the statement which contain keyword Provider. – Dickson Lee Feb 13 '12 at 05:29
No, I find the problem, it should use olddbconnection. – Dickson Lee Feb 13 '12 at 07:33

score 1 · Answer 2 · answered Feb 13 '12 at 04:46

1

http://www.connectionstrings.com/ has lots of good information about different connection strings you might need.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;

answered Feb 13 '12 at 04:46

Lee Baker

562
1
3
12

I try to type this to code, but the system does not support the 'Provider', and I delete it. But it also has problem when executing the myConnection.Open(). – Dickson Lee Feb 13 '12 at 05:20

score 0 · Answer 3 · answered Feb 13 '12 at 04:51

0

You have to pass connections string

Go to Server explorer
Right click on database and select properties
There u will find Connection String
Copy the connection string and store connectionstring

answered Feb 13 '12 at 04:51

Vinod

4,672
4
19
26

yes, I used to do this, but it has problem about the myConnection.Open(); – Dickson Lee Feb 13 '12 at 05:21

C# How to connect the local database (access)

3 Answers3