Sql Count

Hello everybody! My code below will INSERT records in my two tables namely "tblStockMasterFile and tblStockFile". When the Save button is clicked it checks whether a product ID entered already exists or not. If product exists then the records will be save in tblStocktransFile Only otherwise to both tables. I attached the table for reference.

Code:

---

Private Sub cmdSave_Click()
  If CheckNullValue = False Then Exit Sub
  Call ReplaceQuotation(txtProdName)

On Error GoTo ErrHandler
  If Checker = True Then  'Product ID already Exist then save in tblStocktransFile Only
   
    sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason,Sequence)" & _
              "VALUES('" & txtProdID.Text & "','" & txtEntryDate.Text & "','" & txtProdPurchasePrice.Text & _
                  "','" & txtProdQTY.Text & " ','" & txtReason.Text & "','" & txtSEQ.Text & "')"
                '  Debug.Print sSQL
    oConn.Execute sSQL
 
  Else  'Product ID does not Exist so save in both tblStockMasterFile and tblStockTransFile
   
    oConn.BeginTrans
     
        sSQL = "INSERT INTO tblStockMasterFile(ProductID, ProductName,Unit,ReOrderPoint)" & _
              "VALUES( '" & txtProdID.Text & "','" & txtProdName.Text & "','" & txtProdUnit.Text & " ', '" & txtProdReOrderPoint.Text & "')"
      oConn.Execute sSQL
 
        sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason,Sequence)" & _
              "VALUES('" & txtProdID.Text & "','" & txtEntryDate.Text & "','" & txtProdPurchasePrice.Text & _
                  "','" & txtProdQTY.Text & " ','" & txtReason.Text & "','" & txtSEQ.Text & "')"
                '  Debug.Print sSQL
      oConn.Execute sSQL

    oConn.CommitTrans
   
  End If
 
  Call FillListView(lstStockMasterFile, rsStock)
  Call ClearFunction(frmStockMasterFile, "TextBox")

  txtProdID.SetFocus
  Exit Sub

ErrHandler:
  Call msgError(Err)
End Sub 'cmdSave_Click

---

I would like instead of checking the ProductID only, I would also like to check the entry date and if the date already exists for the same product ID i would like to the program to increment the existing sequence value i have in my tblStockTransFile so that there's no need for the user to remember the last sequence value for each of the products and manually type it.

Code:

---

For example:
productID: 01
ProductName: Colgate
Unit: Tube
PurchasePrice: 30.00
Quantity:100
Reason: In --->StockIn
EntryDate: 9/15/07
ReOrderPoint: 10
Sequence: 1

---

If I add more stock to the product the program should look into the sequence value and add 1 to it.

Below is function checker. It lacks the entrycode and I dont know how or what should i add to it. Can someone please help me out please?

Code:

---

'TODO : return true if ProductID already exist
Private Function Checker() As Boolean
  Checker = False

  If rsTempStock.State = adStateOpen Then rsTempStock.Close
  sSQL1 = "SELECT COUNT(ProductID) as ProductIDExist " & _
        "FROM tblStockMasterFile " & _
        "WHERE ProductID = '" & (txtProdID.Text) & "'"
  'Debug.Print sSQL1
  rsTempStock.Open sSQL1, oConn, adOpenKeyset, adLockReadOnly

  If rsTempStock("ProductIDExist") > 0 Then Checker = True
End Function  'Function Checker

---

I am thinking of adding another check function for the date like the code below but I guess it's not how it is done.

Code:

---

'TODO : return true if Date already exist
Private Function DateChecker() As Boolean
  DateChecker = False

  If rsTempStock.State = adStateOpen Then rsTempStock.Close
  sSQL1 = "SELECT COUNT(EntryDate) as DateExist " & _
        "FROM tblStockTransFile " & _
        "WHERE EntryDate = '" & (txtEntryDate.Text) & "'"
  'Debug.Print sSQL1
  rsTempStock.Open sSQL1, oConn, adOpenKeyset, adLockReadOnly

  If rsTempStock("DateExist") > 0 Then Checker = True
End Function  'Function DateChecker

---

I've edited my first to make it more understandable.

I dint go thru it fully...but found something you missed in the DateChecker...

