|
I first tried this code that passes a couple of parameters (part number, part type number) to a stored procedure. I can get this to run fine, insert the data into the table, no problem.
However, I want the stored procedure to return a field that is an auto-increment field called ID.
The stored procedure is syntactically correct, so I'm inclined to think it's something with the C# code.
The error I'm getting is: Parameter1 is not a parameter for procedure UpdatePart_Step_1_Test3
This is the c# code (note that I have so additional parameters that I declare but am not yet passing):
private void btnAdd_Click(object sender, System.EventArgs e)<br />
{<br />
strPartNumberInputReference = txtSearchPart.Text;<br />
strPartTypeID = ddPartType.SelectedValue;<br />
strDwgNumber = txtDwgNumber.Text;<br />
strDwgRevision = txtDwgRevision.Text;<br />
strDwgLocation = txtDwgLocation.Text; <br />
SqlParameter param1 = new SqlParameter("@PartNumber", strPartNumberInputReference); <br />
param1.Direction = ParameterDirection.Input;<br />
SqlParameter param5 = new SqlParameter("@PartTypeValue", strPartTypeID);<br />
SqlParameter param6 = new SqlParameter(@ID, SqlDbType.Int);<br />
param6.Direction = ParameterDirection.Output;<br />
IDOutput = SqlHelper.ExecuteNonQuery (this.connectionString, CommandType.StoredProcedure, "dbo.UpdatePart_Step_1_Test3", param1, param5, param6);<br />
test3 = param6.Value;<br />
<br />
}
Here is the stored proc:
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 PartsDB.dbo.Parts where PartNumber = @PartNumber)<br />
BEGIN<br />
<br />
insert into PartsDB.dbo.Parts<br />
(PartNumber, PartTypeID)<br />
<br />
VALUES<br />
(<br />
@PartNumber,<br />
@PartTypeValue<br />
)<br />
<br />
END<br />
SET @ID = @@IDENTITY<br />
<br />
return 0<br />
GO
Again, if I just send in param1 and param 5 (and change the stored proc so it's not expecting a third parameter) it works fine!
Any ideas what is causing the error? I'm not finding any documentation.
|
|
|
|
|
leckey wrote: SqlHelper.ExecuteNonQuery
This is obviously a helper class, what does the code look like ?
Your code knows far too much about your data layer, why have a helper method and pass it SQL Parameters ?
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
I think the helper is from the Microsoft Application Block for DAL
"Just about every question you've asked over the last 3-4 days has been "urgent". Perhaps a little planning would be helpful?"
Colin Angus Mackay in the C# forum
led mike
|
|
|
|
|
|
I'm not quite sure what you mean by the second part...
I also tried with a dataset (even though the sp is just an insert) and got the same error. If I don't try to return anything, it's fine.
The code in the helper class goes through quite a bit, so I'll try to paste it as the compiler goes through it.
Step 1:
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)<br />
{<br />
if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );<br />
<br />
using (SqlConnection connection = new SqlConnection(connectionString))<br />
{<br />
connection.Open();<br />
<br />
return ExecuteNonQuery(connection, commandType, commandText, commandParameters);<br />
}<br />
}
Step 2:
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)<br />
{ <br />
if( connection == null ) throw new ArgumentNullException( "connection" );<br />
<br />
SqlCommand cmd = new SqlCommand();<br />
bool mustCloseConnection = false;<br />
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection ); RUNS THIS FINE <br />
int retval = cmd.ExecuteNonQuery(); GETS STUCK ON THIS<br />
<br />
cmd.Parameters.Clear();<br />
if( mustCloseConnection )<br />
connection.Close();<br />
return retval;<br />
}
If there is a quick and painless other way to do this I'm all ears...or eyes really. I just want to get done with this!
|
|
|
|
|
leckey wrote: I'm not quite sure what you mean by the second part...
Your presentation layer shouldn't do more than call the middle tier with the data that needs storing. The middle tier should pass the data to a database layer, and inside THAT you should write code that knows what stored proc is being called.
The big question is, why are you using this Microsoft thingy ? What is it giving you ? I've done code that calls a stored proc and gets the out param heaps of times, but I have no idea on how best to use this Microsoft layer that you've got in place, or why you'd use a layer when your overall approach is not n-tiered at all.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
I haven't programmed in about 3 years and a programming friend recommended it to me.
Could you give me an example? The more specific the better...I'm not very bright.
|
|
|
|
|
An example of the call, or of proper n-tiered development ?
SqlCommand cmd = new SqlCommand("InsertTrack", Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@artistName", (artist == null) ? "" : artist);
cmd.Parameters.Add("@trackName", (track == null) ? "" : track);
cmd.Parameters.Add("@albumName", (album == null) ? "" : album);
cmd.Parameters.Add("@filePath", filePath);
cmd.Parameters.Add("@isVideo", isVideo ? 1 : 0);
cmd.Parameters.Add("@thumbPath", (thumbPath == null) ? "" : thumbPath);
cmd.Parameters.Add("@trackID", -1);
cmd.Parameters["@trackID"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
return (int) cmd.Parameters["@trackID"].Value;
That's a call out of the data layer of a video jukebox I wrote. It represents a complete static function in a class that has the Connection as a lazy initialised static member property.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
Christian Graus wrote: cmd.Parameters.Add("@trackID", -1);
Why -1 versus SqDbType.Int?
I'll be trying your version of code in a minute...be wary..I'll probably have more questions.
|
|
|
|
|
That code is a little over complex, it's passing in some values based on booleans. However, the add method takes a name and a value, not a value type, from the look of it.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
Okay, question time!
I have my connection in my web.config file and have this declared in my code behind:
private string connectionString = ConfigurationSettings.AppSettings["connectionString"].ToString();
So I changed my code to:
private object btnAdd_Click(object sender, System.EventArgs e)<br />
{<br />
strPartNumberInputReference = txtSearchPart.Text;<br />
strPartTypeID = ddPartType.SelectedValue;<br />
SqlCommand cmd = new SqlCommand ("UpdatePart_Step_1_Test3", connectionString);<br />
cmd.CommandType = CommandType.StoredProcedure;<br />
cmd.Parameters.Add ("@PartNumber", strPartNumberInputReference);<br />
cmd.Parameters.Add ("@PartTypeValue", strPartTypeID);<br />
cmd.Parameters.Add("@ID",SqlDbType.Int);<br />
cmd.Parameters["@ID"].Direction = ParameterDirection.Output;<br />
cmd.ExecuteNonQuery();<br />
return(int) cmd.Parameters["@ID"].Value;<br />
}
Problem 1: I bolded "connectionString"--the compiler is unhappy and not sure how to resolve this. Geting "cannot convert string..." so I assume I need to change the declaration...but what to?
|
|
|
|
|
It's actually an instance of a connection, which itself is built from the string.
SQLConnection is the class, I think.
// return(int) cmd.Parameters["@ID"].Value;
The return statement will work here, it will box the int and return it as an object. Why is this event handler trying to return an object, where was it called from ? An event handler returns void.
I doubt you want to return a boxed int tho, do you ? My method returned int and was inside a data layer, called from my business layer.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
I'm leaving in 10 min, so if you get them through in time, I'll be happy to help, otherwise it'll be tomorrow morning.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
leckey wrote: SqlParameter param6 = new SqlParameter(@ID, SqlDbType.Int);
Was this just a copy/paste error? I'm pretty sure @ID needs to be in quotation marks.
Also, you should use SCOPE_IDENTITY()[^] instead of @@IDENTITY to get the ID of the row you just inserted. It's not strictly necessary, but it's a good habit to get into. More details in the remarks section on the MSDN site.
Jon Sagara
When I grow up, I'm changing my name to Joe Kickass!
My Site | My Blog | My Articles
-- modified at 17:17 Wednesday 12th July, 2006
|
|
|
|
|
Jon, you are the winner!!! That was my problem. I need to get this done but will probably later take Christian's advice.
As Steve the Pirate says in Dodgeball...
"Gargh!"
|
|
|
|
|
Yeah, I missed that, although when he said, from what you said, I thought it was blowing up elsewhere.
Yes, you definately need to read up on seperation of the presentation tier, although your code will work, it's a design issue.
http://www.15seconds.com/issue/011023.htm[^]
looks like an OK place to start with a quick overview.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
Many thanks again for your help!
|
|
|
|
|
I am not sure if this can be done. I am writing a console application to ftp files. This will be called by a scheduling program. Is there a way to return a value if the transfer fails?
Lyman Ring
|
|
|
|
|
See if this works
static int Main(string[] args)
{
return 3;
}
"Just about every question you've asked over the last 3-4 days has been "urgent". Perhaps a little planning would be helpful?"
Colin Angus Mackay in the C# forum
led mike
|
|
|
|
|
Thanks, My next question would be how to check this value
|
|
|
|
|
Make a .cmd file and put this in it
<yourappname>
echo %ERRORLEVEL%
pause
then double click the .cmd file
"Just about every question you've asked over the last 3-4 days has been "urgent". Perhaps a little planning would be helpful?"
Colin Angus Mackay in the C# forum
led mike
|
|
|
|
|
Thank you very much for your help.
|
|
|
|
|
I am new to C# and have a class project to work on. I am trying to create an Amortization calulator the display the payment results based on the Time period thay have to repay the loan. The calculation part of it is working fine but I can not get it to display the result. Can I please get some help with this? See code Below:
<script runat="server">
protected void Button1_Click(object sender, EventArgs e)
{
Label6.Text = "Final Balance: " +
CalculateBalance(Convert.ToInt32(TextBox1.Text),
(Convert.ToDouble(TextBox2.Text) / 100),
Convert.ToInt32(TextBox3.Text),
Convert.ToInt16(DropDownList1.SelectedItem.Value)).ToString();
}
private string CalculateBalance(int Principal, double Rate, int Years, int Period)
{
double result;
double NumToBeRaised = (1 + Rate / Period);
result=Principal * System.Math.Pow(NumToBeRaised, (Years * Period));
return (result.ToString("C"));
}
private string AmortDisplay(double Principal, double results, int Years, int Period)
{
int AmortTime;
double AmortPayment;
double RemBalance;
string Output;
AmortTime = (Years * Period);
AmortPayment = (results / AmortTime);
RemBalance = -(Principal - AmortPayment);
TextBox4.Text = "Duration\t \tPayment\t \tRemaining Balance \n" + AmortTime + "\t\t" + Convert.ToString(AmortPayment) + "\t\t" + Convert.ToString(RemBalance) + "\n";
}
protected void Button2_Click(object sender, EventArgs e)
{
AmortDisplay(Convert.ToDouble(TextBox1.Text),
Convert.ToDouble(Label6.Text),
Convert.ToInt32(TextBox3.Text),
Convert.ToInt16(DropDownList1.SelectedItem.Value)).ToString();
}
</script>
ext="Principal ($)" Width="165px" BackColor="Transparent" Font-Size="Medium"></asp:Label>
<asp:TextBox ID="TextBox1" runat="server" Style="position: relative" BackColor="Silver"></asp:TextBox><br />
<br />
<asp:Label ID="Label3" runat="server" Style="position: relative" Text="Rate (%)" Width="165px" BackColor="Transparent" Font-Size="Medium"></asp:Label>
<asp:TextBox ID="TextBox2" runat="server" Style="position: relative" BackColor="Silver" TabIndex="1"></asp:TextBox>
<br />
<br />
<asp:Label ID="Label4" runat="server" Style="position: relative" Text="Years:" Width="165px" BackColor="Transparent" Font-Size="Medium"></asp:Label>
<asp:TextBox ID="TextBox3" runat="server" Style="position: relative" BackColor="Silver" TabIndex="2"></asp:TextBox><br />
<br />
<asp:Label ID="Label5" runat="server" Style="position: relative" Text="Compound Frequency:" Width="165px" BackColor="Transparent" Font-Size="Medium"></asp:Label>
<asp:DropDownList ID="DropDownList1" runat="server" Style="position: relative" BackColor="Silver" TabIndex="3">
<asp:ListItem Value="1">Annually</asp:ListItem>
<asp:ListItem Value="4">Quarterly</asp:ListItem>
<asp:ListItem Value="12">Monthly</asp:ListItem>
<asp:ListItem Value="365">Daily</asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:DropDownList><br />
<br />
<asp:Button ID="Button1" runat="server" Style="left: 9px; position: relative; top: 10px"
Text="Calculate" Width="207px" OnClick="Button1_Click" BackColor="Silver" TabIndex="4" Font-Size="Medium" /><br />
<br />
<br />
<br />
<asp:Label ID="Label6" runat="server" Style="position: relative" Width="165px"></asp:Label> <br />
<asp:TextBox ID="TextBox4" runat="server" Height="345px" Style="position: relative"
Width="871px"></asp:TextBox>
Greg
The Belizean Dan Dada!!!
|
|
|
|
|
Of course it doesn't display any result. You don't have any code that displays the result.
---
b { font-weight: normal; }
|
|
|
|
|
Hello,
If I have a method of type
public void myMethod()
{
}
And I create a delegate void NewDelegate(void)
What is the actual difference between the following calls and how they are handled by .NET and the OS
NewDelegate del = NewDelegate(myMethod);
del.BeginInvoke(null, null);
and
Thread t = new Thread(new ThreadStart(myMethod));
t.Start();
I know the delegate will use a thread from the thread pool, but will the thread t as well? What is the actual effect of one versus the other.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
