Visual Studio 2013 connecting to MS Access db 1

[Everton1Blue]

Hello

I have the following my ASP.NET project. It's just a simple form that inserts form field data into a database. In my aspx.vb file I have:

Imports here including: Imports System.Data.OleDb

Partial Class Account_Register
Inherits System.Web.UI.Page


Protected Sub

Dim myMDBConnection As OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myDatabase.mdb;")
myMDBConnection.Open()
myMDBConnection.Close()
End Sub

Protected Sub CreateUser_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Using conn As New OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings("usersConnectionString").ConnectionString)
            
Dim Sql As String = "INSERT INTO userlist (username,password, strEmail) VALUES (@username,@password, @strEmail)"
Dim cmd As New OleDbCommand(Sql, conn)
conn.Open()
cmd.Parameters.AddWithValue("@username", username.Text)
cmd.Parameters.AddWithValue("@password", password.Text)
cmd.Parameters.AddWithValue("@strEmail", strEmail.Text)
cmd.ExecuteNonQuery()
conn.Close()
End Using
End Sub
End Class

I suspect the problem is here with this part of the code: myMDBConnection

Thanks for and advice!

 

[jebenson]

Your code seems to be incomplete.


Dim myMDBConnection As OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myDatabase.mdb;")

Should be:

Dim myMDBConnection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myDatabase.mdb;")

Are you getting any error messages? If so, what are they and what line of code is generating them?


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Everton1Blue]

Hello jebenson

Thanks for your reply.

I have replaced the line I had with the one you suggested and replaced 'Protected Sub' in the script I posted above because it seemed incomplete. So, the code now looks like this:

Sub Page_Load()

        Dim myMDBConnection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myDatabase.mdb;")
        myMDBConnection.Open()
        myMDBConnection.Close()
    End Sub

    Protected Sub CreateUser_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Using conn As New OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings("Provider=Microsoft.Jet.OLEDB.4.0;Data source="C:\myDatabase.mdb;").ConnectionString)

            Dim Sql As String = "INSERT INTO userlist (username,password, strEmail) VALUES (@username,@password, @strEmail)"
            Dim cmd As New OleDbCommand(Sql, conn)
            conn.Open()
            cmd.Parameters.AddWithValue("@username", username.Text)
            cmd.Parameters.AddWithValue("@password", password.Text)
            cmd.Parameters.AddWithValue("@strEmail", strEmail.Text)
            cmd.ExecuteNonQuery()
            conn.Close()
        End Using
    End Sub

The error, which is

Compiler Error Message: BC30004: Character constant must contain exactly one character.

is falling on this line:

Using conn As New OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings("Provider=Microsoft.Jet.OLEDB.4.0;Data source="C:\myDatabase.mdb;").ConnectionString)

Is that because I have already connected to the database in the 'Sub' above it?

Thanks!

 

[jebenson]

No, that connection (myMDBConnection) is only in scope in that sub. Just create the connection in the second sub the same way as in the first:

Using conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source="C:\myDatabase.mdb;")

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Everton1Blue]

That is giving me an error, I'm afraid:

BC30004: Character constant must contain exactly one character.

Using conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source="C:\myDatabase.mdb;")

One character? Maybe this is not the real error, but that is the one the browser is displaying.

I have this, along with other Imports, at the top of my page, which I think is correct: Imports System.Data.OleDb

Thanks again for your patience.

 

[jebenson]

Try making it 2 lines:

Dim conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source="C:\myDatabase.mdb;")

Using conn

...

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Everton1Blue]

It's still the same error on that new:

Dim conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source="C:\myDatabase.mdb;")

BC30004: Character constant must contain exactly one character.

Please see attached screenshot: that is what I am able to see.

Not sure if this is relevant, but in Web.config I have:

<?xml version="1.0"?>
<configuration>

  <location path="Manage.aspx">
    <system.web>
      <authorization>
        <deny users="?"/>
      </authorization>
    </system.web>
  </location>

</configuration>

It's a file I have never touched.

Thanks for all your patience.

 

[jebenson]

Duh...I'm not reading too well today apparently.

The connection string has an extra double-quote in it, after Data source

Dim conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source="C:\myDatabase.mdb;")

change it to this and it will work:

Dim conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data source=C:\myDatabase.mdb;")

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Everton1Blue]

That's great, jebenson

You have been very patient and a great help.

I am not getting any errors there, now.

I still have a couple of things to work on over the next few days but you have clarified a great deal and, for that, I am very grateful. Once this little script is up and running - and I can see why it's up and running - things will have slotted into place.

Many thanks again!