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:
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