SqlDataSource, DataView, CType Function && Page_Load-Compilation ErrorBC30451: Name 'SqlDataSource3' Is Not Declared.

Sqldatasource How To Not Load The Gridview With The SqldataSoruce At Page_load C#
hello all
1)   I was using SQLdataSource and bind it with the gridviw to show its data.  so that is how is running
      when the page load,  i see the data is posted on the page on the gridview.
      But what i need to do when I want a user to click a button  and then the Gridview show the data.
 
   As i was creating a SQLDatasource, it was click click and click but  I want to put that sqldatasouce to load the data in gridview by a click event.
 
2) my second question is also related to the first one or just similar.
I dont want to get all the data rather filter it.  
so once the page load,  there is a button and a text box.
In the text box user going to type the EmployeeID and click the "Employee Info" button and then the data would show up. 
Rather i use te DetaildView Control . What should I do to load the information on the Text boxes rather then on the GridView since it is going to be a single recrod.  
Thanks all
 

View Replies !

---

Programmatic Use Of SQLDataSource Not Returning Dataview
I am creating an ad-hoc reporting module for my website.  The user may select a list of columns and specify the WHERE for the report.  I build the select statement and the where statement and pass the info to a stored procedure.  The stored procedure accepts 3 select parameters and a 3 wherestmt parameters.  I finally got the SQLDatasource to work by setting the parameter values in the Selecting method of the SQLDatasource.  Binding it to a gridview shows me all my values.  Now, I am trying to programmatically call the select method of the SQLDatasource so I can get a dataview or datareader to manipulate further.  I am getting an null back when I call the select method.  Any ideas why? or a workaround?
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AssetMgmtRWConnectionString %>"SelectCommand="getAdhocRptData" SelectCommandType="StoredProcedure" DataSourceMode="DataReader">
<SelectParameters>
<asp:Parameter Name="strSelect" Type="String" />
<asp:Parameter Name="strSelect2" Type="String" />
<asp:Parameter Name="strSelect3" Type="String" />
<asp:Parameter Name="strWhere" Type="String" />
<asp:Parameter Name="strWhere2" Type="String" />
<asp:Parameter Name="strWhere3" Type="String" />
<asp:Parameter Name="bitDebug" Type="Boolean" />
</SelectParameters>
</asp:SqlDataSource>
 
<VB Code>Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Getting an error that the object is null? why is the above code not returning any data?
'Tried using a datareader and datasource.Dim rptview As IDataReader = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), IDataReader)
If rptview.Read Then
lblMsg.Text = "Got Rows"
End If
End Sub
Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting
'For this sp, need to set each paramater even if it is not being used. Setting it in the wizard
'does not work. Need to be set programmatically.
e.Command.Parameters("@strSelect").Value = "p.field1, p.field2 "
e.Command.Parameters("@strSelect2").Value = "s.field1, s.field2"
e.Command.Parameters("@strSelect3").Value = "a.field1, a.field2, a.field4, a.field5"
e.Command.Parameters("@strWhere").Value = "p.field3 is not null and a.field5 is null"
e.Command.Parameters("@strWhere2").Value = "'"
e.Command.Parameters("@strWhere").Value = ""
e.Command.Parameters("@bitdebug").Value = False
End Sub
 
thanks for any help.
 

View Replies !

---

Function Returning Error During Compilation.....
Hi ,

I am creating a function which is going to return a table. The Code ofr the function is as follows...
===============================
Create function udf_qcard (@cg1 varchar(25)) returns @rec_card table (t_cusip varchar(10),t_data varchar(70))
AS
begin
declare @t1_sys char(10),@t1_all varchar(11)
declare @temp_qcard table (tdata varchar(11) collate SQL_Latin1_General_CP1_CS_AS)
if (substring(@cg1,1,2)='Q$')
set @cg1 = (select substring(@cg1,3,len(@cg1)) where substring(@cg1,1,2)='Q$')
DECLARE c1 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @cg1 and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY
insert into @temp_qcard values(@cg1)
OPEN C1
FETCH NEXT FROM c1 INTO @t1_sys,@t1_all
WHILE @@FETCH_STATUS = 0
BEGIN

insert into @temp_qcard values(@t1_all)

declare @t2_sys char(10),@t2_all varchar(10)
DECLARE c2 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @t1_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

begin
OPEN C2
FETCH NEXT FROM c2 INTO @t2_sys,@t2_all
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @temp_qcard values(@t2_all)

declare @t3_sys char(10),@t3_all varchar(10)
DECLARE c3 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups
where groups_system = @t2_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY

begin

OPEN C3
FETCH NEXT FROM c3 INTO @t3_sys,@t3_all
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @temp_qcard values(@t3_all)
FETCH NEXT FROM c3 INTO @t3_sys,@t3_all
end
end
close c3
deallocate c3
FETCH NEXT FROM c2 INTO @t2_sys,@t2_all
end
end
close c2
DEALLOCATE c2

FETCH NEXT FROM c1 INTO @t1_sys,@t1_all
END

CLOSE c1
DEALLOCATE c1
Insert @rec_card select groups_q+groups_cusip,groups_data from tbl_groups
where groups_system in (select tdata from @temp_qcard) and groups_seq>=1 and groups_alldata not like 'Q$%' order by groups_alldata

RETURN
END
==========================

While compiling this I am getting the Below error ....
==================
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 10
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 23
Mixing old and new syntax to specify cursor options is not allowed.
Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 35
Mixing old and new syntax to specify cursor options is not allowed.
=================

Can Anyone please help me how to resolve this issue...

Thanks with Regards.

-Mohit.

View Replies !

---

