Solved

vb.net array

Posted on 2016-07-18
7
24 Views
Last Modified: 2016-07-18
I have an array created like this:
Dim StrArray() As String = Split(drAccessRecord(FieldDescription))

Ive extendedmy access record data by an additional column:
VehCatValue = drAccessRecord(VehCatClient)

for each word in the array I would like to save that vehcatvalue

not sure how to extend the array to do that or even if i have to do it that way.

Ive tried and in some cases it does the insert in others it doesnt, not sure why.

dtaccessRecord is all populated row wise so its that last section within the for loop i think.

the column in the sql server db is called VehCategory (nvarchar(max), null)

Public Sub CreateWords()
        Dim StrClientName As String
        Dim x As Boolean
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Dim index As Long
        Dim StrClientCodeWordPos As String
        Dim StrClientCode As String

        Dim StrWord As String
        Dim intWordLen As Int16
        Dim StrFull As String
        Dim IntWordPosition As Int16
        Dim IntNoOfWords As Int16

        Try
            StrClientName = ""

            'BackColor = Color.FromArgb(107, 115, 115)
            'Me.Refresh()

            If Form1.RadioButton1.Checked = True Then
                StrClientName = "Smmt"
            End If

            If Form1.RadioButton2.Checked = True Then
                StrClientName = "Abi"
            End If


            If Form1.RadioButton3.Checked = True Then
                StrClientName = "Cap"
            End If

            If Form1.RadioButton4.Checked = True Then
                StrClientName = "Glass"
            End If

            If Form1.RadioButton5.Checked = True Then
                StrClientName = "Tvi"
            End If

            con.ConnectionString = "Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True"
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "DELETE FROM [dbo].[TblWords] where ClientName= @ClientName"
            cmd.Parameters.Add(New SqlParameter("@ClientName", StrClientName))
            cmd.ExecuteNonQuery()
            con.Close()

            Dim dtRecordsFromAccess As DataTable
            Dim FieldNameClientCode As String
            Dim FieldDescription As String
            Dim VehCatClient As String
            Dim VehCatValue As String

            Select Case StrClientName
                Case "Smmt"
                    Using cnAccess As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data\SMMT\smmt master.mdb")
                        Using cmdSelect As New OleDb.OleDbCommand
                            cmdSelect.Connection = cnAccess
                            cnAccess.Open()
                            cmdSelect.CommandText = "SELECT [MVRIS CODE], [Model Range] & ' ' & [variant] as ConCatDesc, [Vehicle Category Code] FROM smmt;"
                            'cmdSelect.CommandText = "SELECT [MVRIS CODE], [Model Range] & ' ' & [variant] as ConCatDesc, [Vehicle Category Code] FROM smmt;"

                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccess = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccess)
                        End Using
                    End Using
                    FieldNameClientCode = "MVRIS CODE"
                    FieldDescription = "ConCatDesc"
                    VehCatClient = "Vehicle Category Code"

                Case "Abi"
                    Using cnAccess As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data\Abi\Abi Master.mdb")
                        Using cmdSelect As New OleDb.OleDbCommand
                            cmdSelect.Connection = cnAccess
                            cnAccess.Open()
                            cmdSelect.CommandText = "SELECT [AbiCode], [MODEL_DESCRIPTION] as ConCatDesc, Abi_Cat FROM TClient ;"
                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccess = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccess)
                        End Using
                    End Using

                    FieldNameClientCode = "abiCode"
                    FieldDescription = "ConCatDesc"
                    VehCatClient = "Abi_Cat"

                Case "Cap"
                    Using cnAccess As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data\Cap\Cap Master.mdb")
                        Using cmdSelect As New OleDb.OleDbCommand
                            cmdSelect.Connection = cnAccess
                            cnAccess.Open()
                            cmdSelect.CommandText = "SELECT [CAPid_CAPcat], [LongModelDescription] & ' ' & [ShortDerivativeDescription] as ConCatDesc, CAP_cat FROM CAPDATA ;"
                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccess = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccess)
                        End Using
                    End Using

                    FieldNameClientCode = "CAPid_CAPcat"
                    FieldDescription = "ConCatDesc"
                    VehCatClient = "CAP_Cat"

                Case "Glass"
                    Using cnAccess As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data\Glass\Glass Master 10.mdb")
                        Using cmdSelect As New OleDb.OleDbCommand
                            cmdSelect.Connection = cnAccess
                            cnAccess.Open()
                            cmdSelect.CommandText = "SELECT [GLASSid_GLASScat], [Range_desc] & ' ' & [Trim_desc] as ConCatDesc, GLASS_cat FROM  [Glass Full Table] ;"
                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccess = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccess)
                        End Using
                    End Using

                    FieldNameClientCode = "GLASSid_GLASScat"
                    FieldDescription = "ConCatDesc"
                    VehCatClient = "GLASS_cat"

                Case "Tvi"
                    Using cnAccess As New OleDb.OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0; Data Source=N:\Data\Thatcham\Tvi Master 4.mdb")
                        Using cmdSelect As New OleDb.OleDbCommand
                            cmdSelect.Connection = cnAccess
                            cnAccess.Open()
                            cmdSelect.CommandText = "SELECT [DERIVATIVE_CODE], [Model]  & ' ' & [Trim] as ConCatDesc, TVIVehicleTyep FROM TVIDATA ;"
                            Dim dtAdpt As New OleDb.OleDbDataAdapter(cmdSelect)
                            dtRecordsFromAccess = New DataTable
                            dtAdpt.Fill(dtRecordsFromAccess)
                        End Using
                    End Using
                    FieldNameClientCode = "DERIVATIVE_CODE"
                    FieldDescription = "ConCatDesc"
                    VehCatClient = "TVIVehicleTyep"

            End Select

            Using cnSql As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")
                Using cmdInsert As New SqlClient.SqlCommand
                    cmdInsert.Connection = cnSql
                    cnSql.Open()
                    Dim y As Long
                    Dim Z As Double
                    Dim Counter As Long

                    y = dtRecordsFromAccess.Rows.Count
                    For Each drAccessRecord As DataRow In dtRecordsFromAccess.Rows
                        Dim StrArray() As String = Split(drAccessRecord(FieldDescription))
                        Form1.ProgressBar1.Step = 1
                        Form1.ProgressBar1.Minimum = 1
                        Form1.ProgressBar1.Maximum = y
                        For index = LBound(StrArray) To UBound(StrArray)

                            StrClientCodeWordPos = drAccessRecord(FieldNameClientCode) & "_" & RemoveUnwantedChr(StrArray(index)) & "_" & index + 1
                            StrClientCode = drAccessRecord(FieldNameClientCode)
                            StrFull = RemoveUnwantedChr(drAccessRecord(FieldDescription))
                            StrClientName = StrClientName
                            StrWord = RemoveUnwantedChr(StrArray(index))
                            intWordLen = Len(StrArray(index))
                            IntWordPosition = index + 1
                            IntNoOfWords = UBound(StrArray) + 1
                            VehCatValue = drAccessRecord(VehCatClient)
                            'Insert records in SQL database
                            cmdInsert.CommandText = "INSERT INTO TblWords (ClientCodeWordPosition, ClientCode, ClientName, Word, WordLen, StrFull, WordPosition, NoOfWords, VehCategory) VALUES ('" & StrClientCodeWordPos & "','" & StrClientCode & "','" & StrClientName & "','" & StrWord & "'," & intWordLen & ",'" & StrFull & "'," & IntWordPosition & "," & IntNoOfWords & ", '" & VehCatValue & "')"
                            'cmdInsert.CommandText = "INSERT INTO TblWords (ClientCodeWordPosition, ClientCode, ClientName, Word, WordLen, StrFull, WordPosition, NoOfWords) VALUES ('" & StrClientCodeWordPos & "','" & StrClientCode & "','" & StrClientName & "','" & StrWord & "'," & intWordLen & ",'" & StrFull & "'," & IntWordPosition & "," & IntNoOfWords & "')"
                            cmdInsert.ExecuteNonQuery()

                        Next index

                        Form1.ProgressBar1.PerformStep()
                        Form1.Label3.Text = "# of Files Read = " & Math.Round((Form1.ProgressBar1.Value.ToString / y) * 100, 2) & "%"
                        Form1.Label3.Refresh()
                    Next
                End Using
            End Using

        Catch ex As Exception


        Finally

            con.Close()
        End Try
    End Sub

