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.
| Author |
Topic |
|
itchocks
Starting Member
44 Posts |
 Posted - 2003-04-24 : 02:32:07
|
| Hi , I am new to MsSql, I create a procedure and I get executed it, the below is my procedure which I created as you said.CREATE procedure [dtpass] (@dt1 as datetime, @dt2 as datetime) as begin select Stock.ItemId, StockDetail.Quantity, Sales.Quantity, StockDetail.WhareHouse from StockDetail LEFT OUTER JOIN Stock on StockDetail.ItemId = Stock.ItemId LEFT OUTER JOIN Sales on Stock.ItemId = Sales.ItemId Where Sales.Date between @d1 and @d2 and StockDetail.WareHouse = ''01'' Order by ItemId and the below is my code which I am using to execute the procedure. Here everything is working fine when I pass the two parameters i.e Fromdate and ToDate, I hope the output is also correct.Private Sub Command1_Click() cnn.Open "Provider=SQLOLEDB.1;User ID=scala;Password = scala;Data Source = scala;Intial Catalog = scalaDB" rst.Open "exec dtpass '" & DTPicker1.Value & "'," & "'" & DTPicker2.Value & "'", cnn, adOpenStatic, adLockReadOnly, adCmdText Set MSHFlexGrid1.DataSource = rst End Sub But now I am trying to pass the third parameter, its a string datatype in the Database. How should I create the procedure with the third parameter and how I will write it in VB. the below is the new procedure where I am getting the error. while executing from VBCREATE procedure [dtpass] (@dt1 as datetime, @dt2 as datetime, @WH) as begin select Stock.ItemId, StockDetail.Quantity, Sales.Quantity, StockDetail.WhareHouse from StockDetail LEFT OUTER JOIN Stock on StockDetail.ItemId = Stock.ItemId LEFT OUTER JOIN Sales on Stock.ItemId = Sales.ItemId Where Sales.Date between @d1 and @d2 and StockDetail.WareHouse = @WH Order by ItemId the below is the code i am using in VB to pass three parametersrst.Open "exec dtpass'" & DTPicker1.Value & "'," & "'" & DTPicker2.Value & "'" & "'" & Text1.Text & "'", cnn, adOpenStatic, adLockReadOnly, adCmdText when I try to execute the following error message is coming.Run-time error '-2147217900(80040e14)':Line 1: Incorrect Syntax near ' 01'.Kindly suggest me your idea the way i create the procedure is correct or not. Thank you for your help.Thank you very much,Chock.Chock |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-04-24 : 02:52:58
|
| Chock,The first thing that jumps out at me is that you have not provided a datatype for the @WH parameter in your stored procedure. If it is a string, then you probably want varchar such as...CREATE procedure [dtpass] (@dt1 as datetime, @dt2 as datetime, @WH as varchar(100)) as begin ------------------------------------------------------The more you know, the more you know you don't know.Edited by - AjarnMark on 04/24/2003 02:53:21 |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-04-24 : 02:58:48
|
| On a related note, you might want to make looking into the ADODB Command object your next research topic. I prefer using that, with its parameters that you can append, instead of passing everything in-line like you are doing with the Recordset object. Your way still works, but I find the Command object easier to read, especially as your number of arguments goes up. I also find it sets me in good position to do additional validation of the values I'm about to pass to the sproc.------------------------------------------------------The more you know, the more you know you don't know. |
|
|
|
KnooKie
Aged Yak Warrior
623 Posts |
Posted - 2003-04-28 : 07:40:55
|
| Just a few too many ampersands and the brackets missing i think......rst.Open "dtpass('" & DTPicker1.Value & "','" & DTPicker2.Value & "','" & Text1.Text & "')", cnn, adOpenStatic, adLockReadOnly, adCmdText ====Paul |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-04-28 : 10:40:59
|
| I agree with Mark, use the command object in ADODB. What if your string data has single quotes in it ? what if a field is Null? appending a long string together and executing it can cause lots of problems.- Jeff |
|
|
|
|
|
|
|
|
How to create procedure with parameters