asked on
vb.net array
I have an array created like this:
Dim StrArray() As String = Split(drAccessRecord(Field
Ive extendedmy access record data by an additional column:
VehCatValue = drAccessRecord(VehCatClien
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)
Dim StrArray() As String = Split(drAccessRecord(Field
Ive extendedmy access record data by an additional column:
VehCatValue = drAccessRecord(VehCatClien
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
Visual Basic.NET
Last Comment
Scott McDaniel (EE MVE )
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
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..
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))
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..
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
thank you
Sorry, but this still allows SQL injection. This cannot be an answer.
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.
VehCatValue = drAccessRecord(VehCatClien
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.