|
I am in the process of rewriting an application from VB6 to .Net2005. The application currently uses an access database and has a Order Control Table where the next order number is stored.
How can I retrieve and update the next order number in a single step (if possible) to prevent multiple users from getting the same next order number.
Thank you,
digicd1
|
|
|
|
|
Since Access doesn't support stored procedures, what you do is really just a hack.
The only way I can see this happening is creating a remote Singleton object that handles creating a new order using the latest order number. Since a single can only have one instance of the object at any one time running, all the client have to use this one instance.
But, a simpler method is to just create the order (without the order number) and let the database assign an autonumbered id to it which becomes your order number, or a part of it.
|
|
|
|
|
Simply use a table with an AutoNumber field. When you add a record to the table, you don't specify any value for the AutoNumber field, it will be automatically created. You then run the query "select @@identity" to get the number that was created for that record.
Experience is the sum of all the mistakes you have done.
|
|
|
|
|
Why not consider upsizing your Access database to a MS SQL Server Express database, then use a stored procedure?
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
<br />
<br />
#Region "Insert record into Outbound_Login_Details table with AgentId,login time and other details"<br />
Public Sub INSERT_AGENT_DETAILS()<br />
Try<br />
con.Open()<br />
Command.CommandText = "usp_Outbound_Login_Details"<br />
Command.CommandType = CommandType.StoredProcedure<br />
Command.Parameters.Add("@Agent_ID", SqlDbType.BigInt).Value = AgentID<br />
Command.Parameters.Add("@Extno", SqlDbType.VarChar, 50).Value = g_Extension_Number<br />
<br />
Command.Parameters.Add("@Outbound_Login_id", SqlDbType.BigInt)<br />
Command.Parameters("@Outbound_Login_id").Direction = ParameterDirection.Output<br />
dataReader = Command.ExecuteReader()<br />
g_OutBound_Login_ID = CInt(Command.Parameters("@OutBound_Login_ID").Value)<br />
'Command.ExecuteNonQuery()<br />
'<br />
Catch ex As Exception<br />
lblerror1.Text = ex.Message<br />
Finally<br />
dataReader.Close()<br />
con.Close()<br />
End Try<br />
<br />
End Sub<br />
#End Region<br />
<br />
<br />
****************<br />
CREATE PROCEDURE usp_Outbound_Login_Details <br />
( <br />
@Agent_ID BIGINT , <br />
@Extno VARCHAR(50),<br />
@IPAddress VARCHAR(50) = 0 <br />
)<br />
AS<br />
BEGIN<br />
SET NOCOUNT ON<br />
DECLARE @LoginDateTime AS DATETIME <br />
SET @LoginDateTime = GETDATE() <br />
INSERT INTO Outbound_Login_Details WITH (ROWLOCK) ( Agent_ID , Login_Time , ExtNo,IPAddress ) <br />
VALUES ( @Agent_ID , @LoginDateTime , @ExtNo,@IPAddress ) <br />
SELECT Outbound_Login_ID <br />
FROM Outbound_Login_Details (NOLOCK)<br />
WHERE <br />
Agent_ID = @Agent_ID AND <br />
Login_Time = @LoginDateTime AND <br />
Extno = @ExtNo<br />
END<br />
<br />
GO<br />
<br />
this vb.net code gives me <br />
ex.Message = "Procedure or function usp_Outbound_Login_Details has too many arguments specified."<br />
Please what wrong in vb.net code.....<br />
<br />
<br />
|
|
|
|
|
Your Command object is likely a field on the class. Every time you call the method INSERT_AGENT_DETAILS() you add more parameters to the Command. So, it will fail the second, and subsequent time, the method is called.
|
|
|
|
|
Simple. You didn't specify all the parameters for the SQL Procedure, plus you added one that isn't in the parameter list for it.
This is the list of parameters your SQL Proc is expecting:
@Agent_ID BIGINT ,
@Extno VARCHAR(50),
@IPAddress VARCHAR(50) = 0
You gave it these parameters:
@Agent_ID
@Extno
@Outbound_Login_id
The proc doesn't return any values in a "@Outbound_Login_Id" parameter. It returns a table with a single column called "Outbound_Login_ID".
|
|
|
|
|
hi man
Number one try to change you Procedure to be like this, you have Complicated things in your Procedure, remember the order of declaration is important in your Procedure
<br />
Create Procedure usp_OutBound_Login_Details<br />
(<br />
@Agent_ID BIGINT , <br />
@Extno VARCHAR(50),<br />
@LoginDateTime AS DATETIME,<br />
@IPAddress VARCHAR(50) = 0 <br />
)<br />
as<br />
Insert into OutBound_Login_Details With(RowLock) (Agent_ID , ExtNo,Login_Time,IPAddress)<br />
Values( @Agent_ID,@Extno,Getdate(),@IPAddress)<br />
SELECT Outbound_Login_ID <br />
FROM Outbound_Login_Details (NOLOCK)<br />
WHERE <br />
Agent_ID = @Agent_ID And<br />
Login_Time = Getdate()<br />
AND Extno = @ExtNo<br />
<br />
<br />
number two your Procedure Expect Parameters and you did not supply them from your Sub, i see that you are supplying values
but where does those values come from? do they fly into the Sub from nowhere ?
number three you have Declared a variable "@IPAddress" and initialized it to 0, and now
your procedure that you are calling from your vb code need that parameter and you did not supply it. Why dont you make an IP address
a default value of "0" and dont include it in your Procedure, see how i got rid of the @LoginDateTime variable, you are wasting resouce's.
And in your Procedure does not tell us that you want to receive an Output from this Procedure, we only see your intensions in
your vb code.You have done a lot of mistakes both in vb and in SQL. i suggest you Google and try step by step to spent time
on your Procedure and after that you will know that you want to receive an output from a Procedure, remember to put
output keyword when you declare a variable to will carry an output for you.In your vb.net code be in mind that you are supposed supply an input
make sure that you show us how you get those inputs after that you must come back and show us what you did.
Dont worry i learnt from Dave and Colin that is why am like this
hope it helps
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Hi!
Here is my code and I am getting an error message when I execute it (OleDBException - No value given one or more parameters). I couldn't figure out what is causing the error because all the given parameters are correct. However when I remove the last line from SQL string it is working fine. I assumed that I can use more than one condition after WHERE caluse in the string but it seems that it only accepts only one.
Thanks.
Juneyt
<br />
Dim mySelectQuery As String = "UPDATE " & WhatTable & " SET "<br />
<br />
mySelectQuery += "VMeaning=" & "'" & Replace(TextBox1.Text, "'", Chr(34)) & "',"<br />
mySelectQuery += "Description=" & "'" & Replace(TextBox2.Text, "'", Chr(34)) & "',"<br />
mySelectQuery += "Thoughts=" & "'" & Replace(TextBox3.Text, "'", Chr(34)) & "'"<br />
mySelectQuery += " WHERE OderNumber=" & SorahNumber <br />
<br />
'When I cancel the line below, it is working fine<br />
mySelectQuery += " AND VNumber=" & CInt(ComboBox3.SelectedItem) <br />
<br />
<br />
<br />
Dim myconnection As New OleDbConnection(SQLConnStr)<br />
Dim myCommand As New OleDbCommand(mySelectQuery, myconnection)<br />
<br />
myconnection.Open()<br />
<br />
myCommand.ExecuteNonQuery() 'error occurs here<br />
myconnection.Close()<br />
What a curious mind needs to discover knowledge is noting else than a pin-hole.
|
|
|
|
|
HI,
It seems to me that the OleDbProvider thinks that there is a parameter in your SQL command. Do any of your inputs contain a question mark, which denotes a parameter in OleDb? I would suggest you to use a parameterized SQL statement then pass all your textbox values as parameters using OleDbParameter. Below is the code snippet for the same:
-------------------------------------------------------------------------
BEGIN CODE
Dim strEditCommand As String = "UPDATE " & WhatTable & " SET "
strEditCommand += " VMeaning = ?, Description = ?, Thoughts=?, "
strEditCommand += " WHERE OderNumber=? AND VNumber= ? "
Using con As New OleDbConnection(SQLConnStr)
Using cmd As New OleDbCommand()
cmd.Connection.Open()
cmd.CommandText = strEditCommand
cmd.Parameters.Add(New OleDbParameter("VMeaning", TextBox1.Text))
cmd.Parameters.Add(New OleDbParameter("Description", TextBox2.Text))
cmd.Parameters.Add(New OleDbParameter("Thoughts", TextBox3.Text))
con.Open()
cmd.ExecuteNonQuery()
End Using
Con.Close()
End Using
END CODE
-------------------------------------------------------------------------
I hope this helps  .
Regards,
John Adams
ComponentOne LLC
|
|
|
|
|
Thanks but I have recieced the same error message! Second condition after WHERE clause is causing the problem but no way to understand why?..
What a curious mind needs to discover knowledge is noting else than a pin-hole.
|
|
|
|
|
If you pass an improper parameter, you can get the same error message. For instance, passing in a String where the parameter expects an Integer, or the other way around.
|
|
|
|
|
 Dear Members,
