Datetime Query Issue With C# Stored Procedure

Sep 9, 2007

Ok, i am using the convert function to get the date format from my datetime column. My problem is when C# try's to pull the column name the column name is not sent in the query. When I run the query in the query analyzer the column name is blank in the result window. How can I get the date from the record with out losing the name of my column in the query. My data binding needs the name of the column to bind the data to the drop down list control. Here is the statement:


SQL statement
SELECT DISTINCT convert(datetime, eventDT, 110) FROM tblRecognition

C# code
ddlDateTo.DataSource = _uiCode.Fill.Date();
ddlDateTo.DataTextField = "eventDT";
ddlDateTo.DataBind();


Thank you,

View 2 Replies


ADVERTISEMENT

DateTime Error In Stored Procedure

Apr 15, 2004

Hi;

I have a stored procedure simply adds userid,logintime and status to db but I have an error when running procedure can you help me please??

Create Procedure AddLog
(
@User char(10),
@DLogon DateTime(8),
@Status bit
)
As
Insert Into Log(UserID,LogInTime,Online)
Values(@User,DLogon,Status)

ERROR:

Server: Msg 128, Level 15, State 1, Procedure AddLog, Line 9
The name 'DLogon' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

View 1 Replies View Related

Compare Datetime In Stored Procedure

Mar 14, 2008

Hi,

Can i compare date = null in stored procedure? Does this work? The syntax works but it never get into my if else statement? The weird thing here is it has been working and just this morning, it stopped. I don't know why? Could you please help? Thanks,


set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go











ALTER PROCEDURE [dbo].[UpdateActiveStates1]

@PROVIDERID INT

AS

DECLARE @STARTDATE DATETIME

DECLARE @ENDDATE DATETIME

DECLARE @ACTIVE BIT

SET @ACTIVE = 0

SET @STARTDATE = (SELECT ProhibitionStartDate FROM Providers WHERE ProviderID=@PROVIDERID)

SET @ENDDATE = (SELECT ProhibitionEndDate FROM Providers WHERE ProviderID=@PROVIDERID)

IF ((@STARTDATE = NULL OR @STARTDATE = '1753-01-01') AND (@ENDDATE = NULL OR @ENDDATE = '1753-01-01'))

BEGIN

PRINT 'Setting Inactive due to NULL Start Date and NULL End Date'

SET @ACTIVE=0

END

ELSE IF (@STARTDATE != NULL AND @STARTDATE <= GETDATE())

BEGIN

IF (@ENDDATE = NULL)

BEGIN

PRINT 'Setting Active due to NON-NULL Start Date and NULL End Date'

SET @ACTIVE=1

END

ELSE IF (@ENDDATE >= GETDATE())

BEGIN

PRINT 'Setting Active due to NON-NULL Start Date and NON-EXPIRED End Date'

SET @ACTIVE=1

END

ELSE

BEGIN

PRINT 'Setting Inactive due to NON-NULL Start Date and EXPIRED End Date'

SET @ACTIVE=0

END

END

UPDATE Providers SET Active=@ACTIVE

WHERE ProviderID=@PROVIDERID

UPDATE Actions SET Active=@ACTIVE WHERE ProviderID=@PROVIDERID







View 2 Replies View Related

Stored Procedure Excuting By Datetime

Jan 3, 2008

Hello,

I have got an SQL server stored procedure, and I would like to get this stored procedure, dbo.SpDate_Time_Minute_Today below executed against a matching or exact datetime from the database tables, PRODUCT_SALES.





Code Block

CREATE PROCEDURE dbo.SpDate_Time_Minute_Today
AS
SELECT PROD_SAL_DESC FROM PRODUCT_SALES
WHERE (CONVERT(CHAR(11), PROD_SAL_BY_DATE, 103) + ' ' + SUBSTRING(CONVERT(CHAR(17), PROD_SAL_BY_DATE, 100), 13, 19) = CONVERT(CHAR(11), CURRENT_TIMESTAMP, 103)
+ ' ' + SUBSTRING(CONVERT(CHAR(17), CURRENT_TIMESTAMP, 100), 13, 19))

/* SET NOCOUNT ON */
RETURN
PRODUCT_SALES has columns like this:

