Submit To SQL Database

Dec 6, 2006

Hi I am totally new programing and thought I would try my hands on ASP.NET 2.0.  I created a web form that would take user information and submit it to a SQL database.  However I am not sure how to properly setup the SQL connection.  What am I doing wrong?
default.aspx.cs:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class _Default : System.Web.UI.Page

{
    protected void MultiView1_ActiveViewChanged(object sender, EventArgs e)

    {

    }
    protected void Button1_Click1(object sender, EventArgs e)
    {
        String ConnStr = "Data Source=TECATE;Initial Catalog=subscribe_mainSQL;Integrated Security=True";
        String SQL = "Insert into main (email,fname,lname,userid,status) values ('" + email.Text + "', " + fname.Text + "','" + lname.Text + "','" + userid.Text + "','" + degree.SelectedItem.Value + "')";
    }
}
 
 default.aspx:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" EnableSessionState="True" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Email Sign Up Page</title>
</head>
<body style="text-align: center">
    <form id="form1" runat="server">
        <asp:MultiView ID="MultiView1" runat="server" ActiveViewIndex="0" OnActiveViewChanged="MultiView1_ActiveViewChanged">
            <asp:View ID="signup" runat="server">
        <br />
        <table style="background-color: #f0f0f0" width="450">
            <tr>
                <td style="width: 18px; height: 23px">
                </td>
                <td style="font-weight: bold; font-size: medium; width: 383px; color: black; font-family: verdana;
                    height: 23px">
                    Email Subcribe System</td>
                <td style="width: 19px; height: 23px">
                </td>
            </tr>
        </table>
        <table style="color: white; font-family: verdana; background-color: #5d7b9d" width="450">
            <tr>
                <td style="width: 19px; height: 20px">
                </td>
                <td align="center" style="width: 153px; height: 20px">
                    &nbsp;</td>
                <td align="left" style="width: 18px; height: 20px">
                </td>
                <td style="width: 115px; height: 20px">
                </td>
            </tr>
            <tr>
                <td style="width: 19px">
                </td>
                <td align="left" style="width: 153px">
                    <asp:Label ID="Label1" runat="server" Font-Names="Verdana" Font-Size="X-Small" Text="First Name:"></asp:Label></td>
                <td align="left" style="width: 18px">
                </td>
                <td align="left" style="width: 115px">
                    <asp:Label ID="Label2" runat="server" Font-Names="Verdana" Font-Size="X-Small" Text="Last Name:"></asp:Label></td>
            </tr>
            <tr>
                <td style="width: 19px; height: 26px">
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="fname"
                        ErrorMessage="*" ForeColor="White"></asp:RequiredFieldValidator></td>
                <td align="left" style="width: 153px; height: 26px">
                    <asp:TextBox ID="fname" runat="server" MaxLength="20"></asp:TextBox></td>
                <td align="left" style="width: 18px; height: 26px">
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="lname"
                        ErrorMessage="*" ForeColor="White"></asp:RequiredFieldValidator></td>
                <td align="left" style="width: 115px; height: 26px">
                    <asp:TextBox ID="lname" runat="server" MaxLength="20"></asp:TextBox></td>
            </tr>
            <tr>
                <td style="width: 19px; height: 9px">
                </td>
                <td align="left" style="width: 153px; height: 9px">
                    <asp:Label ID="Label3" runat="server" Font-Names="Verdana" Font-Size="X-Small" Text="10 Digit USC ID:"></asp:Label></td>
                <td align="left" style="width: 18px; height: 9px">
                </td>
                <td align="left" style="width: 115px; height: 9px">
                    <asp:Label ID="Label4" runat="server" Font-Names="Verdana" Font-Size="X-Small" Text="USC Email:"></asp:Label></td>
            </tr>
            <tr>
                <td style="width: 19px">
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="uscid"
                        ErrorMessage="*" ForeColor="White"></asp:RequiredFieldValidator></td>
                <td align="left" style="width: 153px">
                    <asp:TextBox ID="uscid" runat="server" MaxLength="10"></asp:TextBox></td>
                <td align="left" style="width: 18px">
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="email"
                        ErrorMessage="*" ForeColor="White"></asp:RequiredFieldValidator></td>
                <td align="left" style="width: 115px">
                    <asp:TextBox ID="email" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td style="width: 19px">
                </td>
                <td align="left" style="width: 153px">
                    <asp:Label ID="Label5" runat="server" Font-Names="Verdana" Font-Size="X-Small" Text="Degree Status:"></asp:Label></td>
                <td align="left" style="width: 18px">
                </td>
                <td align="left" style="width: 115px">
                    <asp:Label ID="Label6" runat="server" Font-Names="Verdana" Font-Size="X-Small" Text="Confirm USC Email:"></asp:Label></td>
            </tr>
            <tr>
                <td style="width: 19px">
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server" ControlToValidate="degree"
                        ErrorMessage="*" ForeColor="White"></asp:RequiredFieldValidator></td>
                <td align="left" style="width: 153px">
                    <asp:DropDownList ID="degree" runat="server">
                        <asp:ListItem></asp:ListItem>
                        <asp:ListItem>Undergraduate</asp:ListItem>
                        <asp:ListItem>Graduate</asp:ListItem>
                        <asp:ListItem Value="PhD">Ph.D</asp:ListItem>
                    </asp:DropDownList>&nbsp;
                </td>
                <td align="left" style="width: 18px">
                </td>
                <td align="left" style="width: 115px">
                    <asp:TextBox ID="confirmemail" runat="server"></asp:TextBox></td>
            </tr>
            <tr>
                <td style="width: 19px; height: 21px">
                </td>
                <td align="left" style="width: 153px; height: 21px">
                    &nbsp;
                </td>
                <td align="left" style="width: 18px; height: 21px">
                </td>
                <td align="right" style="width: 115px; height: 21px">
                </td>
            </tr>
            <tr>
                <td style="width: 19px; height: 21px">
                </td>
                <td align="left" style="width: 153px; height: 21px">
                    <br />
                    <asp:CompareValidator ID="CompareValidator1" runat="server" ControlToCompare="email"
                        ControlToValidate="confirmemail" ErrorMessage="*Emails Do Not Match" Font-Names="Verdana"
                        Font-Size="X-Small" ForeColor="White"></asp:CompareValidator>
                </td>
                <td align="left" style="width: 18px; height: 21px">
                </td>
                <td align="right" style="width: 115px; height: 21px">
                    <asp:Button ID="Button1" runat="server" CommandName="NextView" Font-Bold="True" Font-Names="Verdana"
                        Font-Size="Small" OnClick="Button1_Click1" Text="Submit" /></td>
            </tr>
        </table>
                <div align="center">
                    &nbsp;&nbsp;
                    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
                        ConnectionString="<%$ ConnectionStrings:subscribe_mainSQLConnectionString %>"
                        InsertCommand="INSERT INTO [main] (, [uscid], [fname], [lname], [degree]) VALUES (@email, @uscid, @fname, @lname, @degree)"
                        OldValuesParameterFormatString="original_{0}" SelectCommand="
                        WHERE = @original_email AND [uscid] = @original_uscid AND [fname] = @original_fname AND [lname] = @original_lname AND [degree] = @original_degree">
                        <InsertParameters>
                            <asp:Parameter Name="email" Type="String" />
                            <asp:Parameter Name="uscid" Type="String" />
                            <asp:Parameter Name="fname" Type="String" />
                            <asp:Parameter Name="lname" Type="String" />
                            <asp:Parameter Name="degree" Type="String" />
                        </InsertParameters>
                    </asp:SqlDataSource>
                </div>
            </asp:View>
            <asp:View ID="thankyou" runat="server">
                <br />
                <table style="background-color: #f0f0f0" width="450">
                    <tr>
                        <td style="width: 18px; height: 23px">
                        </td>
                        <td style="font-weight: bold; font-size: medium; width: 383px; color: black; font-family: verdana;
                    height: 23px">
                            Email Subcribe System</td>
                        <td style="width: 19px; height: 23px">
                        </td>
                    </tr>
                </table>
                <div align="center">
                    <br />
                    <br />
                    <br />
                    <asp:Label ID="Label7" runat="server" Font-Bold="True" Font-Names="Verdana" Font-Size="Small"
                        Text="Thank you for registering."></asp:Label>&nbsp;</div>
            </asp:View>
        </asp:MultiView><div align="center">&nbsp;</div>
    </form>