Quote:

---

Originally Posted by Simply Me

vb Code:

Private Function DateChecker() As Boolean
  DateChecker = False
 
  If rsTempStock.State = adStateOpen Then rsTempStock.Close
  sSQL1 = "SELECT COUNT(EntryDate) as DateExist " & _
         "FROM tblStockTransFile " & _
         "WHERE EntryDate = '" & (txtEntryDate.Text) & "'"
  'Debug.Print sSQL1
  rsTempStock.Open sSQL1, oConn, adOpenKeyset, adLockReadOnly
 
  If rsTempStock("DateExist") > 0 Then Checker = True ' <---this should be DateChecker
End Function  'Function DateChecker

---

This would be the query to return the next sequence number for a product on a given entry date - all in one query - all in one step.

Code:

---

Select IsNull(Max(Sequence),0)+1
      From tblStockTransFile
      Where ProductId='xyz' and EntryDate='09/01/2007'

---

The IsNull() is a MS SQL function - NZ() I believe would be the ACCESS equivalent.

Keep in mind that even though this returns the proper sequence number to use - in a multi-user system that won't really work.

If you are using MS SQL SERVER (I think you are using EXPRESS?) then you should really investigate STORED PROCEDURES. Using these will really make the multi-user aspect of a system much easier to conquer.

Here is the proof of concept

Code:

---

Create Table #Test (ProdId varchar(3), EntryDate datetime, SeqNo int)

Insert into #Test values ('XYZ','2007-09-15',1)
Insert into #Test values ('XYZ','2007-09-15',2)

Select IsNull(Max(SeqNo),0)+1
      From #Test
      Where ProdId='xyz' and EntryDate='09/01/2007'

Select IsNull(Max(SeqNo),0)+1
      From #Test
      Where ProdId='xyz' and EntryDate='09/15/2007'

Drop Table #Test

---

and this returns

Code:

---

           
-----------
1

           
-----------
3

---

Quote:

---

Originally Posted by ganeshmoorthy

I dint go thru it fully...but found something you missed in the DateChecker...

---

Thanks for pointing that out.

Quote:

---

Originally Posted by szlamany

This would be the query to return the next sequence number for a product on a given entry date - all in one query - all in one step.

Code:

---

Select IsNull(Max(Sequence),0)+1
      From tblStockTransFile
      Where ProductId='xyz' and EntryDate='09/01/2007'

---

The IsNull() is a MS SQL function - NZ() I believe would be the ACCESS equivalent.

---

Where does this code fit in to my code above?

Quote:

---

Originally Posted by szlamany

Keep in mind that even though this returns the proper sequence number to use - in a multi-user system that won't really work.

If you are using MS SQL SERVER (I think you are using EXPRESS?) then you should really investigate STORED PROCEDURES. Using these will really make the multi-user aspect of a system much easier to conquer.

---

As what we have discussed in other thread, I am learning EXPRESS but then i have to finish this one before the month end and Im having trouble with attach option in EXPRESS, so, i guess i have to use MS ACCESS for now and then later have to convert it using EXPRESS.

It is not for multi-user for now but it would be good to prepare it for multi-user.

Is my code logic above correct? need your confirmation.
Back to the SEQ topic....Am correct in my analysis that on every DATE that there is an entry the SEQ value will always be "1"? If so, then for the whole year round the table would grow up.

Is this meant to replace my checker function or i just add it be my checker or another function to be called?

Im sorry for the question in post #'s 5 & 6, I just realized that I have to put that in my cmdSave button. Right now, i have this error when I tried it. "Wrong number of arguments used with function in query expression 'IsNull(Max(Sequence),0)+1'"

vb Code:

Private Sub cmdSave_Click()
  If CheckNullValue = False Then Exit Sub
  Call ReplaceQuotation(txtProdName)
 
