Click here to Skip to main content
15,884,836 members
Search within:


Select a different table in an access database with VB.NET
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
VB
MS-Access
1st off, my apologies if this question has been asked. I have scoured the web, but haven't found an exact answer to the problem I'm facing. Secondly, I must stress that, I am not a developer, I'm a mechanical engineer and only writing sowftware as a needs must situation, with that out of the way.

I have a form which passes data to an access db (This works). However I need to update it so that it will pass the information to different tables within the same db, based upon a selection in a combobox. For instance if combobox selection = X then insert into tableX, if combobox = Y then insert into tableY. Any and all help is appreciated.

Note: I've tried using If statements in order to select the appropriate table, but this doesn't work. I get no errors in VS, however when I try to submit, I get a dialog that says "command text not set for the command object".

Please see code below.

What I have tried:

code
Imports System.Data.OleDb

Public Class Form1
Public ds As New DataSet
Dim provider As String
Dim dataFile As String
Dim connString As String
Dim myConnection As OleDbConnection = New OleDbConnection
Dim rs As New resizer
Dim cmd As OleDbCommand


Private con As Object

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click


    provider = "Provider=Microsoft.Jet.OleDb.4.0;Data Source="
    dataFile = "R:\Quality\NCR-Access_Database\NCRdb1.mdb"
    connString = provider & dataFile
    myConnection.ConnectionString = connString
    myConnection.Open()



    Dim str As String
    str = ""

    If ComboBox2.SelectedText = "Assembly" Then
        str = "Insert into [ASSEMBLYtbl]([NCR-No],[Week-No],[Part-No],[Drawing-Rev],[Description],[W/O-Number],[Operator-No],[Operation-No],[Machine-No],[Section],[Batch-Qty],[Reject_Qty],[Disposition],[Mat-Cost],[Standard-Cost],[Defect-Descripition],[Fault-Code],[Dept],[Root-Cause],[NCR-Pinksheet],[Action]) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
    ElseIf ComboBox2.SelectedText = "Grinding" Then
        str = "Insert into [GRINDINGtbl]([NCR-No],[Week-No],[Part-No],[Drawing-Rev],[Description],[W/O-Number],[Operator-No],[Operation-No],[Machine-No],[Section],[Batch-Qty],[Reject_Qty],[Disposition],[Mat-Cost],[Standard-Cost],[Defect-Descripition],[Fault-Code],[Dept],[Root-Cause],[NCR-Pinksheet],[Action]) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
    ElseIf ComboBox2.SelectedText = "Milling" Then
        str = "Insert into [MILLINGtbl]([NCR-No],[Week-No],[Part-No],[Drawing-Rev],[Description],[W/O-Number],[Operator-No],[Operation-No],[Machine-No],[Section],[Batch-Qty],[Reject_Qty],[Disposition],[Mat-Cost],[Standard-Cost],[Defect-Descripition],[Fault-Code],[Dept],[Root-Cause],[NCR-Pinksheet],[Action]) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
    ElseIf ComboBox2.SelectedText = "Mill-Turn" Then
        str = "Insert into [MILL-TURNtbl]([NCR-No],[Week-No],[Part-No],[Drawing-Rev],[Description],[W/O-Number],[Operator-No],[Operation-No],[Machine-No],[Section],[Batch-Qty],[Reject_Qty],[Disposition],[Mat-Cost],[Standard-Cost],[Defect-Descripition],[Fault-Code],[Dept],[Root-Cause],[NCR-Pinksheet],[Action]) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
    ElseIf ComboBox2.SelectedText = "Turning" Then
        str = "Insert into [TURNINGtbl]([NCR-No],[Week-No],[Part-No],[Drawing-Rev],[Description],[W/O-Number],[Operator-No],[Operation-No],[Machine-No],[Section],[Batch-Qty],[Reject_Qty],[Disposition],[Mat-Cost],[Standard-Cost],[Defect-Descripition],[Fault-Code],[Dept],[Root-Cause],[NCR-Pinksheet],[Action]) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
    ElseIf ComboBox2.SelectedText = "Supplier" Then
        str = "Insert into [PURCHASINGtbl]([NCR-No],[Week-No],[Part-No],[Drawing-Rev],[Description],[W/O-Number],[Operator-No],[Operation-No],[Machine-No],[Section],[Batch-Qty],[Reject_Qty],[Disposition],[Mat-Cost],[Standard-Cost],[Defect-Descripition],[Fault-Code],[Dept],[Root-Cause],[NCR-Pinksheet],[Action]) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
    ElseIf ComboBox2.SelectedText = "Subcon" Then
        str = "Insert into [PURCHASINGtbl]([NCR-No],[Week-No],[Part-No],[Drawing-Rev],[Description],[W/O-Number],[Operator-No],[Operation-No],[Machine-No],[Section],[Batch-Qty],[Reject_Qty],[Disposition],[Mat-Cost],[Standard-Cost],[Defect-Descripition],[Fault-Code],[Dept],[Root-Cause],[NCR-Pinksheet],[Action]) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
    ElseIf ComboBox2.SelectedText = "Quality" Then
        str = "Insert into [QUALITYtbl]([NCR-No],[Week-No],[Part-No],[Drawing-Rev],[Description],[W/O-Number],[Operator-No],[Operation-No],[Machine-No],[Section],[Batch-Qty],[Reject_Qty],[Disposition],[Mat-Cost],[Standard-Cost],[Defect-Descripition],[Fault-Code],[Dept],[Root-Cause],[NCR-Pinksheet],[Action]) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
    End If


    cmd = New OleDbCommand(str, myConnection)
    cmd.Parameters.Add(New OleDbParameter("NCR-No", TextBox1.Text))
    cmd.Parameters.Add(New OleDbParameter("Week-No", TextBox3.Text))
    cmd.Parameters.Add(New OleDbParameter("Part-No", TextBox4.Text))
    cmd.Parameters.Add(New OleDbParameter("Drawing_Rev", TextBox5.Text))
    cmd.Parameters.Add(New OleDbParameter("Description", TextBox6.Text))
    cmd.Parameters.Add(New OleDbParameter("W/O-No", TextBox7.Text))
    cmd.Parameters.Add(New OleDbParameter("Operator-No", TextBox8.Text))
    cmd.Parameters.Add(New OleDbParameter("Operation-No", TextBox9.Text))
    cmd.Parameters.Add(New OleDbParameter("Machine-No", TextBox10.Text))
    cmd.Parameters.Add(New OleDbParameter("Section", ComboBox2.Text))
    cmd.Parameters.Add(New OleDbParameter("Batch-Qty", TextBox12.Text))
    cmd.Parameters.Add(New OleDbParameter("Reject_Qty", TextBox13.Text))
    cmd.Parameters.Add(New OleDbParameter("Disposition", TextBox14.Text))
    cmd.Parameters.Add(New OleDbParameter("Mat-Cost", TextBox15.Text))
    cmd.Parameters.Add(New OleDbParameter("Standard-Cost", TextBox16.Text))
    cmd.Parameters.Add(New OleDbParameter("Defect-Description", RichTextBox1.Text))
    cmd.Parameters.Add(New OleDbParameter("Fault-Code", TextBox17.Text))
    cmd.Parameters.Add(New OleDbParameter("Dept", TextBox18.Text))
    cmd.Parameters.Add(New OleDbParameter("Root-Cause", RichTextBox2.Text))
    cmd.Parameters.Add(New OleDbParameter("NCR-Pinksheet", ComboBox1.Text))
    cmd.Parameters.Add(New OleDbParameter("Permanent-Action", RichTextBox3.Text))

    Try

        cmd.ExecuteNonQuery()
        cmd.Dispose()
        myConnection.Close()
        TextBox1.Clear()
        TextBox4.Clear()
        TextBox5.Clear()
        TextBox3.Clear()
        TextBox6.Clear()
        TextBox7.Clear()
        TextBox8.Clear()
        TextBox9.Clear()
        TextBox10.Clear()
        ComboBox2.ResetText()
        TextBox12.Clear()
        TextBox13.Clear()
        TextBox14.Clear()
        TextBox15.Clear()
        TextBox16.Clear()
        RichTextBox1.Clear()
        TextBox17.Clear()
        TextBox18.Clear()
        RichTextBox2.Clear()
        ComboBox1.ResetText()
        RichTextBox3.Clear()


    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