</body>
</html>
 

View 6 Replies


ADVERTISEMENT

How To Submit A Form To An SQL Database?

Feb 3, 2004

I have a registration form, and upon the user clicking the submit button, I want the information to be processed and sent to my SQL database. Each textbox (i.e. username, password, email address) has a corresponding field in a table in the database.

How can I accomplish this? I under stand that I will need an onclick event handler.

View 1 Replies View Related

Submit Form To Database

Dec 7, 2005

Old Frontpage user here... I need to submit a form to a database, like in Frontpage and store the database on the web server.
How do I accomplish this with Visual Studio Express...is there a ASP.NET component or do I need to code it?
Many Thanks,
Jake
p.s. my ASP.NET 2.0 books are coming for Christmas!

View 1 Replies View Related

Submit Users ID With Results.

Mar 8, 2007

Hi
I am trying to create a Quiz system and i need to put the user's Unique ID in the Result Data Table, so that the when they submit there results it puts there Unique ID by the side so that they can view it in there profile, and not anyone elses.
 Thanks
 Gareth Cork

View 1 Replies View Related

SQL-Server Job Submit From Web Page Using C#

Jun 20, 2008

Is it possible to submit a existing sql-server job from web page by using C#.
 
 

View 2 Replies View Related

Submit Query As A Scheduled Job....

