Newbie Trying To Input Data Into Database

Feb 8, 2007

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.

Thanks in advanced for any information provided.

Stealth549

View 2 Replies


ADVERTISEMENT

Output Data Is 2 TIMES The Input Data.SSIS.Newbie

Apr 10, 2008



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.

What could be the reasons. Please help me out.

Thank You

View 5 Replies View Related

Newbie Trying To Get Specific Data From An SSE Database

May 28, 2006

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

View 4 Replies View Related

The Data On The Website Cannot Be Input In The Server Database..

Mar 6, 2008

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")
 
End If
 
 
 
End Sub

View 2 Replies View Related

Newbie Here With A Newbie Error - Getting Database ... Already Exists.

Feb 24, 2007

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:



[SqlException (0x80131904): Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.
Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735075
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360



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?
 
Many thanks.
James 

View 1 Replies View Related

SQL 2012 :: Data Migration - Pull Data From All DBs To Input Into DW Table

Jul 15, 2014

I have a cluster hosting multiple GP databases and a second for my data-warehouse I am playing around with (personal project).

I have scripts that pull data from all the DB's to input into the DW's tables(Customers,Reps,Hub....)

An example of my branch script :

select interidas BranchID,
cmpnynamas BranchDesc,
address1as BranchAddressLine1,
address2as BranchAddressLine2,
address3as BranchAddressLine3,
zipcodeas PostalCode
from dynamics..SY01500

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 ?

View 0 Replies View Related

Input In Sql-server Database

Jun 20, 2007

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
 

View 4 Replies View Related

Data Input Problem

May 28, 2007

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)


        SqlDataSource1.Insert()


    End Sub    


</script>




<InsertParameters>


                <asp:formParameter Name="FirstName" Type="String" FormField="txtFirstName"/>


                <asp:formParameter Name="LastName" Type="String" FormField="txtLastName"/>


                <asp:formParameter Name="Phone" Type="String" FormField="txtPhone"/>


                <asp:formParameter Name="Email" Type="String" FormField="txtEmail"/>


                <asp:formParameter Name="FreeInfo" Type="Boolean" FormField="checkbox1"/>


</InsertParameters>






<asp:Button id="Button1" runat="server" text="Submit" OnClick="insertdata"/>



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.

View 4 Replies View Related

User Input Data In T-sql

Aug 20, 2005

Hi there

I want to give dynamic input to the t-sql code in sql analyzer.how do i do that....

Like adding 2 numbers

declare @a integer,@b integer,@c integer
--I dont want to give like this
--set @a = 5
--set @b = 10
set @c = @a + @b

can anyone help me with this

regards
Vic

Vicky

View 3 Replies View Related

Newbie - Data Conversion?

Jun 15, 2004

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 :).

View 6 Replies View Related

Clean Input Before Submitting To Database

Sep 11, 2006

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.

View 3 Replies View Related

Want To Compare Webform Input To Database

Apr 9, 2008

 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.

View 7 Replies View Related

Hoe To Input Apostrophy (&#39;) In A Database Field From Asp

Aug 16, 2000

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

View 1 Replies View Related

Hoe To Input HTML Code In A Database From Asp

Aug 17, 2000

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.

View 1 Replies View Related

Data Input Screen Design

Sep 14, 2000

does anyone know of easy to use tools to design input screens to access sql version 7 databases

View 1 Replies View Related

Request For Data Input In A DTS Package

Aug 3, 2007

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??

Thanks


Heather Vizina

View 1 Replies View Related

Newbie : Dump The Log On Distribution Database ?

Apr 12, 2001

Hi there,

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.

View 2 Replies View Related

Newbie Q Regarding Opening A Connection To A Database

Jan 2, 2005

Hello, Im new with databases.

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.

Any help would be much appreciated.

View 2 Replies View Related

Newbie: Copy Database But Not Tables

Oct 1, 2007

Hi,

I am using SQL 2000. I have two DB's one is live and the other is developement.

I need to copy the live DB over the developement DB but I can't overide certain tables.

How do I go about? Do I need to this using transact?

View 3 Replies View Related

MSSQL DTS Newbie - Data Transfer Between Two DBs

May 15, 2006

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.

Any help will be greatly appreciated!

View 1 Replies View Related

How To Add Data To My Tables (was SQL Server Newbie)

Feb 1, 2005

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?

Thanks

View 6 Replies View Related

NEWBIE Needs Help Automating Data From Another Server

Sep 7, 2007

Hi,

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]

View 2 Replies View Related

Newbie - Moving Data From Excel To SQL

Nov 14, 2007



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.

View 2 Replies View Related

Problem Matching Input Username With Database

Dec 6, 2005

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;
                   
string user = (string)myReader["username"];
                   
string pass = (string)myReader["password"];
                   
if (user == txtUsername.Text){                       
stat++;
                    }else {
                       
Label3.Text = "Incorrect Username";
                    }
                   
if (pass == txtPassword.Text){
                       
stat++;
                    }else{
                       
Label4.Text = "Incorrect Password";
                    }
                   
if (stat == 2)
                    {
                       
Server.Transfer("shopping.aspx");
                    }
                }
                myReader.Close();
            }
            finally
            {
                myConnection.Close();
            }

        }
    }

View 2 Replies View Related

Query Database User Input From ListBox

Jan 25, 2006

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.
 
        

View 2 Replies View Related

Need Input On Trigger Based Database Auditing

Jun 5, 2008

Hi

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?

Are there any other issues I should be aware of?

Regards, Egil.

View 13 Replies View Related

Update SQL Database Using Excel Sheet As Input

Apr 24, 2007

Hi

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.

Does anyone have a sample VB code to share.

Thanks

Al

View 3 Replies View Related

Not Able Input Full Data Into Char Column

Jul 3, 2002

Hi,

I have a table with column size char(100), But As soon as I enter 60 charcters I get an error 'Maximum limit violation'

Any help please

Thanks
Raja Jayaseelan

View 1 Replies View Related

How To Format The Date Data Type Input In DD-MM-YY

Mar 4, 2004

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.

View 1 Replies View Related

How To Update User Input Data Into Sql Server

May 28, 2007

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.


Jaimin

View 3 Replies View Related

Manually Input Data Into Replicated Table

Jul 17, 2007

Is it possible to have a replicated table that you can open from and input data manually?

That is, I've got a table that I am replicating data to and I want to add some manual data to this table, every now and then.

Will this break replication?


www.beyonder422.com

View 4 Replies View Related

Editing Input Data In Script Component

Aug 22, 2007

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)


I can provide more details if needed.

Thanks for your help.

View 17 Replies View Related

Newbie Question: Referencing A Database Field...

Sep 26, 2006

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.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved