Jump to content

SqlBeginner

Members
  • Posts

    35
  • Joined

  • Last visited

Everything posted by SqlBeginner

  1. I have a form that features a drop down list that uses sqldatasource1 to automatically have the user selected. I'm wanting to add linkbuttons (A-Z) so when the user selects the given linkbutton only users who's last name begins with that letter is visible in the drop down list. I have stored procedure written and created sqldatasource2 for this but I'm unsure of how to code it to reflect what I'm wanting it to do.
  2. CREATE PROCEDURE
  3. I have been looking over stored procedures for quite some time now and can't seem to find an example of what I'm wanting to do. I'm wanting to pull certain fields from database A to be inserted into database B and update accordingly. I was first attempting to write one procedure but then was looking at writing a stored procedure in A and then calling the procedure in B and then inserting there...
  4. DELIMITER //CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))BEGINSELECT city, phoneFROM officesWHERE country = countryName;END //DELIMITER ; Will you explain delimiter and the reason for the information in ()
  5. OK so there is a trigger on the table that add/updates information and I would need to write the stored procedure to have that information updated to the second table?
  6. I am wanting to write a procedure that will insert new/updated fields into another table Ex: tblStaff and tblUsers Fields in tblUsers needs to be inserted into tblStaff when new records or added or fields are updated. What is the best route to go to accomplish this?
  7. I am working on a test server with this right now. I wanted to make sure that by dropping the table it wouldn't be negatively effecting anything when it was time to transfer the information to the live side.
  8. ultimately I have two tables. I'm needing to insert information from one table into the other but I get the error about the fields I'm needing to insert to being too small. (issue from previous post) I was just going to change the data type in the empty table and then do my insert but I got the error message. So that's why I was looking to drop and recreate. The table I was going to drop is ultimately empty so I thought that'd be the best one to drop and recreate.
  9. When I try to change the data type I get the error "Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. I get the error for both tables.
  10. I am needing to change the data type in a table but it won't allow me to. I usually manually drop and recreate a table but was going to try and use SQL server template since the table is rather large (right click on table-> script table as-> drop and create to) Would you suggest using this template or just manually writing it myself and going from there? IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblStaff]') AND type in (N'U'))DROP TABLE [dbo].[tblStaff]GOUSE [Test]GO/****** Object: Table [dbo].[tblStaff] Script Date: 07/10/2013 12:39:35 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[tblStaff]( [StaffID] [bigint] NOT NULL, [LastName] [varchar](50) NULL, [FirstName] [varchar](50) NULL, [Status] [nvarchar](10) NULL, [FileNum] [int] NULL, [Supervisor] [nvarchar](150) NULL, [extension] [nvarchar](25) NULL, [JobID] [varchar](10) NOT NULL, [StartDate] [datetime] NULL, [EndDate] [datetime] NULL, [Note] [nvarchar](250) NULL, [Other] [nvarchar](150) NULL, [AddedBy] [nvarchar](50) NULL, [DateRecDeleted] [datetime] NULL, [DeletedBy] [nvarchar](50) NULL, [SSMA_TimeStamp] [timestamp] NOT NULL, [MachineName] [nvarchar](50) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO
  11. OK thank you! That's exactly what I was trying to figure out!
  12. Yes I was trying to get help as far as varchar and nvarchar so I could pinpoint which fields were to small. One table used nvarchar and the other used varchar so I was trying to see what the conversion for them would be if that makes sense. example of data types that are trying to go into other data types are: bigint ->int varchar(50)->nvarchar(50)
  13. Insert into dbo.tblStaff (StaffID, FirstName, LastName, JobID, StartDate, Email, SS)SELECT [UserID] ,[FirstName] ,[LastName] ,[JobCode] ,[StartDate] ,[EmailAddress] ,[Keyword] FROM [ACU].[dbo].[Users] WHERE [WebActive]='1' and [EndDate] IS NULL and [LoginName] like 'ABCDE%'
  14. I am wanting to insert fields from a table (data types are varchar(50) into another empty table (who's data type is nvarchar(50)). I believe I have created the correct query to achieve this but received an error message when executing "Msg 8152, Level 16, State 4, Line 1 String or binary data would be truncated" Would these two data types be the cause of this? I'm using some other data types (int, bigint) that could possibly be the issue but these are the main ones used to I wanted to see about them first.
  15. The table is empty
  16. Looking at the same table when I attempt to insert the values I'm receiving a new error message. Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK_tblJobs'. Cannot insert duplicate key in object 'dbo.tblJobs'. The statement has been terminated. I went through all fields to ensure that there weren't any duplicate values but it still won't take it. JobID is the pk nvarchar(4) I also went through the 'title' column and changed any names that were the same.
  17. Oh I figured it out!
  18. This is an example of how I'm putting it in INSERT INTO [dbo].[tbljobs] (jobcode, Title) VALUES (2834, 'Clinical Services Director'),(H27A, 'Clinical Social Worker'),(H27C, 'Clinical Social Worker'),(H27D, 'Clinical Social Worker') When I put it into SQL it'll have red lines underneath it and I'll get the error Msg 207, level 16, state 1, line 1 Invalid column name
  19. I'm needing to insert values into a table. I have the data type set up as varchar(4). The values are combinations of 4 letters and/or numbers. When trying to insert it won't accepts the alpha characters. The column is set to Not Null and as the primary key. Any suggestions as to why it won't accept?
  20. OK thanks. When I recreated it missed a letter so it was interfering with the site. We purchase the site so I only have database access.
  21. Should I have received an error message when dropping the table if it had a foreign key set up to the other tables?
  22. on the site it says 500 - Internal server error. There is a problem with the resource you are looking for, and it cannot be displayed.I think the primary key for the table I dropped was linked to two columns in two other tables. So I assumed I needed to go make the columns in the other tables foreign keys but that didn't work either or I just didn't do it correctly. Also the data type in one of the other tables it needs to be linked to is the old data type.
  23. I was needing to change the data type for a field in a table that was the primary key. After researching the solution I came up with was to drop the table and recreate it. I dropped the table and then attempted to recreate it only it wouldn't take the original table name so I modified it and inserted the columns into the table under the correct data types. I believe in doing this I messed something up. When I access the site that the database is tied to I can't view information for the table I recreated or the main table it's connected to. I have tried to go to the affected tables and correct this issue by trying to change it to new table information but I receive an error. Now I am at a standstill and unsure of what needs to be done next. Thanks
×
×
  • Create New...