Dec 25, 2002

I need to have an way to automatically submit a sql query in Sql
2000 - the query joins several lables and exports the info to a file.
Right now I've been doing it interactively in Query analyzer and
using the export to option... I can't use DTS (query way too
complicated!) and the file created is >255 lrecl so that seems to be
a big problem too!

Any suggestions? Save me from this same predicament on New Years Day!
Thanks and Happy Holidays! :(

View 1 Replies View Related

Cant Submit Any Sort Of Variable Data

Feb 27, 2008

Hi there, i have written a page and for a long time it worked (or so i thought) now all of a sudden it dosnt. I have gone right back to basics and tried to only insert one variable, tried using a text box, a string pre populated, or a string populated by the text box - nothing seems to work. However if i hard code in the thing that i want, even into the SQL or into my param it works, whats going on! i think my programming skills are letting me down here!
here is what i am trying to use (as you can see i have commented out all that i was using to get back to basics)
Are there any pointers?SqlConnection objConnAddStock = new SqlConnection(sAddStock);
//This is the sql statement.
 int intUpdateQSStockID = Convert.ToInt32(Request.QueryString["qsStockID"]);
string strCondition;strCondition = "Brand New 12";using (objConnAddStock)
{
objConnAddStock.Open();string sqlAddStock = "UPDATE tbl_stock SET condition = @condition WHERE stock_id = " + intUpdateQSStockID;
 
 
 
 
 /*cat_id = @cat_id, sub_cat_id = @sub_cat_id, n_or_sh = @n_or_sh, " +
"description = @description, size = @size, colour = @colour, cost_price = @cost_price, " +
"selling_price = @selling_price, condition = @condition, notes = @notes, " +
"buying_in_recipt = @buying_in_recipt, visible = @visible, picture1 = @picture1, " +
"picture2 = @picture2, [picture3] = @picture3, picture4 = @picture4, " +
"picture1_thumb = @picture1_thumb, picture2_thumb = @picture2_thumb, " +
"picture3_thumb =@picture3_thumb, picture4_thumb = @picture4_thumb, title = @title, " +
"display_price = @display_price WHERE Stock_id = " + intUpdateQSStockID;*/
 SqlCommand objCmdAddStock = new SqlCommand(sqlAddStock, objConnAddStock);
objCmdAddStock.Parameters.AddWithValue("@condition", conditionTextBox.Text);/* objCmdAddStock.Parameters.AddWithValue("@cat_id", DropDownList2.SelectedValue);
objCmdAddStock.Parameters.AddWithValue("@sub_cat_id", DropDownList1.SelectedValue);
objCmdAddStock.Parameters.AddWithValue("@n_or_sh", n_or_shTextBox.Text);
objCmdAddStock.Parameters.AddWithValue("@description", txtDescription.Text);
objCmdAddStock.Parameters.AddWithValue("@size", sizeTextBox.Text);
objCmdAddStock.Parameters.AddWithValue("@colour", colourTextBox.Text);
objCmdAddStock.Parameters.AddWithValue("@cost_price", cost_priceTextBox.Text);
objCmdAddStock.Parameters.AddWithValue("@selling_price", selling_priceTextBox.Text);
objCmdAddStock.Parameters.AddWithValue("@condition", conditionTextBox.Text);
objCmdAddStock.Parameters.AddWithValue("@notes", notesTextBox.Text);
objCmdAddStock.Parameters.AddWithValue("@buying_in_recipt", TextBox3.Text);
objCmdAddStock.Parameters.AddWithValue("@visible", DropDownList4.SelectedValue);
objCmdAddStock.Parameters.AddWithValue("@picture1", txtPicture1.Text);
objCmdAddStock.Parameters.AddWithValue("@picture2", txtPicture2.Text);
objCmdAddStock.Parameters.AddWithValue("@picture3", txtPicture3.Text);
objCmdAddStock.Parameters.AddWithValue("@picture4", txtPicture4.Text);
objCmdAddStock.Parameters.AddWithValue("@picture1_thumb", txtPicture1_thumb.Text);
objCmdAddStock.Parameters.AddWithValue("@picture2_thumb", txtPicture2_thumb.Text);
objCmdAddStock.Parameters.AddWithValue("@picture3_thumb", txtPicture3_thumb.Text);
objCmdAddStock.Parameters.AddWithValue("@picture4_thumb", txtPicture4_thumb.Text);
objCmdAddStock.Parameters.AddWithValue("@title", txtTitle.Text);
objCmdAddStock.Parameters.AddWithValue("@display_price", txtDisplay.Text);*/
 
try
{
objCmdAddStock.ExecuteNonQuery();
}catch (Exception ex)
{Label17.Text = Convert.ToString(ex);
}finally
{
objConnAddStock.Close();
}Label17.Text = Convert.ToString(intUpdateQSStockID);
 
}

View 3 Replies View Related

How Submit Db_name() As Default Parameter ?

Mar 8, 2006

Hi,
I want to submit db name as as default parameter when calling procedure WITHOUT using variables

Example
use master
go

create procedure get_caller_db @s sysname = DB_NAME() --produce error
as
select @s as [caller db name]

go
use mySuperDB
exec master.dbo.get_caller_db
--
caller db name
--------------
mySuperDB -- result I want

View 2 Replies View Related

Total Monthly Expenses Don't Sum Up When I Submit

Oct 15, 2006

I have a Monthlyexpense column. How do I Sum up this column and put the Total in my ytdexpenses column. Do I use a stored procedure, because I want the monthlyExpenses to SUm up every time I submit a monthly expense to the database and siplay in the ytdExpenses Column.
When I Write a Query all of the rows in the ytdExpenses shows the same amount and do not total up every time I submit to the database. Help please.

monthlyExpenses   ytdExpenses
$1,000    $1,000
$2,000    $3,000
$3,000    $6,000
$2,000    $8,000
$5,000    $13,000

View 20 Replies View Related

Asp.net Form Submit To Sql Server Express

Sep 30, 2007

pls i have asp.net page that i want to connect to sql server express edition database and i want data to be submited from the page into sql server express edition database that i have in my project folder when i click on submit button pls help me with the code to do this

View 1 Replies View Related

Multi Value Parameter Cleared On Submit

Aug 17, 2007

I have a report created in Reporting Services that contains 5 parameters. Two are text [date] fields, and three are drop-down multi-value parameters. One is driven from the result of another, and both of those work fine. My issue is with the final multi-value parameter, which is simply a list of customers. It gets populated from the data set without a problem, and I can select from 1 to all of the options. However, on submitting the report. This one parameter gets cleared out, the report does not run, but instead waits for me to select the customers (again) and resubmit.

Occasionally, I can select a few customers and it will, in fact, run the report. However, 90% of the time it just clears the field and waits.

I can trace the database, and everything looks normal - on submit nothing is sent to the database, so it's not like there's a problem with the query. I've verified the customer table and all records look similar - I don't see any obvious data issues.

Suggestions?

View 4 Replies View Related

Insert Same Data To Two Different Tables With One Submit Button....

Apr 8, 2004

I would like to insert a set of data to my database to 2 different tables.
what will be the SQL Statement for such ? Is it possible to do so?

for example, a webpage contain 2 textbox which require user to key in data. Once click submit button. the info will saved to 2 tables....

View 3 Replies View Related

Forms To View / Edit / Submit To Server

Nov 5, 2013

Currently, I have a set of data that is available on a SQL Server. I want to be able to display the information in the front-end (ie. Access form, excel, webpage?), and if the user wants to edit any information they may do so and with a click of a button, submits to the sql server.

View 1 Replies View Related

All I Wanted Was To Submit Customers Info To My Emailbox

Jan 28, 2008

I have created an order form for my business. I placed a submit button at the bottom. I wanted all the clients information to go into my emailbox. Someone told me about MS SQL Server. I got excited and downloaded the 2005 Express version with all the trimmings. Now I am more confused than I was before. Someone please explain to me what I should do first. Redo the form with the studio then work with the server? I think I may have deleted something important from the server trying to figure it out. Help!!!!!!!!!!

View 7 Replies View Related

Unable To Submit More Than 8000 Characters With Stored Procedure

Jul 20, 2005

Hi all,I have a internet page written in asp to submit into authorscurriculum vitae publications (title, author, year, etc.).If the author submit less than 8000 characters it functions OK, but Ifthe author try's to submit more than 8000 characters the asp page doesnot return an error but the text is not saved in the database or,sometimes, it returned a "Typ mismatch" error.Here is the sp:---------------------------------------------------------------------CREATE PROCEDURE sp_CV_publications(@formCommandnvarchar(255)='process',@id numeric=null,@id_personint=null,@typPubID tinyint= NULL,@publicationstext=null)ASif @formCommand='process'beginINSERT INTO CV_publications(idperson,typPubID,publications)VALUES (@id_person@typPubID,@publications);select 1 as status, @@IDENTITY AS insertedID, * FROMCV_publications WHERE id=@@IDENTITYend----------------------------------------------------------------------------The server is running IIS5 and SqlServer 2000Any ideas ???

View 1 Replies View Related

Please Help Me &"Remove Srting&" On Submit.

Aug 18, 2007

Ok, my host is free and for some reason when my users submit there code inside the discreption "textarea" with this (don't) it crashes the form without sending it to the database?
But if it is sent like this (dont) its ok!

So i am working on replacing the invalid strings so it'll be ok if they forget not to do it. But i am stuck...

Function CleanInput(strIn As String) As String
' Replace invalid characters with empty strings.
Return Regex.Replace(strIn, "[^w.@-]", "")
End Function


Any help i will love you forever :P

View 7 Replies View Related

SQL2005 Cannot Backup A Restored SQL2000 Database With Unknow Database Full-text Catalog Database

Nov 15, 2007



We replicate a SQL2000 database (DataBaseA) to a SQL2000 database (DataBaseB) by using the Restore function and hasn't change its logical name but only the physical data path and file name. It is running fine for a year. We use the same way to migrate the DataBaseB to a new SQL2005 server with the Restore function and the daily operation is running perfect. However, when we do the Backup of DatabaseB in the SQL2005, it just prompt the error message


System.Data.SqlClient.SqlError: The backup of full-text catalog 'DataBaseA' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)


Please note we left the DataBaseA in the old SQL2000 server.


Please help on how we can delete the Full-text catalog from DatabaseB so we can do a backup


Many Thanks

View 1 Replies View Related

Replication :: Syncing Of Database From Local Host Database To Online Database Automatically After Some Interval

Oct 14, 2015

I have database on localhost and i want to show this data on my website. I want to create a database online and want to sync with Local Host. Can it be possible syncing data automatically after some interval?

View 6 Replies View Related

The Database File May Be Corrupted. Run The Repair Utility To Check The Database File. [ Database Name = SDMMC Storage Cardwinp

Oct 9, 2007

yes,I have an error, like 'The database file may be corrupted. Run the repair utility to check the database file. [ Database name = SDMMC Storage Cardwinpos_2005WINPOS2005.sdf ]' .I develope a program for Pocket Pcs and this program's database sometimes corrupt.what can i do?please help me

View 4 Replies View Related

Coping Database Objects From One Database To Another Blank Database.

Aug 18, 2005

I want to create a duplicate database   in sql 2000 using asp.net from a webform
I created a database using CREATE DATABASE .......

But how to copy tables, views, stored procedures to newly created
database from old using asp.net from webform

Is there any another method to create a duplicate database with another name
from existing database on same server ?

View 5 Replies View Related

An Attempt To Attach An Auto-named Database For File (file Location).../Database.mdf Failed. A Database With The Same Name Exists, Or Specified File Cannot Be Opened, Or It Is Located On UNC Share.

Sep 2, 2007

Greetings, I have just arrived back into the country (NZ) and back into ASP.NET.
 I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
 Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J
 

View 3 Replies View Related

The Backup Set Holds A Backup Of A Database Other Than Existing Database. Restore Database Is Terminating Abnormally

Apr 9, 2008



I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".

I tried by using

RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF',
MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF',
REPLACE

And also i tried like


RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'

WITH REPLACE

When i use like this,

RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.

Can i anyone please help me out?

Thanks in Advance,
Anand Rajagopal

View 8 Replies View Related

Cant Create New Database / CREATE DATABASE Permission Denied In Database Master (error 262)

Oct 2, 2007

 
 I am using SQL express and Visual web developer on windows Vista.
When I try to create a new database the following message appears.
 
CREATE DATABASE permission denied in database master (error 262)
I log on to my computer as an administrator.
Help appreciated
 Prontonet
 
 
 

View 4 Replies View Related

NORTHWIND Database Was Re-created From A Different Database:How Can I Change The Entry In Sysdatabases For Database 'NORTHWIND'?

Jan 14, 2008

Hi all,

From the http://msdn.microsoft.com/en-us/library/bb384469.aspx (Walkthrough: Creating Stored Procedures for the Northwind Customers Table, I copied the following sql code:

--UpdateSPforNWcustomersTable.sql--

USE NORTHWIND

GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectCustomers' AND user_name(uid) = 'dbo')

DROP PROCEDURE dbo.[SelectCustomers]

GO

CREATE PROCEDURE dbo.[SelectCustomers]

AS

SET NOCOUNT ON;

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers

GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertCustomers' AND user_name(uid) = 'dbo')

DROP PROCEDURE dbo.InsertCustomers

GO

CREATE PROCEDURE dbo.InsertCustomers

(

@CustomerID nchar(5),

@CompanyName nvarchar(40),

@ContactName nvarchar(30),

@ContactTitle nvarchar(30),

@Address nvarchar(60),

@City nvarchar(15),

@Region nvarchar(15),

@PostalCode nvarchar(10),

@Country nvarchar(15),

@Phone nvarchar(24),

@Fax nvarchar(24)

)

AS

SET NOCOUNT OFF;

INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)

GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'UpdateCustomers' AND user_name(uid) = 'dbo')

