Need help with ms access and vb.net

Question

blackknight2114 0 Newbie Poster

19 Years Ago

hi am a newbie to the vb.net thing. am using ms access for my database. i have a customer table in my database where sutomer details like first name, last name, address, etc are stored. i've created a form in vb.net and i have a text box, a datagrid and a search button. i want to do a search by name when i enter a customer name in the text box. the customer information will be displayed in the datagrid just like in a database. i can't write the codes and i have spent days looking for codes on the net. all i get is sql ones and am not familiar with sql. here are some codes i have been able to write to connect the database. not sure if it's good though.
please help me out.

Dim UP As New OleDb.OleDbCommand
Dim Con As New OleDb.OleDbConnection
Dim Read As OleDb.OleDbDataReader
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Con.Open()
MsgBox("A Connection to the Database is now open")
End Sub
Private Sub frmSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Replace(Application.StartupPath, "bin", "") & "DataStore.mdb;Mode=ReadWrite;Persist Security Info=False"
UP.Connection = Con
UP.CommandType = CommandType.Text
UP.CommandText = "Select Customer * From Customer "
End Sub
Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
Me.Close()
End Sub

.net:vb.net microsoft:access microsoft:sql-server

8 Contributors
11 Replies
412 Views
4 Years Discussion Span
Latest Post 14 Years Ago Latest Post by adam_k

Dani AI

Generated 9 Months Ago

A simple, reliable pattern for the scenario described by is to use ADO.NET (System.Data.OleDb): open the Access file inside a Using block, run a parameterized SELECT with LIKE for partial-name matches, fill a DataTable with an OleDbDataAdapter, and bind that DataTable to a DataGridView. That approach avoids ADODB/Recordset COM interop (the path tried by and ) and prevents common string-concatenation bugs and SQL injection.

' Parameterized name search and bind (VB.NET)
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Path\DataStore.mdb;"
' For Access 2007+ (.accdb) use: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Path\DataStore.accdb;"

Dim term As String = "%" & txtName.Text.Trim() & "%"

Using cn As New OleDb.OleDbConnection(connString)
    Using cmd As New OleDb.OleDbCommand("SELECT * FROM Customer WHERE FirstName LIKE ? OR LastName LIKE ?", cn)
        cmd.Parameters.AddWithValue("?", term)
        cmd.Parameters.AddWithValue("?", term)
        Dim dt As New DataTable()
        Using da As New OleDb.OleDbDataAdapter(cmd)
            da.Fill(dt)
        End Using
        dataGridViewResults.DataSource = dt
    End Using
End Using

Common pitfalls seen in the thread: the "Format of the initialization string" error reported by usually means the connection-string text is malformed (unmatched quotes, stray characters, or an invalid provider name). Also note that the Jet provider is 32-bit only; on 64-bit Windows either target the project to x86 or use the ACE provider and install the correct runtime. Using System.IO.Path.Combine for file paths avoids the fragile Replace(Application.StartupPath, "bin", "") hacks.

Quick practical notes: prefer parameters over string concatenation, set DataGridView.AutoGenerateColumns = True or bind a BindingSource for sorting/paging, and avoid AddWithValue for non-string fields if type precision matters. ADO.NET (System.Data) is the native .NET option; ADODB is legacy COM interop and generally not needed for VB.NET apps.

All 11 Replies

Netcode 33 Veteran Poster

14 Years Ago

see

adam_k 239 Master Poster

14 Years Ago

We are discussing a similar request here: http://www.daniweb.com/software-development/vbnet/threads/386231

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Kshiteesh 0 Newbie Poster · Answer 1 · 2007-08-21T15:43:04+00:00

Dear Blackknight,

Your problem is genuine for any beginner. you can use the following to add new record through add button.

Dim con As New ADODB.Connection

        Dim tmp As New ADODB.Recordset
        Dim s As String

        con.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=K:\Database\db1.mdb"
        con.Open()
        s = "select * from add1"
        tmp.Open(s, con, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)
tmp.AddNew()
        tmp.Fields("ID").Value = T18.Text
        tmp.Fields("Salutation").Value = T1.Text
        tmp.Fields("Fname").Value = T2.Text
        tmp.Fields("Mname").Value = T3.Text
        tmp.Fields("Lname").Value = T4.Text
        tmp.Fields("Degree").Value = T5.Text
        tmp.Fields("Category").Value = T6.Text
        tmp.Fields("Position").Value = T17.Text
        tmp.Fields("Org").Value = T16.Text

tmp.Update()
        MsgBox("Record Saved")
        Exit Sub

To display data in data grid you have to drag datagridview dataset and databinding source from the tool box and connect it to a data base. you can then write following code in the search button to show the items in the datagrid from the access table.