PROD_SAL_NO
PROD_SAL_BY_DATE
PROD_SAL_DESC
PROD_SAL_MFR
PROD_SAL_DEPT

Please do anyone here know how I can achieve this please. Thanks.

View 6 Replies View Related

How To Pass DateTime To A Stored Procedure

May 16, 2008

When I run the following code I get error "Incorrect syntax near 'MyStoredProcedureName".





Code Snippet
public static string GetWithDate(string date)
{
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["Development"].ToString();
SqlConnection conn = new SqlConnection(connString);
conn.Open();
XmlDocument xmlDoc = new XmlDocument();

SqlCommand cmd = new SqlCommand("usp_SVDO_CNTRL_GetPalletChildWorkExceptions", conn); //sw.WriteLine(count++);
cmd.Parameters.Add(new SqlParameter("@date", date));
try
{
cmd.ExecuteReader();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
SqlDataReader rdr = cmd.ExecuteReader(); //<---Bombs

if (conn != null)
conn.Close();
return xmlDoc.InnerXml;
}




I'm assuming this is because my Date is in the wrong format when .NET passes it. I've tested the stored procedure directly in SQL Server Managent Studio and it works (Format of date is '5/15/2008 9:16:23 PM').

View 3 Replies View Related

How Can I Format A Datetime Field In A Stored Procedure

Dec 14, 2006

How can I format a datetime field in a stored procedure to return just the date in MM/DD/YYYY format?

View 13 Replies View Related

Saving Datetime Data In A Stored Procedure

May 23, 2007

Hello everyone  I have a stored procedure that I am trying to save datetime data to at the moment I can create a string that will output 25/05/2007 18:30 Does anyone know the best way to get this into the correct format for my SP parameter
cmdPublish.Parameters.Add(new SqlParameter("@datPublishDate", System.Data.SqlDbType.DateTime));
cmdPublish.Parameters["@datPublishDate"].Value =  ????
 Thanks
 
 

View 3 Replies View Related

Passing Datetime Value Into A Class Stored Procedure

Jan 18, 2008

hello,
I have a stored procedure being called from my class.
All values are ok, except for the DateTime value. What am i doing wrong here ? Am wondering if its the date size of 50 I put there or what ?
command.Parameters.Add(new SqlParameter("@dob", SqlDbType.DateTime,50, dob1));
Error Message
Compiler ErrorMessage: CS1502: The best overloaded method match for 'System.Data.SqlClient.SqlParameter.SqlParameter(string, System.Data.SqlDbType, int, string)' has some invalid arguments
thanks
Ehi

View 3 Replies View Related

Converting A String To Datetime In A Stored Procedure

Aug 19, 2004

I have a stored procedure called from ASP code, and when it is executed, the stored procedure takes in a date as an attribute.

But since the stored procedure call is really just a string in the code, it is taking in the value as a string. So in my stored procedure, I want to convert a string value to a date value.

Any idea how this is done?

View 1 Replies View Related

Sending Null DateTime Value To Stored Procedure From VB

Nov 3, 2005

In a VB.NET script, I am adding the dbnull value to a parameter that will populate a smalldatetime column:
cmd.Parameters.Add("@unitHdApprove", System.DBNull.Value.ToString)

The stored procedure then defines the input as smalldatetime:
@unitHdApprove smalldatetime,

However, the result is that the record is inserted with 1/1/1900 as the date value, instead of <NULL>.

I'm guessing that this occurs because the conversion of a '' to date will return 1/1/1900, and VB requries the parameter value to be a string (at least with this syntax), so System.DBNull.Value.ToString really equals ''.

I've rewritten the proc to accept the date as a string instead, and then for each date, cast it to a smalldatetime or set it to null as is appropriate. But this is a really bulky way to do things with more than a few dates! Is there any way to change what is in my VB code so that the procedure will insert the actual null value?

Thanks,
Sarah

View 2 Replies View Related

MS SQL Dynamic Stored Procedure Using A Datetime Variable

Feb 5, 2004

Hi I'm new to MS SQL and trying to write a very small dynamic stored procedure which is giving me a headache.

What I have is:


CREATE PROCEDURE busy_report

@TableName varchar(255),
@reporteddate datetime=NULL

AS
if @reporteddate is null
select @reporteddate = CURRENT_TIMESTAMP

-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SET DATEFORMAT dmy

-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@TableName + "WHERE reporteddate = ' "
+ @reporteddate + " '"


-- Execute the SQL statement
EXEC(@SQLStatement)
GO

The error I keep getting is:

Server: Msg 8114, Level 16, State 4, Procedure busy_report, Line 0
Error converting data type varchar to datetime.

Any ideas appreciated.

(Edit:)

I've also tried it this way:


CREATE PROCEDURE UK_busy_report

@TableName varchar(255),
@reporteddate datetime=NULL

AS

-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SELECT @reporteddate=CONVERT(datetime, @reporteddate)
IF @@ERROR <> 0 BEGIN

/* Do some error processing */

PRINT 'Error Occured' END

ELSE
-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT vendor, reporteddate, count(vendor) FROM " +
@TableName + "WHERE reporteddate = ' "
+ @reporteddate + " '"


-- Execute the SQL statement
EXEC(@SQLStatement)
GO

Which gives me the same error!

.logic.

View 5 Replies View Related

Optimizing Stored Procedure With Datetime Parameter

Nov 23, 2005

Hi,When I pass a date time parameter the stored procedure takes about 45seconds, when I hard code the parameter it returns in 1 second. How canI rewrite my stored procedure?@createddatelower datetimeWHERE dbo.tblCaseHistory.eventdate > dateadd(d,-7,@createddatelower )AND dbo.tblCaseHistory.eventdate < dateadd(d,-6,@createddatelower ) (45seconds)vs.WHERE dbo.tblCaseHistory.eventdate > dateadd(d,-7,'11/15/05') ANDdbo.tblCaseHistory.eventdate < dateadd(d,-6,'11/15/05') (1 second)thanks for your help,Paul

View 5 Replies View Related

DATETIME Conversion Problem In Stored Procedure

Nov 19, 2006

Hi,

I'm having a problem with inserting a datetime value into a database using VB.net and a Stored Procedure. Below is my stored procedure code and VB.net code. Could somebody please tell me what I am doing wrong ... I am almost frustrated to tears .

Stored procedure:

ALTER PROCEDURE dbo.SPTest
@testvalue DATETIME
AS
INSERT INTO tbl_Rates VALUES (1.2, 1.3, @testvalue, 'EUR/USD')
RETURN 1

VB.NET code:

Dim RatesTA As New RatesDataSetTableAdapters.RatesTableAdapter
Dim ReturnVal As Object
ReturnVal = RatesTA.SPTest(Now)
Console.WriteLine(CType(ReturnVal, Integer))

When I run this the ReturnVal is 0.

I should also mention that my system uses the dd/mm/yyyy date format (Australian) and I am using VB.NET Express and SQL Server Express.

View 1 Replies View Related

Using Datetime Value In Insert Statement In Stored Procedure

Apr 26, 2008



I want to store datetime data in data table.

I am facing problem in using datetime data value in insert statement passed via stored procedure.
Any Help !Thanks in advance

View 3 Replies View Related

Subtracting Datetime In Stored Procedure And Displayed In Hh:mm:ss Format

Nov 22, 2007

 Hi there,I am trying to write a stored procedure in which I will retrieve SessionStartDate, SessionEndDate, and Duration (where Duration is calculdated by subtracting SessionEndDate from SessionStartDate).I was duration in the format  of hours:minutes:seconds.The stored procedure is pasted below. I am getting the following error. Syntax error converting datetime from character string.  Any ideas? ============================== CREATE    PROCEDURE sp_ActiveSessions_UsersBrowsingDurationByDate_List  (    @websiteID AS int = 0,    @SelectedDateFrom AS dateTime,    @SelectedDateTo AS dateTime) ASIF DateDiff(d,@SelectedDateTo,@SelectedDateFrom)=0begin    set @SelectedDateFrom=null endIF ISNULL(@SelectedDateTo, '') = ''begin    SET @SelectedDateTo = @SelectedDateFromendSET @SelectedDateTo = DATEADD(d, 1, @SelectedDateTo)SELECT UserID As 'User ID', SessionStartDate As 'Session Start Date', SessionEndDate AS 'Session End Date', ExitPageTitle As 'Exit Page Title', NumberOfPagesVisited As 'Number of Pages Visited', Convert(datetime, (CONVERT(DATETIME, SessionEndDate, 24) - CONVERT(DATETIME, SessionStartDate, 24)), 101) As 'Duration' FROM ActiveSessions WHERE UserID != 'Anonymous'GROUP BY SessionID, UserID, SessionStartDate, SessionEndDate, NumberOfPagesVisited, ExitPageTitleHAVING (min(SessionStartDate) BETWEEN @SelectedDateFrom AND @SelectedDateTo AND min(SessionEndDate) BETWEEN @SelectedDateFrom AND @SelectedDateTo)GO==============================  <Columns>                                        <asp:BoundColumn DataField="User ID" HeaderText="User ID"></asp:BoundColumn>                                        <asp:BoundColumn DataField="Session Start Date" HeaderText="Session Start Date"></asp:BoundColumn>                                        <asp:BoundColumn DataField="Session End Date" HeaderText="Session End Date"></asp:BoundColumn>                                        <asp:BoundColumn DataField="Duration" HeaderText="Duration"></asp:BoundColumn>                                        <asp:BoundColumn DataField="Number Of Pages Visited" HeaderText="Number Of Pages Visited"></asp:BoundColumn>                                        <asp:BoundColumn DataField="Exit Page Title" HeaderText="Exit Page Title"></asp:BoundColumn>                                    </Columns> ============================ 

View 1 Replies View Related

Strange Datetime Conversion Error In Stored Procedure.

Jul 23, 2005

Hi Everyone,I've been battling this for two days with no luck. I'm using SQLServer 2000.Here's the mystery: I've got a stored procedure that takes a singlevarchar parameter to determine how the result set is sorted. Here itis:CREATE PROCEDURE spDemo @SortField varchar(30)ASSELECT dtmTimeStamp, strEmpName, strOld, strNew, strActionDescFROM ActivityLogORDER BY CASE @SortFieldWHEN 'dtmTimeStamp' THEN dtmTimeStampWHEN 'strEmpName' THEN strEmpNameWHEN 'strOld' THEN strOldWHEN 'strNew' THEN strNewWHEN 'strActionDesc' THEN strActionDescENDGOWhen I execute the stored procedure in the Query Analyzer, it worksperfectly ONLY IF the @SortField parameter is 'dtmTimeStamp' or'strNew'. When passing in any of the other three possible values for@SortField, I get the following error:Server: Msg 241, Level 16, State 1, Procedure spDemo, Line 4Syntax error converting datetime from character string.Now instead of executing the stored procedure, if I copy and paste theSELECT statement directly into the Query Analyzer (after removing theCASE statement and manually trying each different value of @SortField),it works fine for all five possible values of SortField.Even though the error points to Line 4 of the stored procedure, itseems to me that the CASE statement is causing problems for some, butnot all, values of the @SortField parameter.Any ideas?Thanks,Jimmy

View 4 Replies View Related

Ad Hoc Query Vs Stored Procedure Performance Vs DTS Execution Of Stored Procedure

Jan 23, 2008



Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio

What could explain this?

Obviously,

All three scenarios are executed against the same database and hit the exact same tables and indices.

Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).

