ADO.Net & sql server

[ChrisHiggins]

I am new to ado.net and need to do the following:-

1. Read the records in an SQL table where the processed field is false.

2. I then need to check these records and if incorrect write a record into a seperate sql table.

3. if the record is correct then I need to update the processed field to true.

Can anybody help.

Thanks

Chris

 

[ca8msm]

Yes, what do you have so far?


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[chrissie1]

YOu could probably all do this with plain sql without having to use vb.

But like ca8msm (I really do think you need a namechange) said what do you have so far.

Christiaan Baes
Belgium

"Time for a new sig." - Me

 

[ChrisHiggins]

I am porting this from vb6/ado so have all the coding there

I know how to connect to the sql database. and to read records using the datareader. This however only gives me read-only access and I need to be able to update the records so need to know how to access the records as read-write.

I also do not know how to add records to a table.

I agree that I could do all of this in SQL except for the fact that I need to access a third party database vie com objects to validate the data.


Thanks for the replies

Chris

 

[chrissie1]

look for dataadapter and command objects and fill and datatable and you will find millions of examples.

Christiaan Baes
Belgium

"Time for a new sig." - Me

 

[SBendBuckeye]

Hello Chris,

I'm assuming you have 3 stored procs to select 1st table, insert into 2nd table and update 1st table. Let's say you wanted to pull the data into a listbox so you could peruse it and select the items which were bad.

The following is pretty close but is off the top of my head so double check the syntax.

1. Create DataTable for selected data

Imports System
Imports System.Data
Imports System.Data.SqlClient

Private m_DataSet As New DataSet

Sub GetData

    Dim connection As New SqlConnection("YourConnection")
    Dim command As New SqlCommand("StoredProc1", connection)
    command.CommandType = StoredProcedure
    ' Optional if you have parms, repeat as necessary
    command.Parameters.Add("@Parm1", SqlType)
    command.Parameters(0).Value = SomeValue

    Dim dataAdapter as New SqlDataAdapter(command)
    ' Fill the DataSet
    dataAdapter.Fill(m_DataSet, "YourTableName")

    ' Load the ListBox
    Me.YourListBox.DataSource = m_DataSet.Tables("YourTableName")

    ' Define table and columns for 2nd table
    Dim table As New DataTable
    
    ' Repeat as necessary
    Dim column As New DataColumn(etc)
    table.Columns.Add(column)

End Sub

Elsewhere in the code, you would spin through the MultiSelect ListBox and add a row into the 2nd Table for everything that was selected, otherwise you would set your Boolean to True and call the remaining Stored Procs.

Hopefully this should get you started. Good Luck!


Have a great day!

j2consulting@yahoo.com