End Sub


End Class
Posted
Updated 8-Jul-19 3:14am
v3
Add a Solution
Comments
FranzBe 8-Jul-19 5:59am    
Which Access Version are you using. "this doesn't work" means what? You got an exception? From 1st view your code doesn't look wrong (it would be better to have only the table-name in the if-statement as the rest of the sql command remains the same)
The-guy-on-the-couch 8-Jul-19 6:23am    
Using access 2003. Sorry about that, I copied the text and forgot to paste the rest. I get no errors in VS, however when I try to submit, I get a dialog that says "command text not set for the command object".
FranzBe 8-Jul-19 6:33am    
That means: the "str" variable is empty at the end of the if-else statement. You can add another "else" at the end, with the meaning "none of my expected combobox values where matched, something is wrong". Or even better: restructure your code as suggested by the solution below. Set a breakpoint at the start of the if-else statement and examine the content of "ComboBox2.SelectedText". In case you don't know a "Messagebox(ComboBox2.SelectedText)" will do.
The-guy-on-the-couch 8-Jul-19 8:38am    
Thanks for offering your assistance, I tried completing the If Else statement and it producded the same dialog "command text not set". As for the solultion below, I tried the "switch" method however that produces a BC32017 error - Comma, `)`, or a valid expression continuation expected. Removing the $"\"{ComboBox2.SelectedText}\ from the argument clears the error, but I then get an exception unhandled event when I submit the data.
FranzBe 8-Jul-19 8:46am    
ok, one step back: the value that comes from your combobox does not match on of the expected entries for 'tablename' you are checking against. you need to ensure that the selection possible by the combobox is in line with the tablenames you have to provide. Try setting a breakpoint at the beginning of your button1_click event handler, inspect the .SelectedText property. Something is wrong there.

