Member Avatar for bornok15

I'm currently building a VB.net Application and I'm having a problem on reading out records to use in making another record or for computations. For example: table A records will be read and will compute an output to be saved on table B. Tried to use reader on this but i dont seem to get the right syntax. Anyone who could help me out on this? thanks!

  • 3 Contributors
  • 4 Replies
  • 119 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by kvprajapati

Dani AI

Generated

wanted to read rows from TableA in VB.NET, compute values, and save them to TableB. demonstrated the basic ExecuteReader idea but used SqlClient and a pattern that can cause runtime problems (for example executing other commands while the DataReader is still open, or closing the connection before closing the reader). asked to see code — that is useful for diagnosing exact errors.

Two reliable patterns:

  • Server-side: do the work in SQL when possible. Single-statement approaches avoid round-trips and locking surprises. Example SQL (MySQL):

    INSERT INTO TableB (id, computed)
SELECT id, value1 * value2
FROM TableA
WHERE ...

  • Client-side: read ALL rows into memory, close the reader, then perform parameterized inserts in a transaction. This avoids "reader still open" errors and lets you prepare and reuse a single insert command for speed.

Important MySQL/VB.NET tips

  • Use MySql.Data.MySqlClient when talking to MySQL (Connector/NET).
  • Always use Using blocks so connections/commands/readers are disposed.
  • Check rdr.IsDBNull before reading and prefer GetOrdinal to avoid index fragility.
  • Use parameterized commands (no string concatenation) and a transaction for batches.
  • Do not execute inserts on the same connection while the reader is open (MySQL does not support MARS). Close the reader first.

Example workflow (conceptual):

  • Open connection.
  • Execute reader, load computed values into a List(Of T) or DataTable, close reader.
  • Begin transaction, prepare a parameterized insert, loop the list calling ExecuteNonQuery, commit.

For connector docs and examples consult the MySQL Connector/NET documentation: MySQL Connector/NET docs.

Recommended Answers

All 4 Replies

Member Avatar for bornok15

or maybe point me out to an example on the internet.

Member Avatar for Piya27

well, I am not much sure if this helps...

You cud use ExecuteReader and store the values in some variable and then use those variables to be stored in other table entry..

for eg:

Dim con As SqlConnection
Dim cmd As SqlCommand
Dim query As String = ""
Dim a As String 
Dim b As Integer
Dim dr As SqlDataReader

con = New SqlConnection(". . . . ")
query = ("SELECT * FROM MYTABLE1")
con.open()

cmd = New SqlCommand(query,con)

dr = cmd.ExecuteReader

While dr.Read
   a = GetString(0)
   b = GetInt32(1)
End While

Dim query2 As String = ""
Dim cmd2 As SqlCommand

query2 = "INSERT INTO MYTABLE2 VALUES(' " & a & "','" & B "')"
cmd2 = New SqlCommand(query2,con)

cmd2.ExecuteNonQuery

con.Close()
dr.Close()
Member Avatar for Piya27

well, I am not much sure if this helps...

You cud use ExecuteReader and store the values in some variable and then use those variables to be stored in other table entry..

for eg:

Dim con As SqlConnection
Dim cmd As SqlCommand
Dim query As String = ""
Dim a As String 
Dim b As Integer
Dim dr As SqlDataReader

con = New SqlConnection(". . . . ")
query = ("SELECT * FROM MYTABLE1")
con.open()

cmd = New SqlCommand(query,con)

dr = cmd.ExecuteReader

While dr.Read
   a = GetString(0)
   b = GetInt32(1)
End While

Dim query2 As String = ""
Dim cmd2 As SqlCommand

query2 = "INSERT INTO MYTABLE2 VALUES(' " & a & "','" & B "')"
cmd2 = New SqlCommand(query2,con)

cmd2.ExecuteNonQuery

con.Close()
dr.Close()
Member Avatar for kvprajapati

bornok15,
Can I see your code? Post source code with BB code tags.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.