OleDbConnection string with ConfigurationManager

November 7, 2012 - Posted in .NET 4.0, Applications, ASP.NET, MS SQL - Permalink
Web config

<add 
   name="MyConnectionString" 
   connectionString="Data Source=SRV-MY-SQL\SQLCLSTR12;
   Initial Catalog=MyDatabaseName;
   Persist Security Info=True;User ID=MyUser;Password=MyPassword"
   providerName="System.Data.SqlClient" />

Code behind or markup page

'defines sql connection
Dim connString As String
connString = ConfigurationManager.ConnectionStrings("MyConnectionString").ToString
Dim ConnDB As New OleDbConnection("Provider=sqloledb;" & connString)
'define oledbcommand
Dim cmd As New OleDbCommand("SELECT * _
                            "FROM my_table _
                            "WHERE name='Mike' ;", ConnDB)
Dim reader As OleDbDataReader

Array – using array (vb.net) to update multiple records

November 5, 2012 - Posted in .NET 4.0, Applications, ASP.NET, SQL, VS2010 - Permalink
 
'Dim RecIDRE As String = Request.Form("reId")
If Not RecID = 0 Then
Try
'define sql conn
Dim connString As String
connString = ConfigurationManager.ConnectionStrings("MySQLConnectionString1").ToString
Dim conn As New SqlConnection(connString)
Dim cmd As New SqlCommand()

conn.ConnectionString = connString
conn.Open()

Dim printArray As Array = RecID.Split(",")
Dim i As Integer

 For i = 0 To printArray.Length - 1
  count = count + 1
  Dim AdUpdate As String = _
                      "UPDATE real_estate _
                      "SET ad_printed = 'yes' _
                      "WHERE rid = " & printArray(i)
  cmd.Connection = conn
  cmd.CommandText = AdUpdate
  cmd.ExecuteNonQuery()

  Next
  conn.Close()
  conn.Dispose()

  Catch ex As Exception
    Label1.Text = "Real Estate Print Error: " & ex.Message.ToString()
    Label1.Attributes.Add("style", "color: red; fonth-weight: bold;")
  End Try
     Label2.Text = count & " ads printed!"
     Label2.Attributes.Add("style", "color: green; font-size: 12px;")
   Else
     Label2.Text = count & " ads printed!"
     Label2.Attributes.Add("style", "color: blue; font-size: 12px;")
   End If

Update counter in lookup table

October 26, 2012 - Posted in .NET 4.0, Applications, ASP.NET, MS SQL, VS2010 - Permalink
Code behind

Imports System.Web.UI
Imports System.Data.Sql
Imports System.Data.SqlClient

Partial Class _Default
 Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) 
                        Handles Me.Load
 'acquire the ad id
  Dim adLookupCS As String = ConfigurationManager.ConnectionStrings +
                       ("My_ConnectionString1").ToString
  Dim adLookupConn As New SqlConnection(adLookupCS)
  Dim adLookupCmd As New SqlCommand("SELECT * FROM recordLookup;", adLookupConn)
  Dim adLookupRdr As SqlDataReader
    Try
    adLookupCmd.Connection.Open()
    adLookupRdr = adLookupCmd.ExecuteReader()
    If adLookupRdr.Read() Then
    adNumber.Text = adLookupRdr(0)
       Try
        Dim adLNUpdCon As String = ConfigurationManager.ConnectionStrings +
                           ("My_ConnectionString1").ToString
        Dim adLNUpdStr As String = "UPDATE ss_ad_lookup SET lookupNumber = @lkNumb"
        Dim count As Integer = adLookupRdr(0) + 1
        Dim SQLConn As New SqlConnection()
        Dim SQLCmd As New SqlCommand()
        SQLConn.ConnectionString = adLNUpdCon
        SQLConn.Open()

        'define parameters
         SQLCmd.Parameters.AddWithValue("@lkNumb", count)
         SQLCmd.Connection = SQLConn
         SQLCmd.CommandText = adLNUpdStr
         SQLCmd.ExecuteNonQuery()    'execute SQL connection
         SQLConn.Close()
         SQLConn.Dispose()
       Catch ex As Exception
           'ex.Message.ToString()
       End Try
     End If
     Catch ex As Exception
           'ex.Message.ToString()
      End Try
  adLookupCmd.Connection.Close()
  adLookupCmd.Dispose()

    End Sub
End Class
« Older Entries
Newer Entries »