sqldatareader and inner join problem, not getting all records, PLEASE HELP
-
i have this code, when i run this page, this reader dont bring the first record. i ran this sql(SELECT) on the sql database and runs fine, and if i put the seme SELECT in a Sqldatasource and bind the griview, it works fine too. what is wrong with this reader? ****************** any idea. THIS WORKS
SqlDataSource1.SelectCommand = "SELECT products.pid, products.pname, flavors.fl_name, product_flavors.flavor_icon FROM product_flavors LEFT JOIN flavors ON product_flavors.flavorsid = flavors.flavorsid LEFT JOIN products ON product_flavors.pid=products.pid WHERE products.pid = 84"
ProductListView.DataSource = SqlDataSource1
ProductListView.DataBind()********************** THIS NOT BRING ALL THE RECORDS. ALWAYS LEAVE ONE OUT.I THINK THE FIRST ONE.
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim cmdString As String = "SELECT products.pid, products.pname, flavors.fl_name, product_flavors.flavor_icon FROM product_flavors LEFT JOIN flavors ON product_flavors.flavorsid = flavors.flavorsid LEFT JOIN products ON product_flavors.pid=products.pid WHERE products.pid = 84"
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("eConnectionString").ToString)
cmd = New SqlCommand(cmdString, conn)
conn.Open()
Dim myReader As SqlDataReader
myReader = cmd.ExecuteReader()
myReader.Read()
GridView1.DataSource = myReader
GridView1.DataBind()
conn.Close() -
i have this code, when i run this page, this reader dont bring the first record. i ran this sql(SELECT) on the sql database and runs fine, and if i put the seme SELECT in a Sqldatasource and bind the griview, it works fine too. what is wrong with this reader? ****************** any idea. THIS WORKS
SqlDataSource1.SelectCommand = "SELECT products.pid, products.pname, flavors.fl_name, product_flavors.flavor_icon FROM product_flavors LEFT JOIN flavors ON product_flavors.flavorsid = flavors.flavorsid LEFT JOIN products ON product_flavors.pid=products.pid WHERE products.pid = 84"
ProductListView.DataSource = SqlDataSource1
ProductListView.DataBind()********************** THIS NOT BRING ALL THE RECORDS. ALWAYS LEAVE ONE OUT.I THINK THE FIRST ONE.
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim cmdString As String = "SELECT products.pid, products.pname, flavors.fl_name, product_flavors.flavor_icon FROM product_flavors LEFT JOIN flavors ON product_flavors.flavorsid = flavors.flavorsid LEFT JOIN products ON product_flavors.pid=products.pid WHERE products.pid = 84"
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("eConnectionString").ToString)
cmd = New SqlCommand(cmdString, conn)
conn.Open()
Dim myReader As SqlDataReader
myReader = cmd.ExecuteReader()
myReader.Read()
GridView1.DataSource = myReader
GridView1.DataBind()
conn.Close()orsini wrote:
myReader.Read()
I think the above line is the problem When you Use Read reader move to next record. Instead of binding the reader try to bind DataTable.
orsini wrote:
cmd = New SqlCommand(cmdString, conn) conn.Open() Dim myReader As SqlDataReader myReader = cmd.ExecuteReader() myReader.Read() GridView1.DataSource = myReader
Dim adp as new SqlDataAdpter(cmdString,conn); Dim dt as new DataTable adp.Fill(dt); GridView1.DataSource = dtEVEN THE WORD IMPOSSIBLE SAYS I M POSSIBLE.
-
i have this code, when i run this page, this reader dont bring the first record. i ran this sql(SELECT) on the sql database and runs fine, and if i put the seme SELECT in a Sqldatasource and bind the griview, it works fine too. what is wrong with this reader? ****************** any idea. THIS WORKS
SqlDataSource1.SelectCommand = "SELECT products.pid, products.pname, flavors.fl_name, product_flavors.flavor_icon FROM product_flavors LEFT JOIN flavors ON product_flavors.flavorsid = flavors.flavorsid LEFT JOIN products ON product_flavors.pid=products.pid WHERE products.pid = 84"
ProductListView.DataSource = SqlDataSource1
ProductListView.DataBind()********************** THIS NOT BRING ALL THE RECORDS. ALWAYS LEAVE ONE OUT.I THINK THE FIRST ONE.
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim cmdString As String = "SELECT products.pid, products.pname, flavors.fl_name, product_flavors.flavor_icon FROM product_flavors LEFT JOIN flavors ON product_flavors.flavorsid = flavors.flavorsid LEFT JOIN products ON product_flavors.pid=products.pid WHERE products.pid = 84"
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("eConnectionString").ToString)
cmd = New SqlCommand(cmdString, conn)
conn.Open()
Dim myReader As SqlDataReader
myReader = cmd.ExecuteReader()
myReader.Read()
GridView1.DataSource = myReader
GridView1.DataBind()
conn.Close()Try removing the line myReader.Read() before binding it to the GridView The DatReader is a forward-only cursor and you have moved it one record on by reading from it. You might want to check that ANY rows are returned first though - use If myReader.HasRows Then....
-
orsini wrote:
myReader.Read()
I think the above line is the problem When you Use Read reader move to next record. Instead of binding the reader try to bind DataTable.
orsini wrote:
cmd = New SqlCommand(cmdString, conn) conn.Open() Dim myReader As SqlDataReader myReader = cmd.ExecuteReader() myReader.Read() GridView1.DataSource = myReader
Dim adp as new SqlDataAdpter(cmdString,conn); Dim dt as new DataTable adp.Fill(dt); GridView1.DataSource = dtEVEN THE WORD IMPOSSIBLE SAYS I M POSSIBLE.
bah - your answer wasn't there when I started writing mine! :-)
-
bah - your answer wasn't there when I started writing mine! :-)
:)
EVEN THE WORD IMPOSSIBLE SAYS I M POSSIBLE.
-
Try removing the line myReader.Read() before binding it to the GridView The DatReader is a forward-only cursor and you have moved it one record on by reading from it. You might want to check that ANY rows are returned first though - use If myReader.HasRows Then....
thank you so much, both answer are great, both worked fine, thanks again. I am trying to get away from those fance interface user and get more into the code behind. so, when I bind that productlistview. with the reader, i have a funcion that return the selected value.
Public ReadOnly Property prodid() As String
Get
Return ProductListView.SelectedValue
End Get
End Propertywhich i retrieve it in the next page. how can i tell the productlistview, that the datakeynames = myrreader("pid") from the code behind. thanks for your help.