Solved

Append one table to another with variables

Posted on 2008-10-22
10
335 Views
Last Modified: 2010-04-21
Hi,
I am creating an audit system asp net website based on one I have in access.
I have a table that stored details about the audit, date, site etc (AuditDates) and another table (AuditQuestions) that stores each separate question related by the AuditID field.
When creating a new audit I need to append all the rows from my master question table (Questions) into the AuditQuestions also including the correct AuditID from the AuditDates table to link the tables (this will have to be done using variable on the insert statement).
I am completely new to asp.net so any help would be greatly appreciated,
Thanks,
Iain
0
Comment
Question by:RBG IT
  • 6
  • 4
10 Comments
 
LVL 10

Expert Comment

by:jinn_hnnl
ID: 22774210
Is this a question about SQL insert statement which implemented within ASP.NET or is this ASP.NET only? I was abit confuse about your question.

The way I see it, you are asking how do insert that into the database ?

Can you show us what you have now or is this you ask for an idea? If you ask for an idea: I think you have to first insert the Audit data to Audit table, because this will be the parent table, the AuditID will be there after creation (autogenerated or manually),

then you insert the same number of row in Questions to AuditQuestions with AuditID.

So I gues AuditQuestion will have similar columns as the Questions (standard table) and some another more columns which include AuditID - this refer to the Audit table

Is that waht you ask for?

JINN
0
 

Author Comment

by:RBG IT
ID: 22774267
Hi JINN,
I will have to insert a record into the AuditDates first to gerenate the AuditID.
I then have to append the questions from the Questions table (master question set) into the AuditQuestions table including the AuditID from the AuditDates so the 2 are now related and i have a full set of questions that can now be updated for the audit.
I have included the VBA version I created in Access.
Cheers,
Iain


Do Until rstQuestions.EOF                '

        AuditID = CostCentre & ReportTypeID & YearNumber & WeekNumber

        DoCmd.RunSQL _

                "INSERT INTO AuditQuestions (QuestionNo,AuditID,ReportDateID,ReportTypeID,ReportHeaderID,ReportQuestionsID,Question,Count,PriorityID,NonQuestion) VALUES('" _

                & rstQuestions!Order & "','" & AuditID & "','" & _

                    ReportDateID & "','" & ReportTypeID & "','" & _

                    rstQuestions!ReportHeaderID & "','" & _

                    rstQuestions!ReportQuestionsID & "','" & _

                    rstQuestions!ReportQuestion & "','" & _

                    TotalCount & "','" & rstQuestions!PriorityID _

                    & "','" & rstQuestions!NonQuest & "');"

    rstQuestions.MoveNext

Loop

Open in new window

0
 
LVL 10

Expert Comment

by:jinn_hnnl
ID: 22774340
Ohhh ok, so you want to have similar idea in ASP.NET right?

Which language do you want it to be ? VB or C#?
0
 

Author Comment

by:RBG IT
ID: 22774359
Hi Jinn,
I have been trying to create it in ASP.NET without any success!
It would certainly be easier for me in VB as it is more familier,
Cheers,
Iain
0
 
LVL 10

Expert Comment

by:jinn_hnnl
ID: 22774506
Check out this code, the StringBuilder part you can continue with that idea.

I can run this code at my computer now, so you might wanna check the syntax, but this is the idea you should follow, I think you have an Access database store somewhere, so give it the right path, incase you work with MSSql you will use Sql..... instead of OleDB...

Hope this helps

JINN

'Creating connection and command sting

Dim conStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source=D:\\Databases\yourdb.mdb"

Dim conn As OleDbConnection = New OleDbConnection(conStr)

'Now you create a data adapter. A data adapter constructor takes two arguments - A SQL string and a connection object.
 

Dim sqlStr As String = "SELECT * FROM Questions"

' Create data adapter object

Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlStr, conn)  
 

' Create a dataset object and fill with data using data adapter's Fill method

Dim ds As DataSet = New DataSet

