Using a Loop in in SQL Command

[somemightsay]

Hi,

I'm attempting to use a Loop which uses an ICount of 1-9 in order to insert data into a database.

The code is used for a website and when this page is loaded there are already values assigned to intProductIDSelected1, intProductIDSelected2 etc so I am attempting to use ICount to join "intProductIDSelected" & intICount which would loop through the 9 intProductIDSelected statements but with no luck.

Below is the code which is causing the problems.

Dim intICount As Integer      
        Dim strTest As String
        
        strTest = "intProductIDSelected" & intICount
        
        
        
        For intICount = 1 To 9
            If intTest <> 0 Then
            
                Dim strSQLCommand3 As String
                strSQLCommand3 = "INSERT INTO OrderLine(OrderID, ProductID)" & "Values (" & intMaxOrderID & ", " & strTest & ");"
                objOleDbConnection.Open()
                Dim objOleDbCommand3 As System.Data.OleDb.OleDbCommand
                objOleDbCommand3 = New System.Data.OleDb.OleDbCommand(strSQLCommand3, objOleDbConnection)
                objOleDbCommand3.ExecuteNonQuery()
                objOleDbConnection.Close()
            
            End If
        Next

I have tried it with and without the IF statement so this is not causing issues.

I've also tried using single quotes around strTest in the SQL Command, but this just results in the program attempting to enter "intProductIDSelected" into the database as text.

Converting strTest to an Int causes an error.

Thanks.

[jbisono]

Why dont you use an int array to accomplish that?

[Mitja Bonca]

If I look into your string query (INSERT INTO...) you always through the loop insert the same values. I thnik this is not the point here, or am I wrong?

Anyway, you can do the loop something like this, but you will have to get new values for ID and strTest value somehow:

Dim intICount As Integer
Dim strTest As String = Nothing
strTest = "intProductIDSelected" & intICount

objOleDbConnection.Open()
For intICount = 0 To 8
	If intTest > 0 Then
		' intMaxOrderID is ALWAYS THE Same - and thats why you will get an error, 
		'becuase you cannot have 2 identical ids in the dataBase`s table!!
		'and even the strTest is always the same!!
		'I have no clue how you will get new values, and I dont even know why this loop is all about!
		Dim strSQLCommand3 As String = "INSERT INTO OrderLine(OrderID, ProductID)" & "VALUES ('" & intMaxOrderID & "', '" & strTest & "')"
		Dim objOleDbCommand3 As New System.Data.OleDb.OleDbCommand(strSQLCommand3, objOleDbConnection)
		objOleDbCommand3.ExecuteNonQuery()
		'you can create a new ID with simple add +1:
			'if you want to create a new id in the dataTable!!
		intMaxOrderID += 1
	End If
Next
objOleDbConnection.Close()

[somemightsay]

Thanks for the replies, but I went with Jbisono's solution of using an array.

Dim product(9) As String
        product(0) = strProductIDselected1
        product(1) = strProductIDselected2
        product(2) = strProductIDselected3
        product(3) = strProductIDselected4
        product(4) = strProductIDselected5
        product(5) = strProductIDselected6
        product(6) = strProductIDselected7
        product(7) = strProductIDselected8
        product(8) = strProductIDselected9
        product(9) = strProductIDselected10
        
        
       For intICount = 0 To 9
            If String.IsNullOrEmpty(product(intICount)) = False Then
            
                Dim strSQLCommand3 As String
                strSQLCommand3 = "INSERT INTO OrderLine(OrderID, ProductID) Values (" & intMaxOrderID & ", " & product(intICount) & ");"
                objOleDbConnection.Open()
                Dim objOleDbCommand3 As System.Data.OleDb.OleDbCommand
                objOleDbCommand3 = New System.Data.OleDb.OleDbCommand(strSQLCommand3, objOleDbConnection)
                objOleDbCommand3.ExecuteNonQuery()
                objOleDbConnection.Close()
            
            End If
        Next

[Mitja Bonca]

1st think that I have to mention here (and I forgot to say it before), you have to get the last ID from the dataBase, so you can continue inserting from then on.
you can use a "SELECT MAX(IDCOumnNAME) FROM myTable" statement. The you can do:

Dim product As String() = New String(9) {}
product(0) = strProductIDselected1
product(1) = strProductIDselected2
product(2) = strProductIDselected3
product(3) = strProductIDselected4
product(4) = strProductIDselected5
product(5) = strProductIDselected6
product(6) = strProductIDselected7
product(7) = strProductIDselected8
product(8) = strProductIDselected9
product(9) = strProductIDselected10

'GET THE LATEST ID!!
'IF THERE IS NONE( (empty table) SET IT TO 0 
'AND DO INCREMENTATION: Add +1 - to get a new ID!!
For i As Integer = 0 To product.Lenght - 1
	If intTest > 0 Then
		' intMaxOrderID is ALWAYS THE Same - and thats why you will get an error, 
		'becuase you cannot have 2 identical ids in the dataBase`s table!!
		'and even the strTest is always the same!!
		'I have no clue how you will get new values, and I dont even know why this loop is all about!
		Dim strSQLCommand3 As String = "INSERT INTO OrderLine(OrderID, ProductID) VALUES ('" & intMaxOrderID & "', '" & product(i) & "')"
		Dim objOleDbCommand3 As New System.Data.OleDb.OleDbCommand(strSQLCommand3, objOleDbConnection)
		objOleDbCommand3.ExecuteNonQuery()
		'you can create a new ID with simple add +1:
		'if you want to create a new id in the dataTable!!
		intMaxOrderID += 1
	End If
Next
objOleDbConnection.Close()