On Error GoTo ErrHandler
  If Checker = True Then  'Product ID already Exist then save in tblStocktransFile Only
    oConn.BeginTrans
    
      sSQL = "SELECT IsNull(Max(Sequence),0)+1 " & _
              "FROM tblStockTransFile " & _
                  "WHERE ProductID='" & txtProdID.Text & "' AND EntryDate=" & DTEntryDate.Value & ""
      oConn.Execute sSQL
      
      sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice,Quantity, Reason,Sequence)" & _
              "VALUES('" & txtProdID.Text & "'," & Format$(DTEntryDate.Value, "mm-dd-yyyy") & _
                   ",'" & txtProdPurchasePrice.Text & "','" & txtProdQTY.Text & " ','In ','" & txtSEQ.Text & "')"
                  'Debug.Print sSQL
                  '" & rsStock.Fields("Sequence") + 1 & "'
      oConn.Execute sSQL
      
    oConn.CommitTrans
  Else  'Product ID does not Exist so save in both tblStockMasterFile and tblStockTransFile
    
    oConn.BeginTrans
      
        sSQL = "INSERT INTO tblStockMasterFile(ProductID, ProductName,Unit,ReOrderPoint)" & _
              "VALUES( '" & txtProdID.Text & "','" & txtProdName.Text & "','" & txtProdUnit.Text & " ', '" & txtProdReOrderPoint.Text & "')"
      oConn.Execute sSQL
  
        sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason,Sequence)" & _
              "VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & "','" & txtProdPurchasePrice.Text & _
                   "','" & txtProdQTY.Text & " ','In ','" & txtSEQ.Text & "')"
                 '  Debug.Print sSQL
      oConn.Execute sSQL
 
    oConn.CommitTrans
    
  End If
  
  Call FillListView(lstStockMasterFile, rsStock)
  Call ClearFunction(frmStockMasterFile, "TextBox")
 
  txtProdID.SetFocus
  Exit Sub
 
ErrHandler:
  Call msgError(Err)
End Sub 'cmdSave_Click

I changed the IsNull to NZ since i am using MS ACCESS and i get this error "Undefined function 'NZ' in expression."

I don't use ACCESS so I don't know the replacement syntax for the functon ISNULL which is missing from ACCESS. I thought it was NZ() - you would have to look that up yourself.

btw - I see in your code that you have a BEGINTRAN and some SQL statements. If they get an error you do a MSGBOX.

Do you realize that the transaction is held open while the user if thinking about clicking OK to that MSGBOX? You need to ROLLBACK or COMMIT a transaction immediately!

This is really, really bad.

You should get over your SQL EXPRESS issue and you should have started using STORED PROCEDURES in SQL EXPRESS. This would have made your entire SQL life 100% easier. All SQL would be done in STORED PROCEDURES written into the database.

Quote:

---

Originally Posted by szlamany

btw - I see in your code that you have a BEGINTRAN and some SQL statements. If they get an error you do a MSGBOX.

Do you realize that the transaction is held open while the user if thinking about clicking OK to that MSGBOX? You need to ROLLBACK or COMMIT a transaction immediately!

This is really, really bad.

You should get over your SQL EXPRESS issue and you should have started using STORED PROCEDURES in SQL EXPRESS. This would have made your entire SQL life 100% easier. All SQL would be done in STORED PROCEDURES written into the database.

---

I already have a msgbox in my errorhandler i am just missing this " oConn.RollbackTrans"

I really really to migrate to SQL EXPRESS now but I am afraid i might not finish this if i have to learn Stored Procedures and not to mention my issue about the attach option.

ok I am using I changed my connection already I'm using SQL EXPRESS. I added the RollbackTrans already.

I tried your code with the IsNull and the attached pic shows my error.

One of my problem using SQL EXPRESS is this, the project which im working with is in my flash drive so if i want to work on it anytime i can not just do it becuase the database is not present since i cannot copy the database.

The point about using SPROCS is that you put the INSERT statements into a PROCEDURE.

You develop and test them in a query window.

Not in the VB code.

The VB code simply calls the SPROC by passing the important parameters to it. The VB code never ever needs to use BEGIN TRAN or COMMIT or ROLLBACK - that's all does in the isolation of the SPROC.

This separates the UI from the business logic and makes you more productive.

The ATTACHDBFILENAME is easy to use.

So - how do we proceed from here?

Do we get your ATTACHDBFILENAME issue fixed? From another thread of yours...

Quote:

---

Originally Posted by Simply Me

yes i've seen it and where will add the highlighted part of code in the code you post in #17?

