Failed Searching Records By Using SQLCommand Parameter LIKE '%@keywords%'
Aug 8, 2007
I use SQL Server 2005 Developer. I failed to search the records by using SQLCommand Paramater. Please find the code below.
However, when I hardcode like LIKE '%sudoku%' , it works. Could aynone help?
Thanks,
Andy.private bool searchProducts(string keywords)
{
.......
command.CommandText = "SELECT Product.ProductID, Product.Name, Product.Image, ProductPrice.Price, ISNULL(SpecialProduct.PromoDiscount,0) as PromoDiscount FROM Product INNER JOIN ProductPrice ON Product.ProductID = ProductPrice.ProductID LEFT OUTER JOIN SpecialProduct ON Product.ProductID = SpecialProduct.ProductID WHERE Product.Name LIKE '%@keywords%' ";
command.Parameters.Add("@keywords", SqlDbType.VarChar, 100);
command.Parameters["@keywords"].Value = keywords;
.............
}
View 3 Replies
ADVERTISEMENT
Jun 17, 2008
Hi All,
i have some views in my database, and these views are having some columns,i want to know particular column name to be there in particular view.
For example ,just like functionality of sp_search_code 'Keyword'.
like this i want to search in views .
or else please let me know sp_search_code 'Keyword'. was used for views also.
Thanks and Regards,
G.JaganMohanrao.
View 2 Replies
View Related
Jun 3, 2008
I have a problem where records in underlying tables of a dataview are being deleted (seemingly at random)
For example.
CREATE TABLE [Employee] (Id int, Name varchar(50))
CREATE TABLE [Company] (Id int, Name varchar(50))
CREATE TABLE [EmployeeCompany] (CompanyId int, EmployeeId int)
CREATE VIEW [dvEmployee]
AS
SELECT *
FROM [Employee] INNER JOIN [EmployeeCompany]
ON [Employee].[Id] = [EmployeeCompany].[EmployeeId]
CREATE TRIGGER [dvEmployeeUpdate]
ON [dbo].[dvEmployee]
INSTEAD OF UPDATE
AS
BEGIN
UPDATE EmployeeCompany
SET Status = INSERTED.Status
FROM EmployeeCompany, INSERTED
WHERE EmployeeCompany.CompanyId = INSERTED.CompanyId
AND EmployeeCompany.EmployeeId = INSERTED.EmployeeId
END
Because the column [Status] is a t-sql keyword, does the fact that the trigger contains the line "SET Status = ..." without saying "SET [Status] = ..." mean that I could lose records in the EmployeeCompany table?
Reason I'm asking is we have an already designed database that is littered with columns named the same as sql keywords (almost every table has a [Status] column, and there are many [Password] columns). When using a dataview on these tables, triggers exist that aren't putting the [] around these column names (the same as my dvEmployeeUpdate trigger above), and somehow we are seemingly randomly losing records. It is very rare, and they are getting completely deleted, and it seems to be the tables that contain the keyword columns and are used in dataviews with instead of triggers that don't put [] around the column names. Nowhere in any trigger or stored procedure is there a DELETE FROM on these tables, and the software running on the database uses only the data views, and doesn't directly access the underlying tables.
I've been going through all of the code adding the [], but my question is simply whether or not anyone has heard of this causing the deletion of any records, or whether there may be something else going on that I should be looking into?
View 2 Replies
View Related
Mar 6, 2007
I created an xmldocument which I would like to insert in a db field with the data type XML.The following code is giving me the error:System.Data.SqlClient.SqlException: XML parsing: line 1, character 38, unable to
switch the encoding SqlCommand cmdUpdate = new SqlCommand("sp_AddHistory", sqlConnection); cmdUpdate.CommandType = CommandType.StoredProcedure; cmdUpdate.Parameters["@FieldsChanged"].Value = xmlDoc.innerXML; // don't know whether this is good cmdUpdate.ExecuteNonQuery(); innerXML:<?xml version="1.0" encoding="utf-8"?><Fields> <Field> <FieldName>comp_Area</FieldName> <OldValue>Area 52</OldValue> <NewValue>Area 51</NewValue> </Field></Fields> The XML seems fine.. any ideas?
View 3 Replies
View Related
Jun 3, 2008
I'm trying to build an SQL string that should look like this when executed:
UPDATE [Table] SET Active = 'False' WHERE ID IN (3, 4, 5, 6, 7, etc.)
I'm using the convention (in code behind):SqlCommand cmd = new SqlCommand("UPDATE [Table] SET Active = 'False' WHERE ID IN (@TheIDs)", connection);cmd.Parameters.Add("TheIDs", SqlDbType.Text).Value = theIDsAsAnArrayList;
But logically enough I cannot insert them as a text string as they have to be integers seperated by commas.
Question: How can I convert an Array of integers into ... well, a string without the quotes, if you know what I mean?
As it cannot end up like this: UPDATE [Table] SET Active = 'False' WHERE ID IN ("3, 4, 5, 6, 7, etc.")
Note the quotes around the integers.
Any hints on doing this with security in mind are welcome. I know I can concatenate the whole lot as strings, but this is unsecure, so I'm not going for that approach.
View 4 Replies
View Related
Apr 13, 2007
Hi everyone? I have a small problem here, I want to count the records found under the following method: Public Function ValidateAssembly(ByVal assyno As String) As DataSet
Dim SQLConn As SqlConnection = New SqlConnection(Connstr)
Dim adapter As SqlDataAdapter = New SqlDataAdapter
Dim dsVendorInfo As DataSet = New DataSet("AssemblyHeader")
Dim cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand
Dim BMItemno As New SqlParameter("@v_assyno", SqlDbType.VarChar, 10)
BMItemno.Value = GetBomAssyNo(assyno)
SQLConn.Open()
Try
With cmd
.CommandText = "SELECT * FROM dbo.cfn_bom_get_assy(@v_assyno)"
.Parameters.Add(BMItemno)
.Connection = SQLConn
.CommandType = CommandType.Text
End With
adapter.SelectCommand = cmd
adapter.Fill(dsVendorInfo)
Catch x As Exception
End Try
SQLConn.Close()
Return dsVendorInfo
End Function I want to see if this can be done and passed to the actual dataset? Is this possible? Or do I need to pass the found results from my query to a sqlreader? Also, Im I forced to put my colums together in a DataTable before actually binding to the DataSet? Thanks everyone!
View 5 Replies
View Related
Aug 27, 2007
Hello,
Newbee here
64 bit SQL 2005 running on Windows Server 2003 X64
I have an exececute SQL task (in the control flow obviously)
SELECT MAX(last_update) AS OrdersLastUpdateFROM orders
This task executes successfully and I can see that my user variable called "User:tmOrdersLastUpdate" populates correctly in the "variables" pane.. ALL GOOD.
The next step of the Control flow is a dataflow task
Details
DataFlow Source = DataReader Source (MySQL .NET connector)
DataFlow Dest = local SQL Server OLE DB.
In the DataFlow Source the DataReader SQLCommand property is
Select * from orders where last_update >= @User:tmOrdersLastUpdate
I've tried every conceivable permutation and I can't get SSIS to itnerpret the variable as such...it always gets passed to the server as a literal.
How do I pass a user-defined global variable to the WHERE clause in a DataRader object?
Thanks
View 5 Replies
View Related
Sep 21, 2006
Hi,I'm new to ASP.NET, and am currently looking into XML.I'm trying to write XML using data from an SQL Server 2000 table. But I seem to be getting the following error regarding the SQL Server connection:Compiler Error Message: CS1502: The best overloaded method match for 'System.Data.SqlClient.SqlCommand.SqlCommand(string, System.Data.SqlClient.SqlConnection)' has some invalid argumentsSource Error:Line 23: {
Line 24: SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
Line 25: mySqlDataAdapter.SelectCommand = new SqlCommand(queryString, connString);
Line 26: mySqlDataAdapter.Fill(myDataSet);
Line 27: return myDataSet;Source File: c:InetpubwwwrootmappingcreateGeoRSSFile.aspx.cs Line: 25 This is my code:using System;
using System.Data;
using System.Data.SqlClient ;
using System.Configuration;
using System.Collections;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;
public partial class createGeoRSSFile : System.Web.UI.Page
{
protected void Page_Load(object sender, DataSet myDataSet, EventArgs e)
{
string connString = "server=SQLSERV1;database=Historical_Statistics;UID=dbuser;PWD=Password";
string queryString = "SELECT Town, PostCode, Latitude, Longitude FROM UKPostCodes";
using (SqlConnection mySqlConnection = new SqlConnection(connString))
{
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = new SqlCommand(queryString, connString);
mySqlDataAdapter.Fill(myDataSet);
return myDataSet;
}
// Create a new XmlTextWriter instance
XmlTextWriter writer = new XmlTextWriter(Response.OutputStream, Encoding.Unicode);
// Start writing!
writer.WriteStartDocument();
writer.WriteStartElement("item");
// Creating the <town> element
writer.WriteStartElement("town");
writer.WriteElementString("PostCode",myDataSet .Tables[1].Columns("PostCode"));
writer.WriteElementString("geo:lat",myDataSet.Tables[1].Columns("Latitude"));
writer.WriteElementString("geo:lon", myDataSet.Tables[1].Columns("Longitude"));
writer.WriteEndElement();
writer.WriteEndElement();
writer.WriteEndDocument();
writer.Flush();
writer.Close();
}
}What seems to be causing this error?Thanks.
View 4 Replies
View Related
Jul 20, 2004
I want to create a stored procedure which takes a project name and returns any project with a name similar or = to the input parameter. This is so the user can search for a project although the spelling may be incorrect or whatever.....
Any hints on how to start this? It seems that I could use SOUNDEX or DIFFERENCE, but I don't know where to start.
Any hints?
Mike B
View 7 Replies
View Related
Jan 29, 2006
Hi I am developing windows application. I want to find records which has a particular number which is entered in textbox. and result is displayed in datagrid. If the entered number didnt find in database it must be displayed msg. i tried it like as below but not working. qlConnection conn = new SqlConnection(); conn.ConnectionString = "Server=EBSERVER;UID=sa;Database=Airport-Clearance;"; //SqlConnection sqlconn = objcs.GetConnection(); //MessageBox.Show("Connected"); //sqlconn.Open(); //Do what ever SqlDataAdapter filling = new SqlDataAdapter("select * from Airport where awb='" + txtawb.Text ,conn); DataSet displaying = new DataSet(); conn.Open(); filling.Fill(displaying); dataGrid1.DataSource = displaying.DefaultViewManager; conn.Close(); Waiting for reply. Warmest regards, ASIF
View 1 Replies
View Related
Oct 9, 2007
I'm hoping someone can help me with my problem.
I'm reading in records from a 'flat file' and loading them into sql.
I have 5 values I'm loading in. I first check my sql db, if all 5 values match a current record in sql, I don't want to load the record, because it's already there. If it doesn't exist, I need to load it.
It works fine as long as none of my values are NULL. But if I have a record with a field, say Gender, that is null, if its a new record it loads fine, setting gender to NULL in sql. But then when I encounter another record, which is identical, I'm testing to see if it already exists by doing a 'select where Gender = @Gender' and it always returns that the record does not exist in the db - even though it exists (because its using the = instead of is null)!
I need some mechanism where if the value is null it tests 'Gender is null' but if there is a value, it tests gender=gender, [and I need this for all my parameters]. Or is there some other way to do this?
Here is my code for looking to see if the record exists:
public static int ExistInsured(int CaseID, object InsuredLastName, object DateOfBirth, object CurrentAge, object Gender)
{SqlConnection conn = new SqlConnection(connStng.ToString());
SqlCommand cmd = new SqlCommand("SELECT top 1 InsuredID from Insured Where PolicyID = (Select PolicyID from Policy where CaseID = @CaseID) and LastName = @InsuredLastName and DateofBirth = @DateofBirth and Gender = @Gender", conn);
int result = -1;
cmd.CommandType = CommandType.Text;cmd.Parameters.AddWithValue("@CaseID", CaseID);
cmd.Parameters.AddWithValue("@InsuredLastName", InsuredLastName);cmd.Parameters.AddWithValue("@DateOfBirth", DateOfBirth);
cmd.Parameters.AddWithValue("@CurrentAge", CurrentAge);
cmd.Parameters.AddWithValue("@Gender", Gender);
conn.Open();
try
{object ret = cmd.ExecuteScalar();
if ((ret != null) && (ret is int))result = (int)ret;
}
finally
{
conn.Close();
}return result;
}
View 6 Replies
View Related
Nov 28, 2003
ive got a tough one for everyone this time!
i am trying to get a page up that enables me to enter a number into a box on a form, which then on clicking of a search button, queries the database, and returns 2 other fields that relate to the number found in the database, in an ASP datagrid.
the easiest way to do this would be to use OLEDB or ODBC, but the host i am using doesnt support either of them, and so i am trying to use their code, something using OBJDB, but to no avail. frankly, their code is rubbish, and im not sure how to go about doing the above.
i would be very grateful if someone could help me out with this problem,
thanks,
craig
View 1 Replies
View Related
Oct 8, 2007
Hi,
I have 4 tables :
Code Block
Create Table #Request ( [requestid] int , [stateno] nvarchar(5) , [cityno] int , Callid int, UniqueNo int);
Create Table #RequestDetail(reqdetailid int, [customername] Varchar(20), [customerage] int, requestid int);
Create Table #Call(Callid int,Calltype int,callDetailid int )
Create Table #CallDetail(callDetailId int,empid int)
Insert into #CallDetail VALUES(12123,1)
Insert into #CallDetail VALUES(53423,1)
Insert into #CallDetail VALUES(6532,1)
Insert into #CallDetail VALUES(82323,1)
Insert into #CallDetail VALUES(124235,1)
Insert Into #Call VALUES(111,1,12123)
Insert Into #Call VALUES(112,1,53423)
Insert Into #Call VALUES(114,1,6532)
Insert Into #Call VALUES(123,2,6532)
Insert Into #Call VALUES(134,1,124235)
Insert Into #request Values('324234','SA023',12,111,0);
Insert Into #request Values('223452','SA023',12,112,0);
Insert Into #request Values('456456','SA024',12,114,0);
Insert Into #request Values('22322362','SA024',44,123,0);
Insert Into #request Values('22654392','SA023',12,134,0);
Insert into #requestdetail values(1, 'Bill',23,'324234')
Insert into #requestdetail values(2, 'Tom',25,'223452')
Insert into #requestdetail values(3, 'Bobby',27,'456456')
Insert into #requestdetail values(4, 'Guck',29,'22322362')
Insert into #requestdetail values(5, 'Bobby',33,'22654392')
1. My stored proc will take the CallDetailID.
2. I have to find out the requests made on this calldetail.
3. After getting the request, i want to take the Customername, cityno of the request/requestdetail and pass it again to #request table to search for any duplicates within the request.
4. If found return the details of the original record :
[A similar requirement was solved earlier, but the structure has changed. This is a seperate requirement with different table strucure. Hence a new post. ]
thanks
View 7 Replies
View Related
Oct 8, 2007
In my asp.net web application I used two textboxes for date1 and date2 to search records I have used two variable in my stored procedure @OrderDate1 datetime,@OrderDate2 datetime,for todays record (values for date1 and Date2)set @OrderDate1='09/20/2007 12:00 AM';set @OrderDate2='09/20/2007 11:59 PM';for records between (values for date1 and Date2)set @OrderDate1='09/20/2007 12:00 AM';set @OrderDate2='09/24/2007 11:59 PM';I have to search the todays records OR records between these two days (OrderDate >= @OrderDate1 ) AND (OrderDate <= @OrderDate2) It
works fine in my local server>>It finds the todays records by
ginving same date (@OrderDate1 and @OrderDate2) and records between two
dates but it does not work for sql server which is outside india ...if it is problem of date format how i can solve that.. Thanks
View 1 Replies
View Related
Sep 12, 2005
Hope I am in the correct section.
I am installing a FTS system on an existing system (that used LIKE % queries!! hahaha)
Anyway, it is working pretty well (AND FAST!) but when I type in a
common word like "damage" I get like 32,000 records. Now, the
server handles those records in about one second but the ASP page that
returns the results takes about one MINUTE to download. When I
save the source, it is almost 12 MEGS!!
So, basically, I am streaming 12 megs across the pipe and I want to reduce that.
I would like the system to detect over maybe 500 records and cancel the search.
I have put a "TOP 500" into the search and that actually works pretty well but is there a better/smarter method?
Thanks!
cbmeeks
View 3 Replies
View Related
Feb 10, 2008
Hi
Is it possible To pass an SQL command to a ASp.net web service as system.data.SQLclient.sqlcommand?
That means is ispossible to pass the actuall sql command instead of just the string?
If yes how can you do that??
Cheers
View 1 Replies
View Related
Mar 20, 2008
I've developed a report which works fine when I preview it in the Visual Studio report designer, but when it is uploaded to our report server and I try to access it via Internet Explorer, I get an error message saying: "
Parameter validation failed. It is not possible to provide valid values for all parameters. (rsParameterError) "
How would I know exactly which parameter is causing this problem? I've built a couple of reports in the past and they worked fine. The only thing I can think of which is different in this report is that I have a couple of date parameters - Start Date and End Date, which allows the user to select a date range for the data they want to view.
In the SQL Server database, the date value is defined as SmallDateTime and in the Report Parameters the data type is defined as DateTime.
In the database there are Nulls and blanks in some of the fields - could this be the cause of the error?
View 2 Replies
View Related
Jul 23, 2005
Does anyone know an effective way to capture any records that failed toinsert into a table when inserting from an xml document in SQL Svr2000?thanks.-ak
View 3 Replies
View Related
Jul 3, 2007
Hi all,I have setup my database that have 3 columns:1. Primary Key2. UserId (UniqueIdentifier, Guid I guess) - I set it up so I can insert the value from the Membership table (UserId) for some relationship.3. Another Foreign key with just an int. I tried to build a DAL, ran a test and received this error: "Failed to convert parameter value from a String to a Guid." Before I setup my UserId to be UniqueIdentifier and let it be just an Int, I don't have any problem retrieving data. Here is the SELECT query that I built with the DataSet:SELECT aspnet_Users.UserId, t_music.MUSIC_TITLEFROM t_user_viewedJOIN aspnet_Users ON aspnet_Users.UserId = t_user_viewed.UserId JOIN t_music ON t_music.MUSIC_PK = t_user_viewed.MUSIC_PK_FKWHERE aspnet_Users.UserId = @UserId Any help would be greatly appreciated,Kenny.
View 9 Replies
View Related
Apr 15, 2012
i am trying to save data from unbound datagridview but i get the error " format exception unhandled- failed to convert parameter value to a int32" the following is my code:
Private Sub btnPlace_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPlace.Click
' Modify the following code to correctly connect to your SQL Server.
Dim Connection As New SqlConnection("Server=.SQLEXPRESS;Initial Catalog=Store Management System;Integrated Security=SSPI ")
[code]...
View 1 Replies
View Related
Nov 1, 2007
Hi I am having this problem. I have created a stored proc that uses dynamic sql. Now this works fine when I use it in Query Analyzer.
Now when I try to use this in Reporting services it gives me the error:
'
Failed to generate fields for the query
Check the query syntax or click refresh on query toolbar.
Failed to convert parameter value from a string to a datetime
'
My sotred proc is as:
<quote>
-------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[rptDetailedAuditsIssued]
(
@param1 varchar(50) ,
@param2 varchar(50),
@param3 varchar(50),
@param4 datetime ,
@param5 datetime
)
AS
begin
declare @strSQL varchar(500)
set @strSQL = 'SELECT * FROM v_DetailedAuditsIssued WHERE '
if @param1 <> ''
Begin
set @strSQL = @strSQL + 'strRegion = ' + @param1 + ' AND '
End
if @param2 <> ''
Begin
set @strSQL = @strSQL + ' strTaxTypeCode = ' + @param2 + ' AND '
End
if @param3 <> ''
Begin
set @strSQL = @strSQL + ' strAuditType = ' + @param3 + ' AND '
End
set @strSQL = @strSQL + ' (dtmIssuedDate BETWEEN '''
+ CONVERT(nvarchar(30), @param4, 101) + ''' AND '''
+ CONVERT(nvarchar(30),@param5, 101) + ''')'
exec (@strSQ
end
--------------------------------------------------------------------------------------------------------------------------
</quote>
When I run
rptDetailedAuditsIssued '','','','1/1/2000','1/1/2001'in query editor it runs fine. But gives me that error every time I run it in SSRS. I think I got this problem but in earlier cases I used to set the param values to null in Repservices and it worked but its not working this time. This time it stops complaining but does generate any fields.
View 1 Replies
View Related
Aug 1, 2007
Hi All,
I can run the report if I write following in the query and run it as text in report designer's data tab it works fine.
exec abc '9B95363B-F82D-4E55-AD89-2AD928AC981F',NULL,NULL,'07/03/2006',07/08/2006'
But when I am trying to run it as stored procedure as follow
abc
and I assign the same value for the parameter in Define Query Parameter dialog box. it gives following error.
"
an error occured while executing the query.
Failed to convert parameter value from string to guid.
Additiona Information :
failed to convert parameter value from a string to a guid.(system.data)
"
Thanks for help.
View 8 Replies
View Related
Jul 31, 2007
My question is why does the inclusion of my parameter, @GL, cause my report to only show records where the LEFT OUTER JOIN to the GRPLDR table is NOT NULL from tblMain.
Basically, with the parameter in the report, I get 6 pages. Without the parameter, I get 10 pages and those records where the GRPLDR.GLPerson is null for the join.
I tried the exact same query is Crystal reports, and get exactly what I needed by the "NULL values set to default" option.
Here's my query:
SELECT field1, field2, GRPLDR.GLPerson
FROM tblMain LEFT OUTER JOIN
(
SELECT Person AS GLPerson, WPIdentifier
FROM SigAuth WHERE (Title = 'Specific Value')
)
AS GRPLDR ON tblMain.WPIdentifier = GRPLDR.WPIdentifier
WHERE (GRPLDR.GLPerson LIKE @GL + '%')
View 1 Replies
View Related
Feb 10, 2008
Hi, i have some problems passing a guid parameter to a stored procedure; the code is below and the error i get is;
Failed to convert parameter value from a String to a Guid
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);
cmd = new SqlCommand("spInsKeyswap_history", conn);
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
MembershipUser mu = Membership.GetUser(User.Identity.Name);
guid gdUserID = mu.ProviderUserKey;
cmd.Parameters.Add("@user_title", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@first_name", SqlDbType.VarChar, 20);
cmd.Parameters.Add("@last_name", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@email", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@birthday", SqlDbType.VarChar, 15);
cmd.Parameters.Add("@alternate_number", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@msisdn", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@call1", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@call2", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@call3", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@new_sim_msidn", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@dealer_id", SqlDbType.UniqueIdentifier);
cmd.Parameters.Add("@status_code", SqlDbType.Int);
cmd.Parameters.Add("@support_id", SqlDbType.Int);
cmd.Parameters.Add("@return_value", SqlDbType.Int);
cmd.Parameters["@user_title"].Value = txtTitle.Text.ToString();
cmd.Parameters["@first_name"].Value = txtFirstName.Text.ToString();
cmd.Parameters["@last_name"].Value = txtLastName.Text.ToString();
cmd.Parameters["@email"].Value = txtEmailAddress.Text.ToString();
cmd.Parameters["@birthday"].Value = txtBirthday.Text;
cmd.Parameters["@alternate_number"].Value = txtAlternate.Text.ToString();
cmd.Parameters["@msisdn"].Value = txtNew.Text.ToString();
cmd.Parameters["@call1"].Value = txtCall1.Text.ToString();
cmd.Parameters["@call2"].Value = txtCall2.Text.ToString();
cmd.Parameters["@call3"].Value = txtCall3.Text.ToString();
cmd.Parameters["@new_sim_msidn"].Value = txtOld.Text.ToString();
//get logged in users user_id from membership
cmd.Parameters["@dealer_id"].Value = gdUserID;
cmd.Parameters["@status_code"].Value = 1;
cmd.Parameters["@support_id"].Value = 0;
cmd.Parameters["@return_value"].Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
int returnValue = (int)cmd.Parameters["@return_value"].Value;
View 1 Replies
View Related
Jan 1, 2008
Hi:I have written a SQL statement that accepts a letter and then prints out all the records in a table starting with that letter. I was wondering if there is a way that I could change the query so that if prints out all records if a blank or empty value is passed in?Here's my query: ALTER PROCEDURE [dbo].[GetMediaListByFirstLetter] ( @firstLetter char(1))AS SELECT Media_ID, OrgName FROM Media WHERE UPPER(SUBSTRING(Media.OrgName,1,1)) = @firstLetterAny help doing this would be greatly appreciated.Roger
View 5 Replies
View Related
Jul 15, 2013
I have to build a query and I am not sure how to build it.
Our data has providers with different products. We have an “DGH” product and a “PRM”. I need to find the provider that don’t have both. Its sounds very simple, but I am having an awful time figuring it out. Sometimes I get a record that has both products, it is probably how my where clause is working.
Anyway, here is an example of what I don’t want in my records because this is good. It has a DGH and PRM.
pro_pcs pro_lname pro_fname prop_prd pro_tax1 pro_addr100018791 Smith John DGH 12345868 Kiem ST
00018791 Smith John PRM 12345868 Keim ST
I would want to see this one because there only a DGH and it need to have a PRM product.
pro_pcspro_lname pro_fname prop_prd pro_tax1 pro_addr1
00018791 Rubin SamDGH2345868 Kiem ST
I came up with this below and it seems to be working. The only thing is, I now have to make this so people can add a parameter on the pro_tax1 to see the different records. Is there a better way to do this or how can I use what I have below and add a parameter too it.
Select pro_pcs, pro_lname, pro_fname, pro_addr1
from #ALLProducts
Where pro_tax1 = '12345868'
and prop_prd = 'DGH'
except
Select pro_pcs, pro_lname, pro_fname, pro_addr1
from #ALLProducts
Where pro_tax1 = '12345868'and prop_prd = 'PRM'
View 3 Replies
View Related
Feb 7, 2007
In my report I want an optional parameter to filter all records with a specific field that is not blank. I tried several scenario's without result...
In the parameter I want to set a text value like "exampletext".
In the filter I want a check: if the parameter value is "exampletext", only show the records where field "abc" is not blank.
On the tab Filters from the Table properties I can set three values: Expression, Operator and Value.
Please help!
View 1 Replies
View Related
Aug 20, 1999
Where can I find the list of new keywords for sql 7.0? Namely the keyword to allow duplicates when creating a table?
Thanks!
View 1 Replies
View Related
Sep 21, 2007
does anyone know whether the words 'comments' and 'narrative' have become reserved keywords post-SQL2000 ?
View 2 Replies
View Related
Apr 25, 2008
When designing a table, or column name. I am sure its best to avoid using table names or column names that use reserved words such as
"status" and "message"
What happens when we name a column or table using these words? Any tiny performance hit ? I am just wondering whats happening behind the scenes.
Thanks!
mike123
View 8 Replies
View Related
May 16, 2008
How can I find distinct ID'd in this table that has similar keywords to an ID I pass in like for example I pass in 1. It has 3 keywords. I would bring back 14 and 4 because 14 has bike and 4 has VAN
ID Keyword
1car
1boat
1van
14test
14car
14bike
4VAN
5 scooter
View 8 Replies
View Related
Jun 20, 2007
i had writen a stored procedure for inserting vlaues frma form into sql but iam getting this error can any pls help me
Failed to convert parameter value from a String to a Int32
my stored procedureALTER PROCEDURE sp_addcustomer
(@customerid as int,
@fname as char(10),@lname as char(10) ,
@companyname as char(10),@email as nvarchar(50),
@address as char(10),@city as char(10),
@zip as char(10),@country as char(10),
@phone as nvarchar(50),@state as char(10)
)
ASinsert into customer(customerid ,fname ,lname ,companyname, email , address ,city,zip,country,phone,state)
values(@customerid ,@fname ,@lname ,@companyname,@email,@address,@city,@zip,@country,@phone,@state)
RETURN
CODE IN C# IS
SqlConnection cn = new SqlConnection(Session["conn"].ToString());
cn.Open();SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;cmd.CommandType = CommandType.StoredProcedure;cmd.CommandText = "sp_addcustomer";
//SqlParameter p1, p2, p3, p4, p5, p6, p7, p8, p9, p10,p11;cmd.Parameters.Add(new SqlParameter("@customerid",SqlDbType.Int));
cmd.Parameters["@customerid"].Value =CustomerID.Text;cmd.Parameters.Add(new SqlParameter("@fname", SqlDbType.Char));
cmd.Parameters["@fname"].Value = fname.Text;cmd.Parameters.Add(new SqlParameter("@lname", SqlDbType.Char));
cmd.Parameters["@lname"].Value = lname.Text;cmd.Parameters.Add(new SqlParameter("@companyname", SqlDbType.Char));
cmd.Parameters["@companyname"].Value = companyname.Text;cmd.Parameters.Add(new SqlParameter("@email", SqlDbType.NVarChar));
cmd.Parameters["@email"].Value = email.Text;cmd.Parameters.Add(new SqlParameter("@address", SqlDbType.Char));
cmd.Parameters["@address"].Value = address.Text;cmd.Parameters.Add(new SqlParameter("@city", SqlDbType.Char));
cmd.Parameters["@city"].Value = city.Text;cmd.Parameters.Add(new SqlParameter("@zip", SqlDbType.NVarChar));
cmd.Parameters["@zip"].Value = zip.Text;cmd.Parameters.Add(new SqlParameter("@country", SqlDbType.Char));
cmd.Parameters["@country"].Value = Country.Text;cmd.Parameters.Add(new SqlParameter("@phone", SqlDbType.NVarChar));
cmd.Parameters["@phone"].Value = Phone.Text;cmd.Parameters.Add(new SqlParameter("@state", SqlDbType.Char));cmd.Parameters["@state"].Value = state.Text;
cmd.ExecuteNonQuery();
cn.Close();
Response.Redirect("main.aspx?st=Employee added successfully");
View 4 Replies
View Related
Oct 17, 2007
I'm getting this error and I'm not really sure what is wrong. I'm using the sqlhelper class which was writen by microsoft, so I figure the code is right.
Here is code of me passing in the strings. Yes I tried puttinga .ToString() on the end of the sqlparameters.
public class LoginSelectData : DataAccessBase{private EndUser _enduser;public LoginSelectData(){ StoredProcedureName = StoredProcedure.Name.VERIFYUSER.ToString();}public DataSet Get(){DataSet ds;LoginSelectDataParameters _loginselectedataParameters = new LoginSelectDataParameters(this.Enduser);DataBaseHelper dbhelper = new DataBaseHelper(StoredProcedureName);ds = dbhelper.Run(base.ConnectionString, _loginselectedataParameters.Parameters);return ds;
}public EndUser Enduser{get { return _enduser; }set { _enduser = value; }}
}
public class LoginSelectDataParameters{private EndUser _enduser;private SqlParameter[] _parameters;public LoginSelectDataParameters(EndUser userinfo){this.Enduser = userinfo;
Build();
}
private void Build(){
SqlParameter[] parameters = {new SqlParameter("@username",this.Enduser.Username),new SqlParameter("@password",this.Enduser.Password)
};
Parameters = parameters;}public SqlParameter[] Parameters{get { return _parameters; set { _parameters = value; }
}public EndUser Enduser{get { return _enduser; }set { _enduser = value; }}
}
View 4 Replies
View Related
