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.
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
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.
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 RelatedHi 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 RelatedThe 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);
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.
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
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
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
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
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 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
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.DeletingLine 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_variantASset 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.
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
I am the owner of a small business and run sports leagues on my website. I have been using a csv file to gather registrations from my webform and then manually importing them to Access. I also use the same method for collecting their results each week. Can I use SQL to automate my process? If so, where to I get the training I need to set that up? I'd like my customers to be able to log in to their account on the website and post their results (which attach to their name/profile). Then I'd like to extract that information in a report that I can post to the website. Or maybe have the customer sort the results by different criteria types.
Can I do that with SQL? More importantly, am I even in the right ballpark for getting this done?
Thanks to anyone who can give me a nudge in the right direction.
My table structure:
Aircraft_Id(PK, nchar(25),not null)
Service_Ceiling_ft (numeric(18,0), not null)
My Data:
Aircraft_Id Service_CeilingP_ft
F-14 Tomcat 53000
My Query:
SELECT 'Aircraft_Id, 'Service_Ceiling_ft'
FROM Combat
WHERE 'Service_Ceiling_ft' > '51000'
My result:
(No column name) (No column name)
Aircraft_Id Service_Ceiling_ft
I'm not getting any error messages or data. Any advice would be a great help.
thanks
US Navy - We are fueled, armed, and go for launch.
this is a seemingly simple query, but i've been googling around for a while and haven't been able to come up anything, probably because i simply can't explain what I'm trying to do
can someone please point me in the right direction? using sql server 2005.
tbl_vendorprice looks like:
id vend price
1 A 2.00
1 B 3.00
2 A 4.00
2 B 3.50
3 A 8.00
3 B 8.50
and this is what i want as a result
id vendA VendB
1 2.00 3.00
2 4.00 3.50
3 8.00 8.50
thanks
David
Charter Cable does not provide server technology to residential customers.
I know nothing about SQL. I believe that I read that it can be setup on my computer for testing web pages.
I may not even be in the right forum.
Would someone please direct me where to start?
Thanks
All help is appreciated.
Thanks
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 RelatedDear 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
Hello, thanks for reading this.
I am trying to learn a bit about debveloping my own website, and I am in need of a basic direction on how to acomplish this task:
I have an local program running on my PC that creates an MS Access database export of my inventory. This export can be directly uploaded to my web server in a zip format.
My web site is developed in Coldfusion MX7, and uses a MS SQL 2005 database. What I would like to do is create somewhat of an "Auto update" for my website, exporting my access database and updating the MS SQL database automatically.
I really can use some good direction on this problem.
Thank you for any help in advance.
hi.
i am a c++ developer who requires to learn sql.
i have no other programs than the sql server that comes with visual studio pro 2005.
what
do i need to get/do to create a sql databse and to manipulate it in c++.
thank you.
Hi all. The company I work for is looking for a new SQL server. Where can I find information and or a tool for sizing information? By sizing information I mean how big a pile of hardware am I going to need to run MS SQL for x number of connected users with x size database, etc. I've been tooling around the internet and MS' site but can't find any info on this.
Can anyone shed some light for me?
Happy Friday afternoon, all,
My task is seemingly simple. I have data on the server in MS Excel Files. I need to get the data into multiple tables in a SQL Server db on the same server.
I have been only working with SSIS for a bit, so please bear with me.
I can load the data directly from the Excel worksheet to one table, but I need to run an already defined stored procedure on the data from Excel before putting it into tables. I need to loop over all the rows and run the data from each row through the stored procedure.
So, I think I need an Execute SQL Task withing a For Each Loop, but neither is available on the Data Flow page, and I don't see how to use them in the control flow page. I don't see that any of the Data Flow transformations which are available on the dataflow page will do what I need.
I can have created the data flow Source-Query and the Destination-Query; it's the bit in between that has me hung up.
Can anyone please give me a high level overview of what I need to do, or point me to an example of something similar to what I am trying to do?
Thanks and have a great weekend,
Kathryn
Hi,
In the SqlDataSource control if I go to the SelectQuery property and I set one parameter with the “direction� property to “Output� the result doesn’t display in the control, why?
Points:
The procedure witch is in the selectquery property the parameter in it is set to output two.
Has anyone created a stored procedure that notifies a customer that their order_status has been changed from 'submitted' to 'backordered' or from 'backordered' to 'shipped'? Once written, how does the sp get automatically executed? Does anyone have an example or a book/site with an example?
--Thanks in advance.
We lost our Datawarehouse developer that set up this application for us, so I am now in charge... ick, and don't know what I'm doing really.
Lately no messages leaving sys.transmission_queue... all have transmission_status of: "One or more messages could not be delivered to the local service targeted by this dialog."
Setup looks like this:
CREATE MESSAGE TYPE XmlSubmission VALIDATION = WELL_FORMED_XML;
CREATE CONTRACT XmlContract (XmlSubmission SENT BY INITIATOR);
CREATE QUEUE ReceiverXmlQueue WITH STATUS = ON, ACTIVATION (PROCEDURE_NAME = MMRS.MMRS.WrapXmlProcessor, MAX_QUEUE_READERS = 1, EXECUTE AS SELF );
CREATE QUEUE SenderXmlQueue;
CREATE SERVICE Sender ON QUEUE SenderXmlQueue;
CREATE SERVICE Receiver ON QUEUE ReceiverXmlQueue(XmlContract);
ALTER QUEUE ReceiverXmlQueue WITH STATUS = on
Service Broker is enabled on database, and I've tried dropping and recreating these objects to no avail.
When functioning, these messages get recieved and procecessed into a staging SQL table, storing the XML to be loaded up into the relational tables.
Any ideas or clues for troubleshooting would be greatly appreciated!
Hi all,
I have problem to change direction of the textbox, i need to change the driction as bottom to top.
But in report has provide the tb-rl and tb-lr. these are changed the direction as top to bottom.
anybody worked on it , please help.
Thanks
Murugan A
I have some reports on a report server that needs to be reached in two ways.
1 - From a windows client application (over internet)
2 - From a web browser (over internet)
Windows authentication could not be used here, since the users are not part of any domain etc, so I have implemented forms authentication for Reporting Services as described here:
http://msdn2.microsoft.com/en-us/library/ms160724.aspx
My first task was to access the reports from the windows forms application, and I managed to do that without too much trouble using a ReportViewer and setting the custom credentials in this way:
myReportViewer.ServerReport.ReportServerCredentials.SetFormsCredentials(null, "loginname", "password", "");
Works great!
However, now I'm dealing with the second problem... accessing the reports from the web. Sure, this works fine if i just type http://myserver/reports/. This brings up the custom loginpage and if I enter correct login information I get access to the reports... The problem is that I don't want to show everything 'around' (on top of) the report, I just want to show a report. Lets say I have a webpage with two frames. One on the left with a couple of links each representing a singel report. When clicking the link the report should be rendered in the right frame.
What approach should I use here? When and where should the user authentication take place?
I have done some testing with an ASP.NET application using a report viewer, but I seem to get the error:
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'
I guess this is because Reporting services wants to bring up the login page? I can't find any way of setting the credentials for the reportViewer in my ASP.NET app, like I did in the WinForms app. I'm really not a web-developer so I might be missing some obvious points here, if I do, please point me in the right direction...
I have also read about using the Report server web service to render the html-page without using the reportviewer, but when I try to make a call to a service like this:
ReportingService _rs = new ReportingService();
_rs.Url = "http://myServer/ReportServer/ReportService.asmx";
CatalogItem[] items = _rs.ListChildren("/", true);
I get an exception like the one above, telling:
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'
Where should I go from here... any help would be much appriciated!
Regards Andreas
How can you set the text direction to BT-RL 'bottom to top' in SRS? There is control for TB-RL but not BT-RL. Need this for SRS label printing application.
View 1 Replies View RelatedHi.
I am wondering if somebody out there wouldnt mind
looking over my stored procedure?
The idea behind this procedure is (was) that,
(they are numbered...)
1) the procedure checks for the existence of data in the database and
2) if no data exists (if the previous statement throws an error), then execute the insert statement
3) otherwise (data exists), execute the update
I also have "sub" error handling for each statement so, if it is executing the insert, return 99 for fail, 0 for success. the same for update.
problem is that its returning the values for the first select statement and i have no idea what is going on fir the Insert or Update statements as i cant set break points in a stored procedure.
thanks for any feedback.
Code:
ALTER PROCEDURE dbo.sp_UpdateEditProfile
(
@headline VARCHAR(50) = NULL,
@about_me TEXT = NULL,
@email_address VARCHAR(50) = NULL,
@edit_date VARCHAR(50) = NULL
)
AS
BEGIN
SELECT @edit_date = CONVERT(DATETIME,convert(char(26), getdate(), 109))
END
BEGIN
--check to see if user has data there already
1)SELECT headline, about_me FROM EditProfile WHERE email_address=@email_address
--if he doesn't,
IF @@ERROR <> 0
--this is an insert statement
BEGIN
SET ANSI_WARNINGS OFF --ansi errors on text insert
2)INSERT INTO EditProfile
(headline, about_me, email_address, edit_date)
VALUES (@headline, @about_me, @email_address, @edit_date)
SET ANSI_WARNINGS ON
IF @@ERROR <> 0
BEGIN
RETURN(99)
END
ELSE
BEGIN
RETURN(0)
END
END
--if he does
ELSE
--update the data
BEGIN
SET ANSI_WARNINGS OFF --ansi errors
3) UPDATE EditProfile
SET headline=@headline, about_me=@about_me, edit_date=@edit_date WHERE email_address=@email_address
SET ANSI_WARNINGS ON
IF @@ERROR <> 0
BEGIN
RETURN(99)
END
ELSE
BEGIN
RETURN(0)
END
END
END
I am using the MS SQL Server Management Studio Express to create a stored procedure in one of my databases. I specify one of the parameters as OUTPUT as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ProcRetDbl]
@Threshold real, @Result real OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @Result = Channel1 FROM DataTable WHERE Channel2 < @Threshold
END
But then when I look at the properties of the @Result parameter in the Object Explorer's tree, it is shown as "Input/Output". Now, this seems like no problem at all since it will work fine as output, even though I don't need it to be able to do input as well, but I'm wondering why that is happening.
I am using ADO.Net on the other end to execute the procedure and I need to decide what parameter type to set to the SqlParameter object: "Output" or "InputOutput". I'm sure I can sort this out but I usually like to know what I'm doing. Thanks for the help.
Kamen