Add parameters

I need to add parameters to my SQL string, like Where [EndDate] >= @.HStart AND [EndDate] <= @.HEnd, I tried to Dim variables but it caused an error. Can anyone help me with this?

Thank You,

Sub BindDataCurrent()
Where [EndDate] >= @.HStart AND [EndDate] <= @.HEnd"
'MyCommand.Parameters.Add("@.HStart", SqlDbType.VarChar, 80).Value = HistoryStartText.Text
'MyCommand.Parameters.Add("@.HEnd", SqlDbType.VarChar, 80).Value = HistoryEndText.Text
ConnectStr = ConfigurationSettings.AppSettings("ConnectStr")
Dim MyConnection As SqlConnection = New SqlConnection(ConnectStr)
MyConnection = New SqlConnection(ConnectStr)

Dim SQL As String = "Select [Campaign_ID], [Campaign Type], [Campaign Date], [EndDate],[Comment] FROM tblCampaignTracking Where [EndDate] >= @.HStart AND [EndDate] <= @.HEnd"
Dim DA As SqlDataAdapter = New SqlDataAdapter(SQL, MyConnection)
Dim DS As New DataSet
DA.Fill(DS, "tblCampaigns")
MyEditDataGridCurrent.DataSource = DS.Tables("tblCampaigns").DefaultView
MyEditDataGridCurrent.DataBind()
End SubHi,
Why you do not use this select?

Dim SQL As String = "Select [Campaign_ID], [Campaign Type], [Campaign Date], [EndDate],[Comment] FROM tblCampaignTracking Where [EndDate] >= " & HistoryStartText.Text & " AND [EndDate] <= " & HistoryEndText.Text

Your Code:
Sub BindDataCurrent()

ConnectStr = ConfigurationSettings.AppSettings("ConnectStr")
Dim MyConnection As SqlConnection = New SqlConnection(ConnectStr)
MyConnection = New SqlConnection(ConnectStr)

Dim SQL As String = "Select [Campaign_ID], [Campaign Type], [Campaign Date], [EndDate],[Comment] FROM tblCampaignTracking Where [EndDate] >= " & HistoryStartText.Text & " AND [EndDate] <= " & HistoryEndText.Text

Dim DA As SqlDataAdapter = New SqlDataAdapter(SQL, MyConnection)
Dim DS As New DataSet
DA.Fill(DS, "tblCampaigns")
MyEditDataGridCurrent.DataSource = DS.Tables("tblCampaigns").DefaultView
MyEditDataGridCurrent.DataBind()
End Sub

Add linked server at Excel VBA

I had SQLDMO at reference library.

Private Sub test()
Dim s As SQLDMO.SQLServer
Dim ls As SQLDMO.LinkedServer
Set s = New SQLDMO.SQLServer
s.Connect "Server1", "ID", "Password"
Set ls = New SQLDMO.LinkedServer
With ls
.Name = "Server2"
.ProviderName = "SQLOLEDB"
.DataSource = "Server2"
' .ProviderString = ""

End With
s.LinkedServers.Add ls
s.Close

'End Sub

I block ProviderString as i don't know what is it.

I got an error message when it's running the line "s.LinkedServers.Add ls" that

"Run-time error '-2147206257 (80043b8f)':

Automation error"

My question:

1. What is providerstring? what should be put here?

2. How to fix the error

Hi there,

Provider string should look something like the following:

@.provstr=N'DRIVER={<driver name of your linked server, in this case Excel} ;SERVER=<address if your server where the driver resides>;DATABASE=<database name>; USER=<username>; PASSWORD=<password>; OPTION=3'

I found this here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=304918&SiteID=1

Also, this may help: http://www.databasejournal.com/features/mssql/article.php/10894_3085211_2

Since you're not executing within the QA of management studio, I'm guessing that you only need to put Excel's driver name in your provider string, but don't take my word as I haven't tried doing this in VB.

Hope this helps!

Isa