[crabara]

Hi, I'm trying to pass data between two forms, then using that data to call values from a database. Then use these values from the database, to populate static labels on the second form. Basically to give you an idea of the overall project, I'm having a user create a new job this gives it a job id I needed this job id to follow to the second form, hence the passing of data.

My problem however, is I can pass the data but the timing will mess up and unless I put a msgbox before fill the dataset, my query will come up empty. I'd rather not have a pointless message box in my program. Any ideas? The only error I get is "A first chance exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll"

So here is all the code.


Form1

    Option Explicit On
    Option Strict On
    Imports System.Data.OleDb

    Public Class assemblyForm
       Inherits System.Windows.Forms.Form
       Dim dbInsert As New OleDb.OleDbCommand
       Dim dbConnect As New OleDb.OleDbConnection
       Dim Line As String = Environment.NewLine
       Dim ds As New DataSet, ds2 As New DataSet, ds3 As New DataSet
       Dim da As OleDb.OleDbDataAdapter, da2 As OleDb.OleDbDataAdapter, da3 As OleDb.OleDbDataAdapter
       Dim run As Integer, pcr As Integer, JobId2 As Integer, jobNum As Integer

    Private Sub Form3_Load(sender As Object, e As System.EventArgs) Handles Me.Load

        Try
            'Open DB connection
            dbConnect.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\crabara\Desktop\Project Alpha 3\MDB.accdb;Persist Security Info=False;"
            dbConnect.Open()

            'Autopopulate PartNo. textbox
            da = New OleDb.OleDbDataAdapter("SELECT PartNumber FROM PCR_INFO", dbConnect)

            da.Fill(ds, "list")

            Dim col As New AutoCompleteStringCollection
            Dim i As Integer
            For i = 0 To ds.Tables(0).Rows.Count - 1
                col.Add(ds.Tables(0).Rows(i)("PartNumber").ToString())
            Next

            txtPart.AutoCompleteSource = AutoCompleteSource.CustomSource
            txtPart.AutoCompleteCustomSource = col
            txtPart.AutoCompleteMode = AutoCompleteMode.Suggest

        Catch ex As Exception
            MessageBox.Show(ex.Message + Line + "Main Database Not Found" + Line + "Check form_AccessMaintenance source code" + Line + "Database Path", "Critical Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Me.Close()
        End Try
    End Sub

    Private Sub btnSubmit_Click(sender As System.Object, e As System.EventArgs) Handles btnSubmit.Click
        'Increment the Run
        dbInsert.CommandText = "UPDATE PART_LIST SET Run = Run + 1 WHERE PartNumber='" & txtPart.Text & "'"
        dbInsert.CommandType = CommandType.Text
        dbInsert.Connection = dbConnect
        dbInsert.ExecuteNonQuery()

        'Gather Run from PART_LIST DB, put into variable
        da = New OleDb.OleDbDataAdapter("SELECT Run FROM PART_LIST WHERE PartNumber ='" & txtPart.Text & "'", dbConnect)

        da.Fill(ds, "Run")

        run = CInt(ds.Tables("Run").Rows(0).Item(0))

        'Gather PCR from PCR_INFO DB, put into variable

        da2 = New OleDb.OleDbDataAdapter("SELECT PCRNumber FROM PCR_INFO WHERE PartNumber ='" & txtPart.Text & "'", dbConnect)

        da2.Fill(ds2, "PCRNumber")

        pcr = CInt(ds2.Tables("PCRNumber").Rows(0).Item(0))



        'Set parameters for fields
        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "PartNumber"
        dbInsert.Parameters.Item("PartNumber").Value = txtPart.Text
        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "PCRNumber"
        dbInsert.Parameters.Item("PCRNumber").Value = pcr
        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "Run"
        dbInsert.Parameters.Item("Run").Value = run
        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "JobNo"
        dbInsert.Parameters.Item("JobNo").Value = txtJobNo.Text
       


        'Add Values into Assembly DB
        dbInsert.CommandText = "INSERT INTO Assembly(PartNumber,PCRNumber,Run,JobNo) VALUES(txtPart.Text,pcr,run,txtJobNo.Text);"
        dbInsert.CommandType = CommandType.Text
        dbInsert.Connection = dbConnect
        dbInsert.ExecuteNonQuery()
        MessageBox.Show("Job has been successfully submitted" + Line + txtPart.Text)


        'Gather JobID from new job

        da3 = New OleDb.OleDbDataAdapter("SELECT JobID FROM Assembly WHERE PartNumber ='" & txtPart.Text & "' AND JobNo ='" & txtJobNo.Text & "'", dbConnect)

        da3.Fill(ds3, "JobID")

        JobId2 = CInt(ds3.Tables("JobID").Rows(0).Item(0))

        dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "JobID"
        dbInsert.Parameters.Item("JobID").Value = JobId2


        'Add Values into Vinyl DB
        dbInsert.CommandText = "INSERT INTO    Molding(PartNumber,PCRNumber,Run,JobNo,JobID)   VALUES(txtPart.Text,pcr,run,txtJobNo.Text,JobId2);"
        dbInsert.CommandType = CommandType.Text
        dbInsert.Connection = dbConnect
        dbInsert.ExecuteNonQuery()

        'Loads the newly created jobid into the vinyl form
        'This is where the code that is important to the issue starts
        Dim anotherForm As Vinyl
        anotherForm = New Vinyl(JobId2)
        anotherForm.Show()
      

    End Sub

This is the form where all the trouble is
Form 2

Option Explicit On
    Option Strict On
    Public Class Vinyl

    Dim dbInsert As New OleDb.OleDbCommand
    Dim dbConnect As New OleDb.OleDbConnection
    Dim Line As String = Environment.NewLine
    Dim ds As New DataSet, ds2 As New DataSet
    Dim da As OleDb.OleDbDataAdapter, da2 As OleDb.OleDbDataAdapter
    Dim PartNumber As String, PartDescription As String
    Dim PCR As Integer, run As Integer, jobnumber As Integer
    Dim Pdescription As String

    ' Bring data from previous form and load the header
    Public Sub New(ByVal JobNum As Integer)
        MyBase.New()
        InitializeComponent()

        jobnumber = JobNum

    End Sub




    Private Sub Vinyl_Load(sender As Object, e As System.EventArgs) Handles Me.Load

        dbConnect.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\crabara\Desktop\Project Alpha 3\MDB.accdb;Persist Security Info=False;"
        dbConnect.Open()


        da = New OleDb.OleDbDataAdapter("SELECT PartNumber,PCRNumber,Run FROM Molding WHERE JobID ='" & jobnumber & "'", dbConnect)
        'Have to have a msgbox right here in order for this cod
        'MsgBox(jobnumber)
        da.Fill(ds, "Molding")


        PartNumber = CStr(ds.Tables("Molding").Rows(0).Item(0))
        PCR = CInt(ds.Tables("Molding").Rows(0).Item(1))
        run = CInt(ds.Tables("Molding").Rows(0).Item(2))

        lblPart.Text = PartNumber
        lblPCR.Text = CStr(PCR)
        lblRun.Text = CStr(run)

        da2 = New OleDb.OleDbDataAdapter("SELECT PartDescription FROM PART_LIST WHERE PartNumber ='" & PartNumber & "'", dbConnect)
        ' MsgBox(PartNumber)
        da2.Fill(ds2, "PartDescription")

        Pdescription = CStr((ds2.Tables("PartDescription").Rows(0).Item(0)))

        lblPartDescription.Text = CStr(Pdescription)
    End Sub
End Class 

[modi123_1]

Quote

My problem however, is I can pass the data but the timing will mess up and unless I put a msgbox before fill the dataset, my query will come up empty.

Explain this.. since code executes top down it should have all the values as they are assigned. Perhaps put a breakpoint after your insert and step through to make sure your job value is actually there.

[CharlieMay]

Yea, there's a lot of issues with your code.

What do you think this is doing?

dbInsert.CommandText = "INSERT INTO    Molding(PartNumber,PCRNumber,Run,JobNo,JobID)   VALUES(txtPart.Text,pcr,run,txtJobNo.Text,JobId2);"

I can tell you it's not doing what I think you think it's doing

094	    dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "JobID"
095	    dbInsert.Parameters.Item("JobID").Value = JobId2

Can you explain the use of this as a parameter. It appears in your statement that JobID is a field, but you treat it as a parameter. And then in the same statement concatenate the actual values instead of assigning them as parameters.

Also, I'm not 100% sure if you can define a parameter in this way in OLEDB, most people either use ? or @someWord as a parameter making it easy to see that these are not database objects in the sense of being a column name, table name, or syntax.

This code should be throwing database exceptions left and right.

[crabara]

modi123_1, on 03 March 2014 - 01:33 PM, said:

Quote

My problem however, is I can pass the data but the timing will mess up and unless I put a msgbox before fill the dataset, my query will come up empty.

Explain this.. since code executes top down it should have all the values as they are assigned. Perhaps put a breakpoint after your insert and step through to make sure your job value is actually there.

I've put breakpoints in and after where the error is happening. The thing is, when I run the debugging and step into it, the code works as intended. It just when I run it live, it doesn't work unless I have the messagebox. Someone on another forum told me it may be a timing issue.

CharlieMay, on 03 March 2014 - 05:28 PM, said:

Yea, there's a lot of issues with your code.

What do you think this is doing?

dbInsert.CommandText = "INSERT INTO    Molding(PartNumber,PCRNumber,Run,JobNo,JobID)   VALUES(txtPart.Text,pcr,run,txtJobNo.Text,JobId2);"

I can tell you it's not doing what I think you think it's doing

094	    dbInsert.Parameters.Add(dbInsert.CreateParameter).ParameterName = "JobID"
095	    dbInsert.Parameters.Item("JobID").Value = JobId2

Can you explain the use of this as a parameter. It appears in your statement that JobID is a field, but you treat it as a parameter. And then in the same statement concatenate the actual values instead of assigning them as parameters.

Also, I'm not 100% sure if you can define a parameter in this way in OLEDB, most people either use ? or @someWord as a parameter making it easy to see that these are not database objects in the sense of being a column name, table name, or syntax.

This code should be throwing database exceptions left and right.

Well the above code is in fact doing what I think it's doing. I know this because I've ran it over 200 times and the database is filling appropriately. However, the use of the parameters is a code snippet I got while figuring out how to do this. I know my code is sloppy and unrefined, I'm a college student and an intern doing this project with zero guidance except for what I can find on the internet. This has been quite the learning experience, so if you have the correct way I should format that, I would love to see it. That is the one piece of code I can't fully explain.

But yea, I'm not getting any database exceptions and actually that end of it is running great. However, if you have any advice on how to prevent future errors, I'm all ears. I'm open to learning, because I got a lot of it to do

[CharlieMay]

You're telling me that when this piece of code execute

dbInsert.CommandText = "INSERT INTO    Molding(PartNumber,PCRNumber,Run,JobNo,JobID)   VALUES(txtPart.Text,pcr,run,txtJobNo.Text,JobId2);"
  dbInsert.CommandType = CommandType.Text
  dbInsert.Connection = dbConnect
  dbInsert.ExecuteNonQuery()

That you don't get a syntax error when the parser hits txtPart.Text? Because, i'm pretty sure it would.

Keep in mind, this isn't a concatenation of what you have stored in the .Text field of txtPart

[crabara]

CharlieMay, on 04 March 2014 - 08:32 AM, said:

You're telling me that when this piece of code execute

dbInsert.CommandText = "INSERT INTO    Molding(PartNumber,PCRNumber,Run,JobNo,JobID)   VALUES(txtPart.Text,pcr,run,txtJobNo.Text,JobId2);"
  dbInsert.CommandType = CommandType.Text
  dbInsert.Connection = dbConnect
  dbInsert.ExecuteNonQuery()

That you don't get a syntax error when the parser hits txtPart.Text? Because, i'm pretty sure it would.

Keep in mind, this isn't a concatenation of what you have stored in the .Text field of txtPart

No, it runs great like I said over 200 times now, and it gives me exactly the values I want. I do believe it is a concatenation of the .Text Field. I'm getting the correct values, it is a unique number and looking at the database it would be obvious.

I'm new, so what exactly is the problem? Should they be stored into variables? Is that the issue? I'm confused. I know someone on another form didn't like that insert statement either and I had a little trouble with it, but mostly because I had it out of order and now it's working good. Yea, I would love to know the actual issue or maybe it's just a different way to do it? I'm sure, this is literally the biggest program I've ever written.

[crabara]

mod: removed bulk quote

I'm actually headed to a job interview for a VB.net dev internship, I was gonna print out some of this code as an example of what I can do. Is this code terrible? Should I not showcase it? It's bulk of my experience.

This post has been edited by modi123_1: 04 March 2014 - 09:23 AM
Reason for edit:: let's not quote your own post right above.

[crabara]

I figured it out I just added dbconnect.close() to the end of form1 particularly after the dbinsert.executeNonquery(0

[CharlieMay]

crabara, there's a topic in the advanced forum on why your statements are working. You may want to look at fixing those up so that they are apparent to anyone reading the code what is actually happening. I now understand how your code is working. Your statements are actually using a method of insert where the fields are not specified and that is what is happening behind the scenes to your statements (or at least an educated guess). If you are going to have someone look at this in hopes of getting an internship, I would fix them by at least removing the Values (...) part of the insert statement. I prefer to always specify my fields and values in the statements as it removes a lot of potential errors.