CODEWITHSUNDEEP
mysqlsqlvb.net
earl chester siaotong
earl chester siaotong

Reputation: 103

Fill in a datatable with data from database

so here's what im tryng to do. I've got some data to fill a datatable from mysql database. As referred to sample projects, I've been extracting those details doing a SELECT query from an existing table(named examquestion). my query would be something like this:

SELECT * FROM entrancequestion
WHERE Subject='Abstract Reasoning'
ORDER BY RAND()
LIMIT 10)

UNION

(SELECT * FROM entrancequestion
WHERE Subject='English'
ORDER BY RAND()
LIMIT 30)

UNION

(SELECT * FROM entrancequestion
WHERE Subject='Mathematics'
ORDER BY RAND()
LIMIT 30)

UNION

(SELECT * FROM entrancequestion
WHERE Subject='Science'
ORDER BY RAND()
LIMIT 30 )

so all in all the datatable should be filled 100 rows total.

my problem is actually inserting those data to a datatable

first i read the data from the database

dim myqry as string    
'where myqry as the codes above
dim examdt as new datatable
Dim conn As New SqlClient.SqlConnection("server=localhost;User Id=root;database=jnhsdb")

    Try
        conn.Open()

        Dim cmd As SqlCommand = New SqlCommand(myqry, conn)
        'create data reader
        Dim rdr As SqlDataReader = cmd.ExecuteReader

        'loop through result set
        rdr.Read()

        'now im lost on this part.
        'i want to insert the data into a data table. i got some code in mind but i think im doing it wrong.




        conn.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try

so, how do you actually insert those data from a database to a datatable?

follow up question: would it be possible to create a dataset from the datatable along with those queries?

Upvotes: 1

Views: 2477

Answers (1)

suff trek
suff trek

Reputation: 39767

First of all - you contradict yourself. Your query is from MySQL, while your code is for SQL Server. You have to decide what you want to use. If it's indeed MySQL - you need to install MySQL.NET connector. The code below will be almost identical, only instead SQL* classes you will be using MySQL* classes (e.g. MySqlDataAdapter instead of SqlDataAdapter).

That said:

Don't use DataReader to fill the DataTable, use DataAdapter instead. After you created your SqlCommand - try this:

Dim da As New SqlDataAdapter(cmd)
da.Fill(examdt)

Later if needed - you can creatre a DataSet and add this table:

Dim ds As New DataSet
ds.Tables.Add(examdt)

But there're other ways. For example .fill() method of the SqlDataAdapter above is capable of filling DataSet directly instead of DataTable (it will create a table automatically)

Upvotes: 1

Related Questions