Make a for each using SqlDataReader vb.net

Question

i need store the value "IdMaterial" from table 1 ( imagine that have more that 40 records) into a array save all that reacord into table 2 on the code i will show you only save me the first record and not all.

i will apreceate your help i a noobie in proraming

Code :

Dim i As Integer i = 0

        Try


            Dim mater As String
            Dim planta As String
            Dim almacen As String
            Dim lot As String
            Dim cantidad As String
            Dim cantadiat As String
            Dim undad As String
            Dim Cantidadc As String
            Dim CantidadB As String
            Dim Session1 As String
            Dim fecha As String


            '''''
            Dim Con34 As New Data.SqlClient.SqlConnection
            Con34.ConnectionString = C.GetAppConfiguracion("Inventario", "ConnInventario")

            Dim editCustQuery As String = "select * from dbo.s_RptInventarioSAP"
            Con34.Open()
            Using CustCommand As New SqlCommand(editCustQuery, Con34)
                Dim dr As SqlDataReader = CustCommand.ExecuteReader()
                dr.Read()


                mater = dr.GetString(0)
                planta = dr.GetString(1)
                almacen = dr.GetString(2)
                lot = dr.GetString(3)
                cantidad = dr.GetString(4)
                cantadiat = dr.GetString(5)
                undad = dr.GetString(6)
                Cantidadc = dr.GetString(7)
                CantidadB = dr.GetString(8)
                Session1 = dr.GetString(9)
                fecha = dr.GetDateTime(10)
                end using

            Dim Con As New Data.SqlClient.SqlConnection
            Dim StrSQL As String

            Con.ConnectionString = C.GetAppConfiguracion("Inventario", "ConnInventario")

            StrSQL = ""
            StrSQL = "EXEC P_AsigDupla '" & Txtfecha.Text & "','" & cboPlanta0.SelectedValue & "', '" & cboPlanta0.SelectedItem.Text & "','" & cboAlmacen.SelectedValue & "', '" & cboAlmacen.SelectedItem.Text & "', '" & mater & "', '" & lot & "'"
            Con.Open()
            Dim CmdAd As New Data.SqlClient.SqlCommand(StrSQL, Con)
            CmdAd.ExecuteNonQuery()

            Con.Close()

            i = i + 1

            'Next

        Catch ex As Exception
            lbError0.Text = ex.Message

        End Try

    End If
End Sub

Google how to read data from a SqlDataReader (hint: see how the answers/tutorials read back multiple records) and then google how to properly insert data into a database with a SqlCommand (hint: look for how to use parameters). There is a plethora of information out there and I am confident you can find and fix your mistakes. — Igor
– Igor, Commented Nov 10, 2017 at 15:53
Are you missing a loop here? Do you know what a loop is right? — Steve
– Steve, Commented Nov 10, 2017 at 16:00

Mr.Alvaro · Accepted Answer · 2017-11-10 17:02:30Z

0

First of all if I understand correctly, you want to put the information of those 40 rows and multiple columns into multiple arrays. If that is true then you are missing () when declaring the arrays.

        Dim mater() As String
        Dim planta() As String
        Dim almacen() As String
        Dim lot() As String
        Dim cantidad() As String
        Dim cantadiat() As String
        Dim undad() As String
        Dim Cantidadc() As String
        Dim CantidadB() As String
        Dim Session1() As String
        Dim fecha() As String    
        Dim RowCounter as Integer = 0

Second I will approach this different. I will run the query and put the result into a DataTable. Then with a For go through each row and start filling the arrays.

        MySQLOpenConnection()
        Dim MySQLExecute As New MySqlCommand(MySQLCommand, MySQLConnection)
        Dim MySQLAdapter As MySqlDataAdapter = New MySqlDataAdapter(MySQLExecute)
        Dim TableResult As New DataTable("QueryResult")

        MySQLAdapter.Fill(TableResult)
        MySQLCloseConnection()

        For each tablerow as DataRow in TableResult.Rows
            mater(RowCounter) = TableResult.Rows.Item(RowCounter).Item(0)
            planta(RowCounter) = TableResult.Rows.Item(RowCounter).Item(1)
            almacen(RowCounter) = TableResult.Rows.Item(RowCounter).Item(2)
            lot(RowCounter) = TableResult.Rows.Item(RowCounter).Item(3)
            cantidad(RowCounter) = TableResult.Rows.Item(RowCounter).Item(4)
            cantadiat(RowCounter) = TableResult.Rows.Item(RowCounter).Item(5)
            undad(RowCounter) = TableResult.Rows.Item(RowCounter).Item(6)
            Cantidadc(RowCounter) = TableResult.Rows.Item(RowCounter).Item(7)
            CantidadB(RowCounter) = TableResult.Rows.Item(RowCounter).Item(8)
            Session1(RowCounter) = TableResult.Rows.Item(RowCounter).Item(9)
            fecha(RowCounter) = TableResult.Rows.Item(RowCounter).Item(10)

            RowCounter=RowCounter+1
        Next

answered Nov 10, 2017 at 17:02

Mr.Alvaro

10910 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

lololo Over a year ago

now i got the error operator '&' is not defined for types 'string' and '1-dimensional array of byte'

Mr.Alvaro Over a year ago

Can you put your current code to see from where that error is coming from? Also I think maybe there are & inside the values you are retrieving from the database. If that is the case just add to each line .tostring. It will end up like TableResult.Rows.Item(RowCounter).Item(0).toString

lololo Over a year ago

from here : resultado = GM.AsigDupla(Txtfecha.Text, cboPlanta0.SelectedValue, cboPlanta0.SelectedItem.Text, cboAlmacen.SelectedValue, cboAlmacen.SelectedItem.Text, mater, lot) here is i wanna save it afer the for each

Mr.Alvaro Over a year ago

Put your code in a new post inside this thread. Hard to understand where each line ends.

lololo Over a year ago

resultado = GM.AsigDupla(Txtfecha.Text, cboPlanta0.SelectedValue, cboPlanta0.SelectedItem.Text, cboAlmacen.SelectedValue, cboAlmacen.SelectedItem.Text, mater, lot)

|

Mr.Alvaro · Accepted Answer · 2017-11-10 19:12:16Z

0

The comment did not have enough characters for the answer for the second issue so here it goes.

Ok, I see you are using values from the array mater, and lot, but you have to define which one of the values inside the array.

mater() and lot() are as long as the amount of rows of the of the table in the database. So if you have 20 rows on that table, the array lot will have 20 elements going from lot(0) to lot(19) and the same for the others.

If you need to get resultado for each row, then you have to define resultado() as an array and use the same FOR to fill it.

resultado(RowCounter)=GM.AsigDupla(Txtfecha.Text, cboPlanta0.SelectedValue, cboPlanta0.SelectedItem.Text, cboAlmacen.SelectedValue, cboAlmacen.SelectedItem.Text, mater(RowCounter), lot(RowCounter)

I hope this solve your issue.

answered Nov 10, 2017 at 19:12

Mr.Alvaro

10910 bronze badges

2 Comments

lololo Over a year ago

not work , whr i change like you said on the screen show : Unable to cast object of type 'System.String' to type 'System.String[]'. and inside the code the both varielbe sho green line that said : variable is used before it has been assigned a value a null reference exception

lololo Over a year ago

look here stackoverflow.com/questions/47229572/…

Collectives™ on Stack Overflow

Make a for each using SqlDataReader vb.net

2 Answers 2

7 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related