?
Solved

Update Access in VB.Net

Posted on 2009-04-30
18
Medium Priority
?
480 Views
Last Modified: 2013-11-26
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?
0
Comment
Question by:us1975mc
  • 9
  • 6
  • 3
18 Comments
 
LVL 11

Assisted Solution

by:srikanthreddyn143
srikanthreddyn143 earned 600 total points
ID: 24270585
In the textbox value change event , have necessary code to update the Access database and get the data using OleDBAdapter
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 900 total points
ID: 24273510
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
 

Author Comment

by:us1975mc
ID: 24279204
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:srikanthreddyn143
ID: 24280450
TextBox1.BackColor = Color.Red
 
This is the property you can use
0
 

Author Comment

by:us1975mc
ID: 24280486
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
 

Author Comment

by:us1975mc
ID: 24280522
CodeCruiser, Don't know if you looked at my code or not.  Just courious, is this what you were thinking?
0
 
LVL 11

Expert Comment

by:srikanthreddyn143
ID: 24280776
You can use Me.BackColor if you want the form's back color to be changed
0
 

Author Comment

by:us1975mc
ID: 24280809
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24280885
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
 

Author Comment

by:us1975mc
ID: 24280931
I am getting the error: 'BackColor' is not a member of '_Default'
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24281054
Oh its a webpage!!!!!!!!!!!!!!!!

0
 

Author Comment

by:us1975mc
ID: 24281063
Yep! ;)
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24281069
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
 

Author Comment

by:us1975mc
ID: 24282543
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24283256
Why is it a B grade answer then?
0
 

Author Comment

by:us1975mc
ID: 24283275
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24283287
It does not affect you in anyway if you give an A but it encourages the experts to participate more.
0
 

Author Comment

by:us1975mc
ID: 24283320
Again I say, It will never happen again.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question