Code:

---

'Source=.\SQLEXPRESS;AttachDbFilename="C:\BigLongPathName\MyDataFile1.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

---

the pathname of the db file, if this is possible then i just can copy the .mdf file and put it in the same folder with my vb files...so that i will not install anymore sql express to the local machine where i will install the prog.

---

Do we learn about calling SPROCS from VB?

Do we learn about writing SPROCS??

Let's get at least one of these 100% understood and conquered.

Quote:

---

Originally Posted by szlamany

So - how do we proceed from here?

Do we get your ATTACHDBFILENAME issue fixed? From another thread of yours...



Do we learn about calling SPROCS from VB?

Do we learn about writing SPROCS??

Let's get at least one of these 100% understood and conquered.

---

I say YES to all your questions. I like to learn those as we go through this project in fact as we are discussing im reading a about SPROCS.

First, the issue on the sequence field. I attached the error i recieved using it.

If we are doing STORED PROCEDURES first - then show me the code for the SPROC that you have.

I dont have any yet as I am only starting reading it.

The code I have right now is this which is part working. Part working I say because the first time i enter a product it accept it assigned a sequence "0" and is save in the tables but the second time i enter the same product it again accepts it but then the sequence is still "0" and what puzzles me is the Date that it save is not correct instead of "9/16/2007" is saves "6/27/1894" and the third time i enter the same product it already gave me an error message "violation of PRIMARY KEY ConstraintPK_tblStockTransFile. Cannot insert duplicate key in object dbo.tblStockTransFile

vb Code:

Private Sub cmdSave_Click()
  If CheckNullValue = False Then Exit Sub
  Call ReplaceQuotation(txtProdName)
On Error GoTo ErrHandler
  If Checker = True Then  'Product ID already Exist then save in tblStocktransFile Only
    oConn.BeginTrans
 
      sSQL = "SELECT IsNull(Max(Sequence),0)+1 " & _
              "FROM tblStockTransFile " & _
                  "WHERE ProductID='" & txtProdID.Text & "' AND EntryDate=" & DTEntryDate.Value & ""
                  Debug.Print sSQL
      oConn.Execute sSQL
      
      sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice,Quantity, Reason)" & _
              "VALUES('" & txtProdID.Text & "'," & Format$(DTEntryDate.Value, "mm-dd-yyyy") & _
                   ",'" & txtProdPurchasePrice.Text & "','" & txtProdQTY.Text & "','In')"
                  Debug.Print sSQL
                  '" & rsStock.Fields("Sequence") + 1 & "'
      oConn.Execute sSQL
      
    oConn.CommitTrans
  Else  'Product ID does not Exist so save in both tblStockMasterFile and tblStockTransFile
    
    oConn.BeginTrans
      
      sSQL = "INSERT INTO tblStockMasterFile(ProductID, ProductName,Unit,ReOrderPoint)" & _
                "VALUES( '" & txtProdID.Text & "','" & txtProdName.Text & "','" & txtProdUnit.Text & " ', '" & txtProdReOrderPoint.Text & "')"
      oConn.Execute sSQL
      
      sSQL = "SELECT IsNull(Max(Sequence),0 )+ 1" & _
              "FROM tblStockTransFile " & _
                  "WHERE ProductID='" & txtProdID.Text & "' AND EntryDate=" & DTEntryDate.Value & ""
                  Debug.Print sSQL
      oConn.Execute sSQL
      
      sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason)" & _
                "VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & "','" & txtProdPurchasePrice.Text & _
                   "','" & txtProdQTY.Text & " ','In')"
                 '  Debug.Print sSQL
      oConn.Execute sSQL
 
    
    oConn.CommitTrans
    
  End If
  
  Call FillListView(lstStockMasterFile, rsStock)
  Call ClearFunction(frmStockMasterFile, "TextBox")
 
  txtProdID.SetFocus
  Exit Sub
 
ErrHandler:
  oConn.RollbackTrans
  Call msgError(Err)
End Sub 'cmdSave_Click

We don't like IN-LINE SQL in our shop - it's a pain to build and debug. You have date issues - as 100's of other people have had on the forum. You are not formatting your dates consistently in all your SQL statements...

