Friday, February 10, 2012

Connecting to SQL Server 2005 using vb codes.

The data resides in app_data.

In my webconfig file I have this.

<addname="vau"connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|vau.mdf"providerName="System.Data.SqlClient"/>

I understand that there are several different ways to connect and manage information to the database. I think I want to use the behind the codes (.aspx.vb) and on the top of the page I have this.Imports System.Data.

That is where I am stuck. Can you push me in the direction by either explaing to me or post a link that has more information about this.

Thanks.

1st, add a reference to System.Configuration (Assuming VS.Net 2005)

VB.Net code:

Dim cnAs New SqlConnection(ConfigurationManager.ConnectionStrings("vau").ConnectionString)Try cn.Open()Dim cmdAs New SqlCommand() cmd.CommandText ="Select * from SomeTable" cmd.Connection = cnDim rdrAs SqlDataReader = cmd.ExecuteReaderWhile rdr.Read'Do stuffEnd While Catch exAs Exception'Handle errors hereFinally If cn.State = ConnectionState.OpenThen cn.Close()End Try
|||

Whoops, you need another Imports statement:

Imports System.Data.SqlClient
|||

That worked well for select statement but I will also use insert as well as update sql statement. Can you give me examples for those as well. Thanks a ton.

|||
Dim cnAs New SqlConnection(ConfigurationManager.ConnectionStrings("vau").ConnectionString)Try cn.Open()Dim cmdAs New SqlCommand() cmd.CommandText ="Update SomeTable Set SomeField=SomeValue"'Alternatively cmd.CommandText = "Insert Into SomeTable (fields) values (values)" cmd.Connection = cnDim rowsUpdatedAs Integer = cmd.ExecuteNonQuery()Catch exAs Exception'Handle errors hereFinally If cn.State = ConnectionState.OpenThen cn.Close()End Try

No comments:

Post a Comment