Passing parameters from an array

G

Guest

Hi

I am trying to run a query that will return a date. I wanted to pass the contents of an arraylist as a parameter to the query. The query will need to be run many times for each value in the arraylist so i have placed the code in a loop. What i am confused on is how i actullay use the values of the arraylist as a parameter to the query?
I have tried the following....
cmdArrivalDt.Parameters("@InvNo").Value = AvailCaraInv(i
but this does not seem to pass the value in!!

Here is my full code. Can anyone plz help me

Dim InvNo = AvailCaraInv(i

Dim cmdArrivalDt As New SqlCommand("Select min(a.Dt_Of_Arrival) from Booking a, Caravan_Booking b Where @Prop_departure_dt < Dt_Of_Arrival AND Caravan_Inv_No = @InvNo", Cn
cmdArrivalDt.CommandType = CommandType.Tex

'declare the parameter
cmdArrivalDt.Parameters.Add("@Prop_departure_dt", SqlDbType.DateTime
cmdArrivalDt.Parameters.Add("@InvNo", SqlDbType.Char, 5

'now set the value
cmdArrivalDt.Parameters("@Prop_departure_dt").Value = dtp2.Tex
cmdArrivalDt.Parameters("@InvNo").Value = AvailCaraInv(i

For i = 0 To AvailCaraInv.Count -

Tr
Cn.Open(
Dim reader1 As SqlDataReade
reader1 = cmdArrivalDt.ExecuteReader(
reader1.Read(
lblArrivalDt.Text = (reader1.GetSqlString(0).ToString
Catch ex As Exceptio
MsgBox(ex.Message
Finall
Cn.Close(
End Tr

Nex
 
W

William Ryan eMVP

Put the line of code
cmdArrivalDt.Parameters("@InvNo").Value = AvailCaraInv(i)
in the for loop if you want it to change to different values

Bhavna said:
Hi,

I am trying to run a query that will return a date. I wanted to pass the
Click to expand...
contents of an arraylist as a parameter to the query. The query will need to
be run many times for each value in the arraylist so i have placed the code
in a loop. What i am confused on is how i actullay use the values of the
arraylist as a parameter to the query?
I have tried the following.....
cmdArrivalDt.Parameters("@InvNo").Value = AvailCaraInv(i)
but this does not seem to pass the value in!!!

Here is my full code. Can anyone plz help me?


Dim InvNo = AvailCaraInv(i)

Dim cmdArrivalDt As New SqlCommand("Select min(a.Dt_Of_Arrival)
Click to expand...
from Booking a, Caravan_Booking b Where @Prop_departure_dt < Dt_Of_Arrival
AND Caravan_Inv_No = @InvNo", Cn)
 
G

Guest

Hi William
Hi have tried adding the additional line of code u suggested into my loop so thta the value changes for each iteration of the loop. The code still does not seem to return the correct date for the InvNo parameter. In fact i dont even think the value is being passed to the query correctly!
Is there any way i can check the value being passed to the query for each iteration through the loop?
I tried to write
Msgbox(AvailCaraInv(i)) within the loop but i get a error stating...
An unhandled exception of type 'System.ArgumentException' occurred in
microsoft.visualbasic.dl
Additional information: Argument 'Prompt' cannot be converted to type 'String'

here is my full code. Can anyone plz help me debug this problem?

Dim InvNo = AvailCaraInv(i

Dim cmdArrivalDt As New SqlCommand("Select min(a.Dt_Of_Arrival
from Booking a, Caravan_Booking b Where @Prop_departure_dt < Dt_Of_Arriva
AND Caravan_Inv_No = @InvNo", Cn
cmdArrivalDt.CommandType = CommandType.Tex

'declare the parameter
cmdArrivalDt.Parameters.Add("@Prop_departure_dt", SqlDbType.DateTime
cmdArrivalDt.Parameters.Add("@InvNo", SqlDbType.Char, 5

'now set the value
cmdArrivalDt.Parameters("@Prop_departure_dt").Value = dtp2.Tex
cmdArrivalDt.Parameters("@InvNo").Value = AvailCaraInv(i

For i = 0 To AvailCaraInv.Count -

cmdArrivalDt.Parameters("@InvNo").Value = AvailCaraInv(i
'Msgbox(AvailCaraInv(i)) 'this line produces an erro
Tr
Cn.Open(
Dim reader1 As SqlDataReade
reader1 = cmdArrivalDt.ExecuteReader(
reader1.Read(
lblArrivalDt.Text = (reader1.GetSqlString(0).ToString
Catch ex As Exceptio
MsgBox(ex.Message
Finall
Cn.Close(
End Tr

Nex
 
C

Cor

Hi Bhavna,

You have added Bill said "Put". Bill did mean with that in my idea "move"
and not "add"
cmdArrivalDt.Parameters("@InvNo").Value = AvailCaraInv(i)
Click to expand...

Normaly the one above should giving the error and you should not even reach
your messagebox.
For i = 0 To AvailCaraInv.Count - 1

cmdArrivalDt.Parameters("@InvNo").Value = AvailCaraInv(i)
'Msgbox(AvailCaraInv(i)) 'this line produces an error
Try
Cn.Open()
Dim reader1 As SqlDataReader
Click to expand...

I hove this helps?

Cor
 
G

Guest

Hi Cor

I have now just moved the code to within my loop but now for sum strange reason i do not get any results from the query!! When this value was being assigned to the parameter outside of the loop i got a result (even though it was the wrong result as the parameter was not being passed in!!
I still get the same error message from the when i try and display the contents of the InvNo parameter.

An unhandled exception of type 'System.ArgumentException' occurred in
microsoft.visualbasic.dl
Additional information: Argument 'Prompt' cannot be converted to type 'String'

Dim InvNo = AvailCaraInv(i

Dim cmdArrivalDt As New SqlCommand("Select min(a.Dt_Of_Arrival
from Booking a, Caravan_Booking b Where @Prop_departure_dt < Dt_Of_Arriva
AND Caravan_Inv_No = @InvNo", Cn
cmdArrivalDt.CommandType = CommandType.Tex

'declare the parameter
cmdArrivalDt.Parameters.Add("@Prop_departure_dt", SqlDbType.DateTime
cmdArrivalDt.Parameters.Add("@InvNo", SqlDbType.Char, 5

'now set the value
cmdArrivalDt.Parameters("@Prop_departure_dt").Value = dtp2.Tex

For i = 0 To AvailCaraInv.Count -

cmdArrivalDt.Parameters("@InvNo").Value = AvailCaraInv(i
'Msgbox(AvailCaraInv(i)) 'this line produces an erro
Tr
Cn.Open(
Dim reader1 As SqlDataReade
reader1 = cmdArrivalDt.ExecuteReader(
reader1.Read(
lblArrivalDt.Text = (reader1.GetSqlString(0).ToString
Catch ex As Exceptio
MsgBox(ex.Message
Finall
Cn.Close(
End Tr

Nex
 
C

Cor

Hi Bhavna,

Can you look what is that AvailCaraInv(i) (where did you declare it)

I see it on so many places in your code.
Dim InvNo = AvailCaraInv(i)

Dim cmdArrivalDt As New SqlCommand("Select min(a.Dt_Of_Arrival)
from Booking a, Caravan_Booking b Where @Prop_departure_dt <
Click to expand...
Dt_Of_Arrival

Here again, are you sure i is something there?

Cor
 
C

Cor

Hi Bhavna

And then you fill it somewhere that arraylist, how you do that?
Private AvailCaraInv As New ArrayList()
Click to expand...


By the way that () at the end should not be there, but I do not think that
that is the error.

Cor
 
G

Guest

Hi Cor
I am filling my arraylist like so...... AvailCaraInv.Add(reader3.GetSqlString(0)

this is where it is fille
Tr
Cn.Open(
Dim reader3 As SqlDataReade
reader3 = cmd.ExecuteReader(
While reader3.Rea
CBoxCaraInv.Items.Add(reader3.GetSqlString(0)

For i = 0 To AllBookedCaraInv.Count -
'We go through the arra

'---------------------
'remove the inventory numbers that are in the array of previously booked cara'
Dim AllBookedCaraInvI As Integer = CBoxCaraInv.FindStringExact(Tri
(AllBookedCaraInv(i))
If AllBookedCaraInvI <> -1 The
CBoxCaraInv.Items.RemoveAt(AllBookedCaraInvI
End I
Nex

'place an array here of all the caravan invs availbl
AvailCaraInv.Add(reader3.GetSqlString(0)

End Whil
Catch ex As Exceptio
MsgBox(ex.Message
Finall
Cn.Close(
End Try
 
C

Cor

Hi Bhavna,

I get the idea that you try on a very difficult way to find what caravan was
booked previously and then do something with it.

That caravan is booked for a date, when you update everytime that a caravan
is booked, you only have to know if it is there.

You even can use a executescalar for that and does something as

Dim Conn As New SqlConnection(connString)
Dim cmd as New SqlCommand("select count(*) from caravans where caravan = @x
and caravanbookingdate = @y")
cmd.Connection.Open()
Dim count As Integer = CInt(cmd.ExecuteScalar())
Conn.Close()
If count = 0 then "the caravan is not booked

Looks this not much easier?

Cor
 
Top