[RESOLVED] [2008] ConnectionString help needed

[EvilObsidian]

Hi,
I am a new programmer trying to stumble through this on my own. This is my first attempt to manipulate a database, so I've created a very simple one that includes only one table with two columns (a "Person" table with fields "First" and "Last", both strings). My database is called TestBase, created with Microsoft Access 2007. I am using Visual Studios 2008 to write the program.

Question #1) Where does my database go? Right now I have it in the solution's Bin folder.

Question #2) Do I need to enter the path anywhere in my code, or does the program get that info from the DataSource properties?

Question #3) What do I put in the connectionstring property for the ADODC item that I drag and drop onto the form?

Here is my code:

Option Strict On
Option Explicit On

Imports System
Imports System.Data
Imports System.Data.OleDb

Public Class TestApp

Inherits System.Windows.Forms.Form

Protected Const SQL_CONNECTION_STRING As String = _
'????????????????

Private ConnectionString As String = SQL_CONNECTION_STRING

Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click

txtFirst.Text = ""
txtLast.Text = ""

End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

Dim cnOle As OleDbConnection
Dim cmOle As OleDbCommand
Dim strOle As String
Dim firstName As String = txtFirst.Text

Try
' Build Insert statement
strOle = "INSERT Person VALUES (" & _
PrepareStr(firstName) & "," & txtLast.Text

cnOle = New OleDbConnection(ConnectionString)
cnOle.Open()

cmOle = New OleDbCommand(strOle, cnOle)
cmOle.ExecuteNonQuery()

' Close and Clean up objects
cnOle.Close()
cmOle.Dispose()
cnOle.Dispose()

Catch Exp As OleDbException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
End Try

End Sub
Private Function PrepareStr(ByVal strValue As String) As String
' This function accepts a string and creates a string that can
' be used in a SQL statement by adding single quotes around
' it and handling empty values.
If strValue.Trim() = "" Then
Return "NULL"
Else
Return "'" & strValue.Trim() & "'"
End If
End Function

Private Sub TestApp_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

End Sub
End Class

Thanks in advance!

Russ

[EvilObsidian]

I'm still trying things for this and here's what I've come up with where the ???'s are above:

Protected Const SQL_CONNECTION_STRING As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=TestBase.mdb;User Id=admin;Password=;"

Also, I've put in "Microsoft.Jet.OLEDB.4.0" on the ADO's connectionstring property. It runs now and gives me an SQL error that says it cannot find the file at (then shows the path). TestBase (the database) is in the folder that the path on the error message specifies. I tried putting the database right on the desktop and adding a new datasource to that destination, but it still looks along the old path (which never worked anyway). One last thought, I've noticed that the Microsoft Access databases I've created have the file extension .accdb rather than .mdb. However, everywhere I've found examples with code .mdb is used by the programmer. Thanks for reading this, and thanks in advance if you are able to help :-)

Russ

[Joacim Andersson]

For Access 2007 the Provider part should be set to:

Provider=Microsoft.ACE.OLEDB.12.0;

Also change the Data Source to point to your file with the correct extension .accdb.

[EvilObsidian]

Thanks for your help :-)

I believe the connection problem is fixed, the error is gone. Now I get a syntax error in my INSERT statement. I'm going to see if I can work that out and get my program to execute the statement.

Thanks again!

Russ