Trouble Porting A Trivially Simple Function - With Declared Variables
Here is one such function:CREATE FUNCTION my_max_market_date () RETURNS datetimeBEGINDECLARE @mmmd AS datetime;SELECT max(h_market_date) INTO @mmmd FROM holdings_tmp;RETURN @mmmd;ENDOne change I had to make, relative to what I had working in MySQL, wasto insert 'AS' between my variable and its type. Without 'AS', MS SQLinsisted in telling me that datetime is not valid for a cursor; and Iam not using a cursor here. The purpose of this function is tosimplify a number of SQL statements that depend on obtaining the mostrecent datetime value in column h_market_date in the holdings_tmptable.The present problem is that MS SQL doesn't seem to want to allow me toplace that value in my variable '@mmmd'. I could do this easily inMySQL. Why is MS SQL giving me grief over something that should be sosimple. I have not yet found anything in the documentation for SELECTthat could explain what's wrong here. :-(Any ideas?ThanksTed

View Replies !

---

SqlDataSource.Select Error: Unable To Cast Object Of Type 'System.Data.DataView' To Type 'System.String'.
I am trying to put the data from a field in my database into a row in a table using the SQLDataSource.Select statement. I am using the following code: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'" myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)But when I run the code, I get the following error:Server Error in '/YorZap' Application. Unable to cast object of type 'System.Data.DataView' to type 'System.String'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.Source Error: Line 54: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'"
Line 55: 'myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments).GetEnumerator.Current, String)
Line 56: myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)
Line 57:
Line 58: filesTable.Rows.Add(myDataRow)Source File: D:YorZapdir_list_sort.aspx    Line: 56 Stack Trace: [InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.]
ASP.dir_list_sort_aspx.BindFileDataToGrid(String strSortField) in D:YorZapdir_list_sort.aspx:56
ASP.dir_list_sort_aspx.Page_Load(Object sender, EventArgs e) in D:YorZapdir_list_sort.aspx:7
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45
System.Web.UI.Control.OnLoad(EventArgs e) +80
System.Web.UI.Control.LoadRecursive() +49
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3743
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 Please help me!

View Replies !

---

Is It Possible To Use Twice Declared. Variable Names- KILL And After Declared. Variable
is it possible to use twice declared. Variable names-
declared. Variable  and after KILL
and use the same declared. Variable 
like

DECLARE

@StartDate datetime
 
KILL @StartDate datetime (remove from memory)
use after with the same name

i have 2  big stored PROCEDURE
i need to put one after one
and psss only 1 Variable name to the second stored PROCEDURE
like this i don't get this error
 

The variable name '@Start_Date' has already been declared. Variable names must be unique within a query batch or stored procedure.

Msg 134, Level 15, State 1, Line 146

The variable name '@End_Date' has already been declared. Variable names must be unique within a query batch or stored procedure.
i use like
KILL @endDate  ??
KILL @StartDate   ??

 
TNX

View Replies !

---

Two Stored Procedures On Page_load?
Hi i have two stored procedures that need to run on page load, how will i go about doing this, thank you. Here is my code;protected void Page_Load(object sender, EventArgs e)
{if (!IsPostBack)
{SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("retrieve_Articles", conn);command.CommandType = CommandType.StoredProcedure;
conn.Open();SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
ArticleList1.DataSource = reader;
ArticleList1.DataBind();
reader.Close();
}
}
The above loads up the article datagrid, how can i now load up my news section which has the following code;SqlConnection conn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);
SqlCommand command2 = new SqlCommand("retrieve_News", conn);command2.CommandType = CommandType.StoredProcedure;
conn.Open();SqlDataReader reader2 = command.ExecuteReader(CommandBehavior.CloseConnection);
NewsList1.DataSource = reader2;
NewsList1.DataBind();
reader.Close();
}

View Replies !

---

SSRS And Setting ID On Page_Load
I'm looking for a way to set the id (on an asp page) when a selected row from a SSRS report in clicked.  The id set would then become a session parameter on said asp page. 

View Replies !

---

Procedure Or Function Sp_AddDealer Has Too Many Arguments Specified (error Via SqlDataSource)
Procedure or function sp_AddDealer has too many arguments specified.
I am experiencing the 'too many arguments specified' error.  I am running on SQL 2005.  The Parameters lists on SQL server (when I view a dropdown under the sp name) shows a 'returns integer'  (but without the @ the signifies a parameter).I have looked around the forums and haven't seen quite this flavor of this error.  My forehead is sore from beating it against the wall... any clue would be appreciated!
The error occurs when I click the 'new' link button, enter some data and then click the update link button after ... BOOM - Procedure or function sp_AddDealer has too many arguments specified.
Thanks!!
Chip Kigar
Here is the stored Procedure:

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo
ALTER PROCEDURE [dbo].[sp_AddDealer]  @sCarcareSystem varchar(100), @sDealerName varchar(50), @sDealerPhysAddrStreet varchar(200), @sDealerPhysAddrCity varchar(100), @sDealerPhysAddrState varchar(10), @sDealerPhysAddrZip varchar(20), @nReturnCode bigint output, @nReturnId bigint output AS
 SET NOCOUNT ON  DECLARE @m_nCt bigint  SET @nReturnCode = 0 SET @nReturnId = 0
 -- VALIDATE IF (@nReturnCode = 0) BEGIN  SELECT   @m_nCt = COUNT(tblDealers.[_DealerId])   FROM    tblDealers    WHERE   [Dealer Name] = @sDealerName   IF (@m_nCt >0)  BEGIN   SET @nReturnCode = -2000 --'Error for exsiting Dealer'   SET @nReturnId = 0  END END
 -- PROCESS IF (@nReturnCode = 0) BEGIN  SET @nReturnCode = -2 --' Error getting new record id'  DECLARE @m_nNewRecId bigint  SET @m_nNewRecId = 0  EXEC sp_GetNewRecId @m_nNewRecId output  IF (@m_nNewRecId > 0)  BEGIN   SET @nReturnCode = -1 --'Error adding Dealer'   INSERT INTO     tblDealers    (    [_DealerId],    [Carcare System],    [Dealer Name],    [Dealer Phys Addr Street],    [Dealer Phys Addr City],    [Dealer Phys Addr State],    [Dealer Phys Addr Zip]    )    VALUES    (    @m_nNewRecId,    @sCarcareSystem,    @sDealerName,    @sDealerPhysAddrStreet,    @sDealerPhysAddrCity,    @sDealerPhysAddrState,    @sDealerPhysAddrZip    )    SET @nReturnCode = 0 --'Success'   SET @nReturnId = @m_nNewRecId
   END  END
 