The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes

View 1 Replies View Related

Set A Variable To Datetime And Time To Exact Milliseconds In SQL Server In Stored Procedure AS A Single String

Nov 1, 2007

I need to set a variable to datetime and time to exact milliseconds in SQL server in stored procedure.
 
Example:
set  MyUniqueNumber = 20071101190708733
ie. MyUniqueNumber contains yyyymmddhhminsecms
 
Please help, i tried  the following:
1. SELECT CURRENT_TIMESTAMP; ////// shows up with - & : , I want single string as in above example.2.
select cast(datepart(YYYY,getdate()) as varchar(4))+cast(datepart(mm,getdate()) as char(2))+convert(varchar(2),datepart(dd,getdate()),101 )+cast(datepart(hh,getdate()) as char(2))+cast(datepart(mi,getdate()) as char(2))+cast(datepart(ss,getdate()) as char(2))+cast(datepart(ms,getdate()) as char(4))
 
This one doesnot display day correctly, it should show 01 but shows 1
 
 
 
 

View 2 Replies View Related

Stored Procedure In Query Analyzer Vs Linked Procedure In MS Access

Jan 12, 2007

For some reason, I run a stored procedure in Query Analyzer and it works fine. When I run the very same procedure in MS access by clicking on its link I have to run it twice. The first run gives me the message that the stored procedure ran correctly but returned no records. The second run gives me the correct number of records but I have to run it twice. I am running month-to-month data. The first run is Jan thru March. Jan and Feb have no records so I run three months on the first set. The ensuing runs are individual months from April onward. The output is correct but any ideas on why I have to do it twice in Access? I am a bit new to stored procedures but my supervisor assures me that it should be exactly the same.

