VB.NET insert records into SQL 2000 table using data from other tables

I have three tables in MS SQL server 2000 and I am creating an application using VB.Net.  I want to read information from two tables and populate the third.

tblProjects - fldProjectNumber, fldCustomerName, fldStartDate
tblTemplate - fldTaskName, fldStartDay, fldOwner
tblTasks - fldProjectNumber, fldTaskName, fldDueDate, fldOwner

In VB.Net I need to read tblProjects and get fldProjectNumber and fldStartDate.  I then need to read tblTemplate and get the list of tasks and their start day, and their owner.

These tasks then need to be inserted in to tblTasks where fldDueDate is fldStartDate + fldStartDay (fldStartDay is a number showing how many days before or after the project startdate a task is due).

iepaulAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BTosonCommented:
Hi,

What method are you using to access your database?
If you are using LINQ it will make things a lot easier!
0
iepaulAuthor Commented:
I am not using LINQ.  Can I use that with SQL 2000?  
0
BTosonCommented:
Depending on your targetted framework, LINQ acts as a data access layer between VB.NET and your SQL Server.
Otherwisde you are going to have to manually create all of the insert statements and execute them on the server.

What stage are you at with connecting to your server and getting data etc?
0
iepaulAuthor Commented:
This is the code I ended up with in my SQLDataSource_Inserted event.  It works but looks very messy.

                Dim connStr As String = ConfigurationManager.ConnectionStrings("KPIConnectionString").ConnectionString
                Dim myConnection As New SqlConnection(connStr)
                Dim myConnection2 As New SqlConnection(connStr)
                Dim myCommand, myCommand2, myCommand3 As SqlCommand
                Dim intResult As Integer
                strUser = tbUserID.Text
                Dim strID As String = e.Command.Parameters("@ProjectID").Value

                myConnection.Open()
                myCommand = New SqlCommand("UPDATE tblSAPrepProjects SET fldLastChange = 'added by " & strUser & "' WHERE fldID = " & strID, myConnection)
                intResult = myCommand.ExecuteNonQuery
                myCommand.Dispose()

                Dim strStartDate As Date
                myCommand = New SqlCommand("SELECT fldStart FROM tblSAPrepProjects WHERE fldID = " & strID, myConnection)
                Dim dr As SqlDataReader = myCommand.ExecuteReader()
                If dr.HasRows Then
                    dr.Read()
                    strStartDate = dr(0)
                Else
                    Label1.Text = "Error"
                End If
                dr.Close()
                myCommand.Dispose()

                myConnection2.Open()

                myCommand3 = New SqlCommand("SELECT fldTask, fldDay, fldDepartment, fldCostCentre FROM tblSAPrepTemplate", myConnection)
                Dim dr2 As SqlDataReader = myCommand3.ExecuteReader()
                If dr2.HasRows Then
                    While dr2.Read()
                        myCommand2 = New SqlCommand("INSERT INTO tblSAPrepTasks (fldTask, fldStartDate, fldDepartment, fldCostCentre, fldProjID) VALUES " & _
                                                    "('" & dr2(0) & "', '" & Format(strStartDate.AddDays(dr2(1)), "MM/dd/yyyy") & _
                                                    "' , '" & dr2(2) & "', '" & dr2(3) & "', " & strID & ")", myConnection2)
                        myCommand2.ExecuteNonQuery()

                    End While
                End If
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.