xfungalx
asked on
How can I display one record from my datasource in a textbox
Hello experts
I would like to display one row from my datasource (which has only one item due to a specific query) in a textbox. Is this possible? I am using an Access database, I am able to display records in gridview's etc. But what I really want to do is to display one item in a textbox when a user clicks a button. I am not too terribly interested in using a form view (although that does work), unless someone knows how to programatically determine the text inside of that form view after it is filled.
I am obviously just starting to get my feet wet with Visual Web Developer and ASP.net so please forgive my question if it is poorly asked.
I appreciate any input.
I would like to display one row from my datasource (which has only one item due to a specific query) in a textbox. Is this possible? I am using an Access database, I am able to display records in gridview's etc. But what I really want to do is to display one item in a textbox when a user clicks a button. I am not too terribly interested in using a form view (although that does work), unless someone knows how to programatically determine the text inside of that form view after it is filled.
I am obviously just starting to get my feet wet with Visual Web Developer and ASP.net so please forgive my question if it is poorly asked.
I appreciate any input.
Using a OldDbDataAdapter object if db is Access DB:
Dim ds new DataSet()
Dim myConnection As SqlConnection = New SqlConnection("server=(loc al)\VSdotN ET;Trusted _Connectio n=yes;data base=north wind")
Dim mySqlDataAdapter As SqlDataAdapter = New SqlDataAdapter("select * from customers WHERE CUSTOMER_ID=1", myConnection)
sqlDataAdapter.Fill(ds)
Response.Write (ds.Tables(0).Rows(0)("COL UMN_NAME")
More help on that is available at ASP.NET QuickStart tutorial:
ADO.NET Overview
http://samples.gotdotnet.com/quickstart/aspplus/doc/adoplusoverview.aspx
--Nauman.
Dim ds new DataSet()
Dim myConnection As SqlConnection = New SqlConnection("server=(loc
Dim mySqlDataAdapter As SqlDataAdapter = New SqlDataAdapter("select * from customers WHERE CUSTOMER_ID=1", myConnection)
sqlDataAdapter.Fill(ds)
Response.Write (ds.Tables(0).Rows(0)("COL
More help on that is available at ASP.NET QuickStart tutorial:
ADO.NET Overview
http://samples.gotdotnet.com/quickstart/aspplus/doc/adoplusoverview.aspx
--Nauman.
ASKER
Here is what I currently have in my vb code behind page.
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim ds As New Data.DataSet
Dim myConnection As Data.SqlClient.SqlConnecti on = New Data.SqlClient.SqlConnecti on("server =(local)\V SdotNET;Tr usted_Conn ection=yes ;database= mydatabase .mdb")
Dim mySqlDataAdapter As Data.SqlClient.SqlDataAdap ter = New Data.SqlClient.SqlDataAdap ter("SELEC T Users.Password FROM Users WHERE (((Users.Password)=""r3dst 4ff""));", myConnection)
mySqlDataAdapter.Fill(ds)
ShowDataBox.Text = (ds.Tables(0).Rows(0)("Use rname"))
End Sub
I am getting this error :
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
I am almost certain that the error is originating from the connection string, particularly here: database=mydatabase.mdb"
Any ideas? My database is in the App_Data folder.
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim ds As New Data.DataSet
Dim myConnection As Data.SqlClient.SqlConnecti
Dim mySqlDataAdapter As Data.SqlClient.SqlDataAdap
mySqlDataAdapter.Fill(ds)
ShowDataBox.Text = (ds.Tables(0).Rows(0)("Use
End Sub
I am getting this error :
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
I am almost certain that the error is originating from the connection string, particularly here: database=mydatabase.mdb"
Any ideas? My database is in the App_Data folder.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok Ahmed,
It all works assuming I provide the absolute path to my database (located on my C: drive, all the way up to my VS folder etc.). However, I don't know exactly what that path will be when I upload the site to the server. I imagine I could troubleshoot through trial and error at that point but a relative path would be nice.
You have answered the bulk of my question beautifully thus far.
Another quick question regarding using SQL server instead. If I set up a SQL data connection within VWD will it work on the server with zero SQL configuration (server side) on my part? I (like many people nowadays) pay monthly for a webserver (1and1.com). If so, I will probably just go that route.
Also, regarding skvikram's suggestion, he wrote the code to appear directly on the aspx page, as opposed to doing it on the VB code behind form (i.e. <Script runat="Server"> and </script> tags). What is the difference? As I am far more familiar (and that aint saying much) and more comfortable writing straight VB.NET code to VB.NET forms, therefore I prefer to use the code behind (aspx.vb) pages.
I will wait for your reply, and then award points as your solution works just as I would like, sans the absolute/relative path.
It all works assuming I provide the absolute path to my database (located on my C: drive, all the way up to my VS folder etc.). However, I don't know exactly what that path will be when I upload the site to the server. I imagine I could troubleshoot through trial and error at that point but a relative path would be nice.
You have answered the bulk of my question beautifully thus far.
Another quick question regarding using SQL server instead. If I set up a SQL data connection within VWD will it work on the server with zero SQL configuration (server side) on my part? I (like many people nowadays) pay monthly for a webserver (1and1.com). If so, I will probably just go that route.
Also, regarding skvikram's suggestion, he wrote the code to appear directly on the aspx page, as opposed to doing it on the VB code behind form (i.e. <Script runat="Server"> and </script> tags). What is the difference? As I am far more familiar (and that aint saying much) and more comfortable writing straight VB.NET code to VB.NET forms, therefore I prefer to use the code behind (aspx.vb) pages.
I will wait for your reply, and then award points as your solution works just as I would like, sans the absolute/relative path.
1. The best approach here is to give the db path in web.config in appSettings block:
<configuration>
<appSettings>
<add key="DB_PATH" value="DB_PATH">
</appSettings>
<system.web>
</sytem.web>
</configuration>
In your ASPX.VB page you can then reference it like:
Dim strConnectionStrng As String
strConnectionString = System.Configuration.Confi gurationSe ttings.App Settings(" DB_PATH")
When you upload the project to production, you just have to change the db path in web.config and everything will work smooth. For your convenience I recommend you have the following structure in web.config file so that you do not have to change anything after configuring the application first time:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="CURRENT_CONFIG" value="LOCAL"></add>
<add key="SQL_SERVER_CONNECTION _STRING_LO CAL" value="packet size=4096;user id=sa;data source=localhost;persist security info=True;initial catalog=PUBS;password=PASS WORD"></ad d>
<add key="SQL_SERVER_CONNECTION _STRING_PR ODUCTION" value="packet size=4096;user id=sa;data source=192.168.0.1;persist security info=True;initial catalog=PUBS;password=pass word"></ad d>
</appSettings>
</configuration>
In your code you can then look at the value of CURRENT_CONFIG, if it is LOCAL then use SQL_SERVER_CONNECTION_STRI NG_LOCAL, otherwise use SQL_SERVER_CONNECTION_STRI NG_PRODUCT ION. You are using Microsoft Access so your connection string will be different. You should also consider a data layer that will make your life easy and you do not have to declare the DB objects again and again. Url for that is
Writing Data Access Layer
https://www.experts-exchange.com/questions/20993347/Writing-Data-Access-Layer.html?query=Nauman+DatabaseManagement&clearTAFilter=true
To convert this code to VB.NET you can use code translator at http://carlosag.net/Tools/CodeTranslator/Default.aspx
2. If you do the configuration as I suggested, then your application will work with very minimal changes whenever you port application to the server.
3. Vikram has used an inline coding style. If you use VS.NET and code-behind than you do not have to use runat=server attribute in ASPX pages.
4. I also recommend the following structure for your web projects to make sure that your MS ACcess DB files are safe. Assuming you have a control on the server and the server root directory is located in D:\
D:\www.yourwebsite.com
\Db
\www
\temp
HTH, Nauman
<configuration>
<appSettings>
<add key="DB_PATH" value="DB_PATH">
</appSettings>
<system.web>
</sytem.web>
</configuration>
In your ASPX.VB page you can then reference it like:
Dim strConnectionStrng As String
strConnectionString = System.Configuration.Confi
When you upload the project to production, you just have to change the db path in web.config and everything will work smooth. For your convenience I recommend you have the following structure in web.config file so that you do not have to change anything after configuring the application first time:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="CURRENT_CONFIG" value="LOCAL"></add>
<add key="SQL_SERVER_CONNECTION
<add key="SQL_SERVER_CONNECTION
</appSettings>
</configuration>
In your code you can then look at the value of CURRENT_CONFIG, if it is LOCAL then use SQL_SERVER_CONNECTION_STRI
Writing Data Access Layer
https://www.experts-exchange.com/questions/20993347/Writing-Data-Access-Layer.html?query=Nauman+DatabaseManagement&clearTAFilter=true
To convert this code to VB.NET you can use code translator at http://carlosag.net/Tools/CodeTranslator/Default.aspx
2. If you do the configuration as I suggested, then your application will work with very minimal changes whenever you port application to the server.
3. Vikram has used an inline coding style. If you use VS.NET and code-behind than you do not have to use runat=server attribute in ASPX pages.
4. I also recommend the following structure for your web projects to make sure that your MS ACcess DB files are safe. Assuming you have a control on the server and the server root directory is located in D:\
D:\www.yourwebsite.com
\Db
\www
\temp
HTH, Nauman
ASKER
Top notch help, thanks a lot.
Sub btnButton_Click( s As Object, e As EventArgs )
Dim strRow As String
strRow= '''PLACE YOUR CODE HERE TO GET SELECTED ROWS e.g. select * from [table] where [condition]
txtTextBox.text= strRow
End Sub
</Script>