|
I had asked about output parameters yesterday as I had not worked with them before. I did get a response but I am still having some issues. Here is my current stored proc:
CREATE PROCEDURE [dbo].[UpdatePart_Step_1_Test] @PartNumber varchar (15),<br />
@PartTypeDescription varchar (60),<br />
@ID int OUTPUT<br />
AS<br />
declare @pt_id integer, @ThePartNumber integer<br />
<br />
if NOT EXISTS (Select * from Parts where PartNumber = @PartNumber)<br />
BEGIN<br />
<br />
SELECT @pt_id = pt.ID FROM PartTypes pt <br />
<br />
INNER JOIN Parts p on pt.ID = p.PartTypeID<br />
<br />
WHERE pt.Description = @PartTypeDescription<br />
<br />
insert into Parts<br />
(PartNumber, PartTypeID)<br />
<br />
VALUES<br />
(<br />
@PartNumber,<br />
@pt_id<br />
)<br />
<br />
<br />
END<br />
SELECT @ID=@@IDENTITY<br />
return 0<br />
GO
Here is the c# code that calls the stored proc. Note I have some additional parameters declared but I am not yet sending.
private void btnAdd_Click(object sender, System.EventArgs e)<br />
{<br />
strPartNumberInputReference = txtSearchPart.Text;<br />
strDwgNumber = txtDwgNumber.Text;<br />
strDwgRevision = txtDwgRevision.Text;<br />
strDwgLocation = txtDwgLocation.Text;<br />
<br />
ds2 = new DataSet();<br />
SqlParameter param1 = new SqlParameter("@PartNumber", strPartNumberInputReference); <br />
SqlParameter param2 = new SqlParameter("@DwgNumber", strDwgNumber);<br />
SqlParameter param3 = new SqlParameter("@DwgRevision", strDwgRevision);<br />
SqlParameter param4 = new SqlParameter("@DwgLocation", strDwgLocation);<br />
SqlParameter param5 = new SqlParameter("@PartTypeDescription", strPartTypeID);<br />
SqlParameter param6 = new SqlParameter(@ID, SqlDbType.Int);<br />
param6.Direction = ParameterDirection.Output;<br />
<br />
ds2=SqlHelper.ExecuteDataset(this.connectionString, CommandType.StoredProcedure,"dbo.UpdatePart_Step_1_Test", param1, param5);<br />
dt2 = ds2.Tables[0];<br />
}
The problem is that when I try to add I get the error: Procedure 'UpdatePart_Step_1_Test' expects parameter '@ID', which was not supplied
Okay, did some additional reseach and it appears I need to something like the following: (taken from a previous CodeProject post)
DECLARE @MyTitle varchar(50),<br />
@ytd int<br />
SET @MyTitle = 'Some Title'<br />
EXEC dbo.YtdSales @MyTitle, @ytd OUTPUT<br />
SELECT @ytd
I'm not sure where to insert these bits (set, exec, select)into my existing code.
Can someone help me or tell me what is wrong with the original sp?
Thanks!
|
|
|
|
|
Your call to ExecuteDataset references param5, where the parameter called @ID is actually param6. I think this is the problem!
Since your procedure actually does an INSERT, I'd look at using SqlCommand.ExecuteNonQuery since you're not actually returning a resultset. Filling a DataSet is overkill.
It often helps to prevent the client side from getting confused by putting SET NOCOUNT ON at the top of your procedures. This means that the rowcounts from any operations that don't actually return a result set aren't sent to the client. IIRC, if you don't use this SET statement, you can end up getting empty resultsets, which may appear (it's been a while since I did this) as empty DataTables in your DataSet.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Actually I need to pass all three. Thanks for catching that. Param 1 is the part number, param 5 is the part type, and param 6 the ID for output. So now I have:
ds2=SqlHelper.ExecuteDataset(this.connectionString, CommandType.StoredProcedure,"dbo.UpdatePart_Step_1_Test3", param1, param5, param6);
I revamped some earlier code. Basically the part type is a drop down box with a description, but now I send the actual partTypeID. So now my stored proc looks like:
CREATE PROCEDURE [dbo].[UpdatePart_Step_1_Test3]<br />
@PartNumber varchar (15),<br />
@PartTypeValue varchar (60),<br />
@ID int OUTPUT<br />
<br />
AS<br />
<br />
if NOT EXISTS (Select * from Parts where PartNumber = @PartNumber)<br />
BEGIN<br />
<br />
insert into Parts<br />
(PartNumber, PartTypeID)<br />
<br />
VALUES<br />
(<br />
@PartNumber,<br />
@PartTypeValue<br />
)<br />
<br />
END<br />
SELECT @ID = @@IDENTITY<br />
<br />
<br />
return 0<br />
GO
-- modified at 15:43 Wednesday 12th July, 2006
|
|
|
|
|
Hi,
This is how I code:
.aspx page --> BLL class --> DAL class --> BLL class --> .aspx page
In my stored procedure I usually do a test to see if a record has been inserted or updated. To test for an inserted record success, my code is:
-- Check for insert error
IF (@@ERROR <> 0)
BEGIN
SET @ErrorCode = -1; -- SQL Server error
GOTO CleanUp;
END
-- Commit transaction
IF (@TranStarted = 1)
BEGIN
SET @TranStarted = 0;
COMMIT TRANSACTION;
END
RETURN 0;
So if there happens to be an error it will return a -1. Now in my returned parameter, how do I throw this exception based on -1??? Is this the correct way to do it, or is there a better way of doing this? And where should I throw this exception, in my .aspx page, the BLL class, or the DAL class?
Please advise.
Regards
ma se
|
|
|
|
|
You can use @ErrCode as output parameter, @ErrCode value can be collected in .aspx.vb or class file by following codes:
Comm.Parameters.Add("@ErrCode", SqlDbType.Int).Direction = ParameterDirection.Output
Comm.ExecuteNonQuery()
dim returnvalue as integer=comm.Parameters("@result").Value
Kumar
|
|
|
|
|
Ok thanks, but what happens if I want to return @ErrCode, or my own error code, for example -1 or a -2, etc, or the ID of the new car?
|
|
|
|
|
I need to create database file(*.mdb) programmatically, how can I do it?
|
|
|
|
|
use CREATE DATABASE as below:
CREATE DATABASE [testDB] ON (NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\AraWorkFlow_Data.MDF' , SIZE = 3, FILEGROWTH = 10%) LOG ON (NAME = N'AraWorkFlow_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\AraWorkFlow_Log.LDF' , SIZE = 1082, FILEGROWTH = 10%)
|
|
|
|
|
|
You are asking how to make an .mdb (which is a MS Access database).
See this post: clickety[^]
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hello, is it possible to iterate trough all tables in a database with SQL syntax?
My problem is that i would like to programatically have the possibility to make an xml-backup of the entire database to a remote computer. But the number of tables is unknown during design time.
R.
Johan
|
|
|
|
|
You can use following select statement which return no of user tables available in database
select name from sysobjects where type='u'
name column give you table name
Kumar
|
|
|
|
|
There are some system catalogs that can help, but it varies from database vendor to database vendor. If you're using C++ there are some ATL DB client side helpers for this.
Steve S
Developer for hire
|
|
|
|
|
Some databases support INFORMATION_SCHEMA views you can query:
SELECT * FROM INFORMATION_SCHEMA.TABLES
then loop through the results (be sure to look at the field TABLE_TYPE to see what type of table it is
Now select the columns for each table:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Current table name'
|
|
|
|
|
Perfekt, this one was easy..
THX
|
|
|
|
|
Hi
I am developing a UserControl, I save this user control code to the database, which will be loaded dynamically to the Page.
In my user control code consist of following lines:
<asp:SqlDataSource ID="GetDataFromDatabase" runat="server" ConnectionString="<%$ ConnectionStrings:sConnectionString %>"
SelectCommand="SELECT field_defaultvalue FROM cp_productfields WHERE field_product_id='ProductID' AND field_type='DropDownList' AND field_Control_ID='ddVCAuflag'"
ProviderName="<%$ ConnectionStrings:sConnectionString.ProviderName %>"></asp:SqlDataSource>
At this moment, I am manually puting this data to data
base using "INSERT INTO" statement.
But I get following error:
Incorrect syntax near 'ProductID'.
This is because single quotation mark around ProductID (field_product_id='ProductID').
If I change this single quotation to double quotation (e.g. field_id="ProductID") then this works okay.
But in that scenario, SELECT statement fails while execution.
Can anyone please let me know how can I achieve both successfully?
Thanks in advance.
|
|
|
|
|
Did you try using :
"SELECT field_defaultvalue FROM cp_productfields WHERE field_product_id='" & ProductID & "' AND field_type='" & DropDownList & "' AND field_Control_ID='" & ddVCAuflag & "'"
??
thanks.
|
|
|
|
|
Hi,
I just have a couple of questions on the DAAB:
When using db.AddInParameter and the input parameter is an empty string, does it automatically add DBNull.Value to the coloumn? Or should I rather then create a method that checks for an empty string, if it is empty then add DBNull.Value to that coloumn (if it accepts NULL values).
How to get the returned value and throw an exception to the user? I usually have a @ReturnValue parameter, with ParameterDirection.ReturnValue. I was thinking of not doing it this way but rather something like:
using (dbCommand = db.GetStoredProcCommand(strSProc))
{
db.AddInParameter(dbCommand, "@CarName", DbType.String, objCar.Name);
// Rest of the parameters
IDataReader rdr = db.ExecuteReader(dbCommand);
if (rdr.Read())
{
// Do convertions here of what was returned from the stored procedure
}
}
Please can someone help advise.
Regards,
ma se
|
|
|
|
|
String or any parameter except int accept Null value for empty parameter.
Kumar
|
|
|
|
|
I have the following using block (not all code is displayed):
try
{
using (dbCommand = db.GetStoredProcCommand("sp_GetAllCars"))
{
rdr = db.ExecuteReader(dbCommand);
while (rdr.Read())
{
Car objCar = new Car();
objCar.ID = rdr.GetInt32(0); // Required
objCar.Name = rdr.GetString(1); // Required
// Add Car instance to array
carList.Add(objCar);
}
// Close the reader object
rdr.Close();
}
}
catch (SqlException ex)
{
HandleSQLError(ex, "sp_GetAllCars");
}
finally
{
dbCommand.Dispose();
}
What does the using block close and dispose of, because I am thinking that I am closing uneccessary stuff. I always thought that the using block only closed this that were declared in the brackets like:
using (...only closed what inserted here...)
{
...
}
Please can someone comment.
Regards,
ma se
|
|
|
|
|
Hi
I don't think you need the
ma se wrote: dbCommand.Dispose();
dbCommand.Dispose(); in the finally block because the using block would have released the dbcommand
|
|
|
|
|
Thanks I thought so, but what about db and rdr, must I still close rdr? Doesn't the using block take care of this?
|
|
|
|
|
Hi
i am not completely sure what you are trying to do but i would have implemented your code this way:
try
{
using (dbCommand = db.GetStoredProcCommand("sp_GetAllCars"))
{
rdr = dbCommand.ExecuteReader(); //db.ExecuteReader(dbCommand);
while (rdr.Read())
{
Car objCar = new Car();
objCar.ID = rdr.GetInt32(0); // Required
objCar.Name = rdr.GetString(1); // Required
// Add Car instance to array
carList.Add(objCar);
}
// Close the reader object
rdr.Close();
}
}
catch (SqlException ex)
{
HandleSQLError(ex, "sp_GetAllCars");
}
finally
{
}
you need to close rdr since it has exclusive access to the connection and without closing it, you can not use the underlying connection. at the end of the using, the dbCommand will be release. what i think you might need in the finally is to close the underlying connection. but that also depends on if you are not using the connection else where within the function after the finally block. i generally don't dispose my connections. but i use the Close() option since this will return the connection to the pool rather than releasing all its resources which happens with dispose().
-- modified at 5:31 Wednesday 12th July, 2006
|
|
|
|
|
What do you mean by the underlying connection? Is it the db? Do I have to close/ dispose of this db as well? If so where?
I defined db like this:
// Load the default database
Database db = DatabaseGlobals.GetDefaultDatabase;
|
|
|
|
|
If you use a using for the datbase connection and for the data reader then you don't need the calls to close or dispose, e.g.,
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
string text = "spReadCustomers";
SqlCommand command = new SqlCommand();
command.Connection = cn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = text;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int firstNameIdx = reader.GetOrdinal("FirstName");
int lastNameIdx = reader.GetOrdinal("LastName");
string firstName = reader.GetString(firstNameIdx);
string lastName = reader.GetString(lastNameIdx);
}
}
}
Kevin
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
