Hi Phin,
Below is an example of what I do. In this example, I am creating a table to
contain the receiveable info on a group of customers.
Steps
1. The user has selected them from a listbox. The string
'toarraystring' contains the account #s to include in the table.
2. I then delete an sp (sp_dropsp_passarray kills the sp called
'sp_passarray')
3. I then create the procedure sp_passarray anew - you will note that
inside this procedure I check to see if table 'a_rsumtable' exists; if it
does, this sp will first kill it. The sp then goes on to 'select ...
data ... into a_rsumtable', which creates a_rsumtable with the appropriate
data in it
4. I then create the sp
5. I then execute the sp
Now a_rsumtable exists with fresh data in it and when the report is called,
it opens a_rsumtable - it doesn't care that it has different data each time
it opens it.
Let me know if you have any questions.
Regards,
Bernie
Dim toarraystring As String
Dim acount As Integer = 0
For i = 0 To toarray.Length - 1
acount += 1
toarraystring += Chr(39) & Trim(toarray(i)) & Chr(39) & ","
If i <> toarray.Length - 1 Then 'ie, it isn't the last item
If acount > 20 Then
acount = 0
toarraystring += vbCrLf
End If
End If
Next
toarraystring = Mid(toarraystring, 1, toarraystring.Length - 1) &
")"
Dim dcmd As New SqlCommand
dcmd = New SqlCommand("sp_dropsp_passarray", oconn)'drop the sp
dcmd.CommandType = CommandType.StoredProcedure
Try
dcmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.message)
End Try
Dim creationstring As String
creationstring = "CREATE PROCEDURE sp_passarray AS " _
& "if exists (select * from information_schema.tables where
table_name = " _
& "'a_rsumtable')" & vbCrLf & "drop table a_rsumtable" & vbCrLf _
& "select imcacct, pubcode, invnum, inv_dt, brname, " _
& "(case when inv_dt + 31 > getdate() then balance else 0 end) as
under31, " _
& "(case when inv_dt + 61 > getdate() and inv_dt + 31 <= getdate()
then balance else 0 end) as over30, " _
& "(case when inv_dt + 91 > getdate() and inv_dt + 61 <= getdate()
then balance else 0 end) as over60, " _
& "(case when inv_dt + 121 > getdate() and inv_dt + 91 <= getdate()
then balance else 0 end) as over90, " _
& "(case when inv_dt + 121 <= getdate() then balance else 0 end) as
over120" _
& " into a_rsumtable from a_r where imcacct" _
& " in (" & toarraystring & vbCrLf _
& "order by pubcode, imcacct"
sqladapt.SelectCommand = New SqlCommand(creationstring, oconn)
Try
sqladapt.SelectCommand.ExecuteNonQuery() ' create the sp
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Dim ocmd As New SqlCommand
ocmd = New SqlCommand("sp_passarray", oconn) ' execute the sp
ocmd.CommandType = CommandType.StoredProcedure
Try
ocmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.message)
End Try
"Phin" <mr******@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
Bernie,
Thank you for the quick response! I am not sure what you mean to delete
table (which table? in the report?) and recycle. Please your post the
code on how to do this. I've just seen the xsd solution and your way
sounds easier. I apprecaite your help!
Thanks!
Phin
Bernie Yaeger wrote: Hi Phin,
You're going about it in a way that I would not. I do what you need
all the time, and here's how: I delete a table (using an sql command obj)
that I recycle, let's call it ttable. I then recreate ttable using a
'select into' clause. I then run the report. The report always opens ttable, so
it doesn't care that the data is always different.
HTH,
