• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • Last Modified:

vb.net newbie - trying to pass variable (ip address) into oledb connection string

Hi all,  still trying to figure this stuff out - Learning more everyday - I need some help with an oledb connection string - I want to use a variable for the source address (dbProvider = "provider = sqloledb;Data Source=10.10.3.220) - replace ip with variable - I have several servers with the same database / tables - I plan on programaticaly replacing the source by using a variable - Thanks in advance for the help -
Imports System.Data.SqlClient
Public Class Form1
    Dim connetionString As String
    Dim con As New OleDb.OleDbConnection
    Dim command As SqlCommand
    Dim dbProvider As String
    Dim da As New OleDb.OleDbDataAdapter
    Dim ds As New DataSet
    Dim sql As String
    Dim inc As Integer
    Dim maxrows As Integer
    Dim cb As New OleDb.OleDbCommandBuilder(da)
    Dim Server As String
 ---------------------------   
    Private Sub NavigateRecords()
        txtInitials.Text = ds.Tables(0).Rows(inc).Item(1)
        txtFirstName.Text = ds.Tables(0).Rows(inc).Item(2)
        txtLastName.Text = ds.Tables(0).Rows(inc).Item(3)
        txtPass.Text = ds.Tables(0).Rows(inc).Item(6)
    End Sub
---------------------------------------------
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
 
    End Sub
---------------------------------------
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Server = "ip address"
        dbProvider = "provider = sqloledb;Data Source=10.10.3.220;Initial Catalog=Wind;User ID=user;Password=pass"
        sql = "SELECT * FROM Tuser"
        con = New OleDb.OleDbConnection(dbProvider)
        con.Open()        
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "VOBUsers")
        con.Close()
        maxrows = (ds.Tables(0).Rows.Count)
        MsgBox("Number of row(s)  -  " & maxrows)
        inc = -1
 
    End Sub
-------------------------------
    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        If inc <> maxrows - 1 Then
            inc = inc + 1
            NavigateRecords()
        Else
            MsgBox("no more rows")
        End If
    End Sub
--------------------------------
    Private Sub bntPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bntPrev.Click
        If inc > 0 Then
            inc = inc - 1
            NavigateRecords()
        ElseIf inc = -1 Then
            MsgBox("No Records Yet")
        ElseIf inc = 0 Then
            MsgBox("First Record")
        End If
    End Sub
------------------------
    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        If inc <> -1 Then
            inc = maxrows - 1
            NavigateRecords()
        End If
    End Sub
--------------------------
    Private Sub bntFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bntFirst.Click
 
        If inc <> 0 Then
            inc = 0
            NavigateRecords()
        End If
 
 
    End Sub
---------------------------------
    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        ds.Tables(0).Rows(inc).Item(1) = txtInitials.Text
        ds.Tables(0).Rows(inc).Item(2) = txtFirstName.Text
        ds.Tables(0).Rows(inc).Item(3) = txtLastName.Text
        ds.Tables(0).Rows(inc).Item(6) = txtPass.Text
        da.Update(ds, "VOBUsers")
        MsgBox("VOB User Info Updated")
    End Sub
End Class

Open in new window

0
NCSA SCADA
Asked:
NCSA SCADA
  • 4
  • 2
  • 2
  • +1
1 Solution
 
aibusinesssolutionsCommented:
Is this what you mean?
Dim Server as String = "ip address"
dbProvider = "provider = sqloledb;Data Source=" & Server & ";Initial Catalog=Wind;User ID=user;Password=pass"
        

Open in new window

0
 
dacITCommented:
I'm pretty sure you can just do this:

Then you can also pass teh IP as a parameter of the method if you want to.
Dim IP as string = "10.10.10.10"
dbProvider = "provider = sqloledb;Data Source=" & IP & ";Initial Catalog=Wind;User ID=user;Password=pass"
 
or 
 
Private Function BuildString(IP as string) as String
Return "provider = sqloledb;Data Source=" & IP & ";Initial Catalog=Wind;User ID=user;Password=pass"
End Function
 
dbProvider = BuildString("10.10.103.10")

Open in new window

0
 
kirihimeteCommented:
Hey dude,

You can add the string value you need to pass in your web.config and reference it in your code.&nbsp; The idea here is, when you publish your site with compiled code for example, you can swap out the value in your web.confg instead of going back to the code and changing it, recompiling it, then re-deploying it.

In your web config :

<appSettings>
     <add key="datasource" value="provider = sqloledb;Data Source=10.10.3.220;Initial Catalog=Wind;User ID=user;Password=pass"/>
</appSettings>

In your calling method in code: (simply reference your key/value pair in <appSettings> in your web.config

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

dbProvider = ConfigurationManager.AppSettings["datasource"];
/// .... REST OF CODE HERE ///

End Sub


To note:  Calling key/value pairs can be down by reference to the "key" - in this case it is 'datasource' OR you can reference by index starting with zero.
So you can also type dbProvider = ConfigurationManager.AppSettings[0]; as it is the first entry in your <appSettings> list?

Hope this makes sense!

Happy coding!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dacITCommented:
Whoops, guess I was beaten to the punch.
0
 
NCSA SCADAAuthor Commented:
thanks for the quick response
Well - I think so - I will give it a try - I thought I tried the same thing on my own, but it gave me syntax errors - back in a bit
0
 
kirihimeteCommented:
Oh yeah one more thing.  You must Import System.Configuration if you haven't do so already.
0
 
aibusinesssolutionsCommented:
Or if you want to get creative, you can do this.

Dim Server As String = "ip address"
Dim Catalog as String = "Database Name"
Dim user as String = "username"
Dim pass as String = "password"

dbProvider = String.Format("provider = sqloledb;Data Source={0};Initial Catalog={1};User ID={2};Password={3}", Server, Catalog, user, pass)
0
 
kirihimeteCommented:
Creative definately but again..If you need to swap out values, you gotta do it via code, recompile, redeploy.  Web.config baby. Hooah!
0
 
kirihimeteCommented:
Post your code and errors if you're still needing assistance :)
0
 
NCSA SCADAAuthor Commented:
I must say "chalk that one up to being tired" - it worked and for the life of me I can't see what is different from what I tried - thanks again
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now