Db1DataSet1.Clear()

the aforementioned search option is by first name you can change accordingly your field names creating different forms and creating the links of those forms in a single menu form. 

If any problem persists, ask me. 

Regards

Kshiteesh
        Dim conStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source=K:\Database\db1.mdb"
        Dim con As OleDb.OleDbConnection = New OleDb.OleDbConnection(conStr)
        Dim S As String
        Dim datagrid1 As New DataGrid
        S = "select * from add1 where fname='" & TextBox1.Text & "'"
        Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(S, con)
        da.Fill(Db1DataSet1, "add1")
        datagrid1.DataSource = Db1DataSet1.DefaultViewManager

bka_k@Yahoo.com 0 Newbie Poster · Answer 2 · 2007-08-23T23:27:58+00:00

I am trying to use the following to open an MS access DB.

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim SelectText As String = "Select * from myTable"

Dim SqlConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\DesignDentalStudio\DesignDentalStudio\Werner Style Tracking 2006.mdb"

Dim myDA As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(SelectText, SqlConn)
End Sub
End Class

Geting an error:

ArgumentException was unhandled.
Format of the initialization string does not conform to specification starting at index 33. The last line with Dim is highlighted by yellow.

What is the error. can anyone pl comment?Y Or, please give a reference to a complete code to open an access DB from a VB.NET form. Thanks in advance.

ASoke

TransKim -4 Transkim · Answer 3 · 2007-09-13T20:03:59+00:00

Hi I have tried to use this code Dim con As New ADODB.Connection

Dim tmp As New ADODB.Recordset
Dim s As String

con.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=K:\Database\db1.mdb"
con.Open()
s = "select * from add1"
tmp.Open(s, con, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)
tmp.AddNew()
tmp.Fields("ID").Value = T18.Text
tmp.Fields("Salutation").Value = T1.Text
tmp.Fields("Fname").Value = T2.Text
tmp.Fields("Mname").Value = T3.Text
tmp.Fields("Lname").Value = T4.Text
tmp.Fields("Degree").Value = T5.Text
tmp.Fields("Category").Value = T6.Text
tmp.Fields("Position").Value = T17.Text
tmp.Fields("Org").Value = T16.Text

tmp.Update()
MsgBox("Record Saved")
Exit Sub

but VB.NET doesnot recognise that format.
Am need of a search for my customers database too could any one help us out with that problem.

sudantoy 0 Newbie Poster · Answer 4 · 2007-09-18T18:49:29+00:00

check this site it has all that you all seek, its awesome, n don 4get, u owe me 1, lol.
Have fun

bka_k@Yahoo.com 0 Newbie Poster · Answer 5 · 2007-09-18T22:22:00+00:00

check this site it has all that you all seek, its aweThis Website does not apparantly provide anything on how to open a DB from VB.NET.
ASokesome, n don 4get, u owe me 1, lol.
Have fun

This Website does not apparantly give anything on how to open a DB from VB.NET form.

Asoke

Kshiteesh 0 Newbie Poster · Answer 6 · 2007-09-19T15:35:02+00:00

You need to get reference of ado.net. then only that will work or support.

Just go to add reference in the menu bar and double click ado.net. you will get the problem solved..

Regards

Kshiteesh

bka_k@Yahoo.com 0 Newbie Poster · Answer 7 · 2007-09-19T21:29:40+00:00

I did that. It's giving this error. is not a stringHow do I know that ADO.net is created?

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim LevelOneFloorFourTeamOneGeneralistsKunelis1 As String
LevelOneFloorFourTeamOneGeneralistsKunelis1.accdb()
End Sub

siva28 -2 Newbie Poster · Answer 8 · 2011-10-07T12:48:10+00:00

I need code for vb.net 2008 . That is i have invoice number automatic generation. i used tab control . when i clicked tab automatically invoice number generated which is already saved in database+1.
For example ..already my database have EC000 means , when i click tab automatically print EC001 . that is increment one.
please reply ......

siva28 -2 Newbie Poster · Answer 9 · 2011-10-10T14:38:40+00:00

I need code for vb.net 2008 . That is i have invoice number automatic generation. i used tab control . when i clicked tab automatically invoice number generated which is already saved in database+1.
For example ..already my database have EC000 means , when i click tab automatically print EC001 . that is increment one.
please reply .

Private Sub TabPage3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TabPage3.Click

End Sub

This is i used in vb.net form.......when i click this tab page automatically EC001 will appear in textbox...

Need help with ms access and vb.net

Dani AI

Recommended Answers Collapse Answers

All 11 Replies

Recommended Answers