Here is the SQLDataSource.  I plugged the ID parameter, so I got a schema back, but no data.


    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"        InsertCommand="sp_AddDealer" InsertCommandType="StoredProcedure"         SelectCommand="sp_GetDealerByDealerId"   SelectCommandType="StoredProcedure">        <SelectParameters>            <asp:Parameter DefaultValue="2" Name="nDealerId" Type="Int64" />            <asp:Parameter DefaultValue="" Direction="Output" Name="nReturnCode" Type="Int64" />            <asp:Parameter Direction="Output" Name="nReturnId" Type="Int64" />        </SelectParameters>        <InsertParameters>            <asp:Parameter Name="sCarcareSystem" Type="String" />            <asp:Parameter Name="sDealerName" Type="String" />            <asp:Parameter Name="sDealerPhysAddrStreet" Type="String" />            <asp:Parameter Name="sDealerPhysAddrCity" Type="String" />            <asp:Parameter Name="sDealerPhysAddrState" Type="String" />            <asp:Parameter Name="sDealerPhysAddrZip" Type="String" />            <asp:Parameter Direction="InputOutput" Name="nReturnCode" Type="Int64" />            <asp:Parameter Direction="InputOutput" Name="nReturnId" Type="Int64" />        </InsertParameters>    </asp:SqlDataSource>
Here is the Formview:

    <asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1">        <EditItemTemplate>            _DealerId:            <asp:TextBox ID="_DealerIdTextBox" runat="server" Text='<%# Bind("_DealerId") %>'>            </asp:TextBox><br />            Carcare System:            <asp:TextBox ID="Carcare_SystemTextBox" runat="server" Text='<%# Bind("[Carcare System]") %>'>            </asp:TextBox><br />            Dealer Name:            <asp:TextBox ID="Dealer_NameTextBox" runat="server" Text='<%# Bind("[Dealer Name]") %>'>            </asp:TextBox><br />            Address Street:            <asp:TextBox ID="Address_StreetTextBox" runat="server" Text='<%# Bind("[Address Street]") %>'>            </asp:TextBox><br />            Address City:            <asp:TextBox ID="Address_CityTextBox" runat="server" Text='<%# Bind("[Address City]") %>'>            </asp:TextBox><br />            Address State:            <asp:TextBox ID="Address_StateTextBox" runat="server" Text='<%# Bind("[Address State]") %>'>            </asp:TextBox><br />            Address Zip:            <asp:TextBox ID="Address_ZipTextBox" runat="server" Text='<%# Bind("[Address Zip]") %>'>            </asp:TextBox><br />            <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"                Text="Update">            </asp:LinkButton>            <asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"                Text="Cancel">            </asp:LinkButton>        </EditItemTemplate>        <InsertItemTemplate>            _DealerId:            <asp:TextBox ID="_DealerIdTextBox" runat="server" Text='<%# Bind("_DealerId") %>'>            </asp:TextBox><br />            Carcare System:            <asp:TextBox ID="Carcare_SystemTextBox" runat="server" Text='<%# Bind("[Carcare System]") %>'>            </asp:TextBox><br />            Dealer Name:            <asp:TextBox ID="Dealer_NameTextBox" runat="server" Text='<%# Bind("[Dealer Name]") %>'>            </asp:TextBox><br />            Address Street:            <asp:TextBox ID="Address_StreetTextBox" runat="server" Text='<%# Bind("[Address Street]") %>'>            </asp:TextBox><br />            Address City:            <asp:TextBox ID="Address_CityTextBox" runat="server" Text='<%# Bind("[Address City]") %>'>            </asp:TextBox><br />            Address State:            <asp:TextBox ID="Address_StateTextBox" runat="server" Text='<%# Bind("[Address State]") %>'>            </asp:TextBox><br />            Address Zip:            <asp:TextBox ID="Address_ZipTextBox" runat="server" Text='<%# Bind("[Address Zip]") %>'>            </asp:TextBox><br />            <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"                Text="Insert">            </asp:LinkButton>            <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"                Text="Cancel">            </asp:LinkButton>        </InsertItemTemplate>        <ItemTemplate>            _DealerId:            <asp:Label ID="_DealerIdLabel" runat="server" Text='<%# Bind("_DealerId") %>'>            </asp:Label><br />            Carcare System:            <asp:Label ID="Carcare_SystemLabel" runat="server" Text='<%# Bind("[Carcare System]") %>'>            </asp:Label><br />            Dealer Name:            <asp:Label ID="Dealer_NameLabel" runat="server" Text='<%# Bind("[Dealer Name]") %>'>            </asp:Label><br />            Address Street:            <asp:Label ID="Address_StreetLabel" runat="server" Text='<%# Bind("[Address Street]") %>'>            </asp:Label><br />            Address City:            <asp:Label ID="Address_CityLabel" runat="server" Text='<%# Bind("[Address City]") %>'>            </asp:Label><br />            Address State:            <asp:Label ID="Address_StateLabel" runat="server" Text='<%# Bind("[Address State]") %>'>            </asp:Label><br />            Address Zip:            <asp:Label ID="Address_ZipLabel" runat="server" Text='<%# Bind("[Address Zip]") %>'>            </asp:Label><br />            <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"                Text="New">            </asp:LinkButton>        </ItemTemplate>    </asp:FormView>

View Replies !

---

Setting The SQL For A Dataview At Runtime.
I don't know if this topic should go in this forum category or in the SQLDataSource forum.
I need to be able to provide SQL which varies based on what the user enters on a page.  However, this isn't as simple a providing parameters in the SQLDataSource control because the SQL changes depending on what the user selects.  For example, in a customer search page, they choose to search by either first name or last name from a drop down list and then enter the name to search for in a text box.  The dataview should display the matching customers.
How can I set this SQL based on the entered values while also keeping the paging and sorting capabilities of the dataview?  You don't seem to be able to set the SQL for an SQLDataSource at run time.

View Replies !

---

How Do I .. Use A Dataview To Break Up Sqldatareader ? Please Help!
Hello,
I am using a sqldatareader to get back a whole set of data.
The columns are id, data_id and data_desc
for example, the collection is id   data_id   data_desc0    100       this is name for id 00    101       this is address for id 00    102       this is gender for id 01    100       this is name for id 11    101       this is address for id 11    102       this is gender for id 12    100       this is name for id 22    101       this is address for id 22    102       this is gender for id 23    100       this is name for id 33    101       this is address for id 33    102       this is gender for id 3
I want to be able to go thru' this list and break up for each id and create a txt file. So, i will have four text files.
txt0.txt will contain100, this is name for id 0 101, this is address for id 0102, this is gender for id 0
I would like to know, how do i use a dataview to break up the sqldatareader so i can then repeat the loop for each id and generate the text file.
I don't have a problem generating the text file.
Please let me know, how should i go about it.
Thanks a bunch,-Sean
 

View Replies !

---

