Update Access in VB.Net

Im writing a new VB.Net application that will have a textbox and a gridview.  A scanner will be used to input a record number into the textbox.  When the record number is entered, the record in an Access 2003 database needs to be updated (a field with a Yes/No data type to be updated to yes).  As soon as the database is updated the gridview would then show the records that have been scanned.
What I need is a kick start to get me moving.  Any great ideas?
us1975mcAsked:
Who is Participating?
 
CodeCruiserCommented:
TextBox1_TextChanged event:

If TextBox1.text.length > 0 then
   dbcmd.commandtext = "Update ... Set .... Where ...."
   dbcmd.executenonquery()
   dTable.Clear
   dbadp.fill(dTable)
   DataGridView1.DataSource = dTable
end if

In this code, it is assumed that you have oledbconnection open and setup, dbcmd is the oledbcommand object. dbadp is the oledbdataadapter and dtable is a datatable. Let me know if you need more details.
0
 
srikanthreddyn143Commented:
In the textbox value change event , have necessary code to update the Access database and get the data using OleDBAdapter
0
 
us1975mcAuthor Commented:
I have come up with the following code that seems to work.  srikanthreddyn143 gave me the first thought fo where to start and CodeCruiser just meved the idea along.  

This page has a label, textbox, gridview adn an AccessDataSource.  The Box number is scanned into the textbox.  Once TextBox1 has detected a change it fires the sub CheckRecord, I update the accessdatasource sending a GridView1.Databind, this updates the record in the database by setting a flag that the record has been scanned. Finally the GridView1.databind is triggered, causing the gridview to be refreshed displaying all records that have been scanned.

One last question if I can...

Where I have the code:
If ds.Tables(0).Rows.Count > 0 Then
            AccessDataSource1.Update()
            TextBox1.Text = ""
            TextBox1.Focus()
        Else
            TextBox1.Focus()
        End If
I am wnating to set the background color to red if the record was not found and Green if it was found.  Any ideas?
Imports System.Data.SqlClient
Imports System.Data
Imports System.Data.OleDb
 
Partial Class _Default
    Inherits System.Web.UI.Page
 
    Protected Sub TextBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
        CheckRecord()
    End Sub
 
    Protected Sub CheckRecord()
        Dim connetionString As String
        Dim oledbCnn As OleDbConnection
        Dim oledbAdapter As OleDbDataAdapter
        Dim ds As New DataSet
        Dim sql As String
        connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\Inetpub\wwwroot\DORRAS\db\dorscan.mdb';"
        sql = "SELECT BoxNum, Scanned FROM tblMain WHERE BoxNum = " & TextBox1.Text
        oledbCnn = New OleDbConnection(connetionString)
        oledbCnn.Open()
        oledbAdapter = New OleDbDataAdapter(sql, oledbCnn)
        oledbAdapter.Fill(ds)
        oledbAdapter.Dispose()
        oledbCnn.Close()
        If ds.Tables(0).Rows.Count > 0 Then
            AccessDataSource1.Update()
            TextBox1.Text = ""
            TextBox1.Focus()
        Else
            TextBox1.Focus()
        End If
 
        GridView1.DataBind()
    End Sub
 
    Protected Sub GridView1_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.DataBound
        lblTotal.Text = GridView1.Rows.Count & " Records Found"
    End Sub
 
    Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.SelectedIndexChanged
        AccessDataSource1.DeleteParameters(0).DefaultValue = GridView1.SelectedDataKey.Value
        AccessDataSource1.Delete()
    End Sub
 
End Class

Open in new window

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
srikanthreddyn143Commented:
TextBox1.BackColor = Color.Red
 
This is the property you can use
0
 
us1975mcAuthor Commented:
srikanthreddyn143,  I wasn't wanting the text box I was wanting the whole screen.  The individual that will be scanning will use a cordless scanner 20 - 30 feet away looking at the screen.  I need a BIG visual if something is not right.
0
 
us1975mcAuthor Commented:
CodeCruiser, Don't know if you looked at my code or not.  Just courious, is this what you were thinking?
0
 
srikanthreddyn143Commented:
You can use Me.BackColor if you want the form's back color to be changed
0
 
us1975mcAuthor Commented:
srikanthreddyn143,  How do I call it in the code block?  

ie...
        If ds.Tables(0).Rows.Count > 0 Then
            AccessDataSource1.Update()

            Me.BackColor = "Green"

            TextBox1.Text = ""
            TextBox1.Focus()
        Else
            TextBox1.Focus()
        End If
0
 
CodeCruiserCommented:
Try this

  If ds.Tables(0).Rows.Count > 0 Then
            AccessDataSource1.Update()

            Me.BackColor = Color.Green

            TextBox1.Text = ""
            TextBox1.Focus()
        Else
            Me.BackColor = Color.Red
            TextBox1.Focus()
        End If
0
 
us1975mcAuthor Commented:
I am getting the error: 'BackColor' is not a member of '_Default'
0
 
CodeCruiserCommented:
Oh its a webpage!!!!!!!!!!!!!!!!

0
 
us1975mcAuthor Commented:
Yep! ;)
0
 
CodeCruiserCommented:
Check this link.

http://www.velocityreviews.com/forums/t123138-changing-the-background-color.html


By the way, you did not select the ASP.NET as one of the zones for this question!!!!
0
 
us1975mcAuthor Commented:
By setting the body  as <body id="PageBody" runat="server"> and then placing PageBody.Attributes.Add("bgcolor", "green") in the code as such, I got it to work.

        If ds.Tables(0).Rows.Count > 0 Then
            AccessDataSource1.Update()
            PageBody.Attributes.Add("bgcolor", "green")
            TextBox1.Text = ""
            TextBox1.Focus()
        Else
            PageBody.Attributes.Add("bgcolor", "Red")
            TextBox1.Focus()
            TextBox1.Attributes.Add("onfocusin", " select();")
        End If
0
 
CodeCruiserCommented:
Why is it a B grade answer then?
0
 
us1975mcAuthor Commented:
The answer was not given outright.  I had to go in and do a lot more digging.  Nothing against anyone.  And from now on I will give an A just so no one can complain.
0
 
CodeCruiserCommented:
It does not affect you in anyway if you give an A but it encourages the experts to participate more.
0
 
us1975mcAuthor Commented:
Again I say, It will never happen again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.