My form has an optional field for date entry. If user did not enter anything then how can I still maintain null value in the table in an update operation? Thanks for advice.
View 14 RepliesI'm entering a Selection record for a partiuclar lotID,
Once entered, I need to obtain its SelectionID then use it to update a another field within that record.
Here's what I've been doing...
--insert values into a testchangeorders table
INSERT INTO testchangeorders VALUES (2,3,3,3,1,'red',0,5)
--Find the SelectionsID of the last record created for that partiuclar LotID
SELECT MAX (SelectionsID)
FROM testchangeorders
WHERE LotID = 2
--Once located, I was trying to update a field called uniqueID with a contantination of '3-' & the record's SelectionsID
UPDATE testchangeorders
SET UniqueID = ('3-' & SelectionID
WHERE SelectionsID = SELECT MAX (SelectionsID) AND LotID = 2)
I have 2 SQL server 2000 machines, I need to take a table from each one and combine them together based on a date time stamp. The first machine has a database that records information based on an event it is given a timestamp the value of variable is stored and a few other fields are stored in Table A. The second machine Table B has test data entered in a lab scenario. This is a manufacturing facility so the Table A data is recorded by means of a third party software. Whenever a sample is taken in the plant the event for Table A is triggered and recorded in the table. The test data may be entered on that sample in Table B several hours later the lab technician records the time that the sample was taken in Table B but it is not exact to match with the timestamp in Table A. I need to combine each of these tables into a new SQL server 2005 database on a new machine. After combining the tables which I am assuming I can based on a query that looks at the timestamp on both Tables A & B and match the rows up based on the closest timestamp. I need to continuously update these tables with the new data as it comes in. I havent worked with SQL for a couple of years and have looked at several ways to complete this task but havent had much luck. I have researched linked servers, SSIS, etc Any help would be greatly appreciated.
View 10 Replies View RelatedDear All,
What's the most efficient way of joining a 1 to many relation, where a record in table A will have multiple records in table B.
I'd like to select every record in table A but only joining the last relevant record from table B. So:
Table A:
A1 Prj1
A2 Prj2
Table B:
B1 A1 23/12/2005
B2 A1 26/12/2005
B3 A1 2/1/2007
B4 A2 25/12/2006
B5 A2 1/1/2007
So I'd like to list using the most efficient way this:
A1 Prj1 B3 2/1/2007
A2 Prj2 B5 1/1/2007
I'm assuming this is NOT the most efficient way:
select A, (select top 1 date from B orderBy ...)
Any suggestions?
I have a table where the designer allowed time reporting for 8 different types of activity on each timecard, then stored the hours by activity in 8 separate columns in the database. Example:
Table A.
Column Headers: Employee|Date|Phase1Hours|Phase2Hours, etc...
Data: Fred Jones| 7/15/13 |3|3, etc...
The problem is there is no way based on this structure to get an employee's hours for the day in columnar form.
To get this data into columnar form I have used Select queries with Union All, for instance:
SELECT Employee, Date, Phase1
FROM Table A
UNION ALL
SELECT Employee, Date, Phase2
FROM Table A
,etc.....
Query is running slow (I am guessing it is because the same table is repeatedly being accessed).
I need to have a script where it ask the user for a value, the script will search for all records that match the value. Then it will display the numbers of records found and ask the user to enter a different value. The rest of the script will use this new value and increment by 1 n times as the number of records found. I started the script where it will ask for "HANDLE" and display the number of records found with that "HANDLE"
declare @HANDLE as varchar(30)
declare @COUNT as varchar(10)
declare @STARTINV as varchar(20)
set @HANDLE = ?C --This is the parameter to search for records with this value
set @STARTINV = ?C --User will input the starting invoice number
SELECT COUNT as OrderCount FROM SHIPHIST
where HANDLE = @HANDLE
I just can't figure out how to proceed to use the entered invoice # and increment by 1 until it reach the number of records found.
This will be the end results:
Count=5 --results from query
STARTINV=00010 --Value entered by user
Handle,Inv_Num
AAABBB,00010
AAABBB,00011
AAABBB,00012
AAABBB,00013
AAABBB,00014
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
View 3 Replies View Related************* Edited by moderator Adec ***************
Inserted missing < code></ code> tags (without the
spaces inside). Always include such tags when
including code in your postings. Don't force the
moderators to do this for you. Many readers disregard
postings without the code tags.
**************************************************
Hi
Probably a dumb question but I'm doing an insert into a table with an identity field. How do I get the key back straight after I add it?
This is how I do the addition
SQLString = "Insert into users (username,password,status,campus, ulevel ) values ( @username, @password, @status, @campus, @ulevel)"
cmdInsert = new SQLCommand(SQLString, conn)
cmdInsert.Parameters.Add( "@username", _username)
cmdInsert.Parameters.Add( "@password", md5Hasher.ComputeHash(encoder.GetBytes(_password)))
cmdInsert.Parameters.Add( "@status", _status)
cmdInsert.Parameters.Add( "@campus", _campus)
cmdInsert.Parameters.Add( "@ulevel", _ulevel )
conn.Open()
cmdInsert.ExecuteNonQuery()
conn.close()
but about now I need the primary key just generated.
Any ideas?
Thanks
ABold
Is there a way to get the last ROWGUIDCOL entered into a table? I don't believe that doing a SELECT MAX will work, and we really need a way to do this. Does anyone have a nifty way to do this?
TIA...
im trying to summarize all records except the last one entered.
ex
-column001-
1
2
3
4
5
6
the output should be "15" (1+2+3+4+5) is this possible?
One thing i tryed is to put in autodate and time and then count them and leaveout the newest one. Well i can't make it work...
Thx for all help
Hi every one
I want to get the currently entered or updated record in the database table by using SQL Query or stored procedure.
Thanx in advance
Take care
Bye
Someone entered a lot of incorrect data into our SQL 2000 database. Isthere any way to determine who made the changes?Thanks in advance.
View 1 Replies View RelatedHai ,I created a table with primary key clustered. I have entered the datathru E.Manager . If a close the table and open it again , Ii shows therows with the (default) ascending order. Is, there any way to get therows in the user entered order(neither asc or dec order)With ThanksRaghu
View 2 Replies View RelatedMy database has many table, each table has a DateEntered (datetime), EnteredBy (nvarchar(50), LastUpdate (datetime), and LastUpdateBy (nvarachar(50). Is there an easy (ha) way to pull a list of the records that were entered and/or updated for a date range. Hopefully without a select for each table.
Maybe a tool someone knows of?
Hi
I have the problem that the below defined paramter gets entered in the database as a interger. the Field in the DB is a nvarchar(5) and the controll that suplies the value is a TextBox
this is the parameter definition:<asp:ControlParameter ControlID="tbComment" Name="Comment" PropertyName="Text" Type="String" />
Why do I get this error, why does ASP to whant to make an integerfrom this text field? When putting a interger value in the textbox all works well and the data gets posted to the database.
I use a SqlDataSource with automatic generated script.
look forwart to a solution
walter
Hello vmrocha,
Our records indicate that you have never posted to our site before. We hope you find the help you need.
If you need to make a post, we're always happy to help.
I have a VB.NET program that displays the time extracted from a SQL Server database datetime datatype by way of a User-defined scalar function I created. However, sometimes information is entered into the system through the program that does not have a time-- only a date. SQL Server automatically assigns a time of 12:00 AM to these values (since they're a datetime). Is there any way to detect when this happens in my user-defined scalar function so that when I try to extract time values, I can instead return a message/time of my choice? I would rather not assume that all 12:00 AM values are automatically inserted by SQL Server since this might not actually be the case.
View 4 Replies View RelatedI have a procedure that allows a user to enter two dates to run a query. I would like to display those dates on the generated report underneath the title. "5-01-07 to 5-31-07" How would I do this?
View 3 Replies View RelatedHi Guys,
I'm having a problem with some data in our database, basically a web app is storing data into the DB and then recalling it to display to a user. The problem I am having is that for one particular function the DB is causing the app to fail, the app code works for 95% of the data inserted into the DB by the users but it is failing on a few records.. I'v gone through the data manually checking for funny characters or spaces or anything else which is different from the other records, but everything seems to be in order. I doubt very much that this is a system app code problem as the code is working perfectly for all the other records...
Can anyone advise me on what else I can check.. really stuck on this one guys
Thanks Gurj
Hi,
I need to be able to prevent an invalid character from being entered into a sql 2000 databae on import from oracle.
In short, I need to exclude a certain character from being entered and need to be able to send an email which specifies that an attempt was made to enter this character, if the change was due to an insert or an update, the row to be affected in the target database, date and time info. Also the source of the data.
If this is not possible, is it viable to remove the character after insert and still send the email with the required info?
Any one any ideas?
Thanks
Hi,
I need to be able to prevent an invalid character from being entered into a sql 2000 databae on import from oracle.
In short, I need to exclude a certain character from being entered and need to be able to send an email which specifies that an attempt was made to enter this character, if the change was due to an insert or an update, the row to be affected in the target database, date and time info. Also the source of the data.
If this is not possible, is it viable to remove the character after insert and still send the email withe the required info?
Any one any ideas on the cleanest way to achieve this?
Thanks
Hi SQL fans,I realized that I often encounter the same situation in a relationdatabase context, where I really don't know what to do. Here is anexample, where I have 2 tables as follow:__________________________________________ | PortfolioTitle|| Portfolio |+----------------------------------------++-----------------------------+ | tfolio_id (int)|| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)|| folio_name (varchar) | | tfolio_idtitle (int)|--FK----PK->>[ Titles]+-----------------------------+ | tfolio_weight(decimal(6,5)) |+-----------------------------------------+Note that I also have a "Titles" tables (hence the tfolio_idtitlelink).My problem is : When I update a portfolio, I must update all theassociated titles in it. That means that titles can be either removedfrom the portfolio (a folio does not support the title anymore), addedto it (a new title is supported by the folio) or simply updated (atitle stays in the portfolio, but has its weight changed)For example, if the portfolio #2 would contain :[ PortfolioTitle ]id | idFolio | idTitre | poids1 2 1 102 2 2 203 2 3 30and I must update the PortfolioTitle based on these values :idFolio | idTitre | poids2 2 202 3 352 4 40then I should1 ) remove the title #1 from the folio by deleting its entry in thePortfolioTitle table2 ) update the title #2 (weight from 30 to 35)3 ) add the title #4 to the folioFor now, the only way I've found to do this is delete all the entriesof the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =2), and then insert new values for each entry based on the new givenvalues.Is there a way to better manage this by detecting which value has to beinserted/updated/deleted?And this applies to many situation :(If you need other examples, I can give you.thanks a lot!ibiza
View 8 Replies View RelatedIm trying to add a new rcord to my db on a button click usign the following code
'data adapter
Dim dAdapt1 As New SqlClient.SqlDataAdapter
'create a command object
Dim objCommand As New SqlClient.SqlCommand
'command builder
Dim builderT As SqlClient.SqlCommandBuilder
'connection string
Dim cnStr As String = "Data Source=ELEARN-FRM-BETA;Initial Catalog=StudentPlayGround;Integrated Security=True"
'dataset
Dim dsT As DataSet
Private Sub connect()
'connection
objCommand.Connection = New SqlClient.SqlConnection(cnStr)
'associating the builder with the data adapter
builderT = New SqlClient.SqlCommandBuilder(dAdapt1)
'opening the connection
objCommand.Connection.Open()
'query string
Dim query As String = "SELECT * from StudentPlayground..Employees"
'setting the select command
dAdapt1.SelectCommand = New SqlClient.SqlCommand(query, objCommand.Connection)
'dataset
dsT = New DataSet("Trainee Listings")
dAdapt1.Fill(dsT, "Employees")
End Sub
Private Sub BindData()
connect()
DataBind()
End Sub
Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click
Dim empID As Integer = CType(FindControl("TextBox8"), TextBox).Text
BindData()
Dim firstName As String = CType(FindControl("TextBox1"), TextBox).Text
BindData()
Dim lastName As String = CType(FindControl("TextBox2"), TextBox).Text
BindData()
Dim location As String = CType(FindControl("TextBox3"), TextBox).Text
BindData()
Dim termDate As Date = CType(FindControl("TextBox4"), TextBox).Text
BindData()
Dim hireDate As Date = CType(FindControl("TextBox7"), TextBox).Text
BindData()
Dim dept As String = CType(FindControl("TextBox5"), TextBox).Text
BindData()
Dim super As String = CType(FindControl("TextBox6"), TextBox).Text
BindData()
Dim newRow As DataRow = dsT.Tables("Employees").NewRow
newRow.BeginEdit()
newRow.Item(0) = empID
newRow.Item(1) = firstName
newRow.Item(2) = lastName
newRow.Item(3) = location
newRow.Item(4) = hireDate
newRow.Item(5) = termDate
newRow.Item(6) = dept
newRow.Item(7) = super
newRow.EndEdit()
'do the update
Dim insertStr As String = "INSERT INTO Employees" + _
"(EmployeeID,FirstName,LatName,Location,HireDate,TerminationDate,Supervisor)" + _
"VALUES (empID,firstName,lastName,location,hireDate,termDate,dept,super)"
Dim insertCmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(insertStr, objCommand.Connection)
dAdapt1.InsertCommand() = insertCmd
dAdapt1.Update(dsT, "Employees")
'Dim insertCmd As new SqlClient.SqlCommand = (builderT.GetInsertCommand()).ToString())
'dAdapt1.InsertCommand = New SqlClient.SqlCommand(insertCmd.ToString(), objCommand.Connection)
BindData()
objCommand.Connection.Close()
objCommand.Connection.Dispose()
End Sub
im not sure wats going wrong because the record is not being added. Please help!!
Hi again,
In ASP.net, is there any elegant way to handle a set of time inserts from a form when the 2nd time is past midnight?
Specifically, I have a form with 2 textboxes on it (startTime and endTime) that are set up to accept time values (using AJAX MaskedEditExtender for formatting/validation - pretty cool). This data is posted to a sub that enters the data into a table (T_Details). However, I've noticed that the data inserted as part of the record (SQL field is smalldatetime) doesn't take into account the fact that a time value past 23:59:59 in the "endTime" textbox is a time on the next day - it simply rolls to an A.M. date for the same day as the date for the pre-midnight value from the "startTime" textbox.
I'm sure that I can simply do some conditional coding and modify the date if necessary but is there a better way to do it? Thanks as always...this forum is a great resource
Hi, I'm inserting a few columns into my db (they all have a nvarchar(50) ).. but i noticed when i retrieve them out of the db, the length of the string always have some trailing white spaces behind them and such when I try to do stuff like dropdownlist.items.findbyvalue(), it normally fails.I did trace and before the string get into the db, they were teh right length. so I'm not sure where did I do things wrong? thanks
View 1 Replies View RelatedHi..
I am trying to add new record to any table in my database.
But I can't.
I am tryting to add a value (1500 character) and it gives the message below
"The value you entered is not consistent with the data type of the column."
I have tried alot.
The result is my columns in my table accept maximum 900 character.
But the type of my column I try to add a value is text..
I am too angry with SQL
Please help
hi
Actually in my project we need to validate the mailid entered by the user not simple validation ,i need to validate wheather the mailid exists .Ex: ravishankar@yahoo.com entered by user whether this mailid is existed in the yahoo or not i,e we want wheather it exists or not................
Please if any one know the solution please send to me .............
Thanks and regards
RavishankerMaduri
hi,friends
we show record from multiple table using single 'selectcommand'.
like....
---------
select *
from cust_detail,vend_detail
---------
i want to insert value in multiple database table(more than one) using single 'insert command'.
is it possible?
give any idea or solution.
i want to update value in multiple database table(more than one) using single 'update command'
i want to delete value in multiple database table(more than one) using singl 'delete command'
it is possible?
give any idea or solution.
it's urgent.
thanks in advance.
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
I keep getting this error:
'The value you entered is not consistent with the data type or length of the column'
when trying to enter data into a feild, the feild type is char and the length
is 100 i'm entering text 3 words long but no where near a 100 characters long
any one know why this is happening?
This is for Microsoft Server SQL 2012.
I'm trying to create a WHERE clause that will have different results depending on a parameter that is entered. For example, if you put in a number, it will only calculate the rows where the column ID matches that number. However, if you put in 0, which doesn't exist in that column ID, it will instead calculate all the data in the table.
So the below would be a very basic idea of what I'm trying to do, but I'm not sure how to do it with proper syntax.
WHERE IF ID=0, THEN do this
ELSE do this AND ID=#
Hi,I want to know the optimal solution, to find if all the data was entered. Lets say, Table A (date field) and for a given month, i need that all the days in the given month are present in the Table A. Right now i have different solutions, 1) a stored procedure which loops through all the days in the given month against a select statement on Table A2) a stored procedure, create a temp table which contains all the dates in the given month, and a single select statement using where condition (select * from.... where datefield not in (select * from...))I want to know what is the best solution of these two or any other solution.Thanks
View 9 Replies View Related
Hello
I've to write an trigger for the following action
When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz
all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated
statut_tiers to 1
and date_cloture to the same date as entered
the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture
thank you for your help
I've never done a trigger before