VB.net connect to Access Database

[khentz]

Below is my code connection to my database..I highlight with red color my target database. And I would like to ask, what is the safest way to target the database. Because Im thinking, what if my database is change to another directory. If that happens, it will cause trouble to my system. I hope someone can help me with this. Thanks in advance :)

Private Sub btnSaveExit_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveExit.Click
            provider = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source ="
        dataFile = "C:\Users\compUser\Documents\TestDB.accdb"            
connString = provider & dataFile

            myConnection.Open()

        Dim str As String
        str = "insert into tblEmployee ([Fname], [LName], [Position], [Division], [CostCenter], [Shift], [Workstation]) values (?, ?, ?, ?, ?, ?, ?)"

        Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
        cmd.Parameters.Add(New OleDbParameter("FName", CType(txtFName.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("LName", CType(txtLName.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Position", CType(txtPosition.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Division", CType(txtDivision.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("CostCenter", CType(txtShift.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Workstation", CType(txtWorkstation.Text, String)))

        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            txtFName.Clear()
            txtLName.Clear()
            txtPosition.Clear()
            txtDivision.Clear()
            txtCostCenter.Clear()
            txtShift.Clear()
            txtWorkstation.Clear()


        End Try
    End Sub

[Netcode]

And I would like to ask, what is the safest way to target the database. Because Im thinking, what if my database is change to another directory. If that happens, it will cause trouble to my system. I hope someone can help me with this.

Its always better to create a project folder in your C:\ drive and put your dbase files there. The reason is this: putting in Documents like you just did may cause errors when you decide to code on another system because the system name would not always be the same and so there would be an error in locating the database directory but if you happen to have your files in the C:\ drive, then no problem as you can always copy the folder containing your files to the C:\ of the other computer and your codes remain the same

[kingsonprisonic]

str = "insert into tblEmployee ([Fname], [LName], [Position], [Division], [CostCenter], [Shift], [Workstation]) values (?, ?, ?, ?, ?, ?, ?)"

change it to

str = "insert into tblEmployee ([Fname], [LName], [Position], [Division], [CostCenter], [Shift], [Workstation]) values ('?', '?', '?', '?', '?', '?', '?')"

Assuming all fields as varchar.

If i am wrong let me know.

[khentz]

VB.NET Syntax (Toggle Plain Text)
str = "insert into tblEmployee ([Fname], [LName], [Position], [Division], [CostCenter], [Shift], [Workstation]) values (?, ?, ?, ?, ?, ?, ?)"str = "insert into tblEmployee ([Fname], [LName], [Position], [Division], [CostCenter], [Shift], [Workstation]) values (?, ?, ?, ?, ?, ?, ?)"

change it to

VB.NET Syntax (Toggle Plain Text)
str = "insert into tblEmployee ([Fname], [LName], [Position], [Division], [CostCenter], [Shift], [Workstation]) values ('?', '?', '?', '?', '?', '?', '?')"str = "insert into tblEmployee ([Fname], [LName], [Position], [Division], [CostCenter], [Shift], [Workstation]) values ('?', '?', '?', '?', '?', '?', '?')"

Assuming all fields as varchar.

If i am wrong let me know.

hmm..but its working kingsonprisonic..
if im going to put a single quote on every question mark, the question marks will be save on my database instead of the data that i input on the textboxes.

[gearup4it]

get the database path from the user then pass the path to your FilePath variable.

I would recommend using sql 2005\2008 express, it's free
and you can do alot more.

Regards
gearup4it

[gearup4it]

get the database path from the user then pass then path to your FilePath variable.

I would recommend using sql 2005\2008 express, it's free
and you can do alot more.

Regards
gearup4it

[gearup4it]

get the database path from the user then pass the path to your FilePath variable.

Access & SQL Server syntax variation, with regards to ? vs '?'

I would recommend using SQL Server 2005\2008 express, it's free
and you can do alot more.

Regards
gearup4it

[gearup4it]

get the database path from the user then pass the path to your FilePath variable.

Access & SQL Server syntax variation, with regards to the ? issue

I would recommend using SQL Server 2005\2008 express, it's free
easier to manage and you can do alot more, alot easily...

Regards
gearup4it

[gearup4it]

get the database path from the user then pass the path to your FilePath variable.

Access & SQL Server syntax variation, with regards to the ? issue

I would recommend using SQL Server 2005\2008 express, it's free
easier to manage and you can do alot more, alot easily...

Regards
gearup4it

[kingsonprisonic]

Ok then try this

str = "insert into tblEmployee ([Fname], [LName], [Position], [Division], [CostCenter], [Shift], [Workstation]) values ('"+txtFName.Text.Trim+"', '"+txtLName.Text.Trim+"', '"+txtPosition.Text.Trim+"', '"+txtDivision.Text.Trim+"', '"+txtCOstCenter.Text.Trim+"', '"+txtShift.Text.Trim+"', '"+txtWorkstation.Text.Trim+"')"

By the way in your code i found u use 7 parameters in query string
[Fname], [LName], [Position], [Division], [CostCenter], [Shift], [Workstation]
But you supply only 6 of them....

[gearup4it]

Public Class frmMain
    Private Const dataFile = "C:\TestDB\TestDB.accdb"
    Private Sub frmMain_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If DoesDataBaseFileExist() Then
            'continue loading
        Else
            'alternate handling here
            'e.g get input from user
            'exit application
        End If
    End Sub
    Private Function DoesDataBaseFileExist() As Boolean
        If System.IO.File.Exists(dataFile) Then
            Return True
        Else
            Return False
        End If
    End Function
End Class

[gearup4it]

you need to provide some sort of status in your exception handling.
i.e msgbox,print to a label control, log to file.
you are just clearing your text field with no status handling,
how will you know if you have an error?