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

Posted on 2008-11-04
Last Modified: 2012-05-05
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).

Question by:iepaul
    LVL 4

    Expert Comment


    What method are you using to access your database?
    If you are using LINQ it will make things a lot easier!

    Author Comment

    I am not using LINQ.  Can I use that with SQL 2000?  
    LVL 4

    Expert Comment

    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?

    Accepted Solution

    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

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

                    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
                        strStartDate = dr(0)
                        Label1.Text = "Error"
                    End If


                    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)

                        End While
                    End If

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
    1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now