SqlParameter With Parametername Is Not Contained By This SqlParameterCollection
Sep 19, 2007
Hello All,
I'm having a problem tracking down why my app is not instantiating a parameter in a sqldatasource, when I'm expecting it to do so.
When the app fails, this info is displayed:
Server Error in '/' Application.
--------------------------------------------------------------------------------
An SqlParameter with ParameterName @createdby is not contained by this SqlParameterCollection.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.IndexOutOfRangeException: An SqlParameter with ParameterName @createdby is not contained by this SqlParameterCollection.
Source Error:
Line 30: Private Sub SqlDataSource1_Deleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Deleting
Line 31: e.Command.Parameters.Item("@createdby").Value = CType(Membership.GetUser.ProviderUserKey, Guid)
Line 32:
Line 33: End Sub
Source File: ...filename... Line: 31
Stack Trace:
[IndexOutOfRangeException: An SqlParameter with ParameterName @createdby is not contained by this SqlParameterCollection.]
System.Data.SqlClient.SqlParameterCollection.GetParameter(String parameterName) +713105
System.Data.Common.DbParameterCollection.get_Item(String parameterName) +7
diabetes.reading.SqlDataSource1_Deleting(Object sender, SqlDataSourceCommandEventArgs e) in C:Documents and SettingsAlbertMy DocumentsVisual Studio 2005Projectsdiabetesdiabetesdiabetes
eading.aspx.vb:31
System.Web.UI.WebControls.SqlDataSourceView.OnDeleting(SqlDataSourceCommandEventArgs e) +114
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) +682
System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback) +75
System.Web.UI.WebControls.FormView.HandleDelete(String commandArg) +839
System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +556
System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +95
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +109
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +174
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Here's the datasource:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DiabetesOne %>"
DeleteCommand="reading_delete" DeleteCommandType="StoredProcedure" >
<DeleteParameters>
<asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" />
<asp:Parameter Name="rid" Type="Int32" />
<asp:Parameter Name="createdby" Type="Object" />
</DeleteParameters>
</asp:SqlDataSource>
Here's the beginning of the stored procedure showing the parameters:
CREATE PROCEDURE [dbo].[reading_delete]
@rid int,
@createdby sql_variant
AS
set nocount on;
begin try
...the procedural code...
Here's debugger capture, pointing to same issue:
System.IndexOutOfRangeException was unhandled by user code
Message="An SqlParameter with ParameterName @createdby is not contained by this SqlParameterCollection."
Source="System.Data"
StackTrace:
at System.Data.SqlClient.SqlParameterCollection.GetParameter(String parameterName)
at System.Data.Common.DbParameterCollection.get_Item(String parameterName)
at diabetes.reading.SqlDataSource1_Deleting(Object sender, SqlDataSourceCommandEventArgs e) in C:... at System.Web.UI.WebControls.SqlDataSourceView.OnDeleting(SqlDataSourceCommandEventArgs e)
at System.Web.UI.WebControls.SqlDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues)
at System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback)
I've inspected the sqldatasourcecommandeventargs command.parameters in the deleting method during debugging and could not find the parameter in the array, although I "think" it should be because it's explicitly stated in the sqldatasource parameters declarations. I see two parameters in the array, @RETURN_VALUE and @RID, both of which are expected and coincide with the declaration in the stored procedure and the sqldatasource.
View 2 Replies
ADVERTISEMENT
Mar 23, 2007
Stored procedure:1 ALTER PROCEDURE [dbo].[insert_DagVerslag]
2 -- Add the parameters for the stored procedure here
3 @serverID int,
4 @datum datetime,
5 @ID int OUTPUT
6 AS
7 BEGIN
8 -- SET NOCOUNT ON added to prevent extra result sets from
9 -- interfering with SELECT statements.
10 SET NOCOUNT ON;
11 -- Insert statements for procedure here
12 BEGIN TRANSACTION
13 INSERT Log ( serverID, datum)
14 VALUES( @serverID, @datum)
15 COMMIT TRANSACTION
16 SET @ID = @@identity
17 END
18
Method who is calling the stored procedure and causes the error1 public void AddDagVerslag(Historiek historiek)
2 {
3 SqlConnection oConn = new SqlConnection(_connectionString);
4 string strSql = "insert_DagVerslag";
5 SqlCommand oCmd = new SqlCommand(strSql, oConn);
6 oCmd.CommandType = CommandType.StoredProcedure;
7 oCmd.Parameters.Add(new SqlParameter("@serverID", SqlDbType.Int)).Value = historiek.ServerID;
8 oCmd.Parameters.Add(new SqlParameter("@datum", SqlDbType.DateTime)).Value = historiek.Datum;
9 oCmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
10
11 oCmd.Parameters["@ID"].Direction = ParameterDirection.Output;
12
13 try
14 {
15 oConn.Open();
16 int rowsAffected = oCmd.ExecuteNonQuery();
17 if (rowsAffected == 0) throw new ApplicationException("Fout toevoegen historiek");
18 oCmd.Parameters.Clear();
19 historiek.HistoriekID = System.Convert.ToInt32(oCmd.Parameters["@ID"].Value);
20
21 foreach (HistoriekDetail hDetail in historiek.LstHistoriekDetails)
22 {
23 AddDagVerslagCategorie(historiek.HistoriekID, hDetail);
24 }
25 }
26 catch (Exception ex)
27 {
28 throw new ApplicationException("Fout toevoegen historiek : " + ex.Message);
29 }
30 finally
31 {
32 if (oConn.State == ConnectionState.Open) oConn.Close();
33 }
34 }
Whats going wrong, i've added the ID parameter my ParameterCollection... so why cant it be found
View 5 Replies
View Related
Mar 27, 2007
Hi,What I am trying to do is to get the new ID for every record is inserted into a table. I have a For...Each statement that does the loop and using the sqldatasource to so the insert. <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ myConnectionString%>">
<InsertParameters>
<asp:Parameter Name="NewID" Type="int16" Direction="output" />
</InsertParameters>
</asp:SqlDataSource> Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim sqlSelect As String
Session("Waste_Profile_Num") = 2
Session("Waste_Profile_Num2") = 5
sqlSelect = "SELECT Range, Concentration, Component_ID FROM Component_Profile WHERE (Waste_Profile_Num = " & Session("Waste_Profile_Num").ToString() & ")"
SqlDataSource1.SelectCommand = sqlSelect
Dim dv As Data.DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), Data.DataView)
For Each dr As Data.DataRow In dv.Table.Rows
sqlSelect = "INSERT INTO Component_Profile (Waste_Profile_Num, Range, Concentration, Component_ID) "
sqlSelect &= "VALUES (" & Session("Waste_Profile_Num2").ToString() & ", @Range, @Concentration, @Component_ID); SELECT @NewID = @@Identity"
SqlDataSource1.InsertCommand = sqlSelect
'SqlDataSource1.InsertParameters.Add("Waste_Profile_Num", Session("Waste_Profile_Num2").ToString())
SqlDataSource1.InsertParameters.Add("Range", dr("Range").ToString())
SqlDataSource1.InsertParameters.Add("Concentration", dr("Concentration").ToString())
SqlDataSource1.InsertParameters.Add("Component_ID", dr("Component_ID").ToString())
SqlDataSource1.Insert()
SqlDataSource1.InsertParameters.Clear()
MsgBox(Session("NewID").ToString())
Next
End Sub
Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted
Session("NewID") = e.Command.Parameters("@NewID").Value.ToString
End Sub What I have done so far is to display the new id and I am going to use that return id later. However, the first time through the loop, I am able to get the new id but not the second time. So, I wonder, how do I every single new id each time the INSERT statement is executed?STAN
View 5 Replies
View Related
Jan 5, 2007
I have a site which works fine with an Access DB, I now want to upsize it to use a SQL DB... I have changed my OleDB Commands etc... And Used SqlCommands and everything seems fine..Only one problem I have and its really stumped me... I always used SQLDataSource for the app even with the AccessDB, as I knew I would be upsizing at some point. We I have the below SQLDataSource 1 <asp:SqlDataSource ID="RandomBusinessDataSource" runat="server" 2 ConnectionString="<%$ ConnectionStrings:SQLDataBaseConnectionString %>" 3 SelectCommand="SELECT TOP (1) intBusinessID, txtBusinessName, intSubCatID, intCatID, txtTelNo, txtPostCode, txtWebAdd, txtReferred, bitBusinessShow, txtLong, txtLat, memBusinessDesc, intIPAddress, bitBusinessPaid FROM tblBusiness WHERE (intBusinessID = @Param1)" OnSelecting="RandomBusinessDataSource_Selecting">4 <SelectParameters>5 <asp:Parameter Name="Param1" Type="Int32" />6 </SelectParameters>7 </asp:SqlDataSource> Very Simple.... I have my function that is creating my random number and returning a VALID intbusinessID ... And now I have this OnSelecting event1 Protected Sub RandomBusinessDataSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)2 e.Command.Parameters("Param1").Value = MyNewRandomNum3 End Sub
But when I try and run the page I get the following error (Don't forget this page IS working using the SQLDataSource and an Access DB??)An SqlParameter with ParameterName 'Param1' is not contained by this SqlParameterCollection. I am a bit confused?? why would it say this now... Yet it works fine if I just change the connectionString to the access DB?? Any helps very much appreciated... Thanks
View 3 Replies
View Related
Oct 24, 2005
Hi!
I'm doing a class for working with MSDE.
My goal (as you understood) is to separate business and data layers.
I'm facing the following problem:
I have to use something like cmd.Parameters.Add("@Name", value) in my business layer to avoid using
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 16, value) where you can see that 'SqlDbType.NVarChar' doesn't respect the 'rules' for separating Data/Business layer.
Does it cost a lot to use a 'light' constructor (more job for the SQL engine to convert datas)?
Do I have to do a special function in my 'Data class' to convert an Int32 to a SqlDbType.Int for example?
I could see that Parameters.Add is deprecated and I have to use Parameters.AddWithValue in .Net 2 when you have only 2 parameters.
Is there a better way to handle that?
Thanks for your replies ;)
View 16 Replies
View Related
Aug 16, 2004
error:
Another SqlParameterCollection include ParameterName“@Id� SqlParameter?
if I remove one of " RunProc("test",param);" ,there will be right
private void Page_Load(object sender, System.EventArgs e)
{
SqlParameter param = new SqlParameter("@Id", SqlDbType.Int);
param.Value = 1;
RunProc("test",param);
RunProc("test",param); //if i remove this line ,it's OK
}
public void RunProc(string procName, SqlParameter pram)
{
SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
con.Open();
SqlCommand cmd = new SqlCommand(procName, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(pram);
cmd.ExecuteNonQuery();
con.Close();
}
View 1 Replies
View Related
Dec 6, 2004
I currently have a connection class that handles all of my database connectivity. What I am trying to do is build a SqlParameterCollection object on one page, then pass that object to my connection class. Is it possible to do this? I am not getting any compilation errors, but I can not get the parameters to be recognized. Here is what I am trying to do:
Page 1:
string sql = null;
conn.strConn = connectionstring;
sql = "sqlstring";
SqlParameterCollection newcollect = null;
newcollect.Add("@Switch",1);
conn.OpenReader(sql, newcollect);
while (conn.DR.Read())
{
read data onto page here...
}
conn.CloseReader();
Page 2 (connection class) :
public void OpenReader(string sql, SqlParameterCollection collect)
{
Conn = new SqlConnection(strConn);
Conn.Open();
Command = new SqlCommand(sql,Conn);
Command.Parameters.Add(collect); <------This is the root of my question
Command.CommandTimeout = 300;
// executes sql and fills data reader with data
DR = Command.ExecuteReader();
}
Can you do this??? And if so, can anyone tell me why the statement will not return any data? The procedure works perfectly, and will work if I use the standard way of passing the parameters to the command statement.
View 4 Replies
View Related
May 30, 2008
suppose,the type of the stored procedure's paramters is varchar .I hate to add parameterNames and types.If i can read the string of the stored procedure the get the paramterNames by operating text?
public void storeOperate(string stringParameter,string name) { string[] strs=stringParameter.Split('&'); SqlConnection conn = new SqlConnection(getConnectionString.getconnectionString()); SqlCommand cmd=new SqlCommand(name,conn); cmd.CommandText=name; cmd.CommandType=CommandType.StoredProcedure; foreach(string str in strs) { cmd.Parameters.Add(".....",SqlDbType........).Value=str; //my trouble } conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); cmd.Dispose(); }
View 2 Replies
View Related
Jun 16, 2006
I'm using parameters to record into database like:
SqlParameter paramId_empresa = new SqlParameter("@Id_empresa", SqlDbType.Int, 4);
paramId_empresa.Value = idEmpresa;
myCommand.Parameters.Add(paramId_empresa);
And set sql server field to allow null values and default value as (null)
Trouble is I'm getting an Input string was not in a correct format. when the field is blank
Line 182:Line 183: myConnection.Open();Line 184: myCommand.ExecuteNonQuery();Line 185: myConnection.Close();Line 186: }
Thanks a lot for any help
View 1 Replies
View Related
Jun 20, 2006
I am unable to set the value of my SqlParameter unless it is to a string.sqlCmd.Parameters.Add('@myParameter', SqlDbType.Bit).Value := false;
Setting it to false or 0 results in error: "Incompatible types: 'Object' and 'Boolean' (or 'Integer')"
I've tried to cast them as objects and that didn't work. I don't understand why something like 'false' (as a string) will.
FYI: I'm using Delphi.NET and hating it.
View 1 Replies
View Related
Feb 13, 2007
Im trying to execute the following:mySqlCmd.Parameters.Add("@Parent_ID", SqlDbType.Int).Value = (sectionUpdate.iSection.ParentID == 0 ? DBNull.Value : myParentID); However i get an error that Null cant be converted to Int. Is there any way i can uyse the SqlParameters approach but pass in a Null value for the field or must i wrap the entire connection within a IF/ELSE statement one containing a hard-coded NULL within the query and the other as a standard parameter with a proper int value? Thanks
View 1 Replies
View Related
Mar 26, 2007
Hi I am writing an app in flash which needs to hook up to MS SQL via asp.I need the code below to pass the var (ptodaysDate) to the sql statement. I can hard code it in and it works great but I really need to pass the var from flash.Pulling my hair out here, not much left to go.Any help greatly appreciated.---------------------------------------------- [WebMethod] public Schedule[] getSchedule(int ptodaysDate) { SqlDataAdapter adpt = new SqlDataAdapter("SELECT scheduleID, roomName, eventType,unitName,groupName,staffName,staffName2,theDate,theEnd FROM tb_schedule Where theDate >= @rtodaysDate", connString); SqlParameter rtodaysDate = new SqlParameter("@rtodaysDate", ptodaysDate); DataSet ds = new DataSet(); ArrayList al = new ArrayList(); adpt.Fill(ds); foreach (DataRow row in ds.Tables[0].Rows) { Schedule obj = new Schedule(); obj.scheduleID = (int)row["scheduleID"]; obj.roomName = (string)row["roomName"]; obj.eventType = (string)row["eventType"]; obj.unitName = (string)row["unitName"]; obj.groupName = (string)row["groupName"]; obj.staffName = (string)row["staffName"]; obj.staffName2 = (string)row["staffName2"]; obj.theDate = (string)row["theDate"]; obj.theEnd = (string)row["theEnd"]; al.Add(obj); } Schedule[] outArray = (Schedule[])al.ToArray(typeof(Schedule)); return outArray; } public class Schedule { public int scheduleID; public string roomName; public string eventType; public string unitName; public string groupName; public string staffName; public string staffName2; public string theDate; public string theEnd; }
View 2 Replies
View Related
Feb 21, 2008
The FULL System.Data.SqlDbType reference seems unnecessary? Is there something I am doing wrong or something that should be added to Web.Config? If I try ONLY ",SqlDbType," it will not compile?
MY CODE: SqlParameter AName;
AName = myCommand.Parameters.Add("@AName",System.Data.SqlDbType.VarChar,50);
View 2 Replies
View Related
Jul 10, 2004
Hi,
I am getting the following error:
The SqlParameter with ParameterName '@pk' is already contained by another SqlParameterCollection
If I could work out what code to post I would, but I can say that I am managing my Sql data in my code by caching small arrays of SqlParameter objects as pulled from the database. If I need to update the DB I change the cached SqlParameter and re-insert it. If I perform a SELECT I check the cache first to see if I already have the SqlParameter.
However, currently, I am experiencing the above error when performing a select, then later an update, followed by another update.
Would I be correct in saying that a SqlParameter object can only be used once for a database operation and should be discarded? Would I be correct if I said that the SqlCommand object should be discarded? I am barking up the wrong tree entirely?
Distressed in DBLand,
Matt.
View 2 Replies
View Related
Aug 5, 2004
Is it possible to have a SqlParameter setup as an Output, and get it to return @@Identity without using a stored procedure?
I have an INSERT statement, written as TEXT in my code (at the time being, I cannot create stored procedures). I'm trying to find out how to return the created IDENTITY that was generated.
Can someone please explain to me how this works?
Thanks.
View 1 Replies
View Related
Nov 28, 2007
hi
I am creating a stroed procedure which suppose to update an image column of the given row. I have created the same procedures before which worked fine with texts and numbers but not with Images.
this procedure get table name,row Id, column name of the image, and image data. like:
Create PROCEDURE [dbo].[spImageUpd]
(
@TableName varchar(100),
@Id int,
@ImageColName nvarchar(100),
@ImageData image
)
begin
??????
end
I tryed to use
set @sql='update '+ @TableName+ ' set '+@ImageColName + '=' + @ImageDt' + ' where Id='+ cast(@Id as nvarchar(10))
EXEC sp_executesql
@query = @sql,
@params = N'@ImageDt">N'@ImageDt Image',
@ImageDt = @ImageData
but sp_executesql just accept text as params. is it possible to fix this problem?
thank you in advance
regards
View 3 Replies
View Related
Jul 25, 2006
hi ;
I am abut searching in a database that html data stored in it, I want to search something like this: search me
And it stored like this: search <b> me </b>
I don’t know how to remove html tags on stored procedure,
It will be great if you direct me to a search stored procedure that handle this it rather make the sorting and paging stuff too .
tanks
View 4 Replies
View Related
Aug 2, 2006
I am just learning ASP.net, been reading over data grids but I am having a tough time understanding what the parameter line would be used for. I have read the MSDN regarding parameters, they seem to make sense. But the whole System.Byte(0) I cannot understand.
sqlUpdateCommand1 = new SqlCommand();
sqlUpdateCommand1.Parameters.Add(new SqlParameter("@Original_SSN", SqlDbType.VarChar, 11, ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "SSN", DataRowVersion.Original, null));
Any pointers or tutorials regarding this is much appreciated.
Thanks,
Rishi Dhupar
View 1 Replies
View Related
Feb 6, 2005
Below I have pasted the error message, hidden form fields, function code and even the stored procedure code. I'd be most grateful if anyone can show me the error of my ways!
Thanks,
theSpike
I'm calling a stored procedure and besides several varchar fields, I'm passing two integer values (employeeID and businessID). These values come from hidden fields on a form and it's the businessID that throws the format error. I call the same stored procedure for both insert and update (the SP evaluates employeeID to decide btwn insert/update) and the insert works perfectly, but the update throws the format exception error. The only difference is that the employeeID is 0 (zero) for an insert and > 0 when it's updating. I strikes me as particularly bizzare that the line that throws the exception is the businessID, not the employeeID because the businessID is the same value for both insert and update. It's the employeeID that is subject to change.
The error:
Exception Details: System.FormatException: Input string was not in a correct format.
Source Error:
Line 147: //businessID
Line 148: SqlParameter businessID = new SqlParameter("@businessID",SqlDbType.Int,4);
Line 149: businessID.Value = Convert.ToInt32(hdnBusinessID.Value);
Line 150: businessID.Direction = ParameterDirection.Input;
Line 151: theCommand.Parameters.Add(businessID);
I'm calling the SAME function for the insert and update, the same hiddent form fields exist
<input name="hdnBusinessID" id="hdnBusinessID" type="hidden" value="1" />
<input name="hdnEmployeeID" id="hdnEmployeeID" type="hidden" value="7" />
Here's the function that get's called:
void btnAddEmployee_Click(object sender, EventArgs e) {
int intNewEmployeeID;
string connectionstring = ConfigurationSettings.AppSettings["ConnectionString"];
SqlCommand theCommand = new SqlCommand();
theCommand.CommandText = "AMS_EmployeeSave";
theCommand.Connection = new SqlConnection(connectionstring);
theCommand.CommandType = CommandType.StoredProcedure;
// Add parameters to SqlCommand
//employeeID
SqlParameter empID = new SqlParameter("@employeeID",SqlDbType.Int,4);
empID.Value = Convert.ToInt32(hdnEmployeeID.Value);
empID.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(empID);
//businessID
SqlParameter businessID = new SqlParameter("@businessID",SqlDbType.Int,4);
businessID.Value = Convert.ToInt32(hdnBusinessID.Value);
businessID.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(businessID);
//title
SqlParameter title = new SqlParameter("@title",SqlDbType.Char,5);
title.Value = txtTitle.Text.ToString();
title.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(title);
//firstname
SqlParameter firstName = new SqlParameter("@firstName",SqlDbType.VarChar,25);
firstName.Value = txtFirstName.Text.ToString();
firstName.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(firstName);
//middleName
SqlParameter middleName = new SqlParameter("@middleName",SqlDbType.VarChar,15);
middleName.Value = txtMiddleName.Text.ToString();
middleName.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(middleName);
//LastName
SqlParameter lastName = new SqlParameter("@lastName",SqlDbType.VarChar,25);
lastName.Value = txtLastName.Text.ToString();
lastName.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(lastName);
//suffix
SqlParameter suffix = new SqlParameter("@suffix",SqlDbType.Char,5);
suffix.Value = txtSuffix.Text.ToString();
suffix.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(suffix);
//email
SqlParameter email = new SqlParameter("@email",SqlDbType.VarChar,80);
email.Value = txtEmail.Text.ToString();
email.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(email);
//password
SqlParameter password = new SqlParameter("@password",SqlDbType.Char,20);
password.Value = txtPassword.Text.ToString();
password.Direction = ParameterDirection.Input;
theCommand.Parameters.Add(password);
//newEmployeeID output parameter
SqlParameter newEmployeeID = new SqlParameter("@newEmployeeID",SqlDbType.Int,4);
newEmployeeID.Direction = ParameterDirection.Output;
theCommand.Parameters.Add(newEmployeeID);
theCommand.Connection.Open();
theCommand.ExecuteNonQuery();
intNewEmployeeID = Convert.ToInt16(theCommand.Parameters["@newEmployeeID"].Value);
theCommand.Connection.Close();
if(intNewEmployeeID == -1){
lblSaveResult.Text = "There was a problem saving the employee information";
}
else if(hdnEmployeeID.Value == "0"){
lblSaveResult.Text = "New employee successfully added";
}
else{
lblSaveResult.Text = "Employee successfully updated";
}
lblSaveResult.Visible = true;
}
STORED PROCEDURE
CREATE PROCEDURE [dbo].[xxxx]
(@employeeID int,
@businessID int,
@title char(5),
@firstName varchar(25),
@middleName varchar(15),
@lastName varchar(25),
@suffix char(5),
@email varchar(80),
@password char(20),
@newEmployeeID int output)
AS
declare @alreadyExists int
-- passed in ID = 0 means they're adding a new employee so do insert
if @employeeID = 0
begin
select @alreadyExists = count(*) from dbo.AMS_employee where emp_email = @email
if @alreadyExists = 0
begin
insert into AMS_employee (emp_businessID, emp_title, emp_firstName, emp_middleName, emp_lastName, emp_suffix, emp_email, emp_password)
values (@businessID, @title, @firstName, @middleName, @lastName, @suffix, @email, @password)
select @newEmployeeID = @@identity
end
else -- there is already an employee with that email address
select @newEmployeeID = -1
end
-- passed in ID > 0 means they're updating existing employee so do update
else if @employeeID > 0
begin
update AMS_employee set
emp_title = @title,
emp_firstName = @firstName,
emp_middleName = @middleName,
emp_lastName = @lastName,
emp_suffix = @suffix,
emp_email = @email
where emp_ID = @employeeID
select @newEmployeeID = @employeeID
-- only update the password if it one was provided
if len(rtrim(@password)) > 0
begin
update AMS_employee set emp_password = @password where emp_ID = @employeeID
end
end
GO
View 2 Replies
View Related
Mar 29, 2007
Hi,
i don't know if this is the right forum to post to, but here i go:
I'm having a problem...
I need to delete several records having as criteria their pk.
The deletion is made using SqlParameter in the code and a stored procedure on server.
as far as i know, i can do this, either using a IN list:
- build and pass the list of id's from code to the sp:
delete from tbl where attachment.id in (1,2,3)
OR
- execute a simple delete several times.
So far i tried first option, but i get errors in converting the list to ints; that is what the sql engine says.
I prefer this option because i think is faster and requires less roundtrips to the sql server.
My questions are:
1. how do i build a sane IN list?
2. is it worth to use a delete in a loop? the records that must be deleted are 5 at most, per job.
1 job can have [0..5] attachments.
Thank you,
Daniel
View 2 Replies
View Related
Mar 13, 2014
I would like to perform an audit of weak passwords, which is well documented for sql users. Using the same methodology, I should be able to audit weak passwords for contained users. To accomplish this, I must be able to find the location of the password hashes for the contained users.
I have looked at sys.syslogins and sys.sql_logins, which both have the password hash for server level users, but not contained users. I was able to find sys.sysusers, which does contain contained users, but no password hash.
What is the location of the password hashes for contained users?
View 0 Replies
View Related
Aug 1, 2006
I know that anything in a CDATA section will be ignored by an XML parser. Does that hold true for the SSIS XML Source?
I am trying to import a large quantity of movie information and all of the reviews, synopsis, etc are contained in CDATA. example:
<synopsis size="100"><![CDATA[Four vignettes feature thugs in a pool hall, a tormented ex-con, a cop and a gangster.]]></synopsis>
Sounds like a good one, no?
The record gets inserted into the database however it contains a NULL in the field for the synopsis text. I would imagine that the reason for this would fall at the feet of CDATA's nature and that SSIS is ignoring it.
Any thoughts would be appreciated. Thanks.
View 4 Replies
View Related
Aug 2, 2006
We've got an app that is in final testing so we can't go around forcing SqlDbType.VarChar. Is there any way to make SqlParameter default to varchar if you don't explicitly set a type for a string value? It's bad for our db index usage because they're all varchar and nvarchar forces a convert_implicit messing up performance.
View 1 Replies
View Related
Nov 26, 2005
Hi, I have a Table that contains SQL queries in one of its columns.I need to execute those query and finally want to retrive the result in another table i cannot use Cursors,Its working extremely slow, near about 1 min only for 2000 rows.Please tell me how can i minimize my time.Or any solution (without cursor) for such problem.Please help its very urgent.e.g Say TableContainQuery(PKID,QueryField) Now above table have 10000 of records, I need to retrieve data from by executing queries contain in above table. I am using SQL Server.
Regards,Dheeraj
View 3 Replies
View Related
May 15, 2014
Where is located the hash password for the contained database users?I have a script that prints all creating statement so that a Dev environment security can be reapply after a prod data refresh but I can't find the table containing the hash password when the user is "with login" for contained database.
View 4 Replies
View Related
Nov 20, 2014
As far as I know temp tables/objects will be created inside the default filegroup of the partially contained database and not in tempdb. Is it possible to either define a set of files dedicated to temp objects or define a second partially contained database dedicated to temp objects like tempdb?
View 0 Replies
View Related
Jul 29, 2015
I have a SQL Server 2014 database that is partially contained. I am trying to connect to the database via Excel using a contained user login. When I choose Data - Other Sources - From SQL Server, I get the Data Connection Wizard window to Connect to the Server.
This window allows me to enter the server name and a User Name and Password (for SQL authentication), however it does not allow me to specify a database. When I click the Next button, I get a 'login failed' error which I assume is happening because the contained user is not a SQL login.
I have the same issue trying to create an ODBC connection using the ODBC Data Source Administrator.Â
Is there another way for me to connect to a SQL Server database using a contained login with Excel?Â
View 10 Replies
View Related
Oct 8, 2007
I'm developing an custom dataflow transformation task that involves mapping of columns between multiple inputs and outputs. All the mappings are stored in a dataset. At first I thought to store this to an variable but after reloading bids I get an schema not found on xml for the stored dataset. Then I tried to put the dataset into an custom property but that seems to only take strings.
So how do I save the info on the mappings contained in my dataset (as that is most easy while using a datagrid to display mappings) in the package preferably in a way that is not visible to the user.
In short: What is the proper way to save complex datatypes in a custom dataflow task using a custom ui?
View 5 Replies
View Related
Mar 18, 2007
Error 2 'Text' is not a member of 'System.Data.SqlClient.SqlParameter'. I think this error has something to do with the lack of an Import command, anyone point me in the right direction?
View 4 Replies
View Related
May 19, 2008
Dear All,
I have a problem while trying to update the content of my page to the database by the means of a stored procedure
string OcompConnection = ConfigurationManager.ConnectionStrings["GenUserCode"].ConnectionString;
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(OcompConnection);
System.Data.SqlClient.SqlCommand ocmd = new System.Data.SqlClient.SqlCommand("Dealer_Insert", conn);ocmd.CommandType = CommandType.StoredProcedure;
ocmd.Parameters.Add(new SqlParameter("@UserCode"), SqlDbType.NVarChar);
ocmd.Parameters["@UserCode"] = TxtUserCode;
and also there is another error message saying that argument1: can not comvert from system.data.sqlclient.sqlparameter to string.
What am i Missing???
Eventually there is the try{open} and finally{close}
Many Thanks
View 3 Replies
View Related
Jan 31, 2006
Hi.
I get the following error
Quote: Execution of a full-text operation failed. A clause of the query contained only ignored words.
because I try to search words that are 2 characters like "me" or like " on "
why can't I use words that are 2 characters in sql query by using the contains function?
View 1 Replies
View Related
Apr 3, 2015
I have a specific variation on the standard 'Column Invalid' question: I have this query that works fine:
SELECT vd.Question ,
csq.Q# ,
csq.Q_Sort ,
csq.Q_SubSort ,
AVG(CAST(vd.Response AS FLOAT)) AS AvgC ,
vd.RType
[Code] ....
When I add this second average column like this:
SELECT vd.Question ,
csq.Q# ,
csq.Q_Sort ,
csq.Q_SubSort ,
AVG(CAST(vd.Response AS FLOAT)) AS AvgC ,
[Code] ....
I get the error: Column 'dbo.vwData.Response' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Clearly things are in the right place before the change, so I can only assume that the OVER clause is my problem. Is this just not possible?
View 1 Replies
View Related
Jul 30, 2015
In SSMS, I connect Object Explorer to a partially contained database using a contained user login with password. This user has a database role of dbdatareader. When I try to expand the Tables in the database, I get the error:Â
The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Is there a way to set permissions for the contained user so that this could be done?
View 4 Replies
View Related
