Not sure if VB2003 can do this, but in VB2005 the dataset object has a
CreateDataReader property for passing data from a .Net dataset table to sql
server. Here is the code for VB2005 (might work in VB2003)
First: you need a destination table on your sql server DB. The table
structure needs to match the .Net table in your app that the datagridview is
pulling from. If the data in the datagridview is not based on a data table,
then you need to loop through the datagridview (VB2005 - datagrid in VB2003)
and populate a datatable contained in a dataset. Once you have a data table
which is contained in a dataset and also a corresponding destination table on
the server DB -- you can do this the following using a sqlDataAdapter:
'--just getting the table structure into memory here
da.SelectCommand.CommandText = "Select * From serverTable"
da.Fill(ds, "serverTbl")
da.InsertCommand.CommandText = "Insert Into ServerTable(rowID, sName, BDay)
Select @rowID, @sName, @BDay"
da.InsertCommand.Parameters.Add("@rowID", SqlDbType.Int, 4, "rowID")
da.InsertCommand.Parameters.Add("@sName", SqlDbType.varchar, 50, "sName")
da.InsertCommand.Parameters.Add("@BDay", SqlDbType.datetime, 8, "BDay")
Dim reader As DataTableReader = ds.Tables("localAppTbl").CreateDataReader
ds.Tables("serverTbl").Load(reader, LoadOption.Upsert)
da.Update(ds, "serverTbl")
this statement
-->>ds.Tables("serverTbl").Load(reader, LoadOption.Upsert)
is where the transfer of data occurs from your local .Net dataTable to the
destination table on the server DB. Note the arg -- LoadOption.Upsert
Upsert is the LoadOption that worked for me. Then of course, you have to call
-->>da.Update(ds, "serverTbl")
to write the data from memory to the destination table on the disk.
If VB2003 doesn't support this code, you can always loop through your local
..Net dataTable -- this requires you to manually open the sql connection
object.
If conn1.State = ConnectionState.Closed Then conn1.Open()
For Each dr As DataRow In ds.Tables("yourLocalTbl").Rows
da.SelectCommand.CommandText = "Insert Into ServerTable Select " &
dr("rowID").Value.ToString & ", '" & dr("sName").Value.ToString & ", '" &
dr("BDay").Value.ToString & "'"
da.SelectCommand.ExecuteNonQuery
Next
conn1.Close
Regards,
Rich