da.Fill(ds, "Questions") 
 

//AuditID, this is where you get your AuditID, shouldnt' it be from database?

'AuditID = CostCentre + ReportTypeID + YearNumber + WeekNumber

Dim querySB AS New  System.Text.StringBuilder

For Each drDSRow In DS.Tables("Questions").Rows()

        querySB.Append("INSERT INTO AuditQuestions (QuestionNo,AuditID,ReportDateID,ReportTypeID,ReportHeaderID,ReportQuestionsID,Question,Count,PriorityID,NonQuestion) VALUES('")

	querySB.Append(drDSRow("Order") + "', '" + AuditID + "', '" ReportDateID + "', '" + ReportTypeID + "', '" + drDSRow("ReportHeaderID"...........)) 'the rest of your sql query

	dim cmd as new OleDbCommand(querySB.ToString(), conn)

	conn.Open()

	cmd.ExecuteNonQuery '<-- this will return no of row that effected, > 0 means success

	conn.Close()

Next

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:RBG IT
ID: 22775027
Hi Jinn,
Thanks for that it certainly gives me a much better understanding of how it is done.
On you notes you mention the source for the AuditID - it will be taken from a hidden textbox on the page.
I am connecting to SQL Server (the database has been converted from access).
I have setup a table adaptor for the questions table - how would I adapt you code to work with it?
Sorry to ask so much but I am struggling rather a lot!
Cheers,
Iain
0
 
LVL 10

Expert Comment

by:jinn_hnnl
ID: 22775085
I will be out for lunch for awhile, I will keep you posted after

brb

JINN
0
 
LVL 10

Accepted Solution

by:
jinn_hnnl earned 500 total points
ID: 22775406
I am back,

Here is the code reference, complete the StringBuilder and test it out, you have to make sure, your connectionString to connect to the SQLServer database is right, that your application has access the the given database.

Hope this helps
JINN

'Creating connection and command sting

'Make sure you have right configuration so you can connect to the database successfully. Check authentication etc...

Dim conStr As String = "Data Source=212.85.123.213;Initial Catalog=MyDataBaseName;Integrated Security=True" 

Dim conn As SqlConnection = New SqlConnection(conStr)

'Now you create a data adapter. A data adapter constructor takes two arguments - A SQL string and a connection object.

 

Dim sqlStr As String = "SELECT * FROM Questions"

' Create data adapter object

Dim da As SqlDataAdapter = New SqlDataAdapter(sqlStr, conn)  

 

' Create a dataset object and fill with data using data adapter's Fill method

Dim ds As DataSet = New DataSet

da.Fill(ds, "Questions") 

 

'AuditID = CostCentre + ReportTypeID + YearNumber + WeekNumber

Dim querySB AS New  System.Text.StringBuilder

For Each drDSRow In DS.Tables("Questions").Rows()

        querySB.Append("INSERT INTO AuditQuestions (QuestionNo,AuditID,ReportDateID,ReportTypeID,ReportHeaderID,ReportQuestionsID,Question,Count,PriorityID,NonQuestion) VALUES('")

        querySB.Append(drDSRow.Item("Order") + "', '" + AuditID + "', '" ReportDateID + "', '" + ReportTypeID + "', '" + drDSRow.Item("ReportHeaderID"...........)) 'the rest of your sql query

        dim cmd as new SqlCommand(querySB.ToString(), conn)

        conn.Open()

        cmd.ExecuteNonQuery '<-- this will return no of row that effected, > 0 means success

        conn.Close()

Next

Open in new window

0
 

Author Closing Comment

by:RBG IT
ID: 31508653
Thanks for that Jinn, I have to play about with field formats a little but it has solved my problem and given me a better understanding,
Cheers,
Iain
0
 
LVL 10

Expert Comment

by:jinn_hnnl
ID: 22784549
Glad to help,

Later on you will find it's helpful to separate Data handle class, but this will do for most of cases.

Jinn
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

864 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

22 Experts available now in Live!

Get 1:1 Help Now