从中检索数据 [英] retrieving data from
本文介绍了从中检索数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
亲爱的朋友，
我有以下代码，它们将以之字形方式将数据检索到每一行.现在，它实际上仅在每行中显示数据库中的最后一条记录.
Dear friends,
I have the following code which will retrieve the data to each rows in zigzag manner. Right now it is actually displaying only the last record from the database in each row.
Dim intRows As Integer = TextBox2.Text
Dim intColumns As Integer = TextBox3.Text
Dim intValue As Integer = 0
Dim intChkValue As Integer = 0
DataGridView2.RowCount = intRows
DataGridView2.ColumnCount = intColumns
Dim intTotalStrength As Integer = 10
Dim blnOdd As [Boolean], blnEven As [Boolean]
Dim DTResult As New DataTable()
For i As Integer = 0 To intColumns - 1
'dataGridView1.Columns.Add = i;
DTResult.Columns.Add("Column : " & i)
Next
For row As Integer = 0 To intRows - 1
Dim drNew As DataRow = DTResult.NewRow()
For col As Integer = 0 To intColumns - 1
If col Mod 2 = 0 Then
blnOdd = True
blnEven = False
Else
blnEven = True
blnOdd = False
End If
If row = 0 Then
If col = 0 Then
drNew(col) = row + 1
intValue = row + 1
'mycode
Dim s1 As String
Dim sk As String
Dim ik As Integer
s1 = "SELECT distinct TOP " + TextBox2.Text + " nominal.regno FROM course INNER JOIN nominal ON course.courseno = nominal.courseno INNER JOIN timetable2 ON course.coursename = timetable2.course where timetable2.dateofexam='" & DateTimePicker1.Value.Date & "' and timetable2.session='" & ComboBox2.Text & "' order by nominal.regno asc "
Dim cmd1 As SqlCommand
conn1 = New SqlConnection(Common.GetConnectionString)
cmd1 = New SqlCommand(s1, conn1)
Dim dr1 As SqlDataReader
If conn1.State = ConnectionState.Closed Then
conn1.Open()
Else
conn1.Close()
End If
dr1 = cmd1.ExecuteReader()
While dr1.Read()
DataGridView2.Rows(row).Cells(col).Value = dr1.GetValue(0)
End While
cmd1.Dispose()
'mycode
Else
If blnEven Then
intValue += intRows * 2 - 1
Else
intValue += 1
End If
drNew(col) = intValue
'mycode
Dim s1 As String
Dim sk As String
Dim ik As Integer
s1 = "SELECT distinct TOP " + TextBox2.Text + " nominal.regno FROM course INNER JOIN nominal ON course.courseno = nominal.courseno INNER JOIN timetable2 ON course.coursename = timetable2.course where timetable2.dateofexam='" & DateTimePicker1.Value.Date & "' and timetable2.session='" & ComboBox2.Text & "' order by nominal.regno desc "
Dim cmd1 As SqlCommand
conn1 = New SqlConnection(Common.GetConnectionString)
cmd1 = New SqlCommand(s1, conn1)
Dim dr1 As SqlDataReader
If conn1.State = ConnectionState.Closed Then
conn1.Open()
Else
conn1.Close()
End If
dr1 = cmd1.ExecuteReader()
While dr1.Read()
DataGridView2.Rows(row).Cells(col).Value = dr1.GetValue(0)
End While
cmd1.Dispose()
'mycode
End If
Else
If blnOdd Then
intChkValue = Convert.ToInt32((DTResult.Rows(row - 1)(col).ToString())) + 1
'mycode
Dim s1 As String
Dim sk As String
Dim ik As Integer
s1 = "SELECT distinct TOP " + TextBox2.Text + " nominal.regno FROM course INNER JOIN nominal ON course.courseno = nominal.courseno INNER JOIN timetable2 ON course.coursename = timetable2.course where timetable2.dateofexam='" & DateTimePicker1.Value.Date & "' and timetable2.session='" & ComboBox2.Text & "' order by nominal.regno asc"
Dim cmd1 As SqlCommand
conn1 = New SqlConnection(Common.GetConnectionString)
cmd1 = New SqlCommand(s1, conn1)
Dim dr1 As SqlDataReader
If conn1.State = ConnectionState.Closed Then
conn1.Open()
Else
conn1.Close()
End If
dr1 = cmd1.ExecuteReader()
While dr1.Read()
DataGridView2.Rows(row).Cells(col).Value = dr1.GetValue(0)
End While
cmd1.Dispose()
'mycode
Else
intChkValue = Convert.ToInt32((DTResult.Rows(row - 1)(col).ToString())) - 1
' DataGridView2.Rows(row).Cells(col).Value = intChkValue
'mycode
Dim s1 As String
Dim sk As String
Dim ik As Integer
s1 = "SELECT distinct TOP " + TextBox2.Text + " nominal.regno FROM course INNER JOIN nominal ON course.courseno = nominal.courseno INNER JOIN timetable2 ON course.coursename = timetable2.course where timetable2.dateofexam='" & DateTimePicker1.Value.Date & "' and timetable2.session='" & ComboBox2.Text & "' order by nominal.regno desc "
Dim cmd1 As SqlCommand
conn1 = New SqlConnection(Common.GetConnectionString)
cmd1 = New SqlCommand(s1, conn1)
Dim dr1 As SqlDataReader
If conn1.State = ConnectionState.Closed Then
conn1.Open()
Else
conn1.Close()
End If
dr1 = cmd1.ExecuteReader()
While dr1.Read()
DataGridView2.Rows(row).Cells(col).Value = dr1.GetValue(0)
End While
cmd1.Dispose()
'mycode
End If
drNew(col) = intChkValue
End If
Next
DTResult.Rows.Add(drNew)
Next
For row As Integer = 0 To intRows - 1
For col As Integer = 0 To intColumns - 1
If DTResult.Rows(row)(col).ToString() <> "" AndAlso Convert.ToInt32(DTResult.Rows(row)(col).ToString()) > intTotalStrength Then
DTResult.Rows(row)(col) = ""
End If
Next
Next
您能帮我显示所有记录吗?
Can you please help me to show all the records?
推荐答案
您没有所有记录的原因有两个:
1)因为顶部 [选择 [
There are 2 reasons why you do not have all records:
1) becouse of TOP[^] instruction,
2) becouse of DISTINCT instruction.
You need to understand SELECT[^] instruction.
Let say, we have tableCarswith 10 records andModels, where you have all models for each car.
To get all non-duplicate cars inModelstable:
SELECT DISTINCT M.CarID, C.Name
FROM Models AS M LEFT JOIN Cars AS C ON M.CarId = C.ID
ORDER BY M.CarID
要在Models表中获取所有有重复的汽车，您需要删除DISTINCT指令.
要获取Models表中的前10条记录:
To get all cars (with duplicates) in Models table you need to remove DISTINCT instruction.
To get first 10 records in Models table:
SELECT TOP(10) C.ID, C.Name
FROM Models AS M LEFT JOIN Cars AS C ON M.CarId = C.ID
ORDER BY M.CarID
要获取模型"表中的所有记录，您需要删除TOP(10)指令.
To get all records in Models table, you need to remove TOP(10) instruction.
这篇关于从中检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