Query In Dataview.rowfilter
In the dataview rowfilter property, how can I say that I just wantTitles that start with 'A' to 'J' ??ex:dv.rowfilter = " Country = ' France ' "I wantdv.rowfilter = " Title = (from 'A' to 'J') "This doesn't work :dv.rowfilter = " Title like '[a-j]%' "Neither does :dv.rowfilter = " Title like 'a%'" AND "Title like 'b%'" AND..so onBut this does work just for the Titles starting with 'a'dv.rowfilter = " Title like 'a%' "any idea ?JMT

View Replies !

---

Compilation Error
I'm trying to connect to an SQL database through my asp.net page and I'm getting an Compiler Error Message: BC30188: Declaration expected for the following codes:

DBConn= New OledbConnection("Provider=sqloledb;" _

DBInsert.Commandtext = "Insert Into GuestInfo" _

DBInsert.Connection =DBConn

DBInsert.Connection.Open

DBInsert ExecuteNonQuery()

What I'm trying to do is connect to the SQL database and input new information to the database.

This is the entire code for connecting and entering info into the database. The SQL Database's name is HMS. I'm stuck and I can't figure it out.

Dim DBConn as oledbConnection
Dim DBInsert As New oledbCommand
DBConn= New OledbConnection("Provider=sqloledb;" _
& "server=localhost;" _
& "Initial Catalog=HMS;" _
& "User id=sa;" _
& "Password=yourpassword;")
DBInsert.Commandtext = "Insert Into GuestInfo" _
& "(FirstName,Lastname,Address,City,State,Zipcode) values ('" _
&"'" & txtFirstName.Text & "', " _
&"'" & txtLastName.Text & "', " _
&"'" & txtAddress.Text & "', " _
&"'" & txtCity.Text &"', " _
&"'" & txtState.Text &"', " _
&"'" & txtZipCode.Text &"', ")"
DBInsert.Connection =DBConn
DBInsert.Connection.Open
DBInsert ExecuteNonQuery()

View Replies !

---

Prevent SP Compilation
Hi,I'm using SQL Server 2000 MSDE on a laptop running Windows XP.I have a couple of SP's that that quite some time to compile. So I waswondering: is there any way to have the database *not* recompile them everytime after a reboot?BOL says: "As a database is changed by such actions as adding indexes orchanging data in indexed columns, the original query plans used to accessits tables should be optimized again by recompiling them. This optimizationhappens automatically the first time a stored procedure is run afterMicrosoft® SQL ServerT 2000 is restarted."Now the SQL Server is restarted a lot, because laptops don't have endlessbatteries <g>Cheers,Bas

View Replies !

---

Avoiding Compilation
Using small stored procs or sp_executesql dramatically reduces the number ofrecompiles and increases the reuse of execution plans. This is evident fromboth the usecount in syscacheobjects, perfmon, and profiler. However I'm ata loss to determine what causes a compilation. Under rare circumstances theusecount for Compiled Plan does not increase as statements are run. Seemsto correspond to when there is no execution plan. It would seem to me thatcompilation is a resource intensive task that if possible (data and schemaare not changing) should be held to a minimum.How does one encourage the reuse of compile plans?Is this the same as minimizing compilation?Looks like some of this behavior is changing in SQL 2005....Thanks,Danny

View Replies !

---

Resize Dataview Edit Fields
Hi Forum I have a problem with DetailView style. The dynamic part is ok fills with data! choose edit the DetailView re creates the fields displaying the editable content. The problem I want to address is the fields display only part of the data, ie say 20char of data but only view 12, the user then has to scroll accross. How to resize these fields is the ? much thanks Paul

View Replies !

---

