locked
Adding Data to an Access DB Table RRS feed

  • Question

  • I'm teaching myself vb.net after two decades of using vb6.

    I'm writing a program where I pull data from several MS Access (2010) tables, and put selective data from those fields into a new table.

    I'm getting a syntax error..but can't see it...cause I don't know what to look for

    here is the code

    Private Sub WriteStudentRec(ByRef ThisStudentRec As TargetXStudent, ByVal ThisStudentRecID As String)
            Dim sSQL As String = QQ
            Dim dbFields As String = QQ
            Dim cmdInsert As New OleDb.OleDbCommand
            Dim sDbFields As String

            sDbFields = "Student ID, Last Name, First Name, Middle Name, Former Last Name, Nickname, Salutation ,Suffix ,Email ,Email Opt Out ,SSN ,CEEB Code ,Mailing Street,"
            sDbFields = sDbFields & "Mailing City ,Mailing State/Province ,Mailing Zip/Postal Code,Mailing Country ,Other Street ,Other City ,Other State/Province ,Other Zip/Postal Code,"
            sDbFields = sDbFields & "Other Country ,Home Phone ,Mobile Phone ,Other Phone ,Birthdate ,Gender ,Citizenship ,IPEDS Hispanic ,IPEDS Ethnicities ,Primary Other Citizenship,"
            sDbFields = sDbFields & "Language ,Home Language ,Country of Permanent Residence,Birth City ,Birth State ,Birth Country ,Program ,Anticipated Major ,Concentration ,Campus,"
            sDbFields = sDbFields & "College ,Degree ,Anticipated Enrollment Status ,Anticipated Housing ,Anticipated Start Term,Anticipated Start Year ,Student Stage ,Student Type"
            sDbFields = sDbFields & "Marital Status ,Religion ,Graduation Year ,Lead Source,How Did You Find Out About Us"

            sSQL = "INSERT INTO TargetX_Student (" & sDbFields & ")  VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
            cmdInsert.Parameters.Clear()
            cmdInsert.Connection = m_cnTargetXAccessDB 'Connections to Access DB
            cmdInsert.CommandText = sSQL
            cmdInsert.Parameters.AddWithValue("@Student ID", ThisStudentRec.Student_ID)
            cmdInsert.Parameters.AddWithValue("@Last Name", ThisStudentRec.LastName)
            cmdInsert.Parameters.AddWithValue("@First Name", ThisStudentRec.FirstName)
            cmdInsert.Parameters.AddWithValue("@Middle Name", ThisStudentRec.MiddleName)
            cmdInsert.Parameters.AddWithValue("@Former Last Name", ThisStudentRec.FormerLastName)
            cmdInsert.Parameters.AddWithValue("@Nickname", ThisStudentRec.Nickname)
            cmdInsert.Parameters.AddWithValue("@Salutation", ThisStudentRec.Salutation)
            cmdInsert.Parameters.AddWithValue("@Suffix", ThisStudentRec.Suffix)
            cmdInsert.Parameters.AddWithValue("@Email", ThisStudentRec.Email)
            cmdInsert.Parameters.AddWithValue("@Email Opt Out", ThisStudentRec.EmailOptOut)
            cmdInsert.Parameters.AddWithValue("@SSN", ThisStudentRec.SSN)
            cmdInsert.Parameters.AddWithValue("@CEEB Code", ThisStudentRec.CEEBCode)
            cmdInsert.Parameters.AddWithValue("@Mailing Street", ThisStudentRec.MailingStreet)
            cmdInsert.Parameters.AddWithValue("@Mailing City", ThisStudentRec.MailingCity)
            cmdInsert.Parameters.AddWithValue("@Mailing State/Province", ThisStudentRec.MailingState_Province)
            cmdInsert.Parameters.AddWithValue("@Mailing Zip/Postal Code", ThisStudentRec.MailingZip_PostalCode)
            cmdInsert.Parameters.AddWithValue("@Mailing Country", ThisStudentRec.MailingCountry)
            cmdInsert.Parameters.AddWithValue("@Other Street", ThisStudentRec.OtherStreet)
            cmdInsert.Parameters.AddWithValue("@Other City", ThisStudentRec.OtherCity)
            cmdInsert.Parameters.AddWithValue("@Other State/Province", ThisStudentRec.OtherState_Province)
            cmdInsert.Parameters.AddWithValue("@Other Zip/Postal Code", ThisStudentRec.OtherZip_PostalCode)
            cmdInsert.Parameters.AddWithValue("@Other Country", ThisStudentRec.OtherCountry)
            cmdInsert.Parameters.AddWithValue("@Home Phone", ThisStudentRec.HomePhone)
            cmdInsert.Parameters.AddWithValue("@Mobile Phone", ThisStudentRec.MobilePhone)
            cmdInsert.Parameters.AddWithValue("@Other Phone", ThisStudentRec.OtherPhone)
            cmdInsert.Parameters.AddWithValue("@Birthdate", ThisStudentRec.Birthdate)
            cmdInsert.Parameters.AddWithValue("@Gender", ThisStudentRec.Gender)
            cmdInsert.Parameters.AddWithValue("@Citizenship", ThisStudentRec.Citizenship)
            cmdInsert.Parameters.AddWithValue("@IPEDS Hispanic", ThisStudentRec.IPEDSHispanic)
            cmdInsert.Parameters.AddWithValue("@IPEDS Ethnicities", ThisStudentRec.IPEDSEthnicities)
            cmdInsert.Parameters.AddWithValue("@Primary Other Citizenship", ThisStudentRec.PrimaryOtherCitizenship)
            cmdInsert.Parameters.AddWithValue("@Language", ThisStudentRec.Language)
            cmdInsert.Parameters.AddWithValue("@Home Language", ThisStudentRec.HomeLanguage)
            cmdInsert.Parameters.AddWithValue("@Country of Permanent Residence", ThisStudentRec.CountryofPermanentResidence)
            cmdInsert.Parameters.AddWithValue("@Birth City", ThisStudentRec.BirthCity)
            cmdInsert.Parameters.AddWithValue("@Birth State", ThisStudentRec.BirthState)
            cmdInsert.Parameters.AddWithValue("@Birth Country", ThisStudentRec.BirthCountry)
            cmdInsert.Parameters.AddWithValue("@Program", ThisStudentRec.Program)
            cmdInsert.Parameters.AddWithValue("@Anticipated Major", ThisStudentRec.AnticipatedMajor)
            cmdInsert.Parameters.AddWithValue("@Concentration", ThisStudentRec.Concentration)
            cmdInsert.Parameters.AddWithValue("@Campus", ThisStudentRec.Campus)
            cmdInsert.Parameters.AddWithValue("@College", ThisStudentRec.College)
            cmdInsert.Parameters.AddWithValue("@Degree", ThisStudentRec.Degree)
            cmdInsert.Parameters.AddWithValue("@Anticipated Enrollment Status", ThisStudentRec.AnticipatedEnrollmentStatus)
            cmdInsert.Parameters.AddWithValue("@Anticipated Housing", ThisStudentRec.AnticipatedHousing)
            cmdInsert.Parameters.AddWithValue("@Anticipated Start Term", ThisStudentRec.AnticipatedStartTerm)
            cmdInsert.Parameters.AddWithValue("@Anticipated Start Year", ThisStudentRec.AnticipatedStartYear)
            cmdInsert.Parameters.AddWithValue("@Student Stage", ThisStudentRec.StudentStage)
            cmdInsert.Parameters.AddWithValue("@Student Type", ThisStudentRec.StudentType)
            cmdInsert.Parameters.AddWithValue("@Marital Status", ThisStudentRec.MaritalStatus)
            cmdInsert.Parameters.AddWithValue("@Religion", ThisStudentRec.Religion)
            cmdInsert.Parameters.AddWithValue("@Graduation Year", ThisStudentRec.GraduationYear)
            cmdInsert.Parameters.AddWithValue("@Lead Source", ThisStudentRec.LeadSource)
            cmdInsert.Parameters.AddWithValue("@How Did You Find Out About Us", ThisStudentRec.HowDidYouFindOutAboutUs)

            Try
                cmdInsert.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Critical, "Exception Message")
            End Try

        End Sub

    NOTE: the Access DB fields are the header names of the CSV fields that I'm going to dump from access, and are the correct names in order.

    Is there a better way to do this?


    VC Swindell PepperEyes.com

    Tuesday, February 26, 2013 4:07 PM