DROP PROCEDURE dbo.UpdateCustomers

GO

CREATE PROCEDURE dbo.UpdateCustomers

(

@CustomerID nchar(5),

@CompanyName nvarchar(40),

@ContactName nvarchar(30),

@ContactTitle nvarchar(30),

@Address nvarchar(60),

@City nvarchar(15),

@Region nvarchar(15),

@PostalCode nvarchar(10),

@Country nvarchar(15),

@Phone nvarchar(24),

@Fax nvarchar(24),

@Original_CustomerID nchar(5)

)

AS

SET NOCOUNT OFF;

UPDATE [dbo].[Customers] SET [CustomerID] = @CustomerID, [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE (([CustomerID] = @Original_CustomerID));

SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)

GO

====================================================================================
I executed the above code in my SQL Server Management Studio Express (SSMSE) and I got the following error messages:

Msg 911, Level 16, State 1, Line 1

Could not locate entry in sysdatabases for database 'NORTHWIND'. No entry found with that name.

Make sure that the name is entered correctly.

===============================================================================================================
I know I recreated the NORTHWIND Database from a different Database before and I did not do anything for the entry in sysdatabases. How can I change the entry in sysdatabases for database 'NORTHWIND' now? Please help and advise.

Thanks in advance,
Scott Chang

