Simple database wth Access

A

AFterlife

Regular
Joined
Dec 21, 2003
Messages
73
Could someone give me the basic way to extract data to say a textbox and a datagrid in ASP. Ive done it in windows with connection, adapter and a dataset. Is it the same type of strategy? Or is there a whole different process. I tried one like you do in windows and the datagrid and textbox just disappeared.
 
There are two methodologies; both you will need the connection and data adapter for.

From there you can put it in a table or a dataset; that's up to you. You could also use a DataReader if your really wanted to, but most people don't.

From here is where things branch off. Some people like to DataBind the text box to the table or dataset. Some people like to put in the data manually like so:

this.Text1.Text = MyTable.Rows[0]["SomeColumn"];

It all depends on what your trying to do. Using DataBinding is the most common way and the 'preferred' method among programmers, but there are instances where you might not want to do it that way.
 
I would wrap the whole enchilada in a try catch block and send the ex.Message to a textbox on your form. You can't use Console.WriteLine in asp apps so you have to send the results to the form.

Try
.......

Catch ex as Exception
me.txtError.Text = ex.message
end try
 
OK. How come when i do this. With a button and a textbox using a connection,data adapter, dataset with this in the button and this was just to extract a field into a textbox.
Visual Basic: 
  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        da.Fill(DataSet11)
        TextBox1.Text = Convert.ToString(DataSet11.tblName.Rows(0)("fn"))

    End Sub

I get this:
Server Error in '/data' Application.
--------------------------------------------------------------------------------

The Microsoft Jet database engine cannot open the file 'C:\Inetpub\wwwroot\data\db1.mdb'. It is already opened exclusively by another user, or you need permission to view its data.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: The Microsoft Jet database engine cannot open the file 'C:\Inetpub\wwwroot\data\db1.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

Source Error:


Line 117:
Line 118: Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Line 119: da.Fill(DataSet11)
Line 120: TextBox1.Text = Convert.ToString(DataSet11.tblName.Rows(0)("fn"))
Line 121:


Source File: c:\inetpub\wwwroot\data\WebForm1.aspx.vb Line: 119

Stack Trace:


[OleDbException (0x80004005): The Microsoft Jet database engine cannot open the file 'C:\Inetpub\wwwroot\data\db1.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
System.Data.OleDb.OleDbConnection.InitializeProvider()
System.Data.OleDb.OleDbConnection.Open()
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
data.WebForm1.Button1_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\data\WebForm1.aspx.vb:119
System.Web.UI.WebControls.Button.OnClick(EventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.5
 
With TextBox... you only have to databind the right property... (Text in this case).
With a DataGrid... you'll have to set a DataSource and a DataMember. you're not forced to put a DataMember if DataSource is a table (and if you want readable result).

In both case however (and only in ASP.NET)... you'll have to DataBind your control before it render.

Eg. :
TextBox1.DataBind()
DataGrid1.DataBind()
 
This is how I get it done.

This is a nonquery meaning you don't expect anything back, because it's an insert

Code: 
dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = C:\db1.mdb" 
Dim objConnAcc As New OleDbConnection(strConn)
Dim strSQL As String = "INSERT INTO X (a, b, c) VALUES ('" & a & "', '" & b & "', " & c & “)"
Dim objCmd As New OleDbCommand(strSQL, objConn)
       	objCmd.CommandType = CommandType.Text
            	objCmd.CommandText = strSQL
Try
            objConnAcc.ConnectionString = strConn
            objConnAcc.Open()
            objCmd.ExecuteNonQuery()
        Catch ex As System.Exception
            Console.WriteLine("Error " & ex.Message)
        Finally
            objConnAcc.Close()
        End Try


But you want something back so you must have a select. This one uses a datareader because you expect more than one record back. If you thought you were only going to get a single record you could use ExecuteScalar.

Code: 
Dim strSQL As String = "SELECT x, y FROM MyTable"
Dim cmdOra As New OracleCommand(strSQL, objORAConn)
cmdOra.CommandType = CommandType.Text
cmdOra.CommandText = strSQL
Dim dr As OracleDataReader
        Try
            objConnORA.Open()
            dr = cmdOra.ExecuteReader()
            Do While dr.Read
                MyX = CType(dr.GetValue(0), String)
                MyY = CType(dr.GetValue(1), String)
'Set your text box here
Me.TextBox1.Text = MyX.ToString
            Loop
	dr.Close
        Catch ex As Exception
            Console.WriteLine("***** : {0}", ex.Message)
            Return False
        Finally
            objConnORA.Close()
        End Try
 
Back
Top