Click here to Skip to main content
15,890,947 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I created an application in VB-2012 that connects to two servers and executes stored procedures depending on what button is clicked on the UI.
I created both of my connections as follows;

Public Class xxx
 Dim cServer As String = "Server1"
 Dim cConnStr As String = "Server=SQL1;database=mast1;User ID=ts;Password=xxxx"
 Dim conn As New SqlConnection(cConnStr)
 Dim sqlCmd As SqlCommand = conn.CreateCommand

 Dim cConStrIT2 As String = "Server=SQL2;database=mast2;User ID=ts;Password=xxxx"
 Dim conIT2 As New SqlConnection(cConStrIT2)
 Dim sqlcmdIT2 As SqlCommand = conIT2.CreateCommand

'****** Execute SProc from server 1
Private Sub btnErr32_Click(sender As System.Object, e As System.EventArgs) Handles btnErr32.Click
  sqlCmd = New SqlCommand("sp_EDI_INLOAD_ERROR0032 @batchid, @membID", conn)
  sqlCmd.Parameters.Add("@batchid", SqlDbType.NVarChar).Value = cBatchid
  sqlCmd.Parameters.Add("@membID", SqlDbType.NChar).Value = cMembIDPaidClm
  ExecuteSQL(nWhatMessage)  'execute the command'
End Sub

'****** Execute SProc from server 2
Private Sub btnPost(sender As System.Object, e As System.EventArgs) Handles 
            btnPost.Click
 sqlcmdIT2 = New SqlCommand("exec sp_EDI_INLOAD_PROF_IT3 @batchid", conIT2)
 sqlcmdIT2.Parameters.Add("@batchid", SqlDbType.NVarChar).Value = cBatchid
 ExecuteSQLIT3(7)
End Sub

Private Sub ExecuteSQL(nMessage As Integer)
    conn.Open()
    dr = sqlCmd.ExecuteReader
    conn.Close()
End Sub

Private Sub ExecuteSQLIT3(nMessage As Integer)
    conIT2.Open()
    dr = sqlcmdIT2.ExecuteReader
    conIT2.Close()
End Sub

End Class
This code works fine.
An enhancement to the application.
The first connection to a server can be one of two servers (1 or 3), depending on the parameter that will be passed.
The second connection will remain as is. 
I made the following changes listed below.
I capture the parameter and i process the correct connections string but when the code gets to the ExecuteSQL subs, i get a few different error messages. Below is what i tried to do.


What I have tried:

Public Class xxx
  Dim cConnStr As String
  Dim cServer As String
  Dim cWhatServer As String() = Environment.GetCommandLineArgs()
  Dim sqlCmd As SqlCommand
  Dim conn As New SqlConnection()

Private sub TS_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
  cWhatServer = Environment.GetCommandLineArgs()

  If cWhatServer.Length > 1 Then
       cServer = cWhatServer(1) "SRV1"
  Else
       cServer = "SRV3"
  End If

  If cServer = "SRV1" Then
     cConnStr = "Server=SQL1;database=mast1;User ID=ts;Password=xxxx"
  Else
     cConnStr = "Server=sQL3;database=mast3;User ID=ts;Password=xxxx"
  End If

  conn = New SqlConnection(cConnStr)
  sqlCmd = conn.CreateCommand
End Sub

'****** Execute SProc from server 1 or Server 3
Private Sub btnErr32_Click(sender As System.Object, e As System.EventArgs) Handles btnErr32.Click
  sqlCmd = New SqlCommand("sp_EDI_INLOAD_ERROR0032 @batchid, @membID", conn)
  sqlCmd.Parameters.Add("@batchid", SqlDbType.NVarChar).Value = cBatchid
  sqlCmd.Parameters.Add("@membID", SqlDbType.NChar).Value = cMembIDPaidClm
  ExecuteSQL(nWhatMessage)  'execute the command'
End Sub

'****** Execute SProc from server 2
Private Sub btnPost(sender As System.Object, e As System.EventArgs) Handles 
            btnPost.Click
 sqlcmdIT2 = New SqlCommand("exec sp_EDI_INLOAD_PROF_IT3 @batchid", conIT2)
 sqlcmdIT2.Parameters.Add("@batchid", SqlDbType.NVarChar).Value = cBatchid
 ExecuteSQLIT3(7)
End Sub

Private Sub ExecuteSQL(nMessage As Integer)
    conn.Open()
    dr = sqlCmd.ExecuteReader
    conn.Close()
End Sub

Private Sub ExecuteSQLIT3(nMessage As Integer)
    conIT2.Open()
    dr = sqlcmdIT2.ExecuteReader
    conIT2.Close()
End Sub

End Class
Posted
Comments
David_Wimbley 24-Jan-18 18:52pm    
"I capture the parameter and i process the correct connections string but when the code gets to the ExecuteSQL subs, i get a few different error messages. Below is what i tried to do."

And what error messages did you get? We can't replicate your errors without having access to your servers so thats not much to go on.
Member 11318400 25-Jan-18 13:47pm    
Hello David,
the error message that keeps displaying is
"Could not find stored procedure 'sp_EDI_INLOAD_ERROR0032'."
And it occurs when i try to execute the dr = sqlcmd.ExecuteReader command.
Also Note, that as i was adding my code to this page I forgot to include the
Dim dr As SqlDataReader. This Dim statement does exist in my code.
Thank you for your quick response.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900