How To Bind TextBox Controls To DataView ?
Hi,
I use a filter on a dataview which has uniq rows. I need to bind several fields of the result filtered row to textboxes. Here the code :
protected void Page_Load(object sender, EventArgs e)    {        string id = Request.QueryString["id"];
        if (PreviousPage != null)        {            SqlDataSource dsPrev = (SqlDataSource)PreviousPage.FindControl("SqlDataSource1");
            DataView dv = (DataView)(dsPrev.Select(DataSourceSelectArguments.Empty));            dv.RowFilter = "dr_id='" + id + "' ";            dv.RowStateFilter = DataViewRowState.CurrentRows;
            txtFirstName.Text = // ???  FirstName Field
            txtLastName.Text= // ???  LastName Field
            txtAddress.Text=  // ???  Address Field                               }    }
   I don't know which cod to put in those lines.
   On the help of the dataView there is an example that use DataBindings. This example is in the DataView.RowFilter article:
Text1.DataBindings.Add("Text",view,"CompanyName");
The big problem is that I don't have a method or event call 'DataBindings'. I just have 'DataBind' method or dataBinding event and they both don't support the 'Add' method.
So how can I bound those controls to my filtered Dataview field value.
Thanks,
David

View Replies !

---

How To Use MDX Script In RS Dimension Filter (dataview)
Hi, I need to create filter which retrieves data for current moment. [time].[date].lastchild is not an option because the calender in database is generated till following year (2008).

I guess script goes like..

[time].[date].currentmember

but it does not work.


I'm green with these MDX scripts.. I'd be very pleased if some one of you gurus could help me.

Best Regards,
Eeva

View Replies !

---

Query In Dataview/update Form
Question related to Visual Basic Video Lesson 09 (Databinding.wmv) by Bob Tabor. 

Made a table and a UI form according to this lesson (table colums: CustomerID, FirstName, LastName).

Is it possible to use the same  or an identical looking query form to find "Bob" by typing Bob in the Fist Name textbox or "Tabor" by typing Tabor in the Last Name textbox as in FileMaker? 

Thanks,

View Replies !

---

Cannot Convert Type 'int' To 'System.Data.DataView'
I have the following SqlDataSource in my page:<asp:SqlDataSource ID="sqlds" runat="server" ConnectionString="<%$ ConnectionStrings:cs %>"    SelectCommand="SELECT [Quest1], [Quest2], [Quest3], [Quest4], [Quest5] WHERE ([QuestID] = @QuestID)">    <SelectParameters>        <asp:Parameter Name="QuestID" />    </SelectParameters> In my code I get the following error: "Cannot convert type 'int' to 'System.Data.DataView'", I don't understand why I am getting this error because I am casting my SqlDataSource as type DataView when passing it to my DataView type.Code: protected void Chart(string record)    {        System.Data.DataView dv = (System.Data.DataView)sqlds.SelectParameters.Add("QuestID", System.TypeCode.Int32, record); //Error line       ...       ...       ...}If anyone could point me in the right direction I would be very grateful. 

View Replies !

---

Compilation / Re-build Issue
 
Hi,
 We are using .Net 2.0 for developing our application, All the file in this application are source safed, Whenever we  do modification in the code it take longer time to build  approax it takes around 2 min to display the default page (login page).
 
Please do send out your suggestions to reduce the time take for the build, is there any setting need to be done in IDE to make the build process much faster.
 
Regards
K.Karthik Doss

View Replies !

---

SP Compilation Confirmation Message?
How can we say whether the SP is successfully compiled or not if we are compiling it on the server as a part of the TSQL script since it does not throw any message like ORACLE does.

In oracle, system will let you know whether the the procedure is successfully complied or not?

Thanks/

View Replies !

---

SQL Compilation And Execution Plan
Hi all,

I€™m having a test regarding to the image data type. The test program is written with sql native api and just update the image data type column, but I looked the SQL Compilations/sec and Batch Requests/sec counters in SQLServer:QL Statistics using Perfmon, both values are almost the same. It seemed whenever the stored procedure is called, SQLServer compiles it and makes execution plan again. But when I had a test without image data type, SQL Compilation/sec was 0. SQL version is Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) (Build 2600: Service Pack 2).

Is SQL server working the way expected or am I missing something?

View Replies !

---

Compilation Of Stored Procs
 Hi,

I would like to know if the execution plans of stored procs also get migrated when we do migration to 2005 from 2000 using attachdetach method or we will need to re-run the stored procs?

The thing is when I am running the Stored procs in 2005, its performing  really slow in first run.

Any help in his regard is highly appreciated.

Thanks,
Ritesh

 

 

 

 

 

View Replies !

---

Bulk Update Of All Changes From DataView To DataSet (only Opening Connection Once)?
(Hope this isn't a "stupid" question, but I haven't been able to find a straight-forward answer anywhere)"
I currently have code that iterates through a dataview's records, making a change to a field in some of the records. The way I have this coded, a conection has to opened & closed for each individual record that's updated:
    dsrcUserIae.UpdateCommand = "UPDATE UserIAE SET blnCorrect = @blnCorrect WHERE (ID = @ID)"    dsrcUserIae.UpdateParameters.Add("blnCorrect", SqlDbType.Bit)    dsrcUserIae.UpdateParameters.Add("ID", SqlDbType.Int)
    Dim myDataView As DataView = CType(dsrcUserIae.Select(DataSourceSelectArguments.Empty), DataView)         For Each myRow As DataRowView In myDataView       If myRow("FkUsersAnswerID") = myRow("AnswerID") Then         intCorrect = 1       Else         intCorrect = 0       End If       dsrcUserIae.UpdateParameters.Item("blnCorrect").DefaultValue = intCorrect       dsrcUserIae.UpdateParameters.Item("ID").DefaultValue = myRow("ID")       intUpdateResult = dsrcUserIae.Update()    Next
It seems like I should be able to do something like this (call update once), but I'm not sure how... 
    dsrcUserIae.UpdateCommand = "UPDATE UserIAE SET blnCorrect = @blnCorrect WHERE (ID = @ID)"    dsrcUserIae.UpdateParameters.Add("blnCorrect", SqlDbType.Bit)    dsrcUserIae.UpdateParameters.Add("ID", SqlDbType.Int)
    Dim myDataView As DataView = CType(dsrcUserIae.Select(DataSourceSelectArguments.Empty), DataView)            For Each myRow As DataRowView In myDataView      If myRow("FkUsersAnswerID") = myRow("AnswerID") Then        myRow("blnCorrect") = 1      Else        myRow("blnCorrect") = False      End If    Next
      intUpdateResult = dsrcUserIae.Update() 'Want all changed myRow("blnCorrect") to be updated to datasource
Can anybody explain how to do the bulk update? I've seen some info about AcceptChanges and Merge, but I'm not sure if they apply here, or if they more for Transactions.

View Replies !

---

How To Access Underlaying DataView Of SQLDataSouce In It's Selected Event
I need access to the underlying DataView associated with a declaratively defined SQLDataSource. Ideally, I need this access in the SQLDataSource's Selected event. I know that I can use the SQLDataSouce.Select() method to return the underlying DataView object. But, there is a GridView control bound to the datasource so I don't want to explicity call Select() since it will end up being called again implicity by the gridview itself. I have seen that this can be done with an ObjectDataSouce as follows:
void odsDataSouce_Selected(object sender, ObjectDataSourceStatusEventArgs e) {  DataView oDV = (DataView)e.ReturnValue;}
Is there any way to accomplish the same thing with a SQLDataSouce?  It seems hard to believe that the query results are not exposed to the Selected event.
Thanks!
 
 

View Replies !

---

Compilation Error On Store Procedure
Hi all,Here is my error: Server: Msg 245, Level 16, State 1, Procedure NewAcctTypeSP, Line 10Syntax error converting the varchar value 'The account type is already exist' to a column of data type int.Here is my procedure:ALTER PROC NewAcctTypeSP(@acctType VARCHAR(20), @message VARCHAR (40) OUT)ASBEGIN  --checks if the new account type is already exist IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @acctType) BEGIN  SET @message = 'The account type is already exist'  RETURN @message END
 BEGIN TRANSACTION  INSERT INTO AcctTypeCatalog (acctType) VALUES (@acctType)  --if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction  IF @@error <> 0 OR @@rowcount <> 1   BEGIN    ROLLBACK TRANSACTION    SET @message = 'Insertion failure on AcctTypeCatalog table.'    RETURN @message       END  ELSE    BEGIN        COMMIT TRANSACTION   END
 RETURN @@ROWCOUNTENDGO
--execute the procedureDECLARE @message VARCHAR (40);EXEC NewAcctTypeSP 'CDs', @message;I am not quite sure where I got a type converting error in my code and anyone can help me solve it???(p.s. I want to return the @message value to my .aspx page)Thanks.

View Replies !

---

C++ Ole DB Stack Overflow During Sql Server Compilation
hi,when i execute :CCommand<CManualAccessor, CBulkRowset, CNoMultipleResults> rs;rs.SetRows(100);HRESULT code_resultat = rs.Open(session, requete, &propset, NULL,DBGUID_DBSQL, FALSE);with a requete with length = 13000, it works perfectlybut when my requete length is 200000 (example : SELECT * FROM myTABLEWHERE id_table IN("lot of number : more then 30000 number"))i have code_resultat = DB_E_ERRORSINCOMMAND (= 0x80040e14)and when i explore the IErrorInfo message, i have :minor = 565 and the message issource :Microsoft OLE DB Provider for SQL Serverserveur has made a stack overflow during compilation...Is there a solution to extract to data ?in a fast way ...thanks in advance ...Mike