Using SPROCS makes all this easier.

Unfortunately I do not have time to look at your VB code to get it cleaned up.

I've got a deliverable I'm working on for Monday AM on a pocket PC that's giving me my own headaches.

I suggested 3 things I could do with you today - if you are interested in conquering any of those 3 I'm available.

Quote:

---

Originally Posted by szlamany

We don't like IN-LINE SQL in our shop - it's a pain to build and debug. You have date issues - as 100's of other people have had on the forum. You are not formatting your dates consistently in all your SQL statements...

Using SPROCS makes all this easier.

Unfortunately I do not have time to look at your VB code to get it cleaned up.

I've got a deliverable I'm working on for Monday AM on a pocket PC that's giving me my own headaches.

I suggested 3 things I could do with you today - if you are interested in conquering any of those 3 I'm available.

---

I am very much interested to learn.

Creating a procedure is done in a query window - in Management Studio.

The procedure is saved as a .SQL text file from that window.

Here's an example of a small procedure we have.

Code:

---

USE TCS
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[frmPerTax_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[frmPerTax_Update]
GO

Create Procedure frmPerTax_Update
        @Towncode varchar(2)
        ,@Yr int
        ,@BillNum varchar(10)
        ,@Closed varchar(1)
        ,@Comment varchar(50)
as

Set NoCount On

Update PerTax_T Set Closed=IsNull(@Closed,'')
                ,Comment=IsNull(@Comment,'')
                Where Yr=@Yr and Town=@Towncode and BillNum=@BillNum

GO
GRANT EXECUTE ON frmPerTax_Update TO TCSUser
Go
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

---

This creates the SPROC - does it make sense? Please review it and ask questions on any item you are unclear on.

You have parameters that are passed from VB into the SPROC - and they are properly datatyped. And those parameter variables are used in the SQL statements throughout the SPROC.

Your SPROC for UPDATE would need to have parameters for each value you need - with the proper datatype. Your SAVE sproc would have each of your INSERTS in that one sproc - so it's doing all the work in one single call.

You test the SPROC from another QUERY WINDOW - with something like this:

Code:

---

Begin Tran
Exec frmPerTax_Update '01',2007,'0000123','Y','WE JUST CLOSED THIS BILL'
Select * From PerTax_T Where BillNum='0000123'
Rollback

---

Notice how the testing is wrapped in a BEGIN TRAN/ROLLBACK - that is done so we can test dozens of calls without messing with our tables for real. The SELECT after the EXEC is an example of me wanting to see the results of my SPROC execution - testing that is actually worked.

So all this SPROC work is done outside of VB - and it's extremely easy to produce working SQL procedures with this.

Don't even think about calling them from VB first - you develop and test them all in Management Studio.

Quote:

---

Originally Posted by szlamany

Creating a procedure is done in a query window - in Management Studio.

The procedure is saved as a .SQL text file from that window.

Here's an example of a small procedure we have.

[code]USE TCS
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[frmPerTax_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[frmPerTax_Update]
GO

---

This is checking if the procedure exists if it exists then drop it right? Question:Do I need to use * all the time?

Quote:

---

Create Procedure frmPerTax_Update
@Towncode varchar(2)
,@Yr int
,@BillNum varchar(10)
,@Closed varchar(1)
,@Comment varchar(50)
as

Set NoCount On

Update PerTax_T Set Closed=IsNull(@Closed,'')
,Comment=IsNull(@Comment,'')
Where Yr=@Yr and Town=@Towncode and BillNum=@BillNum

GO
GRANT EXECUTE ON frmPerTax_Update TO TCSUser
Go
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

---

here you created the procedure named as frmPerTax_Update. From the set NoCount ON downwards i down know what are there uses.
QUOTE]

Exists() is a nice SQL construct. If checks for the existence of rows matching the WHERE clause. It does not actually return anything.

So the SELECT * is standard and acceptable in an EXISTS() function.

All those SET statements are simply boilerplate that I've copied from MS examples.

The GRANT is something you probably would not need - as we have permission issues that we have to deal with in our apps. You could remove the GRANT statement if you need to.

Now - let's get your SPROC written to insert into the MASTER and TRANS file.

I have tried creating SPROCS and sucessfully run it.

vb Code:

Use MasterFile
If EXISTS(SELECT name FROM sysobjects
        WHERE name='myProcedure')
    DROP PROCEDURE myProcedure
GO
 
CREATE PROCEDURE myProcedure
AS
SELECT tblStockMasterFile.ProductID, tblStockMasterFile.ProductName,
          tblStockMasterFile.Unit, tblStockMasterFile.ReOrderPoint,
          tblStockTransFile.EntryDate, tblStockTransFile.Sequence,
          tblStockTransFile.PurchasePrice, tblStockTransFile.Quantity,
          tblStockTransFile.Reason
              FROM tblStockMasterFile INNER JOIN tblStockTransFile ON
                  tblStockMasterFile.ProductID = tblStockTransFile.ProductID

Can I just call this in my form_load

Put a GO statement after the USE MASTERFILE.

Each GO statement sends the "prior" command or block of commands to the server to be executed.

Using this SPROC to get data from your database is great.

Please show me the VB code you are using to get this executed - we need to make sure you follow proper techniques now before we get burned.

Im guessing right now as i am reading a book while we talked. I call my this first

vb Code:

Public Sub openConnection()
  Set oConn = New ADODB.Connection
 
  With oConn
    .Provider = "SQLOLEDB"
    .CursorLocation = adUseClient
    .Properties("Data Source").Value = ".\SQLEXPRESS"
    .Properties("Initial Catalog").Value = "MasterFile"
    .Properties("Integrated Security").Value = "SSPI"
    .CursorLocation = adUseClient
'    .Mode = adModeReadWrite
    .Open
  End With
End Sub

then i got to have something like this cmd.Commandtype=adCMDStoredProc

here is my old code on form load. I dont what to do next.

vb Code:

Option Explicit
Dim rsStock As ADODB.Recordset
Dim rsTempStock As ADODB.Recordset
Dim CMD As New ADODB.Command---->i just added this
Dim sSQL$, sSQL1$, iTempProdID$
Dim edwin%
 
 
Private Sub Form_Load()
  Call openConnection
  Me.Left = LeftPos - 400
  Me.Top = TopPos - 200
 
  Set rsStock = New ADODB.Recordset
  Set rsTempStock = New ADODB.Recordset
  rsStock.CursorLocation = adUseClient
 
    sSQL = "SELECT tblStockMasterFile.ProductID, tblStockMasterFile.ProductName, " & _
          "tblStockMasterFile.Unit, tblStockMasterFile.ReOrderPoint, " & _
          "tblStockTransFile.EntryDate, tblStockTransFile.Sequence, " & _
          "tblStockTransFile.PurchasePrice, tblStockTransFile.Quantity, " & _
          "tblStockTransFile.Reason " & _
              "FROM tblStockMasterFile INNER JOIN tblStockTransFile ON " & _
                  "tblStockMasterFile.ProductID = tblStockTransFile.ProductID"
 
  If rsStock.State = adStateOpen Then rsStock.Close
  rsStock.Open sSQL, oConn, adOpenStatic, adLockOptimistic
  
  DTEntryDate.Value = Format(Now,"mm/dd/yyyy") 'Display the Current Date
  Call FillListView(lstStockMasterFile, rsStock)
End Sub 'Form_Load

This creates a command object and a recordset object and executes a stored procedure.

Code:

---

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

Dim offCmnd As ADODB.Command

Set offCmnd = New ADODB.Command

With offCmnd
    .CommandText = "rptDailyAttTeacher"
    .ActiveConnection = gCn
    .CommandType = adCmdStoredProc
   
    .Parameters.Append .CreateParameter("@Yr", adInteger, adParamInput, , frmTeaAttend.lblYr.Caption)
    .Parameters.Append .CreateParameter("@Bldg", adInteger, adParamInput, , frmTeaAttend.gstrBldg)
    .Parameters.Append .CreateParameter("@Attendance_Date", adDate, adParamInput, , Format$(dtpOffice.Value, "SHORT DATE"))
    .Parameters.Append .CreateParameter("@Sort_by_Grade$N_Y", adVarChar, adParamInput, 1, "N")
    .Parameters.Append .CreateParameter("@Final_Report$N_Y", adVarChar, adParamInput, 1, "N")
    Set rs = .Execute()
End With

Set offCmnd = Nothing

---

This one is from a very old program we have - but it has adDate datatypes - which should help you later.

this is what i have now before i read your post above

vb Code:

Private Sub Form_Load()
  Call openConnection
  Me.Left = LeftPos - 400
  Me.Top = TopPos - 200
  
  'Calling myProcedure SPROC
  CMD.ActiveConnection = oConn
  CMD.CommandText = "[myProcedure]"
  CMD.CommandType = adCmdStoredProc
  Set rsStock = CMD.Execute
  Set rsTempStock = CMD.Execute
  
  'DTEntryDate.Value = Date 'Display the Current Date
  Call FillListView(lstStockMasterFile, rsStock)
End Sub 'Form_Load

I don't see you creating or destroying your CMD or RSSTOCK objects - it's nicer to create and destroy them as I hav done.

Also - you have CMD.Execute on two lines of code - why are you filling two recordsets that way?

Recordsets are typically used to load data and immediately put it into controls, for instance - not to be retained.

Are you retaining these recordsets.

this is what i have now before i read your post above

vb Code:

Private Sub Form_Load()
  Call openConnection
  Me.Left = LeftPos - 400
  Me.Top = TopPos - 200
  
  'Calling myProcedure SPROC
  CMD.ActiveConnection = oConn
  CMD.CommandText = "[myProcedure]"
  CMD.CommandType = adCmdStoredProc
  Set rsStock = CMD.Execute
  Set rsTempStock = CMD.Execute
  
  'DTEntryDate.Value = Date 'Display the Current Date
  Call FillListView(lstStockMasterFile, rsStock)
End Sub 'Form_Load

the code now looks like this

vb Code:

Set rsStock = New ADODB.Recordset
  Set rsTempStock = New ADODB.Recordset
  Set CMD = New ADODB.Command
  
  'Calling myProcedure SPROC
  With CMD
    .ActiveConnection = oConn
    .CommandText = "[myProcedure]"
    .CommandType = adCmdStoredProc
    Set rsStock = .Execute()
    Set rsTempStock = .Execute()
  End With

but i dont understand the parameters

I am destroying them on form_Unload.

Regarding the rsTempStcok, I use it here

vb Code:

'TODO : return true if ProductID already exist
Private Function Checker() As Boolean
  Checker = False
 
  If rsTempStock.State = adStateOpen Then rsTempStock.Close
  sSQL1 = "SELECT COUNT(ProductID) as ProductIDExist " & _
         "FROM tblStockMasterFile " & _
         "WHERE ProductID = '" & (txtProdID.Text) & "'"
  'Debug.Print sSQL1
  rsTempStock.Open sSQL1, oConn, adOpenKeyset, adLockReadOnly
 
  If rsTempStock("ProductIDExist") > 0 Then Checker = True
End Function  'Function Checker

when i added this line

Code:

---

    .Parameters.Append .CreateParameter("@EntryDate", adDate, adParamInput, , Format$(DTEntryDate.Value, "SHORT DATE"))

---

i got error "Procedure myProcedure has no parameters and arguments were supplied."

Its late here now, almost 1:00 A.M.
I guess we have to continue tomorrow night again. It's night time here and I think its day time there.

thanks for the usual help.

The parameters don't come into play on this SPROC - as it's returning all rows - there is no WHERE clause.

Do you want a WHERE clause?

You will use a WHERE clause on a UPDATE type of SPROC

And again - why do you need to exact same copies of the same recordset?

The code you showed is closing rsTempStock if it's open - so why did you fill it initially????

So there's no need to add the .parameters for now.

How about the codes in save button. If I make SPROC for it, how do i call it?

Quote:

---

Originally Posted by Simply Me

So there's no need to add the .parameters for now.

How about the codes in save button. If I make SPROC for it, how do i call it?

---

I already said that the UPDATE sproc will have parameters. It was the "select" sproc that had no WHERE clause - right?

This question makes me think that you are trying to overcome calling the UPDATE sproc and developing the UPDATE sproc all in one moment. That won't work - you need to go through this in stages.

Develop the SAVE sproc.

It will have parameters for each field that is being saved and each item in the WHERE clause that fingers the right record.

You are not supposed to be thinking about calling if from VB - you need to focus on the database requirements first. What fields are being updated - each one needs to be a parameter.

I am trying to create procedure for the code below under my SAVE button.

vb Code:

If Checker = True Then  'Product ID already Exist then save in tblStocktransFile Only
    oConn.BeginTrans
 
      sSQL = "SELECT IsNull(Max(Sequence),0)+1 " & _
              "FROM tblStockTransFile " & _
                  "WHERE ProductID='" & txtProdID.Text & "' AND EntryDate=" & DTEntryDate.Value & ""
                  Debug.Print sSQL
      oConn.Execute sSQL
      
      sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice,Quantity, Reason)" & _
              "VALUES('" & txtProdID.Text & "'," & DTEntryDate.Value & _
                   ",'" & txtProdPurchasePrice.Text & "','" & txtProdQTY.Text & "','In')"
                  Debug.Print sSQL
                  '" & rsStock.Fields("Sequence") + 1 & "'
      oConn.Execute sSQL
      
    oConn.CommitTrans
  Else  'Product ID does not Exist so save in both tblStockMasterFile and tblStockTransFile
    
    oConn.BeginTrans
      
      sSQL = "INSERT INTO tblStockMasterFile(ProductID, ProductName,Unit,ReOrderPoint)" & _
                "VALUES( '" & txtProdID.Text & "','" & txtProdName.Text & "','" & txtProdUnit.Text & " ', '" & txtProdReOrderPoint.Text & "')"
      oConn.Execute sSQL
      
      sSQL = "SELECT IsNull(Max(Sequence),0 )+ 1" & _
              "FROM tblStockTransFile " & _
                  "WHERE ProductID='" & txtProdID.Text & "' AND EntryDate=" & DTEntryDate.Value & ""
                  Debug.Print sSQL
      oConn.Execute sSQL
      
      sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason)" & _
                "VALUES('" & txtProdID.Text & "'," & DTEntryDate.Value & ",'" & txtProdPurchasePrice.Text & _
                   "','" & txtProdQTY.Text & " ','In')"
                 '  Debug.Print sSQL
      oConn.Execute sSQL
 
    
    oConn.CommitTrans
    
  End If
  
  Call FillListView(lstStockMasterFile, rsStock)
  Call ClearFunction(frmStockMasterFile, "TextBox")
 
  txtProdID.SetFocus
  Exit Sub
 
