i have 2 server, Database Server and Application Server.
i create a stored prosedure in Database SERVER (sql 2005)
i call this stored prosedure from my application but , my application dont see my stored prosedure..
what is a problem ? this is working my local computer and database.
my stored prosedure : sp_MyStoredProsedure
application :
...........Using myConnection As New SqlConnection(Config.ConnectionString)Dim myCommand As SqlCommand = New SqlCommand("sp_MyStoredProsedure", myConnection)
Hello Dears, I need to make an stored procedure with two parameter the first patameter will take the you birthdate and the second will take the the the date for now and this sp will calculate your age
how can i do this anyone will help i will greetful
I am inserting value in the database with identity column, but I want to know the identity column value that is inserted by this command. I don't want to use any additional query to fetch this value, so I have created a stored Prosedure that Inserts value with identity column in the database and also returns last identity column value. But i don't know how to fetch this value in ASP.Net, because ExecuteNonQuery() is used for INSERT,UPDATE and DELETE only, bu using this method my half of need will be completed (that is insertion) but how i fetch returned value. I am putting table and Stored Prosedure, pls reply me how i get returned value. --following is my emp table create table emp ( EID int identity(1,1), EName varchar(50) ) --following is my stored prosedure
create proc ReturnID @ename varchar(50) as insert into emp values(@ename) begin tran declare @err int set @err = @@ERROR if @err <> 0 return select max(eid) from emp commit tran
--following command insert value in database and returns last ID ReturnID jasim
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
set Status = 'Open' from (select [order].OrderId from CRM_Order [order]
inner join crm_orderproduct Oproduct on Oproduct.OrderId=[order].OrderId
group By [order].OrderId
having count(Oproduct.OrderProductId)=0 )
I m trying to update the Order <Table> Field Status, where order products count is zero. The Select statement lonely working fine but in update statement getting error.
Message ordering is of utmost importance in our application.
As i found in testing the only way to ensure message ordering is if they are in the same conversation.If you send multiple messages in different conversations there is no garantee which will be processed first.
Therefore i will be creating conversations that last "forever", that is using a single conversation.
I plan on doing a BEGIN DIALOG CONVERSATION when an inititator site is setup and writing the conversation handle guid to a table.
I will them simply SEND ON SONVERSATION using the guid, i will never issue a end conversation from target or initiator.
Is this theory solid, ie: is there a better way or best practice to do this?
I know that conversatons persist with sql server restarts, however what happens if an initiator site db is restored ?
I was thinking of adding logic to first check if a conversation endpoint exists with the specified guid if not , then start another conversation. But is this the best way?
hi there, i have a invoice template that when printed has a box for the users address (which there always will be) and a box for their delivery address if they are having the items delivered. However sometimes they dont have things delivered and so the record in the delivery table does not exist. In this case it will throw an error, how can i avoid this. the code im using i have posted below string sql = "SELECT [del_address], [del_post_code], [del_date], [del_time] From tbl_del WHERE order_ID = " + intOrderID;
//This creates a sql command which executes the sql statement.SqlCommand sqlCmd = new SqlCommand(sql, myConn); myConn.Open();SqlDataReader dr = sqlCmd.ExecuteReader(); //This reads the first result from the sqlReader dr.Read(); try {
Hi All,Just wondered if i could ask you for some advice and help.I'm looking to write a site, and one of the functions of the site is to remind the user of a certain event at a certain time. I was wondering how i would do this? or if anyone has a working example i could use?Also I dont quite know what database to use...... would MySQL do the function i am after???? If so then no more problem lol If not what database would you recommend i use?ThanksDanny
Hi all, can anyone explain to me why i am getting this error:
Cannot insert the value NULL into column 'WORKPATTERN_END', table 'MockDownload_V52.dbo.EMPLOYEE_WORKPATTERN'; column does not allow nulls. INSERT fails.
The code i am using is:
DELETE EMPLOYEE_WORKPATTERN INSERT INTO EMPLOYEE_WORKPATTERN( EMPLOY_REF) SELECT CAST(EMPLOY_REF AS VARCHAR(10)) FROM EMPLOYEE
Hi Friends, is it possible to find a table in which database it is? ex: i have one table name rider. i've created it in one database, but i dont know in which database it is.but i know the server name.
I have a very smal question that im doing with the query builder in visual studio .net 2005.
there i have this sql question
SELECT tblObjekt.ObjektArkivID, tblObjekt.KundID, tblObjekt.KundensArkivNummer, tblObjekt.HuvudTitel, tbl_Spar.SparID FROM tblObjekt INNER JOIN tbl_Spar ON tblObjekt.ObjektArkivID = tbl_Spar.ObjektArkivID.
The problem i have is that it only finds 9 of 11 rows in the database. I thought that maybe some field were "null" but every field has some text in it.
The query must work since it finds 9 items, but 2 are missing. Someone that knows what can be wrong?
Hi. I have a table with Login and Logoff Time of users, but there could be duplicate Logtimes in the dataset, but for different products. Because of this I cant do a distinct in the dataset. I need the Product and some other details in my Report.
I tried to make two datasets. One for the Select distinct and one for the other.
But the Problem is: in my report, I need a table, where I make the Sum of the Logintime a day and in another column I calculate with data from the other dataset.(Logtime + data from dataset2). But this doesnt work, so I think, that is it not possible to join 2 dataset in one table.
Hello All Prg's I need your help, I use Database SQL server Express with C# when I entred some data into DB by clciking on the table in server Explorer in Visual Studio 2005 IDE, and then I make SQL in the code as: if (e.KeyCode == Keys.Return) { bool res; int OldPlayerId = 0; string ConnectionStr = Program.Member_Connect; string sqlQuery = "select Player_ID from Player where Player_Code = '" + textBox1.Text + "'"; SqlConnection Sql_connection = new SqlConnection(ConnectionStr); Sql_connection.Open(); SqlCommand command = new SqlCommand(sqlQuery, Sql_connection); SqlDataReader dataReader = command.ExecuteReader(); if (dataReader.HasRows) { if (dataReader.Read()) OldPlayerId = dataReader.GetInt32(0); else MessageBox.Show("rtet"); } else { res = false; } comboBox2.SelectedValue = textBox1.Text; command.Dispose(); Sql_connection.Close(); Sql_connection.Dispose(); comboBox1.SelectedValue = OldPlayerId; } it work correctly on the old data that I entred them manualy, but when I enter new data from my project, this query don't give correct value, it still work correctly on old data,
I tried to take same data by combobox connected to view that contain same sql ,it work always. but I need manualy Sql. Please help me , I am very lating to delever my project... please help me thank you very mush
Im trying to recover my database using the mdf and ldf files.I dont have any backup and i have recovered two of the mdf files usinga tool which "discovers" deleted files after hard drive formatting...It sounds cool, isnt it...:? :(Obviously, i get a "suspect" message when the server starts and the logfile says this kind of things:ˇ"Full PathName.MDF is not a primary database file." (This is one ofthe files repaired using the magic tool.ˇError: 823, Severity: 24, State: 6ˇ"I/O error (torn page) detected during read at offset0000000000000000 in file 'Full PathiName2.mdf'... Name2.mdf is thesecond fileˇDevice activation error. The physical file name 'Full PathName2.mdf'may be incorrect.When i try to execute the command "DBCC CHECKDB ('Database_Name') WITHPHYSICAL_ONLY" i get the following message :ˇCould not open FCB for invalid file ID 0 in database 'Logs'.Do you have any ideas? Thank you very much...:D
Help Stored procedure working but not doing anything New Post Quote Reply Please i need some help.
I am calling a stored procedure from asp.net and there is a cursor in the stored procedure that does some processing on serval tables.
if i run the stored procedure on Query Analyzer it works and does what it is suppose to do but if i run it from my asp.net/module control it goes. acts likes it worked but it does not do what is suppose to do. i believe the cursor in the stroed procedure does not run where is called programmatically from the asp.net/module control page.plus it does not throw any errors
This is the code from my control System.Data.SqlClient.SqlParameter [] param={new System.Data.SqlClient.SqlParameter("@periodStart",Convert.ToDateTime(startDate)),new System.Data.SqlClient.SqlParameter("@periodStart",Convert.ToDateTime(endDate)),new System.Data.SqlClient.SqlParameter("@addedby",UserInfo.FullName+ " "+UserInfo.Username)};
set @NoOfDeadPensioners=0 set @NoOfEnrolledPensioners=0 set @DeactivatedPensioners=0 set @actionType ="PayList Generation"
DECLARE paylist_cursor CURSOR FORWARD_ONLY READ_ONLY FOR
select p.pensionerId,p.isAlive,p.isActive,py.currentMonthlypension from pensioner p left outer join pensionpaypoint py on p.pensionerid=py.pensionerId
where p.isActive = 1
OPEN paylist_cursor
FETCH NEXT FROM paylist_cursor INTO @pensioner_id,@isAlive,@isActive,@currentMonthlypension
WHILE @@FETCH_STATUS = 0 BEGIN
set @NoOfLoadedPensioners=@NoOfLoadedPensioners+1 if(@isAlive=0) begin update Pensioner set isActive=0 where pensionerid=@pensioner_id set @DeactivatedPensioners =@@ROWCOUNT+@DeactivatedPensioners set @NoOfDeadPensioners =@@ROWCOUNT+@NoOfDeadPensioners end else begin insert into pensionpaylist(pensionerId,dateAdded,addedBy, periodStart,periodEnd,amountPaid) values(@pensioner_id,getDate(),@addedby, @periodStart, @periodEnd,@currentMonthlypension) set @NoOfEnrolledPensioners =@@ROWCOUNT+ @NoOfEnrolledPensioners end
-- Get the next author. FETCH NEXT FROM paylist_cursor INTO @pensioner_id,@isAlive,@isActive,@currentMonthlypension END
CLOSE paylist_cursor DEALLOCATE paylist_cursor
set @reportSummary ="The No. of Pensioners Loaded: "+Convert(varchar,@NoOfLoadedPensioners)+"<BR>"+"The No. Of Deactivated Pensioners: "+Convert(varchar,@DeactivatedPensioners)+"<BR>"+"The No. of Enrolled Pensioners: "+Convert(varchar,@NoOfEnrolledPensioners)+"<BR>"+"No Of Dead Pensioner from Pensioners Loaded: "+Convert(varchar,@NoOfDeadPensioners) insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType) values(getDate(),0, @periodStart, @periodEnd,@reportSummary,'Pay List Generation')
if (@@ERROR <> 0) BEGIN
insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType) values(getDate(),1, @periodStart,@periodEnd,@reportSummary,'Pay List Generation')
I'm pretty new when it comes to Stored Procs, and for some reason, the CurAge isn't coming up with an actual numeric value. Does anyone see what I am doing wrong?
CREATE PROCEDURE sp_GetTargetProducts @hmid int, @wtid int AS DECLARE @CurAge INT if exists( SELECT LastName, FirstName, HMID, DATEDIFF (YY , BirthDate, GetDate()) As CurAge FROM Members WHERE hmid = @hmid ) BEGIN SELECT DISTINCT Product, ProductAdvance, AgeBottom, AgeTop, ProductName, ProductDesc, ProductPrice, ProductPicture FROM Products WHERE wtid = @wtid AND AgeBottom <= @CurAge AND AgeTop >= @CurAge GROUP BY Product, ProductAdvance, AgeBottom, AgeTop, ProductName, ProductDesc, ProductPrice, ProductPicture return(1) END else return(0) GO
I m using sql server 2005 i have got one request ,to apply page level locking on database can nyone how it is done i can do that for a single script and for session(transaction isolation level) but dont know about database level locking scheme
I do weekly full backups of my SQL databases via a scheduled T-SQL job.I noticed that I have some static databases that dont normally change,so I dont want to back it up if it has not changed, but when it does,then I want a backup.Is there something in the master table, as example, that I can checkprior to running the backup that will indicate any changes?An example is the Northwind database. I could exclude it from thebackup, but then I would not back it up if it where to change. Againthis is an example, I would not need to modify Northwind.Thanks in advance for any ideas; they usually give me ideas to problemsyet to come....Rob Camarda
Anyone can help me why is this not working? My function is public void updateSupplierInformation(string FirstLetter, string Vendor, string SegmentTeam, string VendorRank, string ScrubOwner) { SqlCommand myCommand = new SqlCommand("sp_LAST_UpdateSupplier", myConnection);
myConnection.Close(); } My Stored procedure is: ALTER PROCEDURE sp_LAST_UpdateSupplier @FirstLetterS varchar(1), @VendorS varchar(30), @SegTeamS varchar(30), @VendorRankS varchar(30), @ScrubOwnerS varchar(30) AS UPDATE [gntm_user].LAST_Supplier_Ownership SET First_Letter =@FirstLetterS,Seg_Team =@SegTeamS,Vendor_Rank =@VendorRankS, Scrub_Owner =@ScrubOwnerS WHERE Vendor=@VendorS
RETURN
When I debug on any of the myParam value it displays "@FirstLetterS,@VendorS" etc. not the value which is being passed in the function which is FirstLetter,Vendor. Can anyone help me on this please? Thanks in advance
Hi, I am working on VS 2005 and I am using the grid view with Stored procedure the case is ..I want the grid to view all the supplier records by default .. but if the user specified a supplier name and/or type the grid should onlyview the relevant records.. What i did is .. I have created a text box (to enter the supplier name) and a drop down list (to choose the type) ** The problem is the grid does not appear unless I choose and fill the text box and drop down list and it only works with the type notwith the name ((does not match both parameters)) Can you please help I am stuck !!-------------------------------- My procedure is as following: ALTER PROCEDURE p_test @SupName nvarchar(200)='',@TypeID int= 0 AS declare @SQL_Text varchar(8000)set @SQL_text = 'select * from dbo.v_supplier' +' where 1 = 1' if rtrim(ltrim(@SupName)) <> ''set @SQL_Text = @sql_text + ' and name = '''+ @SupName +'''' if @TypeID <> ''set @SQL_Text = @sql_text + ' and type_id = '+ CONVERT(varchar, @TypeID) if CONVERT(varchar, @TypeID)= '0'set @SQL_Text = @sql_text BEGIN exec(@sql_text) END ---------------------------------------- My grid parameters is as following: <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSourceSup"></asp:GridView> <asp:SqlDataSource ID="SqlDataSourceSup" runat="server" ConnectionString="<%$ ConnectionStrings:con %>" SelectCommand="p_test" SelectCommandType="StoredProcedure"> <SelectParameters><asp:ControlParameter ControlID="txtSup" Name="SupName" PropertyName="Text" Type="String" DefaultValue="" /><asp:ControlParameter ControlID="drpType" Name="TypeID" PropertyName="SelectedValue" DefaultValue = 0 Type="Int32" /></SelectParameters> </asp:SqlDataSource>
Hi can someone tell me whats wrong with this stored procedure. All im trying to do is get the publicationID from the publication table in order to use it for an insert statement into another table. The second table PublicaitonFile has the publicaitonID as a foriegn key. Stored procedure error: cannot insert null into column publicationID, table PublicationFile - its obviously not getting the ID. ALTER PROCEDURE dbo.StoredProcedureUpdateDocLocField @publicationID Int=null,@title nvarchar(MAX)=null,@filePath nvarchar(MAX)=null ASBEGINSET NOCOUNT ON IF EXISTS (SELECT * FROM Publication WHERE title = @title)SELECT @publicationID = (SELECT publicationID FROM Publication WHERE title = @title)SET @publicationID = @@IDENTITYEND IF NOT EXISTS(SELECT * FROM PublicationFiles WHERE publicationID = @publicationID)BEGININSERT INTO PublicationFile (publicationID, filePath)VALUES (@publicationID, @filePath)END
What is the best way to store images in sql server and then retrieve them with stored proc's using vb.net/asp.net? There seems to be a lot of different opinions about just saving the img tag, the path, etc.
Having a little trouble not seeing why this insert is not happening.... --snip-- DECLARE c_studId CURSOR FOR SELECT studentId FROM students FOR READ ONLY OPEN c_studId FETCH NEXT FROM c_studId INTO @studentId IF( @@FETCH_STATUS = 0 ) BEGIN SET @studRec = 'Found' END CLOSE c_studId DEALLOCATE c_studId BEGIN TRAN IF (@studRec <> 'Found') BEGIN INSERT INTO students (studentId) VALUES (@studentId) END Well, you get the idea, and I snipped a lot of it.Why is it not inserting if the user is not found?Thanks all,Zath
I have a SP below that authenticates users, the problem I have is that activate is of type BIT and I can set it to 1 or 0. If I set it to 0 which is disabled, the user can still login. Therefore I want users that have activate as 1 to be able to login and users with activate as 0 not to login
Hi there below is my code for a sproc. however when i run it, it doesnt seem to create a table
USE [dw_data] GO /****** Object: StoredProcedure [dbo].[usp_address] Script Date: 10/24/2007 15:33:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROC [dbo].[usp_address] ( @TableType INT = null
)
AS
IF @TableType is NULL OR @TableType = 1
BEGIN
IF NOT EXISTS (SELECT 1 FROM [DW_BUILD].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'address')
I am working with a complex Stored Procedure which I need to be able to filter and sort by various criteria. Can I please ask how I can get the data that the SP returns into place that I can apply my search criteria?
Ive tried the code below in the SQL Query Analyzer but all I get is: "Server: Msg 197, Level 15, State 1, Line 10 EXECUTE cannot be used as a source when inserting into a table variable."
Hi All,I am trying to write a basic stored procedure to return a range ofvalues but admit that I am stumped. The procedure syntax used is:ALTER PROCEDURE Rpt_LegacyPurchaseOrderSp(@StartPoNumber PONumberType = 'Null',@FinishPoNumber PONumberType = 'Null')ASSET @StartPoNumber = 'PO_NUMBER_POMSTR'SET @FinishPoNumber = 'PO_NUMBER_POMSTR'SELECTIPPOMST_SID,--Start tbl_IPPOMSTPO_NUMBER_POMSTR,VENDOR_NUMBER_POMSTR,SHIP_NUMBER_POMSTR,CHANGE_ORDER_POMSTR,FOB_POINT_POMSTR,ROUTING_POMSTR,DATE_ISSUED_POMSTR,DATE_LAST_RECPT_POMSTR,CONTACT_PERSON_1_POMSTR,PREPAID_COLLECT_POMSTR,TERMS_POMSTR,AMOUNT_ESTIMATED_POMSTR,AMOUNT_RECEIVED_POMSTR,AMOUNT_PAID_POMSTR,LOCATION_CODE_POMSTR,SHIPPING_POINT_POMSTR,PRINT_IND_POMSTR,BUYER_POMSTR,SHIPMENT_POMSTR,STATUS_POMSTR,CURRENCY_POMSTR,CURRENCY_STATUS_POMSTR,AMOUNT_EST_CUR_POMSTR,AMOUNT_REC_CUR_POMSTR,AMOUNT_PAID_CUR_POMSTR,--Finish tbl_IPPOMSTIPPOITM_SID,--Start tbl_IPPOITMPO_NUMBER_POITEM,ITEM_NUMBER_POITEM,CATEGORY_POITEM,DESCRIPTION_POITEM,VENDOR_NUMBER_POITEM,DATE_ORIGINAL,DATE_RESCHEDULED,ACCOUNT_NUMBER_POITEM,STOCK_NUMBER_POITEM,JOB_NUMBER_POITEM,RELEASE_WO_POITEM,QUANTITY_ORDERED_POITEM,QUANTITY_RECVD_POITEM,UOM_POITEM,UNIT_WEIGHT_POITEM,UNIT_COST_POITEM,EXTENDED_TOTAL_POITEM,MATERIAL_NUMBER_POITEM,COMPLETE_POITEM,LOCATION_CODE_POITEM,INSPECTION_POITEM,BOM_ITEM_POITEM,COST_ACCOUNT_POITEM,CHANGE_ORDER_POITEM,TAX_CODE_POITEM,ISSUE_CODE_POITEM,QUANTITY_INSPECT_POITEM,EXC_RATE_CURR_POITEM,UNIT_COST_CURR_POITEM,EXTENDED_TOTAL_CURR_POITEM,PLANNER_POITEM,BUYER_POITEM--Finish tbl_IPPOITMIPVENDM_SID,--Start tbl_IPVENDMVENDOR_NUMBER_VENMSTR,VENDOR_NAME_VENMSTR,ADDRESS_LINE_1_VENMSTR,ADDRESS_LINE_2_VENMSTR,ADDRESS_LINE_3_VENMSTR,CITY_VENMSTR,STATE_VENMSTR,ZIP_CODE_VENMSTR,COUNTRY_VENMSTR--Finish tbl_IPVENDMFROM tbl_IPPOMSTJOIN tbl_IPPOITMON tbl_IPPOITM.PO_NUMBER_POITEM = tbl_IPPOMST.PO_NUMBER_POMSTRJOIN tbl_IPVENDMon tbl_IPVENDM.VENDOR_NUMBER_VENMSTR = tbl_IPPOMST.VENDOR_NUMBER_POMSTRWHERE tbl_IPPOMST.PO_NUMBER_POMSTR >= @StartPoNumber ANDtbl_IPPOMST.PO_NUMBER_POMSTR <= @FinishPoNumberBasically, no rows are returned for the valid (records in database)range I enter. I have been troubleshopoting the syntax. This hasinvolved commenting out references to @FinishPoNumber so in effect Ijust pass in a valid PO Number using @StartPoNumber parameter. Thisworks in terms of returning all 76545 PO records.Can anyone help me to identify why this syntax will not return a rangeof PO records that fall between @StartPoNumber and @FinishPoNumber?Any help would be greatly appreciated.Many Thanks*rohan* & Merry Christmas!
SELECT Band.Name, Member.Name FROM Member JOIN MemberOf ON Member.Mid = MemberOf.Mid JOIN Band On MemberOf.Bid = Band.Bid WHERE MemberOf.Instrument = 'keyboards';
which basically shows me the name of the bands who had keyboard players. I would like it to also display the names of the band who didnt have keyboard players, replacing the keyboard players name with "NULL".
so... my questions are :)
how do u get it to display the records that do not match the condition, and how do u get it to replace the keyboard players name with "NULL" when they do not match the condition.
PS. The three tables are
Band. Which has Bid as a primary key. Member. Which has Mid as a primary key. MemberOf. Which links these through its two foreign keys Mid and Bid.
