|
It is not too bad for a first message. You even managed to publish formatted code, something that is rare in the VB forum for unknown reasons.
[EDIT]Breaking up your SQL statement to reduce the source width would have made things more readable though...[/EDIT]
FYI: you can still edit your message, after you have posted it. And you can reply to it if you want to add information. There is no need to start a new thread at all, keep it all together, that is easier to all of us.
|
|
|
|
|
Hello experts,
I am really stumped on this one. I have a VB6 front end tool to log in customer queris and the corresponding resolutions with start and end timestamps, customer name,email, phone number etc. Each customer interation is logged into the database via the application into the database and needs to be tagged with a unique ID.
The requirement is that I need to generate the ID every time a new case is logged in the program and save the information to a temporary table first and then push it to the main table. this is ecause there are multiple users for the application and in order to avoid dupliation of the Case ID I have used the "idReturn" sub procedure to find the last updated ID and increment it to log a new case in the table.
The problem is that the new case ID does not enerate in spite of the procedure I added , because the query in subprocedure "cmdUpdate_Click()" does not update the main table form the temporary table. Easch user would run an instance of the application from his or her own systema nd according to the a parameter set from the login form, another field called "RepID" will be updated in the table whe they log a case by clicking on the "Update" Command button after which the next Case Id should be generated. i guess I am kinda looking for a Token system to make sure that when a record has been updated with a unique case ID, the next user to press the update button to log a case should e given the next new unique ID dynamically created to store in the database.
Any suggestions? Or could someone please direct me to some examples of Multi_user database programs in VB6 with similar record lock criteria?
I can send anyone the source code if they wish to analyse it further.
The database record would look like this
ID Date Month Weekday Time Originator CustName.. etc
1 27.05.2008 December Tuesday 9:00:00AM Distributor Vicky West.etc
.
.
.
Please see the code for the form below. Sorry if it is toooooo looooooong!
Option Explicit
Public strword, intplace, idlast
Dim temp As Long
Dim currID As String
Private Sub cmdAdd_Click()
Data1.Recordset.AddNew
Dim temp As Long
date.Text = DateValue(Now)
Dim m
m = Month(Now)
monthnam.Text = MonthName(m, False)
weekd.Text = WeekdayName(Weekday(Now))
time.Text = TimeValue(Now)
starttime = Now
orig.Text = ""
Text1.Text = ""
copname.Text = ""
contactno.Text = ""
contemail.Text = ""
qrytype.Text = ""
prodcode.Text = ""
mel.Text = ""
vendcode.Text = ""
batchlot.Text = ""
saleval.Text = ""
qry.Text = ""
answer.Text = ""
sourcecontact.Text = ""
status.Text = ""
cmdAdd.Visible = False
cmdUpdate.Enabled = True
End Sub
Private Sub cmdRefresh_Click()
End Sub
Private Sub cmdUpdate_Click()
cmdUpdate.Enabled = False
cmdAdd.Visible = True
cmdAdd.Enabled = True
If status.Text = "Closed" Then
endtime = Now
End If
Data1.Recordset.Update
Dim conn As New ADODB.Connection
Dim mydb As Database
Dim qd As QueryDef
Dim CONNECT_STRING As String
CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\10.192.16.21\global\uktech\db\uktech.mdb"
Dim RS As Recordset
Set RS = New Recordset
'Generate a SQL
Dim strSQL
strSQL = "INSERT INTO main ([ID],[Date], [Month], Weekday, [Time], Originator, [First & Last Name], [Company name], [Contact No], [Contact Email], [Type of query], [Product code], MEL, [Vendor code], [Batch/Lot/Serial], [Sale value (GBP)], QUERY, ANSWER, STATUS, REPID, ContactSource, starttime, endtime, totaltime, Methcontact )"
strSQL = strSQL & "SELECT [ID],[Date], [Month], [Weekday], [Time], [Originator], [First & Last Name], [Company name], [Contact No], [Contact Email], [Type of query], [Product code], [MEL], [Vendor code], [Batch/Lot/Serial], [Sale value (GBP)], [QUERY], [ANSWER], [STATUS], [REPID], [ContactSource], [starttime], [endtime], [totaltime], [Methcontact]"
strSQL = strSQL & "FROM tempmain;"
With RS
.Open strSQL, CONNECT_STRING
End With
Set RS = NothingCall idReturn
Call idReturn
Call deleteTemptable
End Sub
Private Sub Command1_Click()
Dim strSQL1 As String
Dim key As String
key = Text2.Text
'This specifies the SQL to be executed
strSQL1 = "SELECT DISTINCT * "
strSQL1 = strSQL1 & "FROM [qa]where QUERY LIKE '%" & key & "%'"
'This specifies that the type of the ADODC is for SQL
Adodc1.CommandType = adCmdText
'This assigns the SQL result to the ADODC
Adodc1.RecordSource = strSQL1
Adodc1.Refresh
'Adodc1.Recordset.Requery
'The result from the SQL will then be used
'to populate the datagrid
Set DataGrid1.DataSource = Adodc1
DataGrid1.Columns(0).Width = 7300
DataGrid1.Columns(1).Width = 7200
DataGrid1.AllowUpdate = False
End Sub
Private Sub Command2_Click()
Adodc1.Recordset.AddNew
Adodc1.Recordset(0) = qry.Text
Adodc1.Recordset(1) = answer.Text
Adodc1.Recordset.Update
End Sub
Private Sub Command3_Click()
Form3.Show
Unload Me
End Sub
Private Sub Form_Click()
Call idReturn
End Sub
Private Sub Form_Load()
Call idReturn
cmdAdd_Click
cmdAdd.Enabled = False
DataGrid1.AllowUpdate = False
DataGrid1.Columns(0).Width = 7300
DataGrid1.Columns(0).Caption = "QUERY"
DataGrid1.Columns(1).Width = 7300
DataGrid1.Columns(1).Caption = "ANSWER"
End Sub
Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Call idReturn
End Sub
Private Sub Option1_Click()
methcontact.Text = "Call"
End Sub
Private Sub Option2_Click()
methcontact.Text = "Email"
End Sub
Private Sub orig_Click()
Combo1.Visible = True
orig.Visible = False
End Sub
Private Sub combo1_Click()
orig.Text = Combo1.Text
Combo1.Visible = False
orig.Visible = True
End Sub
Private Sub qry_GotFocus()
strword = ""
intplace = 0
End Sub
Private Sub qrytype_Click()
Combo2.Visible = True
qrytype.Visible = False
End Sub
Private Sub Combo2_click()
qrytype.Text = Combo2.Text
Combo2.Visible = False
qrytype.Visible = True
End Sub
Private Sub sourcecontact_Click()
Combo4.Visible = True
sourcecontact.Visible = False
End Sub
Private Sub status_Click()
Combo3.Visible = True
status.Visible = False
If status.Text = "Closed" Then
endtime = Now
End If
End Sub
Private Sub combo3_Click()
status.Text = Combo3.Text
Combo3.Visible = False
status.Visible = True
End Sub
Private Sub combo4_Click()
sourcecontact.Text = Combo4.Text
If Combo4.Text = "Others" Then
sourcecontact.Text = ""
MsgBox "Please Specify The Customer's Source Of Contact Information in this Text Box", , "Contact Source Details"
sourcecontact.SetFocus
End If
Combo4.Visible = False
sourcecontact.Visible = True
End Sub
Public Sub idReturn()
Dim CONNECT_STRING As String
CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\10.192.16.21\global\uktech\db\uktech.mdb"
Dim RS As Recordset
Set RS = New Recordset
'Generate a SQL
Dim strSQL As String
strSQL = "select count(*) from main "
With RS
.Open strSQL, CONNECT_STRING
End With
temp = RS(0)
id.Text = temp + 1
RS.Requery
RS.Close
Set RS = Nothing
End Sub
Private Sub deleteTemptable()
Dim conn As New ADODB.Connection
Dim mydb As Database
Dim qd As QueryDef
With conn 'this reliably opens a connection
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\10.192.16.21\global\uktech\db\uktech.mdb"
.Open
End With
Set mydb = OpenDatabase("\\10.192.16.21\global\uktech\db\uktech.mdb")
Set qd = mydb.QueryDefs("deletetemp")
Call qd.Execute
End Sub
Thanks in advance and Best Regards,
Kumar (kshincsk@gmail.com)
|
|
|
|
|
The problem with generating the case ID first is that two users can get caseIDs before either one is written to the database. Your primary key shouldn't be generated and should NOT be calculated until the record using it is written to the database. You don't checkout a case ID first, you get it last, after certain minimum data is written to the database.
Also, Access is NOT a good candidate for a multiuser database. Yes, there are people around here who will argue that point, but I've supported too many of these and had to fix too many problems with them to given these people any credit.
|
|
|
|
|
Hello Dave,
Thank you very much for your input. I tried it. First, I had the user form update the temporary table via ADODC control and then wrote a query to insert the record to the main table from the temporary table. Then I used the data1.recordset.delete method to delete the temporary table.
The problem is the data is not transferrin from the temporary table to the main table but the temporary table is deleted. The result is that there is no addition to the main table.
Could you help me with this please?
Regards,
Kumar
kshincsk@gmail.com
|
|
|
|
|
Simple, don't use a temporary table. You're just needlessly making more "round trips" to the database and doing stuff that isn't really buying you anything.
|
|
|
|
|
Thanks a lot for the advice. I just created a record off the fly and used it to retrieve the ID with minimum value and "Flag=N" meaning it has ot been used for ay update. Then , I insert the data based on the text fields in my form. then I change the "Flag" field to 'Y' to say that this particular record has been used and use the followind method. Then I used the "idreturn" method to return the lowest value of ID with Flag=N. Worked like a charm!!! Absolutely no multi-user conflicts since each user would be opening a different record at the same time.
<br />
Dim strsql<br />
Dim conn As New ADODB.Connection<br />
Dim rs As New ADODB.Recordset<br />
strsql = "SELECT * "<br />
strsql = strsql & "FROM main "<br />
strsql = strsql & "WHERE Flag ='N' AND ID=" & temp<br />
<br />
With conn 'this reliably opens a connection<br />
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\10.192.16.21\global\uktech\db\uktech.mdb"<br />
.Open<br />
<br />
End With<br />
<br />
With rs<br />
<br />
.Open strsql, conn, adOpenStatic, adLockOptimistic<br />
<br />
<br />
End With<br />
'Insert code here to update the Recordset with the txt box values<br />
.<br />
.<br />
.<br />
.<br />
.<br />
.<br />
.<br />
rs.update<br />
set rs= nothing<br />
End Sub<br />
<br />
.<br />
.<br />
.<br />
<br />
Public Sub idReturn()<br />
<br />
Dim CONNECT_STRING As String<br />
<br />
CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\10.192.16.21\global\uktech\db\uktech.mdb"<br />
<br />
Dim rs As Recordset<br />
<br />
Set rs = New Recordset<br />
<br />
'Generate a SQL<br />
Dim strsql As String<br />
<br />
strsql = "select MIN(ID) from main WHERE Flag='N'"<br />
<br />
With rs<br />
.Open strsql, CONNECT_STRING<br />
<br />
End With<br />
<br />
temp = rs(0)<br />
id.Text = temp<br />
rs.Requery<br />
rs.Close<br />
Set rs = Nothing<br />
<br />
End Sub
I got promoted to System Analyst on Friday a couple of hours after I showed my manager this program. This makes it the 7th app I have developed since I joined work October last year.
Thanks for your support.
Best regards,
Kumar
|
|
|
|
|
That's all well and good, but what if a second user runs the same query and it shows up exactly between the time user number 1 runs that query, and then runs the query to change the flag to 'N'? Trust me, it IS going to happen. Flags like this just don't work in a multiuser environment. If the operation requires more than a single step (query) to "checkout" an ID, you will end up with this problem. Maybe not today, maybe not next week, but even a year from now, it's bound to show up. The chances of it happening also increase with the inevitable increase in the number of users using this database.
|
|
|
|
|
|
You asked the same question in the c# forum you only changed the language.
The answer is the same as you got there
Don't cross post, it's considered rude.
|
|
|
|
|
Don't cross dress (unless you're into that, then it's ok) and don't cross post!
Any suggestions, ideas, or 'constructive criticism' are always welcome.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
Don't cross post. See my reply in the ASP.NET forum 
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
Hello all,
I need a small help in resolving an error
I want to create a folder with time and date
I am usingthe code,
MkDir ("c:\moved\PK3\" + str(Hour(SystemTime)) + "." + str(Minute(SystemTime)) + " " + str(Day(Systemdate)) + "_" + str(Month(Systemdate)) + "_" + str(Year(Systemdate)))
for creating a folder inside " c:\moved\PK3\ "
Most of the time its work fine. But some times an error will come as "Path/file access error".
Its not happen often . How to resolve this error?
Thanks and regrds
Denny
|
|
|
|
|
Post the full exception detail include the full message, stack trace and everything else.
From the info you've given my best guess is that you are trying to create a directory that already exists. (because you've already run the method once in this minute)
Simon
|
|
|
|
|
Ya Simon, U are right. whn i tried to run the code in minute less time only the the problem come..Thanks for your information
|
|
|
|
|
Hi.
I'm a spanish developer, i need to implement a 2D Closet designer very similar to this: http://www.ikea.com/ms/es_ES/rooms_ideas/pax_step_by_step_08/index.html
The system must do this:
1. drag items with texture, from the menu to a design area.
2. drop in the area in function of the properties, for example:
- Object_A, can drop only in the base of the closet.
- The size of the Object_B is from point A to the point of collision in the near object
- We can put the Object_C, only from top to bottom (move restricted in x direction).
3. The user can edit the position and the size of any object in any time.
Did any of you know resources (tutorials, examples, templates, libraries, plug-ins...) that can help me to finish saving time?
I have a little problem of time.
Thank you.
Hi.
I'm a spanish developer, i need to implement a 2D Closet designer very similar to this: http://www.ikea.com/ms/es_ES/rooms_ideas/pax_step_by_step_08/index.html
The system must do this:
1. drag items with texture, from the menu to a design area.
2. drop in the area in function of the properties, for example:
- Object_A, can drop only in the base of the closet.
- The size of the Object_B is from point A to the point of collision in the near object
- We can put the Object_C, only from top to bottom (move restricted in x direction).
3. The user can edit the position and the size of any object in any time.
Did any of you know resources (tutorials, examples, templates, libraries, plug-ins...) that can help me to finish saving time?
I have a little problem of time.
Thank you.
|
|
|
|
|
Hi
When i try to break point (Debug) the vb.net program. then program not stop at the break point and when we move the cursor on break point it shows the following error "Debugger problem "The breakpoint will not currently be hit. No symbols have been loaded for this document"
i also change the configuration setting to debug mode. but success is so far.
Please suggest how i solve this problem
Thanks
~Khatri Mitesh
khatrimitesh@hotmail.com
Bikaner (Rajasthan)
INDIA
|
|
|
|
|
When you are debugging a program, the debugger (visual studio) needs to locate and load the matching symbol files (the .pdb files) for the specific version being debugged.
It's possible that the .pdb files for some reason are getting mismatched. Perhaps there are some old .pdb files hanging around from an old build. Try deleting everything in the bin and obj directories of every project. This will cause all the projects to rebuild fully and regenerate their .pdb files.
If this doesn't work, check the project settings to see if it's configured to generate symbol files. (Right click on the project->Properties->Build tab->Advanced->Output:Debug Info. Set to either full (for debug builds) or pdb only (for release builds).
Simon
|
|
|
|
|
If you are still unable to get that to work, you can right click on the breakpoint, choose locations, and enable the "allow code to be different" checkbox at the bottom. This will let you debug code when the deployed PDB/DLL doesnt match your debugger copy.
|
|
|
|
|
Very nice tip astanton, didn't know that option was there.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
Thanks,
I used it all the time for debugging JS in asp.net apps until I found FF/firebug and IE8.
|
|
|
|
|
Thanks to All dear
I try everything but Success is so far.....
So Please suggest what i do.... bcoz i try all the suggesstions and i also search on google and msdn but.... no success.
So plz gave me help!
Thanx
Khatri Mitesh
~Khatri Mitesh
khatrimitesh@hotmail.com
Bikaner (Rajasthan)
INDIA
|
|
|
|
|
Hi, How can I create a move next command in web development???
Dim DS As New DataSet
TextBox1.Text = DS.Tables(0).Rows(DS.Tables(0).Rows.Count() + 1).Item("ITEM_NAME")
This code that I've used just give a move last command and i can't figure out how to the move next so that I can look for the items in my data base one-by-one.
can you give me some solution?
Thanks a lot in advance.
|
|
|
|
|
1 - we have an ASP.NET forum
2 - please tell me you don't have a textbox called textbox1 ?
3 - ADO.NET returns a full record set. You can iterate over it any way you like, but there's no movenext, like there was in ADO.
Christian Graus
Driven to the arms of OSX by Vista.
|
|
|
|
|
ohh.. thanks for d reply. And about the textbox1 in the code. I'm just testing the code that I've used and didn't bother to change the ID of the textbox. But thanks for the advice though.
Happy Holiday  .
|
|
|
|
|
Hi,
i had a crystal report.it takes time to load.so i would like to show the user that it is loading
so that like to know how can I create a progress bar that will show the progress of a loading crystal report in vb.net?
the time of to display progerss bar to just report opend when the report are open than the progres bar dosen't show please help
if have a source code so please give it please
modified on Wednesday, December 17, 2008 12:24 AM
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
