Page 1 of 2
Using Stored Procedure but run time
Posted: Mon Jan 06, 2014 6:56 am
by 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
Re: Using Stored Procedure but run time
Posted: Mon Jan 06, 2014 9:47 am
by 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.
Re: Using Stored Procedure but run time
Posted: Mon Jan 06, 2014 10:56 am
by 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
Re: Using Stored Procedure but run time
Posted: Mon Jan 06, 2014 11:17 am
by 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.
Re: Using Stored Procedure but run time
Posted: Wed Jan 08, 2014 11:49 am
by 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 ?
Re: Using Stored Procedure but run time
Posted: Wed Jan 08, 2014 12:50 pm
by 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.
Re: Using Stored Procedure but run time
Posted: Mon Jan 13, 2014 9:51 am
by 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
Re: Using Stored Procedure but run time
Posted: Mon Jan 13, 2014 12:57 pm
by 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.
Re: Using Stored Procedure but run time
Posted: Tue Jan 14, 2014 7:10 am
by mamb
no property called Type in DS1
and i don't have enum StiSqlSourceType
Re: Using Stored Procedure but run time
Posted: Tue Jan 14, 2014 7:22 am
by Alex K.
Hello,
Please check the last version.
Thank you.