Open in new window

0
Comment
Question by:PeterBaileyUk
  • 3
  • 2
  • 2
7 Comments
 
LVL 84
ID: 41717063
This line:

VehCatValue = drAccessRecord(VehCatClient)

Simply grabs the value from the DataRow (drAccessRecord) in the "VehCatClient" column, and stores it in the VehCatValue variable. It does not have anything to do with an array. If you're trying to get values from the data in VehCatClient, and if those values are in some sort of array, then you'd have to tell us more about that.

Your modifications to the cmdInsert.CommandText seem to be okay, assuming they work.

In other words - if your intent is to get the value of VehCatClient and insert that into tblWords.VehCategory, then your code should work as-is.

If that is not your intent, then you'd have to clarify that a bit.
0
 
LVL 33

Expert Comment

by:ste5an
ID: 41717065
First of all: Use OOP. Separate different aspects into different methods. E.g. the form handling of the radio buttons or reading data.

Your method should be something like

Public Sub CreateWords(aRecordsFromAccess As DataTable, 
        AFieldNameClientCode As String,  
        AFieldDescription As String, 
        AVehCatClient As String, 
        APercentageComplete As Action(Of Long))

Open in new window


Then: Use parameterized queries. You currently allow SQL injection.

And last but not least: Can you rephrase your question? What does "for each word in the array I would like to save that vehcatvalue" exactly mean? You're already saving it in your loop..
0
 