2 solutions

Quote:
Note: I've tried using If statements in order to select the appropriate table, but this doesn't work. Please see code below.

"It doesn't work" is probably the most useless problem report we get - and we get it a lot. It tells us nothing about what is happening, or when it happens.
So tell us what it is doing that you didn't expect, or not doing that you did.
Tell us what you did to get it to happen.
Tell us any error messages.

The code you have will work, provided the table name in your ComboBox is present in one of the checks - and that includes any character cases - "Uppercase" is not the same as "uppercase" or "UpperCase" when you compare strings for example.

The way I would do it is to use a Switch:
VB
Dim str As String = ""

    Select Case ComboBox2.SelectedText.ToLower()
        Case "assembly"
            str = "ASSEMBLYtbl"
        Case "grinding"
            str = "GRINDINGtbl"
        Case "milling"
            str = "MILLINGtbl"
        Case "mill-turn"
            str = "[MILL-TURNtbl]"
        Case "turning"
            str = "TURNINGtbl"
        Case "supplier"
            str = "PURCHASINGtbl"
        Case "subcon"
            str = "PURCHASINGtbl"
        Case "quality"
            str = "QUALITYtbl"
        Case Else
            Throw New ArgumentException($"\"{ComboBox2.SelectedText}\" is not a known table name")
    End Select
Or even a Dictionary, and then use than table name:
VB
str = $"INSERT INTO {str} ([NCR-No],[Week-No],[Part-No],[Drawing-Rev],[Description],[W/O-Number],[Operator-No],[Operation-No],[Machine-No],[Section],[Batch-Qty],[Reject_Qty],[Disposition],[Mat-Cost],[Standard-Cost],[Defect-Descripition],[Fault-Code],[Dept],[Root-Cause],[NCR-Pinksheet],[Action]) Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"