Answers

  • Okay I had a few minutes to spare and going with Paul's suggestion I would do the following if using VS2008 or higher. This way you can clearly see your statement and requires no string concatenation.

    Also, in the future avoid using spaces and slashes in column names. If you need them for operations inside MS-Access create columns without spaces and slashes etc. then in the design view set the caption proper of the column with spaces etc. The caption is not used by OleDb, only inside MS-Access i.e. in view mode or forms and reports.

    Dim InsertStatement As String =
        <SQL>
            INSERT INTO TargetX_Student 
            (
                [Student ID], 
                [Last Name], 
                [First Name], 
                [Middle Name], 
                [Former Last Name], 
                Nickname, 
                Salutation,
                Suffix,
                Email,
                [Email Opt Out],
                SSN,
                [CEEB Code],
                [Mailing Street],
                [Mailing City],
                [Mailing State/Province],
                [Mailing Zip/Postal Code],
                [Mailing Country],
                [Other Street],
                [Other City],
                [Other State/Province],
                [Other Zip/Postal Code],
                [Other Country],
                [Home Phone],
                [Mobile Phone],
                [Other Phone],
                Birthdate,
                Gender,
                Citizenship,
                [IPEDS Hispanic],
                [IPEDS Ethnicities],
                [Primary Other Citizenship],
                Language,
                [Home Language],
                [Country of Permanent Residence],
                [Birth City],
                [Birth State],
                [Birth Country],
                Program,
                [Anticipated Major],
                Concentration,
                Campus,
                College,
                Degree,
                [Anticipated Enrollment Status],
                [Anticipated Housing],
                [Anticipated Start Term,Anticipated Start Year],
                [Student Stage],
                [Student Type],
                [Marital Status],
                Religion,
                [Graduation Year],
                [Lead Source],
                [How Did You Find Out About Us]
            )  
            VALUES 
            (
                ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
                ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
                ?,?,?,?,?,?,?,?,?,?,?,?,?,?
            )
        </SQL>.Value
    cmdInsert.CommandText = InsertStatement


    kevininstructor

    • Marked as answer by Alexander Sun Monday, March 11, 2013 8:28 AM
    Friday, March 1, 2013 6:03 PM

