Hi There - is it possible to output the SQL that a SQLDataSource
control is generating. I am having a difficult time diagnosing
errors such as the following:
Keyword not supported: 'unicode'.
Any help is appreciated.
I have read some ideas on this, but nothing is working for me.I have an SQLDataSource bound to a FormView. I need to use the FormView to Insert new rows. When I type new values, all is well. When I type a duplicate, a get a runtime primary key error. That's fine, but how do I trap that? Overriding Page_Error doesn't work for me.Anyone please?
View 1 Replies View RelatedI'm new to using SQL Data Source, so bare with me on the newbie question.
Is there a way to do a Try...Catch type scenario on the SDS? I have a grid and a SDS that is mapped together but previously I use to use a Try...Catch and show any errors. What can I do to display a message if there is an error with the SDS?
Try 'Call to DBCatch label1.txt = "Error: " & ex.Message.ToStringEnd Try
And is the best way to determine if there are any records to display is to use the SDS_Selected event?Dim Rec as Integer = e.AffectedRowsIf Rec = 0 Then label1.text = "No Records Found."End If
Ok, when i use a sqldatasource control and build a INSERT command like below. My code always gives me a already have @UserId parameter error when I try to insert. What am I doing wrong here? SqlDataSource INSERT COMMAND:INSERT INTO Ranger_Profile(UserId, FirstName, LastName, Address, City, State, Zip, HomePhone, CellPhone, Email) VALUES (@UserId, @FirstName, @LastName, @Address, @City, @State, @Zip, @HomePhone, @CellPhone, @Email)Page Code:Dim RangerProfileDataSource As SqlDataSource = SqlDataSource1RangerProfileDataSource.InsertParameters.Add("UserId", CreateNewUser.UserName.ToString)RangerProfileDataSource.InsertParameters.Add("FirstName", txtFirstName.Text.ToString)RangerProfileDataSource.InsertParameters.Add("LastName", txtLastName.Text.ToString)RangerProfileDataSource.InsertParameters.Add("Address", txtAddress.Text.ToString)RangerProfileDataSource.InsertParameters.Add("City", txtCity.Text.ToString)RangerProfileDataSource.InsertParameters.Add("State", txtState.Text.ToString)RangerProfileDataSource.InsertParameters.Add("Zip", txtZip.Text.ToString)RangerProfileDataSource.InsertParameters.Add("HomePhone", txtHomePhone.Text.ToString)RangerProfileDataSource.InsertParameters.Add("CellPhone", txtCellPhone.Text.ToString)RangerProfileDataSource.InsertParameters.Add("Email", CreateNewUser.Email.ToString)Dim rowsaffected As Integer = SqlDataSource1.InsertIf rowsaffected = 0 Then'End If
View 4 Replies View RelatedI have a sqldatasource and I allow deletions that could cause a constraint violation. I want to capture this error however, I am using the sqldatasource bound to my datacontrol. I dont know where to catch the error. Unfortunately there is no OnError event for the control... There is no code behind for this at this time. I am hoping there is a way to do this without completely rewiring everything.
My thanks in advance,
Hi all,I have a stored procedure which sets an output variable. How could I access this after calling an SqlDataSource.Select()? ThanksChris
View 9 Replies View RelatedI'm scheduling a T-SQL job with SQL Agent. The job uses PRINT to write out various progress and status messages as it runs. I created an Operator, and set a Notification when the job fails -- will that send me the entire output of the job, or just the error message?
View 1 Replies View RelatedI am using SqlDataSource programmatically in my data access layer - mainly for convenience but it does generally work fine with no obvious performance issues.
The problem I have is with getting back an output parameter. I have an insert-type stored procedure (in Sql Server 2005) operating on a table with an identity column as the primary key:
ALTER PROCEDURE [dbo].[InsertAlbum](@ArtistID int, @Title nvarchar(70), @NewID int OUTPUT)ASDECLARE @err intINSERT INTO dbo.ALBUMS (ARTISTID, TITLE)VALUES (@ArtistID, @Title)SELECT @err = @@error IF @err <> 0 RETURN @errSET @NewID = SCOPE_IDENTITY()
This works fine when run from Sql Server Management Studio and @NewID has the correct value.
My data access code is roughly as follows:
dsrc = New SqlDataSource()dsrc.ConnectionString = ConnectionStringdsrc.InsertCommand = "InsertAlbum"dsrc.InsertCommandType = SqlDataSourceCommandType.StoredProcedureDim parms As ParameterCollection = dsrc.InsertParametersDim newid As IntegerAddParameter(parms, "ArtistID", TypeCode.Int32, ParameterDirection.Input, 0, album.ArtistID)AddParameter(parms, "Title", TypeCode.String, ParameterDirection.Input, 0, album.Title)Dim p As New Parameter("NewID", TypeCode.Int32)p.Direction = ParameterDirection.Outputparms.Add(p)Try Dim rv As Integer = dsrc.Insert() newid = parms("NewID") Return newidCatch ex As Exception Return -1End Try
The row is inserted into the database, but however I try to define and add the NewID parameter it never has a value.
Has anyone tried to do this and can tell me what I am doing wrong?
Jon
I have a stored proc that I'd like to return an output param from. I'm using a SQLDataSource and invoking the Update method which calls the sproc.The proc looks like this currently:
ALTER proc [dbo].[k_sp_Load_IMIS_to_POP_x]@vcOutputMsg varchar(255) OUTPUT
AS
SET NOCOUNT ON ;
select @vcOutputMsg = 'asdf'
The code behind looks like this:protected void SqlDataSource1_Updated(object sender, SqlDataSourceStatusEventArgs e)
{
//handle error on return
string returnmessage = (string)e.Command.Parameters["@vcOutputMsg"].Value;
}
On the page source side, the params are defined declaratively:
<UpdateParameters>
<asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" />
<asp:Parameter Direction="InputOutput" Name="vcOutputMsg" Type="String" />
</UpdateParameters>
When I run it, the code behind throws the following exception - "Unable to cast object of type 'System.DBNull' to type 'System.String'"
PLEASE HELP! What am I doing wrong? Is there a better way to get output from a stored proc?
I have an event:
Private Sub SqlDataSourceIncome_Deleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSourceIncome.Deleted
Dim command As SqlClient.SqlCommand
command = e.Command
If command.Parameters("@nReturnCode").Value <> 0 Then
DROPDEAD()
End If
That fires from:
<DeleteParameters>
<asp:Parameter Name="nDeletebyId" Type="Int64" />
<asp:Parameter Name="nOtherId" Type="Int64" />
<asp:Parameter Direction="Output" Name="nReturnCode" Type="Int64" />
<asp:Parameter Direction="Output" Name="nReturnId" Type="Int64" />
</DeleteParameters>
End Sub
When I:
GridViewIncome.DeleteRow(GridViewIncome.SelectedRow.RowIndex)
But nReturnCode is ALWAYS NULL... I even did a stored procedure that just:
ALTER PROCEDURE [dbo].[sp_nDeletebyId]
@nReturnCode bigint output,
@nReturnId bigint output AS
SET @nReturnCode = 0
SET @nReturnId = 0
And STILL got nothing but the NULLS... the insert & update stuff works fine, with identical code... it's just the DELETED event that I can't seem to knock. Has anyone seen this before? The above sample stored proc did return 0 when executed one the server...
and, BTW, the row is deleted!
Chip Kigar
I am having a problem getting the output parameter from a stored procedure that deletes a record. I use the EXACT same process for the insert and update procs and they work fine. For some reason, the delete proc is either not returning an output parameter, or the SqlDataSource (dsCompany) is not able to get the information in the dsCompany_Deleted method. I simply want the proc to return an output value so that when I eventually get around to checking if the record can be deleted due to foreign key restraints, I can deliver the appropriate message to the user.I have highlighted the line that is giving the problem. I also highlighted a default value in the parameter, that when removed causes the app to crash because it is getting a null value back. Thanks in advance to anyone who can help!STORED PROC (eventually I will add foreign key restraints)
ALTER PROCEDURE CompanyDelete
@iCompanyId int,
@iOutput int OUTPUT
AS
BEGIN
DELETE
FROM tblCompany
WHERE iCompanyId = @iCompanyId
SELECT @iOutput = 1
END
CODE BEHIND (abbreviated)protected void btnCompany_Click(object sender, EventArgs e) {
dsCompany.InsertParameters["sCompany"].DefaultValue = txtCompany.Text;
dsCompany.Insert();
txtCompany.Text = "";
txtCompany.Focus(); }
protected void dsCompany_Inserted(object sender, SqlDataSourceStatusEventArgs e) {
if ((int)e.Command.Parameters["@iOutput"].Value > 0) {
lblMessage.Text = "Insert successful.";
}
else {
lblMessage.Text = "Insert failed. Duplicate record would be created.";
}
}
protected void dsCompany_Deleted(object sender, SqlDataSourceStatusEventArgs e) {
if ((int)e.Command.Parameters["@iOutput"].Value > 0) {
lblMessage.Text = "Delete successful.";
}
else {
lblMessage.Text = "Delete failed. Other records use this value.";
}
}
protected void dsCompany_Updated(object sender, SqlDataSourceStatusEventArgs e) {
if ((int)e.Command.Parameters["@iOutput"].Value > 0) {
lblMessage.Text = "Update successful.";
}
else {
lblMessage.Text = "Update failed. Duplicate record would be created.";
}
}
protected void gvCompany_RowEditing(object sender, GridViewEditEventArgs e) {
lblMessage.Text = "";
}
ASPX (abbreviated)<asp:SqlDataSource ID="dsCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" DeleteCommand="CompanyDelete" DeleteCommandType="StoredProcedure" InsertCommand="CompanyInsert" InsertCommandType="StoredProcedure" SelectCommand="CompanySelect" SelectCommandType="StoredProcedure" UpdateCommand="CompanyUpdate" UpdateCommandType="StoredProcedure" OnInserted="dsCompany_Inserted" OnDeleted="dsCompany_Deleted" OnUpdated="dsCompany_Updated">
<DeleteParameters>
<asp:Parameter Name="iCompanyId" Type="Int32" />
<asp:Parameter Direction="Output" Name="iOutput" Type="Int32" Size="8" DefaultValue="0"/>
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="iCompanyId" Type="Int32" />
<asp:Parameter Name="sCompany" Type="String" />
<asp:Parameter Direction="Output" Name="iOutput" Type="Int32" Size="8" DefaultValue="0" />
</UpdateParameters>
<SelectParameters>
<asp:Parameter DefaultValue="1" Name="iZero" Type="Int32" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="sCompany" Type="String" />
<asp:Parameter Direction="Output" Name="iOutput" Type="Int32" Size="8" DefaultValue="0" />
</InsertParameters>
</asp:SqlDataSource>
I recently updated the datatype of a sproc parameter from bit to tinyint. When I executed the sproc with the updated parameters the sproc appeared to succeed and returned "1 row(s) affected" in the console. However, the update triggered by the sproc did not actually work.
The table column was a bit which only allows 0 or 1 and the sproc was passing a value of 2 so the table was rejecting this value. However, the sproc did not return an error and appeared to return success. So is there a way to configure the database or sproc to return an error message when this type of error occurs?
I have a parent package that calls child packages inside a For Each container. When I debug/run the parent package (from VS), I get the following error message: Warning: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
It appears to be failing while executing the child package. However, the logs (via the "progress" tab) for both the parent package and the child package show no errors other than the one listed above (and that shows in the parent package log). The child package appears to validate completely without error (all components are green and no error messages in the log). I turned on SSIS logging to a text file and see nothing in there either.
If I bump up the MaximumErrorCount in the parent package and in the Execute Package Task that calls the child package to 4 (to go one above the error count indicated in the message above), the whole thing executes sucessfully. I don't want to leave the Max Error Count set like this. Is there something I am missing? For example are there errors that do not get logged by default? I get some warnings, do a certain number of warnings equal an error?
Thanks,
Lee
I have a quick question regarding SQL Server Enterprise Manager. I'm looking at setting up a job to automatically create DDL for a user database. This will be done along with our normal nightly backup routine.
I'm very familiar with using EM to create SQL scripts, but is there anyway to schedule this task? I've considered DTS and some type of scheduled package, but can't seem to find anything similar. I'm thinking I may need a custom task.
Could someone please shed some light on the subject? If not from within EM, how about any third party tools? FYI - I already own the Embarcadero suite and am trying it out wwith that.
Thank You.
Anthony Robinson
hi
I want to generate excel file which contain table name , column name,datatype ,size
how we can do in sql server
is there any way
pleases tell the steps
Server:
SQL 2005 SP2 on Win 2003 Ent. SP1
A 3rd part app is requiring that I create a credential, whick in turn requires an SMK be set. When I try to create the credential, I get an error message indicating a decryption error. When I run the alter command to regenerate the key (without force) it throws an error indicating the key cannot be decrypted. According to a KB article I found, this may indicate that a key has never been generated.
My question is, I have a number of production databases in this instance, including SQL Reporting Services. Except for the SRS DB's, all other user db's are simple db's that don't use encryption. If I run the Force command to generate the key, am I going to break anything? I'm really concerned about report servioces.
Thanks.
I need a tool to generate sql code of database including all data like "insert into table values()". Same as sql file in IBuySpy portal. How can I generate a file like this? I tried with enterprise manager but it doesn't generate insert statements and default values of some fileds lost.
Can someone help me?
Hello all,
I have a data driven subscription with the information about the file name/file extension/ path etc coming in from a database. The problem is that the subscription status after running tells me that things are done and there is no error but the file is not being generated in the specified directory and for that reason the file is not generated at all anywhere on the hard disk. can anybody please help.
The information in the database for the report is as follows
FileName : Test1
FileExtn : True
Path = \ram\C$Reports
Render_Format = PDF
Username = Administrator
Password = password
Writemode = Overwrite
All the fields are varchar(50) in size.
Hi,
How to create surrogate key in a dimension table?
What transformations can be used to create it?
Hi all,I am following the procedure where I generate XMLs of tables and put itin a DataSet and read that dataset back into another database to bewritten into the tables.Although I am VERY close to completion a small problem that I am facingis that the XMLs being generated have as the table name "table" and notthe actual names.Is this a known issue or am I amiss? and, does anyone have a solutionfor this?Thank you.*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies View RelatedHi guys,Apologies Simon for not making it clearer last time. What i am doing isreading a table from the SQL Server 2000 database and using:SQLDataSet.WriteXml(strFileName, XmlWriteMode.WriteSchema);to write to the DataSet. But I realised that when I do that the XML isgenerated correctly, the only error being that<xs:element name="Table"> comes in as the tablename instead of theactual table name.Any suggestions will be appreciated. Thank you.*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies View RelatedHelloFor my client, I need to generate reports from the information storedin the database. The client has fixed format forms (on paper e.g. USCustoms forms etc).Will I need to redesign the forms in the application and then show theinformation?Another approach is to scan the forms as image and print theinformation on top of that image, so when it is printed , theinformation will be displayed at the right places.Is there any other way? How is the reporting done if the forms arepre-defined and the information is stored in a databaseThanks for your input
View 1 Replies View RelatedHi, I'm trying to use the SK script from Donald Farmers book but the code isn't accepted
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim CurrentKey As Integer
Public Overrides Sub PreExecute()
CurrentKey = CInt(Me.Variables.FILCodesSK)
End Sub
Public Overrides Sub Input_ProcessInputRow(ByVal Row As Input0Buffer)
CurrentKey += 1
Row.SurrogateKey = CurrentKey
End Sub
End Class
There is a problem with the use of the overrides on the Input_ProcessInputRow sub should this be renamed?
Cheers, Al
Hi,
I am working with SQL Server 2005 Reporting Service from few days, though I am
not expert, for some reason I have to run on field without having sound
knowledge of RDL, but need your help, gys. I am using SQL Server Business
Intelligence Development Studio to design report.
Here is the procedure of my work to populate a RDL report
I used a stored procedure for the DataSet of my RDL
Then I drag and drop necessary field to my report layout.
Put required parameters to preview tab and then run report.
This is quite simple, I didn't face any problem with this process, even though
the process may not correct, but working perfect. My problem is
when the stored procedure returns multiple result set. The data tab only
shows the first result set though the SP returns multiple result set, I have
run the SP in the Query Analyzer. I don't want change my stored procedure.
And please give me some suggestions about the best procedure
to develop RDL report in real life.
Please reply me ASAP, it€™s very urgent.
Thank youTareqe
Hi,
Can any one tell me how can I create auto number (similar feature to MS Access) i.e. autmatic increament by 1 in MS SQL 2005 (without using any script)
What is the C# code I use to do this?
I'm guessing it should be fairly simple, as there is only one row selected. I just need to pull out a specific field from that row and then insert that value into a different SqlDataSource.
Starwin writes "when i execute DBCC CHECKDB, DBCC CHECKCATALOG
I reveived the following error.
how to solve it?
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -2093955965, index ID 711, page ID (3:2530). The PageId in the page header = (34443:343146507).
. . . .
. . . .
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1635188736)' (object ID -1635188736).
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1600811521)' (object ID -1600811521).
. . . .
. . . .
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -8748568, index ID 50307, page ID (3:2497). The PageId in the page header = (26707:762626875).
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -7615284, index ID 35836, page ID (3:2534). The PageId in the page heade"
Does anyone know an efficient method for generating a sequence number in the following form?
Starting with 2 columns
1 2
----
A X
A Y
B X
B Y
B Z
C X
I want to then generate a third column as follows:
1 2 3
-------
A X 1
A Y 2
B X 1
B Y 2
B Z 3
C X 1
The purpose being so that I can easily identify the previous row within a Column1 group. So given column1=A and column2=Y I know that the previous row is Column3 - 1 where column1 = A. Therefore I will be able to join to the previous result of any row within any group quickly for future calculations.
Any ideas? Thanks.
Hi,
I am using a relation(table) which has a artificial key. I want to use this key as the primary key hence is unique. What datatype is associated with this attribute in MS SQL 2000. How can I generate unique id everytime I add a new record to this table ?
Thanks
-Sudhakar
I want to make up a system that I can create barcodes for my students and scan books for checkout. I am using Windows xp and .net winforms to finish the task. But I have no source for doing that. I am also wondering if my HTC sense could be used for scanning.
View 13 Replies View RelatedI have been working on a Human Resource Management software in C# .NET. In its Employee Attendance module i need to keep records of attendance of all workers for one month in a single table.The problem I encounter is to auto generate an exact copy of the Attendance table after a particular month is finished. How can i accomplish this? I use SQL server 2008.
View 4 Replies View RelatedHi,
I have an error table that is to be updated by more than one package.
There is a sequence number generated in the error table. It is generated by using the max value of the previous data present in the table.
When more than one package runs parallely, conflict occurs in generating the sequence number.
How can this be handled?
Hi.I have a database I need to supply something (I'm assuming a t-sql script..maybe something else is better) to update customer tables with.The operations include mostly changing varchar lengths, though a couple ofcolumns were renamed.I'd like to maybe figure out how to get Enterprise Manager or Query Analyzerto generate the scripts.I can't just send alter table scripts because I'm involving all sorts ofconstraints that have to be disabled/or dropped, the alter made, then havethem enabled/ or re-created.Basically I'm hoping to get the tools to do the rather large amount of workfor me. I'm targetting sql server 2000.Can someone make a knowledgeable suggestion?RegardsJeff Kish
View 4 Replies View Related