Author Comment

by:PeterBaileyUk
ID: 41717088
I wasnt sure but ive tracked the error down to the command string, in its previous form this worked perfectly:

cmdInsert.CommandText = "INSERT INTO TblWords (ClientCodeWordPosition, ClientCode, ClientName, Word, WordLen, StrFull, WordPosition, NoOfWords) VALUES ('" & StrClientCodeWordPos & "','" & StrClientCode & "','" & StrClientName & "','" & StrWord & "'," & intWordLen & ",'" & StrFull & "'," & IntWordPosition & "," & IntNoOfWords & " )"

'adding the VehCatValue has caused problems so: How can I add the extra column as I got it wrong in the previous posted code after the IntNoOfWords.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 41717092
Your new syntax below should work:

 cmdInsert.CommandText = "INSERT INTO TblWords (ClientCodeWordPosition, ClientCode, ClientName, Word, WordLen, StrFull, WordPosition, NoOfWords, VehCategory) VALUES ('" & StrClientCodeWordPos & "','" & StrClientCode & "','" & StrClientName & "','" & StrWord & "'," & intWordLen & ",'" & StrFull & "'," & IntWordPosition & "," & IntNoOfWords & ", '" & VehCatValue & "')"

Assuming VehCatValue does not have any sort of special characters, like single quotes and such, and also assuming you've got the column name right.
0
 

Author Closing Comment

by:PeterBaileyUk
ID: 41717099
thank you
0
 
LVL 33

Expert Comment

by:ste5an
ID: 41717107
Sorry, but this still allows SQL injection. This cannot be an answer.
0
 
LVL 84
ID: 41717639
The code accepts only checkbox inputs, and does not directly interact with any control where a user could enter text (i.e. Textbox). As such, I'm not sure how SQL Injection could be a risk here. If there is a risk, I'd certainly like to see how so that I could avoid it.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now