Insert via SqlDataAdapter

Hi, not sure this is feasible or if I can explain what I need to do, but...

I am creating a table in my console app, and filling it with data. I then need to do an insert to the database from my table. I have tried lots of different code snippets but so far nothing is working.

My database table has 6 columns but some have defaults so its only necessary to actually insert a few of the columns, and this is what I'm trying to do. Say I have columns A, B, C, D, E and F and I am basically trying to do INSERT INTO (A, C, E) VALUES --> all the rows in my datatable, which has only these three columns.

The few times I have gotten my code to actually get to where its connected and trying to do the insert, I get a Value cannot be NULL error... but that version of my code is long gone and I'm back to trying anything and everything.

Does anyone have any suggestions or referalls for me I'm just about stumped on this one.

Thanks.

Private Function SaveData(ByVal p_DataTable As DataTable) As Boolean

Try

Dim myDS As New DataSet()

Dim myDataAdapter As New SqlDataAdapter()

myDataAdapter.SelectCommand = New SqlCommand("SELECT A, C, E FROM MY_DB_TABLE WHERE 0 = 1", myDataConn)

Dim cb As SqlCommandBuilder = New SqlCommandBuilder(myDataAdapter)

myDataConn.Open()

myDataAdapter.Fill(myDS, "MyTable")

' Code to modify data in DataSet here

' Without the SqlCommandBuilder this line would fail.

myDataAdapter.Update(p_DataTable)

Catch ex As Exception

Console.WriteLine(ex.ToString)

Finally

myDataConn.Close()

End Try

End Function

Answer this question

Insert via SqlDataAdapter

John Veson
If you can point me to examples, that would be great... meanwhile I'll see what I can find. Thanks.
Vyatsek

You lost me when you said "roll your own INSERT/UPDATE/DELETE commands specifying on the values you pulled downyour own INSERT/UPDATE/DELETE commands specifying on the values you pulled down"

And I don't even understand what the CB does... just found it as part of some other post. It doesn't LOOK like its doing anything.
vtortola

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

http://davidhayden.com/blog/dave/archive/2006/03/08/2877.aspx

http://msmvps.com/blogs/williamryan/archive/2004/07/10/9890.aspx

Hope this helps. Bill
.net sukbir
The SqlBulkCopy is made for tasks like this. You can use a DataReader, nest them even and fire off the bulk copy. This should work well for you. let me know if you need any help.
akjal
CommandBuilders generate CRUD logic based on a SELECT statement. So to be able to update any operation, an adapter needs a valid UPDATE, DELETE and INSERT command. All in all, you need 4 statements. Each one can be different. So you can specify columnmappings for each parameter which will automatically use values from it. Let me see if I can find an in depth example.
Pooja Katiyar
THen just set AcceptChangesDuringFill to false on the dataset and don't call it until after the update. Everything will be fired with the INsert command in that case. Then just call UPdate, and make sure you have an INSERT command configured. That's the 'old' way of doing it.
Antony1
Sounds like the way to go, except... the DB table I need to insert into has 7 columns... I have only 3 column values to insert (so the default values are populated for the remaining columns). I'll try it but I have a feeling it won't work in this case.
JimBim76

SqlBulkCopy is not available to me actually. I am using Studio 7 with .NET Framework 1.0.

I DO have Framework 2.0 installed on my machine but all of my references point to the 1.0 folder. If I attempt to manually add a reference from the 2.0 folder I get an error that the .dll is not a valid assembly or COM component.

Hopelessness and despair are setting in... ;)
Jon Stroh
It's not the best way ;-). However if INSERTS are your primary concern, you can look to the SqlBulkCopy class for real speed - it's very easy to use and amazingly performant. Are you only dealing with INserts as far as performance goes
god_of_coolness

If that isn't the best way, then I'll avoid it. Its a very data-heavy app, churning lots and lots of record, so it needs to be the most efficient way it can be. I really want to avoid a loop with an insert for each record if at all possible.

Thanks for your help!
Loki70

I am not able to follow, with so many attempts on my part and jumping around from reply to reply. Can you point me to a sample of the big picture I would really appreciate it.

Thanks.
Shawn Wildermuth - MVP &#40;C&#35;&#41;

Ok, the CommandBuilder is not the way to go with this. Write your own SELECT command and just pull whatever values you are interested in. On the Database side, make sure your defaults are set correctly. Then, roll your own INSERT/UPDATE/DELETE commands specifying on the values you pulled down. As long as the DB has those other values set as defaults, you'll be ok.

P.S. Not an issue concerning this problem, but you probably want to put a Return True/False in there.
Tony Abraham

I have a read, and for every record in that read is another read with an insert to another table. I'm trying to do this to keep it as streamlined as possible:

initial READ loop

nested READ
add records to datatable for bulk insert later

once all records are written to the DT, I want to do a massive insert.
Insert via SqlDataAdapter

Answer this question