How to pass a two date parameter from VB to Ms Sql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to pass a two date parameter from VB to Ms Sql

Author

Topic

itchocks
Starting Member

44 Posts

Posted - 2003-04-22 : 09:04:08

Hai ,

I am trying to write a procedure and pass the paramters through VB.
The below is my procedure i had created in VB i hard coded the date and use the ADO control and tried to display the output through the grid. its working fine. Now I am trying to pass the date parameter through VB. if it works, I hope i can bring the further things one by one. I need your help in this. when you have time kindly tell me how to pass the date parameter to Ms Sql from VB. I am connecting Ms Sql to VB through ODBC by coding and not using the controls.

The below is the porcedure I create in Ms Sql server.

Create Procedure [dtpass] as
Declare @d1 as datetime, @d2 as datetime

Select Stock.ItemId, StockDetail.Quantiy, Sales.Quantity, StockDetail.WareHouse from StockDetail Left Outer Join Stock on
StockDetail.ItemId = Sales.ItemId Where StockDetail.WareHouse = '01' and Sales.Date between @d1 and @d2 order by ItemId.
[\Blue]

How i will execute this procedure in VB. I mean how I will pass the FromDate and ToDate to this procedure from VB.

For example the below is the code I am using to connect the Database server is below,

Dim cnn as ADODB.Connection
Dim rst as ADODB.Recordset

Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordsest

cnn.Open "Provider=SQLOLEDB.1;User ID=scala;Password = scala;
Data Source=scala;Intial Catalog = scalaDB"

rst.Open rst.Open "select distinct ItemId from Stock order by ProductName", cnn, adOpenStatic, adLockReadOnly, adCmdText

do I want to modify the above connection mehtod to the database or not. and also inform me with this code how I will pass the parameters to the procedures, to use the Procdeure what are the References I want to add to VB. When you have time reply me.

I am just devoloping the front end so there should not be any updates in my database because of this procedure. Kindly suggest me your idea.

Thank you very much,
Chock.

Chock

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-22 : 09:19:30

use iso standard which can be passed as string.
'YYYYMMDD'

Second you want to use ADO more specifically
create a connection
create a command object
possibly create a recordset object
set connection properties
set command properties
set parameter of command object
open connection
execute command
possibly return Recordset object if you need it
disconnect connection
process recordset
set recordset = nothing
set command = nothing
set connection = nothing

If you do a forum search on
"ADO command"
you'll get lots more detail

Edited by - ValterBorges on 04/22/2003 09:22:43

Edited by - ValterBorges on 04/22/2003 09:25:40

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-22 : 21:13:26

That what I do
Example:
In a Module I have :
Global gcnSQL As ADODB.Connection

Sub Open_cnSQL()
sDBname = "SMARTSERVER2000"
Set gcnSQL = New ADODB.Connection
With gcnSQL
.ConnectionString = "Provider=SQLOLEDB.1;Password=nt2000;Persist Security Info=True;User ID=admin;Initial Catalog=smart;Data Source=SMARTSERVER2000"
.CommandTimeout = 0
.Open
End With
End Sub

Sub close_cnSQL()
'Cerrar la conexion
Set gcnSQL = Nothing
End Sub

In the form :

Dim adoRS As ADODB.Recordset

Private Sub Form_Load()
Open_cnSQL

End Sub

Private Sub Form_Unload(Cancel As Integer)
close_cnSQL
End Sub

Private Sub cmd_ItemsNew_Click()
Dim sFile As String
Dim dDia As Date

dDIA = Date
Set adoRS = New ADODB.Recordset
sSQL = "SP_ITEMSNUEVOS '" & dDia & "', 2000, 'BP-2000', 'TJU33'"
adoRS.Open sSQL, gcnSQL, adOpenForwardOnly, adLockReadOnly, adCmdText

With adoRS

If .EOF And .BOF Then 'Not fount
...
Else
Do Until .EOF
....
....
.MoveNext
Loop

End If
.Close
End With
Set adoRS = Nothing
End Sub

You have to put the date value between ' '
Tell me if this helps!!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-22 : 21:23:04

That's one way but a command object allows you to pass parameters in and out and allows for a command that does not return a recordset to be executed which improves performance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources