Hello everyone, I am new to mssql so please excuse all my silly questions.
I can create databases and tables. This is fairly easy, now I am having trouble with inputting data into the database using the data entry browser (or whatever it is called to enter data directly into a table).
I usually get an error when trying to submit the entry, firstly I have a "id" field which I think I have set to auto increase (Identity is ticked, seed and increment is set to 1).
when I go to add data all fields have "Null" in them and from what I understand it allows the fields to be empty, except the primary key which is the id field in this case and I expect that to be filled in automatically like with mysql but isn’t.
Can some kind person instruct or direct me somewhere on how to enter data into a table without it failing.
Hello All, I am migrating data from one database to another. I am using Multicast to seperate (legal street,legal mail and legal city) and (mail_street,mail_state,mail_zip,mail_city) also later after UNION of the above I am doing two lookups as I had to get contact ID and Customer ID from other two tables. In UNION i am matching (Mail street legal street) and so on.
I am getting double the data in the output. my input data is 1000000 and im gettin 2000000.
It was suggested to me to try and post this here in the sql express forum.
I am trying to query a specific field from my sse database based on two different variables. And use this information to fill a textbox with an integer.
The first variable is a combobox that is filled with data in one of the other tables in the sse database. This table has a PKcolumn that is an integer and the identity of the column. The only other column is a nvarchar(30) string (which is the actual data in the combobox)
The second variable is a numeric up down that isn't actualy using info from the database but will be a reference. It has a max value written in to the code:
if NumericUpDown1.Value > 20 Then
MessageBox.Show("This form only supports characters up to level 20")
NumericUpDown1.Value = 20
The texbox value will be found in a table that has several columns, the first column is an identity column (also the PK) then it has a column that holds an integer value (the filter criteria for the numeric up down) the next column has the value that will fill the text box, then several other columns that are irrelevant for this, and then the final column is a foreign key that is linked to the primary key of information found in the table that feeds the combo box.
I will post a picture of the database diagram to show all of this information for a better understanding of this post.
I am not looking for someone to write the code for me, but I am totally stumped as to what I need to do, If someone can help me out with this I would greatly appreciate it, thanks in advanced.
http://static.flickr.com/75/154867997_a55d3b0b9e.jpg - this is the table diagram
hi there.. I have write a code in the submit button using vb code so that when people key in their email.. it will be saved in my created database called test.mdf. but when i debug it, i tried to write an my email address in the textbox in my website but when i click at the submit button, the web page display the email was not enter in my database. here is the code, can anybody help me. i'm really lost here.. Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim TestDataSource As New SqlDataSource()TestDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("TestConnectionString1").ToString()
TestDataSource.InsertCommandType = SqlDataSourceCommandType.Text TestDataSource.InsertCommand = "INSERT INTO Email(EmailAddress, IPAddress, DateTimeStamp) VALUES (@EmailAddress,@IPAddress,@DateTimeStamp)" TestDataSource.InsertParameters.Add("EmailAddress", emailAddressTextBox.Text) TestDataSource.InsertParameters.Add("IPAddress", Request.UserHostAddress.ToString())TestDataSource.InsertParameters.Add("DateTimeStamp", DateTime.Now()) Dim rowsAffected As Integer = 0
Try rowsAffected = TestDataSource.Insert() Catch ex As Exception Server.Transfer("test_problem.aspx")
Finally
TestDataSource = Nothing
End Try
If rowsAffected <> 1 ThenServer.Transfer("test_problem.aspx") ElseServer.Transfer("Test_Confirm.aspx")
Hi there I sorry if I have placed this query in the wrong place. I'm getting to grips with ASP.net 2, slowly but surely! When i try to access my site which uses a Sql Server 2005 express DB i am receiving the following error:
Server Error in '/jarebu/site1' Application.
Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'. 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: Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
This is the connection string that I am using: <connectionStrings> <add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;Initial Catalog=ASPNETDB;User Instance=True" providerName="System.Data.SqlClient"/> </connectionStrings>
The database is definitly in the folder that the error message relates to. What I'm finding confusing is that the connection string seems to be finding "aranga"s database. Is it something daft?
Where interid in ('comp1', 'comp2', 'comp4', 'comp5')
what would be the best way to using these scripts pull the data to my testDW and not have duplicate data issues?
I was thinking of using a staging DB on the GP cluster and then building an import data package to run nightly. the issue i had was how do i avoid duplicate data ?
hy, i wrote an input function to put some data in my database with click of button it doesnt work and i cant find the mistake =/ anyone of you can help? now theres one thing that isnt right, and that is that the datasiz of messagetext is set to max, and here i put it in to 50, cause dont know how to put it to max cause you can only put in ant integer , also in the insert into, i did not put all of the columns cause the data i input is only for certain columns, ( don't think thats a problem) Greetz Roy1 2 Private mocon As clsAdocon 3 Dim naam As String 4 Dim type As String 5 Dim folder As String 6 Dim sUDL = ConfigurationManager.ConnectionStrings("masterConnectionString").ConnectionString 7 8 Protected Sub btnopslaan_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnopslaan.Click 9 10 folder = "Out" 11 Select Case True 12 Case rdbMail.Checked 13 type = "Mail" 14 Case rdbFax.Checked 15 type = "Fax" 16 Case rdbSms.Checked 17 type = "Sms" 18 End Select 19 20 If type = "Mail" Then 21 Dim strSql As String 22 strSql = "INSERT INTO Message(ToName, ToEmail, Subject, MessageText, TypeBericht, Folder) VALUES(@ToName,@ToEmail,@Subject,@MessageText,@TypeBericht,@Folder);" 23 24 Try 25 'connectie met database 26 Dim objCn As New SqlConnection(sUDL) 27 Dim objCmd As SqlCommand = objCn.CreateCommand() 28 objCmd.CommandText = strSql 29 30 objCmd.Parameters.Add("@ToName", SqlDbType.NVarChar, 50).Value = naam 31 objCmd.Parameters.Add("@ToEmail", SqlDbType.NVarChar, 50).Value = txtAan.Text 32 objCmd.Parameters.Add("@Subject", SqlDbType.NVarChar, 50).Value = txtOnderwerp.Text 33 objCmd.Parameters.Add("@MessageText", SqlDbType.NVarChar, 50).Value = txtbericht.Text 34 objCmd.Parameters.Add("@Folder", SqlDbType.NChar, 10).Value = folder 35 objCmd.Parameters.Add("@TypeBericht", SqlDbType.NChar, 10).Value = type 36 37 objCn.Open() 38 objCmd.ExecuteNonQuery() 39 objCn.Close() 40 Catch ex As Exception 41 42 End Try 43 End If 44 45 End Sub
In Visual Web Developer I have created a data input form based on the documentation I found in the .NET Framework Class Library (SqlDataSource.InsertCommand Property). Originally my form contained 4 textboxes (FirstName, LastName, Phone, Email) and worked fine. All this data is nvarchar string data.
When I added a checkbox I get an error that “String was not recognized as a valid Boolean� when the checkbox is checked. When the checkbox is unchecked the data is input without a problem. This is a bit data field in the table.
Here is my VB code, Insert Parameters for my SqlDataSource1, and button code:
<script runat="server">
Private Sub InsertData(ByVal Source As Object, ByVal e As EventArgs)
I suspect that the problem is with the VB code which will only accept string input data, but don’t know how to fix it. Any thoughts on how to fix this problem? Thanks in advance for any help provided.
Hi everyone... sorry, but I'm really new at this! I'm trying to make a table out of an imported Excel file (which is automatically generated as an all-text data type). I wrote a query that takes this excel file and puts it into the format we need (i.e., appropriate column headers, removing "garbage" characters and spaces). Is there any way to format the data going into the table by a specified datatype?
Sorry if this is simplistic or wasting your time, but I'm really stumped :o. However, keep in mind that I started learning SQL this morning and I've already gotten this far :).
Is there some recommended way to clean input before submitting it to the database? We'd like to develop a library that can be used on our ASP/ASP.NET apps to filter input before it's sent to the SQL Server and Oracle databases. Is there a way to create a .NET DLL that can be used for both ASP.NET and classic ASP apps. Thanks.
Hello, I am writing a website (in vb) to allow for room use reservations and I am looking for a way to compare the selected start and end times which are date-time format to records already in the database for that day/time and specific room. If the selected time does not fall within an already reserved time frame I then want to insert it into the table. I have already written a procedure to do the inserting and that works fine. I just want to validate it before calling the insert. Any ideas? The fields to be validated on the form are textboxes containing date time which populate as read only after the time selection is chosen from dropdown. Thanks in advance It's greatly appreciated.
My name is Syed Kamran Ahmed and i'm an ASP Programmer. I've got a problem with entering values in database from ASP page, if i use apostropy in a text box and then passes values to Sql server store Procedures then it will give following error: Error Type: Microsoft OLE DB Provider for SQL Server (0x80040E14) Line 1: Incorrect syntax near 's'. my calling store procedure syntax is: rsinsert_products.Open "exec sp_insert_products '"&vPdescription&"',dbc, adOpenDynamic ,adLockOptimistic . Where vPdescription contains kamran's home. Please tell me how i'm gonna put "'" in database from above method. Thanx. Kamran Ahmed
I want to put HTML code in my ASP form and then input in to a database in SQL SERVER 7.0, but due to some syntax problems i can't be able to do it. Please tell me how i'm gonna input a typical HTML code in Database field. Thanx.
Hi there. This is only my second post ever- so please bear with me.
First, I am using SQL Server 2000.
I have built a DTS package using an Oracle ODBC and the following query;
select pv.segment1 vendor_nbr, pv.vendor_name,pv.attribute4 , pv.creation_date from po.po_vendors pv , po.po_vendor_sites_all pvs where pv.vendor_id = pvs.vendor_id(+) operating unit AND trunc(pv.creation_date) = trunc(sysdate)
This DTS package is not always run on the same day the records are created. So......
What I want to do is have the dts package ask the user for the date they are looking for-
I know I need to probably use a parameter, but I have been unable to figure out how to do this in a DTS pkg. I have several reSQL reference books and I don't even know what to search for in Books online. Could anyone help??
I'm a newbie. I've installed a replication between 2 NT4 servers, with SQL 6.5. My logreader tasks has crashed "Can't allocate space for objects 'syslogs' in database 'distribution' etc...". With a DBCC SQLPERF(LOGSPACE) I can see that the log is 100% full, but I can't dump it : when I use DUMP TRAN DISTRIBUTION WITH NO_LOG, my distribution log is still 100% full. So how can I do to dump it and what can I do to prevent it from filling up ? Do I need to dump it like I do for my database but how could it work if a DUMP TRAN doesn't work ?
Thanks in advance.
Sorry for the English, it's not my native language.
I know that opening a connection to a DB is expensive. Usually I write a method that opens a connection to the DB then I execute a query and then close the connection. Pretty standard.
OK, so how do I handle opening connections to the database when I need to run multiple queries. For example, i have a webpage that need to query the database to see if the user has moderator privledges, then depending on that query I have to query the DB again for moderator specific information or non-modertaor information.
So in this case how do i handle opening connections to the DB. Is it ok to generally have to open a connection to a DB multiple times on a page load?
The obvious solution is to keep the connection open. That is, open a connection, query the Db, keep the connection open, do the conditional statment ( if is_Moderator) then query the DB again for the info that I need, and then close the connection. But, from all the books that Ive been reading this is not a good practice because business logic should not been in the dataAccess layer.
I am just starting out with MSSQL, but have previous experience with MySQL and PGSQL. What I'm trying to do is do a nightly dump of data from a proprietary DB into MSSQL. Access to the prop DB is through an ODBC driver.
I already have a DTS script that will dump a specific table, but every time it runs it just appends the data to the end of the table.
What I am looking for is a way to download a list of tables, upload them into the MSSQL DB, and if something failed, rollback to the state before the data transfer and somehow alert of the fact.
Hi. I just set up my first sql server database and I've managed to connect to it via ASP as a test. I'm not sure how to add data to my tables. In MS Access, you can edit the table and add records. How do I do that in SQL Server? I'm using the Enterprise manager tool to create tables.... does it have something i can use?
I am able to do these actions interactively from SQL 2005 (not developers nor enterprise edition, just using SQL 2005 Mgmt Studio) and want to "script/batch" them so I can have them automatically run at a pre selected time.
First: I am able to delete the table by performing a right click on the table, then click Delete from Mgmt Studio SQL 2005. I verify the table is completely gone with a refresh. (I pulled the code that did this ..... DROP TABLE etc. to Notepad)
2nd: I am able to import the table (again from Mgmt Studio SQL 2005) and have saved this action as a SSIS. Execute the script and "waLa" I have all 17K rows of data. I pulled this create table code into notepad also.
Now I put the code of both of the above actions together (drop table and create table) into one SQL query and execute it. This does not give me the same results of above, instead my table is blank now.
Maybe there is a better way. The business problem I am attempting to solve: I am refreshing the data in a as/400 table weekly. I want that refreshed data to be available in the SQL2005 database without my having to press buttons first thing Monday morning. Can any one help? Thanks in advance.
Below is the Code:
USE [400kas] GO /****** Object: Table [dbo].[navar100] Script Date: 09/07/2007 16:09:04 ******/ DROP TABLE [dbo].[navar100] GO
USE [400kas] GO /****** Object: Table [dbo].[Query] Script Date: 09/07/2007 16:12:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[navar100]( [CMPNO] [decimal](3, 0) NOT NULL, [ARTDT] [datetime] NOT NULL, [AUDDT] [datetime] NOT NULL, [ARDDT] [datetime] NOT NULL, [CCUS#] [decimal](6, 0) NOT NULL, [CCNAM] [nvarchar](25) NOT NULL, [CUSNO] [decimal](6, 0) NOT NULL, [CNAME] [nvarchar](25) NOT NULL, [SHPNO] [decimal](4, 0) NOT NULL, [ARRCD] [nvarchar](1) NOT NULL, [AUDUS] [nvarchar](10) NOT NULL, [INVNO] [decimal](6, 0) NOT NULL, [CUSPO] [nvarchar](15) NOT NULL, [REFNO] [decimal](6, 0) NOT NULL, [COMNT] [nvarchar](10) NOT NULL, [SHPPO] [nvarchar](15) NOT NULL, [AMONT] [decimal](13, 2) NOT NULL, [AMOUNT] [decimal](24, 8) NOT NULL, [REMAN] [decimal](13, 2) NOT NULL, [INREG] [decimal](3, 0) NOT NULL, [INSAL] [decimal](3, 0) NOT NULL, [TMCOD] [nvarchar](2) NOT NULL, [CRHLD] [nvarchar](1) NOT NULL, [CRLIM] [decimal](13, 0) NOT NULL, [CRDAY] [decimal](3, 0) NOT NULL, [TCRCD] [nvarchar](3) NOT NULL, [TEXRT] [decimal](11, 6) NOT NULL, [R1RGL] [decimal](13, 2) NOT NULL, [TAXAM] [decimal](13, 2) NOT NULL, [TFRTX] [decimal](13, 3) NOT NULL, [TFRGT] [decimal](13, 2) NOT NULL, [TSPCH] [decimal](13, 2) NOT NULL, [SPCST] [decimal](13, 2) NOT NULL, [IRPFT] [decimal](13, 2) NOT NULL ) ON [PRIMARY]
Hi there. i have the following excel spreadsheet that needs to be moved into a database:
ADDRESS STREET SURNAME GIVEN PHONE CITY POSTAL NOTES TERR
40 Goodless Crt You M (416) 123-1234 SC M1B 1A1
SC-123-12
59 Bellevue Ave Doe Jon (416) 123-1234 SC M1B 1A3
SC-123-13
Most of these fields are going to be created as reference tables... and the main contact list table will only include a fk to the actual value. eg) the contact table for the above will look like:
ADDRESS STREET SURNAME GIVEN PHONE CITY POSTAL NOTES TERR
40 1 You M (416) 123-1234 1 M1B 1A1
5
59 2 Doe Jon (416) 123-1234 1 M1B 1A3
6
My question is, what is the best way to move all this data into SQL Server Express? I've been playing around with the LinkedServers section in sql and have managed to create a connection to my excel workbook. I am able to select all worksheets as tables... and i've also been able to select all records from the main contacts sheet. not knowing any better, my initial thoughts are to create separate sql statements for each reference table. eg) select street from excellink...contact$ and then combine it somehow with an insert statement into the streets table in my database. Is this the right approach? if it is, can you help with the syntax of the sql statement? Please and thanks.
I tried matching the input username with the database. Although the input value is the same as the database, but it doesnt goes into the if statement to increase the "stat" value. Please advise what went wrong. Thanks.
protected void btnEnter_Click(object sender, EventArgs e) { if (txtUsername.Text.Length > 0) { status++; Label3.Text = ""; } else { Label3.Text = "Please enter a username"; }
if (txtPassword.Text.Length > 0) { status++; Label4.Text = ""; } else { Label4.Text = "Please enter a password"; }
if (status == 2) { int stat = 0; string mySelectQuery = "SELECT * FROM users";
SqlConnection myConnection = new SqlConnection("Data Source=WINSON-COMP;Initial Catalog=winson;Integrated Security=True");
SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection); try { myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader(); while (myReader.Read()) { stat = 0;
I have a problem selecting fields from a table where fields are equal to user input from a listbox. example listbox of zip codes: 33023[red]22300[/red]39844[red]29339[/red]23883[red]38228[/red] user wants to retreive highlight zip codes from database.connection working perfect.Thank you for your help.
I am building a pretty simple intranet application where we need to be able to track changes to tables. The tracking feature do not need to be very advanced, we just need to see who changed something and what it was. Therefore I decided just to use a trigger based solution, but need some input/advice since my SQL skills is somewhat lacking.
Consider the following (mock-up) schema:
-- My content table CREATE TABLE [Content]( [ContentGuid] [uniqueidentifier] NOT NULL PRIMARY KEY DEFAULT (newid()), [Data] [nvarchar](4000) NOT NULL, [ChangedBy] [nchar](10) NOT NULL, [MaybeNull] [int] NULL )
-- My history table CREATE TABLE [History]( [ChangedTable] [nvarchar](50) NOT NULL, [ReferenceGuid] [uniqueidentifier] NOT NULL, [ChangedBy] [nchar](10) NOT NULL, [ChangedOn] [datetime] NOT NULL DEFAULT (getutcdate()), [IsDelete] [bit] NOT NULL DEFAULT ((0)), [Changes] [xml] NOT NULL ) ON [PRIMARY]
-- My insert/update trigger CREATE TRIGGER [RecordChangeOnInsertUpdate] ON [Content] AFTER INSERT,UPDATE AS BEGIN SET NOCOUNT ON;
DECLARE @Changes xml SET @Changes = (select * from [inserted] for xml raw ('Content'), elements xsinil)
INSERT INTO [History] ([ChangedTable] ,[ReferenceGuid] ,[ChangedBy] ,[ChangedOn] ,[IsDelete] ,[Changes]) SELECT 'Content', ContentGuid, ChangedBy, getutcdate(), 0, @Changes FROM [inserted] END
-- My delete trigger CREATE TRIGGER [RecordChangeOnDelete] ON [Content] AFTER DELETE AS BEGIN SET NOCOUNT ON;
DECLARE @Changes xml SET @Changes = (select * from [deleted] for xml raw ('Content'), elements xsinil)
INSERT INTO [History] ([ChangedTable] ,[ReferenceGuid] ,[ChangedBy] ,[ChangedOn] ,[IsDelete] ,[Changes]) SELECT 'Content', ContentGuid, ChangedBy, getutcdate(), 1, @Changes FROM [deleted] END
I have decided to use the "one history table for all table changes" method. The changes for a row is stored in a xml column which obviously limits the total size of columns in a table being tracked, but that is not a problem in my application. In general I like this set up, since I will be able to change the schema continuously without having to change the triggers, and since the application will probably evolve a lot over the coming months this is pretty important to me.
EDIT: I should add that all the tables I will be tracking have a uniqueidentifier column. This makes it possible to related table rows in the different tables being tracked with their history.
A few concerns with the above SQL:
- Can the inserted/deleted table change between "SET @Changes = (select * ..." and the "INSERT INTO ..." statement, such that the data is not valid? If so, how to work around that?
- If I were to (hypothetically) perform a "UPDATE [Content] SET [Data] = 'something'", not only is my update trigger called once for each row updated, but the XML added to the inserted row in the history table ([History].[Changes]) represent all the rows updated in the batch update. How do I get around this?
I am a very beginner in SQL and know a little bit VB.
I created a database table with two columns, one for key and the other for data. I'd like to update the data column using excel sheet which contains the same columns, key and data.
hi all , does SQL server able to input the date that is originally from 2/6/2004 11:07:46 AM to DD-MM-YY 06-02-04 and store in the DB. For example , this is the SQL insert query
INSERT INTO ADMIN ( ID , NAME , DATE ) VALUES ( 'A001' , 'karen' ,'2/6/2004 11:07:46 AM')
the date will automatically converted to 06-02-04 and store in DB .
Does it need to use SQL Rule or user-defined data type.
hi , i am new to this. how should i update the user input values into sql server database? i am using asp.net and c# FIELDS are- userId, name, description, startTime, endTime, audiencePassword, presenterPassword
i know it must be simple...but i haven't worked on this before.
This is very similar to the post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1901076&SiteID=1
I am trying to do this. Basically, if the field I am checking contains a space - not an empty string - I want to assign it an empty string so it will get converted to NULL when it hits the SQL destination. The space character from the source is causing the rows to fail I want to force a NULLin the database as a "workaround" from something I am working on.
I thought I had this set up correctly, but it is not working. I have the field in question as an input field set to ReadWrite
If Row.[COLNAME].ToString = " " Then Row.[COLNAME] = "" End If
I am getting the following error:
[Assign District Code [12469]] Error: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)
Hi all, I am still in the process of learning ASP.NET. The simple goal I am trying to accomplish is to set a string equal to a field in my SQL Database using ASP.NET.I am just not sure what the correct syntax is to do so. It may be work noting that I am programming in VB.NET. I have my SQL Data Source setup correctly and inserted into my form with an ID of "PDCalculations".The field that I want to reference is "Percentage" which is an integer field.The reason I want to put the amount into a string is so I can use it in some calculations that are happening on my form.Thanks in advance.