View 5 Replies View Related

Copy Database One Database To Onther Database

Mar 27, 2007

hii want to copy one database table to onther database table using script?my database is ms-sql server 2000 

View 4 Replies View Related

SQL Server 2005 Express: The Database Principal Owns A Schema In The Database, And Can Not Be Dropped.

Jan 11, 2006

I recently added a new user to my database.  Now I want to delete that user, but I keep getting the error above.  What do I need to do to delete my recently added user?

View 4 Replies View Related

Database Create ODBC Connections To Access Database Directly And Update Data?

Sep 10, 2012

We have a SQL database that uses Active Directory with Windows Authentication. Can users that are members of the Active Directory group that has read/write access to the SQL database create ODBC connections to access the database directly and update the data? They dont have individual logins on the server. They are only members of the Active Directory group that has a login?

View 1 Replies View Related

SQL Server 2008 :: How To Update The Database Physical File Location In Master Database

Mar 8, 2015

I had to to relocate the database log file and I issued an Alter database command but by mistake I put a space in the file name as below. The space is at the beginning file name. Now I am unable get the database loaded to SQL Server. The database has 2 replications configured, so deleting and re-attaching the database means the replication needs to be re-configured. Is there an alternative way to issue a command to update the database FILENAME ? Not sure if this can be edited in master database (sys files).

