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?
 
iepaulConnect With a Mentor Author 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.