ADO .NET Code

[Xmas79]

I got around and around without finding nothing useful for creating an ADO.NET connection to an access database... Finally I got it working and now I post here the code:

Dim myConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0...." ' Put here the connection string
Dim mySelectQuery As String = "SELECT * FROM table" 'This is your sql query
Dim myConnection As New System.Data.OleDb.OleDbConnection(myConnString) 'Create a new connection
Dim myCommand As New System.Data.OleDb.OleDbCommand(mySelectQuery, myConnection)         
Dim myReader As System.Data.OleDb.OleDbDataReader = myCommand.ExecuteReader()
        
myConnection.Open() ' Open Connection
 
Try
    While myReader.Read() ' Read all records
        ' Do what you want with data... 
        ' Use myReader. methods to retrive your data:
        ' myReader.GetInt32(0) gets a 32-bit int from 1st field (zero-based)
        ' myReader.GetString(1) gets a string from 2nd field.... 
        ' For exampe:
        MsgBox(myReader.GetInt32(0).ToString() + ", " + myReader.GetString(1))
    End While
Finally
    ' Close connection
    myReader.Close()
    myConnection.Close()
End Try

I hope this will be useful

Xmas.

[Xmas79]

Made a little mistake... Put the myConnection.Open() statement before the dim myReader....
You'll get an error...

[stingrae]

xmas79, i know what you mean. here's my version:

http://www.vbforums.com/showthread.p...hreadid=221166

[ram2002]

You have done great job.Really Good.

I am struggling to put data into datagrid at runtime by dataset.

Here is my code:
Dim SQLConn As SqlConnection
SQLConn = New SqlConnection()
Dim sConnectionString As String
sConnectionString = "Data Source=VIDHYA;user id=sa;PWD=;Initial Catalog=pubs;"
SQLConn = New SqlConnection(sConnectionString)
Dim SQLCommand As New SqlCommand("SELECT * FROM addressdb", SQLConn)
Dim SQlAdop As New SqlDataAdapter(SQLCommand)
Dim mydataset As New DataSet("selectAddress")

Try
SQLConn.Open()

SQlAdop.Fill(mydataset, "addressdb")

Dim rowaddress As System.Data.DataRow


DataGrid1.DataSource = mydataset



Catch Mycxception As Exception
MessageBox.Show(Mycxception.ToString())
Finally
If SQLConn.State = ConnectionState.Open Then
SQLConn.Close()
End If
If Not mydataset Is Nothing Then
mydataset = Nothing
End If
End Sub.


Please help me out

[iulianionescu]

I think the problem is here:

DataGrid1.DataSource = mydataset

this should read

DataGrid1.DataSource = mydataset.Table(0).DefaultView

[Xmas79]

iulianionescu is right. You should also add this:

DataGrid1.DataBind()

It should work now.

[ram2002]

Yes it works fine.But i cannot use
Datadrid1.DataBind()

In Vb.NET

datagrid1.databindings() is not working.

Thanks.

[stingrae]

here's a way that i've used:

        Dim strSQLStm As String = "SELECT clm_First as FirstName, " & _
                                         "clm_Last as Surname, " & _
                                         "clm_Address as Address, " & _
                                         "clm_Suburb as Suburb, " & _
                                         "clm_Postcode as Postcode, " & _
                                         "clm_ClientID as ClientID " & _
                                  "FROM tbl_ClientMaster " & _
                                  "WHERE (clm_First LIKE '" & txtFirst.Text & "%') " & _
                                    "AND (clm_Last LIKE '" & txtSurname.Text & "%') " & _
                                  "ORDER BY clm_First, clm_Last"

        Dim daCustomers As New OleDb.OleDbDataAdapter(strSQLStm, gconDatabase)
        Dim dsCustomers As New DataSet()
        daCustomers.Fill(dsCustomers, "tbl_ClientMaster")
        grdClients.SetDataBinding(dsCustomers, "tbl_ClientMaster")

[ram2002]

Hi All,

Thanks For helping me.

I Need one more help.

In Visual Basic 6.we select the column and displays it in textbox .
Private Sub LedgerGrid_Click()

If MSFlexGrid.Col = 1 Then
Text1.Text = MSFlexGrid.Text
End If

End Sub

Samething i would like to do in VB.NET.But Vb.NET does not have Col property.How can i do it VB.NET

Thanks.

[stingrae]

I don't think this isn't exactly what you're after but might help your a bit:

dim grdClients as new DataGrid
Private Const cColClientID As Integer = 5
ClientID = grdClients.Item(grdClients.CurrentRowIndex, cColClientID)

[robbedaya]

Xmas79,
I used your code, and i've put the myconnection.open before the dim myreader but i got this error:

An unhandled exception of type "System.Data.OleDB.OleDBException occurred in System.data.dll
Additional information: No error information available: DB_SEC_E_AUTH_FAILED(0x80040e4d)

what does this mean? why doesn't it work?

[ram2002]

Have you tried this link:

http://support.microsoft.com/default...b;en-us;313547

[iulianionescu]

Why do you need to open the connection anyway? The dataadapter fills the dataset without a prior need for the connection to be opened. Actually this is the main difference with .NET that the database access is done in a non-connected mode and the connection is only briefly done while the data is exchanged...