View Replies !

---

SSIS Package Compilation And Execution
I am wondering something, once we've created a job that executes a package at a given time interval, does that package get recompiled each time the job spins up and executes the package? Or is the package compiled once and then that compiled code is executed each run after the first run?

What I'm seein is this; I have a package that reads data from flat text files and then dumps that data into the database. The package will take 3 minutes to execute when executing on a single file, but when it's looping through ~50 files, it will take ~30 minutes to execute, that is less than a minute per file. Why is this?

Hopefully I'm just forgetting something and not setting a checkbox or radio button somewhere. The job is set up as an SSIS job, not as a command line job.

Thanks in advance for any help you can give me.

Wayne E. Pfeffer
Sr. Systems Analyst
Hutchinson Technolgy Inc.

View Replies !

---

Delaying Compilation Real Time
Hi,
I would like to find out about SSIS compilation. Can you mention anything regarding this issue or can you point me out to a website for this topic please?

Thanks

View Replies !

---

Newbie With An Easy Compilation Error Question.
I've been looking over this and can't see anything wrong. Can anyone shed some light on this for me?
------------------

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: CS0117: 'System.Data.SqlClient.SqlConnection' does not contain a definition for 'ExecuteReader'

Source Error:



Line 16: SqlCommand myComm = new SqlCommand("SELECT users, password FROM users WHERE username='" + username + "' AND password='" + password + "'", myConn);
Line 17: myConn.Open();
Line 18: SqlDataReader myReader = myConn.ExecuteReader();
Line 19: do
Line 20: {


Source File: D:Inetpubhoteladvisor estLogin.aspx Line: 18


void Login(string username, string password)
{
SqlConnection myConn = new SqlConnection ("server = client1; uid = dbadmin; pwd = dbadmin; database = hotels");
SqlCommand myComm = new SqlCommand("SELECT users, password FROM users WHERE username='" + username + "' AND password='" + password + "'", myConn);
myConn.Open();
SqlDataReader myReader = myConn.ExecuteReader();
do
{
while (reader.Read())
{
if (username == myReader.GetString(1) && password == myReader.GetString(2))
{
messages.Text = "Your login was successful!";
}
else
{
messages.Text = " Your login was unsuccessful!";
}
}
}
while (reader.NextResult());
myReader.Close();
myConn.Close();
}
void Submit_Click(Object sender, EventArgs e)
{
Login(username.Text, password.Text);
}


Edit by moderator - NetProfit: Added < code>< /code> tags.

View Replies !

---

Ignore Compilation Errors For Creation Of Stored Procedures
I have an application that is moving from an home made full text search engine to using the full text indexing engine of SQL 2005.  I have a stored procedure that I want to behave as:
check documents table to determine whether a full text index for SQL's full text engine has been created.
If it has not, query the documentText table (which is the table for my in-house full text search)
If it has, use the full text indexing engine 
 
My problem is that compilation of the TSQL to create the stored procedure fails when the full text index has not already been created with the followign error:
 

Msg 7601, Level 16, State 2, Procedure My_FullTextSearch, Line 0

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Documents' because it is not full-text indexed.
 
In my test lab, I tried:
1. creating the full text index
2. creating the stored procedure 
3. deleting the ful text index
which gets me to the desired end result of having a stored procedure that can determine whether or not the full text index has been created yet (the procedure works in this state).  But I creating this index as part of this stored procedure creation in production is not an option.
 
My question - Can I somehow tell SQL to ignore the compilation errors it encounters while creating this stored procedure?  If not, is there some other way to create this "smart" stored procedure?
 
Here's a code snippet stripped down to the bare minimum to generate the error:
 
 

CREATE PROCEDURE [My_FullTextSearch]

@Term VarChar(1000)

AS

BEGIN

SET NOCOUNT ON;

IF NOT OBJECTPROPERTY(OBJECT_ID('Documents'), 'TableHasActiveFulltextIndex')=1

BEGIN

Select [DocumentID]

from [DocumentText]

where [Term] like '%' + LTRIM(@Term) + '%'

END

ELSE

BEGIN

Select [key] from FREETEXTTABLE(Documents, Contents, @Term)

END

END

 

View Replies !

---

Stored Procedure Compilation Question: Doing Disparate Things In Aproc
To minimize the very large number of stored procedures typicallyassociated with an application, I have gotten in the habit ofcombining a select, insert, update, and delete all in one procedure,and passing an argument to indicate which to use. (I use defaultvalues for all input params to avoid having to declare them forselects and deletes.) So I'll have just one PersonAdmin proc insteadof PersonGet, PersonInsert, PersonUpdate, and PersonDelete procsWhile this is nice for housekeeping, I wonder what the compiler doeswith such an architecture,and I fear the worst. The select returns arecordset; the others don't.Is this a bad idea?If it is, I really wish SQL would permit some sort of user folderstructure in the proc list.

View Replies !

---

Gridview / SqlDataSource Error - Procedure Or Function &<stored Procedure Name&> Has Too Many Arguments Specified.
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure  in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.

View Replies !

---

SqlDataSource.SelectParameters Causing Procedure Or Function Stored Procedure Has Too Many Arguments Specified.
 Hi everybody,   I am having trouble how to fixed this code. I am trying to supply the parameterinside a stored procedure with a value, and displays error message shown below. If I did not supply the parameter with a value, it works. How to fix this?Error Message:Procedure or function <stored proc name> has too many arguments specified.Thanks,den2005 
Stored procedure:

Alter PROCEDURE [dbo].[sp_GetIdeaByCategory]
@CatId <span class="kwd">int</span> = 0
AS
BEGIN
SET NOCOUNT ON;

Select I.*, C.*, U.* From Idea I inner join IdeaCategory C on I.CategoryID = C.IdeaCategoryID inner join Users U on I.UserID = U.UserID Where I.CategoryID = @CatId Order By LastModifiedDate Desc
End


oDataSource.ConnectionString = constr;
oDataSource.SelectCommand = storedProc;<span class="cmt">//storedproc - sp_GetIdeaByCategory</span>
oDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
oDataSource.SelectParameters.Add(<span class="st">&quot;@CatId&quot;</span>, catId);
gdvCategories.DataSourceID = oDataSource.ID;

gdvCategories.DataBind(); &lt;&lt;--- Error occured here


 

View Replies !

---

Retrieving The Value From A Function In The &"Where&" Clause On SqlDataSource
Hi there,
I am using VWD Express edition.
I have the following SqlDataSource control:
 
<asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:Oracle FCCI_BH ConnectionString %>"
ProviderName="<%$ ConnectionStrings:Oracle FCCI_BH ConnectionString.ProviderName %>"
SelectCommand='SELECT "DATEI", "BTS", "TRF_55" FROM "R163BTS" WHERE (("DATEI" >= :DATEI) AND ("DATEI" <= :DATEI2) AND ("BTS"=:BTS))'>
<SelectParameters>
<asp:ControlParameter ControlID="ddlFromDate" Name="DATEI" PropertyName="SelectedValue"
Type="DateTime" />
<asp:ControlParameter ControlID="ddlToDate" Name="DATEI2" PropertyName="SelectedValue"
Type="DateTime" />
<asp:  *** Here is my request please *** 
Name="BTS" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
 
I want that the "BTS" value to be retrieved from a function which I already have:
Public Function get_BTS_value() as String
 
Thanks in advanced
Salan

View Replies !

---

Getting SqlDbType Not Declared
How do I dim SqlDbType in my code?Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TrainUserConnectionString").ConnectionString)
Dim cmd As New Data.SqlClient.SqlCommandWith cmd
.Connection = conn
.CommandType = Data.CommandType.StoredProcedure
.CommandText = "UpdateTopicscmd.Parameters.Add("@classificationID", SqlDBType.Int)cmd.Parameters.Add("@TitleID", SqlDBType.Int)
conn.Open()
For Each item As ListItem In CheckBoxList1.Items
If item.Selected Thencmd.Parameters("@classificationID").Value = item.Valuecmd.Parameters("@TitleID").Value = DropDownList1.SelectedValue
cmd.ExecuteNonQuery()
End If
Next
conn.Close()
End WithEnd Sub
End Class
 
 

View Replies !

---

SqlStatementSourceType Is Not Declared
I have simple SSIS package with only ScriptTask. Script was copied from MSDN:
 



Code Snippet
 
 
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask
 
...
 

Public Sub Main()

Dim pkg As Package = New Package()

Dim exec1 As Executable = pkg.Executables.Add("STOCK:SQLTask")

Dim th As TaskHost

th = CType(exec1, TaskHost)

Dim myVar As Variable = pkg.Variables.Add("myVar", False, "User", 100)

th.Properties("SqlStatementSourceType").SetValue(th, SqlStatementSourceType.Variable)
...
 
Last line shows error - 'SqlStatementSourceType' is not declared.
Well, Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask is imported so SqlStatementSourceType should be visible. I don't see microsoft.sqlserver.sqltask.dll in task's references and can't add it because Add Reference doesn't show this DLL.
 
Could you please forward me in right direction?
 
Thank you,
Alexander

View Replies !

---

Type 'adCmdStoredProc' Is Not Declared
I am trying to set up a "cmd.CommandType = adCmdStoredProc" but Ireceive the error "Type 'adCmdStoredProc' is not declared". What do Ineed to do to declare it? I am using MSDE with SQLDataAdapter. I amtrying to exceute a stored procedure from within my VB .NET 2003 code.Thanks.JH

View Replies !

---

Set Value For Variable In A Declared Cursor
Hi,

I have a problem on setting the value for the variable in a declared cursor. Below is my example, I have declared the cursor c1 once at the top in a stored procedure and open it many times in a loop by setting the variable @str_var to different values. It seems the variable cannot be set after the cursor declared. Please advise how can I solve this issue.

------------------------------------------------------------------------
DECLARE @str_var VARCHAR(10)
DECLARE @field_val VARCHAR(10)

DECLARE c1 CURSOR LOCAL FOR
SELECT field1 FROM tableA WHERE field1 = @str_var


WHILE (Sometime TRUE)
BEGIN

....

SET @str_var = 'set to some values, eg. ABC123, XYZ123'

OPEN c1

FETCH c1 INTO @field_val

WHILE (@@fetch_status != -1)
BEGIN

PRINT @field_val
...

FETCH c1 INTO @field_val
END

CLOSE c1

END

DEALLOCATE c1

----------------------------------------------------------------------

Thanks a lots,
Vincent

View Replies !

---

Using Declared Variables In SQL INSERT Statement.
 
I am new to scripting in general and I've run into an issue when attempting to write a VB variable to a database table in SQL Express.  I am trying to record the value of the variable to the db, but it does not appear that the value is being passed to SQL.  If I hard code the values in the SQL statement it works fine.  Can someone explain what I'm doing wrong accomplish this?  My code is below.  Thanks in advance. 
file.aspx
<asp:SqlDataSource ID="SqlDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:SqlConnectionString %>"
SelectCommand="SELECT * FROM [Table]"
InsertCommand="INSERT INTO [Table] (field1, field2) VALUES (& variable1 &, & variable2 &);" >
</asp:SqlDataSource>
file.aspx.vb
Protected Sub Button_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button.Click
Dim variable1 As String = FileUpload.FileName
Dim variable2 As String = Date.Now
Dim path As String = Server.MapPath("~/directory/)
If FileUpload.HasFile = True Then
Try
SqlDataSource.Insert()
FileUpload.PostedFile.SaveAs(path & _
FileUpload.FileName)
End Try
 
End If
 
End Sub

View Replies !

---

Substringing Declared Varables In SQL Server
I need to be able to update a row of data in a table based upon the first character of a char(50) field.

if char(1) of employee_Job_class = "X" then update field Class_description = "Temporary"

Anyone have any suggestions ??

View Replies !

---

Cursor Declared With Variable In Where Clause
When I execute next query on sqlserver 6.5 nested in stored procedure I can see that 'open testCursor' selected rows using new value of @var. When I execute query on sqlserver 7.0 I can see that 'open testCursor' selected rows using value of @var before 'declare ... cursor'. Is there any way to force sqlserver 7.0 to proccess cursor like it did it before.

select @var = oldValue

declare testCursor cursor
for select someColumns
from someTable
where someColumn = @var

select @var = newValue

open testCursor

fetch next from testCursor into @someColumns

Thank's in advance.

Mirko.

View Replies !

---

Problem Returning A Declared Variable
when I run this sproc all I get out of it is "the commands completed successfully" and doesn't return the value. If anyone can point out where the error is I would really appreciate it. Thanks


Code:


Create Procedure LookupLeagueIdByUserName(@userName as varchar(40) = '') as
begin
if (@userName = '')
raiserror('LookupLeagueIdByUserName: Missing parameters', 16,1)
else
begin
Declare @leagueId int
Set @leagueId = -1

--Check if the username belong to a player
Select @leagueId = leagueId From Users u
inner join players p on p.userId = u.userId
inner join teams t on p.teamId = t.teamId
where u.userName = @userName

if (@leagueId > 0)
begin
return @leagueId
end
else
begin
--Check if the username belong to a teamUser
Select @leagueId = leagueId From Users u
inner join teamUsers tu on tu.userId = u.userId
inner join teams t on tu.teamId = t.teamId
where u.userName = @userName

if (@leagueId > 0)
begin
return @leagueId
end
else
begin
--Check if the username belong to a leagueUser
Select @leagueId = leagueId From Users u
inner join leagueUsers lu on lu.userId = u.userId
where u.userName = @userName

if (@leagueId > 0)
begin
return @leagueId
end
else
begin
--username is not in db or is an admin user
return -1
end
end
end
end
end
return
-- when I run this I get no results returned
LookupLeagueIdByUserName 'chris'

View Replies !

---

Script Component Name Ouput0Buffer Is Not Declared???
Doing a simple test with a script component in a DataFlow to transform some data from a flat file. I have new columns under the default Ouput 0 .. however in my code when I try this, I get the above error.

 

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

   Output0Buffer.AddRow()

End Sub

 

There will of course be a lot more code in the Script Component, but not clear on why I can't reference it.

View Replies !

---

Stored Procedure Using A Declared Cursor
I need to write a stored procedure using T-SQL to declare a cursor for containing id(staff_no), names and specialism of all doctors that have specialism, The contents of the cursor then are to be displayed using a loop and print statement to give a formatted display of the output of each record within the cursor.

The doctors table has the following columns with specialism allowing NULL values

doctor
(
staff_no CHAR(3),
doctor_name CHAR(12),
position CHAR(15),
specialism CHAR(15),
PRIMARY KEY(staff_no)
)

Any help would be greatly appreciated.

View Replies !

---

Obtaining Collation Length Of Declared Variable Within SP
Morning All,Can I have some help with this one please, I am having to make a fixed length text file based on information from the DBDeclare @EDIString varchar(MAX)Declare @RecordType varchar(2)Declare @RegistrationMark varchar(7)Declare @Model_Chassis varchar(11)Declare @LocationCode Varchar(4)Declare @MovementDate varchar(8)Declare @IMSAccountCode varchar(5)Declare @MovementType varchar(8)Declare @NotUsed1 Varchar(28)Declare @NotUsed2 varchar(7)Select @RecordType = RecordType, @RegistrationMark = RegistrationMark, @Model_Chassis = Model_And_Chassis, @LocationCode = LocationCode, @MovementDate = MovementDate, @IMSAccountCode = IMSAccountCode, @Movementtype = MovementTypeCode from Fiat_OutBoundOnce I have selected the information from the DB I need to ensure that each field is the correct length.  I therefore want to pass the variable and the length of the variable into a function to return the correct length.So if location Code = 'AB'  this needs to be four characters long so want to pass it into a function and return 'AB  'As I need to do this for 70+ variables is there an easy way to obtain the length of the collation for the variable?regardsTom

View Replies !

---

Go And Goto In One Sql Script Gives Error Label Not Declared
Hi,I have a problem:I am writing an update script for a database and want to check for theversion and Goto the wright update script.So I read the version from a table and if it match I want to "GotoVersionxxx"Where Versionxxx: is set in the script with the right update script.Whenever I have some script which need Go commands I get error in theoutput thatA GOTO statement references the label 'Versionxxx' but the label hasnot been declared.But the label is set in the script by 'Versionxxx:'Is there a way I can solve this easily?Thanks in advance

View Replies !

---

Select Declared Variable With Case Statements
I am trying to gather counts for table imports made for files from friday - sunday and create a variable that will be the body of an email.
I'd like to use either a case statement or a while statement since the query is the same but the values must be collected for each day (friday, saturday and sunday) and will all be included in the same email.

I have declared all variables appropriately but I will leave that section of the code out.


Select @ifiledate = iFileDate from tblTelemark where
iFileDate = CASE
WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-3, 101), '/','') THEN