ErrHandler:
  oConn.RollbackTrans
  Call msgError(Err)
End Sub 'cmdSave_Click

I have come up of something like the code below but it is still lacking. I dont have parameters yet. Am I on the right track?

Code:

---

Use MasterFile
GO
If EXISTS(SELECT name FROM sysobjects
                WHERE name='StockIn')
        DROP PROCEDURE StockIn
GO

CREATE PROCEDURE StockIn
        @ProdID varchar(50),@EntryDate datetime,
        @ProdPurchasePrice money, @ProdQTY numeric(18,0)
AS
DECLARE @errorCode int 
INSERT tblStockTransFile (ProductID, EntryDate, PurchasePrice,Quantity)
              VALUES(@ProdID,@EntryDate,@ProdPurchasePrice,@ProdQTY)
SET @ErrorCode=@@ERROR
IF (@errorCode=0)
        RETURN (0)
ELSE
        RETURN (@ErrorCode)

---

Am I doing it correct?

Have you tested it in a query window yet?

Code:

---

Exec StockIn 'xyz','2007-09-17',100,1.5

---

Substitute the 'xyz' for a real product code if you like.

Post #19 shows a better way to test with a BEGIN TRAN and ROLLBACK with a SELECT for proof of concept.

i get this error "Msg 547, Level 16, State 0, Procedure StockIn, Line 7
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblStockTransFile_tblStockMasterFile". The conflict occurred in database "MasterFile", table "dbo.tblStockMasterFile", column 'ProductID'.
The statement has been terminated."

That message clearly states that the foreign key is not happy with you.

There is no entry in the Master File yet - are you simply using a bad set of sample data??