How to save data and retrieve data from database using two tables in gridview?

[snamyna]

Hello guys.. I've been searching for a solution of my problem weeks ago but it did not work out. My problem goes like this: I am using a gridview and use a sqldatasource to retrieve the data in database to display the questions that the users need to answer. Then, i add new column for answer column where in that column, i put a radiobuttonlist with Yes/No options. I have been used the sqldatasource to retrieve the data earlier, so of course it is impossible to use one more sqldatasource to save the answer entered by the users. for your information, the answer column is from different table in same database with the question table. How is it to make possible to retrieve the data and save the data in different table in gridview? Please, i really need your help.. Thanks for your time.

[ktrrzn]

Hi snamyna, As far as i know, in your problem, u can combine these two tables into one if they used QuestionId as primary key and if there is only one Answer for each Question in your database tables. If don't want to combine, here is some suggestion. you can use three temp DataTables: one for question and another one for answer. and final one for combination of both. u must declare SqlDataAdapter to **Fill** the DataSet and to **Update** them back. see example in : here[^] and here[^]

DataSet dsTable = new DataSet();
adapter.Fill(dsTable); // u need to create connection string and query string to fill the data from database.
DataTable dtQuestion = dsTable["Question"]; // assign data result from question dataset
DataTable dtAnswer = dsTable["Answer"]; // assign data result from answer dataset
DataTable dtBind = dtQuestion.Clone(); // copy all data from Question
dtBind.Columns.Add("Answer",System.Type.GetType("System.Boolean")); // create new column "Answer" with Boolean type, 1 for yes and 0 for no

for(int index=0;index < dtBind.Rows.Count;index++)
{
for(int j=0; j < dtAnswer.Rows.Count; j++)
{
if(dtBind.Rows[index]["QuestionId"].Equals(dtAnswer.Rows[j]["QuestionId"]) // i assume that u used QuestionId as primary key for both Tables
{
dtBind.Rows[index]["Answer"] = dtAnswer.Rows[j]["Answer"];
break; // break from inner loop
}
}
}

and bind it to gridview from codebehind file.(.asp.cs)

GridView1.DataSource = dtBind;
GridView1.DataBind();

you need to change your girdview databound column

[snamyna]

Hye ktrrzn.. Thanks for your reply but i have come out with the solution earlier where I just retrieve the data to insert to the gridview by using SqlDataSource, but for saving the other column in the gridview into the database, I am using Sql query in .vb file instead of SqlDataSource. :) Now I got another problem where I want to display all data that has same max values. Do u have any idea where should I rewrite so that I can display all the data? I have debugging this code but there's no error. After running it, all the data came out was the same. This is the overview of my data. Career - Marks Medical - 12 Sport Science - 12 Dietetic - 12 Optometry - 12 Physiology - 12 Medical Laboratory Technology - 12 Biomedical - 12 Audiology - 12 Forensic - 12 Veterinary - 12 The result that came out after running my code is : Veterinary - 12 Veterinary - 12 Veterinary - 12 Veterinary - 12 Veterinary - 12 Veterinary - 12 Veterinary - 12 Veterinary - 12 Veterinary - 12 Veterinary - 12 So how to display all different careers with the same marks? This is my vb code. Dim connString As String = "Data Source=AINA-PC;Initial Catalog=LaluanKerjaya;Integrated Security=True" Dim con As SqlConnection = New SqlConnection(connString) Dim kp As String = CType(Session.Item("KP"), String) Dim noic As String = CType(Session.Item("NOIC"), String) KPLabel.Text = noic Dim cmdQuery As String = "SELECT count(Career) FROM Analysis WHERE Jumlah = (SELECT MAX(Marks) FROM Analysis ) AND UserID = '" + kp + "'" Dim cmd As SqlCommand = New SqlCommand(cmdQuery, con) Dim dr As SqlDataReader Dim labelCount As Integer Dim career As String con.Open() dr = cmd.ExecuteReader() While dr.Read() labelCount = dr.GetValue(0) End While dr.Close() For i As Integer = 0 To labelCount - 1 ' Create the label control and set its text attribute Dim Label1 As New Label Dim cmdQuery2 As String = "SELECT Career FROM Analysis WHERE Marks= (SELECT MAX(Marks) FROM Analysis ) AND UserID = '" + kp + "'" Dim cmd2 As SqlCommand = New SqlCommand(cmdQuery2, con) Dim dr2 As SqlDataReader = cmd2.ExecuteReader() 'con.Open() While dr2.Read() career= dr2("Career").ToString() 'Session("CAREER") = career End While

[ktrrzn]

hi snamyna, I think you are looping unnecessarily twice. Bcoz u already know the loop count of ur career that has Max marks The only for loop is sufficient, u don't need while loop as usual. I think, in ur mind, u confuse that SqlDataReader should be loop at the end of file. it is correct for common. but in ur case, u already know the loop count. Another problem is u assign the value dr2 to career (same variable) till the end of while loop without adding to placeholder, after u finish career variable will hold last value of dr2 so, u end up with last value and when u add to PlaceHolder1 the Label1.Text is last value. And then u loop again with For loop and start to query again, get the rows and while loop again loop until to last value and end up with last value, assign it to Label1.Text with exactly the same value(last row). So, as a suggestion, u juz re-arrange ur coding logic. Here:

Dim cmdQuery2 As String = "SELECT Career FROM Analysis WHERE Marks= (SELECT MAX(Marks) FROM Analysis ) AND UserID = '" + kp + "'"

Dim cmd2 As SqlCommand = New SqlCommand(cmdQuery2, con)
Dim dr2 As SqlDataReader = cmd2.ExecuteReader()

'con.Open()
For i As Integer = 0 To labelCount - 1 '//** u can also u while loop in here the result will be same
' Create the label control and set its text attribute
Dim Label1 As New Label

dr2.Read()

career= dr2("Career").ToString()
Label1.Text = career '//** u can also directly assign the value from dr2 here

Dim Literal1 As New Literal
Literal1.Text = "
"

' Add the control to the placeholder
PlaceHolder1.Controls.Add(Label1)
PlaceHolder1.Controls.Add(Literal1)

Next
dr2.Close()

If u query is right, the problem is the order of ur coding logic. Hope it works!

[snamyna]

Thanks! That works very well. sorry to be so you-know-what. Thanks again! ;)