Using Stored Procedure but run time

[mamb]

I was take a look for your video in YouTube
about Using Stored Procedure in Designer
but I wont to build new report at run time connected with Stored Procedure
for that please , could you give me example by vb.net windows project to build new report run time include :
1- build new sql connection to database in report
2- connect to stored procedure and pass the parameters with values from form

Thank you

[Alex K.]

Hello,

Please check the following article on our knowledbase:
http://support.stimulsoft.com/index.php ... connection
You can use the similar code.

Thank you.

[mamb]

Thank you for reply
.. but sorry your link is not talk about connect to stored procedure and pass the parameters with values from form

[Andrew]

Hello,

In addition, please see the following video
http://www.youtube.com/watch?v=lhyyHr0AvrY

Please let us know whether this help you.

Waiting for your reply.
Thank you.

[mamb]

Thank you for reply
I have seen video in link
but I want make same by code only
I will build the stimulsoft dictionary by code
it will connect to data and run Stored Procedure after pass the parameters
in video I see button "Retrieve Columns" how i can Retrieve Columns in code ?

[Alex K.]

Hello,

Please see the following code:

Code: Select all

report.Dictionary.Databases.Clear();
report.Dictionary.Databases.Add(new Stimulsoft.Report.Dictionary.StiSqlDatabase("Connection", connectionString));
//Add DataSources:
StiSqlSource DS1 = new StiSqlSource("Connection", "Pr01", "Pr01", "execute Pr01", true, false);
//add parameters
StiDataParameter param = new StiDataParameter("@param1", (int)SqlDbType.Int, 1);
DS1.Parameters.Add(param);
report.Dictionary.DataSources.Add(DS1);
// fill data or schema to dataTableDS1 from SqlDataAdapter or SqlDataReader
//...
//Add Columns:
foreach (DataColumn col in dataTableDS1.Columns)
{
    DS1.Columns.Add(col.ColumnName, col.DataType);
}

Thank you.

[mamb]

Unfortunately,, I was converting the code to vb.net and tried it but show this error message
"The parameterized query '(@Code varchar(50),@FromCode varchar(50),@ToCode varchar(50))exe' expects the parameter '@Code', which was not supplied. Statement(s) could not be prepared."

Please, take A look for this code and tell me where is the error

Code: Select all

Imports Stimulsoft.Report.Dictionary

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connectionString As String = "Persist Security Info=False;User ID=sa;Password=123;Initial Catalog=ALJ_VenusNet_Oct10;Data Source=RTW\DEVSERVER2008R2"
        report.Dictionary.Databases.Clear()
        report.Dictionary.Databases.Add(New Stimulsoft.Report.Dictionary.StiSqlDatabase("Connection", connectionString))
        'Add DataSources:
        Dim DS1 As New StiSqlSource("Connection", "sys_repBanks", "sys_repBanks", "execute sys_repBanks @Code, @FromCode, @ToCode", True, False)
        'add parameters
        Dim param1 As New StiDataParameter("@Code", CInt(SqlDbType.VarChar), 50)
        param1.Value = ""
        DS1.Parameters.Add(param1)

        Dim param2 As New StiDataParameter("@FromCode", CInt(SqlDbType.VarChar), 50)
        param2.Value = ""
        DS1.Parameters.Add(param2)

        Dim param3 As New StiDataParameter("@ToCode", CInt(SqlDbType.VarChar), 50)
        param3.Value = ""
        DS1.Parameters.Add(param3)
        report.Dictionary.DataSources.Add(DS1)

        Dim Conn As New SqlClient.SqlConnection(connectionString)
        Dim Comm As New SqlClient.SqlCommand("sys_repBanks", Conn)
        Comm.CommandType = CommandType.StoredProcedure
        Dim Para1 As New SqlClient.SqlParameter
        Para1.ParameterName = "@Code"
        Para1.DbType = SqlDbType.VarChar
        Para1.Size = 50
        Para1.Value = ""

        Comm.Parameters.Add(Para1)
        
        Dim Para2 As New SqlClient.SqlParameter
        Para2.ParameterName = "@FromCode"
        Para2.DbType = SqlDbType.VarChar
        Para2.Size = 50
        Para2.Value = ""

        Comm.Parameters.Add(Para2)
        
        Dim Para3 As New SqlClient.SqlParameter
        Para3.ParameterName = "@ToCode"
        Para3.DbType = SqlDbType.VarChar
        Para3.Size = 50
        Para3.Value = ""

        Comm.Parameters.Add(Para3)
        Dim Adpt As New SqlClient.SqlDataAdapter(Comm)
        Dim DS As New DataSet
        Using Conn
            Conn.Open()
            Comm.Connection = Conn
            Comm.ExecuteNonQuery()

            Adpt.Fill(DS)
        End Using
       



        Dim dataTableDS1 As DataTable = DS.Tables(0)

        For Each col As DataColumn In dataTableDS1.Columns
            DS1.Columns.Add(col.ColumnName, col.DataType)
        Next
        report.Show()

    End Sub
End Class

[Alex K.]

Hello,

Please try to check the following code:

Code: Select all

...
Dim DS1 As New StiSqlSource("Connection", "sys_repBanks", "sys_repBanks", "sys_repBanks", True, False)
DS1.Type = StiSqlSourceType.StoredProcedure
...

Thank you.

[mamb]

no property called Type in DS1
and i don't have enum StiSqlSourceType

[Alex K.]

Hello,

Please check the last version.

Thank you.