Hi all,
i have an insert function using stored procedure with parameters. At some point while assigning parameters i make a casting problem but all i get is:
String was not recognized as a valid Boolean.
how can i find the exact line causing the error?
thank you
-shane
Hi.
I have some contracts and i want to find for every specific contract (every contract can be repeated 2-3 or more times (same policynumber) the contracts that have the same date. That's the contracts INSIDE the contract that have the same data.
I was trying something like
select distinct m.masterpolicyno from multicontract m
where m.category=3
and m.issuedate = m.issuedate
but it think i must use "having" and "in" but i can't figure it out.
Any help?
Thanks.
Hi,
I have the fields like this.
Session1 Session2 Session3 Session4----------------------------------------------------------------------------------------- SQL Server-Part1 SQL Server-Part2 ASP.Net CSS
C# SQL Server-Part3 ASP.Net Javascript
I have set the Full-Text to all the columns. For searching i wrote the below query
SELECT * FROM <TABLE NAME> WHERE CONTAINS(*,'"SQL Server-Part1"')
My Result expectation is: The First Record should come. But the result of the query bring the two records. It see the "SQL Server" string also. I need to find the exact word. How to do it? Please answer me as soon as possible. Ganesh.
I am required to find or check that a specific word exists in string or not.
Suppose I have to find the word 'st' than I need the result true if and only if the following occurrences are there.
1. 'St is valid;' -> true
2. 'DOB is valid;ST is invalid;' -> true
3. 'DOB is valid; ST is invalid;' -> true
4. 'DOB is valid;invalid ST;' -> true
5. 'DOB is valid; invalid ST;' -> true
6. 'DOB is valid; invalid STate;' -> false
Means the exact ST should be search. It is not free text search.
T-SQL is needed to be used in select statement with case using PATINDEX, RegEx or any suitable t-sql command.
Hello all
Trying to delete some data from a SSCE (2005) DB produces the exception:
SqlCeException
There was an error parsing the query. [ Token line number = 1,Token line offset = 43,Token in error = C]
Here is the code I am using
string dsc = Application.StartupPath + "\FCDB07.sdf";
conn = new SqlCeConnection("DataSource = " + dsc);
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM DataContainer WHERE FileName =" + dgContainers[0, SelRowIndex].Value.ToString();
cmd.ExecuteNonQuery(); //There was an error parsing the query. [ Token line number = 1,Token line offset = 43,Token in error = C ]
conn.Close();
Any Idea on What causes this?
TIA
Trophus
Hey all-
I'm trying to insert some values into an SQL Compact database on a WM6 device but there is something apparently wrong with my SQL statement...
The program is going to allow users to schedule an SMS message to be sent at a certain date and time. I'm using a database to keep track of the scheduled SMS messages. The database has 3 rows: phone number, message, and the date/time to be sent.
Here is the relevent code:
private void scheduleMenu_Click(object sender, EventArgs e)
{
//connect to DB and do our scheduling magic
string message = messageBox.Text; //should rename messageBox...
string phoneNum = phoneNumBox.Text;
string dataBase = @"Program FilesSMS_Scheduler2SMSDatabase.sdf";
//SqlCeEngine eng = new SqlCeEngine(dataBase);
SqlCeConnection conn = new SqlCeConnection("Data Source=" + dataBase);
conn.Open();
//insert phone number, message text, and date/time into DB
string cmd = "INSERT INTO Scheduler(phoneNum, message, date) VALUES("+ phoneNum + ", "+ message + ", "+ dateTimePicker1.Value +")";
SqlCeCommand cmdPhone = new SqlCeCommand(cmd,conn);
cmdPhone.ExecuteNonQuery(); //error occures here...
messageBox.Text = "";
MessageBox.Show("Message Scheduled!");
}
I'm guessing it doesn't like how I am trying to get the data from the different text boxes and the DateTimePicker to go inside the SQL command. Does anyone have any ideas on how to fix my SQL command or how to get data from a textbox and DateTimePicker to be inserted into a database a different way?
I'm getting a specific error when i try to run a .sql script for a Custom TableProfile from the Asp.net site,when i Parse the .sql file Commands execute perfectly. But when i actually "Execute" the .sql script i get the following errors::
Msg 15151, Level 16, State 1, Line 5
Cannot find the user 'WYATT-PCWyattASPNET', because it does not exist or you do not have permission.
How can i fix this??
Thxs in Advance Rattlerr
I'm getting a specific error when i try to run a .sql script for a Custom TableProfileProvider ,when i Parse the .sql file Commands execute perfectly. But when i actually "Execute" the .sql script i get the following error. It will produce the table but with "Errors" which dosn't do anyone any good to have errors in the table. I even used the Sql Server Surface Area Configuration to add a new Administrator with the name listed below (WYATT-PCWyatt), that didnt help either..Here is part of the Sample Scheme the problem lies in the [YOURMACHINENAMEASPNET]
Server = WYATT-PCSQLEXPRESS
Computer Name = Wyatt-PC
OS: Windows Vista Home Premium
===========================================================
use TableProfileProviders
go
--
--grants on ASP.NET stored procedures and tables used by the custom providers
--
grant EXECUTE on dbo.aspnet_Applications_CreateApplication to [YOURMACHINENAMEASPNET]
grant EXECUTE on dbo.aspnet_Users_CreateUser to [YOURMACHINENAMEASPNET]
grant SELECT on dbo.aspnet_Users to [YOURMACHINENAMEASPNET]
grant UPDATE on dbo.aspnet_Users(LastActivityDate) to [YOURMACHINENAMEASPNET]
go
--drop table dbo.ProfileTable_1
--go
create table dbo.ProfileTable_1 (
UserId uniqueidentifier not null Primary Key,
FirstName nvarchar(50) null,
LastName nvarchar(50) null,
Age int null,
LastUpdatedDate datetime not null)
go
grant SELECT,INSERT,UPDATE,DELETE on dbo.ProfileTable_1 to [YOURMACHINENAMEASPNET]
go
================================================================================
Msg 15151, Level 16, State 1, Line 5
Cannot find the user 'WYATT-PCWyattASPNET', because it does not exist or you do not have permission.
How can i fix this??
I have mysql type db in which I need computer to search for a key phrase of text that has several hundred occurances inside product table descriptions of products on live site. Once it finds the text, I would like for it to simply remove it, and look for the next occurance of the phrase and repeat the process until all are flagged and removed. I was told their may be a line of sql code that employs such a feature. Can anyone tell me what such a line would be?
View 1 Replies View RelatedHi everyone,
I have a cmd file that executes sql statements:
something like this:
Code Snippet
OSQL -S %sqlServer% -E -b -n -i DATABASE.sql"
IF ERRORLEVEL 1 GOTO error
echo -
...
:error
@ECHO An error occured in [%errorLevel%]
echo [%errorLevel%] > %1
GOTO EXIT
if there is an error, I print the errorlevel in a txt file wich is a parameter in the command (%1)
What I want is: instead of sending the %errorlevel% to the txt, I want to send the error description but I don't know the variable that keeps the error description.
Any help please
Thanx in advance
'@P0' means what?
thank you!
SELECT
usr_login "User ALM EMCID",
employeeID "User PV EMPID",
usr_udf_nt_login "ALM USR LOGIN",
usr_first_name "User FirstName",
usr_last_name "User LastName",
usr_email "User Email",
usr_emp_type "Employee Type",
usr_status "ALM Status",
PeopleView.Active_Term "PV Status",
IsActive "AD Status",
benabled "ACE Status",
usr_start_date "User Start Date",
usr_end_date "User End Date",
usr_udf_manager_emcid "Manager EMCID",
manager_first_name "Manager FirstName",
manager_last_name "Manager LastName",
manager_email "Manager Email",
manager_status "Manager Status",
manager_end_date "Manager End Date"
from
(select
ALM.usr_login,
PV.employeeID,
ALM.usr_udf_nt_login,
ALM.usr_first_name,
ALM.usr_last_name,
ALM.usr_email,
ALM.usr_emp_type,
ALM.usr_status,
PV.Active_Term,
AD.IsActive,
ACE.benabled,
ALM.usr_start_date,
ALM.usr_end_date,
ALM.usr_udf_manager_emcid,
ALM.manager_first_name,
ALM.manager_last_name,
ALM.manager_email,
ALM.manager_status,
ALM.manager_end_date
from dbo.ALM_USERS ALM,
dbo.PeopleViewPV,
dbo.corp_emp_ids AD,
dbo.ALM_USERS ACE
where cast(ALM.USR_login as int)= PV.EmployeeID -- PV
and ALM.USR_login = CAST(AD.employeeid AS varchar)---AD
and ALM.USR_login = ACE.chdata ----ACE
AND ALM.USR_login NOT IN ('BTGWC1', 'DMADMIN', 'IFILL', 'TESTUSER', 'WTDEV06', 'XELOPERATOR', 'XELSELFREG', 'XELSYSADM')
AND ALM.USR_STATUS = 'Disabled')
i have a huge stored proc which has about 8-9 cursors which move data from a few temp tables to the final tables...and also do lots of calculations in between..
anytime there is an error in the stored proc i have an error page which looks like this
**********************
Server Error in '/WebApplication3' Application.
--------------------------------------------------------------------------------
Error converting data type varchar to bigint. 139 pdiscnum 37 3429 4978 139 139 4979 50.93 189.93 4980 -189.93 0 4981 139 139 4982 -139 0 4986 23 23 4987 0 23 4988 0 23 5121 139 162 5122 -328.93 -166.93 3430 4983 89 89 4984 -89 0 5096 89 89 5097 -178 -89 5135 89 0 5144 139 139 3431 4989 89 89 4990 -89 0 5100 89 89 5101 -178 -89 3432 4991 139 139 4992 4.32 143.32 4993 -143.32 0 5102 139 139 5103 -278 -139 3433 4994 139 139 4995 50.93 189.93 4996 8.64 198.57 4997 -198.57 0 5003 0 0 5123 139 139 5124 -328.93 -189.93 3434 4998 59 59 4999 -59 0 5000 59 59 5001 -59 0 5002 0 0 5041 19 19 5042 0 19 5043 -2.39 16.61 5044 -16.61 0 5045 19 19 5046 -2.39 16.61 5047 -16.61 0 5056 0 0 5084 39 39 5085 -5.85 33.15 5086 -49.76 -16.61 5104 59 42.39 5105 .........
************************************************
a very lengthy page...the numbers are prbly due to the print stmts of the results of some calculations...
but my q is..is there any way to return the xact line number where the error occured in the stored proc instead of these infinite list of numbers...its taking pretty long time to go through the entire stored proc to isolate the error...
thanks
dinakar
hi,
i got this error when i run app.
---------------
Line 1: Incorrect syntax near '='.
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.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '='.
Source Error:
Line 40: Dim adpt As New SqlDataAdapter("SELECT * FROM SMS_student_class_master WHERE" & _
Line 41: "stud_id=" & sid, con)
Line 42: adpt.Fill(ds, "SMS_student_class_master")
Line 43: txt.Text = ds.Tables.Item("roll_no").ToString
Line 44: con.Close()
Source File: c:inetpubwwwrootaspnetsmsassignment_d.aspx.vb Line: 42
--------
what should i do?
anyone have any idea?
plz give solution.
it's urgent.
thanks in advance
I have SQLEXPR (SP1) and expanded it with the /x switch.
Next, I tried the following from the command line and the installation generates an error message about my test password not being strong enough.
setup.exe /qn ADDLOCAL=ALL SECURITY MODE=SQL SQLACCOUNT="NT AUTHORITYNETWORK SERVICE" SQLPASSWORD=elinor&2JACOB2#zinno SQLBROWSER="NT AUTHORITYNETWORK SERVICE" SQLBROWSERPASSWORD=elinor&2JACOB2#zinno DISABLENETWORKPROTOCOLS=0 SQLBROWSERAUTOSTART=1
I'm trying to create a customized SQLexpr. Is this the correct way to do it? What am I doing wrong?
Thanks,
jerry
Hi,
I have a problem with a report in SSRS 2005. I tried to add some custom code to a report.
The code is absolutely simple and correct.
But when trying to show the report the debugger shows the following error message:
error [BC30494] Line is too long.
When I remove the code the report works again.
The code is about 12 words and could not be the problem.
Could anyone help please?
Best regards,
Stefoon
Hi,
I have built a stored procedure and the code is like below:
Code Snippet
DECLARE @Table_Name VARCHAR(100)
DECLARE @Table_Desc SQL_VARIANT
DECLARE @Query VARCHAR(8000)
USE HRD
SELECT @Table_Name = 'Employee'
SELECT @Query = 'IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[DEL_' + UPPER(@Table_Name) + ']''))
DROP TRIGGER [dbo].[DEL_' + UPPER(@Table_Name) + ']'
EXEC(@Query)
SELECT @Table_Desc = (SELECT a.value
FROM
sys.extended_properties a, sys.tables b
WHERE a.major_id = b.object_id
AND a.minor_id = 0
AND a.name = 'MS_DESCRIPTION'
AND b.name = @Table_Name)
SELECT @Query = '''CREATE TRIGGER [DEL_' + UPPER(@Table_Name) + '] ON dbo.' + @Table_Name + '
FOR DELETE
AS
DECLARE @Old_Value VARCHAR(8000)
DECLARE @New_Value VARCHAR(8000)
DECLARE @P_Key VARCHAR(8000)
DECLARE @P_Key_Value VARCHAR(8000)
DECLARE @P_Key_Insert VARCHAR(8000)
DECLARE @Comment VARCHAR(8000)
SELECT @P_Key_Insert = ''''''''
EXEC(''''IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_PrimKey]'''''''') AND type in (N''''''''U''''''''))
DROP TABLE [dbo].[Temp_PrimKey]'''')
SELECT K.COLUMN_NAME INTO Temp_PrimKey
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME
WHERE
T.CONSTRAINT_TYPE = ''''PRIMARY KEY'''' AND T.TABLE_NAME = ''''' + @Table_Name + '''''
EXEC(''''IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_Value]'''''''') AND type in (N''''''''U''''''''))
DROP TABLE [dbo].[Temp_Value]'''')
EXEC(''''CREATE TABLE [dbo].[Temp_Value](
[PValue] [VARCHAR](max) NOT NULL
) ON [PRIMARY]'''')
EXEC(''''IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_Deleted]'''''''') AND type in (N''''''''U''''''''))
DROP TABLE [dbo].[Temp_Deleted]'''')
SELECT * INTO Temp_Deleted FROM deleted
DECLARE Curs_PrimKey CURSOR FOR
SELECT * FROM Temp_PrimKey
OPEN Curs_PrimKey
FETCH NEXT FROM Curs_PrimKey INTO @P_Key
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC(''''INSERT INTO Temp_Value SELECT ''''+ @P_Key + '''' FROM Temp_Deleted'''')
SELECT @P_Key_Value = (SELECT PValue FROM Temp_Value)
EXEC(''''DELETE FROM Temp_Value'''')
SELECT @P_Key_Insert = @P_Key_Insert + @P_Key + '''' = '''' + @P_Key_Value + '''', ''''
FETCH NEXT FROM Curs_PrimKey INTO @P_Key
END
CLOSE Curs_PrimKey
DEALLOCATE Curs_PrimKey
EXEC(''''IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_Deleted]'''''''') AND type in (N''''''''U''''''''))
DROP TABLE [dbo].[Temp_Deleted]'''')
EXEC(''''IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_Value]'''''''') AND type in (N''''''''U''''''''))
DROP TABLE [dbo].[Temp_Value]'''')
SELECT @P_Key_Insert = LEFT(@P_Key_Insert, LEN(@P_Key_Insert)-2)
SELECT @Comment = ''''' + CAST(@Table_Desc AS VARCHAR) + ' deleted, '''' + @P_Key_Insert
INSERT INTO TLOG(Log_Date, Log_Reference, Log_Comment)
VALUES (GETDATE(), @P_Key_Insert, @Comment)
'''
--PRINT @Query
EXEC(@Query)
Problem is when I run it gives error:
Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'CREATE TRIGGER [DEL_EMPLOYEE] ON dbo.Employee FOR DELETEAS DECLARE @Old_Value VARCHAR(8000)DECLARE @New_Value VARCHA'.
If I print the @Query and run it in a query analyzer using the statement EXEC it worked. It's giving me headache, this supposed to be a simple exec statement. Please advise. Thanks.
I think this simple. First I would like to know how to get the exact date (today's date)?
View 20 Replies View RelatedHi,
for some AP issue, the file I upload must be without the line feed/carriage return in the last line.
for example:
original fixed-length file (exported from SSIS)
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
4 <-- with line feed/carriage return in the last line
The file format that AP request. The file only has 3 records, so it should end in the third line.
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
Should I use script component to do it ? I am new for VB . Anyone would help me ?
Thank you all.
I need the Trend line for the following data in Line chart they are the following data. The following are the graph are my output and i need the trend line for these Key_gap value.
This is the link [URL] ....
I need the same trend line for the Bar-Chart in SSRS 2005.
I hope I'm posting this in the correct forum (forgive me if I'm not) since I'm not sure if this is an issue with inserting an item into a db or the processing of what I get out of it. I wrote a basic commenting system in which someone my post a comment about something written on the site. I wanted to keep it very simple, but I at least want the ability for a user to have newlines in their comment without having to hardcode a <br /> or something like that. Is there a way for me to detect a newline if someone, for example, is going to their next paragraph?
Let me know if you need a better explanation.
Thanks in advance!
G'day everyoneThat's a space between the ticks.It's all part of a longer script but seeing as the failure occurs online 1if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[config]GOThat's three lines only. Does it matter that they're in Unicode?Any ideas?Kind regards,Bruce M. AxtensSoftware EngineerStrapper Technologies
View 3 Replies View RelatedHi,
I want to give a non system administrator account permission to execute xp_cmdshell.
I'm testing it by running this:
xp_cmdshell 'dir c:'
and getting this error
Msg 50001, Level 1, State 50001
xpsql.cpp: Error 1314 from CreateProcessAsUser on line 636
I have a setup a proxy account in SQL Server Agent as instructed - it's the same account that the SQL Server Service is running under.
I've deselected the 'only users with sysadm privilages...' option in SQL Server Agent properties and I've tried granting a variety of permissions to the proxy account based suggestions I've seen on google. It's a domain account which has local admin rights.
Other people seem to have found solutions but none of them have worked for me - any siggestions? SQL Server 2k Std Edn and Windows 2003 Server Std Edn.
Thanks,
Ian
IPB WARNING [2] mysql_connect() [function.mysql-connect]: Too manyconnections (Line: 131 of /ips_kernel/class_db_mysql.php)There appears to be an error with the database.You can try to refresh the page by clicking here.Error ReturnedSQL error: Too many connectionsSQL error code:Date: Saturday 17th of June 2006 02:44:59 PMWe apologise for any inconvenienceWhy I am getting this error?
View 1 Replies View RelatedHi,
I have a flat file with several rows of entire type in one of the rows a string comes and when it goes away to guard in the BD it falls, since I can know in that this row of the flat file the string?????
Dear All, I always got this error in CLR TVF:
Msg 6260, Level 16, State 1, Line 1
An error occurred while getting new row from user defined Table Valued Function :
System.InvalidOperationException: Invalid attempt to FieldCount when reader is closed.
System.InvalidOperationException:
at System.Data.SqlClient.SqlDataReaderSmi.get_FieldCount()
at System.Data.Common.DbEnumerator.BuildSchemaInfo()
at System.Data.Common.DbEnumerator.MoveNext()
Here is my code:
using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Collections;
public static class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "rowfiller",DataAccess=DataAccessKind.Read,TableDefinition = "ActID int, ActName nvarchar(50), ActCreatorID int,ActDesp nvarchar(200),ActCreateDate datetime,ActModifyDate datetime, ActStartDate datetime, ActEndDate datetime, Status int, Cost int")]
public static IEnumerable Func_GetSchCatActivityIDTable(int CatActivityID)
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
string sqlstring = "select * from Activity where CatActivityID=@CatActivityID;";
connection.Open();
SqlCommand command = new SqlCommand(sqlstring, connection);
command.Parameters.AddWithValue("@CatActivityID", CatActivityID);
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
}
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft Performance","CA1811:AvoidUncalledPrivateCode")]
public static void rowfiller(Object obj,
out SqlInt32 ActID,
out SqlString ActName,
out SqlInt32 ActCreatorID,
out SqlString ActDesp,
out SqlDateTime ActCreateDate,
out SqlDateTime ActModifyDate,
out SqlDateTime ActStartDate,
out SqlDateTime ActEndDate,
out SqlInt32 Status,
out SqlInt32 Cost,
)
{
SqlDataRecord row = (SqlDataRecord)obj;
int column = 0;
ActID = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
ActName = (row.IsDBNull(column)) ? SqlString.Null : new SqlString(row.GetString(column)); column++;
ActCreatorID = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
ActDesp = (row.IsDBNull(column)) ? SqlString.Null : new SqlString(row.GetString(column)); column++;
ActCreateDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
ActModifyDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
ActStartDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
ActEndDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
Status = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
Cost = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
}
};
Can anyone tell me what I am doing wrong? Many thanks
.
can any help me why the same exact code works in vb.net but not in C# . case is very simple
I have a table called MenuItems which has menu items and a table sizeandprice which has price for each menuitem based on the size. simple case of 1 to many relationship between menuitems table to sizeandprice table. I am trying to display in a gridview control couple of fields from menuitems table and have another template field in which i am display price and size field from the child table which is sizeand price. so basically this is how my code looks like
Page in vb works fine
<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
Dim ds As SqlDataSource = CType(e.Row.FindControl("SqlDataSource2"), SqlDataSource)
ds.SelectParameters("fkMenuItemID").DefaultValue = GridView1.DataKeys(e.Row.RowIndex).Value
End If
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PPQ_DataConnectionString1 %>" SelectCommand="SELECT [MenuItemID], [MenuItemType], [ItemName] FROM [MenuItems]"></asp:SqlDataSource>
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="MenuItemID"
DataSourceID="SqlDataSource1" OnRowDataBound="GridView1_RowDataBound">
<Columns>
<asp:BoundField DataField="MenuItemID" HeaderText="MenuItemID" InsertVisible="False"
ReadOnly="True" SortExpression="MenuItemID" />
<asp:BoundField DataField="MenuItemType" HeaderText="MenuItemType" SortExpression="MenuItemType" />
<asp:BoundField DataField="ItemName" HeaderText="ItemName" SortExpression="ItemName" />
<asp:TemplateField HeaderText="Size And Price">
<ItemTemplate>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:PPQ_DataConnectionString1 %>"
SelectCommand="SELECT [ItemSize], [ItemPrice] FROM [SizeAndPrice] WHERE ([fkMenuItemID] = @fkMenuItemID)">
<SelectParameters>
<asp:Parameter Name="fkMenuItemID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource2">
<ItemTemplate>
<%#Eval("ItemSize")%>: <%#Eval("ItemPrice", "$ {0:F2}")%><br />
</ItemTemplate>
</asp:Repeater>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
if you would notice that in order populate my repeater with childrows i need to know the menuitemid from the parent row which i do in rowdatabound event however same code in C# does not render any values for my item price and size defined inside the repeater control. here is how the c# page looks like
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
SqlDataSource source2 = e.Row.FindControl("SqlDataSource2") as SqlDataSource;
source2.SelectParameters["fkMenuItemID"].DefaultValue = GridView1.DataKeys[e.Row.RowIndex].Value as string;
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PPQ_DataConnectionString1 %>"
SelectCommand="SELECT [MenuItemID], [MenuItemType], [ItemName] FROM [MenuItems]">
</asp:SqlDataSource>
</div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="MenuItemID"
DataSourceID="SqlDataSource1" OnRowDataBound="GridView1_RowDataBound" >
<Columns>
<asp:BoundField DataField="MenuItemID" HeaderText="MenuItemID" InsertVisible="False"
ReadOnly="True" SortExpression="MenuItemID" />
<asp:BoundField DataField="MenuItemType" HeaderText="MenuItemType" SortExpression="MenuItemType" />
<asp:BoundField DataField="ItemName" HeaderText="ItemName" SortExpression="ItemName" />
<asp:TemplateField HeaderText="Size And Price">
<ItemTemplate>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:PPQ_DataConnectionString1 %>"
SelectCommand="SELECT [ItemSize], [ItemPrice] FROM [SizeAndPrice] WHERE ([fkMenuItemID] = @fkMenuItemID)">
<SelectParameters>
<asp:Parameter Name="fkMenuItemID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource2">
<ItemTemplate>
<%#Eval("ItemSize")%>: <%#Eval("ItemPrice", "$ {0:F2}")%><br />
</ItemTemplate>
</asp:Repeater>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</form>
</body>
</html>
can anyone tell me what i am doing wrong?
I'm currently working on a match function to compare two char based columns in differnet tables to create a join.
In this particular case, I'm using a few different approaches to create a higher match ratio, as typos do happen.
For instance I use a join function using convert(char(10), tbla.field) = convert(char(10), tblb.field) to match only using the first 10 characters, as a lot of records have different endings, but are in fact the same.
Are there any other ways I could attempt to make matches? I was wondering if there was a dedicated string comparison operation giving me a percentage feedback. Debut joining dbut would give an 80% match, and thus I would leave it up to the user to decide to minimum match requirements.
Thanks in advance
OK, this is a simple question for you, but i am a newbie to SQL2000, and i need to find out which Service Pack we are currently running. Could anybody please give me a short reply :)
Thanks
Chris
I've a table with values that have the '-' char, ex:LO-AS-1.I'm trying to search the table for that value but it returns more records since it breaks the search term into several others:
SELECT * FROM sys.dm_fts_parser ('"LO-AS-1"', 2070, 0, 0) returns:
lo-as-1
lo
as - noise word
1 - noise word
nn1 - noise word
Is there anyway of searching for the exact word? With LIKE it works fine but it's very slow....
For a long running stored procedure, how can I determine the SQL statement within the stored procedure that is currently running?
The Activity Monitor only shows the name of the stored procedure and whether it is a SELECT/INSERT/UPDATE, never the complete statement.
Thank you.
In one of my packages, I am trying to create functionality that is similar to the fuzzy grouping transformation in that it takes a set of records and returns a subset of unique records. However, rather than fuzzy-matching columns which creates significant overhead, I just want to compare columns exactly, similar to how the lookup transformation works. At this point, data has already been fuzzy unique/duplicate seperated, this step is to only keep one record for each exactly similar dupe for an alias table.
At the moment, I am doing this (with lack of scalability foresight) with a fuzzy grouping transformation with all columns set to exact except for one which is set to fuzzy with a minimum similarity of 0.99. While this worked during development when I was testing with a small subset of data, it is unreasonable for the ~5mil records of production data it needs to operate on.
I am sure there is an efficient way to do this. I have thought about using a lookup transformation or combinations thereof, but I have not come to a definite solution yet.
Does anyone have any experience doing something similar to what I am trying or have any suggestions/ideas/questions/etc? Any help will be greatly appreciated.
Hello All,
I'm looking for a TSQL function or technique that when provided a date time as string and also a format, it must return a valid tsql datetime object/variable.
for example somethig like
GetDateTime('20071010', 'YYYYMMDD') or GetDateTime('10/10/2008', 'MM/DD/YYYY')
Essentially I want to get away from Implicit conversion from string to datetime. I want better control over this to ensure we dont run into any issues when our code runs on systems configured to be on various cultures.
I can even live with a few fixed formats and not being able to support arbitrary formats.
Any help/ideas/advise will be appreciated.
Regards,Avinash