HetEmumejem
New Member
Hi All,<BR><BR>I was using ASP but now I want to use ASP.NET I am little bit confused in DATABASE connectibity. I got code from msdn but it is not working I just want this is for ASP.NET or VB.NET please help me. This is example for SQL Server i want to user access database and the DSN=test. can any one help me.<BR><BR>-------Code-------<BR><%@Import Namespace="System.Data"%><BR><%@Import Namespace="System.Data.OleDb"%><BR><! <BR>This example uses ADO.NET to read records from a database and print two<BR>fields from all returned records to an active server page. The database<BR>is located on the local server.<BR>><BR><html><BR><Script Language=VB Runat=Server><BR> Sub Page_Load(Sender As Object, e As EventArgs)<BR> Dim dtConn As OleDbConnection<BR> Dim dtCmd As OleDbDataAdapter<BR> dim dtSet As DataSet<BR> dim dtTable As DataTable<BR> dim iLoop, iNumRows As Integer<BR> dim sSQL As String<BR> <BR> sSQL = "SELECT * FROM Employees;"<BR> <BR> ' Create a connection to the data source. <BR> dtConn = New OleDbConnection("Provider=SQLOLEDB;server=localhost;" _<BR> & "Integrated Security=SSPI;Initial Catalog=Northwind")<BR><BR> ' Create a Command object with the SQL statement.<BR> dtCmd = New OleDbDataAdapter(sSql, dtConn)<BR><BR> ' Fill a DataSet with data returned from the database.<BR> dtSet = New DataSet<BR> dtCmd.Fill(dtSet)<BR> <BR> ' Create a new DataTable object and assign to it<BR> ' the new table in the Tables collection.<BR> dtTable = New DataTable<BR> dtTable = dtSet.Tables(0)<BR> ' Find how many rows are in the Rows Collection <BR> ' of the new DataTable object.<BR> iNumRows = dtTable.Rows.Count<BR> If iNumRows = 0 then<BR> Response.Write("<p>No records.</p>")<BR> Else<BR> Response.Write("<p>" & Cstr(iNumRows) & " records found.</p>")<BR> For iLoop = 0 To iNumRows - 1<BR> ' Print the values of the two columns in the Columns<BR> ' Collection for each row.<BR> Response.Write(dtTable.Rows(iLoop).Item("FirstName") _<BR> & " " & dtTable.Rows(iLoop).Item("LastName") & "<BR>")<BR> Next iLoop<BR> End If<BR> Response.Write("<p>End of data.</p>") <BR> End Sub<BR></Script><BR></html><BR>-------End--------When you say "it isn't working", could you be more specific? What error is it generating?<BR><BR>Also, since you're connecting to a SQL database, it's preferable to use the SqlDataAdapter rather than the OleDb one. Don't forget to import System.Data.SQLClient also.I think it is the access jet connection string<BR><BR>Imports System<BR>Imports System.Data<BR>Imports System.Data.SqlClient ' For MS SQL Only<BR>Imports System.Data.OleDB ' Others<BR><BR>Public Class TESTCONN<BR> Inherits System.Web.UI.Page<BR> Protected WithEvents Label1 As System.Web.UI.WebControls.Label<BR> Protected WithEvents ERRMSG As System.Web.UI.WebControls.Label<BR> Protected WithEvents DBCONNLIST As System.Web.UI.WebControls.DropDownList<BR> Protected WithEvents Button1 As System.Web.UI.WebControls.Button<BR> Protected WithEvents ACTIONMSG As System.Web.UI.WebControls.Label<BR> Protected WithEvents DG1 As System.Web.UI.WebControls.DataGrid<BR> 'Protected WithEvents TB1 As System.Web.UI.WebControls.TextBox<BR> <BR>#Region " Web Form Designer Generated Code "<BR><BR> 'This call is required by the Web Form Designer.<BR> <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()<BR><BR> End Sub<BR><BR> Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init<BR> 'CODEGEN: This method call is required by the Web Form Designer<BR> 'Do not modify it using the code editor.<BR> InitializeComponent()<BR> End Sub<BR><BR>#End Region<BR><BR> Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load<BR> '-----------------------------------------------------------------------<BR> ' Info fromDreamweaver UltraDev 4: Then Complete Reference page 348<BR> ' <BR> 'Put user code to initialize the page here<BR> Dim myCONNS As New SqlConnection()<BR> Dim myCOMDS As New SqlDataAdapter()<BR> Dim myCONN As New OleDbConnection()<BR> Dim myCOMD As New OleDbDataAdapter()<BR> Dim ds As New DataSet()<BR><BR> Dim svSORTFLD As String = "ACCODE"<BR> Dim mvSQL As String<BR> mvSQL = "SELECT * FROM CUSTMST"<BR><BR> Dim mvCONNTYPE As String<BR> mvCONNTYPE = DBCONNLIST.SelectedItem.Value.ToString<BR> ACTIONMSG.Text = "Selected No. = " & mvCONNTYPE<BR><BR> Select Case (mvCONNTYPE)<BR> Case "1"<BR> ' myCONNS = New SqlConnection("server=DBS-P4;database=testMSSQL;Trusted_Connection=yes")<BR> myCONNS = New SqlConnection("server=DBS-P4;uid=sa;pwd=test;database=testMSSQL")<BR> myCOMDS = New SqlDataAdapter(mvSQL, myCONNS)<BR><BR> Case "2"<BR> ' myCONN = New OleDbConnection("PROVIDER=SQLOLEDB;Data Source=DBS-P4;Initial Catalog=testMSSQL;Trusted_Connection=yes")<BR> myCONN = New OleDbConnection("PROVIDER=SQLOLEDB;Network Library=DBMSSOCN;Data Source=DBS-P4;Initial Catalog=testMSSQL;User ID=sa;Password=test")<BR> myCOMD = New OleDbDataAdapter(mvSQL, myCONN)<BR><BR> Case "3"<BR> Dim mvPATH As String ' For Access Jet 4.o Database<BR> mvPATH = Server.MapPath("/webdbx/testJET.mdb")<BR> myCONN = New OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & mvPATH & ";User ID=;Password=;")<BR> ' myCONN = New OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;Data Source=c:Inetpubwwwrootwebdbx estJET.mdb;User ID=;Password=;")<BR> myCOMD = New OleDbDataAdapter(mvSQL, myCONN)<BR><BR> Case Else<BR> ERRMSG.Text = "Please select a database connection"<BR> Exit Sub<BR> End Select<BR><BR> If mvCONNTYPE = "1" Then<BR> myCONNS.Open() ' USE MS SQL 2000<BR> myCOMDS.Fill(ds, "FILE1")<BR> Else ' Other OLE DB<BR> myCONN.Open()<BR> myCOMD.Fill(ds, "FILE1")<BR> End If<BR><BR> If svSORTFLD > "" Then<BR> ds.Tables("FILE1").DefaultView.Sort = svSORTFLD '* Sort Expression<BR> End If<BR> DG1.DataSource = ds.Tables("FILE1").DefaultView<BR> DG1.DataBind()<BR> End Sub<BR><BR>End Class