12 Replies - 1282 Views - Last Post: 29 April 2014 - 05:49 PM Rate Topic: -----

#1 larnth  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 28-April 14

Need help with Vb.net to SQL communication.

Posted 28 April 2014 - 06:59 PM

Imports System.Data.OleDb

Public Class StudentApplicationForm

    Private Sub SubmitButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SubmitButton.Click
        Dim DataConnect As New OleDbConnection("SCHOOL INFORMATION HERE")
        'Try catch so we don't crash (>")>   (>"<)   <("<)
        Try

            Dim Query As String
            Dim Query2 As String
            Dim Query5 As String
            Dim command As New OleDbCommand()
            Dim SID As Integer
            Dim AppID As Integer
            Dim PID As Integer
            Dim DataRead As OleDbDataReader
            Dim DataRead1 As OleDbDataReader
            Dim query4 As String
            Dim query1 As String

            DataConnect.Open()
            Query = "INSERT INTO Student_T (StudentFName, StudentMName, StudentLName, SSN, DOB, Citizenship, Ethnicity, Gender, StudentCellNum, StudentWorkNum, StudentHomeNum, StudentEmail, ProgramEnrollmentStatus, TermGPA, CumulativeGPA, GradStatus, GradDate, SpecialNotes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,'-',0,0,'N','-','-');"
            command.Connection = DataConnect
            command.CommandText = Query
            command.Parameters.AddWithValue("StudentFName", txtFirstName.Text)
            command.Parameters.AddWithValue("StudentMName", txtMiddleName.Text.ToString)
            command.Parameters.AddWithValue("StudentLName", txtLastName.Text)
            command.Parameters.AddWithValue("SSN", txtSSN.Text)
            command.Parameters.AddWithValue("DOB", txtBirthday1.Text)
            command.Parameters.AddWithValue("Citizenship", cbxCitizenship.Text)
            command.Parameters.AddWithValue("Ethnicity", cbxEthnic.Text)
            command.Parameters.AddWithValue("Gender", cbxGender.Text)
            command.Parameters.AddWithValue("StudentCellNum", txtCPhone.Text)
            command.Parameters.AddWithValue("StudentWorkNum", txtWPhone.Text)
            command.Parameters.AddWithValue("StudentHomeNum", txtHPhone.Text)
            command.Parameters.AddWithValue("StudentEmail", txtEmail.Text)
            command.ExecuteNonQuery()



            query1 = "SELECT StudentID FROM Student_T WHERE StudentFName = '" & txtFirstName.Text.ToString() & "' AND StudentLName = '" & txtLastName.Text.ToString() & "';"
            command.Connection = DataConnect
            command.CommandText = query1
            DataRead = command.ExecuteReader()
            If DataRead.Read() Then
                SID = DataRead("StudentID")
            End If

            If cbxDegreeSeeking.Text = "Professional MIS" Then
                PID = 1
            End If

            Query2 = "INSERT INTO Aptitude_T (GMATVerbal, GMATQuantitative, GMATTotal, GMATDate, GRETotal, GREDate, TOEFLTotal, TOEFLDate, StudentID) VALUES (?,?,?,?,?,?,?,?, " & SID & "); INSERT INTO Education_T (StudentID, CollegeName, DegreeFocus, DegreeType, DegreeGPA, YearsAttended) VALUES (" & SID & ",?,?,?,?,?); INSERT INTO Application_T (DegreeSeeking, DesiredSession, FTPTStatus, DateApplied, AssistantshipRequest, AssistantshipStatus, OfferStatus, OfferDate, StudentID, ProgramID) VALUES (?,?,?,?,'-','-','No','-'," & SID & "," & PID & "); INSERT INTO Address_T (StreetAddress1, AddressCountry, AddressstateorProvince, AddressZipeCode, AddressCity, StudentID) VALUES (?,?,?,?,?," & SID & ");"
            command.Connection = DataConnect
            command.CommandText = Query2
            command.Parameters.AddWithValue("GMATVerbal", txtGMATVerbal.Text)
            command.Parameters.AddWithValue("GMATQuantitative", txtGMATQuant.Text)
            command.Parameters.AddWithValue("GMATTotal", txtGMATTotal.Text)
            command.Parameters.AddWithValue("GMATDate", txtGMATDate.Text)
            command.Parameters.AddWithValue("GRETotal", txtGRETotal.Text)
            command.Parameters.AddWithValue("GREDate", txtGREDate.Text)
            command.Parameters.AddWithValue("TOEFLTotal", txtTOEFLTotal.Text)
            command.Parameters.AddWithValue("TOEFLDate", txtTOEFLDate.Text)

            command.Parameters.AddWithValue("CollegeName", txtCName1.Text)
            command.Parameters.AddWithValue("DegreeFocus", txtMajor1.Text)
            command.Parameters.AddWithValue("DegreeType", cbxDegree1.Text)
            command.Parameters.AddWithValue("DegreeGPA", txtCGPA1.Text)
            command.Parameters.AddWithValue("YearsAttended", txtCFrom1.Text)

            command.Parameters.AddWithValue("DegreeSeeking", cbxDegreeSeeking.Text)
            command.Parameters.AddWithValue("DesiredSession", cbxTerm.Text)
            command.Parameters.AddWithValue("FTPTStatus", FTPTComboBox.Text)
            command.Parameters.AddWithValue("DateApplied", AppDateTextBox.Text)

            command.Parameters.AddWithValue("StreetAddress1", txtPAddress.Text)
            command.Parameters.AddWithValue("AddressCountry", cbxPCountry.Text)
            command.Parameters.AddWithValue("AddressStateorProvince", cbxPState.Text)
            command.Parameters.AddWithValue("AddressZipCode", txtPZipCode.Text)
            command.Parameters.AddWithValue("AddressCity", txtPCity.Text)
            command.ExecuteNonQuery()


            query4 = "SELECT ApplicationID FROM Application_T WHERE StudentID = " & SID & ";"
            command.Connection = DataConnect
            command.CommandText = query4
            DataRead1 = command.ExecuteReader()
            If DataRead1.Read() Then
                AppID = DataRead1("ApplicationID")
            End If


            Query5 = "INSERT INTO Checklist_T (AppFeeStatus, ResumeStatus, TranscriptStatus, EssayStatus, GMAT_GREStatus, TOEFLStatus, SummaryStatus, SupFinStatus, ApplicationID) VALUES (?,?,?,?,?,?,?,?," & AppID & ");"
            command.Connection = DataConnect
            command.CommandText = Query5
            If chkAppFeePaid.Checked = True Then
                command.Parameters.AddWithValue("AppFeeStatus", "Y")
            Else
                command.Parameters.AddWithValue("AppFeeStatus", "N")
            End If
            If chkResume.Checked = True Then
                command.Parameters.AddWithValue("ResumeStatus", "Y")
            Else
                command.Parameters.AddWithValue("ResumeStatus", "N")
            End If
            If chkTranscript.Checked = True Then
                command.Parameters.AddWithValue("TranscriptStatus", "Y")
            Else
                command.Parameters.AddWithValue("TranscriptStatus", "N")
            End If
            If chkEssay.Checked = True Then
                command.Parameters.AddWithValue("EssayStatus", "Y")
            Else
                command.Parameters.AddWithValue("EssayStatus", "N")
            End If
            If chkGMATScore.Checked = True Then
                command.Parameters.AddWithValue("GMAT_GREStatus", "Y")
            Else
                command.Parameters.AddWithValue("GMAT_GREStatus", "N")
            End If
            If chkTOEFL.Checked = True Then
                command.Parameters.AddWithValue("TOEFLStatus", "Y")
            Else
                command.Parameters.AddWithValue("TOEFLStatus", "N")
            End If
            If chkEducation.Checked = True Then
                command.Parameters.AddWithValue("SummaryStatus", "Y")
            Else
                command.Parameters.AddWithValue("SummaryStatus", "N")
            End If
            If chkFinancial.Checked = True Then
                command.Parameters.AddWithValue("SupFinStatus", "Y")
            Else
                command.Parameters.AddWithValue("SupFinStatus", "N")
            End If
            command.ExecuteNonQuery()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            MessageBox.Show("Successfully applied.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
            'Close the connection, regardles of the rest - with or without errors.
            If DataConnect.State = ConnectionState.Open Then DataConnect.Close()
        End Try
    End Sub
End Class




That's the block I'm currently working with. The error(s) I'm getting are "There is already an open DataReader associated with this Command which must be closed first." and with some alterations "Command Parameter [22]" is invalid." and this error is followed by 11 other errors doing numbers 23-33.

I'm at a loss here, please offer any help you can.

Is This A Good Question/Topic? 0
  • +

Replies To: Need help with Vb.net to SQL communication.

#2 ebolisa  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 150
  • Joined: 22-September 09

Re: Need help with Vb.net to SQL communication.

Posted 29 April 2014 - 01:46 AM

I didnít test your code but you should try to make it neater for easy reading and troubleshooting.

Try to separate the steps into subs or functions, see code below as an idea.

Open and close your connections

Step through the code to see where it fails.

Done that, Iím sure youíll find the problem in a short time.

Imports System.Data.OleDb

Public Class StudentApplicationForm

    Dim DataConnect As New OleDbConnection("SCHOOL INFORMATION HERE")
    Dim Query As String
    Dim Query2 As String
    Dim Query5 As String
    Dim command As New OleDbCommand()
    Dim SID As Integer
    Dim AppID As Integer
    Dim PID As Integer
    Dim DataRead As OleDbDataReader
    Dim DataRead1 As OleDbDataReader
    Dim query4 As String
    Dim query1 As String

    Private Sub SubmitButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SubmitButton.Click

        'update student table
        updateStudentTable()

        'get student ID
        getStudentID()

        'update aptitude
        updateAptitude()

        'get application ID
        getApplicationID()

        'update check list table
        updateCheckListTable()


    End Sub

    Private Sub updateStudentTable()
        'Try catch so we don't crash (>")>   (>"<)   <("<)
        Try

            DataConnect.Open()
            Query = "INSERT INTO Student_T (StudentFName, StudentMName, StudentLName, SSN, DOB, Citizenship, Ethnicity, " _
                & "Gender, StudentCellNum, StudentWorkNum, StudentHomeNum, StudentEmail, ProgramEnrollmentStatus, TermGPA, " _
                & "CumulativeGPA, GradStatus, GradDate, SpecialNotes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,'-',0,0,'N','-','-');"

            command.Connection = DataConnect
            command.CommandText = Query
            command.Parameters.AddWithValue("StudentFName", txtFirstName.Text)
            command.Parameters.AddWithValue("StudentMName", txtMiddleName.Text.ToString)
            command.Parameters.AddWithValue("StudentLName", txtLastName.Text)
            command.Parameters.AddWithValue("SSN", txtSSN.Text)
            command.Parameters.AddWithValue("DOB", txtBirthday1.Text)
            command.Parameters.AddWithValue("Citizenship", cbxCitizenship.Text)
            command.Parameters.AddWithValue("Ethnicity", cbxEthnic.Text)
            command.Parameters.AddWithValue("Gender", cbxGender.Text)
            command.Parameters.AddWithValue("StudentCellNum", txtCPhone.Text)
            command.Parameters.AddWithValue("StudentWorkNum", txtWPhone.Text)
            command.Parameters.AddWithValue("StudentHomeNum", txtHPhone.Text)
            command.Parameters.AddWithValue("StudentEmail", txtEmail.Text)

            command.ExecuteNonQuery()

            DataConnect.Close()
            command.Dispose()

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

    End Sub

    Private Sub getStudentID()

        Try

            query1 = "SELECT StudentID FROM Student_T WHERE StudentFName = '" & txtFirstName.Text.ToString() & "' AND StudentLName = '" & txtLastName.Text.ToString() & "';"
            command.Connection = DataConnect
            command.CommandText = query1
            DataRead = command.ExecuteReader()
            If DataRead.Read() Then
                SID = DataRead("StudentID")
            End If

            If cbxDegreeSeeking.Text = "Professional MIS" Then
                PID = 1
            End If

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub updateAptitude()

        Try

            Query2 = "INSERT INTO Aptitude_T (GMATVerbal, GMATQuantitative, GMATTotal, GMATDate, GRETotal, GREDate, TOEFLTotal, TOEFLDate, StudentID) VALUES (?,?,?,?,?,?,?,?, " & SID & "); INSERT INTO Education_T (StudentID, CollegeName, DegreeFocus, DegreeType, DegreeGPA, YearsAttended) VALUES (" & SID & ",?,?,?,?,?); INSERT INTO Application_T (DegreeSeeking, DesiredSession, FTPTStatus, DateApplied, AssistantshipRequest, AssistantshipStatus, OfferStatus, OfferDate, StudentID, ProgramID) VALUES (?,?,?,?,'-','-','No','-'," & SID & "," & PID & "); INSERT INTO Address_T (StreetAddress1, AddressCountry, AddressstateorProvince, AddressZipeCode, AddressCity, StudentID) VALUES (?,?,?,?,?," & SID & ");"
            command.Connection = DataConnect
            command.CommandText = Query2
            command.Parameters.AddWithValue("GMATVerbal", txtGMATVerbal.Text)
            command.Parameters.AddWithValue("GMATQuantitative", txtGMATQuant.Text)
            command.Parameters.AddWithValue("GMATTotal", txtGMATTotal.Text)
            command.Parameters.AddWithValue("GMATDate", txtGMATDate.Text)
            command.Parameters.AddWithValue("GRETotal", txtGRETotal.Text)
            command.Parameters.AddWithValue("GREDate", txtGREDate.Text)
            command.Parameters.AddWithValue("TOEFLTotal", txtTOEFLTotal.Text)
            command.Parameters.AddWithValue("TOEFLDate", txtTOEFLDate.Text)

            command.Parameters.AddWithValue("CollegeName", txtCName1.Text)
            command.Parameters.AddWithValue("DegreeFocus", txtMajor1.Text)
            command.Parameters.AddWithValue("DegreeType", cbxDegree1.Text)
            command.Parameters.AddWithValue("DegreeGPA", txtCGPA1.Text)
            command.Parameters.AddWithValue("YearsAttended", txtCFrom1.Text)

            command.Parameters.AddWithValue("DegreeSeeking", cbxDegreeSeeking.Text)
            command.Parameters.AddWithValue("DesiredSession", cbxTerm.Text)
            command.Parameters.AddWithValue("FTPTStatus", FTPTComboBox.Text)
            command.Parameters.AddWithValue("DateApplied", AppDateTextBox.Text)

            command.Parameters.AddWithValue("StreetAddress1", txtPAddress.Text)
            command.Parameters.AddWithValue("AddressCountry", cbxPCountry.Text)
            command.Parameters.AddWithValue("AddressStateorProvince", cbxPState.Text)
            command.Parameters.AddWithValue("AddressZipCode", txtPZipCode.Text)
            command.Parameters.AddWithValue("AddressCity", txtPCity.Text)

            command.ExecuteNonQuery()
            command.Dispose()

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

    End Sub

    Private Sub getApplicationID()

        Try

            query4 = "SELECT ApplicationID FROM Application_T WHERE StudentID = " & SID & ";"
            command.Connection = DataConnect
            command.CommandText = query4
            DataRead1 = command.ExecuteReader()

            If DataRead1.Read() Then
                AppID = DataRead1("ApplicationID")
            End If

            command.Dispose()

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


    End Sub

    Private Sub updateCheckListTable()

        Try
            Query5 = "INSERT INTO Checklist_T (AppFeeStatus, ResumeStatus, TranscriptStatus, EssayStatus, GMAT_GREStatus, TOEFLStatus, SummaryStatus, SupFinStatus, ApplicationID) VALUES (?,?,?,?,?,?,?,?," & AppID & ");"
            command.Connection = DataConnect
            command.CommandText = Query5
            If chkAppFeePaid.Checked = True Then
                command.Parameters.AddWithValue("AppFeeStatus", "Y")
            Else
                command.Parameters.AddWithValue("AppFeeStatus", "N")
            End If
            If chkResume.Checked = True Then
                command.Parameters.AddWithValue("ResumeStatus", "Y")
            Else
                command.Parameters.AddWithValue("ResumeStatus", "N")
            End If
            If chkTranscript.Checked = True Then
                command.Parameters.AddWithValue("TranscriptStatus", "Y")
            Else
                command.Parameters.AddWithValue("TranscriptStatus", "N")
            End If
            If chkEssay.Checked = True Then
                command.Parameters.AddWithValue("EssayStatus", "Y")
            Else
                command.Parameters.AddWithValue("EssayStatus", "N")
            End If
            If chkGMATScore.Checked = True Then
                command.Parameters.AddWithValue("GMAT_GREStatus", "Y")
            Else
                command.Parameters.AddWithValue("GMAT_GREStatus", "N")
            End If
            If chkTOEFL.Checked = True Then
                command.Parameters.AddWithValue("TOEFLStatus", "Y")
            Else
                command.Parameters.AddWithValue("TOEFLStatus", "N")
            End If
            If chkEducation.Checked = True Then
                command.Parameters.AddWithValue("SummaryStatus", "Y")
            Else
                command.Parameters.AddWithValue("SummaryStatus", "N")
            End If
            If chkFinancial.Checked = True Then
                command.Parameters.AddWithValue("SupFinStatus", "Y")
            Else
                command.Parameters.AddWithValue("SupFinStatus", "N")
            End If

            command.ExecuteNonQuery()
            command.Dispose()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            MessageBox.Show("Successfully applied.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
            'Close the connection, regardles of the rest - with or without errors.
            If DataConnect.State = ConnectionState.Open Then DataConnect.Close()
        End Try
    End Sub


End Class


Was This Post Helpful? 1
  • +
  • -

#3 belgarion262  Icon User is offline

  • Prince of all (2) Saiyans

Reputation: 163
  • View blog
  • Posts: 947
  • Joined: 25-October 09

Re: Need help with Vb.net to SQL communication.

Posted 29 April 2014 - 02:30 AM

I got nothing, but had to comment on your comment.

(>")> (>"<) <("<)

Love it.

This post has been edited by belgarion262: 29 April 2014 - 02:31 AM

Was This Post Helpful? 0
  • +
  • -

#4 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Need help with Vb.net to SQL communication.

Posted 29 April 2014 - 04:13 AM

since the datareader is a forward only stream, there is really no reason to declare it globally. Move your datareader into the sub(s) where they are needed. Instantiate them, fill them, read through the results and close them.

Also, in your GetStudentID() sub, since your select statement is returning a single row and column, you could remove the overhead of the reader and use the ExecuteScalar method to retrieve that single field of information.

Also, that INSERT statement on line 54 is going to need to be broken up into separate insert statements. Pretty sure you can't do that with OLEDB. The parameters will have to created for each separate statement too.

EDIT:
Sorry, I was looking at the code ebolisa posted but the problems are the same, they are just referenced against that code instead of yours.

This post has been edited by CharlieMay: 29 April 2014 - 05:27 AM

Was This Post Helpful? 1
  • +
  • -

#5 larnth  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 28-April 14

Re: Need help with Vb.net to SQL communication.

Posted 29 April 2014 - 02:03 PM

View PostCharlieMay, on 29 April 2014 - 04:13 AM, said:

since the datareader is a forward only stream, there is really no reason to declare it globally. Move your datareader into the sub(s) where they are needed. Instantiate them, fill them, read through the results and close them.


It's been a while since I've taken any courses on VB.Net and I don't have a lot of time to do too much searching (the little I did didn't help with syntax and placement of lines of code, could you give me an example of how to do this so I can implement this change?

Thanks so much for your help so far.

View PostCharlieMay, on 29 April 2014 - 04:13 AM, said:

Also, that INSERT statement on line 54 is going to need to be broken up into separate insert statements. Pretty sure you can't do that with OLEDB. The parameters will have to created for each separate statement too.


For this can I just make multiple queries and run them back to back? How could I best do this?

View Postebolisa, on 29 April 2014 - 01:46 AM, said:

I didnít test your code but you should try to make it neater for easy reading and troubleshooting.

Try to separate the steps into subs or functions, see code below as an idea.

Open and close your connections

Step through the code to see where it fails.

Done that, Iím sure youíll find the problem in a short time.


Thank you for your work at altering the layout of my code. I haven't done any coding in VB.Net in a while (been working on more algorithmic courses for data analytics lately) and have been just re-using old code from other programs!
Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Need help with Vb.net to SQL communication.

Posted 29 April 2014 - 02:06 PM

Basically, it's what you have for the most part. Oledb will not run multiple SQL commands in a single statement, meaning, you cannot separate commands using a ; like you can in SQLServer and other more robust database engines. This means that you will need a separate block of code and parameters for each INSERT INTO that you have in the group of statements.

Executing one, and then moving on to the next.

You have the code, you just need to break it out and rearrange it.
Was This Post Helpful? 1
  • +
  • -

#7 larnth  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 28-April 14

Re: Need help with Vb.net to SQL communication.

Posted 29 April 2014 - 02:10 PM

View PostCharlieMay, on 29 April 2014 - 02:06 PM, said:

Basically, it's what you have for the most part. Oledb will not run multiple SQL commands in a single statement, meaning, you cannot separate commands using a ; like you can in SQLServer and other more robust database engines. This means that you will need a separate block of code and parameters for each INSERT INTO that you have in the group of statements.

Executing one, and then moving on to the next.

You have the code, you just need to break it out and rearrange it.


So something like this should be functional?

'Try catch so we don't crash (>")>   (>"<)   <("<)
        Try
            DataConnect.Open()
            Query = "INSERT INTO Student_T (StudentFName, StudentMName, StudentLName, SSN, DOB, Citizenship, Ethnicity, " _
                & "Gender, StudentCellNum, StudentWorkNum, StudentHomeNum, StudentEmail, ProgramEnrollmentStatus, TermGPA, " _
                & "CumulativeGPA, GradStatus, GradDate, SpecialNotes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,'-',0,0,'N','-','-');"

            command.Connection = DataConnect
            command.CommandText = Query
            command.Parameters.AddWithValue("StudentFName", txtFirstName.Text)
            command.Parameters.AddWithValue("StudentMName", txtMiddleName.Text.ToString)
            command.Parameters.AddWithValue("StudentLName", txtLastName.Text)
            command.Parameters.AddWithValue("SSN", txtSSN.Text)
            command.Parameters.AddWithValue("DOB", txtBirthday1.Text)
            command.Parameters.AddWithValue("Citizenship", cbxCitizenship.Text)
            command.Parameters.AddWithValue("Ethnicity", cbxEthnic.Text)
            command.Parameters.AddWithValue("Gender", cbxGender.Text)
            command.Parameters.AddWithValue("StudentCellNum", txtCPhone.Text)
            command.Parameters.AddWithValue("StudentWorkNum", txtWPhone.Text)
            command.Parameters.AddWithValue("StudentHomeNum", txtHPhone.Text)
            command.Parameters.AddWithValue("StudentEmail", txtEmail.Text)

            
Query2 = "INSERT INTO Aptitude_T (GMATVerbal, GMATQuantitative, GMATTotal, GMATDate, GRETotal, GREDate, TOEFLTotal, TOEFLDate, StudentID) VALUES (?,?,?,?,?,?,?,?, " & SID & ");"
            command.Connection = DataConnect
            command.CommandText = Query2
            command.Parameters.AddWithValue("GMATVerbal", txtGMATVerbal.Text)
            command.Parameters.AddWithValue("GMATQuantitative", txtGMATQuant.Text)
            command.Parameters.AddWithValue("GMATTotal", txtGMATTotal.Text)
            command.Parameters.AddWithValue("GMATDate", txtGMATDate.Text)
            command.Parameters.AddWithValue("GRETotal", txtGRETotal.Text)
            command.Parameters.AddWithValue("GREDate", txtGREDate.Text)
            command.Parameters.AddWithValue("TOEFLTotal", txtTOEFLTotal.Text)
            command.Parameters.AddWithValue("TOEFLDate", txtTOEFLDate.Text)
command.ExecuteNonQuery()
            DataConnect.Close()
            command.Dispose()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try




Or have I mis-done something?
Was This Post Helpful? 0
  • +
  • -

#8 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Need help with Vb.net to SQL communication.

Posted 29 April 2014 - 02:18 PM

Yes, but you MAY need to clear your previous parameters since they are all being added to the same command object.
Was This Post Helpful? 1
  • +
  • -

#9 larnth  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 28-April 14

Re: Need help with Vb.net to SQL communication.

Posted 29 April 2014 - 02:45 PM

View PostCharlieMay, on 29 April 2014 - 02:18 PM, said:

Yes, but you MAY need to clear your previous parameters since they are all being added to the same command object.


Imports System.Data.OleDb

Public Class StudentApplicationForm

    Dim DataConnect As New OleDbConnection("Connection Info")
    Dim Query As String
    Dim Query2 As String
    Dim Query5 As String
    Dim command As New OleDbCommand()
    Dim SID As Integer
    Dim AppID As Integer
    Dim PID As Integer
    Dim query4 As String
    Dim query1 As String

    Private Sub SubmitButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SubmitButton.Click
        'update student table
        updateStudentTable()
        'get student ID
        getStudentID()
        'update aptitude
        updateAptitude()
        'get application ID
        getApplicationID()
        'update check list table
        updateCheckListTable()

    End Sub

    Private Sub updateStudentTable()
        'Try catch so we don't crash (>")>   (>"<)   <("<)
        Try
            DataConnect.Open()
            Query = "INSERT INTO Student_T (StudentFName, StudentMName, StudentLName, SSN, DOB, Citizenship, Ethnicity, " _
                & "Gender, StudentCellNum, StudentWorkNum, StudentHomeNum, StudentEmail, ProgramEnrollmentStatus, TermGPA, " _
                & "CumulativeGPA, GradStatus, GradDate, SpecialNotes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,'-',0,0,'N','-','-');"

            command.Connection = DataConnect
            command.CommandText = Query
            command.Parameters.AddWithValue("StudentFName", txtFirstName.Text)
            command.Parameters.AddWithValue("StudentMName", txtMiddleName.Text.ToString)
            command.Parameters.AddWithValue("StudentLName", txtLastName.Text)
            command.Parameters.AddWithValue("SSN", txtSSN.Text)
            command.Parameters.AddWithValue("DOB", txtBirthday1.Text)
            command.Parameters.AddWithValue("Citizenship", cbxCitizenship.Text)
            command.Parameters.AddWithValue("Ethnicity", cbxEthnic.Text)
            command.Parameters.AddWithValue("Gender", cbxGender.Text)
            command.Parameters.AddWithValue("StudentCellNum", txtCPhone.Text)
            command.Parameters.AddWithValue("StudentWorkNum", txtWPhone.Text)
            command.Parameters.AddWithValue("StudentHomeNum", txtHPhone.Text)
            command.Parameters.AddWithValue("StudentEmail", txtEmail.Text)
            command.Parameters.Clear()
            command.ExecuteNonQuery()

            command.Dispose()
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            If DataConnect.State = ConnectionState.Open Then DataConnect.Close()
        End Try
    End Sub
    Private Sub getStudentID()

        Dim DataRead As OleDbDataReader
        Try
            DataConnect.Open()
            query1 = "SELECT StudentID FROM Student_T WHERE StudentFName = '" & txtFirstName.Text.ToString() & "' AND StudentLName = '" & txtLastName.Text.ToString() & "';"
            command.Connection = DataConnect
            command.CommandText = query1
            DataRead = command.ExecuteReader()
            If DataRead.Read() Then
                SID = DataRead("StudentID")
            End If

            If cbxDegreeSeeking.Text = "Professional MIS" Then
                PID = 1
            End If
            command.Dispose()
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            If DataConnect.State = ConnectionState.Open Then DataConnect.Close()
        End Try
    End Sub
    Private Sub updateAptitude()
        Try
            DataConnect.Open()
            Query2 = "INSERT INTO Aptitude_T (GMATVerbal, GMATQuantitative, GMATTotal, GMATDate, GRETotal, GREDate, TOEFLTotal, TOEFLDate, StudentID) VALUES (?,?,?,?,?,?,?,?, " & SID & ");"
            command.Connection = DataConnect
            command.CommandText = Query2
            command.Parameters.AddWithValue("GMATVerbal", txtGMATVerbal.Text)
            command.Parameters.AddWithValue("GMATQuantitative", txtGMATQuant.Text)
            command.Parameters.AddWithValue("GMATTotal", txtGMATTotal.Text)
            command.Parameters.AddWithValue("GMATDate", txtGMATDate.Text)
            command.Parameters.AddWithValue("GRETotal", txtGRETotal.Text)
            command.Parameters.AddWithValue("GREDate", txtGREDate.Text)
            command.Parameters.AddWithValue("TOEFLTotal", txtTOEFLTotal.Text)
            command.Parameters.AddWithValue("TOEFLDate", txtTOEFLDate.Text)
            command.Parameters.Clear()

            Dim Query3 As String
            Query3 = "INSERT INTO Education_T (StudentID, CollegeName, DegreeFocus, DegreeType, DegreeGPA, YearsAttended) VALUES (" & SID & ",?,?,?,?,?);"
            command.Connection = DataConnect
            command.CommandText = Query3
            command.Parameters.AddWithValue("CollegeName", txtCName1.Text)
            command.Parameters.AddWithValue("DegreeFocus", txtMajor1.Text)
            command.Parameters.AddWithValue("DegreeType", cbxDegree1.Text)
            command.Parameters.AddWithValue("DegreeGPA", txtCGPA1.Text)
            command.Parameters.AddWithValue("YearsAttended", txtCFrom1.Text)
            command.Parameters.Clear()

            Dim query4 As String
            query4 = "INSERT INTO Application_T (DegreeSeeking, DesiredSession, FTPTStatus, DateApplied, AssistantshipRequest, AssistantshipStatus, OfferStatus, OfferDate, StudentID, ProgramID) VALUES (?,?,?,?,'-','-','No','-'," & SID & "," & PID & ");"
            command.Connection = DataConnect
            command.CommandText = query4
            command.Parameters.AddWithValue("DegreeSeeking", cbxDegreeSeeking.Text)
            command.Parameters.AddWithValue("DesiredSession", cbxTerm.Text)
            command.Parameters.AddWithValue("FTPTStatus", FTPTComboBox.Text)
            command.Parameters.AddWithValue("DateApplied", AppDateTextBox.Text)

            Dim query5 As String
            query5 = "INSERT INTO Address_T (StreetAddress1, AddressCountry, AddressstateorProvince, AddressZipCode, AddressCity, StudentID) VALUES (?,?,?,?,?," & SID & ")"
            command.Connection = DataConnect
            command.CommandText = query5
            command.Parameters.AddWithValue("StreetAddress1", txtPAddress.Text)
            command.Parameters.AddWithValue("AddressCountry", cbxPCountry.Text)
            command.Parameters.AddWithValue("AddressStateorProvince", cbxPState.Text)
            command.Parameters.AddWithValue("AddressZipCode", txtPZipCode.Text)
            command.Parameters.AddWithValue("AddressCity", txtPCity.Text)
            command.ExecuteNonQuery()
            command.Dispose()
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            If DataConnect.State = ConnectionState.Open Then DataConnect.Close()
        End Try
    End Sub

    Private Sub getApplicationID()
        Dim DataRead As OleDbDataReader
        Try
            DataConnect.Open()
            query4 = "SELECT ApplicationID FROM Application_T WHERE StudentID = " & SID & ";"
            command.Connection = DataConnect
            command.CommandText = query4
            DataRead = command.ExecuteReader()
            If DataRead.Read() Then
                AppID = DataRead("ApplicationID")
            End If
            DataConnect.Close()
            command.Dispose()
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            If DataConnect.State = ConnectionState.Open Then DataConnect.Close()
        End Try
    End Sub
    Private Sub updateCheckListTable()
        Try
            DataConnect.Open()
            Query5 = "INSERT INTO Checklist_T (AppFeeStatus, ResumeStatus, TranscriptStatus, EssayStatus, GMAT_GREStatus, TOEFLStatus, SummaryStatus, SupFinStatus, ApplicationID) VALUES (?,?,?,?,?,?,?,?," & AppID & ");"
            command.Connection = DataConnect
            command.CommandText = Query5
            If chkAppFeePaid.Checked = True Then
                command.Parameters.AddWithValue("AppFeeStatus", "Y")
            Else
                command.Parameters.AddWithValue("AppFeeStatus", "N")
            End If
            If chkResume.Checked = True Then
                command.Parameters.AddWithValue("ResumeStatus", "Y")
            Else
                command.Parameters.AddWithValue("ResumeStatus", "N")
            End If
            If chkTranscript.Checked = True Then
                command.Parameters.AddWithValue("TranscriptStatus", "Y")
            Else
                command.Parameters.AddWithValue("TranscriptStatus", "N")
            End If
            If chkEssay.Checked = True Then
                command.Parameters.AddWithValue("EssayStatus", "Y")
            Else
                command.Parameters.AddWithValue("EssayStatus", "N")
            End If
            If chkGMATScore.Checked = True Then
                command.Parameters.AddWithValue("GMAT_GREStatus", "Y")
            Else
                command.Parameters.AddWithValue("GMAT_GREStatus", "N")
            End If
            If chkTOEFL.Checked = True Then
                command.Parameters.AddWithValue("TOEFLStatus", "Y")
            Else
                command.Parameters.AddWithValue("TOEFLStatus", "N")
            End If
            If chkEducation.Checked = True Then
                command.Parameters.AddWithValue("SummaryStatus", "Y")
            Else
                command.Parameters.AddWithValue("SummaryStatus", "N")
            End If
            If chkFinancial.Checked = True Then
                command.Parameters.AddWithValue("SupFinStatus", "Y")
            Else
                command.Parameters.AddWithValue("SupFinStatus", "N")
            End If
            command.ExecuteNonQuery()
            command.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            MessageBox.Show("Successfully applied.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
            'Close the connection, regardles of the rest - with or without errors.
            If DataConnect.State = ConnectionState.Open Then DataConnect.Close()
        End Try
    End Sub
End Class



This is the newest update, I actually have 4 errors now, but I think we're making progress.
line 53 - no parameter given
line 130 - parameter 5-8 invalid.
line 151 - COM object that has been separated from its underlying RCW cannot be used.
msgbox command paramter 8-16 invalid

These errors pop up in messageboxes one at a time and cite all this mumbojumbo (can provide screenshot) ending with the listed issues.

What am I missing/doing wrong?
Was This Post Helpful? 0
  • +
  • -

#10 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Need help with Vb.net to SQL communication.

Posted 29 April 2014 - 02:49 PM

before clearing the parameters, each of those need to be executed with ExecuteNonQuery

Create SQL statement
Define Parameters
command.ExecuteNonQuery
clear parameters

repeat for query2... etc...
Was This Post Helpful? 1
  • +
  • -

#11 larnth  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 28-April 14

Re: Need help with Vb.net to SQL communication.

Posted 29 April 2014 - 02:57 PM

View PostCharlieMay, on 29 April 2014 - 02:49 PM, said:

before clearing the parameters, each of those need to be executed with ExecuteNonQuery

Create SQL statement
Define Parameters
command.ExecuteNonQuery
clear parameters

repeat for query2... etc...


Dear. God.

I think this has fixed it. Going to check the DB now - no errors produced!!!!

I did, in fact, fix it! Thank you so much for your help!!
Was This Post Helpful? 0
  • +
  • -

#12 larnth  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 28-April 14

Re: Need help with Vb.net to SQL communication.

Posted 29 April 2014 - 05:43 PM

View PostCharlieMay, on 29 April 2014 - 02:49 PM, said:

before clearing the parameters, each of those need to be executed with ExecuteNonQuery

Create SQL statement
Define Parameters
command.ExecuteNonQuery
clear parameters

repeat for query2... etc...


I'm back with another issue. I used all the same code (slight alterations) to do updating of the table values that we were, before, inserting. It's giving me a huge array of errors again...

Line 660 - Arithmetic overflow error converting nvarchar to data type numeric at:

line 685 - System.invalidOperationException: Command parameter[8]" is invalid. (also 9-25 are invalid)

line 183 - System.Runtime.InteropServices.InvalidComObjectException: COM object that has been separated from its underlying RCW cannot be used.


Command parameter[8]" is invalid. (again - this time 8-33)

are the errors that are produced when I click the update button. Here's the code:

    'Handles the update of student information on application side of form.
    Private Sub UpdateButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateButton.Click
        'get student ID
        getStudentID()
        'update student table
        updateStudentTable()
        'update aptitude
        updateAptitude()
        'get application ID
        getApplicationID()
        'update check list table
        updateCheckListTable()
    End Sub

    Private Sub updateStudentTable()
        Dim Query As String
        'Try catch so we don't crash (>")>   (>"<)   <("<)
        Try
            DataConnect.Open()
            Query = "UPDATE Student_T SET StudentFName = ?, StudentMName = ?, StudentLName = ?, SSN = ?, DOB = ?, Citizenship = ?, Ethnicity = ?, " _
                & "Gender = ?, StudentCellNum = ?, StudentWorkNum = ?, StudentHomeNum = ?, StudentEmail = ?, ProgramEnrollmentStatus = ?, TermGPA = ?, " _
                & "CumulativeGPA = ?, GradStatus = ?, GradDate = ?, SpecialNotes = ? WHERE StudentID = " & SID & ";"

            command.Connection = DataConnect
            command.CommandText = Query
            command.Parameters.AddWithValue("StudentFName", txtFirstName.Text)
            command.Parameters.AddWithValue("StudentMName", txtMiddleName.Text.ToString)
            command.Parameters.AddWithValue("StudentLName", txtLastName.Text)
            command.Parameters.AddWithValue("SSN", txtSSN.Text)
            command.Parameters.AddWithValue("DOB", txtBirthday1.Text)
            command.Parameters.AddWithValue("Citizenship", cbxCitizenship.Text)
            command.Parameters.AddWithValue("Ethnicity", cbxEthnic.Text)
            command.Parameters.AddWithValue("Gender", cbxGender.Text)
            command.Parameters.AddWithValue("StudentCellNum", txtCPhone.Text)
            command.Parameters.AddWithValue("StudentWorkNum", txtWPhone.Text)
            command.Parameters.AddWithValue("StudentHomeNum", txtHPhone.Text)
            command.Parameters.AddWithValue("StudentEmail", txtEmail.Text)
            command.Parameters.AddWithValue("SpecialNotes", txtNotes.Text)
            command.Parameters.AddWithValue("ProgramEnrollmentStatus", ProgEnrollComboBox.Text)
            command.Parameters.AddWithValue("TermGPA", TermGPATextBox.Text)
            command.Parameters.AddWithValue("CumulativeGPA", CumuGPATextBox.Text)
            command.Parameters.AddWithValue("GradStatus", GradStatusComboBox.Text)
            command.Parameters.AddWithValue("GradDate", GradDateTextBox.Text)
            command.ExecuteNonQuery()
            command.Parameters.Clear()
            command.Dispose()
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            If DataConnect.State = ConnectionState.Open Then DataConnect.Close()
        End Try
    End Sub

    Private Sub updateAptitude()
        Dim Query2 As String
        Try
            DataConnect.Open()
            Query2 = "UPDATE Aptitude_T SET GMATVerbal = ?, GMATQuantitative = ?, GMATTotal = ?, GMATDate = ?, GRETotal = ?, GREDate = ?, TOEFLTotal = ?, TOEFLDate = ? WHERE StudentID =  " & SID & ");"
            command.Connection = DataConnect
            command.CommandText = Query2
            command.Parameters.AddWithValue("GMATVerbal", txtGMATVerbal.Text)
            command.Parameters.AddWithValue("GMATQuantitative", txtGMATQuant.Text)
            command.Parameters.AddWithValue("GMATTotal", txtGMATTotal.Text)
            command.Parameters.AddWithValue("GMATDate", txtGMATDate.Text)
            command.Parameters.AddWithValue("GRETotal", txtGRETotal.Text)
            command.Parameters.AddWithValue("GREDate", txtGREDate.Text)
            command.Parameters.AddWithValue("TOEFLTotal", txtTOEFLTotal.Text)
            command.Parameters.AddWithValue("TOEFLDate", txtTOEFLDate.Text)
            command.ExecuteNonQuery()
            command.Parameters.Clear()

            Dim Query3 As String
            Query3 = "UPDATE Education_T SET CollegeName = ?, DegreeFocus = ?, DegreeType = ?, DegreeGPA = ?, YearsAttended = ? WHERE StudentID = " & SID & ";"
            command.Connection = DataConnect
            command.CommandText = Query3
            command.Parameters.AddWithValue("CollegeName", txtCName1.Text)
            command.Parameters.AddWithValue("DegreeFocus", txtMajor1.Text)
            command.Parameters.AddWithValue("DegreeType", cbxDegree1.Text)
            command.Parameters.AddWithValue("DegreeGPA", txtCGPA1.Text)
            command.Parameters.AddWithValue("YearsAttended", txtCFrom1.Text)
            command.ExecuteNonQuery()
            command.Parameters.Clear()

            Dim query4 As String
            query4 = "UPDATE Application_T SET DegreeSeeking = ?, DesiredSession = ?, FTPTStatus = ?, DateApplied = ?, AssistantshipRequest = ?, AssistantshipStatus = ?, OfferStatus = ?, OfferDate = ? WHERE StudentID = " & SID & " AND ProgramID = " & PID & ";"
            command.Connection = DataConnect
            command.CommandText = query4
            command.Parameters.AddWithValue("DegreeSeeking", cbxDegreeSeeking.Text)
            command.Parameters.AddWithValue("DesiredSession", cbxTerm.Text)
            command.Parameters.AddWithValue("FTPTStatus", FTPTComboBox.Text)
            command.Parameters.AddWithValue("DateApplied", AppDateTextBox.Text)
            If chkRequested.Checked = True Then
                command.Parameters.AddWithValue("AssistantshipRequest", "Y")
            Else
                command.Parameters.AddWithValue("AssistantshipRequest", "N")
            End If
            command.Parameters.AddWithValue("AssistantshipStatus", txtAStatus.Text)
            command.Parameters.AddWithValue("OfferStatus", OfferComboBox.Text)
            command.Parameters.AddWithValue("OfferDate", txtDateAccepted.Text)
            command.ExecuteNonQuery()
            command.Parameters.Clear()

            Dim query5 As String
            query5 = "UPDATE Address_T SET StreetAddress1 = ?, AddressCountry = ?, AddressstateorProvince = ?, AddressZipCode = ?, AddressCity = ? WHERE StudentID = " & SID & ";"
            command.Connection = DataConnect
            command.CommandText = query5
            command.Parameters.AddWithValue("StreetAddress1", txtPAddress.Text)
            command.Parameters.AddWithValue("AddressCountry", cbxPCountry.Text)
            command.Parameters.AddWithValue("AddressStateorProvince", cbxPState.Text)
            command.Parameters.AddWithValue("AddressZipCode", txtPZipCode.Text)
            command.Parameters.AddWithValue("AddressCity", txtPCity.Text)
            command.ExecuteNonQuery()
            command.Parameters.Clear()
            command.Dispose()
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            If DataConnect.State = ConnectionState.Open Then DataConnect.Close()
        End Try
    End Sub


    Private Sub updateCheckListTable()
        Dim Query5 As String
        Try
            DataConnect.Open()
            Query5 = "UPDATE Checklist_T SET AppFeeStatus = ?, ResumeStatus = ?, TranscriptStatus = ?, EssayStatus = ?, GMAT_GREStatus = ?, TOEFLStatus = ?, SummaryStatus = ?, SupFinStatus = ? WHERE ApplicationID = " & AppID & ";"
            command.Connection = DataConnect
            command.CommandText = Query5
            If chkAppFeePaid.Checked = True Then
                command.Parameters.AddWithValue("AppFeeStatus", "Y")
            Else
                command.Parameters.AddWithValue("AppFeeStatus", "N")
            End If
            If chkResume.Checked = True Then
                command.Parameters.AddWithValue("ResumeStatus", "Y")
            Else
                command.Parameters.AddWithValue("ResumeStatus", "N")
            End If
            If chkTranscript.Checked = True Then
                command.Parameters.AddWithValue("TranscriptStatus", "Y")
            Else
                command.Parameters.AddWithValue("TranscriptStatus", "N")
            End If
            If chkEssay.Checked = True Then
                command.Parameters.AddWithValue("EssayStatus", "Y")
            Else
                command.Parameters.AddWithValue("EssayStatus", "N")
            End If
            If chkGMATScore.Checked = True Then
                command.Parameters.AddWithValue("GMAT_GREStatus", "Y")
            Else
                command.Parameters.AddWithValue("GMAT_GREStatus", "N")
            End If
            If chkTOEFL.Checked = True Then
                command.Parameters.AddWithValue("TOEFLStatus", "Y")
            Else
                command.Parameters.AddWithValue("TOEFLStatus", "N")
            End If
            If chkEducation.Checked = True Then
                command.Parameters.AddWithValue("SummaryStatus", "Y")
            Else
                command.Parameters.AddWithValue("SummaryStatus", "N")
            End If
            If chkFinancial.Checked = True Then
                command.Parameters.AddWithValue("SupFinStatus", "Y")
            Else
                command.Parameters.AddWithValue("SupFinStatus", "N")
            End If
            command.ExecuteNonQuery()
            command.Parameters.Clear()
            command.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            MessageBox.Show("Successfully updated.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
            'Close the connection, regardles of the rest - with or without errors.
            If DataConnect.State = ConnectionState.Open Then DataConnect.Close()
        End Try
    End Sub


Was This Post Helpful? 0
  • +
  • -

#13 larnth  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 28-April 14

Re: Need help with Vb.net to SQL communication.

Posted 29 April 2014 - 05:49 PM

I'm so embarrassed.... There was a misplace parenthesis and I mis-ordered two parameters.. Thank you for looking, if you already looked at this.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1