I am trying to convert some Vbscripts to Vb.net GUI. Here I found a Script like this
Here I had a strange Condition
Dim strvar as string
Dim strprog as string
Dim AdvBinCnt as integer
strvar = "32"
AdvBinCnt = 1
Select Case strvar And 2 ^ AdvBinCnt
Case 1
strprog = "silent & Case Values is: " & (strvar And 2 ^ AdvBinCnt)
MsgBox(strProg)
Case 2
strprog = "Reboot & Case Values is: " & (strvar And 2 ^ AdvBinCnt)
MsgBox(strProg)
Case 4
strprog = "No Reboot & Case Values is: " & (strvar And 2 ^ AdvBinCnt)
MsgBox(strProg)
End Select
I have just copied and pasted same code from VBScript to VB.net Code Behind.
However I could not figure out How above Select Case Expression ( Select Case strvar And 2 ^ AdvBinCnt) is working.
Would some one Please show some light on how this Case Condition (strvar And 2 ^ AdvBinCnt)is working.
Example: This Expression evalutes as select case "32" And 2^1
Note: the Code is working fine as it is and total programs are working depending on this values returned from select Case. I have to send this to my pm asap. however I did n't know how the condition works.
Would some please expalin how it is working or pleae direct me to a web site where I could get some explanation on similar above condition.
Thanks in Advance for your Help.
Thank you,
Vamsy.
Vamsy Krishna
|
|
|
|
|
2 ^ 1 is 2. Doing 2 ^ x gives you the binary number 1000 where there are x 0's after the 1. So, the AND operator returns true if there are any bits set in common between the two numbers, 2 ^ x allows you to specify that the AND operator returns true only if bit number x is set in strvar.
I suspect that because VB is utter crap, strvar is being magically converted into an int for the purpose of this test.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Actually, it's not the string that get's converted to a number. It's the other way around - the (2 ^ AdvBinCnt) is an integer that gets converted to a string. I have no idea why this code was written this way. It's simple math - you don't need the strings.
The expression (strvar And 2 ^ AdvBinCnt), where strvar is "32", won't return the value 32 until AdvBinCnt is 5.
|
|
|
|
|
Ah - well, that's even worse, it ends up being a string comparison. See why I hate VB ?
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
I hate the implied stuff too. I always turn on all of the Strict stuff to eliminate most of it.
OT: I'm not going to make it to the MVP Summit. My wife will be 8 months pregnant by then! 
|
|
|
|
|
Hi All
I am using a WebBrowser control to navigate to sites in my VB.Net application. After navigating I want to Navigate to some of the links in it depending on some conditions. Can anybody advise how can I collect the links contained in the WebBrowser Document and how can I Raise a click event for a collected link so that it automatically navigates to the link.
I have written the code as bellow :
Dim link1 As mshtml.HTMLLinkElement
link1 = WebBrowser1.Document.Links(10)
Now How can I click the link link1 to navigate to the link.
Please Help. Thanks in advance.
Sekhar
|
|
|
|
|
Did you try using WebBrowser1.Navigate ?
"Legacy code" often differs from its suggested alternative by actually working and scaling.
—Bjarne Stroustrup
|
|
|
|
|
Hi Nilesh
I had already used that before I get this problem. I am able to navigate to url addresses using
WebBrowser1.Url = New System.Uri(txtURL.Text)
and after navigating to the site in the txtURL i found there are several links and several buttons. I want to know how I can click on a link or a button on the page so that I can actually simulate the process of someone sitting and clicking on the links.
Thanks for your response.
If anybody can help me out in finding a solution to this problem then it will help me a lot.
Thanks in advance.
Sekhar
|
|
|
|
|
Hi All
I am using a WebBrowser control to navigate to sites in my VB.Net application. After navigating I want to Navigate to some of the links in it depending on some conditions. Can anybody advise how can I collect the links contained in the WebBrowser Document and how can I Raise a click event for a collected link so that it automatically navigates to the link.
I have written the code as bellow :
Dim link1 As mshtml.HTMLLinkElement
link1 = WebBrowser1.Document.Links(10)
Now How can I click the link link1 to navigate to the link.
Please Help. Thanks in advance.
|
|
|
|
|
Hi,
On the Webrowser's click event try
link1.Navigate(URLstring).
Hope this helps.
Regards,
John Adams
ComponentOne LLC
|
|
|
|
|
Thanks Adams for your response.
My problem is when I assign the link in the line
link1 = WebBrowser1.Document.link(10)
it doesn't produce any output and the immediate line after this line also doen't gets executed. I think some errors occur in this line and the compiler is not able to track it. for which the focus goes to the exe after this line gets executed. If you can help me out in solving the problem, it will be a great help.
Thanks in advance.
Sekhar 
|
|
|
|
|
Hi All,
I have Process ID(PID).
Consider, Iam opening Word Document(a1.doc), It has a PID called 2010.
Then, when i open another file (b1.doc) using the same window again, it shows same pid for both.
How can i get differentiate both the files using the PID?.
Thanks & Regards
Kumaran
|
|
|
|
|
You can't. They're using the same process. A single instance of Word can open multiple documents at the same time, so there's only going to be one process ID.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