And do yourself two favours in future:
1) Stop using hyphens and underscores in field names! Use uppercase to mark the start of each word instead - then you don't need all the square brackets which clutter your code and make it harder to read.
2) Stop using Visual Studio default names for everything - you may remember that "TextBox8" is the mobile number today, but when you have to modify it in three weeks time, will you then? Use descriptive names - "tbMobileNo" for example - and your code becomes easier to read, more self documenting, easier to maintain - and surprisingly quicker to code because Intellisense can get to to "tbMobile" in three keystrokes, where "TextBox8" takes thinking about and 8 keystrokes...
 
Share this answer
 
Posted
this is a variation of what is already posted as solution 1 above. perhaps it is of some help for you:


VB
Public Class Form1

  Dim tableDictionary As Dictionary(Of String, String) = New Dictionary(Of String, String)()

  Private Sub FillDictionary()
    tableDictionary.Add("assembly", "ASSEMBLYtbl")
    tableDictionary.Add("grinding", "GRINDINGtbl")
    tableDictionary.Add("milling", "MILLINGtbl")
    tableDictionary.Add("mill-turn", "[MILL-TURNtbl]")  ' you should rename this!
    tableDictionary.Add("turning", "TURNINGtbl")
    tableDictionary.Add("supplier", "PURCHASINGtbl")
    tableDictionary.Add("subcon", "PURCHASINGtbl")
    tableDictionary.Add("quality", "QUALITYtbl")
  End Sub

  Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    FillDictionary()
    ComboBox2.DataSource = tableDictionary.ToList()
    ComboBox2.ValueMember = "Key"
    ComboBox2.DisplayMember = "Key"
  End Sub

  Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    If ComboBox2.SelectedIndex = -1 Then
      MessageBox.Show("you must select something")
      Return
    End If

    Dim theTableName As String = "not defined"
    If tableDictionary.TryGetValue(ComboBox2.SelectedValue, theTableName) Then
      MessageBox.Show("you selected table: " + theTableName)
    Else
      MessageBox.Show("something went wrong")
      Return
    End If

    Dim sqlCommand As String = $"INSERT INTO {theTableName}  the rest of your command"
    MessageBox.Show($"this is the command for access: {sqlCommand}")
  End Sub
End Class
 
Share this answer
 
Posted
Updated 8-Jul-19 3:19am
v2
Comments
The-guy-on-the-couch 10-Jul-19 5:26am    
FranzBe thank you for your assistance, I finally got around to trying the above method, however when I submit the data, VS returns an "Exception unhandled" System.ArgumentNullException: 'Value cannot be null.
Parameter name: key' at the line 
If tableDictionary.TryGetValue(ComboBox2.SelectedValue, theTableName) Then
Unfortunatley I'm uncertain as to what value this could be, please could you advise?
FranzBe 10-Jul-19 15:54pm    
ok, the snippet above is working, I tested that before posting it. I can't see your code so I have no idea what is going wrong on your side. If you add a new form to your solution and paste the code above into it (and add a button1 and a ComboBox2) you have a snippet that builds an sql command string having the tablename as variable dependent on the setting of a combobox. That was the original question. You can then copy and paste the required parts into your form that is not yet working. If you were a colleague next floor I would offer you a visit an have a look on your screen. This "I get an error here" without seeing the context unfortunately leads to nothing.
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
Copy table structure from an access database into a new access database
Access table using vb.net
How to get the selected value in combo box and display it to datagridview ? using VB.NET and ms access database.
How to select from 2 tables VB.NET
Access tables in VB.net
How to copy access database tables to another access database in C#
How to select monthly data in vb.net from Access Database
How to truncate the table of access database using vb.net
Save all items of Listbox to access Database Table in VB.Net
Sql string in an access database

Advertise
Privacy
Cookies
Terms of Use
Last Updated 8 Jul 2019
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web03 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900