[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 595
  • Last Modified:

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).

0
iepaul
Asked:
iepaul
  • 2
  • 2
1 Solution
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now