|
I installed Oracle 9.2.0.1.0 client on my machine and wrote the following piece of code to call a stored function from my asp.net application
Imports System
Imports System.Data.OracleClient
Imports System.Data.Common
Imports System.Configuration
Imports System.Data
Dim connection As OracleConnection = Nothing
connection = New OracleConnection(ConfigurationManager.ConnectionStrings("Conn1").ToString())
Dim command As OracleCommand = New OracleCommand("dtx_ivr_sf", connection)
'command.Connection = connection
'command.CommandText = "dtx_ivr_sf(P_parcel_id)"
command.CommandType = CommandType.StoredProcedure
Dim RetVal As New OracleParameter("RetVal", OracleType.VarChar, 200)
Dim P_parcel_id As New OracleParameter("P_parcel_id", OracleType.VarChar, 10)
RetVal.Direction = ParameterDirection.ReturnValue
P_parcel_id.Direction = ParameterDirection.Input
P_parcel_id.Value = "0000006611"
command.Parameters.Add(RetVal)
command.Parameters.Add(P_parcel_id)
connection.Open()
command.ExecuteNonQuery()
Dim str As String = RetVal.Value.ToString()
If Not String.IsNullOrEmpty(str) Then
m_boo_IsDelinquent = True
End If
But I am getting an exception on "command.ExecuteNonQuery()" as
"ORA-06502: PL/SQL: numeric or value error: character string buffer too smal"
Do I need to install odp.net for this? Please help. I know my Oracle stored function is working fine, as I can run it from Oracle forms. Any help is appreciated
|
|
|
|
|
At a guess I think your return parameter is probably too small
ljammala wrote: Dim P_parcel_id As New OracleParameter("P_parcel_id", OracleType.VarChar, 10)
try increasing the 10 and see what result you get.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I still get the same error increasing size to 100.
|
|
|
|
|
I increased the size of my output parameter "RetVal" and that solved the problem. Thank you very much 
|
|
|
|
|
I am wondering whats wrong with my code as i defined a button to click, search and copy the results but the data would not be inputted to the next datarow. It just keeps overridding the first datarow.
<br />
Button_click<br />
<br />
Dim dr As DataRow<br />
Dim dt As New DataTable<br />
<br />
dt = objDataSet.Tables(0)<br />
<br />
<br />
Dim dt2 As New DataTable<br />
<br />
<br />
For Each dr In dt.Rows<br />
For i As Integer = 0 To dt.Columns.Count - 1<br />
<br />
If dr(i).ToString = TextBox1.Text Then<br />
<br />
Dim arData() As Object = {dr(0), dr(1), dr(2)} <br />
dt2.Columns.Add("IC Roll")<br />
dt2.Columns.Add("F2")<br />
dt2.Columns.Add("F3")<br />
<br />
dr = dt2.NewRow()<br />
dt2.Rows.Add(arData)<br />
<br />
Exit For<br />
End If<br />
Next<br />
DataGrid2.DataSource = dt2<br />
Next<br />
|
|
|
|
|
You are really need to pay attention to the order of execution (what executes next)
Dim dr As DataRow
#1) you are creating a new instance and then turning around and replacing it
Dim dt As New DataTable
dt = objDataSet.Tables(0)
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Dim dt2 As New DataTable
For Each dr In dt.Rows
For i As Integer = 0 To dt.Columns.Count - 1
If dr(i).ToString = TextBox1.Text Then
#2) you are creating columns inside the for next loop
Dim arData() As Object = {dr(0), dr(1), dr(2)}
dt2.Columns.Add("IC Roll")
dt2.Columns.Add("F2")
dt2.Columns.Add("F3")
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
#3)you ask for a new row -- you replaced your variable in your for next loop and you never populate it nor give it to any table
dr = dt2.NewRow()
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
dt2.Rows.Add(arData)
Exit For
End If
Next
DataGrid2.DataSource = dt2
Next
<<<<<<<<<<>>>>>>>>>>>>>
should look like this
Dim dr As DataRow, drNew as DataRow
Dim dt As DataTable = objDataSet.Tables(0)
Dim dt2 As New DataTable
dt2.Columns.Add("IC Roll")
dt2.Columns.Add("F2")
dt2.Columns.Add("F3")
For Each dr In dt.Rows
For i As Integer = 0 To dt.Columns.Count - 1
If dr(i).ToString = TextBox1.Text Then
drNew = dt2.NewRow
drNew(0) = dr(0)
drNew(1) = dr(1)
drNew(2) = dr(2)
dt2.Rows.Add(drNew)
drNew = Nothing
Exit For
End If
Next
Next
DataGrid2.DataSource = dt2
'Never argue with an idiot; they'll drag you down to their level and beat you with experience.' ~ anonymous
'Life's real failure is when you do not realize how close you were to success when you gave up.' ~ anonymous
|
|
|
|
|
When you are adding new rows with an object array, you don't have to execute the NewRow() method before doing the Add. I'm also not sure why you would be setting your datagrid's datasource while still inside the for loop, or why you are adding columns to dt2 while inside the for loop. Maybe I'm just not seeing the forest for the trees. Anyways, one suggestion would be for you to change your code to something like the following and see if that resolves your issue. I'm not sure it will, but it's a thought.
Dim dr As DataRow
Dim dt As New DataTable
dt = objDataSet.Tables(0)
Dim dt2 As New DataTable
dt2.Columns.Add("IC Roll")
dt2.Columns.Add("F2")
dt2.Columns.Add("F3")
For Each dr In dt.Rows
For i As Integer = 0 To dt.Columns.Count - 1
If dr(i).ToString = TextBox1.Text Then
Dim arData() As Object = {dr(0), dr(1), dr(2)}
dt2.Rows.Add(arData)
Exit For
End If
Next
Next
dt2.AcceptChanges()
DataGrid2.DataSource = dt2
|
|
|
|
|
Hi
Hope you can help. I've got data saved in SQL DataTable in 1 column is the date an invoice was sent. 2nd column is a Bit(Boolean). This tells me whether the invoice has been paid.
I want to work it so that on form load, a msgbox tells the User to send a reminder for a particular invoice.
The code I'm using works fine for 1st 2 rows and then misses out all other rows.
Any ideas please? 
Dim CurrentDate As Date
Dim ReminderDate As Date
Dim InvoiceDate As Date
Dim msgbxReminder As MsgBoxResult
CurrentDate = Date.Now.Date
InvoiceDate = Me.DachelDataSet.Invoices.Rows(Me.InvoicesBindingSource.Position).Item("InvoiceDate")
ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate)
For Each dr As DataRow In Me.DachelDataSet.Invoices.Rows
If dr("Cleared") Is System.Convert.DBNull Then
If CurrentDate = ReminderDate Then
msgbxReminder = MsgBox("The Invoice for" & " " & CurrentClient & " " & "which is Invoice Number " & " " & CurrentInvoiceID & " " & "is 1 month overdue.")
End If
End If
Next
Thanks all 
Kris MCP
|
|
|
|
|
Hi,
I think there are at least two flaws:
1.
you should not compare for equality if CurrentDate=ReminderDate, since doing so
you might miss a reminder; you need to use an inequality.
2.
The reminder date depends on the order, so it MUST be calculated inside the ForEach loop,
using data pertaining to the current row.
Hope this helps.
|
|
|
|
|
Hi Luc,
Thanks for that, I can see what you're saying.
By having the reminder date outside the ForEach loop, it's not being read on each loop.
I've changed the code to
For Each dr As DataRow In Me.DachelDataSet.Invoices.Rows
ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate)
If dr("Cleared") Is System.Convert.DBNull And ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate) Then
If CurrentDate <> ReminderDate Then
Else
msgbxReminder = MsgBox("The Invoice for" & " " & CurrentClient & " " & "which is Invoice Number " & " " & CurrentInvoiceID & " " & "is 1 month overdue.")
End If
End If
Next
End Sub
However, still getting stuck on 2nd row of table.
Have I misunderstood something? 
Thanks alot
Kris MCP
|
|
|
|
|
KrisnNala wrote: If CurrentDate <> ReminderDate
you made it worse!
equals is not good, since that only fores on one specific day, and your code may not run every day.
You need greater-equal.
And is InvoiceDate really the same for every order??? Try to focus a bit.
|
|
|
|
|
Hi Luc
Sorry getting tired but got to get the contract done.
You say use greater equals? Do you mean >=?
No InvoiceDate is definately not the same, although it can be.
Thanks for your help.
Kris MCP
|
|
|
|
|
KrisnNala wrote: ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate)
If dr("Cleared") Is System.Convert.DBNull And ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate) Then
The second half of the condition will always be true. You just set the variable to the same value that you are comparing it to.
KrisnNala wrote: If CurrentDate <> ReminderDate Then
Else
That is the exact same thing as an equality comparison.
What happens if the program is not run every single day? If the reminder date happens to be on the weekend, then on monday it will not be equal any more.
Use ReminderDate <= CurrentDate to get all invoices that has passed the reminder date.
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
KrisnNala wrote: InvoiceDate = Me.DachelDataSet.Invoices.Rows(Me.InvoicesBindingSource.Position).Item("InvoiceDate")
You have to get the invoice date inside the loop, otherwise you will use the same invoice date for all records.
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
Hi Guff
Thanks alot for your help. Still have a problem that it's not moving through each row of the table. I know it's something really stupid I'm missing but getting annoyed with my self.
Code is now
Dim CurrentDate As Date
Dim ReminderDate As Date
Dim InvoiceDate As Date
Dim msgbxReminder As MsgBoxResult
CurrentDate = Date.Now.Date
For Each dr As DataRow In Me.DachelDataSet.Invoices.Rows
If dr("Cleared") Is System.Convert.DBNull Then
InvoiceDate = Me.DachelDataSet.Invoices.Rows(Me.InvoicesBindingSource.Position).Item("InvoiceDate")
ReminderDate = DateAdd(DateInterval.Month, 1, InvoiceDate)
If ReminderDate <= CurrentDate Then
msgbxReminder = MsgBox("The Invoice for" & " " & CurrentClient & " " & "which is Invoice Number " & " " & CurrentInvoiceID & " " & "is 1 month overdue.")
End If
End If
Next
Thanks Alot
Kris MCP
|
|
|
|
|
You still read the invoice date from the same data row eventhough you do it inside the loop. Read the invoice date from the data row dr instead.
Despite everything, the person most likely to be fooling you next is yourself.
|
|
|
|
|
Hi Guffa
Thanks alot for your help. All sorted. 
Kris MCP
|
|
|
|
|
I have a solution with a couple projects in VB and CS with the following structure:
Solution: Utils.Net
VB proj: Utilities
CS proj: Utilities.Print
Is there a way to compile the entire solution into 1 .dll called utilities.dll. Is it possible to add all source files into one project and compile that project into a single .dll?
|
|
|
|
|
Each project should be able to compile individually. Do you have Utilities.Print reference to the utilities.dll that should be generated in the VB Project called Utilities? If so, everything ought to fall right in place.
"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
|
|
|
|
|
That's what I figured. I thought rather than have a utilities.dll and a utilitiesprint.dll (or referencing the Print class from the utils class), that they could be combined into a single .dll. Not a big deal, just curious if it could be done.
|
|
|
|
|
Maybe you can look into ILMerge for that.
|
|
|
|
|
I have an .ocx file made in vb 6.0 which i wanna use in vb.net 2.0
Could anyone plz tell me how to do that.
I have referenced it buti am not able to use the user-control which it contains.
|
|
|
|
|
Do you have a code example of how you are trying to accomplish this?
"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
|
|
|
|
|
I have referenced it by:
Right-clicking the project--->add reference--->browse---> add.
It got added in my project but I am unable to usethe usercontrolthat it contains. How can I do this?
|
|
|
|
|
I want my grid to resize when form is resized but i want to use just anchor and dock properties of the grid.
i.e.
resolution 800,600
grid.location = covering 25% of the bottom part on the form remains
25% when it is maximized
Riaz Ahmed
0300-2668545
Software Engineer
DCCTechnologies
riaz.ahmed@dcc.com.pk
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