ddave

View 2 Replies View Related

Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results

Jun 13, 2007

Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB  multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID,    S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName',    T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID    FROM [Item].ItemDetails I    INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID    INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID    FROM [Item].ItemDetails IOr something like that... Any thoughts? 

View 3 Replies View Related

ERROR:Syntax Error Converting Datetime From Character String. With Stored Procedure

Jul 12, 2007

Hi All,





i have migrated a DTS package wherein it consists of SQL task.

this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.



But the SP can executed in the client server. can any body help in this regard.





Thanks in advance,

Anand

View 4 Replies View Related

SQL Server Admin 2014 :: Estimated Query Plan For A Stored Procedure With Multiple Query Statements

Oct 30, 2015

When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.

1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?

<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>

2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?

View 0 Replies View Related

Stored Procedure Dbo.SalesByCategory Of Northwind Database: Enter The Query String - Query Attempt Failed. How To Do It Right?

Mar 25, 2008

Hi all,
In the Programmability/Stored Procedure of Northwind Database in my SQL Server Management Studio Express (SSMSE), I have the following sql:


USE [Northwind]

GO

/****** Object: StoredProcedure [dbo].[SalesByCategory] Script Date: 03/25/2008 08:31:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[SalesByCategory]

@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'

AS

IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'

BEGIN

SELECT @OrdYear = '1998'

END

SELECT ProductName,

TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)

FROM [Order Details] OD, Orders O, Products P, Categories C

WHERE OD.OrderID = O.OrderID

AND OD.ProductID = P.ProductID

AND P.CategoryID = C.CategoryID

AND C.CategoryName = @CategoryName

AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear

GROUP BY ProductName

ORDER BY ProductName

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:

Imports System.Collections.Generic

Imports System.ComponentModel

Imports System.Drawing

Imports System.Text

Imports System.Windows.Forms

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.Common

Imports System.Diagnostics

Public Class ConnectionPoolingForm

Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance

Public Sub New()

' This call is required by the Windows Form Designer.

InitializeComponent()

' Add any initialization after the InitializeComponent() call.

'Force app to be available for SqlClient perf counting

Using cn As New SqlConnection()

End Using

InitializeMinSize()

InitializePerfCounters()

End Sub

Sub InitializeMinSize()

Me.MinimumSize = Me.Size

End Sub

Dim _SelectedConnection As DbConnection = Nothing

Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged

_SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection)

EnableOrDisableButtons(_SelectedConnection)

End Sub

Sub DisableAllButtons()

btnAdd.Enabled = False

btnOpen.Enabled = False

btnQuery.Enabled = False

btnClose.Enabled = False

btnRemove.Enabled = False

btnClearPool.Enabled = False

btnClearAllPools.Enabled = False

End Sub

Sub EnableOrDisableButtons(ByVal cn As DbConnection)

btnAdd.Enabled = True

If cn Is Nothing Then

btnOpen.Enabled = False

btnQuery.Enabled = False

btnClose.Enabled = False

btnRemove.Enabled = False

btnClearPool.Enabled = False

Else

Dim connectionState As ConnectionState = cn.State

btnOpen.Enabled = (connectionState = connectionState.Closed)

btnQuery.Enabled = (connectionState = connectionState.Open)

btnClose.Enabled = btnQuery.Enabled

btnRemove.Enabled = True

If Not (TryCast(cn, SqlConnection) Is Nothing) Then

btnClearPool.Enabled = True

End If

End If

btnClearAllPools.Enabled = True

End Sub

Sub StartWaitUI()

Me.Cursor = Cursors.WaitCursor

DisableAllButtons()

End Sub

Sub EndWaitUI()

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

End Sub

Sub SetStatus(ByVal NewStatus As String)

RefreshPerfCounters()

Me.statusStrip.Items(0).Text = NewStatus

End Sub

Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click

Dim strConn As String = txtConnectionString.Text

Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder()

Try

bldr.ConnectionString = strConn

Catch ex As Exception

MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error)

Return

End Try

Dim dlg As New ConnectionStringBuilderDialog()

If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then

txtConnectionString.Text = dlg.ConnectionString

SetStatus("Ready")

Else

SetStatus("Operation cancelled")

End If

End Sub

Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click

Dim blnError As Boolean = False

Dim strErrorMessage As String = ""

Dim strErrorCaption As String = "Connection attempt failed"

StartWaitUI()

Try

Dim cn As DbConnection = _ProviderFactory.CreateConnection()

cn.ConnectionString = txtConnectionString.Text

cn.Open()

lstConnections.SelectedIndex = lstConnections.Items.Add(cn)

Catch ex As Exception

blnError = True

strErrorMessage = ex.Message

End Try

EndWaitUI()

If blnError Then

SetStatus(strErrorCaption)

MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

Else

SetStatus("Connection opened succesfully")

End If

End Sub

Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click

StartWaitUI()

Try

_SelectedConnection.Open()

EnableOrDisableButtons(_SelectedConnection)

SetStatus("Connection opened succesfully")

EndWaitUI()

Catch ex As Exception

EndWaitUI()

Dim strErrorCaption As String = "Connection attempt failed"

SetStatus(strErrorCaption)

MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End Sub

Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click

Dim queryDialog As New QueryDialog()

If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then

Me.Cursor = Cursors.WaitCursor

DisableAllButtons()

Try

Dim cmd As DbCommand = _SelectedConnection.CreateCommand()

cmd.CommandText = queryDialog.txtQuery.Text

Using rdr As DbDataReader = cmd.ExecuteReader()

If rdr.HasRows Then

Dim resultsForm As New QueryResultsForm()

resultsForm.ShowResults(cmd.CommandText, rdr)

SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned))

Else

SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected))

End If

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

End Using

Catch ex As Exception

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

Dim strErrorCaption As String = "Query attempt failed"

SetStatus(strErrorCaption)

MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

Else

SetStatus("Operation cancelled")

End If

End Sub
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the code successfully and I got a box which asked for "Enter the query string".
I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.

Thanks in advance,
Scott Chang

View 4 Replies View Related

Stored Procedure Query Optimization - Query TimeOut Error

Nov 23, 2004

How to optimize the following Stored procedure running on MSSQL server 2000 sp4 :

CREATE PROCEDURE proc1
@Franchise ObjectId
, @dtmStart DATETIME
, @dtmEnd DATETIME
AS
BEGIN


SET NOCOUNT ON

SELECT p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec
, count(1) "Count"
, sum(Amount) "Total"
FROM tb_Event t
JOIN tb_Prod p
ON ( t.ProdId = p.ProdId )
JOIN tb_ACDef a
ON ( t.ACDefId = a.ACDefId )
JOIN tb_Curr c
ON ( t.CurrId = c.CurrId )
JOIN tb_Event e
ON ( t.EventId = e.EventId )
JOIN tb_Setl s
ON ( s.BUId = t.BUId
and s.SetlD = t.SetlD )
WHERE Fran = @Franchise
AND t.CDate >= @dtmStart
AND t.CDate <= @dtmEnd
AND s.Status = 1
GROUP BY p.Product
, c.Currency
, c.Minor
, a.ACDef
, e.Event
, t.Dec

RETURN 1
END



GO

View 8 Replies View Related

Help: Why Excute A Stored Procedure Need To More 30 Seconds, But Direct Excute The Query Of This Procedure In Microsoft SQL Server Management Studio Under 1 Second

May 23, 2007

Hello to all,
I have a stored procedure. If i give this command exce ShortestPath 3418, '4125', 5 in a script and excute it. It takes more 30 seconds time to be excuted.
but i excute it with the same parameters  direct in Microsoft SQL Server Management Studio , It takes only under 1 second time
I don't know why?
Maybe can somebody help me?
thanks in million
best Regards
Pinsha 
My Procedure Codes are here:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ShortestPath] (@IDMember int, @IDOther varchar(1000),@Level int, @Path varchar(100) = null output )
AS
BEGIN
 
if ( @Level = 1)
begin
select @Path = convert(varchar(100),IDMember)
from wtcomValidRelationships
where wtcomValidRelationships.[IDMember]= @IDMember
and PATINDEX('%'+@IDOther+'%',(select RelationshipIDs from wtcomValidRelationships where IDMember = @IDMember) ) > 0
end
if (@Level = 2)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and PATINDEX('%'+@IDOther+'%',B.RelationshipIDs) > 0
end
if (@Level = 3)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',C.RelationshipIDs) > 0
end
if ( @Level = 4)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0
and PATINDEX('%'+@IDOther+'%',D.RelationshipIDs) > 0
end
if (@Level = 5)
begin
select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember)
from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D, wtcomValidRelationships as E
where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0
and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0
and charindex(convert(varchar(100),E.IDMember),D.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',E.RelationshipIDs) > 0
end
if (@Level = 6)
begin
select top 1 @Path = '' from wtcomValidRelationships
end
END
 
 
 

View 6 Replies View Related

SQL Query: Finding Records Between Datetime Inside Datetime

Mar 17, 2007

Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table:
ID (int)     |    stamp_start (Type: DateTime)        |      stamp_end (Type: DateTime)           |      Username (varchar)0             |      17-03-07 12:00:00                      |            17-03-07 12:30:00                     |     Hovgaard
The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time.
 Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark

View 2 Replies View Related

Stored Procedure Query

Jun 27, 2007

hey.. say i have a table with 4 columns, Id, col1, col2 & col 3the way it works is the id and one of the col's will have info, the other 2 cols will be emptyim trying to write a proc that returns the value of the column that has info + a number that is stored in a string to represent the column eg: CREATE PROCEDURE proc_Test     @Id int,      @Answer varchar(100) outputasset nocount on    select @Answer = col1 +', 1'from myTablewhere Id= @Idif @@rowcount < 1select @Answer = col2 +', 2'from myTablewhere Id= @Idif @@rowcount < 1

select @Answer = col3 +', 3'
from myTable
where Id= @IdreturnGO but for some reason, it only process's the first select statment and if nothing is in the column, it returns - ', 1' cheers!!! 

View 4 Replies View Related

Need Stored Procedure For This Query

Apr 1, 2008

 Hi,I am weak in writing stored procedure and want to learn it step by step.Now I have written a query and the requirement is such that I need to convert it in stored procedure.query:Select distinct empskill.tcatid,Coalesce(prm,0) as prm,coalesce(secn,0) as secn,a as technology,coalesce(skd,0) as skd,coalesce(knd,0) as knd,coalesce(tnd,0)as tnd,coalesce(dnd,0) as dnd from empskill RIGHT OUTER JOIN (select tcatid,Count(skilltypeid) AS prm from empskill where skilltypeid=1 group by tcatid) prms ON empskill.tcatid=prms.tcatid LEFT OUTER JOIN (select tcatid,Count(skilltypeid) AS secn from empskill where skilltypeid=2 group by  tcatid) secs on empskill.tcatid=secs.tcatid RIGHT OUTER JOIN (select technology.category as a,empskill.tcatid from empskill,technology where empskill.tcatid=technology.tcatid group by empskill.tcatid,technology.category ) s ON empskill.tcatid=s.tcatid LEFT OUTER JOIN (select tcatid,Count(skilllevelid) AS skd from empskill where skilllevelid=1 group by tcatid) skds on empskill.tcatid=skds.tcatid LEFT OUTER JOIN (select tcatid,Count(skilllevelid) AS knd from empskill where skilllevelid=2 group by tcatid) knds on empskill.tcatid=knds.tcatid LEFT OUTER JOIN (select tcatid,Count(skilllevelid) AS tnd from empskill where skilllevelid=3 group by  tcatid) tnds on empskill.tcatid=tnds.tcatid LEFT OUTER JOIN (select tcatid,Count(skilllevelid) AS dnd from empskill where skilllevelid=4 group by tcatid) dnds on empskill.tcatid=dnds.tcatid union select top 1 500 as tcatid,'' as prm,'' as sec,'more' as technology,'' as skd,'' as knd,'' as tnd,'' as dnd from empskill Can you please explain step by step how to convert this to stored procedure.Thanks a lot 

View 3 Replies View Related

Stored Procedure In A Query

Nov 16, 1999

In access, I can create a function that I can call in a query. It runs that function iteratively. Do I have the same ability with SQL server? Can I call a stored procedure WITHIN a query? I would like to be able to do something like:

Insert into tblOrders(spFirst_Name) Select {Call spUpperLower(tblClients.First_Name)} from tblClients Where ...


So basically, spUpperLower would run on every First Name Row in the result set.

Any help would be much appreciated.


Thanks,
Brad

View 3 Replies View Related

Run A Stored Procedure From A Query?

Oct 2, 2001

I want to run a stored procedure from a query...
can I do this? Thanks!

CREATE VIEW dbo.V_EmploymentTerminationsResignations
AS
--the next 3 lines produce errors...
declare @StartDate datetime
declare @EndDate datetime
exec [hrs2].[dbo].[sp_getquarterinfo] --@StartDate OUTPUT, @EndDate OUTPUT
--they're designed to replace the hard coded stuff in the WHERE statement...

--a snippet of the originl view...
SELECT TOP 100 PERCENT dbo.Employee.OrgID, dbo.Employee.SSN, dbo.Employee.EMPLOYEE_NAME, , dbo.Employee.SPECIAL_STATUS,
WHERE ...
(dbo.Employee.LAST_PERS_ACTN_DATE BETWEEN CONVERT(DATETIME, '2001-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2001-10-01 00:00:00', 102))

ORDER BY dbo.Employee.LAST_PERS_...

View 1 Replies View Related

Query To Stored Procedure

Oct 22, 2007

Hi,

I have created an SQL query which returns the expected data. However, when I try to run it as a stored procedure, I get no rows returned.

(I assume I running the procedure correctly e.g. exec getNonDeployed RCN20047)


Code:

CREATE PROCEDURE getNonDeployed
@packageID text
AS
BEGIN

DECLARE @CollectionID VARCHAR(10)
DECLARE @Collections TABLE (CollectionID VARCHAR(10))

set @CollectionID = (SELECT
CollectionID
from ProgramOffers
where OfferID='@packageID');

WITH CollectionFull (SubCollectionID) AS
(
-- Create the anchor query. This establishes the starting
-- point
SELECT
SubCollectionID
from v_CollectToSubCollect a
where a.SubCollectionID=@CollectionID
UNION ALL
-- Create the recursive query. This query will be executed
-- until it returns no more rows
select
a.SubCollectionID
from v_CollectToSubCollect a
inner join CollectionFull b on b.SubCollectionID=a.ParentCollectionID
)
Insert Into @Collections
SELECT * FROM CollectionFull

select a.RuleName AS MACHINENAME, c.IPAddress0 as IPADDRESS
from v_CollectionRuleDirect a
inner join @Collections b on b.CollectionID=a.CollectionID
inner join v_GS_DEVICE_NETWORK c on c.ResourceID=a.ResourceID
where RuleName not in (select distinct a.MachineName as MACHINENAME
from v_StatusMessage a, v_StatMsgAttributes b, v_CollectionRuleDirect c, v_GS_DEVICE_NETWORK d, v_Collection e
where (a.RecordID=b.RecordID AND a.MachineName=c.RuleName and c.ResourceID=d.ResourceID and c.CollectionID=e.CollectionID)
AND b.AttributeValue IN ('@packageID'))


END

View 1 Replies View Related

Query In Stored Procedure

Jul 31, 2007

Hi All:

I have a situation where Im calling a stored procedure to insert some information.

When this information is inserted it is given a date/time stamp - say something in Aug.

There will be some corresponding records from the previous month already in the database that have some additional information that was manually added.

I need to query the corresponding records from the previous month and insert that info into the records that were just inserted.

the problem Im having is that i need to grab the most recent corresponding record. could be a day or a month prior to the one I just inserted. the also could be a record for months prior to that.

So how do i get the most recent corresponding record to my inserted record

Any suggestion on how to query this? and then pass the result to an udpate statement

View 8 Replies View Related

Using A Stored Procedure As A Sub Query

Jul 23, 2005

Is there any way to do the following?select Max(FieldOne) From (spGetSomeData 100,'test' )Or do I need to define a view and have the stored proc use that view?The stored proc does some things with temp tables that would bedifficult to replicate with a view which is why I'm asking.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved