Problem In C# Code Behind And With SQL String Date And The Between Statement
May 13, 2008
Hi
I have a problem to search between two dates, if I use Convert(varchar(10),ContactCreateDate,105) in a single textbow and for a single search date it is okay, but when I use the Convert(varchar(10),ContactCreateDate,105) against two textboxes it did't function.
Can anybody help me ?
MY codeif (TBContactSearchDateStart.Text.Length > 0 && TBContactSearchDateEnd.Text.Length > 0)
{
strWhere += " AND Convert(varchar(10),ContactCreateDate,105) BETWEEN '%" + TBContactSearchDateStart.Text + "%' AND '%" + TBContactSearchDateEnd.Text + "%'";
View 1 Replies
ADVERTISEMENT
Feb 5, 2007
My source file is showing column 10 as string. My destination table is datetime. I am using the derived transformation with a conditional statement. How do I convert the value from string to date. Everywhere I try the (DT_DATE) I get an error.
[Column 10] == "01/01/0001" ? " 01/01/1801" : [Column 10] <= "12/31/1801" ? "12/31/1801" : [Column 10]
View 9 Replies
View Related
Sep 21, 2006
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
View 3 Replies
View Related
Mar 18, 2014
I have the following
Column Name : [Converted Date]
Data Type : varchar(50)
When I try and do month around the [Converted Date] I get the following error message
“Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.”
My Query is
SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]
View 7 Replies
View Related
Nov 16, 2015
SELECT * ,[Due]
FROM [Events]
Where Due >= getdate() +90
This returns the error: Conversion failed when converting date and/or time from character string
Why would this be? How to cast or convert this so that it will work?
View 24 Replies
View Related
Sep 28, 2007
Using DTS package in 2000 version, I am dumping TXT file contents into SQL Table,
I have one column having date in format YYYYMMDD(20070929) and corresponding column in SQL is datetime, but it fails on data type mismatch.
I have no choice of making date column in SQL to string or Varchar etc,
is there any way to make that date column in SQL to convert the value upon transformation from format (YYYYMMDD) to M/DD/YYYY (9/29/2007).
many many thanks,
View 2 Replies
View Related
Jan 30, 2007
I asked this question below, but the answer was that the conversion will take place automatically, but I can't get that to happen. I have a flat file with an 8 position field that I identify as string (and I also tried date) that is yyyymmdd and it needs to go into the database field that is datetime format. IS there something I am doing wrong with the definition of it, or do I need to add some kind of conversion, and if so, what and how would that be done. I'm a dts Sql2000 expert, but the SSIS thing is driving me crazy. I have a ton of dts' to convert and the migration tool doesn't work because there are a lot of active X scripts in them. thanks for your help. Boston Rose
View 1 Replies
View Related
Feb 15, 2008
Hi,
I Have this simple convertion in a Script component
Dts.Variables("dateOfProcess").Value = CDate(lineMCF.Substring(30, 2) + "/" + lineMCF.Substring(28, 2) + "/" + lineMCF.Substring(24, 4))
this works fin in my development environment which has a spanish version of SQL Server and uses "DD/MM/YYYY" as date format.
but the production environment has an english version of SQL Server and "MM/DD/YYYY" date format, so the package crashes in this server.
How do I convert the string to date not depending on the SQL server language.
thanks.
View 2 Replies
View Related
Jan 8, 2013
I have a table in which a date value is stored as varchar.some of these values are stored ina dd/mm/yyyy format and other values are stored in a yyyy-mm-dd format..Now I wish to retrieve some data by querying between two dates. However I need to convert the varchar date value to datetime in order to do this but since the date value is in two different formats, the following doesn't work.
select date_value
from my_table
where CONVERT(DATETIME, date_value, 103) between @date1 and @date2
How can you convert the date value to datetime when its stored in mutiple formats. I can't change the table itself as I dont have admin privelages.
View 14 Replies
View Related
May 2, 2008
I currently have the following code in my designer file
<asp:SqlDataSource ID="SqlDataSource1" Runat="server" SelectCommand="select Site_name, system_id, ASP_Archive, sitetimes,HPOV_ROC, UPPER(CircuitType) as CircuitType, QwestCircuit_ID, SiteConfig,Site_Nat, PVC_VCI from tblASPCustomerWan order by Asp_Archive asc"
UpdateCommand="UPDATE tblASPCustomerWan SET [Site_name] = @Site_name, [system_id] = @system_id, [ASP_Archive] = @ASP_Archive, [sitetimes] = @sitetimes, [HPOV_ROC] = @HPOV_ROC, [CircuitType] = @CircuitType, [QwestCircuit_ID] = @QwestCircuit_ID, [SiteConfig]= @SiteConfig, [Site_Nat]=@Site_Nat,[PVC_VCI]=@PVC_VCI"
ConnectionString="server=localhost;Trusted_Connection=yes;uid=portal_user;pwd=Cr@zyP@55w0rd;database=CusPortal_Staging" />
I would like the change the connection so it takes it value from System.Configuration.ConfigurationManager.AppSettings("appStrConnection"), how can i do that??
View 1 Replies
View Related
Jan 14, 2008
How do I convert for example, a value comming from C# app "TextboxGBDataDeleted" to int column GBdatadeleted? I have a RangeValidator on this textbox to accept only numbers. I don't want to write C# code. I woul prefer to do this is SQL. Thank you.
My SQL update statement:
INSERT INTO KPITbl
(Lead, WRM, PTDB, PAR, PM, RequestingLOB, LOB, StartLocation, FinishLocation, Description, ProjectType, ServerName, ServerType, DCOorSTANDALONE, Responsible,
Status, RAG, StartDates, EndDates, TreeOrDomainImpacted, NumOfSites, NumOfUsers, GBdatamoved, GBdatadeleted, NumOfSrvrsAdded,
NumOfSrvrsDecommed, NumOfAppsDeployed, EUTEngineeringConsult, Comments, TimeSpend, Complexity, ECM, LastUpdated)
VALUES (@Lead,@WRM,@PTDB,@PAR,@PM,@RequestingLOB,@LOB,@StartLocation,@FinishLocation,@Description,@ProjectType,@ServerName,@ServerType,@DCOorSTANDALONE,@Responsible,@Status,@RAG,@StartDates,@EndDates,@TreeOrDomainImpacted,@NumOfSites,@NumOfUsers,@GBdatamoved,@GBdatadeleted,@NumOfSrvrsAdded,@NumOfSrvrsDecommed,@NumOfAppsDeployed,@EUTEngineeringConsult,@Comments,@TimeSpend,@Complexity,@ECM,(getdate() ) )
~~~~~~~~~~~~~~~~~~~~~~MY TABLE~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE [dbo].[KPITbl](
[TaskID] [int] IDENTITY(1,1) NOT NULL,
[Lead] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WRM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PTDB] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PAR] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RequestingLOB] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOB] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartLocation] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FinishLocation] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProjectType] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerName] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerType] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Responsible] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RAG] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StartDates] [smalldatetime] NULL,
[EndDates] [smalldatetime] NULL,
[TreeorDomainImpacted] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NumOfSites] [int] NULL,
[NumOfUsers] [int] NULL,
[GBdatamoved] [int] NULL,
[GBdatadeleted] [int] NULL,
[NumOfSrvrsAdded] [int] NULL,
[NumOfSrvrsDecommed] [int] NULL,
[NumOfAppsDeployed] [int] NULL,
[EUTEngineeringConsult] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Comments] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TimeSpend] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Complexity] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastUpdated] [smalldatetime] NULL,
[DCOorSTANDALONE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ECM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StatusCompletedDate] [smalldatetime] NULL,
[StatusCancelledDate] [smalldatetime] NULL,
CONSTRAINT [PK_Sheet1a] PRIMARY KEY CLUSTERED
(
[TaskID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
View 4 Replies
View Related
Apr 19, 2008
hi,
i was able to succesfully configure my remote server over the internet and tried using express edition to connect to my remote server and everything when successful.
is there a way to copy that connection string used to connect to my remote server so as i wanted to create a project in vb using that connection string..
where can i find the connection string code used for my connection remotely ..
i am using express to connect to my sql server
thanks
View 4 Replies
View Related
May 15, 2007
Every time I move my project from my computer to the testing server I have to change the connection string references in the aspx side and in my code behind.
For the code behind I declared the SqlConnection string at the top of the code-behind page for both connection strings and comment out the one not in use. Obviously I then comment out the string not in use in the WebConfig as well.
Being superlatively lazy I always look for the easiest and quickest way to do most anything - connection strings included. Having to comment out strings twice brought rise to the question of whether I can refer to the connection string in the web.config file from the code-behind page. I'm sure it can be done, and I did a good amount of hunting around, but I couldn't find any examples of code that would do that.
Currently, as I said above, I have two connection strings declared at the top of my code-behind. Here's an example of one:Private sqlConn As New SqlConnection("Data Source=DATABASESERVER;Initial Catalog=DATABASE;User ID=USER;Password=PASSWORD")
Then, I just use sqlConn as usual in my binding without having to "dim" it in every sub:sdaPersonnel = New SqlDataAdapter(strSqlPersonnel, sqlConn)
Then there's the SqlConnections set up by the wizard on the aspx side:<asp:SqlDataSource ID="sqlDataSourcePayrollCompany" Runat="Server" ConnectionString="<%$ ConnectionStrings:DATABASECONNECTIONSTRING %>" ...>
And for the connection in the web.config:<add name="DATABASECONNECTIONSTRING" connectionString="Data Source=DATABASESERVER;Initial Catalog=DATABASE;User ID=USER;Password=PASSWORD" providerName="System.Data.SqlClient" />
So, what would be the code in the code-behind page to refer to the connection string in the web.config file?
Thanks!
View 2 Replies
View Related
Feb 28, 2008
I have written a CLR function in C# to access a sql server different from the current server where I have deplyed the assembly.
DataSet ds = new DataSet();
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
string connString = "Data Source=SQLSERVER;Initial Catalog=DATABASE;User ID=userid;Password=password;";
SqlConnection sqlConn = new SqlConnection(connString);
sqlCmd.Connection = sqlConn;
sqlConn.Open();
try
{
SqlDataAdapter sda = new SqlDataAdapter();
sqlCmd.CommandText = "StoredProcedureName";
sqlCmd.Parameters.Add("@Param1", SqlDbType.VarChar);
sqlCmd.Parameters["@Param1"].Value = sParam1;
sda.SelectCommand = sqlCmd;
sda.Fill(ds);
}
finally
{
sqlConn.Close();
}
return ds;
I was getting the following error
"Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed."
so I tried doing the following
SqlClientPermission pSql = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
pSql.Assert();
and then I get this error
"Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed."
Any idea how to remedy this ?
Thanks in advance
- Kan
View 4 Replies
View Related
Feb 20, 2008
I have a RPC which gives me multiple - single record rows like
Robert|K|Half|TX|1123823|1423904 -- This is one such record that i get in 1 Column
Now I need to split the above record into 6 Fields and populate in my local DB
I know i could use any .NET Language and accomplish this. But Iam limited to using SSIS/T-SQL Proc's
Any ideas / directions?
View 6 Replies
View Related
Jun 19, 2007
I want to write a query like this:
select ' select count(*) '+ '/*' + table_name + '*/' + char(10) + ' FROM '+table_name...
The result should looks like this:
--------------------------------------------
select count(*) /* ---- emp_name */
FROM emp_name
---------------------------------------------
query result:
COUNT(*)----EMP_NAME
-------------------
3
-------------------------------------------
But, now I can only got
COUNT(*)
-------------------
3
What's wrong with my original code?
View 4 Replies
View Related
Apr 15, 2008
Hi all,
Please help me for the code of connectivity
it was working fine,but,when i hav written code for validation for id n password,its showing error as
Violation of PRIMARY KEY constraint 'PK_Login'. Cannot insert duplicate key in object 'login_detail'. The statement has been terminated.
Here is my code given below.
Please help me
Imports System
Imports System.IO
Imports System.Xml
Imports System.Text
Imports System.Security.Cryptography
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports System.Data
Imports System.Data.SqlClientPublic Class WebForm1
Inherits System.Web.UI.Page#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
End SubProtected WithEvents txtUser As System.Web.UI.WebControls.TextBox
Protected WithEvents txtPwd As System.Web.UI.WebControls.TextBoxProtected WithEvents btnSubmit As System.Web.UI.WebControls.Button
Protected WithEvents lblError As System.Web.UI.WebControls.Label
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End RegionPrivate Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim objConn As SqlClient.SqlConnection
Dim ds As New DataSetDim m_strConnection As String = "server=172.16.152.17;Database=Trial;UID=abhi_Asset4;PWD=L!nux001;Connect Timeout=100"objConn = New SqlClient.SqlConnection
objConn.ConnectionString = m_strConnection
objConn.Open()
Dim strSQL As String
strSQL = "insert into login_detail(UserID,Password) values('" + txtUser.Text + "','" + txtPwd.Text + "')"Dim objCommand As SqlClient.SqlCommandobjCommand = New SqlClient.SqlCommand(strSQL, objConn)
objCommand.CommandText = strSQL
objCommand.ExecuteNonQuery()
objConn.Close()
End SubPrivate Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
If (txtUser.Text.Trim() = "") Then
lblError.Text = "UserID should not be blank"
ElseIf (Convert.ToInt32(txtUser.Text.Length) < 5) Then
lblError.Text = "UserID length must be more than 5"
ElseIf (Convert.ToInt32(txtUser.Text.Length) > 20) Then
lblError.Text = "UserID length must not be greater than 20"
ElseIf (txtPwd.Text.Trim() = "") Then
lblError.Text = "Password should not be blank"
ElseIf (Convert.ToInt32(txtPwd.Text.Length) < 5) Then
lblError.Text = "Password length must be more than 5"
ElseIf (Convert.ToInt32(txtPwd.Text.Length) > 20) Then
lblError.Text = "Password length must not be greater than 20"
Else
Server.Transfer("Success.aspx")
End IfEnd Sub
End Class
View 1 Replies
View Related
Oct 11, 2006
I'm trying to create a form that allows someone to find an
address from a postal code search in the database. My query works exactly as I’d
like in query builder using the following select:
Select [FIELD_LIST] from addresses WHETE POSTCODE LIKE ‘%’+@POSTCODE+’%’
I then pass the user entered post code to the select statement
which is executed. However, I’m getting some odd behaviour. Assuming there is
an address in the database with the post code
“LS11 0ES”...
If I search for LS11, nothing is returned;
If I search for LS11%, nothing is returned;
If I search for %LS11%, nothing is returned;
If I search for %LS11%%, the data is returned.
If I search for %LS%1%%, the data is returned (as is LS21
0ES etc. etc.).
However, I want the user to be able to enter shorter search
strings and it to pull all the data back out, so they can enter a substring
such as LS and it will pull out all the data without the users needing to enter
the full pattern of % symbols.
If I go into query builder (in visual web developer) and
enter just “LS” it works as I’d want, but not when pulled from a web page.
Any ideas?
Thanks
View 3 Replies
View Related
Jan 26, 2008
Hello i constructed the following query in SQL management studio and it runs fine, exactly how i want it to. However when i run it in my code behind i get the error: 'Too many characters in character literal'
I desperatly need to use this query exactly how it displays in the mangement tool - why wont it work the same in my code behind?
(I am using c# in my code behind and binding the results of the query to a gridview)
Regards
"SELECT DISTINCT (dbo.AuthorList(publicationID))+ ';' + ISNULL(' Editor: '+Publication.editor, '')+ '' + ISNULL(' "'+Publication.title+'"', '')+ '' + ISNULL(' Book: "'+Publication.booktitle+'"', '')+ '' + ISNULL(' '+Publication.type, '')+ '' + ISNULL(' '+Publication.journal, '')+ '' + ISNULL(' Series: '+Publication.series+'.', '')+ '' + ISNULL(' '+Publication.school, '')+ '' + ISNULL(' '+Publication.institution, '')+ '' + ISNULL(' '+Publication.organisation, '')+ '' + ISNULL(' '+Publication.month, '')+ '' + ISNULL(' ('+convert(nvarchar, Publication.year)+')', '')+ '' + ISNULL(' '+Publication.publisher, '')+ '' + ISNULL(' '+Publication.address+'.', '')+ '' + ISNULL(' '+Publication.howpublished, '')+ '' + ISNULL(' Ed: '+convert(nvarchar, Publication.edition)+'.', '')+ '' + ISNULL(' '+Publication.note, '')+ '' + ISNULL(' Vol:'+Publication.volume+'.', '')+ '' + ISNULL(' No:'+Publication.number+'.', '')+ '' + ISNULL(' Pages:'+Publication.pages+'.', '')+ '' + ISNULL(' Chapter:'+Publication.chapter+'.', '') AS SingleColumn, Publication.publicationID FROM Publication;"
View 1 Replies
View Related
May 14, 2002
I have coded an alter statement for adding a column to a temp table inside an sp,but it skips the alter statement while executing the sp! This happens even if I run the same code on query analyzer too. If I use 'GO' statement before the alter command,then it works fine on Query Analyzer.But, I can't use 'GO' in an sp. I am using the sa account. Any ideas on how to fix this??
Thanks.
Di.
View 1 Replies
View Related
Sep 24, 2001
BREAK THE DATE TIME FUNCTION DOWN INTO MINUTES...HELP!
View 1 Replies
View Related
Aug 1, 2014
I wanted to determine the date of the last Saturday with respect to the current date so that I could create a temp table with date ranges numbered 1-4 e.g. up to that date, post link below: URL....I now want to make this function that any time I pass it a day name (mon-sun) it will calculate the date of the previous (mon-sun) with respect to the current day. This date will then be the last date in my range.
So for example I run the function and pass it 'Friday' then I want last date in my range to be the 25th of July (today being Friday so need last Friday), I pass it Thursday I want it to give me the 31st of July etc. I need last date of that date but if I call Friday and today is Friday then need last friday; I think this is the trickier part of it.
This calculation I would image would need to use a combination of datediff dateadd etc. but really don't know how to do this dynamically.Currently I have a function that I pass the last date for the date range I want for my weeks range, I also give it the numbere of weeks I want in my range and also pass the date from the query that I want to know what week range it is in.
ALTER FUNCTION [dbo].[daterangeweeknumber]
(
-- Add the parameters for the function here
@refDate datetime,
@Weeks INT,
@lastdate datetime
]code]....
But now I want just to be able to pass it the day name - in this instance would be 'Wednesday' and want it to return date ranges with last date as Wed the 30th of July. Reason I want to use day name is that people will be running these reports on a certain day - for instance on Wednesday and last day to be Monday - and this will mean they do not have to keep changing the date in the call to function.
View 2 Replies
View Related
Sep 8, 2015
I am querying with a SELECT statement against an address table that has a post code column with corrupt data.
Sample record:- Northants NN4 0NB
Should be NN4 0NB
I have used the following on another column:-
LEFT(A.Addr1,CASE WHEN CHARINDEX ('(', A.Addr1) =0 THEN LEN(A.Addr1) ELSE CHARINDEX('(' ,A.Addr1) -1 END) AS 'Address 1'
but unable to correct this problem?
View 9 Replies
View Related
Nov 27, 2006
Hi you all,
In abc.aspx, I use a GridView and a SqlDataSource with a SelectCommand. The GridView's DataSourceID is the SqlDataSource.
In abc.aspx.cs, I would like to use an IF statement in which if a criterion is not satistied then I will use the SqlDataSource with another SelectCommand string. Unfortunately, I have yet to know how to write code lines in order to do that with the SqlDataSource. Plz help me out!
View 1 Replies
View Related
Feb 20, 2004
hi
have a problem ...
I tried to test my statement in QA as it returns no dataset ,it gives me empty columns in return,I though it will return all the columns and some records of (lmeyer) as the current user ,who logged in on windows auth,but it return only the columns,is that means my query analyser cannot read the login1 ,column which has the username as (lmeyer)
select * from tstudents where (login1='@param1')
if not what could me wrong in my code ,because I get no response to the sql server
Public Function login(ByVal login1 as string) as dataset
Dim myconnection as new sqlconnection("server=G103-TT03;database=CampusLANDB;Trusted_Connection=yes")
dim mycommand as new sqlcommand ("Select * from tStudents Where login1 = @param1",myconnection)
mycommand.parameters.add(new SQLClient.SqlParameter("@param1",login1))
dim DS as new dataset()
try
myconnection.open
dim adpt as new sqldataadapter
adpt.selectcommand = mycommand
adpt.fill(DS,"tStudents")
catch ex as exception
throw new exception(ex.message)
return nothing
finally
myconnection.close
end try
try
mydatagrid.datasource=DS.Tables("tStudents")
mydatagrid.databind()
catch ex as exception
errorlabel.text=ex.message
end try
return DS
End Function
thankx in advance
pamela mkosana
View 12 Replies
View Related
Oct 24, 2007
I am new to SQL; the following code is supposed find comments that are not null on one day before the studydate.
SELECTp.PatientID,P.LastName,P.FirstName,P.BirthTime,s.StudyDate,s.Modality,s.Comments
FROMStudies s INNER JOIN Patients p
ON s.PatientID = p.PatientID
WHEREs.Comments IS NOT NULL
AND s.StudyDate >= CONVERT(char,DATEADD(day, -12, GETDATE()),101)
AND s.StudyDate < CONVERT(char,DATEADD(day, -11, GETDATE()),101)
Is there a problem with this code? I expect there is because when I ran the query it didn't return any results even though I am positive there are comments in the database one day before the studydates.
Any help would be greatly appreciated
Thanks a ton,
Regards
-avery
View 2 Replies
View Related
May 19, 2008
Hello All,i have a sql database with a table as follows. CustID (PK, Varchar(50) not null )Address ( VarChar(max), null)City ( VarChar(max), null)State( VarChar(max), null)Zip ( VarChar(5), null) ----------------------------- this is not working cuts off the leading zero like ( 01234) is shown as 1234DateShipped( datetime, null) --------------------- this display 05/18/2008 12:00AM. i just want to display (05/18/08).how can we do the above two. any suggestions please. i tried smalldatetime no luck . i appreciate it.Thanks.
View 3 Replies
View Related
Feb 10, 2006
I am trying to understand creating SQL Server projects and managed code. So I created a C# SQL Server Database project and named it "CSharpSqlServerProject1" and followed the steps in the following "How to: " from the Help files:
"How to: Create and Run a CLR SQL Server Stored Procedure "
I used the exact code in this "How to: " for creating a SQL Server managed code stored procedure (see below) in C#. However it didn't even compile! When I went to build the code I got the following error message:
"Error 1 Target string size is too small to represent the XML instance CSharpSqlServerProject1"
It does not give a line number or any further information! Since this is a Microsoft example I'm following I figure others must have run into this too. I can't figure out how to fix it!
Here's the code as copied directly from the howto:
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[SqlProcedure()]
public static void InsertCurrency_CS(
SqlString currencyCode, SqlString name)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlCommand InsertCurrencyCommand = new SqlCommand();
InsertCurrencyCommand.CommandText =
"insert Sales.Currency (CurrencyCode, Name, ModifiedDate)" +
" values('" + currencyCode.ToString() +
"', '" + name.ToString() +
"', '" + System.DateTime.Now.ToString() + "')";
InsertCurrencyCommand.Connection = conn;
conn.Open();
InsertCurrencyCommand.ExecuteNonQuery();
conn.Close();
}
}
}
Thanks for any help you can give!
View 7 Replies
View Related
Apr 8, 2006
In Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB
I created the following parameter:Dim strName As String Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50) parameterBName.Value = strName myCommand.Parameters.Add(parameterBName)
I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER "
error is:Line 1: Incorrect syntax near 'V'. 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 'V'.
Source Error:
Line 59: Line 60: Line 61: myCommand.ExecuteNonQuery() 'Execute the query
View 2 Replies
View Related
Jul 24, 2007
How can I get the UserID to pass to the sql statement? The date is being passed but nothing else
Thank you.
My code:Public Function GetEmployeeByID(ByVal Today As Date, ByVal UserID As String) As SqlDataReaderDim dtToday As DateTime
dtToday = DateTime.Today
'Create connectionDim con As New SqlConnection(_connectionString)
'Create commandDim cmd As SqlCommand = New SqlCommand()With cmd
.Connection = con
.CommandText = "SELECT UserID FROM ATTTblAttendance WHERE UserID = @UserID And Today = " & dtToday" -->The UserID no showing up here
.CommandType = CommandType.Text
'Add Parameters.Parameters.AddWithValue("@UserID", UserID)
.Parameters.AddWithValue("@Today", Today)
End With
'Return DataReader
con.Open()Return cmd.ExecuteReader()
End Function
View 15 Replies
View Related
Apr 16, 2008
I'm trying to add a 'change password' control to my site and seem to be having some issues. I have code that works if I statically define what user is displayed on the form, but I cant get it to detect the 'authenticated' user and show them the reset for for that ID.If I take the "+ myid" out of the select statement and just define the username statically the form works properly. Error:System.Data.SqlClient.SqlException: The column prefix
'System.Security.Principal' does not match with a table name or alias name used
in the query. Here's a piece of the code that is supposed to detect the current logged in user. However, it gives the error. (some of the code may be redundant but its not causing issues that I can tell) public void InitPage() { IPrincipal p = HttpContext.Current.User; String myid = HttpContext.Current.User.ToString(); SqlServer sqlServer = new SqlServer(Util.SqlConnectionString()); DataTable dt; SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["myconnection"].ConnectionString); SqlDataAdapter cmd1 = new SqlDataAdapter("select * from USER WHERE USER_NAME = "+ myid, cnn); DataTable UIDtable = new DataTable(); cmd1.Fill(UIDtable); User_Id.Value = UIDtable.Rows[0]["ID"].ToString(); dt = sqlServer.USER_SELECT(Util.SiteURL(Request.QueryString["Pg"].ToString()), User_Id.Value);
View 1 Replies
View Related
Jul 14, 2003
Hi:
the following is my TSQL command of a job(after passing databaseName, I don't want multiple steps, just with 1 step)
--===================================
BACKUP DATABASE ABC To Backup_ABC_1 with init, name = 'Backup of 1(M, W, F ) Job_ABC_1Mon' go backup log ABC with truncate_only go print 'shrinkdatabase begin-- ' dbcc checkdb(ABC) go dbcc shrinkdatabase(ABC, 10)
Issue: with 3 'go' inside the whole line, execution failed. after replace 'go' with 'begin' and 'end' for each sql statemet, the execution works. Someone could explain why 'go' as a batch does not work in this situation?
also, is this a good practice to
1. truncate_only
2. dbcc checkdb
3. shrinkdatabase
after a daily backup?
thanks
David
View 5 Replies
View Related
May 3, 2005
I have a table called evidence, which has the following Fields
| evidence_id | Description| Standards|
E001 blagh 1.1,1.2,1.3
Ok I am trying to search the comma-separated string in the standards field using the like clause so I can display the evidence_id.
SQL looks like
SELECT Evidence.Standards, *
FROM Evidence
WHERE (((Evidence.Standards) Like '%1.1%'));
However it will not search through the list and select for example if I change 1.1 to 1.2. The commas wont allow it.
It works if I just have one item in the list that is just 1.1. Can anyone help me to search a comma-separated string for a certain string?
Thanks
Asylum
View 3 Replies
View Related
