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
Last edited by Simply Me; Sep 15th, 2007 at 05:16 AM.
To give is always to be NOBLE...
To received is always to be BLESSED....
Each day strive to be NOBLE
Each day strive to be BLESSED
If this post has helped you. Please take time to rate it.
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
Last edited by szlamany; Sep 15th, 2007 at 11:54 AM.
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
I dint go thru it fully...but found something you missed in the DateChecker...
Thanks for pointing that out.
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?
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.
Last edited by Simply Me; Sep 16th, 2007 at 02:39 AM.
To give is always to be NOBLE...
To received is always to be BLESSED....
Each day strive to be NOBLE
Each day strive to be BLESSED
If this post has helped you. Please take time to rate it.
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
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.
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
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.
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
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.
To give is always to be NOBLE...
To received is always to be BLESSED....
Each day strive to be NOBLE
Each day strive to be BLESSED
If this post has helped you. Please take time to rate it.
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.
To give is always to be NOBLE...
To received is always to be BLESSED....
Each day strive to be NOBLE
Each day strive to be BLESSED
If this post has helped you. Please take time to rate it.
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...
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?
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.
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
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
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.
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
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.
To give is always to be NOBLE...
To received is always to be BLESSED....
Each day strive to be NOBLE
Each day strive to be BLESSED
If this post has helped you. Please take time to rate it.
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.
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
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? Questiono I need to use * all the time?
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]
To give is always to be NOBLE...
To received is always to be BLESSED....
Each day strive to be NOBLE
Each day strive to be BLESSED
If this post has helped you. Please take time to rate it.
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.
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
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.
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
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)
Last edited by Simply Me; Sep 17th, 2007 at 08:29 AM.
To give is always to be NOBLE...
To received is always to be BLESSED....
Each day strive to be NOBLE
Each day strive to be BLESSED
If this post has helped you. Please take time to rate it.
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."
To give is always to be NOBLE...
To received is always to be BLESSED....
Each day strive to be NOBLE
Each day strive to be BLESSED
If this post has helped you. Please take time to rate it.
Reply With Quote
Originally Posted by Simply Me
o I need to use * all the time?