Add data in datatable to table in MSSQL

[jcs1953]

I have the following code:

Dim mytable As New DataTable
Dim mytable2 As New DataTable
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connectionString As String = GetConnectionString("XE_ConnectionString")
Dim providerFactory As DbProviderFactory = DbProviderFactories.GetFactory("Oracle.DataAccess.Client")
Dim Sql = "SELECT RESULTS.RESULT_NUMERIC,REQ_PANELS.RUN_DATE FROM REQ_PANELS, REQUISITIONS, RESULTS, TESTS WHERE REQUISITIONS.ACC_ID = REQ_PANELS.ACC_ID AND REQ_PANELS.RP_ID = RESULTS.RP_ID AND RESULTS.TEST_ID = TESTS.TEST_ID AND (REQUISITIONS.PAT_ID = '3848.1') AND (TESTS.TEST_NAME = 'BUN') ORDER BY REQ_PANELS.RUN_DATE"
Dim connection As DbConnection = providerFactory.CreateConnection
connection.ConnectionString = connectionString
Dim command As DbCommand = providerFactory.CreateCommand
command.Connection = connection
command.CommandText = Sql
command.CommandType = CommandType.Text
connection.Open()

Dim dr As DbDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
mytable.Load(dr)

GridView1.DataSource = mytable
GridView1.DataBind()
connection.Dispose()
command.Dispose()


This gives me a datatable in memory called 'mytable'. The table looks like this:
RESULT_NUMERIC RUN_DATE
10.2 2/4/2009
8.7 3/5/2009

I also have a table in an SQL database called 'Combined_results' with the same column headings (empty rows).
What I need to do is this:
1. Empty the SQL table.
2. Copy the 'mytable' data to the SQL table.
There is no primary key.
I imagine that this is easy to do for the experts but is giving me fits.
Thanks

 

[RiverGuy]

Something like the following should work:

        Dim Con As New SqlClient.SqlConnection("some connection string")
        Con.Open()
        Dim Cmd As New SqlClient.SqlCommand
        With Cmd
            .Connection = Con
            .CommandType = CommandType.Text
            .CommandText = "TRUNCATE TABLE Combined_results"
            .ExecuteNonQuery()
            .CommandText = "INSERT INTO Combined_results (RESULT_NUMERIC, RUN_DATE) VALUES (@RESULT_NUMERIC, @RUN_DATE)"
            .Parameters.Add("@RESULT_NUMERIC", SqlDbType.Decimal)
            .Parameters.Add("@RUN_DATE", SqlDbType.DateTime)
        End With
        For Each dr As DataRow In myTable.Rows
            Cmd.Parameters("@RESULT_NUMERIC").Value = dr.Item("RESULT_NUMERIC")
            Cmd.Parameters("@RUN_DATE").Value = dr.Item("RUN_DATE")
            Cmd.ExecuteNonQuery()
        Next
        Con.Close()

Of course, add exception handling, clean up your objects, and even wrap these into a transaction if you wish.

 

[jcs1953]

I can't seem to get it to work. In stepping through the code (I put it under a BTN2_Click), it gets to: Cmd.Parameters("@RESULT_NUMERIC").Value = dr.Item("RESULT_NUMERIC")
and then goes directly to con.close. No errors but no update either. I'm sure the connection string is correct. Any ideas?

 

[RiverGuy]

Try

Cmd.Parameters("@RESULT_NUMERIC").Value = Convert.ToDecimal(dr.Item("RESULT_NUMERIC"))

 

[jcs1953]

I forgot to mention that the data type for Result_numeric is Float. I changed that in the code.
I tried this: Removed everything in the Btn2_Click and put
GridView1.DataSource = mytable
GridView1.DataBind()
The mytable variable is Dim mytable As New DataTable and is a Page variable. When I run it Gridview1 doesn't populate. Shouldn't mytable be availiable in the private sub Btn2_click event?

 

[jcs1953]

Also another question. In the same Protected sub btn1_click that I have the code in the first post I have:
Dim connectionString1 As String = GetConnectionString("FMC_AccessConnectionString")
Dim providerFactory1 As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.SqlClient")
Dim SQL1 = "SELECT Round(CMPatientDataFile.Result_Numeric,2)as RESULT_NUMERIC,CMPatientDataFile.Run_Date FROM CMPatientDataFile WHERE CMPatientDataFile.Pat_ID = '3848.1' AND CMPatientDataFile.Test_Name = 'BUN' ORDER BY CMPatientDataFile.Run_Date"
Dim connection1 As DbConnection = providerFactory1.CreateConnection
connection1.ConnectionString = connectionString1
Dim command1 As DbCommand = providerFactory1.CreateCommand
command1.Connection = connection1
command1.CommandText = SQL1
command1.CommandType = CommandType.Text
connection1.Open()

Dim dr2 As DbDataReader = command1.ExecuteReader()
mytable.Load(dr2)

GridView2.DataSource = mytable
GridView2.DataBind()

connection1.Dispose()
command1.Dispose()
This is basically the same except for quering the SQL database. Can I have more than one query? Dim SQL and Dim SQL1 and run both while the connection to the database is open?

 

[RiverGuy]

Why do you want to run two queries at the same time? You mentioned something about a "Page variable". Is this an ASP.Net application? If so, I'm thinking your filled DataTable might be out of scope. That could be one reason no records are inserted into the SQL table. If you think your issue has something to do with the ASP.Net issue, I would suggest you post this up in the ASP.Net forum as you'll find more experts familiar with .Net web programming over there.

 

[jcs1953]

Thanks for trying to help. I try posting at the ASP.Net forum.