Rewriting a VBA app In .NET - Connecting to DB

[Ian W]

I've started to rewrite a AC97 app in .NET.

I've never done any .net stuff and i'm confused on how I can connect and update to my back end access db's.

What is the best method to do this?

I need to be able to open a recordset based on a select statement and then update the record.

Any help much appreciated.

 

[cjard]

CLick the DW3 link in my sig and follow the Creating a Simple Data App tutorial
If you prefer to learn off a video, google "forms over data"

 

[Ian W]

CLick the DW3 link in my sig and follow the Creating a Simple Data App tutorial
If you prefer to learn off a video, google "forms over data"

Thanks for the link.

I'm probably incorrect but I don't think that is what I am trying to acomplish. I don't want to be displaying data on a form, its data manipulation i'm after for the most part.

I was hoping there was a actual bit of code that I could write to open the recordsets rather than using the wizards (or are the wizards preferable?)

 

[Hack]

Just a quickie example. HTH

Imports System.Data.OleDb

Public Class Form1

Private oConn As OleDbConnection

Private Sub Form_Load()
oConn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\yourdatabase.accdb;Persist Security Info=False;")

oConn.Open()
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sSQL As String = String.Empty
        sSQL = "SELECT field FROM table "
        Dim command As New OleDbCommand(sSQL, conn)
        Dim reader As OleDbDataReader = command.ExecuteReader()
        ListBox1.Items.Clear()
       If reader.HasRows = True Then 'make sure there is data returned
             While reader.Read()
                      ListBox1.Items.Add(reader.GetString(0))
              End While
       Else
              MessageBox.Show("No data matched your search criteria.")
       End If
        reader.Close()
    End Sub
End Class

 

[Ian W]

Many thanks Hack, looks just like what i'm after !

 

[cjard]

Ian, Hack has a habit of posting code from old tutorials or his own efforts and with very dubious coding practices.. Pasting SQLs into button click event handlers is far from sensible. Using "wizards" is sensible because they generate well formed, well encapsulated, secure, fast code. After all; you use a "wizard" (the forms designer) to design your forms rather than typing out all the layout commands by hand..

Do the DW3 tutorial. At the end of it you'll understand how to connect to, download, update and save data (including related data). You'll have in your app code a DataTable which is essentially a tabular representation of your database data like the old RecordSet was. This is not just about visually manipulating data with a GUI. You can loop over and alter the DataTable as you wish, filter it, manipulate it in code, get it to perform simple aggregate operations etc. When the time comes you can use LINQ to achieve more sophisticated manipulations in the client side but for now the impetus is on getting you started on a path of doing things properly, not thrusting some code at you that looks similar to the ways we did things back then; though the familiarity might be appealing and give you a sense of security that it's the right way, things have moved on; do not assume that a datareader works in the same way to the methods youre used to, just because you use it in a loop to pull data.

If you'd prefer to accept 10 lines of pasted code over an entire suite of tutorials from the makers of the programming language youre using, this is of course up to you but you are definitely forging a rod for your own back..

 

[Ian W]

cjard,

Point taken

Since I got my data reading working I need to do more anyway so I was planning on going down the tuturiol route.

Thanks for the advice.

 

[Ian W]

Ok, now i'm even more confused..

I have run through some of the wizards and I can connect to the data and what not so i'm ok with that now

If I explain what I am trying to acomplish then if someone could recommend the best 'guide' to check then I will have a go at that.

Ok,

VB form has 1 text box which I enter an IP address in and then hit my 'Connect' button.

The program then connects to a networked device that has a barcode scanner on it.

What then needs to happen is the user scans a serial number and this is passed back (done this part) and a query is run on the database which pulls all the information based on that serial number. I then send this data back to the scanner device to display on the screen.

This is why i'm thinking I need to do this with code, I don't need to be able to display anything on the form itself as its all purely back end. All the data needs to be send down the network to be displayed on our networed device.

Hopefully this makes sense.

Thanks

Ian

 

[InertiaM]

Before I comment any further, one question - is there any likelihood that you will ever have more than one networked device sending queries to your program?

 

[Ian W]

Yes that is a possibility for the future.

Currently there is one VBA form in access97 per device (4 of these in use).

Was going to make it one vb.net exe per device.

 

[InertiaM]

In that case, I would :-

1. Write the code rather than use wizards.
2. Keep all the devices communicating with one exe - do not try and use multiple copies.
3. Add all incoming messages to a Queue (Of T), including the address that they came from.
4. Use a BackgroundWorker to process the Queue, do your querying, and put the reply into a sending Queue.
5. Use another BackgroundWorker to process the sending Queue.
6. Append the incoming messages and sent messages to a textbox that shows (for example) the last 10 incoming and outgoing messages. You'll need to investigate Delegates to manage this.

 

[Ian W]

Hi,

Thanks for the reply but I think you overestimate my coding level.

I have still been unable to update my data in my table.

I've read and completed the majority of MS Walkthroughs but i'm still no further in understanding it.

Can't believe its so difficult to pull a record and update a value in it to show its been scanned.

 

[InertiaM]

Thanks for the reply but I think you overestimate my coding level.

I may have overestimated your current coding level, but not what your coding level really needs to be to build a stable, functional application as you describe.

Can't believe its so difficult to pull a record and update a value in it to show its been scanned.

Honestly, it isnt difficult. Go to Access and write an Update query which would do what you wanted it to. View the SQL, and copy it. Now try the following :-

Imports System.Data.OleDb

Public Class Form1

Private oConn As OleDbConnection
Private iRecordsAffected As Integer = 0
Private sSQL As String = String.Empty

Private Sub Form_Load()
        oConn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\yourdatabase.accdb;Persist Security Info=False;")
        oConn.Open()
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        sSQL = "UPDATE BarcodeTable SET BarcodeTable.Scanned = 1 WHERE BarcodeTable.ID = 3"   'put your SQL in here
        Dim command As New OleDbCommand(sSQL, conn)
        iRecordsAffected = command.ExecuteNonQuery()
        MessageBox.Show(String.Format ("{0} record(s) updated.", iRecordsAffected))
    End Sub
End Class

Does that update?

 

[Ian W]

Thanks

Between my last reply and your reply I managed to figure it out.

My code is basically what you have written but mines based on input from netstream rather than button clicks.

Got a very basic version of my program working now.

Thanks for your help

 

[InertiaM]

Can you post the section of code that deals with receiving the scanned barcode? I'll then show you how to use a BackgroundWorker to process the messages.