i have 3 tables in sql the relation between them is one to one
person ==> employee ==> sales_department_stuff&
another relation ( one to one )between
person(the same as above ) ==> customer
i want to put person id in employee not repeated in customer becase all (employee and customer is a person )
sooooooo
i put this sql statment to try to insert person id in employee then in sales_department_stuff table to complet his ordersinsert into Person (Person_Name_Ar,Person_Name_En) values ('aaaaa',' ssssssss')select @@identity from Personinsert into Employee values (@@identity,'1') select @@identity from Personinsert into sales_department_Staff values (@@identity,'1')select @@identity from Personinsert into Driver values (@@identity,'2','2222','1/1/2005','5') /* SET NOCOUNT ON */
RETURN
the first 3 statment is run and success
but underline stetment return error that
he can insert null value in id field ,,,,,,,,,,,,,,,,
he can compile @@identity in these statment ??????????????
I have two tables I need to Select the record from the First table and insert them into the second table and delete the record from the first table how can i do that with the SQL Statment?
I have a table "Person" that has two columns "FirstName" and "LastName". How can I insert multiple rows into this table using the INSERT stsmt. I want to use just one insert statement.
Hello, How can I do something like: Insert Into Displayed(snpshot_id, user_id, user_domain, ddisplay, iWidth, iHeight, disp_size, disp_format, watermark, frc_update, creditcost) Values ((Select snpsht_id from SiteIndex Where user_domain like '%domain'), 1000, (Select domain_id from UserDomains Where user_domain like 'domain'), GetDate(), 480, 360, 2, 1, 1, 0, 3) Im getting an error: Subqueries are not allowed in this context. Only scalar expressions are allowed.
I have two tables that have a one-to-one relationship. The reason they are two table is that one table has user data, whereas the other table has security information. The security information will be in a different security zone.
How do I do an insert into the two tables so that I do not get an integrity error?
Hello, I have a table (SQL Server 2000) which contains data and I want to create insert statements based on this data. The reason for this is to create a .sql file to run this against another database to insert the same data. I know I can do an import but was trying to do this via a .sql script. Is there any way to generate these insert statement automatically using some SQL tool? Thanks
Hi, I'm having problems passing a querystring from the datanavigateurlformatstring to be used in a insert sp on a webform. Please can someone take a look at my code and point me in the right direction. At the moment I'm gettingthis error messageCompiler Error Message: BC30451: Name 'userid' is not declared. but in the url i can see the querystring I've passed from the previous page. Thanks in advance Dave 1 Protected Sub Execute_Clicked(ByVal sender As Object, ByVal e As EventArgs) 2 3 Dim objConn As New System.Data.SqlClient.SqlConnection() 4 objConn.ConnectionString = "My connection string" 5 objConn.Open() 6 Dim objCmd As New System.Data.SqlClient.SqlCommand("test_insert", objConn) 7 objCmd.CommandType = System.Data.CommandType.StoredProcedure 8 9 10 Dim puserid As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@userid", System.Data.SqlDbType.Int) 11 Dim pdate As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@date", System.Data.SqlDbType.DateTime) 12 Dim papplicants As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@applicants", System.Data.SqlDbType.Int) 13 Dim pclients As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@clients", System.Data.SqlDbType.Int) 14 Dim pcontacts As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@contacts", System.Data.SqlDbType.Int) 15 16 17 18 19 20 puserid.Direction = System.Data.ParameterDirection.Input 21 puserid.Value = Convert.ToInt32(userid.QueryString) 22 pdate.Direction = System.Data.ParameterDirection.Input 23 pdate.Value = Convert.ToDateTime([date].Text) 24 papplicants.Direction = System.Data.ParameterDirection.Input 25 papplicants.Value = Convert.ToInt16(applicants.Text) 26 pclients.Direction = System.Data.ParameterDirection.Input 27 pclients.Value = Convert.ToInt16(clients.Text) 28 pcontacts.Direction = System.Data.ParameterDirection.Input 29 pcontacts.Value = Convert.ToInt16(contacts.Text) 30 31 32 33 command.ExecuteNonQuery() 34 35 36 objConn.Close() 37 38 End Sub
Simple statment is giving me a logout on the server
INSERT INTO Angebot (Nummer,Variante,Bearbeiter,Datum,Geld,Kurs,Language,Flag,AngebotStatus,TStatus) VALUES (90360,3,'Admin',DEFAULT,'CPI_BE-D',1,'CPI_AG-1',4,NULL,59)
The statment works fine on production, however i have played a backup from production to my laptop to test something and as soon as I add a record to the database my connection to the database is droped (the data is not added to the table).
the error message I get is double, however I execute the statement only once.
Msg 0, Level 11, State 0, Line 0 Für den aktuellen Befehl ist ein schwerwiegender Fehler aufgetreten. Löschen Sie eventuelle Ergebnisse.
Msg 0, Level 20, State 0, Line 0 Für den aktuellen Befehl ist ein schwerwiegender Fehler aufgetreten. Löschen Sie eventuelle Ergebnisse.
Sugestions anyone?
PS: the message in English is:
Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.
I can think of ways to resolve this issue, but I am wondering if there is a standard practice or some setting that is used to ensure that text containing a single quote, such as "Bob's house", is passed correctly to the database when using a SqlDataSource with all of the default behavior. For example, I have a FormView setup with some text fields and a SqlDataSource that is used to do the insert. There is no code in the form currently. It works fine unless I put in text with a single quote, which of course messes up the insert or update. What is the best way to deal with this? Thank you
I cant find the error in this sql statment. What I want it to do is return everything from book, locations.locationname, and author.fullname. I want to to only return the first 10 rows. This will be used in paging, so, eventually it will be first 10, than 11-20, etc. Heres what I have, without the row limitSELECT RowNum, book.*, locations.LocationName, author.fullname FROM (SELECT book.*, locations.LocationName, author.fullname ROW_NUMBER() OVER(ORDER BY book.id) as RowNum FROM book INNER JOIN Author ON book.AuthorID = Author.ID OR book.AuthorID2 = Author.ID OR book.AuthorID3 = Author.ID OR book.AuthorID4 = Author.ID OR book.AuthorID5 = Author.ID INNER JOIN Locations ON book.LocationID = Locations.ID WHERE (Author.FullName LIKE '%' + @Search + '%') OR (Author.FirstName LIKE '%' + @Search + '%') OR (Author.LastName LIKE '%' + @Search + '%')) as BookInfo
I have two tables. One for videos and one for a "block list"Videos : VideoID UserID VideoURL VideoTitle etc. VideoBlockList :VideoID UserIDI have a datalist to show the videos but i want for the datalist to miss out any videos that a user can not see.So if the block list has "VideoID" = 2 and UserID = 1 if user 1 does a search then the search will skip out the video 2.how is this done? i tryed to do it using a specific / custom SQL statment but it errored cos the NOT value conflicted with the search... any ideas? thanks in advance si!
Select @ID = top 1 ID From Contents Where Contents.UserID=@UserID And Contents.Status=4 AND Contents.InEdit=1 why it dosn't get back the ID vlaue but when i remove the top1 one its work well why
Hi I have two tablesTABLE 1 named problemas with the field N_problem (numeric)Tabe 2 named resolvidos with the field Resol (numeric) How can i select all the records from table 1 who are not in Tabe2 ? Thank you mario
Does anyone know how to write a statement in SQL Server that is similiar to Microsoft Access's IIF function. Im not quite sure how the syntax works in a SQL Server IF statement. Thanks!
if (@cknum is not null) and (@company is null) select * from PMTK_tbl where Company = @company
else if (@compnay is not null) and (cknum is null) select * from PMTK_tbl where check_Num = @cknum else select * from PMTK_tbl where Check_Num = @cknum and Company = @company end if
I have a database used for a point of sale system it has a main table with the total amount of a check - the tip the tip is save in a different table that holds payments the issue is i need to make a statement that finds checks based of the total with the tips added. the table that holds tips can have more then one tip because you can have multiple payments on a check.
The statement i have now is like this
select jc.total + sum(jp.tip) as total from jc innerjoin jp on key where total <= @max and total >= @min group by jc.total
but this statement uses total before the tip is added. I am not sure how else to do this any help would be great
I imported a excel file from SSIS and created a table called Lockbox. To avoid the user from having to change the excel file -it is being imported as is. I only need 4 fields: [Contract ID] , [Check Number], [Owner ID], [Site ID]
The table I need to import to Transaction has Diffrent Column Names -ex-CustomerID, ResortID. The columns are in diffrent order. And I need to add more information into them like UserID = 'Hwells', Trantype = 'MF' and convert to a diffrent data type [Site ID] to text.
Hello, Is there an SELECT statement to just return the last 100 row in my tables? I have about 500 rows in my tables and I only need the info on the last 100 rows. Thanks Steve
hello, i have a page "Picture.aspx?PictureID=4" i have a FormView witch shows details about that picture and uses a stored procedure with input parameter the "@PictureID" token from query string the Pictures table has among other rows "PictureID", "UserID" - uniqueidentifier - from witch user the picture belongs to i have a second FormView on the same page, witch should show "other pictures from the same user" and uses a Stored Procedure how should i write that stored procedure...frist to take the UserID from the picture with PictureID=4, then to pass it as input parameter and select the pictures witch has as owner the user with that UserID, and if can be done, to avoid showing the PictureID=4 again a solution should be to add at querry the UserID too, but i want to avoid that any sugestion is welcomed, please help me THANKS
Hello, I have three sql select statments I would like to combine into one. I have created a statment that works but I am not sure if it is a good solution performance wise. Is there a better way to run this query? Thanks Very Much!! if exists (Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight From SiteIndex Where update_freq = 0 and nextupdate < GetDate() Order By nextupdate) Begin Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight From SiteIndex Where update_freq = 0 and nextupdate < GetDate() Order By nextupdate End else if exists (Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight From SiteIndex Where nextupdate < GetDate() Order By importance desc) begin Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight From SiteIndex Where nextupdate < GetDate() Order By importance desc end else Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight From SiteIndex Order By nextupdate
Hello All; I am using an sql statement to pass only the year using DatePart function, but i receive the following error.
curYear = DatePart(DateInterval.Year, Now) Dim Date1 = "'" & curYear & "/01/01'" Dim Date2 = "'" & curYear & "/31/12'" Dim strAll As String = "SELECT * FROM Boxes WHERE EntryDate BETWEEN Convert(DateTime, " & Date1 & ") And CONVERT(DATETIME," & Date2 & ")" The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. What is the cause, please?
Okay here is the deal. I need to take all data from tbl 1 and match it to data in 3 other tbls. I need to have it return everything back to me even if it is null.... IE tbl 1 I match the invoice_num to tbl2 site_id and then tbl2 marekt to tbl3 market. however even if tbl1 invoice_num dose not match tb2 site_id I still need to have it retun to a null value to the site_id. Here is what I have so far. This will return everything where the invoice_num and site_id match.
Code:
Select distinct t1.ID,t1.Deposit_date,t1.Ref_Num,t1.Company,t1.check_num,t1.Check_Date,t1.Check_Date,t1.Check_Total, t1.Individual_PMT,t1.Invoice_Num,t1.Invoice_Desc, t2.site_id,t3.CompanyCode,t3.CostCenter From( PMTK_tbl as t1 Left Join Leaseinfo as t2 on t2.site_id = t1.Invoice_Num) inner Join CostCenters AS t3 on t2.market = t3.market and t2.market_region = t3.RegionCode