Select @countfri1 = Count(*) from tbl1
Select @countfri2 = Count(*) from tbl2
Select @countfri3 = Count(*) from tbl3
Select @countfri4 = Count(*) from tbl4


WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-2, 101), '/','') THEN
Select @countsat1 = Count(*) from tbl1
Select @countsat2 = Count(*) from tbl2
Select @countsat3 = Count(*) from tbl3
Select @countsat4 = Count(*) from tbl4

WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-1, 101), '/','') THEN
Select @countsun1 = Count(*) from tbl1
Select @countsun2 = Count(*) from tbl2
Select @countsun3 = Count(*) from tbl3
Select @countsun4 = Count(*) from tbl4


END

Is there a way to do what this that works???

View Replies !

---

Can Varbinary And Varchar Lengths Be Declared Dynamically?
I just learned that bit masking a varbinary column can increase it's length in bytes unnecessarily.  For example, I ran the following...

declare @v1 varbinary(max)

set @v1 = 0x0100AB

select len(@v1)

set @v1 = @v1 | (len(@v1) - 2) * 256

select master.dbo.fn_varbintohexstr(@v1)

select len(@v1)
...and get
3
0x00000000000101ab
8
 
This messes up a plan I had for varbinary column use.
 
So I quickly tried the following to look for strategies to deal with this unwanted growth...thought being that while I'm passed a varbinary(max), operating on a varbinary that matches its passed length would avoid the unwanted growth after bit masking....
 

declare @i int

set @i = 5

declare @v2 varbinary(@i)
 
...but got errors.  
 
So I suppose I can use a combo of the len, substring etc functions to correct the situation after bit masking but would like to know if the more elegant approach of dynamically sizing a varbinary is possible in t-sql, or if perhaps there is a way to prevent the unwanted growth during bit masking.       

View Replies !

---