I'm a bit new to VB .NET programming. I was given a project to build a web page for our Shipping department to pull up some order & invoice numbers, based on an order number parameter.
Basically, what I'm trying to do is, when the user enters an order number, I want the web page to display the invoices & invoice dates that are associated with the order number.
I've put together some code already, but not sure it is actually working. The web page comes up in Debugging mode and it acts like it is pulling data from the Oracle database, but nothing shows up in the Gridview (which I bound to the dataset... I think).
Dim cmd As New OleDbCommand("SELECT rcta.interface_header_attribute1 order_number, rcta.trx_number invoice_number, " & _
"trunc(rcta.creation_date) invoice_date " & _
"FROM ar.ra_customer_trx_all rcta " & _
"WHERE rcta.interface_header_attribute1 = '" & txtOrderNumber.Text & "'")
Dim sdaShipData As New OleDbDataAdapter(cmd)
Dim dsShipData As New sdDataSet
Being new to VB, I'm guessing my code may not be totally correct. I've tried lots of things and done tons of searching on Google for help, but nothing I've tried seems to work. At this point, I'm not even sure if the dataset is being populated correctly
or at all either. I'm a bit lost right now and could use some expert thoughts.
In case it helps, my dataset has one data table in it (dtShipData). My dataset class is sdDataSet. I added 3 columns to the data table and the gridview, manually. Not sure if should do that or not. I don't have any SQL behind the datatable itself...
do I need to?
You don't need to make a call to the ExecuteReader method of the Connection object, so that can be taken out. In addition, you close the connection before filling your DataSet, so move the call to the Close method to after you call the Fill method of the
DataAdapter.
The sdDataSet, is that a typed DataSet you created, or...?
Thanks
Carsten
Please click Mark as Answer if this post is of help to you. :-)
Protected Sub btnRetrieveData_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRetrieveData.Click
cn.ConnectionString = ConfigurationManager.ConnectionStrings("ptchomfg").ConnectionString
Dim cmd As New OleDbCommand("SELECT rcta.interface_header_attribute1 order_number, rcta.trx_number invoice_number, " & _
"trunc(rcta.creation_date) invoice_date " & _
"FROM ar.ra_customer_trx_all rcta " & _
"WHERE rcta.interface_header_attribute1 = '" & txtOrderNumber.Text & "'")
'Changed the connection here
Dim sdaShipData As New OleDbDataAdapter(cmd,cn)
Dim dsShipData As New sdDataSet
cn.Open()
sdaShipData.Fill(dsShipData)
cn.Close()
Me.dgShipData.Visible = True
'Changed the code here
dgShipData.DataSource = dsShipData.tables("dtShipData")
dgShipData.DataBind()
End Sub
You don't need to make a call to the ExecuteReader method of the Connection object, so that can be taken out. In addition, you close the connection before filling your DataSet, so move the call to the Close method to after you call the Fill method of the
DataAdapter.
The sdDataSet, is that a typed DataSet you created, or...?
No, it's a generic dataset. I didn't build any SQL code into it or tie it directly to a database, tables, columns (if I understand correctly what a typed dataset is).
No, it's a generic dataset. I didn't build any SQL code into it or tie it directly to a database, tables, columns (if I understand correctly what a typed dataset is).
Okay, I tried that, but it still won't populate the GridView. All I get is the column names in the GridView. Do I need to configure the GridView differently to show the data from the dataset? Or is there a better way to display the data from the dataset
on the web form?
Okay, which column names do you see; the ones you manually added or the actually column names referred to in the Select statement? If the former, clear the manually columns, and if the former, your Select statement doesn't seem to be returning any rows.
I think the GridView is an excellent server control for displaying data in a Web Form, although there other options.
Thanks
Carsten
Please click Mark as Answer if this post is of help to you. :-)
I deleted out my manual column names, but still nothing when I run it. I double checked the SQL using TOAD and used the same order number in the WHERE clause as I used on the web form. In TOAD, I get 4 rows. Originally I did try creating a typed dataset
and using the same SQL code (without the WHERE clause), I saw data in the dataset preview mode.
The only thing I can think of is maybe the translation of the WHERE clause and insert the textbox.text is causing a problem? It shouldn't be since I use single quotes around the value in the WHERE clause, unless it isn't pulling the textbox value correctly,
or at all.
Drrwho
Member
2 Points
39 Posts
New to VB... working with dataset and gridview to display data on web form
Jul 26, 2010 01:49 PM|LINK
I'm a bit new to VB .NET programming. I was given a project to build a web page for our Shipping department to pull up some order & invoice numbers, based on an order number parameter.
Basically, what I'm trying to do is, when the user enters an order number, I want the web page to display the invoices & invoice dates that are associated with the order number.
I've put together some code already, but not sure it is actually working. The web page comes up in Debugging mode and it acts like it is pulling data from the Oracle database, but nothing shows up in the Gridview (which I bound to the dataset... I think).
Here is the code I've written at this point:
Imports System.Data.OleDb
Namespace ShippingPerformanceReport
Public Class ShipDataUpdate
Inherits System.Web.UI.Page
Dim cn As New OleDbConnection
Protected Sub btnRetrieveData_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRetrieveData.Click
cn.ConnectionString = ConfigurationManager.ConnectionStrings("ptchomfg").ConnectionString
Dim cmd As New OleDbCommand("SELECT rcta.interface_header_attribute1 order_number, rcta.trx_number invoice_number, " & _
"trunc(rcta.creation_date) invoice_date " & _
"FROM ar.ra_customer_trx_all rcta " & _
"WHERE rcta.interface_header_attribute1 = '" & txtOrderNumber.Text & "'")
Dim sdaShipData As New OleDbDataAdapter(cmd)
Dim dsShipData As New sdDataSet
cmd.CommandType = Data.CommandType.Text
cmd.Connection = cn
cn.Open()
cmd.ExecuteReader()
cn.Close()
sdaShipData.Fill(dsShipData.dtShipData)
Me.dgShipData.Visible = True
dgShipData.DataSource = dsShipData.dtShipData
dgShipData.DataBind()
End Sub
End Class
End Namespace
Being new to VB, I'm guessing my code may not be totally correct. I've tried lots of things and done tons of searching on Google for help, but nothing I've tried seems to work. At this point, I'm not even sure if the dataset is being populated correctly or at all either. I'm a bit lost right now and could use some expert thoughts.
In case it helps, my dataset has one data table in it (dtShipData). My dataset class is sdDataSet. I added 3 columns to the data table and the gridview, manually. Not sure if should do that or not. I don't have any SQL behind the datatable itself... do I need to?
Thanks in advance for the help!
integrasol
Star
12156 Points
2355 Posts
Re: New to VB... working with dataset and gridview to display data on web form
Jul 26, 2010 03:17 PM|LINK
I notice a few things;
You don't need to make a call to the ExecuteReader method of the Connection object, so that can be taken out. In addition, you close the connection before filling your DataSet, so move the call to the Close method to after you call the Fill method of the DataAdapter.
The sdDataSet, is that a typed DataSet you created, or...?
Carsten
Please click Mark as Answer if this post is of help to you. :-)
My Blog
jgangwisch
Contributor
2449 Points
366 Posts
Re: New to VB... working with dataset and gridview to display data on web form
Jul 26, 2010 03:18 PM|LINK
change your code to the following:
Protected Sub btnRetrieveData_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRetrieveData.Click cn.ConnectionString = ConfigurationManager.ConnectionStrings("ptchomfg").ConnectionString Dim cmd As New OleDbCommand("SELECT rcta.interface_header_attribute1 order_number, rcta.trx_number invoice_number, " & _ "trunc(rcta.creation_date) invoice_date " & _ "FROM ar.ra_customer_trx_all rcta " & _ "WHERE rcta.interface_header_attribute1 = '" & txtOrderNumber.Text & "'") 'Changed the connection here Dim sdaShipData As New OleDbDataAdapter(cmd,cn) Dim dsShipData As New sdDataSet cn.Open() sdaShipData.Fill(dsShipData) cn.Close() Me.dgShipData.Visible = True 'Changed the code here dgShipData.DataSource = dsShipData.tables("dtShipData") dgShipData.DataBind() End SubDrrwho
Member
2 Points
39 Posts
Re: New to VB... working with dataset and gridview to display data on web form
Jul 26, 2010 03:24 PM|LINK
No, it's a generic dataset. I didn't build any SQL code into it or tie it directly to a database, tables, columns (if I understand correctly what a typed dataset is).
Drrwho
Member
2 Points
39 Posts
Re: New to VB... working with dataset and gridview to display data on web form
Jul 26, 2010 03:26 PM|LINK
jgangwisch,
I used the code you provided and am getting an error for line 11 (Dim sdaShipData As New OleDbDataAdapter(cmd, cn)) saying:
"Overload resolution failed because no accessible 'New' can be called with these arguments"
I have Visual Studio 2005, if that has anything to do with it.
integrasol
Star
12156 Points
2355 Posts
Re: New to VB... working with dataset and gridview to display data on web form
Jul 26, 2010 03:36 PM|LINK
Replace the cmd variable in the call to the constructor with the string for creating the Command object for the Select statement, as follows:
Dim sdaShipData As New OleDbDataAdapter("SELECT rcta.interface_header_attribute1 order_number, rcta.trx_number invoice_number, " & _
"trunc(rcta.creation_date) invoice_date " & _
"FROM ar.ra_customer_trx_all rcta " & _
"WHERE rcta.interface_header_attribute1 = '" & txtOrderNumber.Text & "'", cn)
Carsten
Please click Mark as Answer if this post is of help to you. :-)
My Blog
integrasol
Star
12156 Points
2355 Posts
Re: New to VB... working with dataset and gridview to display data on web form
Jul 26, 2010 03:39 PM|LINK
How did you create the DataSet, by adding a new item to your project, and choosing the DataSet template? If so, it is a typed DataSet. :-) http://msdn.microsoft.com/en-us/library/esbykkzb(VS.80).aspx
Carsten
Please click Mark as Answer if this post is of help to you. :-)
My Blog
Drrwho
Member
2 Points
39 Posts
Re: New to VB... working with dataset and gridview to display data on web form
Jul 26, 2010 03:42 PM|LINK
Okay, I tried that, but it still won't populate the GridView. All I get is the column names in the GridView. Do I need to configure the GridView differently to show the data from the dataset? Or is there a better way to display the data from the dataset on the web form?
integrasol
Star
12156 Points
2355 Posts
Re: New to VB... working with dataset and gridview to display data on web form
Jul 26, 2010 03:47 PM|LINK
Okay, which column names do you see; the ones you manually added or the actually column names referred to in the Select statement? If the former, clear the manually columns, and if the former, your Select statement doesn't seem to be returning any rows.
I think the GridView is an excellent server control for displaying data in a Web Form, although there other options.
Carsten
Please click Mark as Answer if this post is of help to you. :-)
My Blog
Drrwho
Member
2 Points
39 Posts
Re: New to VB... working with dataset and gridview to display data on web form
Jul 26, 2010 03:54 PM|LINK
I deleted out my manual column names, but still nothing when I run it. I double checked the SQL using TOAD and used the same order number in the WHERE clause as I used on the web form. In TOAD, I get 4 rows. Originally I did try creating a typed dataset and using the same SQL code (without the WHERE clause), I saw data in the dataset preview mode.
The only thing I can think of is maybe the translation of the WHERE clause and insert the textbox.text is causing a problem? It shouldn't be since I use single quotes around the value in the WHERE clause, unless it isn't pulling the textbox value correctly, or at all.