-
Sep 20th, 2007, 09:56 AM
#1
Thread Starter
PowerPoster
[RESOLVED] Code needs to be fixed.[Desperate]
Here's my code:
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
'SEQ = rsStock.Fields("Sequence")
oConn.BeginTrans
sSQL = "SELECT IsNull(Max(pSequence),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 = "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
1. Check for the product ID if already exists.
2. If No, then it will save record in tblStockMasterfFile (Product ID, Product Name, Unit, Re-Order Point) and tblStockTransFile (Product ID, Entry Date, Sequence, Purchase Price, Quantity, Reason). Note: the sequence value should be 1.
3. If Yes, then it checks for the Entry Date.
4. If Entry Date is the same then it saves the product and the sequence value is incremented by 1.
5. If Entry Date is not the same then it saves the product and the sequence value is 1.
I am having trouble with SPROC as I am very very new to it especially calling the SPROC in VB so as much as possible if I can do away with it for the time being then its better for me.
Any help is much appreciated and thanks in advance.
Last edited by Simply Me; Sep 21st, 2007 at 08:32 PM.
-
Sep 20th, 2007, 01:14 PM
#2
Re: Code needs to be fixed.
First, don't use thiswith a SELECT clause. You are asking for a result set of records that match your SELECT criteria and those records will be stored in a recordset. Do something like
Code:
Dim rs As ADODB.Recordset
sSQL = "SELECT IsNull(Max(pSequence),0)+1 "
sSQL = sSQL & "FROM tblStockTransFile "
sSQL = sSQL & "WHERE ProductID='" & txtProdID.Text & "' AND EntryDate='" & DTEntryDate.Value & "')"
'Debug.Print sSQL
Set rs = New ADODB.Recordset
rs.Open sSQL, oConn
-
Sep 20th, 2007, 06:43 PM
#3
Thread Starter
PowerPoster
Re: Code needs to be fixed.
Thanks Hack! I'll try this. I'll be back for a result.
-
Sep 20th, 2007, 11:20 PM
#4
Thread Starter
PowerPoster
Re: Code needs to be fixed.
I received an error because I need to save the sequence value in the table becuase sequence is a part of the composite key so it should not be null. so in my code (in bold letter) I added pSequence.
Code:
sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason,pSequence)" & _
"VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & "','" & txtProdPurchasePrice.Text & _
"','" & txtProdQTY.Text & " ','In',What should I put here?)"
'Debug.Print sSQL
oConn.Execute sSQL
-
Sep 21st, 2007, 08:28 PM
#5
Thread Starter
PowerPoster
Re: Code needs to be fixed.
Hack or Anybody can help me out here please?
-
Sep 21st, 2007, 10:40 PM
#6
Re: Code needs to be fixed.[Desperate]
The highest sequence no bumped by one...
-
Sep 22nd, 2007, 07:02 AM
#7
Thread Starter
PowerPoster
Re: Code needs to be fixed.[Desperate]
Originally Posted by randem
The highest sequence no bumped by one...
What do you mean by that? Any code please?
-
Sep 22nd, 2007, 02:29 PM
#8
Re: Code needs to be fixed.[Desperate]
Well, since you are using sequence numbers I assume they are unique and ascending, so you would need to get the highest sequence number you last used then increment it by one to add a new record to your table.
-
Sep 23rd, 2007, 05:45 AM
#9
Thread Starter
PowerPoster
Re: Code needs to be fixed.[Desperate]
Yes that's what Im trying to achieve here. please refer to post #1.
-
Sep 23rd, 2007, 07:35 AM
#10
Thread Starter
PowerPoster
Re: Code needs to be fixed.[Desperate]
someone here who can help please? I really need this one to be fix so that I can go on with what I am doing. Im stuck in this part. What I want to achieve is listed at the bottom of my first post.
-
Sep 23rd, 2007, 08:54 AM
#11
Thread Starter
PowerPoster
Re: Code needs to be fixed.[Desperate]
i tried doing using the code below
Code:
Private Sub cmdSave_Click()
If CheckNullValue = False Then Exit Sub
Call ReplaceQuotation(txtProdName)
On Error GoTo ErrHandler
Dim rs As ADODB.Recordset
Dim SEQ As Integer
If Checker = True Then 'Product ID already Exist then save in tblStocktransFile Only
oConn.BeginTrans
sSQL = "SELECT MAX(pSequence) FROM tblStockTransFile " & _
"WHERE ProductID='" & txtProdID.Text & _
"' AND EntryDate='" & DTEntryDate.Value & "'"
If rsStock.State = adStateOpen Then rsStock.Close
rsStock.Open sSQL, oConn, adOpenStatic, adLockOptimistic
If Not IsNull(rsStock(0)) Then
SEQ = rsStock(0) + 1
Else
SEQ = "1"
End If
rsStock.Close
Set rsStock = Nothing
sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice,Quantity, Reason,pSequence)" & _
"VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & _
"','" & txtProdPurchasePrice.Text & "','" & txtProdQTY.Text & "','In',SEQ)"
'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 = "SELECT MAX(pSequence) FROM tblStockTransFile " & _
"WHERE ProductID='" & txtProdID.Text & _
"' AND EntryDate='" & DTEntryDate.Value & "'"
If rsStock.State = adStateOpen Then rsStock.Close
rsStock.Open sSQL, oConn, adOpenStatic, adLockOptimistic
If Not IsNull(rsStock(0)) Then
SEQ = rsStock(0) + 1
Else
SEQ = "1"
End If
rsStock.Close
Set rsStock = Nothing
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,pSequence)" & _
"VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & "','" & txtProdPurchasePrice.Text & _
"','" & txtProdQTY.Text & " ','In',SEQ)"
'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
the name "SEQ" is not permitted in this context. Valid Expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
-
Sep 23rd, 2007, 10:08 AM
#12
Frenzied Member
Re: Code needs to be fixed.[Desperate]
SEQ is Integer, change
If Not IsNull(rsStock(0)) Then
SEQ = rsStock(0) + 1
Else
SEQ = "1"
End If
to
If Not IsNull(rsStock(0)) Then
SEQ = cInt(rsStock(0)) + 1
Else
SEQ = 1
End If
EDIT : Cast before adding
-
Sep 23rd, 2007, 10:33 AM
#13
Thread Starter
PowerPoster
Re: Code needs to be fixed.[Desperate]
I tried it but the error is still the same
-
Sep 23rd, 2007, 10:34 AM
#14
Thread Starter
PowerPoster
Re: Code needs to be fixed.[Desperate]
what do you mean by "Cast before Adding"?
-
Sep 23rd, 2007, 10:41 AM
#15
Frenzied Member
Re: Code needs to be fixed.[Desperate]
Originally Posted by Simply Me
what do you mean by "Cast before Adding"?
Cint(), cast the value to Integer before adding.
just a precaution. Not explicitly needed. if the pSequence is Integer in the DB, may not need it because the recordset would cast it.
this is the error
Code:
& txtProdQTY.Text & "','In',SEQ)"
shouldnt it be
Code:
& txtProdQTY.Text & "','In'," & SEQ & ")"
?
-
Sep 23rd, 2007, 11:01 AM
#16
Thread Starter
PowerPoster
Re: Code needs to be fixed.[Desperate]
Oh noohhhh. I've been doing this for almost two weeks now... Yes that's the error.
oooppppsss. there's another error "No Transaction is Active" and is pointing to oConn.RollbackTrans.
-
Sep 23rd, 2007, 11:08 AM
#17
Frenzied Member
Re: Code needs to be fixed.[Desperate]
If its says not txn is active, the error occured before a begintrans.
So comment the On Error Goto and check where the error is occuring, or put a msgbox for err.description before the rollback.
-
Sep 23rd, 2007, 11:24 AM
#18
Thread Starter
PowerPoster
Re: Code needs to be fixed.[Desperate]
ok the error now after commenting On Error Goto is "Object Variable or With block variable not set" and is pointing to rsStock.Requery of the code below
Code:
'TODO: Populate the ListView control
Sub FillListView(lstStockMasterFile As ListView, rsStock As ADODB.Recordset)
Dim lst As ListItem
rsStock.Requery
lstStockMasterFile.ListItems.Clear
While Not rsStock.EOF
Set lst = lstStockMasterFile.ListItems.Add(, , rsStock("ProductID")) 'DISPLY IN LISTVIEW
lst.SubItems(1) = rsStock.Fields("ProductName")
lst.SubItems(2) = rsStock.Fields("Unit")
lst.SubItems(3) = Format$(rsStock.Fields("PurchasePrice"), "###,###,##0.00")
lst.SubItems(4) = rsStock.Fields("Quantity")
lst.SubItems(5) = rsStock.Fields("Reason")
lst.SubItems(6) = rsStock.Fields("EntryDate")
lst.SubItems(7) = rsStock.Fields("ReOrderPoint")
rsStock.MoveNext
Set lst = Nothing 'Destroy the list
Wend
End Sub 'FillListView
-
Sep 23rd, 2007, 12:03 PM
#19
Frenzied Member
Re: Code needs to be fixed.[Desperate]
Thats becuse you close ther recordset before you call for that function
in Private Sub cmdSave_Click()
Code:
rsStock.Close
Set rsStock = Nothing
should come after you call Call FillListView(lstStockMasterFile, rsStock)
you cant close the Recordset if you are using it in that function.
-
Sep 23rd, 2007, 09:21 PM
#20
Thread Starter
PowerPoster
Re: Code needs to be fixed.[Desperate]
I already commented that line but still I get the same error message. I noticed that when I clicked debug and the error points to rsStock.Requery and then I stop the program and re-run it again, the record is already saved in the table.
-
Sep 23rd, 2007, 10:14 PM
#21
Frenzied Member
Re: Code needs to be fixed.[Desperate]
There are two occurances of that code in your Save function. Can you post the changed save function again?
If it says "Object Variable or With block variable not set" , probably becaus the object is not initialized. Check the call stack also to find the sequence of function calls.
-
Sep 23rd, 2007, 11:41 PM
#22
Thread Starter
PowerPoster
Re: Code needs to be fixed.[Desperate]
No, the error is "No Transaction is active" this time. Here's the edited code
Code:
Private Sub cmdSave_Click()
Dim SEQ As Integer
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
'==========================THIS IS WORKING ALREADY==================================
sSQL = "SELECT MAX(pSequence) FROM tblStockTransFile " & _
"WHERE ProductID='" & txtProdID.Text & _
"' AND EntryDate='" & DTEntryDate.Value & "'"
If rsStock.State = adStateOpen Then rsStock.Close
rsStock.Open sSQL, oConn, adOpenStatic, adLockOptimistic
If Not IsNull(rsStock(0)) Then
SEQ = CInt(rsStock(0)) + 1
Else
SEQ = 1
End If
' rsStock.Close
' Set rsStock = Nothing
'==========================THIS IS WORKING ALREADY==================================
sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice,Quantity, Reason,pSequence)" & _
"VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & _
"','" & txtProdPurchasePrice.Text & "','" & txtProdQTY.Text & "','In'," & SEQ & ")"
'Debug.Print sSQL
oConn.Execute sSQL
oConn.CommitTrans
Else 'Product ID does not Exist so save in both tblStockMasterFile and tblStockTransFile
oConn.BeginTrans
'==========================THIS IS WORKING ALREADY==================================
sSQL = "SELECT MAX(pSequence) FROM tblStockTransFile " & _
"WHERE ProductID='" & txtProdID.Text & _
"' AND EntryDate='" & DTEntryDate.Value & "'"
If rsStock.State = adStateOpen Then rsStock.Close
rsStock.Open sSQL, oConn, adOpenStatic, adLockOptimistic
If Not IsNull(rsStock(0)) Then
SEQ = rsStock(0) + 1
Else
SEQ = "1"
End If
' rsStock.Close
' Set rsStock = Nothing
'==========================THIS IS WORKING ALREADY==================================
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,pSequence)" & _
"VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & "','" & txtProdPurchasePrice.Text & _
"','" & txtProdQTY.Text & " ','In'," & SEQ & ")"
'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
-
Sep 23rd, 2007, 11:46 PM
#23
Re: Code needs to be fixed.[Desperate]
Quesition... Why would you use transactions on one insert statement? Transaction are meant for multiple Inserts/Delete/Update queries that if one doesn't complete you can roll the others back. Having a transaction on other queries is pointless...
Last edited by randem; Sep 24th, 2007 at 12:27 AM.
-
Sep 23rd, 2007, 11:48 PM
#24
Re: Code needs to be fixed.[Desperate]
It is also pointless to do a transaction on a select statement...
-
Sep 23rd, 2007, 11:52 PM
#25
Re: Code needs to be fixed.[Desperate]
Also if you are using Bound Data Controls... You could be at this for years... No one knows what goes on behind the scenes in those things...
-
Sep 24th, 2007, 12:25 AM
#26
Frenzied Member
Re: Code needs to be fixed.[Desperate]
Back to Square one, comment the error handler and find where the error occurs. the TXn error could occur in Committrans and Rollbacktrans. try to find where its generated.
And randem is right, no point in having txns for those querries. Are you using bounded controls?
-
Sep 24th, 2007, 02:36 AM
#27
Thread Starter
PowerPoster
Re: Code needs to be fixed.[Desperate]
Yes, randem is correct. I'll remove the transaction in the IF part.
No, I'm not using bound controls. Im using ADODB
-
Sep 24th, 2007, 04:08 AM
#28
Thread Starter
PowerPoster
Re: Code needs to be fixed.[Desperate]
When I commented the On Error GoTo ErrHandler the error message now is "item cannot be found in the collection corresponding to the requested name or ordinal" when I end the program and run it again the record is now displayed in the listview, meaning the record is saved. Why is it behaving like that? IM SO CONFUSE.
Below is my whole code:
vb Code:
Option Explicit
Dim rsStock As ADODB.Recordset
Dim rsTempStock As ADODB.Recordset
Dim CMD As ADODB.Command
Dim sSQL$, sSQL1$
Private Sub cmdSave_Click()
Dim SEQ As Integer
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
'==========================THIS IS WORKING ALREADY==================================
sSQL = "SELECT MAX(pSequence) FROM tblStockTransFile " & _
"WHERE ProductID='" & txtProdID.Text & _
"' AND EntryDate='" & DTEntryDate.Value & "'"
If rsStock.State = adStateOpen Then rsStock.Close
rsStock.Open sSQL, oConn, adOpenStatic, adLockOptimistic
If Not IsNull(rsStock(0)) Then
SEQ = CInt(rsStock(0)) + 1
Else
SEQ = 1
End If
' rsStock.Close
' Set rsStock = Nothing
'==========================THIS IS WORKING ALREADY==================================
sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice,Quantity, Reason,pSequence)" & _
"VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & _
"','" & txtProdPurchasePrice.Text & "','" & txtProdQTY.Text & "','In'," & SEQ & ")"
'Debug.Print sSQL
oConn.Execute sSQL
Else 'Product ID does not Exist so save in both tblStockMasterFile and tblStockTransFile
'==========================THIS IS WORKING ALREADY==================================
sSQL = "SELECT MAX(pSequence) FROM tblStockTransFile " & _
"WHERE ProductID='" & txtProdID.Text & _
"' AND EntryDate='" & DTEntryDate.Value & "'"
If rsStock.State = adStateOpen Then rsStock.Close
rsStock.Open sSQL, oConn, adOpenStatic, adLockOptimistic
If Not IsNull(rsStock(0)) Then
SEQ = rsStock(0) + 1
Else
SEQ = "1"
End If
' rsStock.Close
' Set rsStock = Nothing
'==========================THIS IS WORKING ALREADY==================================
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,pSequence)" & _
"VALUES('" & txtProdID.Text & "','" & DTEntryDate.Value & "','" & txtProdPurchasePrice.Text & _
"','" & txtProdQTY.Text & " ','In'," & SEQ & ")"
'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
Private Sub cmdUpdate_Click()
If CheckNullValue = False Then Exit Sub
Call ReplaceQuotation(txtProdName)
'TODO: check whether the PK (Product) already exist or not.
If iTempProdID <> txtProdID.Text Then
'check whether user has change the PK otherwise it'll not be saved
If Checker = True Then
Call msgExist("Product")
txtProdID.SetFocus
' Start highlight before first character.
txtProdID.SelStart = 0
' Highlight to end of text.
txtProdID.SelLength = Len(txtProdID.Text)
Exit Sub
End If 'Checker
End If 'iTempCName
If (MsgBox("Are you sure?", vbInformation + vbYesNo, ProgName) = vbYes) Then
On Error GoTo ErrHandler
sSQL = "UPDATE tblStockMasterFile " & _
"SET ProductID= '" & txtProdID.Text & "'," & _
"ProductName = '" & txtProdName.Text & "'," & _
"Unit= '" & txtProdUnit.Text & "'," & _
"ReOrderPoint='" & txtProdReOrderPoint.Text & "'" & _
"WHERE ProductID = '" & iTempProdID & "'"
oConn.Execute sSQL
'Debug.Print sSQL
Call FillListView(lstStockMasterFile, rsStock)
Call ClearFunction(frmStockMasterFile, "TextBox")
cmdUpdate.Enabled = False
cmdSave.Enabled = True
lstStockMasterFile.Enabled = True
End If
Exit Sub
ErrHandler:
Call msgError(Err)
End Sub 'cmdUpdate_Click
Last edited by Simply Me; Sep 24th, 2007 at 04:28 AM.
-
Sep 24th, 2007, 04:28 AM
#29
Thread Starter
PowerPoster
Re: Code needs to be fixed.[Desperate]
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
'TODO : return true if all required field has been filled
Private Function CheckNullValue() As Boolean
CheckNullValue = False
If Len(Trim$(txtProdID.Text)) = 0 Then
Call msgMustBeFilled("Product ID")
txtProdID.SetFocus
Exit Function
ElseIf Len(Trim$(txtProdName.Text)) = 0 Then
Call msgMustBeFilled("Product Name")
txtProdName.SetFocus
Exit Function
ElseIf Len(Trim$(txtProdUnit.Text)) = 0 Then
Call msgMustBeFilled("Product Unit")
txtProdUnit.SetFocus
Exit Function
ElseIf Len(Trim$(txtProdPurchasePrice.Text)) = 0 Then
Call msgMustBeFilled("Product Purchase Price")
txtProdPurchasePrice.SetFocus
Exit Function
ElseIf Len(Trim$(txtProdQTY.Text)) = 0 Then
Call msgMustBeFilled("Product Quantity")
txtProdQTY.SetFocus
Exit Function
ElseIf Len(Trim$(txtProdReOrderPoint.Text)) = 0 Then
Call msgMustBeFilled("Product Re-Order Point")
txtProdReOrderPoint.SetFocus
Exit Function
End If
'If cmdAddCategory.Enabled = True Then Call ReplaceQuotation(txtCategoryName)
CheckNullValue = True
End Function 'CheckNullValue
Private Sub Form_Load()
Call openConnection
Me.Left = LeftPos - 400
Me.Top = TopPos - 200
Set rsStock = New ADODB.Recordset
Set rsTempStock = New ADODB.Recordset
Set CMD = New ADODB.Command
'Calling myProcedure
With CMD
.ActiveConnection = oConn
.CommandText = "[myProcedure]"
.CommandType = adCmdStoredProc
Set rsStock = .Execute()
Set rsTempStock = .Execute()
End With
Set CMD = Nothing
DTEntryDate.Value = Format$(Now, "mm/dd/yyyy") 'Display the Current Date
Call FillListView(lstStockMasterFile, rsStock)
End Sub 'Form_Load
'TODO: Populate the ListView control
Sub FillListView(lstStockMasterFile As ListView, rsStock As ADODB.Recordset)
Dim lst As ListItem
rsStock.Requery
lstStockMasterFile.ListItems.Clear
While Not rsStock.EOF
Set lst = lstStockMasterFile.ListItems.Add(, , rsStock("ProductID")) 'DISPLY IN LISTVIEW
lst.SubItems(1) = rsStock.Fields("ProductName")
lst.SubItems(2) = rsStock.Fields("Unit")
lst.SubItems(3) = Format$(rsStock.Fields("PurchasePrice"), "###,###,##0.00")
lst.SubItems(4) = rsStock.Fields("Quantity")
lst.SubItems(5) = rsStock.Fields("Reason")
lst.SubItems(6) = rsStock.Fields("EntryDate")
lst.SubItems(7) = rsStock.Fields("ReOrderPoint")
rsStock.MoveNext
Set lst = Nothing 'Destroy the list
Wend
End Sub 'FillListView
Private Sub Form_Unload(Cancel As Integer)
If rsStock.State = adStateOpen Then rsStock.Close
Set rsStock = Nothing
If rsTempStock.State = adStateOpen Then rsTempStock.Close
Set rsTempStock = Nothing
End Sub 'Form_Unload
Private Sub lstStockMasterFile_DblClick()
If lstStockMasterFile.ListItems.Count <> 0 Then
sSQL = "SELECT ProductID, ProductName " & _
"FROM tblStockMasterFile " & _
"WHERE ProductID = ' & lstStockMasterFile.ListItems.Item(lstStockMasterFile.SelectedItem.Index).Text & '"
edwin = lstStockMasterFile.SelectedItem.Index
txtProdID.Text = lstStockMasterFile.ListItems.Item(edwin).Text
iTempProdID = txtProdID.Text
txtProdName.Text = lstStockMasterFile.ListItems.Item(edwin).SubItems(1)
txtProdUnit.Text = lstStockMasterFile.ListItems.Item(edwin).SubItems(2)
txtProdPurchasePrice.Text = lstStockMasterFile.ListItems.Item(edwin).SubItems(3)
txtProdQTY.Text = lstStockMasterFile.ListItems.Item(edwin).SubItems(4)
'txtReason.Text = lstStockMasterFile.ListItems.Item(edwin).SubItems(5)
DTEntryDate.Value = lstStockMasterFile.ListItems.Item(edwin).SubItems(6)
txtProdReOrderPoint.Text = lstStockMasterFile.ListItems.Item(edwin).SubItems(7)
lstStockMasterFile.ListItems.Remove (edwin)
lstStockMasterFile.Enabled = False
cmdSave.Enabled = False
cmdUpdate.Enabled = True
txtProdSearch.Text = vbNullString
End If
End Sub 'lstStockMasterFile_DblClick
Private Sub lstStockMasterFile_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
Case vbKeyDelete
If lstStockMasterFile.ListItems.Count <> 0 Then
edwin = lstStockMasterFile.SelectedItem.Index
sSQL = "DELETE FROM tblStockMasterFile " & _
"WHERE ProductID= '" & (lstStockMasterFile.ListItems.Item(edwin).Text) & "'"
'Debug.Print sSQL
If (MsgBox(lstStockMasterFile.SelectedItem.SubItems(1) & " will permanently be deleted!" & Chr(13) & " Delete anyway?", vbCritical + vbYesNo + vbDefaultButton2, ProgName) = vbNo) Then
Exit Sub
Else
oConn.Execute sSQL
lstStockMasterFile.ListItems.Remove (edwin)
End If
End If 'lstStockMasterFile.ListItems.Count
lstStockMasterFile.Refresh
End Select 'Case KeyCode
End Sub 'lstStockMasterFile_KeyDown
Function doQuery(ByVal sCriteria As String, ByVal sfieldName As String) As String
If InStr(sCriteria, "'") <> 0 Then
sCriteria = Replace(sCriteria, "'", "''")
End If
If InStr(sCriteria, "[") <> 0 Then 'Added 5-14-07
sCriteria = Replace(sCriteria, "[", "[[]")
End If
' doQuery = "SELECT ProductID,ProductName,Unit,ReOrderPoint FROM tblStockMasterFile " & _
"WHERE " & sfieldName & " LIKE '" & sCriteria & "%'"
doQuery = "SELECT tblStockMasterFile.ProductID, tblStockMasterFile.ProductName, " & _
"tblStockMasterFile.Unit, tblStockMasterFile.ReOrderPoint, " & _
"tblStockTransFile.EntryDate, tblStockTransFile.pSequence, " & _
"tblStockTransFile.PurchasePrice, tblStockTransFile.Quantity, " & _
"tblStockTransFile.Reason " & _
"FROM tblStockMasterFile INNER JOIN tblStockTransFile ON " & _
"tblStockMasterFile.ProductID = tblStockTransFile.ProductID " & _
"WHERE " & sfieldName & " LIKE '" & sCriteria & "%'"
End Function 'doQuery
-
Sep 24th, 2007, 04:29 AM
#30
Thread Starter
PowerPoster
Re: Code needs to be fixed.[Desperate]
Here is my Procedure
vb Code:
Use MasterFile
GO
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.pSequence,
tblStockTransFile.PurchasePrice,
tblStockTransFile.Quantity,
tblStockTransFile.Reason
FROM tblStockMasterFile INNER JOIN tblStockTransFile ON
tblStockMasterFile.ProductID = tblStockTransFile.ProductID
-
Sep 24th, 2007, 05:02 AM
#31
Frenzied Member
Re: Code needs to be fixed.[Desperate]
OK , this is the same error in your other thread. So I think its the same problem. Your rsStock might be changed somewhere during the code.
Best is to put a breakpoint in the start of the save function , and step through the code execution (pressing F8). And try to find where the error is occuring.
-
Sep 24th, 2007, 05:08 AM
#32
Frenzied Member
Re: Code needs to be fixed.[Desperate]
Why are you using only one recordset for all your results? (rsStock) If your are using one recordset, you have to make sure that its holding the correct records everywhere you are using it. I think its very hard to maintain like that. Better if you use another recordset for your other needs.
I dont see any harm using many recordsets as long as you close them and destroy properly.
-
Sep 24th, 2007, 07:12 AM
#33
Thread Starter
PowerPoster
Re: Code needs to be fixed.[Desperate]
its working now, I used another recordset for SELECT MAX(pSequence)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|