I would like to return an xmldocument from a 2005 vb clr stored procedure.
This is my definition for the stored procedure. passing in a string, return xmldoc.
Can I not return an xmldoc as output? The solution will build, but not run.
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Sub SP_Transform(ByVal cc As String, <Out()> ByVal RetValue As XmlDocument)
Error 1 Column, parameter, or variable #2: Cannot find data type XmlDocument. SqlServerProject1
Hi, I have xmldocument type that stores xml data. I would like to able to store that data in sql server 2005 as a xml data type. How can I accomplish this using ado sqlcommand? XmlDocument xdoc = new XmlDocument();SqlConnection conn = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["Test"].ConnectionString);conn.Open();SqlCommand cmd = new SqlCommand(); cmd.Connection = conn;cmd.CommandType = CommandType.StoredProcedure;cmd.CommandText = "spStoreApp";cmd.Parameters.AddWithValue("@PersId", "222-22-2222");cmd.Parameters.AddWithValue("@AppData", xdoc);try{cmd.ExecuteNonQuery();Response.Write("Storage of application successful");}catch (SqlException sqlx){Response.Write(sqlx.Message);}conn.Close();thanks in advance.
OK I am working on a SSIS package and am using a script task. I have to get remote xmldocuments from a server. Sounds simple enough. In C# it was easy but for some reason vb.net will not let me do this:
I can do a httpwebrequest and get the xml string into a variable strResponse
But the following will not work. The XmlDocument doesn't exists in the script task the vb code. On a side note I am importing Imports System.Xml in case anyone asks.
Dim doc As New XmlDocument doc.LoadXml(strResponse)
Could anyone tell me how I would parse the xml elements using a script task?
here is the full code I am working with but all of the xml stuff has that blue squggly line underneathit.
Private Function ParseXmlIntoList(ByVal strResponse As String, ByVal l As List(Of StockInfo)) As List(Of StockInfo)
Dim doc As New XmlDocument
Try doc.LoadXml(strResponse) Catch isError = True Return l End Try
Dim root As XmlElement = doc.DocumentElement Dim list As XmlNodeList = root.SelectNodes("/StockData/Stock")
Dim x As Integer = 0
Dim n As XmlNode For Each n In list If l((x + intStartCount)).Symbol = getXMLElement(n, "Symbol") Then l((x + intStartCount)).Price = Convert.ToDecimal(getXMLElement(n, "Last")) l((x + intStartCount)).CompanyName = getXMLElement(n, "Name") x += 1 Else
isError = True Return l End If Next n
intStartCount = intStartCount + 25
Return l
End Function
Any help would be greatly appreciated.
Thanks,
JBelthoff • Hosts Station is a Professional Asp Hosting Provider • Position SEO can provide your company with SEO Services at an affordable price › As far as myself... I do this for fun!
Hi there, How do i load a XML document saved in SQL server as a XML data type into XmlDocument xdoc = new XmlDocument();xdoc.Load(// INTO HERE ); I can load a xml file saved to disk but haven't figured out how to retrive from a recordset. I have tried the following:
XmlDocument xdoc = new XmlDocument();xdoc.Load(GetXmlFile(pnrID).ToString());
public string GetXmlFile(int pnrID){ SqlConnection cnn = null;SqlCommand cmd = null; string XML = ""; try { cnn = new SqlConnection(); cnn.ConnectionString = "Server=;Initial Catalog=;UID=;PWD=;"; cnn.Open(); string selectQry = "SELECT [XML] FROM [TEMP_PNR] WHERE PnrID = @PnrID"; cmd = new SqlCommand(selectQry, cnn); cmd.Parameters.AddWithValue("@pnrID", pnrID); SqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) XML = rdr.GetSqlXml(0).ToString(); } catch (Exception ex) { throw ex; } finally { cmd.Dispose(); cnn.Close(); } return XML; } But this genereates the following error: Could not find file 'C:Program FilesMicrosoft Visual Studio 8Common7IDESystem.Data.SqlTypes.SqlXml'. Any idea how i can achive this?
The test sub below operates on a SQL Server Table with an xml-type field ("xml"). The purpose of the sub is to learn about storing and retrieving a whole xml document as a single field in a SQL Server table row.When the code saves to the xml field, it somehow automagically strips the xml.document.declaration (<?xml...>). So when it reads the xml field back and tries to create an xmldocument from it, it halts at the xmldocument.load.I order to get the save/retrieve from the xmlfield to work, I add the <?xml declaration to the string when I read it back in from the xml field (this is in the code below).At that point the quickwatch on the string I'm attempting to load into the xmldocument is this:-----------------------------------------------------<?xml version="1.0" encoding="utf-16" ?><Control type="TypeA"><Value1><SubVal1A>Units</SubVal1A><SubVal1Btype="TypeA">Type</SubVal1B></Value1><Value2><SubVal2A>Over</SubVal2A><SubVal2B>Load</SubVal2B></Value2></Control>-----------------------------------------------------The original xml document string is this:-----------------------------------------------------<?xml version="1.0" encoding="utf-16" ?><Control type="TypeA"> <Value1> <SubVal1A>Units</SubVal1A> <SubVal1B type="TypeA">Type</SubVal1B> </Value1> <Value2> <SubVal2A>Over</SubVal2A> <SubVal2B>Load</SubVal2B> </Value2></Control>-----------------------------------------------------which seems to have all the same characters as the quickwatch result above, but clearly is formatted differently because of the indenting.THE FIRST QUESTION: Is there a simpler way to do this whole thing using more appropriate methods that don't require adding the xml.document.declaration back in after reading the .xml field, or don't require using the memorystream to convert the .xml field in order to load it back to the XML document.THE SECOND QUESTION: Why does the original document open in the browser with "utf-16", but when I write the second document back to disk with "utf-16" it won't open...I have to change it to "utf-8" to open the second document in the browser.Here's the test sub'============================================ Public Sub XMLDSTest() '=========================================== Dim ColumnType As String = "XML" '=========================================== '----------Set up dataset, datatable, xmldocument Dim wrkDS As New DSet1() Dim wrkTable As New DSet1.Table1DataTable Dim wrkAdapter As New DSet1TableAdapters.Table1TableAdapter Dim wrkXDoc As New XmlDocument wrkXDoc.Load(SitePath & "App_XML" & "XMLFile.xml") Dim str1 = wrkXDoc.OuterXml Dim wrkRow As DSet1.Table1Row wrkRow = wrkTable.NewRow '=======WRITE to SQL Server============== '------ build new row With wrkRow Dim wrkG As Guid = System.Guid.NewGuid TestKey = wrkG.ToString .RecordKey = TestKey .xml = wrkXDoc.OuterXml '<<< maps to SQL Server xml-type field End With '----- add row to table and update to disk wrkTable.Rows.Add(wrkRow) wrkAdapter.Update(wrkTable) wrkTable.AcceptChanges() '----- clear table wrkTable.Clear() '=======READ From SQL Server ============== '----refill table, read row, wrkAdapter.FillBy(wrkTable, TestKey) Dim wrkRow2 As DSet1.Table1Row = _ wrkTable.Select("RecordKey = '" & TestKey & "'")(0) '===== WRITE TO New .xml FILE =========================== Dim wrkS1 As New StringBuilder Select Case ColumnType Case "XML" '---if xml build xml declaration: '---add this to xml from sql table => <?xml version="1.0" encoding="utf-16" ?> wrkS1.Append("<?xml version=" & Chr(34) & "1.0" & Chr(34)) wrkS1.Append(" encoding=" & Chr(34) & "utf-16" & Chr(34) & " ?>") wrkS1.Append(wrkRow2.xml) End Select Dim wrkBytes As Byte() = (New UnicodeEncoding).GetBytes(wrkS1.ToString) Dim wrkXDoc2 As New XmlDocument Dim wrkStream As New MemoryStream(wrkBytes) wrkXDoc2.Load(wrkStream) '=========================================== '---- this just shows that the file actually was touched Dim wrkN2 As XmlNode = wrkXDoc2.CreateNode(XmlNodeType.Text, "ss", "TestNode2") wrkN2 = wrkXDoc2.SelectSingleNode("//Value1/SubVal1B") wrkN2.Attributes("type").Value = "This was from the xml field" '---------------- '------ update the encoding....otherwise the file won't open in the browser with utf-16 Dim wrkN1 As XmlNode = wrkXDoc2.CreateNode(XmlNodeType.Element, "ss", "TestNode") wrkN1 = wrkXDoc2.FirstChild wrkN1.InnerText = Replace(wrkN1.InnerText, "utf-16", "utf-8") '------------Now write the file back as an .xml file Dim wrkFilePath As String = SitePath & "App_XML" & "XMLFile2.xml" Dim wrkXW As XmlWriter = XmlWriter.Create(wrkFilePath) wrkXDoc2.WriteContentTo(wrkXW) wrkXW.Close() End Sub===============================
I am having probelms trying to get a stored proedure to return an output value. The outline of the specific request is that I supply a varchar which is unique within a set of tables (tables named in another table). I want to search each of the tables until I find the one that has the value and when found return the GUID of the row and the table name. I have not been successful in getting the value of the GUID (an int) to be returned. If I run in debug and stop after the SQL call I can see the value in the SQL output parameter but it does not appear in the variable I specified in the SQL parameter setting. I am probably doing something simplly wrong but cannot see it. Please help if you can. Regards, Major (that is my Christian name ;-) The code files are copied below. Web app code sing System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; using EFormsLIB; public partial class _Default : System.Web.UI.Page { String gstrConnectionString; // Connection to the SQL database, set in the config file to allow for changes while testing and running protected void Page_Load(object sender, EventArgs e) { // Read the connection string from the config file EFormsRoutines r = new EFormsRoutines(); gstrConnectionString = r.ReadConfigString("EForms21ConnectionString", ""); } protected void btnCancel_Click(object sender, EventArgs e) { this.Dispose(); } protected void btnGet_Click(object sender, EventArgs e) { // Get GUID for the form using UniqueDocID as the BlueWare ID. int intGUID = -1; SqlConnection SqlCDB = new SqlConnection(gstrConnectionString); SqlCommand SqlCmd = SqlCDB.CreateCommand(); SqlCmd.CommandText = "GetGUIDfromBWID"; SqlCmd.CommandType = CommandType.StoredProcedure; SqlParameter BWIDParameter = new SqlParameter(); BWIDParameter.ParameterName = "@BWID"; BWIDParameter.SqlDbType = SqlDbType.VarChar; BWIDParameter.Size = 30; BWIDParameter.Direction = ParameterDirection.Input; BWIDParameter.Value = TextBox1.Text; SqlCmd.Parameters.Add(BWIDParameter); SqlParameter GUIDParameter = new SqlParameter(); GUIDParameter.ParameterName = "@GUID"; GUIDParameter.SqlDbType = SqlDbType.Int; GUIDParameter.Direction = ParameterDirection.Output; GUIDParameter.Value = intGUID; SqlCmd.Parameters.Add(GUIDParameter); SqlCmd.Connection.Open(); int ret1 = SqlCmd.ExecuteNonQuery(); SqlCmd.Connection.Close(); Label1.Text = intGUID.ToString(); } } web app aspx <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Label ID="Label1" runat="server" Style="z-index: 100; left: 48px; position: absolute; top: 24px" Text="Label"></asp:Label> <asp:Button ID="btnGet" runat="server" OnClick="btnGet_Click" Text="Get" style="z-index: 101; left: 8px; position: absolute; top: 48px" /> <asp:Button ID="btnCancel" runat="server" OnClick="btnCancel_Click" Text="Cancel" style="z-index: 102; left: 64px; position: absolute; top: 48px" /> <asp:TextBox ID="TextBox1" runat="server" Style="z-index: 103; left: 48px; position: absolute; top: 0px" Width="48px"></asp:TextBox> <asp:Label ID="Label2" runat="server" Style="z-index: 104; left: 0px; position: absolute; top: 0px" Text="BWID"></asp:Label> <asp:Label ID="Label3" runat="server" Style="z-index: 106; left: 0px; position: absolute; top: 24px" Text="GUID"></asp:Label> </div> </form> </body> </html> SQL procedure set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[GetGUIDfromBWID] @BWID varchar(30), @GUID int outputASBEGIN SET NOCOUNT ON; declare formscursor cursor for select formname from forms for read only declare @found int, @formname varchar(30) select @found = 0 open formscursor fetch next from formscursor into @formname while (@found = 0) begin if (@@fetch_status <> 0) select @found = 2 else begin if @formname = 'UAT040' begin if (select count(*) from UAT040 where BWID = @BWID) =1 begin select @GUID = (select GUID from UAT040 where BWID = @BWID) select @found = 1 end end else if @formname = 'GEN001' begin if (select count(*) from GEN001 where BWID = @BWID) =1 begin select @GUID = (select GUID from GEN001 where BWID = @BWID) select @found = 1 end end else if @formname = 'GEN002' begin if (select count(*) from GEN002 where BWID = @BWID) =1 begin select @GUID = (select GUID from GEN002 where BWID = @BWID) select @found = 1 end end else if @formname = 'CFT001' begin if (select count(*) from CFT001 where BWID = @BWID) =1 begin select @GUID = (select GUID from CFT001 where BWID = @BWID) select @found = 1 end end fetch next from formscursor into @formname end end deallocate formscursor if @found = 2 select @GUID = -16 select @GUIDend
I have a .rdl file that was exported out of ProClarity's Desktop Professional 6.1 using their RS plug-in. I uploaded the file into Report Manager and when I execute it, I get the following error:
An error has occurred during report processing.
Query execution failed for data set 'Three_Month_Funding_Trend'. Unable to recognize the requested property ID 'ReturnCellProperties'.
Does anyone have any idea what this is referring to? Does it have something to do with my configuration, connection or the report definition? Other reports such as DBMS based reports work fine.
Ok, I'm a long time reader of this forum and just about everytime I come here looking for an answer its allready been posted and answered. But I can't seem to find this one anywhere. Sorry if its allready been posted...Just point me in the right direction.
Ok, Ive been coding in VB2005 for a while but i'm new to SQL stored procedures. This is prolly going to sound stupid but I don't know where to start. Heres what I need to do.
I have a stored procedure that requires a parameter. I can pass that parameter to the stored procedure and execute it just fine. But I need the result thats returned when the stored procedure is executed. I can't seem to find anyway to return the Result back to me. DataReaders keep blowing up and I have no Idea Why????? Here is how i'm passing the param to the stored proc.
I'm running MS SQL BTW.
Dim myConnection As New SqlClient.SqlConnection("yadayada;Database=BlaBlaBla;Trusted_Connection=True;")
Dim cmd As New SqlClient.SqlCommand("NameOfStoredProc")
sqlParam.Value = Address 'declared higher up.....its just an int
myConnection.Open()
cmd.ExecuteNonQuery()
myConnection.Close()
Thats all great, but I need to read back the results the stored proc returns. If I execute this in server management studio, it returns a result. But nothing I try in my asp page works. Any help would be Great. Code samples are good too.
Here is the scenario: I have a report in SSRS 2005 that uses an OLE DB connection to send a MDX query to an AS 2000 cube. The report has been working fine for weeks. When the report ran today, it returned the row and column metadata, but not the cell values.
I ran the MDX query separately in the SSRS query editor pane, and it returns all values properly. I was also able to perform the query using ProClarity, so it appears that the issue is between the result set and SSRS.
I have a very strange problem using SQL Server 2005
I have several machines running an application, the problem is that on all machines except one of them the size of the result set that gets returned when I execute the following query is dfferent:
Select * from custoemr where EmployeeID = 3
on three out of the four machine the size of the result set is 1000, where on the other machine the size of the result set is 250, No errors are generated..
Can someome please teel me how to preceed in resolving this issue..
We recently upgrade to 2005(sp). We have one report that ran fine in 2000 but leaves out data from certain columns (date related) in the results, so we chalked it up to being a non compatiable issue. So, I decided to try and switch the DB back to 2000 compatibility (in our test env) and then back to 2005. After that the report started returning the proper data. We can€™t really explain why it worked but it did. So we thought we would try it in prod (we knew it was a long shot) and it didn€™t work. So the business needs this report so we thought we would refresh the test system from prod, but now we are back to square one. I was wondering if anyone else has heard or seen anything like this. I am open to any idea€™s, no matter how crazy. J The systems are configured identically. Let me know if you need more information.
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
BEGIN DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' ) END BEGIN INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22' END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
BEGIN DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' ) END BEGIN INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22' END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this: SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89 The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1. Why?
I hvae a stored procedure that has this at the end of it: BEGIN EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, '' END SET NOCOUNT OFF
SELECT Something FROM Something Joins….. Where Something = Something now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure. What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
On cmd.ExecuteNonQuery(); I am getting the following error "Procedure or function usp_Question_Count has too many arguments specified."Any Ideas as to how to fix this. Oh and I will include the SP down at the bottom // Getting the Correct Answer from the Database. int QuiziD = Convert.ToInt32(Session["QuizID"]); int QuestionID = Convert.ToInt32(Session["QuestionID"]); SqlConnection oConn = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\quiz.mdf;Integrated Security=True;User Instance=True"); SqlCommand cmd = new SqlCommand("usp_Question_Count", oConn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter QuizParam = new SqlParameter("@QuizId", SqlDbType.Int); QuizParam.Value = QuiziD; cmd.Parameters.Add(QuizParam); SqlParameter QuestionParam = new SqlParameter("@QuestionID", SqlDbType.Int); QuestionParam.Value = QuestionID; cmd.Parameters.Add(QuestionParam); SqlParameter countParam = new SqlParameter("@CorrectAnswer", SqlDbType.Int); countParam.Direction = ParameterDirection.Output; //cmd.Parameters.Add(workParam); cmd.Parameters.Add(countParam); oConn.Open(); cmd.ExecuteNonQuery(); // get the total number of products int iCorrectAnswer = Convert.ToInt32(cmd.Parameters["@CorrectAnswer"].Value); oConn.Close();Heres the stored ProcedureALTER PROCEDURE dbo.usp_Find_Correct_Answer @QuizID int, @QuestionID int, @CorrectAnswer int OUTPUT /* ( @parameter1 int = 5, @parameter2 datatype OUTPUT ) */AS /* SET NOCOUNT ON */ SELECT @CorrectAnswer=CorrectAnswer FROM Question WHERE QuizID=@QuizID AND QuestionOrder=@QuestionID
I have a web application that is data driven from a SQL 2005 database. The application needs to prompt the user to enter some information that will be logged into a SQL table. It should always be the last row in the table that is being worked on at any one time. Over a period the user will need to enter various fields. Once the data is entered into a field they will not have access to amend it. Therefore I need to be able to SELECT the last row of a table and present the data to the user with the 'next field' to be edited. As I'd like to do this as a stored procedure which can be called from an ASP page I wonder if anyoen might be able to help me with some T-SQL code that might achieve it? Regards Clive
I'm hoping someone can help me w/this query. I'm gathering data from two tables and what I need to return is the following: An employee who is not in the EmployeeEval table yet at all and any Employee in the EmployeeEval table whose Score column is NULL. I'm So lost PLEASE HELP. Below is what I have. CREATE PROCEDURE dbo.sp_Employee_GetEmployeeLNameFNameEmpID ( @deptID nvarchar(20), @Period int)ASSELECT e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText, e.EmployeeID, e.LastName + ',' + e.FirstName AS FullName, ev.Score FROM Employee AS eLEFT JOIN EmployeeEval as ev ON e.EmployeeID = ev.EmployeeIDWHERE e.DeptID = @deptId OR (e.deptid = @deptID AND ev.Score = null AND ev.PeriodID = @Period)GO
hello, I have a small problem. i'm adding records into the DB. the primary key is the company name which is abviously unique. before saving the record i check in the stored procedure if the company code is unique or not. if unique then the record is added & an output parameter is set to 2 & should b returned to the data access layer. if not unique then 3 should be returned. but everytime it seems to be returning 2 whether it is unique or not. can u plz help me? here is the code of the data access layer: cmd.Parameters.Add("@Status", SqlDbType.Int); cmd.Parameters["@Status"].Value = ParameterDirection.ReturnValue;
//cmd.UpdatedRowSource = UpdatedRowSource.OutputParameters; cmd.ExecuteNonQuery(); status = (int)cmd.Parameters["@Status"].Value;
here is the stored procedure: CREATE PROCEDURE spOrganizationAdd( @OrgCode varchar(10), @OrgName varchar(50), @AddressLine1 varchar(30), @AddressLine2 varchar(30), @City varchar(15), @State varchar(15), @Country varchar(15), @PinCode varchar(7), @Phone varchar(20), @Fax varchar(20), @Website varchar(30), @Email varchar(50), @CreatedBy int, @LastModifiedBy int, @Status INTEGER OUTPUT) AS BEGIN TRAN IF EXISTS(SELECT OrgCode FROM tblOrganizationMaster WHERE OrgCode = @OrgCode) BEGIN SET @Status = 3
END ELSE BEGIN INSERT INTO tblOrganizationMaster VALUES( @OrgCode, @OrgName, @AddressLine1 , @AddressLine2 , @City , @State, @Country, @PinCode, @Phone, @Fax , @Website, @Email, @CreatedBy , GETDATE(), @LastModifiedBy , GETDATE()) SET @Status = 2 END IF @@ERROR = 0 COMMIT TRAN ELSE ROLLBACK TRAN
Hello, Im currently working on a asp.net file hosting wesite, and im being faced with some problems I currently have 4 sql tables. DownloadTable, MusicTable, ImageTable and VideoTable. Each of those tables contain a UserName column, a fileSize column and sme other columns. What i want to do is add up all the values in the fileSize column, for each table, and then add them up with the tables, and return one value, and all this happens where the UserName column corresponds to the UserName of the currently logged on User. I already have an sql statement that performs this exact thing. It is as follow select TotalDownLoadSize = sum(DownloadSize) + (select sum(VideoSize) from VideoTable where ([UserName] = @UserName ) ) + (select sum(ImageSize) from Images where ([UserName] = @UserName) ) + (select sum(MusicSize) from MusicTable where ([UserName] = @UserName) ) from DownloadTable where ([UserName] = @UserName) But the problem is that all of the tables have to have a value in there size columns for the corresponding user, for this sql statement to return something. For example, lets say i logged in as jon. If, for the UserName jon, the sum of DownloadTable returned 200, the sum of VideoTable returned 300, the sum of MusicTable returned 100. The sql statement i stated above will return 4 instead of 600, if the sum of ImageTable returned zero. Is there way around this? Im not sure if ive been cleared enough, please feel free to request more info as needed. Thank you very much, and thx in advance.
I have a stored procedure that does all the dirty work for me. I'm just having one issue. I need it to run the total number of RECORDS, and I need it to return the total number of new records for TODAY. Here is part of the code:SELECT COUNT(ID) AS TotalCount FROM CounterSELECT COUNT(*) AS TodayCount FROM Counter WHERE DATEPART(YEAR, visitdate) = Year(GetDate()) AND DATEPART(MONTH, visitdate) = Month(GetDate()) AND DATEPART(DAY, visitdate) = Day(GetDate())The statement works great, I just need to return TotalCount and TodayCount in one query. Is this possible?
Okay so here's a wierd one. I use SQLYog to peek into/administrate my databases.I noticed that this chunk of code is not producing a value... Using Conn As New MySqlConnection(Settings.MySqlConnectionString) Using Cmd As New MySqlCommand("SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE '%?CurrentPage%') AND Enabled=1", Conn) With Cmd.Parameters .Add(New MySqlParameter("?CurrentPage",thisPage)) End With Conn.Open() Exists = Cmd.ExecuteScalar() End Using End Using Exists is declared outside of that block so that other logic can access it. thisPage is a variable declared outside, as well, that contains a simple string, like 'index.aspx'. With the value set to 'index.aspx' a count of 1 should be returned, and is returned in SQLYog. SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE '%index.aspx%') AND Enabled=1 This produces a value of 1, but NO value at all is returned from Cmd.ExecuteScalar(). I use this method in MANY places and don't have this problem, but here it rises out of the mist and I can't figure it out. I have no Try/Catch blocks so any error should be evident in the yellow/red error screen, but no errors occur in the server logs on in the application itself. Does anybody have any ideas?
Hi everybody, I have a stored procedure that creates some temporary tables and in the end selects various values from those tables and returns them as a datatable. when returning the values, some fields are derived from other fields like percentage sold. I have it inside a Coalesce function like Coalesce((ItemsSold/TotalItems)*100, 0) this function returns 0 for every row, except for one row for which it returns 100. Does that mean for every other row, the value of (ItemSold/TotalItems)*100 is NULL ? if so, how can I fix it ? any help is greatly appriciated. devmetz
Hi I have two text boxes, Textbox A and Textbox B, what i am trying to do is when values are entered in the textboxes, a query runs and returns the results in a datagrid. However I am unusre on how to structure the stored procedure. Can anyone lead me in the right direction, thanks
I have the following stored procedure that is returning nothing can anyone please help? SELECT job_id, line_num, cust_id, cust_po_id, product_desc, form_num, revision, flat_size, new_art_reprint, order_qty, po_recieved_date, ord_ent_date, customer_due_date, scheduled_ship_date, act_ship_date, act_ship_qty, ship_from, ship_to, price_per_m, misc_charges, commentsFROM tblOrderWHERE (cust_id = @Cust_Id) AND (po_recieved_date BETWEEN @Start AND @End) When I input parameters I make sure my start date is before the first po_recieved_date and the end date is after it yet it is returning nothing. I also made sure that I am putting the correct @Cust_Id
I'm trying to return the identity from a stored procedure but am getting the error - "specified cast is invalid" when calling my function. Here is my stored procedure - ALTER Procedure TM_addTalentInvite @TalentID INT AS Insert INTO TM_TalentInvites ( TalentID ) Values ( @TalentID ) SELECT @@IDENTITY AS TalentInviteID RETURN @@IDENTITY
And here is my function - public static int addTalentInvite(int talentID) { // Initialize SPROCstring connectString = "Data Source=bla bla"; SqlConnection conn = new SqlConnection(connectString);SqlCommand cmd = new SqlCommand("TM_addTalentInvite", conn); cmd.CommandType = CommandType.StoredProcedure; // Update Parameterscmd.Parameters.AddWithValue("@TalentID", talentID); conn.Open();int talentUnique = (int)cmd.ExecuteScalar(); conn.Close();return talentUnique; } Any help you can give me will be greatly appreciated thanks!
I have the following query. select top 3 dbo.oncd_incident.open_date,dbo.onca_product.description,dbo.onca_user.full_name,dbo.oncd_incident.incident_id,email, dbo.oncd_contact.first_name,dbo.oncd_contact.last_name,dbo.oncd_contact.contact_id from dbo.oncd_incident inner join dbo.oncd_incident_contact on dbo.oncd_incident_contact.incident_id=dbo.oncd_incident.incident_id inner join dbo.oncd_contact on dbo.oncd_contact.contact_id=dbo.oncd_incident_contact.contact_id inner join dbo.oncd_contact_email on dbo.oncd_contact_email.contact_id=dbo.oncd_contact.contact_id inner join dbo.onca_user on dbo.oncd_incident.assigned_to_user_code=dbo.onca_user.user_code inner join dbo.onca_product on dbo.onca_product.product_code=dbo.oncd_incident.product_code where dbo.oncd_incident.incident_status_code='CLOSED' and email is not null and dbo.oncd_incident.open_date>DateAdd(wk,-2,getdate()) and dbo.oncd_incident.completion_date>=DateAdd(dd,-2,getdate()) and dbo.oncd_incident.assigned_to_user_code in (select user_code from dbo.onca_user) order by newid() I want the query to be executed for each row returned by the sub query.If I use IN keyword it returns top 3 rows for any 3 of the users.But I want top 3 rows to be returned for each of teh user.Please help.
I have a stored procedure that inserts a record. I call the @@Identity variable and assign that to a variable in my SQL statement in my asp.net page.
That all worked fine when i did it just like that. Now I'm using a new stored procedure that inserts records into 3 tables successively, and the value of the @@Identity field is no longer being returned.
As you can see below, since I don't want the identity field value of the 2 latter records, I call for that value immediately after the first insert. I then use the value to populate the other 2 tables. I just can't figure out why the value is not being returned to my asp.net application. Think there's something wrong with the SP or no?
When I pass the value of the TicketID variable to a text field after the insert, it gives me "@TicketID".
I'm using SQL-MSDE and have a table defined with a 'identity seed' column that automatically gets assigned when a record is added (I do not load this value). This column is also my KEY to this table. I'm using INSERT to add a record. Is there a way to return this KEY value after doing the INSERT?
