Inserting Array Rows into SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Inserting Array Rows into SQL

Author

Topic

radzima
Starting Member

4 Posts

Posted - 2007-12-27 : 14:22:35

I have several different arrays I am storing information in that was pulled from SQL, now I need to put it all back together into SQL. Only problem is I created a loop to go through and insert each item, but after it's done there are several duplicates... for example:
I have 3 rows of data selected for insertion, it will run through, but all three rows will be of the last item... like it runs through fine but doesn't increment...

Here's a snippet of the code that does the loop and insert


Dim theCommand As System.Data.SqlClient.SqlCommand
        theCommand = New System.Data.SqlClient.SqlCommand
        theCommand.Connection = theConnection
        theCommand.CommandText = _
        "DECLARE @TSID int " & _
        "SELECT @TSID = (SELECT MAX(TSPropertyID)+1 FROM TSProperties) " & _
        "INSERT Into TSProperties " & _
        "Values (@TSID, 1,'" & GXSelectCodeArray(m) & "','" & GXSelectNameArray(m) & "', '','','" & GXSelectUpdateArray(m) & "','" & Now & "') " & _
        "DECLARE @HSID int " & _
        "SELECT @HSID = (SELECT MAX(HSPropertyID)+1 FROM HSProperties) " & _
        "INSERT into HSProperties " & _
        "Values (@HSID,'" & GXSelectNameArray(m) & "','" & GXSelectDescrArray(m) & "', '102','', '1', '0')" & _
        "Select name from hsproperties where hspropertyid = @HSID"
        'Create an SQL DataAdapter to read the data.
        Dim theDataAdapter As System.Data.SqlClient.SqlDataAdapter
        theDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        theDataAdapter.SelectCommand = theCommand
        'Open the command and then read the data.
        Try
            Dim r As Integer
            r = UBound(GXSelectNameArray)
            If GXSelectNameArray(0) = "" Then
                MsgBox("Please select at least one access code.", MsgBoxStyle.OkOnly, "Nothing Selected")
            Else
                While m <= r
                    theConnection.Open()
                    Dim access As System.Data.DataSet = New System.Data.DataSet
                    access.DataSetName = "Access"
                    theDataAdapter.Fill(access, "Access")
                    theConnection.Close()
                    m = m + 1
                End While

            End If

nr
SQLTeam MVY

12543 Posts

Posted - 2007-12-27 : 15:31:56

Looks like you are using m to create the command before the loop so it is probably 0 when it selects the entry.
Try displying some values as it runs (especially CommandText) and you will probably see what's wrong.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

radzima
Starting Member

4 Posts

Posted - 2007-12-27 : 15:55:52

I am using m to loop it, r being the number of items in each array. m is used to insert each line into the table from the array

Values (@HSID,'" & GXSelectNameArray(m) & "','" & GXSelectDescrArray(m) & "', '102','', '1', '0')" & _

I have run it through step by step and all my numbers look right, but it only inserts one value, like m isn't counting up. I have also used

for m = 0 to r
theConnection.Open()
Dim access As System.Data.DataSet = New System.Data.DataSet
access.DataSetName = "Access"
theDataAdapter.Fill(access, "Access")
theConnection.Close()
next m

with the same results

nr
SQLTeam MVY

12543 Posts

Posted - 2007-12-27 : 19:41:23

Where is m used in the loop?
"Values (@TSID, 1,'" & GXSelectCodeArray(m) & "','" & GXSelectNameArray(m) & "', '','','" & GXSelectUpdateArray(m) & "','" & Now & "') " & _

is outside the loop so a single value will be used.
Looks to me like that loop would run the same command r times.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources