Tuesday, March 27, 2012
Executing multiple reports to form single document
In my project I have lot of small small reports having the same criteria
e.g. Company and Years, the requirement here is to execute all these reports
and create a single document that can be delivered to customer's customer. I
am not able to find any such posting in this group or not able to find any
pointers on this.
Your help on this would be really appreciated.
Thanks in Advance
--
CloudsYou could use subreports as a way of combining your multiple reports. You
would have one "master" report that consisted of simply several subreports.
That would enable you to run the master and export it.
Mike G.
"Clouds" <Clouds@.discussions.microsoft.com> wrote in message
news:56C49443-0E2F-401A-B5B8-5D694B2D8780@.microsoft.com...
> Hi Everyone!
> In my project I have lot of small small reports having the same criteria
> e.g. Company and Years, the requirement here is to execute all these
> reports
> and create a single document that can be delivered to customer's customer.
> I
> am not able to find any such posting in this group or not able to find any
> pointers on this.
> Your help on this would be really appreciated.
> Thanks in Advance
> --
> Clouds
executing Insert SQLdatasource
Hey forum,
the .NET form controls make it extremely easy to manage database data, control + SQLdatasource et voilĂ , its there.
i have a web form, and a SQLdatasource with an insert query with parameters (works perfectly, only the Date picker Lite textbox ID as parameter wont register, but i have a thread runnin on their forums for that). when i open the Query builder and execute the query it works! woot! but now, how can i make it execute on a button press? i tried with several commands, like SQLdatasource1.execute, and several other commands i searched (can post later, not behind my test environment with my tools/webpage at the moment). so what is the command to let the SQLdatasource execute it's query? it's all i need to know to make several forms, please ghelp me! :)
gr. michael
bump|||Hi,
According to your question, are you in trouble with executing insert operation by a button? If so, you may create the Button_Click event and useSqlDataSource.InsertCommand andSqlDataSource.Insert()to achieve your purpose.
For more details about the usage of SqlDataSource, pls refer the following link:
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.aspx
Thanks.
sqlMonday, March 26, 2012
Executing an MS SQL stored procedure from a java servlet
an SQL server stored procedure. I'm using the WebLogic 8 App. server.
I am able to retrieve database information, so I know my application
server can talk to the database.
I've determined the failure occurs when the the following statement is
executed: cstmt.execute(); (due to the failure of println statements
placed afterwards). I get the following error after trying to execute
the stored procedure call:
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
procedure 'insertTheForm'
The username and password i'm using to connect is a Windows user with
admin rights. It is also associated with the Odbc connection--and of
course is a database user..with full rights. I have executable
permissions on the stored procedure set up as well. I did a microsoft
recommended registry fix as well (for a previous
error:http://support.microsoft.com/defaul...;en-us;Q238971).
Am I missing something? I posted my servlet code below.
Thanks for any help!
Dinesh
formHandlingServlet.class
--------
package showme;
/*
* formHandlingServlet.java
*
* Created on July 6, 2003, 7:01 PM
*/
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import java.text.DateFormat;
/**
*
* @.author Administrator
*/
public class formHandlingServlet extends HttpServlet {
private static final String email1 = "email";
private static final String password1 = "password1";
private static final String password2 = "password2";
private static final String displayname = "displayname";
Connection dbConn = null;
// create a persistent conneciton to the SQL server
public void init() throws ServletException
{
String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
String dbURL = "jdbc:odbc:Con2";
String usernameDbConn = "dinesh";
String passwordDbConn = "werty6969";
try
{
Class.forName(jdbcDriver).newInstance();
dbConn = DriverManager.getConnection(dbURL, usernameDbConn,
passwordDbConn);
}
catch (ClassNotFoundException e)
{
throw new UnavailableException("jdbc driver not found:" + dbURL);
}
catch (SQLException e)
{
throw new UnavailableException("error: " + e);
}
catch (Exception e)
{
throw new UnavailableException("error: " +e);
}
}
public void doPost(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException
{
response.setContentType("text/plain");
PrintWriter out = response.getWriter();
//extract parameter information from register.jsp
String email1 = request.getParameter("email1");
String password1 = request.getParameter("password1");
String password2 = request.getParameter("password2");
String displayname = request.getParameter("displayname");
try
{
//make a callable statement for a stored procedure.
//It has four parameters
CallableStatement cstmt = dbConn.prepareCall(
"{call insertTheForm(?, ?, ?, ?)}");
//set the values of the stored procedure's input parameters
out.println("calling stored procedure . . .");
cstmt.setString(1, email1);
cstmt.setString(2, password1);
cstmt.setString(3, password2);
cstmt.setString(4, displayname);
//now that the input parameters are set, we can proceed to execute the
insertTheForm stored procedure
cstmt.execute();
out.println("stored procedure executed");
}
catch (SQLException e)
{
throw new UnavailableException("error: " + e);
}
}
}
dinesh wrote:
> Hi Joseph its nice to get a reply from a BEA employee..I will check out the
> bea groups. Well, yes I am able to execute the query from the MS query
> analyzer. I am also able to perform a table read from a servlet, I run into
> problems when trying to insert data. I tried to use the ms jdbc and
> implement it as instructed by bea edocs. here is my error:
> formHandlingServlet.java [79:1] cannot resolve symbol
> symbol : variable conn
> location: class showme.formHandlingServlet
> CallableStatement cstmt = conn.prepareCall(
The source need some work. You define a connection object in a try block.
That's the full scope of the object (ie: no one sees it outside the try block).
Don't create a connection in init(). Just do it in the post() right before you're
going to use it, and close it in a finally block:
Connect ion conn = null; // outside try block
try {
...
conn = d.connect(...);
... do all jdbc ...
} catch (Exception e) {
...
} finally {
try { conn.close();} catch (Exception (ignore){}
}
Joe Weinstrein
> ^
> source
> ---
> package showme;
> /*
> * formHandlingServlet.java
> *
> * Created on July 6, 2003, 7:01 PM
> */
> import javax.servlet.*;
> import javax.servlet.http.*;
> import java.io.*;
> import java.sql.*;
> import java.text.DateFormat;
> import java.util.*;
> /**
> *
> * @.author Administrator
> */
> public class formHandlingServlet extends HttpServlet {
> private static final String email1 = "email";
> private static final String password1 = "password1";
> private static final String password2 = "password2";
> private static final String displayname = "displayname";
> // create a persistent conneciton to the SQL server
> public void init() throws ServletException
> {
> Properties props = new Properties();
> props.put("user", "dinesh");
> props.put("password", "xyxyxyxy6969");
> props.put("db", "users");
> props.put("server", "COMPAQSERVER");
> try
> {
> Driver myDriver = (java.sql.Driver)Class.forName
> ("weblogic.jdbc.mssqlserver4.Driver").newInstance();
> Connection conn = myDriver.connect("jdbc:weblogic:mssqlserver4",
> props);
> }
> catch (ClassNotFoundException e)
> {
> //throw new UnavailableException("jdbc driver not found:" +
> dbURL);
> }
> catch (SQLException e)
> {
> throw new UnavailableException("error: " + e);
> }
> catch (Exception e)
> {
> throw new UnavailableException("error: " +e);
> }
> }
> public void doPost(HttpServletRequest request, HttpServletResponse
> response) throws ServletException, IOException
> {
> response.setContentType("text/plain");
> PrintWriter out = response.getWriter();
> //extract parameter information from register.jsp
> String email1 = request.getParameter("email1");
> String password1 = request.getParameter("password1");
> String password2 = request.getParameter("password2");
> String displayname = request.getParameter("displayname");
> try
> {
> //make a callable statement for a stored procedure.
> //It has four parameters
> CallableStatement cstmt = conn.prepareCall(
> "{call dbo.insertTheForm(?, ?, ?, ?)}");
> //set the values of the stored procedure's input parameters
> out.println("calling stored procedure . . .");
> cstmt.setString(1, email1);
> cstmt.setString(2, password1);
> cstmt.setString(3, password2);
> cstmt.setString(4, displayname);
> //now that the input parameters are set, we can proceed to
> execute the insertTheForm stored procedure
> cstmt.execute();
> out.println("stored procedure executed");
> out.close();
> }
> catch (SQLException e)
> {
> throw new UnavailableException("error: " + e);
> }
> }
> }
> "Joseph Weinstein" <joe.remove_this@.bea.com.remove_this> wrote in message
> news:3F0A582A.DE7760DD@.bea.com.remove_this...
> > dinesh prasad wrote:
> > > I'm trying to use a servlet to process a form, then send that data to
> > > an SQL server stored procedure. I'm using the WebLogic 8 App. server.
> > > I am able to retrieve database information, so I know my application
> > > server can talk to the database.
> > Hi! Two or three things:
> > 1 - We don't support the use of the jdbc-odbc bridge because it's flakey
> and
> > not threadsafe. You should download and use MS's own type-4 jdbc driver.
> > 2 - Can you execute this stored procedure from a commandline MS DBMS
> client
> > when you log in with the same user? I ask this, because this user's
> default database
> > context might not be in the database where the procedure is.
> > 3 - You can get quick weblogic-specific help in our support newsgroups,
> which
> > you can find under the support page at www.bea.com.
> > Joe Weinstein at BEA
> > > > I've determined the failure occurs when the the following statement is
> > > executed: cstmt.execute(); (due to the failure of println statements
> > > placed afterwards). I get the following error after trying to execute
> > > the stored procedure call:
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
> > > procedure 'insertTheForm'
> > > > The username and password i'm using to connect is a Windows user with
> > > admin rights. It is also associated with the Odbc connection--and of
> > > course is a database user..with full rights. I have executable
> > > permissions on the stored procedure set up as well. I did a microsoft
> > > recommended registry fix as well (for a previous
> > > error:http://support.microsoft.com/defaul...;en-us;Q238971).
> > > Am I missing something? I posted my servlet code below.
> > > > Thanks for any help!
> > > Dinesh
> > > > formHandlingServlet.class
> > > > --------
> > > package showme;
> > > /*
> > > * formHandlingServlet.java
> > > *
> > > * Created on July 6, 2003, 7:01 PM
> > > */
> > > import javax.servlet.*;
> > > import javax.servlet.http.*;
> > > import java.io.*;
> > > import java.sql.*;
> > > import java.text.DateFormat;
> > > > /**
> > > *
> > > * @.author Administrator
> > > */
> > > public class formHandlingServlet extends HttpServlet {
> > > > private static final String email1 = "email";
> > > private static final String password1 = "password1";
> > > private static final String password2 = "password2";
> > > private static final String displayname = "displayname";
> > > > Connection dbConn = null;
> > > > // create a persistent conneciton to the SQL server
> > > > public void init() throws ServletException
> > > {
> > > String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
> > > String dbURL = "jdbc:odbc:Con2";
> > > String usernameDbConn = "dinesh";
> > > String passwordDbConn = "werty6969";
> > > > try
> > > {
> > > Class.forName(jdbcDriver).newInstance();
> > > dbConn = DriverManager.getConnection(dbURL, usernameDbConn,
> > > passwordDbConn);
> > > }
> > > catch (ClassNotFoundException e)
> > > {
> > > throw new UnavailableException("jdbc driver not found:" + dbURL);
> > > }
> > > catch (SQLException e)
> > > {
> > > throw new UnavailableException("error: " + e);
> > > }
> > > catch (Exception e)
> > > {
> > > throw new UnavailableException("error: " +e);
> > > }
> > > }
> > > > public void doPost(HttpServletRequest request, HttpServletResponse
> > > response) throws ServletException, IOException
> > > {
> > > response.setContentType("text/plain");
> > > PrintWriter out = response.getWriter();
> > > > //extract parameter information from register.jsp
> > > > String email1 = request.getParameter("email1");
> > > String password1 = request.getParameter("password1");
> > > String password2 = request.getParameter("password2");
> > > String displayname = request.getParameter("displayname");
> > > > try
> > > {
> > > //make a callable statement for a stored procedure.
> > > //It has four parameters
> > > > CallableStatement cstmt = dbConn.prepareCall(
> > > "{call insertTheForm(?, ?, ?, ?)}");
> > > > //set the values of the stored procedure's input parameters
> > > > out.println("calling stored procedure . . .");
> > > cstmt.setString(1, email1);
> > > cstmt.setString(2, password1);
> > > cstmt.setString(3, password2);
> > > cstmt.setString(4, displayname);
> > > //now that the input parameters are set, we can proceed to execute the
> > > insertTheForm stored procedure
> > > > cstmt.execute();
> > > out.println("stored procedure executed");
> > > }
> > > > catch (SQLException e)
> > > {
> > > throw new UnavailableException("error: " + e);
> > > > }
> > > }
> > > > }|||ok, great I have it working now, thanks Joe!!
Dinesh
"Joseph Weinstein" <joe.remove_this@.bea.com.remove_this> wrote in message
news:3F0AD70E.8876F92@.bea.com.remove_this...
>
> dinesh wrote:
> > Hi Joseph its nice to get a reply from a BEA employee..I will check out
the
> > bea groups. Well, yes I am able to execute the query from the MS query
> > analyzer. I am also able to perform a table read from a servlet, I run
into
> > problems when trying to insert data. I tried to use the ms jdbc and
> > implement it as instructed by bea edocs. here is my error:
> > formHandlingServlet.java [79:1] cannot resolve symbol
> > symbol : variable conn
> > location: class showme.formHandlingServlet
> > CallableStatement cstmt = conn.prepareCall(
> The source need some work. You define a connection object in a try block.
> That's the full scope of the object (ie: no one sees it outside the try
block).
> Don't create a connection in init(). Just do it in the post() right before
you're
> going to use it, and close it in a finally block:
> Connect ion conn = null; // outside try block
> try {
> ...
> conn = d.connect(...);
> ... do all jdbc ...
> } catch (Exception e) {
> ...
> } finally {
> try { conn.close();} catch (Exception (ignore){}
> }
> Joe Weinstrein
> > ^
> > source
> > ---
> > package showme;
> > /*
> > * formHandlingServlet.java
> > *
> > * Created on July 6, 2003, 7:01 PM
> > */
> > import javax.servlet.*;
> > import javax.servlet.http.*;
> > import java.io.*;
> > import java.sql.*;
> > import java.text.DateFormat;
> > import java.util.*;
> > /**
> > *
> > * @.author Administrator
> > */
> > public class formHandlingServlet extends HttpServlet {
> > private static final String email1 = "email";
> > private static final String password1 = "password1";
> > private static final String password2 = "password2";
> > private static final String displayname = "displayname";
> > // create a persistent conneciton to the SQL server
> > public void init() throws ServletException
> > {
> > Properties props = new Properties();
> > props.put("user", "dinesh");
> > props.put("password", "xyxyxyxy6969");
> > props.put("db", "users");
> > props.put("server", "COMPAQSERVER");
> > try
> > {
> > Driver myDriver = (java.sql.Driver)Class.forName
> > ("weblogic.jdbc.mssqlserver4.Driver").newInstance();
> > Connection conn =
myDriver.connect("jdbc:weblogic:mssqlserver4",
> > props);
> > }
> > catch (ClassNotFoundException e)
> > {
> > //throw new UnavailableException("jdbc driver not
found:" +
> > dbURL);
> > }
> > catch (SQLException e)
> > {
> > throw new UnavailableException("error: " + e);
> > }
> > catch (Exception e)
> > {
> > throw new UnavailableException("error: " +e);
> > }
> > }
> > public void doPost(HttpServletRequest request, HttpServletResponse
> > response) throws ServletException, IOException
> > {
> > response.setContentType("text/plain");
> > PrintWriter out = response.getWriter();
> > //extract parameter information from register.jsp
> > String email1 = request.getParameter("email1");
> > String password1 = request.getParameter("password1");
> > String password2 = request.getParameter("password2");
> > String displayname = request.getParameter("displayname");
> > try
> > {
> > //make a callable statement for a stored procedure.
> > //It has four parameters
> > CallableStatement cstmt = conn.prepareCall(
> > "{call dbo.insertTheForm(?, ?, ?, ?)}");
> > //set the values of the stored procedure's input parameters
> > out.println("calling stored procedure . . .");
> > cstmt.setString(1, email1);
> > cstmt.setString(2, password1);
> > cstmt.setString(3, password2);
> > cstmt.setString(4, displayname);
> > //now that the input parameters are set, we can proceed to
> > execute the insertTheForm stored procedure
> > cstmt.execute();
> > out.println("stored procedure executed");
> > out.close();
> > }
> > catch (SQLException e)
> > {
> > throw new UnavailableException("error: " + e);
> > }
> > }
> > }
> > "Joseph Weinstein" <joe.remove_this@.bea.com.remove_this> wrote in
message
> > news:3F0A582A.DE7760DD@.bea.com.remove_this...
> > > > > dinesh prasad wrote:
> > > > > I'm trying to use a servlet to process a form, then send that data
to
> > > > an SQL server stored procedure. I'm using the WebLogic 8 App.
server.
> > > > I am able to retrieve database information, so I know my application
> > > > server can talk to the database.
> > > > Hi! Two or three things:
> > > 1 - We don't support the use of the jdbc-odbc bridge because it's
flakey
> > and
> > > not threadsafe. You should download and use MS's own type-4 jdbc
driver.
> > > 2 - Can you execute this stored procedure from a commandline MS DBMS
> > client
> > > when you log in with the same user? I ask this, because this user's
> > default database
> > > context might not be in the database where the procedure is.
> > > 3 - You can get quick weblogic-specific help in our support
newsgroups,
> > which
> > > you can find under the support page at www.bea.com.
> > > > Joe Weinstein at BEA
> > > > > > > I've determined the failure occurs when the the following statement
is
> > > > executed: cstmt.execute(); (due to the failure of println statements
> > > > placed afterwards). I get the following error after trying to
execute
> > > > the stored procedure call:
> > > > [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
> > > > procedure 'insertTheForm'
> > > > > > The username and password i'm using to connect is a Windows user
with
> > > > admin rights. It is also associated with the Odbc connection--and of
> > > > course is a database user..with full rights. I have executable
> > > > permissions on the stored procedure set up as well. I did a
microsoft
> > > > recommended registry fix as well (for a previous
> > error:http://support.microsoft.com/defaul...;en-us;Q238971).
> > > > Am I missing something? I posted my servlet code below.
> > > > > > Thanks for any help!
> > > > Dinesh
> > > > > > formHandlingServlet.class
> > > > > > --------
> > > > package showme;
> > > > /*
> > > > * formHandlingServlet.java
> > > > *
> > > > * Created on July 6, 2003, 7:01 PM
> > > > */
> > > > import javax.servlet.*;
> > > > import javax.servlet.http.*;
> > > > import java.io.*;
> > > > import java.sql.*;
> > > > import java.text.DateFormat;
> > > > > > /**
> > > > *
> > > > * @.author Administrator
> > > > */
> > > > public class formHandlingServlet extends HttpServlet {
> > > > > > private static final String email1 = "email";
> > > > private static final String password1 = "password1";
> > > > private static final String password2 = "password2";
> > > > private static final String displayname = "displayname";
> > > > > > Connection dbConn = null;
> > > > > > // create a persistent conneciton to the SQL server
> > > > > > public void init() throws ServletException
> > > > {
> > > > String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
> > > > String dbURL = "jdbc:odbc:Con2";
> > > > String usernameDbConn = "dinesh";
> > > > String passwordDbConn = "werty6969";
> > > > > > try
> > > > {
> > > > Class.forName(jdbcDriver).newInstance();
> > > > dbConn = DriverManager.getConnection(dbURL, usernameDbConn,
> > > > passwordDbConn);
> > > > }
> > > > catch (ClassNotFoundException e)
> > > > {
> > > > throw new UnavailableException("jdbc driver not found:" + dbURL);
> > > > }
> > > > catch (SQLException e)
> > > > {
> > > > throw new UnavailableException("error: " + e);
> > > > }
> > > > catch (Exception e)
> > > > {
> > > > throw new UnavailableException("error: " +e);
> > > > }
> > > > }
> > > > > > public void doPost(HttpServletRequest request, HttpServletResponse
> > > > response) throws ServletException, IOException
> > > > {
> > > > response.setContentType("text/plain");
> > > > PrintWriter out = response.getWriter();
> > > > > > //extract parameter information from register.jsp
> > > > > > String email1 = request.getParameter("email1");
> > > > String password1 = request.getParameter("password1");
> > > > String password2 = request.getParameter("password2");
> > > > String displayname = request.getParameter("displayname");
> > > > > > try
> > > > {
> > > > //make a callable statement for a stored procedure.
> > > > //It has four parameters
> > > > > > CallableStatement cstmt = dbConn.prepareCall(
> > > > "{call insertTheForm(?, ?, ?, ?)}");
> > > > > > //set the values of the stored procedure's input parameters
> > > > > > out.println("calling stored procedure . . .");
> > > > cstmt.setString(1, email1);
> > > > cstmt.setString(2, password1);
> > > > cstmt.setString(3, password2);
> > > > cstmt.setString(4, displayname);
> > > > //now that the input parameters are set, we can proceed to execute
the
> > > > insertTheForm stored procedure
> > > > > > cstmt.execute();
> > > > out.println("stored procedure executed");
> > > > }
> > > > > > catch (SQLException e)
> > > > {
> > > > throw new UnavailableException("error: " + e);
> > > > > > }
> > > > }
> > > > > > }
>|||
Quote:
Originally Posted by dinesh prasad
I'm trying to use a servlet to process a form, then send that data to
an SQL server stored procedure. I'm using the WebLogic 8 App. server.
I am able to retrieve database information, so I know my application
server can talk to the database.
I've determined the failure occurs when the the following statement is
executed: cstmt.execute(); (due to the failure of println statements
placed afterwards). I get the following error after trying to execute
the stored procedure call:
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored
procedure 'insertTheForm'
The username and password i'm using to connect is a Windows user with
admin rights. It is also associated with the Odbc connection--and of
course is a database user..with full rights. I have executable
permissions on the stored procedure set up as well. I did a microsoft
recommended registry fix as well (for a previous
error:http://support.microsoft.com/defaul...;en-us;Q238971).
Am I missing something? I posted my servlet code below.
Thanks for any help!
Dinesh
formHandlingServlet.class
--------
package showme;
/*
* formHandlingServlet.java
*
* Created on July 6, 2003, 7:01 PM
*/
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import java.text.DateFormat;
/**
*
* @.author Administrator
*/
public class formHandlingServlet extends HttpServlet {
private static final String email1 = "email";
private static final String password1 = "password1";
private static final String password2 = "password2";
private static final String displayname = "displayname";
Connection dbConn = null;
// create a persistent conneciton to the SQL server
public void init() throws ServletException
{
String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
String dbURL = "jdbc:odbc:Con2";
String usernameDbConn = "dinesh";
String passwordDbConn = "werty6969";
try
{
Class.forName(jdbcDriver).newInstance();
dbConn = DriverManager.getConnection(dbURL, usernameDbConn,
passwordDbConn);
}
catch (ClassNotFoundException e)
{
throw new UnavailableException("jdbc driver not found:" + dbURL);
}
catch (SQLException e)
{
throw new UnavailableException("error: " + e);
}
catch (Exception e)
{
throw new UnavailableException("error: " +e);
}
}
public void doPost(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException
{
response.setContentType("text/plain");
PrintWriter out = response.getWriter();
//extract parameter information from register.jsp
String email1 = request.getParameter("email1");
String password1 = request.getParameter("password1");
String password2 = request.getParameter("password2");
String displayname = request.getParameter("displayname");
try
{
//make a callable statement for a stored procedure.
//It has four parameters
CallableStatement cstmt = dbConn.prepareCall(
"{call insertTheForm(?, ?, ?, ?)}");
//set the values of the stored procedure's input parameters
out.println("calling stored procedure . . .");
cstmt.setString(1, email1);
cstmt.setString(2, password1);
cstmt.setString(3, password2);
cstmt.setString(4, displayname);
//now that the input parameters are set, we can proceed to execute the
insertTheForm stored procedure
cstmt.execute();
out.println("stored procedure executed");
}
catch (SQLException e)
{
throw new UnavailableException("error: " + e);
}
}
}
Hi,
You cannot call MS SQL Server stored procedure using the "call" verb. You need to use "exec" verb. CallableStatement cstmt = dbConn.prepareCall(
"{exec insertTheForm(?, ?, ?, ?)}");
Hope it helps you.
Sanjeev.
Wednesday, March 21, 2012
ExecuteReader: Connection property has not been initialized.
Private Sub VerifyNoDuplicateEmail()
Dim conn As SqlConnection
Dim sql As String
Dim cmd As SqlCommand
Dim id As Guid
sql = "Select UserID from SDCUsers where email='{0}'"
sql = String.Format(sql, txtEmail.Text)
cmd = New SqlCommand(sql, conn)
conn = New SqlConnection(ConfigurationSettings.AppSettings("cnSDCADC.ConnectionString"))
conn.Open()
Try
'The first this we need to do here is query the database and verify
'that no one has registed with this particular e-mail address
id = cmd.ExecuteScalar()
Response.Write(id.ToString & "<BR>")
Catch
Response.Write(sql & "<BR>")
Response.Write("An error has occurred: " & Err.Description)
Finally
If Not id.ToString Is Nothing Then
'The e-mail address is already registered.
Response.Write("Your e-mail address has already been registered with this site.<BR>")
conn.Close()
_NoDuplicates = False
Else
'It's safe to add the user to the database
conn.Close()
_NoDuplicates = True
End If
End Try
End SubWeb.Config
<appSettings>
<!-- User application and configured property settings go here.-->
<!-- Example: <add key="settingName" value="settingValue"/> -->
<add key="cnSDCADC.ConnectionString" value="workstation id=STEPHEN;packet size=4096;integrated security=SSPI;data source=SDCADC;persist security info=False;initial catalog=sdc" />
</appSettings>
Can anyone show me the error of my ways?
Thanks,
StephenPlease elaborate on "generating an error". An exact error mesage will probably go a long way in helping you correct your problem.
Also, please use parameters and not string concetenation to build your SQL commands!!
Instead of this:
sql = "Select UserID from SDCUsers where email='{0}'"
sql = String.Format(sql, txtEmail.Text)
cmd = New SqlCommand(sql, conn)
do something like this:
sql = "Select UserID from SDCUsers where email=@.email"
cmd = New SqlCommand(sql, conn)
cmd.Parameters.Add(New SqlParameter("@.email", SqlDbType.VarChar, 99)).Value = txtEmail.Text
Terri|||Terri,
The exact error is the title of the thread "ExecuteReader: Connection property has not been initialized."
Thanks,
Stephen|||Terri,
I tried the changes you suggested and still recieved the same error. After taking a little time off and coming back the to the problem I finally found the error of my ways. I was initializing the CMD object before the CONN object. Thanks for you help.
Stephen|||Oh yes, so you were. :-)
Please use parameters anyway -- that suggestion was something of an aside. And next time you post please include the error message. We could have helped you a lot quicker with that information!
Terri
Monday, March 19, 2012
ExecuteNonQuery: Connection property has not been initialized
first step that I am taking is creating a web form that can check the
username against a database to see if it already exists. I would it to do
this on the fly, if possible. When I execute my current code, I get the
following error:
ExecuteNonQuery: Connection property has not been initialized
Below is the code from the page itself:
--
<!-- #INCLUDE FILE="../include/context.inc" -->
<!-- #INCLUDE FILE="../include/db_access.inc" --
<script language="VB" runat="server"
Sub CheckButton_Click(Sender as Object, e as EventArgs)
Dim result As Int32
Dim cmd As OdbcCommand
cmd = new OdbcCommand( "(? = CALL CheckUserExists(?))", db_conn )
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add( "result", OdbcType.Int ).Direction =
ParameterDirection.ReturnValue
cmd.Parameters.Add( "@.userName", OdbcType.VarChar, 100 ).Value =
Request.Form("userName")
cmd.ExecuteNonQuery()
result = cmd.Parameters("result").Value
If result <> 1 Then
CheckResults.Text="<font color=""#ff0000"">Username already
exists!</font>"
Else
CheckResults.Text="<font color=""#009900"">Username is
available.</font>"
End If
end Sub
</script
<html><body>
<form runat="server">
<asp:TextBox id=userName runat="server" />
<asp:Button id=CheckButton runat="server" Text="Check Username"
onClick="CheckButton_Click" /
<p>
<asp:Label id=CheckResults runat=server />
</form>
</body></html>
--
Can anyone see why I might get this error? Here are some more details of
the error:
Line 15: cmd.Parameters.Add( "@.userName", OdbcType.VarChar, 100 ).Value =
Request.Form("userName")
Line 16:
*Line 17: cmd.ExecuteNonQuery()
Line 18: result = cmd.Parameters("result").Value
Thank You,
Jason WilliardHi Jason,
To me it seems that you are still using asp type techniques of data access. You will have to initate your dbconnection object before you can actually use any database related functions.
With your code you are missing ofdb_conn variable. Which I am presuming that you have in include file, but this will not work with asp.net.
HTH|||It's looks like your maye be geeting error because of following reason.
1.Your connection string is not correct or not open as we can't see when and where it was intialize/open .
2.check your store procdure and see if you are passing correct parameter,correct typen etc.
3.In your code it looks likes may be you have open your connection in db_access.inc but i don't think it's good approach .you should open the connection in same sub and close as soon as you you finished.Or if you want to write neat and clean code and also don't want to wire same code agaian then create data layer class where you can perform all the database realted operation.
Arvind Malik|||I made some changes to the page so that the db connection is all done from within the Sub. Below are the code changes that I made:
--
Sub CheckButton_Click(Sender as Object, e as EventArgs)
Dim db_conn_str As String
Dim db_conn As OdbcConnection
Dim resultAs Int32
Dim cmdAs OdbcCommand
Dim context_dsn_nameAs String = "adwarefilter"
db_conn_str = "dsn=" & context_dsn_name & ";"
db_conn = New OdbcConnection( db_conn_str )
db_conn.Open()
cmd = new OdbcCommand( "(? = CALL CheckUserExists(?))", db_conn )
cmd.CommandType = CommandType.StoredProcedure
--
Now I am getting a new error message:
--
Exception Details: System.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '='.
Source Error:
Line 28: cmd.Parameters.Add( "@.username", OdbcType.VarChar, 100 ).Value = Request.Form("userName")
Line 29:
Line 30: cmd.ExecuteNonQuery()
--
Any suggestions?|||What is this query: "(? = CALL CheckUserExists(?))", ? Where is the SQL Query?
Brian|||This is a call to a Stored Procedure. The SQL query is within the Stored Proc.|||I am pretty sure that your stored procedure call should look like this:
cmd = new OdbcCommand( "{CALL CheckUserExists(?)}", db_conn )
Note that I made these changes:
-- removed the question mark (?) and the equal sign (=)
-- changed a set of parentheses to a set of curly braces
But, I have a question -- why oh why are you using the System.Data.Odbc class instead of System.Data.SqlData? Are you using SQL Server 7?
Terri|||Actually, I hadn't noticed you are using a ReturnValue, sorry!! The question mark and equal sign can remain. The problem is likely just the use of parentheses instead of curly braces.
cmd = new OdbcCommand( "{? =CALL CheckUserExists(?)}", db_conn )
Terri