ALTER DATABASE [User_DB]
MODIFY FILE (NAME = User_DB_log, FILENAME = 'I:SQLLogs User_DB_log.ldf')
GO

View 1 Replies View Related

I Have A Database On A Network Drive That I Use For Archiving Purposes, But When The Server Is Rebooted The Database Becomes Suspect.

Mar 31, 2007

I created the db with the attached script and I am able to access ituntil I reboot the server. I've tried enabling flag 1807 via the SQLserver service and the startup parameters of the instance. In allcases the database always come up suspect after a reboot. There wasone instance where I was able to recover, but I am not sure how thathappened.Does anyone have an idea of how I can reboot the server without thedatabase becomming suspect?USE MASTERGODBCC TRACEON(1807)GO--DBCC TRACEOFF(1807)--DBCC TRACESTATUS(1807)GOCREATE DATABASE ReadyNAS ON( NAME = ReadyNAS_Data,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Data.mdf',SIZE = 100MB,MAXSIZE = 20GB,FILEGROWTH = 20MB)LOG ON ( NAME = ReadyNAS_Log,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Log.ldf',SIZE = 20MB,MAXSIZE = 100MB,FILEGROWTH = 10MB)

View 5 Replies View Related

Database Explorer In VB Express:AdventureWorks.mdf-Files Do Not Match The Primary File Of The Database

Nov 5, 2007

Hi all,

I downloaded and ran AdventureWorks.msi into my SQL Server Management Studio Express (SSMSE) one year ago.But I did not know how to attach it to my SSMSE then. Last week, I deleted it from the "Add or Remove" of Control Panel and I downloaded the new AdventureWork.msi and installed it my SSMSE. Today, I tried to use the Database Explorer of VB 2005 Express for the first Stored Procedure programming. I clicked on AdventureWorks.mdf and I got the following error: One or more files do not match the primary of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupt and should be restored from a backup. Cannot open user default database. Login failed. Login failed for user 'CENADe1enxshc'. Log file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_Data_log.ldf' does not match the primary file. It may be from a different database of the log may have been rebuilt previously. Please help and advise me how to correct this problem.

Thanks,
Scott Chang

View 9 Replies View Related

2005 Database Will Be Shown In Management Sudio Express Or App_Data/Database Explorer?

Feb 13, 2008

I have installed 2 SQL Server 2005 Express sample databases from 2 books, ASPnet 2.0 and ADOnet 2.0. The ASPNETDB.MDF was shown in App_Data and Database Explorer, but not in the SQL Server Management Studio Express. The AdoStepBy Step database created by a ConfigDB.exe was displayed in the Management Studio, but not in the App_Data, or Database Explorer.

Is this the way SQL Server 2005 runs the 2005 databases for SQL Server 2005 Express only? Or also in SQL Server 2005?

TIA,
Jeffrey

View 6 Replies View Related

SQL Security :: How To Create Database Specifications On Newly Created Database Automatically For Audit

Jul 15, 2015

I am setting up SQL audit on sql servers in my environment based on requirement. I want to create database specifications ASAP database created. I tried DDL trigger but Audit doesn't support triggers. So I created audit specifications on model database. the only problem with this is every specification created on new database with same name.database specification name includes newly created database name or other methods to create database specifications on newly created databases.

View 6 Replies View Related

Integration Services :: Import Varying Number Of Tables Each Time From One Database To Different Database

Sep 9, 2015

I am new to SSIS. I have been struggling with this for the past one week. I have a weird task. I need to import several tables from one database to a different server with a new database name. We need to do this at the end of every year. The main problem here is that the number of tables varies every year. You may not have all the tables as last year or may have more tables. So I need to create a dynamic task that takes care of this every year without changing the package.

I have performed the following tasks **

1. Create a new dynamic database. ( I have used Execute SQL Task to do this) 2. Copy all the table structures ( I have used Execute SQL Task to do this)

3. Import Data. This is the main problem. I was trying to create a dynamic connection string with variables as suggested in several forums but I finally came to know that this cannot be done if the table structures are different as the metadata cannot be refreshed at runtime.

4. The final step to create a process to validate the data (the count from each table for both source and destination. I think this can be done with Sql task.

What is the best method to do this? My DBA does not like “Transfer SQL Objects Task” or “transfer Database Task”. I would like to create this as a dynamic process.

View 5 Replies View Related







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