Inserting records using sqldataadapter 
Author Message
 Inserting records using sqldataadapter

I am creating a temporary table in SQL Server where new
records are going to be inserted from the a customer table.
I can not get it to work when inserting into a "CREATE
TABLE"..... however, it works if I select records from an
existing table.
Is it a way around to accomplish this insert? Thanks!
My example is:

  Private Sub Load  ...........
        Dim custno, tempCust as string
        tempQuery = "CREATE TABLE #myTempTable
(Customername varchar(20) NULL)"
        strQueryCust = "SELECT * from Customers WHERE
customernumber  = '" & custno & "'"

            oTempDA = New SqlDataAdapter(tempQuery,
SqlConnection1)

            Dim oDS As DataSet = New DataSet()
            Dim oDA As SqlDataAdapter = New SqlDataAdapter
()
            oDA = New SqlDataAdapter(strQueryCust,
SqlConnection1)
            oDA.Fill(oDS, "Customers")

            For i = 0 To oDS.Tables
("Customers").Rows.Count - 1
                tempCust = oDS.Tables("Customers").Rows
(i).Item("dermnumber").TRIM
                InsertTemp(tempCust)
            Next
            oDA.dispose
        End SUb

           Sub InsertTemp(Byval tempcust as string)

                StrQuery = "INSERT INTO #myTempTable  

                Dim InsertCmd As New SqlCommand(StrQuery,
SqlConnection1)

                oTempDA.InsertCommand = InsertCmd
                oTempDA.InsertCommand.Connection =
SqlConnection1
                oTempDA.InsertCommand.Parameters.Add(New

20, "dermnumber"))
                oTempDA.InsertCommand.Parameters(0).Value
= tempCust

                SqlConnection1.Open()
                oTempDA.InsertCommand.ExecuteNonQuery()

           End Sub



Tue, 11 Jan 2005 23:56:45 GMT  
 Inserting records using sqldataadapter
Do you get an error when inserting the row?

Yannick.

--
This posting is provided "AS IS" with no warranties, and confers no rights.


Quote:
> I am creating a temporary table in SQL Server where new
> records are going to be inserted from the a customer table.
> I can not get it to work when inserting into a "CREATE
> TABLE"..... however, it works if I select records from an
> existing table.
> Is it a way around to accomplish this insert? Thanks!
> My example is:

>   Private Sub Load  ...........
> Dim custno, tempCust as string
>         tempQuery = "CREATE TABLE #myTempTable
> (Customername varchar(20) NULL)"
>         strQueryCust = "SELECT * from Customers WHERE
> customernumber  = '" & custno & "'"

>             oTempDA = New SqlDataAdapter(tempQuery,
> SqlConnection1)

>             Dim oDS As DataSet = New DataSet()
>             Dim oDA As SqlDataAdapter = New SqlDataAdapter
> ()
>             oDA = New SqlDataAdapter(strQueryCust,
> SqlConnection1)
>             oDA.Fill(oDS, "Customers")

>             For i = 0 To oDS.Tables
> ("Customers").Rows.Count - 1
>                 tempCust = oDS.Tables("Customers").Rows
> (i).Item("dermnumber").TRIM
> InsertTemp(tempCust)
>             Next
>     oDA.dispose
> End SUb

>    Sub InsertTemp(Byval tempcust as string)

>                 StrQuery = "INSERT INTO #myTempTable

>                 Dim InsertCmd As New SqlCommand(StrQuery,
> SqlConnection1)

>                 oTempDA.InsertCommand = InsertCmd
>                 oTempDA.InsertCommand.Connection =
> SqlConnection1
>                 oTempDA.InsertCommand.Parameters.Add(New

> 20, "dermnumber"))
>                 oTempDA.InsertCommand.Parameters(0).Value
> = tempCust

>                 SqlConnection1.Open()
>                 oTempDA.InsertCommand.ExecuteNonQuery()

>    End Sub



Sat, 22 Jan 2005 04:48:25 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Loading tables from a database via SQLDataAdapter and SQLDataAdapter.Fill

2. SQLDataAdapter parameter not supplied during insert?

3. SQL Stored Proc for insert/ SQLDataAdapter insertcommand Subroutine

4. controling transaction using dataset and sqldataadapter

5. Using parameterized stored procedures with SqlDataAdapter?

6. Using DAO to insert records

7. ADP: Insert New Record using ADO

8. Inserting new records using ADO - problem??

9. Insert records using DBGrid??

10. How to insert record with OLE field using VB

11. Inserting new records using ADO - problem??

12. Insert records using DBGrid??

 

 
Powered by phpBB® Forum Software