All replies

  • Column names with embedded spaces need to be enclosed within brackets (e.g. [Student ID]). I would fix this issue first for all of those columns and then try your code again.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, February 26, 2013 4:46 PM
  • Okay I had a few minutes to spare and going with Paul's suggestion I would do the following if using VS2008 or higher. This way you can clearly see your statement and requires no string concatenation.

    Also, in the future avoid using spaces and slashes in column names. If you need them for operations inside MS-Access create columns without spaces and slashes etc. then in the design view set the caption proper of the column with spaces etc. The caption is not used by OleDb, only inside MS-Access i.e. in view mode or forms and reports.

    Dim InsertStatement As String =
        <SQL>
            INSERT INTO TargetX_Student 
            (
                [Student ID], 
                [Last Name], 
                [First Name], 
                [Middle Name], 
                [Former Last Name], 
                Nickname, 
                Salutation,
                Suffix,
                Email,
                [Email Opt Out],
                SSN,
                [CEEB Code],
                [Mailing Street],
                [Mailing City],
                [Mailing State/Province],
                [Mailing Zip/Postal Code],
                [Mailing Country],
                [Other Street],
                [Other City],
                [Other State/Province],
                [Other Zip/Postal Code],
                [Other Country],
                [Home Phone],
                [Mobile Phone],
                [Other Phone],
                Birthdate,
                Gender,
                Citizenship,
                [IPEDS Hispanic],
                [IPEDS Ethnicities],
                [Primary Other Citizenship],
                Language,
                [Home Language],
                [Country of Permanent Residence],
                [Birth City],
                [Birth State],
                [Birth Country],
                Program,
                [Anticipated Major],
                Concentration,
                Campus,
                College,
                Degree,
                [Anticipated Enrollment Status],
                [Anticipated Housing],
                [Anticipated Start Term,Anticipated Start Year],
                [Student Stage],
                [Student Type],
                [Marital Status],
                Religion,
                [Graduation Year],
                [Lead Source],
                [How Did You Find Out About Us]
            )  
            VALUES 
            (
                ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
                ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,
                ?,?,?,?,?,?,?,?,?,?,?,?,?,?
            )
        </SQL>.Value
    cmdInsert.CommandText = InsertStatement


    kevininstructor

    • Marked as answer by Alexander Sun Monday, March 11, 2013 8:28 AM
    Friday, March 1, 2013 6:03 PM