Several textboxes are being on a web form for date entry. and we want to allow the user to leave date fields blank (if particular date is unknown). However, when the field is left blank, the SQL Server 2000 database defaults to 1/1/1900 (datatype = datetime).My question is as follows: How is it possible to leave a date textbox field blank, and either send 00 0 000 or spaces to the SQL Server 2000 database datetime field?Any advice/insight is appreciated! Rob
Hi, I am doing a report in which i have to create Wall chart kind of layout and for that i have to place Text Boxes on the report at run time.Now In my Layout i have some Text Boxes which are going Out of the page(Starts from page 1 and Ending on page 2)but when i am looking my report in the Print Preview the Report Viewer is shifting the entire Text Box to Page 2. Can i do something to print the report as they are seen in the Normal layout.
I would like a form that has 3 text boxes. These 3 boxes are going to be used for entering search criteria. The first box is for searching a field called CasePK. The second is for searching a field called LinePK and the third is for searching a field called DOS. DOS is a date field. The other two are text fields.I am using a SQLDATASOURCE and a Gridview. What will the SelectCommand look like so that I can search the 3 fields using the data from the textboxes?
I have three text boxes firstname, lastname, dob. It is set up to do a like search on the the text boxes. If a user wants to do a search for just the last name it will not work. You must type in something in all three boexes. Doesn't anybody know how to correct this. Here is the code. Thanks.
<%@ 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 Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load GridView1.Visible = False GridView2.Visible = False End Sub Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click GridView1.DataSourceID = "SqlDataSource1" GridView1.DataBind() GridView2.DataSourceID = Nothing GridView2.DataBind() GridView1.Visible = True End Sub Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click GridView2.DataSourceID = "SqlDataSource2" GridView2.DataBind() GridView1.DataSourceID = Nothing GridView1.DataBind() GridView2.Visible = True End Sub
Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>Meditech Radiology Numbers</title> <link href="StyleSheet.css" rel="stylesheet" type="text/css" /> </head> <body> <form id="form1" runat="server"> <div id="master_headertop"><asp:HyperLink ID="HomePageLink" runat="server" ImageUrl="~/images/headertop_img.jpg" /></div>
Hi,Hope if someone can help me here. Keep in mind I an fairly new to .NET and SQL and am learning to break my MS Access habit :) I have a web form that is using a SqlDataSource and a FormView control. In addition to this I have 2 text boxes. What I am trying to do is display results in the FormView based on what a user types into one of the Text Boxes (one or the other…Not both)
The SELECT statement in the SqlDataSource looks like this in concept. SELECT Field1, Field2, Field3, Field4FROM dbo.MYTABLEWHERE (Field1 = @Field1) AND (Field2 IS NULL)OR (Field2 = @Field2) AND (Field1 IS NULL)
I have the two text boxes pointing at the parameters (@Field1 and @Field2) so in theory I would expect that when a user populates one of the text boxes and clicks a button to databind the FormView it would display a record matching that criteria…. But it’s not all I get is a blank/missing FormView. I tried different variations on the SQL statement and tried using = '' instead of IS NULL but still the same results. However, if I populate one text box with a value that I know is not in my table and populate the other with a value of which I know exists in my table is…It works.What am I missing?
how visibility can be toggled between two textboxes in ssrs? For example if textbox1 is visible and textbox2 is hidden initially and textbox2 should be visible when clicked on textbox1 and textbox1 should go invisible when textbox2 is shown... and then again when clicked on textbox2, textbox1 should be visible and textbox2 should go invisible.In a short, it is like switching visibility between 2 textboxes... A click on first textbox should display the second textbox and another click on second text box should display first text box.I cannot provide parameter as it is not there in the requirement. how to toggle the text within a single text box to fulfill above requirement...
Hi folk.. can some body help me with this problem..???
I have a grid view on a form which has got colums which are set as data combo boxes. and a third column whish is set to a text box. all the 3 controls on the datagrid view are bound with sql server table. they are asociated with two saparate tables within 1 database... now what i want is that the user of the application can select either combo box 1 of teh 2nd combo box. and automatically the syncronize and also the text column asociates with related data..
for example
Country City Pincode India vasco 403802
now while flling the grid vew.. the user can select either country or city.. automatically both syncronize with related data and so does the text box.
I have a user table with Label and value fields where i would like to control the display of the text boxes based on the values selected in my user table.Can we adjust the text box positions dynamically based on the user table values.
Ex: Table
Label1 Field1 Label2 Field2 Label3 Field3 ID 100 Dept Sales
Report Design :
Label1: ID Field1:100 Label2: Field2: Label3:Dept Field3:Sales
Expected Result :
Label1: ID Field1:100 Label3:Dept Field3:Sales
In my table i don't have values for Label 2 & Field 2 , can we adjust the spacing conditionally to be utilized by Label3 & Field3 in SSRS.
Note : Above mentioned data is just for an example and in my actual report i can have more than 3 columns and report is looking ugly with all the spacing if i don't have data in all the fields.
when inserting or updating fields in sql server and the field is blank sql still adds several spaces into the field. Then if I try to check whether a field is empty or not for display I get incorrect results because fields that should be empty have 2 or 3 spaces in them instead and therefor aren't selected as being empty. Is there any way around this? to force the fields to be saved as blanks or null?
I thought this was working, but apparently it was not. I was wondering how I would create a SELECT Statement for values that are blank (Equal to ""). I really could have swore that this was working, but I guess it wasn't.
// create an open the connection OleDbConnection conn = new OleDbConnection(conString); OleDbCommand command = new OleDbCommand(); command = conn.CreateCommand();
// create the DataSet DataSet ds = new DataSet();
// run the query command.CommandText = "SELECT ID AS [#], Company AS [Company], FName AS [First Name], LName AS [Last Name], Type AS [Type] FROM Table1 WHERE Tags = @P0;"; OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter = new OleDbDataAdapter(command); command.Parameters.Add("@P0", OleDbType.VarChar).Value = ""; adapter.Fill(ds);
// close the connection conn.Close();
bindingSource1.DataSource = ds.Tables[0];
DataGridView.DataSource = bindingSource1;
// set the size of the dataGridView Columns this.DataGridView.Columns[0].Visible = false; this.DataGridView.Columns[1].Width = 234; this.DataGridView.Columns[2].Width = 50; this.DataGridView.Columns[3].Width = 50; this.DataGridView.Columns[4].Width = 75;
//Sort on the Title Column DataGridViewColumn sortColumn = DataGridView.Columns[1]; ListSortDirection direction; direction = ListSortDirection.Ascending; DataGridView.Sort(sortColumn, direction);
//Set the Selected Property of the First Row to False DataGridView.Rows[0].Selected = false; } catch { }
I am trying to validate and import a Excel file into the database table using script component. The file contains some blank columns in the sheet. How can I handle the blank spaces while validating the file in the Script Component?The code is as follows:Dim excelcmd As OleDbCommand = New OleDbCommand("SELECT Item,TaxCode,ItemDescription FROM [Input$]WHERE LEN(Item)>=0 AND LEN(ItemDescription)>=0 AND LEN(TaxCode)>=0", excelConn) Dim excelreader As OleDbDataReader = excelcmd.ExecuteReader() Dim row As Integer = 0 While excelreader.Read() NameValsBuffer.AddRow() NameValsBuffer.ItemCode = CStr(IIf(excelreader.GetString(0).Length = 0, "#", excelreader.GetString(0))) NameValsBuffer.TaxCode = CStr(IIf(excelreader.GetString(1).Length = 0, "#", excelreader.GetString(1))) NameValsBuffer.ItemDescription = CStr(IIf(excelreader.GetString(2).Length = 0, "#", excelreader.GetString(2))) NameValsBuffer.CompanyId = Me.Variables.CompanyId NameValsBuffer.UserId = Me.Variables.UserId End While
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
Hi, i'm trying to do a full text search on my site to add a weighting score to my results. I have the following database structure: Documents: - DocumentID (int, PK) - Title (varchar) - Content (text) - CategoryID (int, FK) Categories: - CategoryID (int, PK) - CategoryName (varchar) I need to create a full text index which searches the Title, Content and CategoryName fields. I figured since i needed to search the CategoryName field i would create an indexed view. I tried to execute the following query: CREATE VIEW vw_DocumentsWITH SCHEMABINDING ASSELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryNameFROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID GOCREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndexON vw_Documents(DocumentID) But this gave me the error: Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns. I tried converting the Content to a varchar(max) within my view but it still didn't like. Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.
I'm in a bit of a jam here and will appreciate any help.
I need the SQL code to replace a record if the record is empty.
For instance, I have about 7 columns containing over 40K records. In the firstname field, some records are blank. I need to replace all the blank firstname fields with this: 'now invalid' (without the quotes)
Originally, I hit this problem with PHP. But then I was able to reproduce using VBScript.
I have a table with a couple of text fields, and one Real field in a table. One row of data in the table. Both text fields set, but the real field doesn't even need to have a value set.
Connecting to SQL 2005 using a DSN - ODBC.
I'm using a stored procedure I named ExecIt that runs a SQL statement that is sent in and returns records.
If I use cursor location adUseClient, it returns values for the text fields.
If I use asUseServer, it returns blank values.
If I use a SQL statement directly, it works. Only fails when using SQL statement by way of ExecIt.
In each case, cursor type is adOpenStatic or adOpenDynamic.
Using this combination fo cursor type and cursor location, it mimics what I see in SQL profiler when PHP is using ODBC.
I have followed many tutorials on selecting and replacing text in text fields, varchar fields and char fields, but I have yet to find a single script that will to all 3 based on field type. Let's assume for a moment that I don't know where all in my database a certain value that I need changed resides ... i.e., the data's tablename and fieldname. How would I go about doing the following ... or more importantly, is this even possible in a SQL only procedure?1) Loop over entire database and get all user tables2) Loop over all user tables and get all fields3) Loop over all fields and determine the field type4) switch between field types and change a string of text from 'a' to 'b'Please be gentle, I'm a procedure newb.
I am trying to bcp import a text file into a SQL Server 2000 database.The text file is coming out of a java application where orderinformation is written to the text file. Each record is on it's ownrow, so the last item in each record has a new line character at theend of it to create the next row. This works well in creating the filehowever bcp does not like to import this text file with the extra blankline at the end. If I change the new line character to the beginning ofthe records then there is a blank line at the top of the text file,which bcp also does not like. Does anyone have any suggestions for meto get around this issue?Thanks,
I have a data table in my project that has a "text" (not varchar) field in it. I am trying to load this field with little paragraphs of text for showing on my pages. How do I get the free text loaded into the table? The database explorer and all the data grid controls cut the text off at one line. There doesn't seem to be any way to get multiline text into this table.
i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:
Code:
IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))
Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table
but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"
i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:
IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))
Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table
but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"
Is it possible to enable parameter of type date to be blank (not null)?
I created parameter of type date, gave it no default value, when I pressed the preview tab I got error message "The property 'DefaultValue' of report parameter 'DateParamName' doesn't have the expected type" Thanks in advance!
Since migrating Reporting Services from SQL 2005 to SQL 2008, the Last Run date, visible on the Details view of reports in a folder under Report Manager, has been blank. I would hope that this is supposed to be populated and equivalent to the "When Run" date visible in the 2005 version of Report Manager. Any setting that needs to be configured in order for this to show, or if this has been reported as a bug?
Hi every one, I am facing problem in printing the reports from browser and also when i export it to pdf,the problem i am facing is blank pages are coming when report column getting the large amount of text around 2500 characters into column value. can any one help me in this issue?. if the report is getting acceptable amout of data it is printing in proper way i.e no balnk pages at all.i maintained all properties like margins+body size < page size.
Hi, Basically the above is a very common requirement, please comment on my solution which I've arrived at by searching through the web; -
In summary I have used 3 SSIS components these are "Flat File Source", "Derived Column" and "SQL Server Destination".
1) File Connections Manager Editor 1.1) Within File Connections Manager Editor; - Name the data type e.g. "INTERCHANGE_NET_APP_DATE_SRC" and assign a type to the data type e.g. string[DT_STR]
1.2) Click on the Preview button to ensure the expected text is assigned to the expected data type.
2.4) Select "database timestamp [DT_DBTIMESTAMP] " as Data Type.
2.5) Within the Mappings tab of the SQL Destination Editor have; - Input Column as INTERCHANGE_NET_APP_DATE and Destination Column as INTERCHANGE_NET_APP_DATE.
Please comment on the above, I will then pass on my suggestion to Microsoft.
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
using SQL 6.5 sp_makewebtask in a stored procedure I want to create a press-info file. My query calls four fields, a datum field, two varchar fields and a text field, it's the field "PrText" in my example:
EXEC sp_makewebtask @outputfile='F:EntwicklungRedaktionZeitwenden27.ht m', @query='SELECT Datum, Ort, Headline, PrText FROM news WHERE ID = 27', @templatefile='F:EntwicklungRedaktionZeitwendenTem plates.htm'
My question: Am I limited to 255 characters in this case? After 255 characters the text is cut off and I have only a truncated text fragment.
Is there a possibility to get the whole text without using @blobfmt (I don't want to use this parameter because I want to have the datum data, the varchar headline AND the text in ONE HTML-file.)
I have tried the same as dynamic query in ASP where I have no problem to get the whole content of the text field, but I want to create a static file because this information will be read often, but won't be updated frequently and I don't want to charge the server resources to much.
Has anybody an idea? Any help would be appreciated.
The Text datatype can hold several million characters The size of a record can be no more than 8 KB in normal data.
Created a table with one text field. For some reason can only store 1K of data into it. Even if the 8K limit was a factor (which it shouldn't for a text field) I cannot add more than 1K of data into my text field. 1023 characters to be exact.
Why is SQL Server 2000 doing this and more importantly how to resolve?
