RBG IT
asked on
Append one table to another with variables
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
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
ASKER
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
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
Ohhh ok, so you want to have similar idea in ASP.NET right?
Which language do you want it to be ? VB or C#?
Which language do you want it to be ? VB or C#?
ASKER
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
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
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
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
ASKER
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
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
I will be out for lunch for awhile, I will keep you posted after
brb
JINN
brb
JINN
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Cheers,
Iain
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
Later on you will find it's helpful to separate Data handle class, but this will do for most of cases.
Jinn
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