Archive

Archive for the ‘Database’ Category

DataTable.DefaultView Property – Gets a customized View of the Table that may include a Filtered View, or a Cursor position.

Gets a customized view of the table that may include a filtered view, or a cursor position.
The DefaultView property returns a DataView you can use to sort, filter, and search a DataTable.

The following example sets a property of the DataTable object’s DataView through the DefaultView property. The example also shows the binding of a DataGridView control to a DataTable named “Suppliers” that includes a column named “CompanyName.”

Private Sub BindDataGrid()
   Dim table As New DataTable

   ' Insert code to populate a DataTable with data.

   ' Bind DataGrid to DataTable
   DataGrid1.DataSource = table
End Sub 

Private Sub ChangeRowFilter()
   Dim gridTable As DataTable = _
       CType(dataGrid1.DataSource, DataTable)

   ' Set the RowFilter to display a company names 
   ' that begin with A through I.
   gridTable.DefaultView.RowFilter = "CompanyName < 'I'"
End Sub



Categories: Database, VB.NET Tags: ,

SQL Connection String using SqlConnectionStringBuilder Class

November 7, 2010 Leave a comment

Dim strConnection as String
Dim SQLDataName as string
Dim SQLServerName as string

' read connection string from app.config AppSettings

strConnection = Configuration.ConfigurationManager.AppSettings("dbConnection")
 Dim sqlBuilder As New SqlConnectionStringBuilder

' assign Connection string to SqlConnectionStringBuilder Classes ConnectionString Property
sqlBuilder.ConnectionString = tempstr

' now you have access to all of the elements that make up the Connection string

SQLDatabaseName = sqlBuilder.InitialCatalog
SQLServerName = sqlBuilder.DataSource

Categories: Database Tags:

Creating DataTable in Memory

October 16, 2010 Leave a comment

Dim dtDataTable As DataTable = Nothing
Dim dtRow As DataRow

dtDataTable.Columns.Add("LastName", GetType(String))
dtDataTable.Columns.Add("FirstName", GetType(String))
dtDataTable.Columns.Add("Age", GetType(Integer))

dtRow= dtDataTable.NewRow()
dtRow.Item("LastName") = "Smith"
dtRow.Item("FirstName") = "John"
dtRow.Item("Age") = 25
dtDataTable.Rows.Add(dtRow)

Categories: Database Tags:

Calling a Stored Procedure using ADO in a VBScript

October 11, 2010 Leave a comment

This information comes fromĀ  Microsoft Knowledge Base Article 164485.

This article demonstrates three methods of calling a stored procedure from Active Server Pages using ActiveX Data Objects (ADO).

The following example uses the Command object to call a sample stored procedure sp_test. This stored procedure accepts an integer and has a return value of an integer as well:

This first method queries the data source about the parametersĀ 
of the stored procedure. This is the least efficient method of callingĀ 
a stored procedure.
Set cn = Server.CreateObject("ADODB.Connection")
   Set cmd = Server.CreateObject("ADODB.Command")
   cn.Open "data source name", "userid", "password"
   Set cmd.ActiveConnection = cn
   cmd.CommandText = "sp_test"
   cmd.CommandType = adCmdStoredProc
   ' Ask the server about the parameters for the stored proc
   cmd.Parameters.Refresh
   ' Assign a value to the 2nd parameter.
   ' Index of 0 represents first parameter.
   cmd.Parameters(1) = 11
   cmd.Execute</pre>
Method 2 declares the stored procedure, and then explicitly
declares the parameters.
<pre>Set cn = Server.CreateObject("ADODB.Connection")
   cn.Open "data source name", "userid", "password"
   Set cmd = Server.CreateObject("ADODB.Command")
   Set cmd.ActiveConnection = cn
   cmd.CommandText = "sp_test"
   cmd.CommandType = adCmdStoredProc
   cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, _
      adParamReturnValue)
   cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, _
      adParamInput)
   ' Set value of Param1 of the default collection to 22
   cmd("Param1") = 22
   cmd.Execute

Method 3 is probably the most formal way of calling a stored procedure. It uses the canocial.

Set cn = Server.CreateObject("ADODB.Connection")
   cn.Open "data source name", "userid", "password"
   Set cmd = Server.CreateObject("ADODB.Command")
   Set cmd.ActiveConnection = cn
   ' Define the stored procedure's inputs and outputs
   ' Question marks act as placeholders for each parameter for the
   ' stored procedure
   cmd.CommandText = "{?=call sp_test(?)}"
   ' specify parameter info 1 by 1 in the order of the question marks
   ' specified when we defined the stored procedure
   cmd.Parameters.Append cmd.CreateParameter("RetVal", adInteger, _
   adParamReturnValue)
   cmd.Parameters.Append cmd.CreateParameter("Param1", adInteger, _
     adParamInput)
   cmd.Parameters("Param1") = 33
   cmd.Execute

Note in the above examples, various methods of accessing the Parameters
collection of the Command object are used. Some use the default collection of
the Command object while others specify what property of a given collection to access.

Categories: Database Tags: ,