Insert Data from a User Created DataSet?

[John Rugo]

Hi All,
I am trying to do the fowoing:

1. Create a DataSet (no problem here) Code below:

'-----------------------------------Create DataSet

Dim myDS As New Data.DataSet("CIRCUITS")

Dim myCircuits As Data.DataTable = myDS.Tables.Add("CIRTBL")

Dim myDr As Data.DataRow



With myCircuits

.Columns.Add("CircuitBucket", Type.GetType("System.String"))

.Columns.Add("CircuitType", Type.GetType("System.String"))

.Columns.Add("SiteID", Type.GetType("System.String"))

.Columns.Add("SiteIDB", Type.GetType("System.String"))

.Columns.Add("Requestor", Type.GetType("System.String"))

.Columns.Add("Provisioner", Type.GetType("System.String"))



End With



'-----------------------------------Populate DataSet

For Each lvNode In lv.Items

myDr = myCircuits.NewRow()

myDr("CircuitBucket") = lvNode.SubItems(45).Text

myDr("CircuitType") = lvNode.SubItems(46).Text

myDr("SiteID") = lvNode.SubItems(39).Text

myDr("SiteIDB") = lvNode.SubItems(40).Text

myDr("Requestor") = lvNode.SubItems(41).Text

myDr("Provisioner") = lvNode.SubItems(42).Text



myCircuits.Rows.Add(myDr)

Next



2. Now I want to insert the data using a Stored Procedure (SQL)



'-----------------------------------INSERT DataSet

cnS = New SqlConnection(cnMAIN$)

cnS.Open()



Dim SQLCMD As New SqlCommand

SQLCMD.Connection = cnS

SQLCMD.CommandText = "SELECT * FROM myTable"

SQLCMD.CommandType = CommandType.Text



daS = New SqlDataAdapter

daS.SelectCommand = SQLCMD

daS.Fill(myDS, "myTable")



Dim cmdINSERT As New SqlCommand("usp_INSERT_CIRCUIT")

cmdINSERT.CommandType = CommandType.StoredProcedure



cmdINSERT.Parameters.Add(New SqlParameter("@CircuitBucket",
SqlDbType.VarChar, 10, "CircuitBucket"))

cmdINSERT.Parameters.Add(New SqlParameter("@CircuitType",
SqlDbType.VarChar, 10, "CircuitType"))

cmdINSERT.Parameters.Add(New SqlParameter("@SiteID",
SqlDbType.VarChar, 100, "SiteID"))

cmdINSERT.Parameters.Add(New SqlParameter("@SiteIDB",
SqlDbType.VarChar, 100, "SiteIDB"))

cmdINSERT.Parameters.Add(New SqlParameter("@Requestor",
SqlDbType.VarChar, 100, "Requestor"))

cmdINSERT.Parameters.Add(New SqlParameter("@Provisioner",
SqlDbType.VarChar, 100, "Provisioner"))



daS.InsertCommand = cmdINSERT

cmdINSERT.Connection = cnS



My problem here is definatly, and at leaset, the SelectCommand is failing
with an error stating that it does not know what the Table object supplied
is. In the above example it is myTable. I don't even need a select command
because I am inserting only; but I get an error if I exclude the
SelectCommand.



The reason I am doing this is that I need to pass the data into a stored
procedure that will in turn decide which Insert statement to use based on
the @CircuitBucket Parameter.



Any ideas regading the proper way of dealing with this please help.

Thanks,



John.

 

[kalpesh at vsnl dot net]

I think, you can ignore DataTable creation. Since, you need to run an SP
in a loop, with values from rows in a listview

You can do that simply by creating a command & parameters as in step 2 &
run a loop to pass parameters to it (which will come from listview)

HTH
Kalpesh

 

[John Rugo]

I'm not sure what you mean?

I think, you can ignore DataTable creation. Since, you need to run an SP
in a loop, with values from rows in a listview

You can do that simply by creating a command & parameters as in step 2 &
run a loop to pass parameters to it (which will come from listview)

HTH
Kalpesh

 

[Kalpesh Shah]

Ultimately, what you are doing ?

Loop thru the listview & use them as a parameter to call an SP, right ?
So, no need to use a datatable as an intermediate storage

1 Create an oledbcommand (for the storedproc which you want to call)
2 Define parameters (name/type/direction/value) for the params that SP
reuiqres
3 Write a loop, for listview, in which, supply the value for all
parameters (which will come from listview subitems) & execute by call
oledbcommand.executenonquery (or any other method, you feel appropriate)

I hope you got what I am saying

Kalpesh

 

[John Rugo]

I got it right after I sent the last message. I needed on the last step:
basically I neeed to rearange my code.

cnS = New SqlConnection(cnMAIN$)

cnS.Open()



daS = New SqlDataAdapter



Dim cmdINSERT As New SqlCommand("usp_IMPORT_ML_PCCM")

cmdINSERT.CommandType = CommandType.StoredProcedure



cmdINSERT.Parameters.Add(New SqlParameter("@id",
SqlDbType.VarChar, 100, "RID"))

cmdINSERT.Parameters.Add(New SqlParameter("@CircuitBucket",
SqlDbType.VarChar, 11, "CircuitBucket"))

...........



daS.InsertCommand = cmdINSERT

cmdINSERT.Connection = cnS



daS.Update(myDS, "CIRTBL")



I have had problems looping through rows and inserting them into databases.
It would seem that the connection erros indicating that the connection is
being used or is closed or something. It may have to do with the speed at
which data is being passed and the connection is being open or closed. I
origionally ran into this when trying to delete multiple rows of data from a
ListView. I would pass in the column containing the unique record ID to a
stored proc. and it failed everytime. So I started creating datasets to
handle this. If there was a better way I would like to know.



John.