How To Check If Sql Insert Command Was Successful

Jan 14, 2008

I'm running a pretty standard insert command on a button (myCommand.ExecuteNonQuery();)

 I have a few other methods that run, but I only want them to proceed if the above was inserted successfully, how would i check that?

View 8 Replies


ADVERTISEMENT

How To Return Identity Value When Insert Into Slq Command Run Successful ?

Feb 10, 2006

hello,
        I'm new to store procedure in slq 2000.
       I want to create  a sproc in  my sql 2000 sevrer,I have a table named "Supplier",
now I want to return  my identiy field value when  I use insert into sql command (insert into "Supplier")  successful.
   So how to create this store procedure ?
much thanks

View 6 Replies View Related

Process Exit Code Of Successful Command

Jul 5, 2000

I wish to have a batch file exit with a return code of 3 if it fails.
In the batch file I have the single command: Exit 3
On SQL Server the Process Exit Code of a successful command is set to 0.

Yet, when I run the job SQL Server says that it is successful.

View 1 Replies View Related

SQL 2012 :: How To Check For Successful Full Backups On Availability Group Databases

Apr 20, 2015

I am setting up Availability Groups and I want to use the secondary replica to perform the full copy_only backups to reduce the load on the primary replica.But what is the best way to check for successful full backups on Availability Group databases?

Previously I could check the system table msdb.dbo.backupset but this is not available for copy_only backups.So I wonder how people are monitoring that their full backups have been successful?

Do you just check that the SQL Agent job that runs the backup was successful?

Or do you search the SQL Server Error Log for entries like "Database backed up. Database: xxx" where database xxx is in an Availability Group?

Or is there a better method?

View 1 Replies View Related

ExecuteScalar Returns 0 (null) But INSERT Is Successful.

Sep 25, 2007



I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:


RecordID = cmd.ExecuteScalar


I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.

No exception is thrown of any type or anything. Does anybody know what may be happening?

View 7 Replies View Related

Defining Command,commandtype And Connectionstring For SELECT Command Is Not Similar To INSERT And UPDATE

Feb 23, 2007

i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this        Dim test As New SqlDataSource()        test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString()        test.InsertCommandType = SqlDataSourceCommandType.Text        test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) "                  test.InsertParameters.Add("roll", TextBox1.Text)        test.InsertParameters.Add("name", TextBox2.Text)        test.InsertParameters.Add("age", TextBox3.Text)        test.InsertParameters.Add("email", TextBox4.Text)        test.Insert() i am using UPDATE command like this        Dim test As New SqlDataSource()        test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString()        test.UpdateCommandType = SqlDataSourceCommandType.Text        test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll                                                         123 "        test.Update()but i have to use the SELECT command like this which is completely different from INSERT and  UPDATE commands   Dim tblData As New Data.DataTable()         Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated                                                                                Security=True;User Instance=True")   Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn)   Dim da As New Data.SqlClient.SqlDataAdapter(Command)   da.Fill(tblData)   conn.Close()                   TextBox4.Text = tblData.Rows(1).Item("name").ToString()        TextBox5.Text = tblData.Rows(1).Item("age").ToString()        TextBox6.Text = tblData.Rows(1).Item("email").ToString()       for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me

View 2 Replies View Related

What Command Do I Use To Check How Many Processes Are Currently Running On The Server ?

Feb 29, 2008



Hello friends I want to ask that by which command I can know about how many process are currently running on the server ?

Waiting for reply.......

View 7 Replies View Related

Insert Command Fails When I Want To Insert Records In Data Table

Apr 20, 2008

On my site users can register using ASP Membership Create user Wizard control.
I am also using the wizard control to design a simple question and answer  form that logged in users have access to.
it has 2 questions including a text box for Q1 and  dropdown list for Q2.
I have a table in my database called "Players" which has 3 Columns
UserId Primary Key of type Unique Identifyer
PlayerName Type String
PlayerGenre Type Sting
 
On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table.
I am having problems getting this to work and keep getting exceptions.
 Be very helpful if somebody could check the code and advise where the problem is??
 
 
<asp:Wizard ID="Wizard1" runat="server" BackColor="#F7F6F3"
BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"
DisplaySideBar="False" Font-Names="Verdana" Font-Size="0.8em" Height="354px"
onfinishbuttonclick="Wizard1_FinishButtonClick" Width="631px">
<SideBarTemplate>
<asp:DataList ID="SideBarList" runat="server">
<ItemTemplate>
<asp:LinkButton ID="SideBarButton" runat="server" BorderWidth="0px"
Font-Names="Verdana" ForeColor="White"></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle Font-Bold="True" />
</asp:DataList>
</SideBarTemplate>
<StepStyle BackColor="#669999" BorderWidth="0px" ForeColor="#5D7B9D" />
<NavigationStyle VerticalAlign="Top" />
<WizardSteps>
<asp:WizardStep runat="server">
<table class="style1">
<tr>
<td class="style4">
A<span class="style6">Player Name</span></td>
<td class="style3">
<asp:TextBox ID="PlayerName" runat="server"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="PlayerName" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style5">
 
<td class="style3">
<asp:DropDownList ID="PlayerGenre" runat="server" Width="128px">
<asp:ListItem Value="-1">Select Genre</asp:ListItem>
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:DropDownList>
</td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="PlayerGenre" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
 
</tr>
</table>
  Sql Data Source
<asp:SqlDataSource ID="InsertArtist1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [Playerst] ([UserId], [PlayerName], [PlayerGenre]) VALUES (@UserId, @PlayerName, @PlayerGenre)"
 
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>">
<InsertParameters>
<asp:Parameter Name="UserId" Type="Object" />
<asp:Parameter Name="PlayerName" Type="String" />
<asp:Parameter Name="PlayerGenre" Type="String" />
</InsertParameters>
 
 
</asp:SqlDataSource>
</asp:WizardStep>
 
 Event Handler
 
To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
 SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1");
MembershipUser myUser = Membership.GetUser(this.User.Identity.Name);
Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue;
DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString());
DataSource.Insert();
 
}
 

View 1 Replies View Related

Start A Job Task From The Command Line & Check To See If It&#39;s Running

Apr 22, 2000

I need to start a job task from the command line. Does anyone know of the correct command(s) to do this?

I also need to check to see if a specific job task is currently executing from the command line. Does anyone know of the correct command(s) to do this?

Thanks for any assistance.

Jim

View 4 Replies View Related

SQL 2012 :: Check Command That Ensures Only Certain Values Inserted In Table?

Jan 14, 2015

I am trying to create a check command that ensures only A'B','c','D','E','F','G and s1, s2 can be inserted in the table, is this even applicable? Heres my code:

Create Table GRADE (
GradeVARCHAR2(1) CONSTRAINT pk_Grade PRIMARY KEY
CONSTRAINT check_grade
CHECK (substr(Grade = 'A','B','c','D','E','F','G')),

Salary_Scale VARCHAR2(2) CONSTRAINT check_SScale
CHECK (substr(Salary_Scale = 'S1', 'S2')),
)
/

View 1 Replies View Related

Check Before INSERT

Aug 6, 2007

I have a pretty standard form that inserts users name, office, and team. It generates a random 10 digit ID for them. How would i got about checking the table to make sure that ID doesn't exist?
Here's my insert code.
        string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString;        SqlConnection myConnection = new SqlConnection(strConnection);
        string usercode = GenPassWithCap(9);
        String insertCmd = "INSERT into users (ID, firstname, lastname, office, team) values (@id, @firstname, @lastname, @office, @team)";        SqlCommand myCommand = new SqlCommand(insertCmd, myConnection);
        myCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.VarChar, 10));        myCommand.Parameters["@id"].Value = usercode;
        myCommand.Parameters.Add(new SqlParameter("@firstname", SqlDbType.VarChar, 50));        myCommand.Parameters["@firstname"].Value = txtFirstName.Text;
        myCommand.Parameters.Add(new SqlParameter("@lastname", SqlDbType.VarChar, 50));        myCommand.Parameters["@lastname"].Value = txtLastName.Text;
        myCommand.Parameters.Add(new SqlParameter("@office", SqlDbType.VarChar, 75));        myCommand.Parameters["@office"].Value = dwnOffice.SelectedValue;
        myCommand.Parameters.Add(new SqlParameter("@team", SqlDbType.VarChar, 20));        myCommand.Parameters["@team"].Value = dwnTeam.SelectedValue;
        myCommand.Connection.Open();
            myCommand.ExecuteNonQuery();
 Do I run a completey different select command before hand and try to match that field?

View 1 Replies View Related

Check Box Insert Question

Dec 10, 2003

I have 3 tables - Data, Facility and FacilityKey

The FacilityKey table will hold the Data ID and Facility ID based on a series of check boxes on a form.

My question is what is the best way to do the insert into the FacilityKey table from the form? If 5 facilities are checked I don't want to do 5 separate calls to the database for inserts, but I'm a bit confused on what the best method would be.

Thank you!

View 1 Replies View Related

Check Data Before Insert

Aug 15, 2007

I need help thinking about this problem :-)

I have an SSIS pkg that automatically downloads financial extracts from an ftp site. Once the files are downloaded, I load the extract to a table. The table is first deleted before the insert, so that each time the table has "fresh" data (whatever was in the extract for that day).

Once the extract data is in the table, I load the data into yet another table that combines data from many tables. Simple enough.

Problem is, sometimes when I download the extract, it hasn't been updated yet, so I'm downloading an OLD extract. This old data then gets loaded into the first table. That's ok, because it doesn't really hurt anything. I can always delete the table and reload it if necessary.

The problem occurs when the old data goes from this table into the OTHER table. We don't want old data in this other table!

I need a way to check that I'm not loading the same data 2 days in a row into the OTHER table.

I realize that I might be able to solve this problem without using SSIS, but the solutions I've come up with so far aren't 100% satisfactory. I can use a query to check dates and that sort of thing, but it isn't foolproof, and would create problems if I need to manually force the process though, that is, if I need to override the date logic.

Anyways, I'm wondering if there's an SSIS approach to this problem... I can't rely on timestamps on the data files either. They're not accurate.

This is has been very perplexing to me.

Thanks

View 8 Replies View Related

Check For Column &&amp; Insert

Jan 7, 2007

Can you tell me if this is possible? (and how to do it!!)

The application is VS2005, with sql database.

I want to check if a specific column exists in a specific table in the database and if not then add it, all via my application.

I'm happy knowing how to connect to the database & pass sql commands (as I'm doing that anyway to set off backups), but not the actual queries I'd need.

View 3 Replies View Related

SQL INSERT Command

Sep 16, 2006

Hello!I have problems with my SQL insert command..string zdroj = "server=LIBRA; database=ufd; uid=sa; password=555; ";string dotaz = "SELECT heslo FROM tbl_UFD_Uzivatelia WHERE meno='"+meno.Text+"'";SqlConnection conn = new SqlConnection(zdroj);SqlCommand sqlCmd = new SqlCommand(dotaz, conn);conn.Open(); SqlDataReader reader; reader = sqlCmd.ExecuteReader();reader.Read();Does something exist as oposite of DataReader? How can I execute insert command?Hope you will help me, thanx a lot

View 6 Replies View Related

Insert Command

Sep 29, 2006

How to pass insert sqlquery using dataset

View 2 Replies View Related

Help With Insert Into Command...

Jan 10, 2007

Hi guys! I have an INSERT INTO sqlcommand that inserts values into a table from another table. Why do I always get this error?
Violation of PRIMARY KEY constraint 'PK_TE_shounin_zangyou'. Cannot insert duplicate key in object 'dbo.TE_shounin_zangyou'.The statement has been terminated.
Here's the insert command...
      Dim update_phase As New SqlCommand("INSERT INTO TE_shounin_zangyou (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_zangyou WHERE [syain_No] = @syain_No", cnn)      
The table where I would want to insert data is empty, but I get this error. What seems to be the problem?
Thanks.
Audrey

View 8 Replies View Related

Insert Command

Jan 28, 2007

i want to include an insert command to enable admin user to insert new record through the grid view but for some reason the insert command doesnt work, and i want the page to just display the record of the restaurant name "Sakura Yeshi" but i cannot do that and i dont know why? Can somebody help me out with this? i know this might be easy but just i dont know how to deal with it. Thanks
My Code:<script runat="server">
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)        Dim resmen As String        Dim connection As SqlConnection = New SqlConnection()        connection.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()
        resmen = ("SELECT resname,menu,price,date FROM rest_info WHERE resname = 'sakura yeshi'")        Dim myCommand As SqlCommand = New SqlCommand(resmen, connection)
        connection.Open()        Dim dr As SqlDataReader = myCommand.ExecuteReader()               While dr.Read()            admingrid.DataBind()                End While        connection.Close()    End Sub</script>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">    <strong>Sakura Yeshi Admin Page<br />    <asp:GridView ID="admingrid" runat="server" AutoGenerateColumns="False" CaptionAlign="Left" DataSourceID="SqlDataSource1" Style="z-index: 100; left: 163px; position: absolute; top: 182px">         <Columns>               <asp:CommandField ShowdeleteButton="True" ShowEditButton ="True" ShowinsertButton="True" />                <asp:BoundField DataField="resname" HeaderText="resname" ReadOnly="True" SortExpression="resname" />                <asp:BoundField DataField="menu" HeaderText="menu" SortExpression="menu" />                <asp:BoundField DataField="price" HeaderText="price" SortExpression="price" />                <asp:BoundField DataField="date" HeaderText="date" SortExpression="date" />            </Columns>
        </asp:GridView>        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"            SelectCommand="SELECT [resname], [menu], [price], [date] FROM [rest_info]"            DeleteCommand="DELETE FROM [rest_info] WHERE [resname] = 'sakura yeshi'"             InsertCommand="INSERT INTO [rest_info] ([resname], [menu], [price], [date]) VALUES (@resname, @menu, @date, @price)"            UpdateCommand="UPDATE [rest_info] SET [resname] = @resname, [menu] = @menu, [price] = @price, [date] = @date WHERE [resname] = 'sakura yeshi'">
<InsertParameters>                <asp:Parameter Name="resname" Type="Char" />                <asp:Parameter Name="menu" Type="char" />                <asp:Parameter Name="price" Type="Decimal" />                <asp:Parameter Name="date" Type="datetime" />            </InsertParameters>            <UpdateParameters>                <asp:Parameter Name="resname" Type="Char" />                <asp:Parameter Name="menu" Type="char" />                <asp:Parameter Name="price" Type="Decimal" />                <asp:Parameter Name="date" Type="datetime" />            </UpdateParameters>            <DeleteParameters>                <asp:Parameter Name="resname" Type="String" />            </DeleteParameters></asp:SqlDataSource></asp:Content>

View 8 Replies View Related

Insert By Command.

Jul 18, 2007

Hi,
I am creating a page which has 5 text boxes.
Firstname, lastname, pass, zip, email
and a command button. now when i click this button i want it to use a stored procedure and insert the values from the text box to db table. i dont know what code to put in the command button.

View 3 Replies View Related

Insert Command

Nov 11, 2005

I have three tables t1, t2, t3. I want to insert a new row in t1. I am using access 2000 with sql commands for the queries. There are 4 colums in t1. I know the values of two of the colums. The remaining two columns values are going to be look up in the other 2 tables, 1 value from t2 table and the other value from t3.. I need this to be in one statement. From what I have seen you can either use insert with values() or you can populate your table with unkown values from another table. But I want to do both, since 2 of my values are known and the other 2 need to come from another table.

I hope that I made it clear enough. Can anyone help me

Thanks

View 4 Replies View Related

Sql Insert Command Help

May 16, 2007

i am completely new to sql commands so i really need the help on this one.



I have three tables and they go like this



--User Table

firstTable->integerID

firstTable->stringName



--Group Table

secondTable->integerID

secondTable->stringName



--User to Groups table

thirdTable->integerFirstID

thirdTable->integerSecondID

thirdTable->stringDescription



the first table is has a user id and name and the second table has a group id and name, the third table keeps track of how many groups the user is assigned to. The problem i am having is writing a sql insert into command to add more entries to the third table from an asp.net 2.0 page, my sql command is incorrect.



The problem i am having i think, is that i am populating a dropdownlist with the names of all of the groups and when i select one that should be the group i am assigning to the user, the problem is, is that i dont know how to associated that dropdownlist string's name with its ID so that it can be passed to my insert command. is there a way to accomplish this all as a sql command? because i have a formview with a multiview and then a gridview under nested under all of that, and it would be a pain to write a event to work with all of the that.



if i do this it works

INSERT INTO ThirdTable (UserID,GroupID,Description) VALUES '318', '2', 'some description')



View 3 Replies View Related

Help...pls Check What's Wrong....not Able To Do Insert With Sqldatasource

May 9, 2007

Hi frdz,         I m the new user of ASP.NET WEB APPLICATIONS WITH C# LANGUAGE.         I m using SQL SERVER 2005 and SQLDATASOURCE to get or retrieve the data from the database.         I have created the stored procedure for insert and update.         The stored procedure is executing fine when i m running it from sqlserver2005.         My problem is with the web-application page.         I m not able to insert or update data thru that...         Pls check the code and tell me what's missing out ..........SQLDATASOURCE<asp:SqlDataSource ID="srcemp" runat="server" ConnectionString="<%$ ConnectionStrings:empmaster  %>"     InsertCommand="empStoredProcedure" InsertCommandType="StoredProcedure"     UpdateCommand="empStoredProcedure" UpdateCommandType="StoredProcedure"      DeleteCommand="DELETE FROM empmaster          WHERE empid = @empid" DeleteCommandType="Text"     SelectCommand="select * from empmaster " SelectCommandType="Text">     <InsertParameters><asp:Parameter Name="empname" Type="String" /><asp:Parameter Name="address" Type="String" /><asp:Parameter Name="city"  /><asp:Parameter Name="pincode" /><asp:Parameter Name="state" /></InsertParameters> <UpdateParameters><asp:Parameter Name="empname" Type="String" /><asp:Parameter Name="address" Type="String" /><asp:Parameter Name="city"  /><asp:Parameter Name="pincode" /><asp:Parameter Name="state" /></UpdateParameters>     </asp:SqlDataSource>GRIDVIEW  <asp:GridView ID="empGridView" runat="server" AutoGenerateColumns="False"         DataKeyNames="empid" DataSourceID="srcemp"         Width="56px" >             <Columns>    <asp:TemplateField>    <ItemTemplate>    <asp:LinkButton ID="btnDelete" runat="server" CommandName="Delete" OnClientClick="return confirm('Are you sure you want to delete this row ?');">Delete</asp:LinkButton>    </ItemTemplate>    </asp:TemplateField>                                                  <asp:boundfield datafield="empname"            headertext="emp Name"/>           <asp:boundfield datafield="address"            headertext="Address"/>          <asp:boundfield datafield="city"            headertext="City"/>         <asp:boundfield datafield="state"            headertext="State"/>                                     <asp:CheckBoxField            DataField="deleted"            HeaderText="In Existance" />            </Columns>          </asp:GridView>C# code protected void cmdsubmit_Click(object sender, EventArgs e)    {        srcemp.InsertParameters["empname"].DefaultValue = tbcompanyname.Text;        srcemp.InsertParameters["address"].DefaultValue = tbaddress.Text;         srcemp.InsertParameters["city"].DefaultValue = tbcity.Text;        srcemp.InsertParameters["pincode"].DefaultValue = tbpincode.Text;        srcemp.InsertParameters["state"].DefaultValue = cmbstate.SelectedItem.ToString();         srcemp.Insert();}Note :I m not getting a single error msg for the above code in web-page or stored procedure but it does not insert,update or delete the record from the database....Thanxs in adv...pls reply at earliest if possible...What's missing ??can anyone check out..what's wrong ??

View 1 Replies View Related

Performing Insert Query With Check

Feb 29, 2008

hii,,i am using asp.net 2005 and sql server 2005.i have a web page in which i can enter details and it gets stored in a table in a database..in the table thrs a column called as sme_id,,what i want is when one inserts a new sme_id from the page,,it should check in the table so tht no duplicate sme_id wil b generated..,,this code is workin fine,,i just want to implement the above condition...here is the insert code which i have used along with sql datasource:::__________________________
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:sme_trackerConnectionString %>"
InsertCommand="INSERT INTO SME_Master(SME_Id, FirstName, LastName, Type_of_SME, Agency_id, Agency_Name, Email, Address, Phone, Mobile, Fax, TimeZone_Id, Experience, City, State, Status, Level_Of_Exam, Other_Comments, Certificate, Expertise)
 VALUES (@SME_Id, @FirstName, @LastName, @Type_of_SME, @Agency_id, @Agency_Name, @Email, @Address, @Phone, @Mobile, @Fax, @TimeZone_Id, @Experience, @City, @State, @Status, @Level_Of_Exam, @Other_Comments, @Certificate, @Expertise)"
OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [SME_Master]">
_______hope u got my problem,,,,,reply asap....thnks in advance

View 11 Replies View Related

Query Question - Check Before Insert

Dec 1, 2003

I have a table, emailaddresses with an emailaddress field.

before i do an insert from a stored proc, i want to check if the emailaddress is already in the database.

pseudo-code:

if emailaddresssparameter is IN emailaddress then
do not insert
else
insert into table
end

i've got the insert statement and the stored proc, but how do i write the check to see if it's already there? I mean i could do a select * from emailaddress wehre emailaddress=emailaddressparam

but how to i test it? if the count=1 then skip?

here's my proc now:

ALTER PROCEDURE dbo.AddOneEmailAddress

(
@emailAddress varchar(255),
@emailID int=0 OUTPUT
)

AS
/* SET NOCOUNT ON */
insert into EmailAddresses
(email_address)
values
(@emailAddress)
set @emailID=@@identity

RETURN @@identity

View 2 Replies View Related

SSIS: Check -&&>Update -&&>Insert

Sep 7, 2007

I have two tables from two different Databases

DB1.dbo.Table1 and DB2.dbo.Table2

eX:

Table 1
KEY LName FName Updated
1 GYM ABC Y
1 TIM ABC N
1 PIN ABC N
2 QWE SAD Y
......
....


Table 2
KEY LName FName Updated
1 JIM ABC Y
2 QWE SAM Y

....
....


1) Table 1 and Table 2 are of same structure.
2) In table2, as in above example, few changes have beeen done for KEY1 AND Update =Y, Similarly KEY= 2 AND UPDATED=Y, like for KEY= 1 LName was changed to JIM instead of GYM and for KEY= 2 FName has been changed to SAM instead of SAD.

3) Now I want to do this in SSIS where
a) Its going to process rows of Table2 and check in table1 according to KEY and UPDATE=Y and update the Table1 with Updated = N and Insert that particulra process row of Table2 into Table1

and hence Resultant of Table1 must be like this

Table 1
KEY LName FName Updated
1 GYM ABC N
1 TIM ABC N
1 PIN ABC N
1 JIM ABC Y

2 QWE SAD N
2 QWE SAM Y
......
....

Can somebody help me how to do this in SSIS. Thnaks a lot in advance

View 1 Replies View Related

SQL INSERT COMMAND PROBLEM

May 11, 2007

I am having trouble issuing a database command.  Dim insertTopic As String = "INSERT forum_Topics " _
+ "(forum_id, topic_title, topic_poster, " _
+ "topic_type, topic_time) " _
+ "VALUES(@forum_id, @topic_title, @topic_poster," _
+ "@topic_type, @topic_time);UPDATE forum_Forums " _
+ "SET forum_topics = forum_topics + 1 " _
+ "WHERE forum_id = @forum_id"  I have the forum_Topics table primary key enabled on topic_id. It is supposed to give it a topic_id number automatically, so I don't have to insert a topic_id I get this error message... Cannot insert the value NULL into column 'topic_id', table 'ASPNETDB.MDF.dbo.forum_Topics'; column does not allow nulls. INSERT fails.The statement has been terminated.Of course the collum does not allow NULLS, but I shouldn't have to insert a topic_id, because it is the primary key and it is the identity. What am I missing or doing wrong? 

View 5 Replies View Related

SqlDataSource Insert Command....

Oct 12, 2007

Hello all,
I am having problem to insert the record into database from sqldatasource control. my code is listed below, and i can't find anything why it cause the exception...
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet"                ConnectionString="<%$ ConnectionStrings:WebsiteDataConnection %>"                 SelectCommand="SELECT * FROM [ServiceAgents]"                InsertCommand="INSERT INTO ServiceAgents VALUES(@Ser_STATE, @Ser_CITY, @Ser_AGENT, @Ser_PHONE, @Ser_EQUIPMENT)">                                <InsertParameters>                    <asp:FormParameter Name="Ser_STATE" FormField="TextBox_state"/>                    <asp:FormParameter Name="Ser_CITY" FormField="TextBox_city"/>                    <asp:FormParameter Name="Ser_AGENT" FormField="TextBox_agent"/>                    <asp:FormParameter Name="Ser_PHONE" FormField="TextBox_phone"/>                    <asp:FormParameter Name="Ser_EQUIPMENT" FormField="TextBox_equip" />                   </InsertParameters>            </asp:SqlDataSource>
The table has got the fields that needed for insert command, for example:
            <table>                    <tr>                        <td>State:</td>                        <td>                            <asp:TextBox ID="TextBox_state" runat="server"></asp:TextBox>                            <asp:RequiredFieldValidator                            id="RequiredFieldValidator1"                            runat="server"                            ControlToValidate="TextBox_state"                            Display="Static"                            ErrorMessage="Please enter a state." />                        </td>                    </tr>                                        <tr>                        <td>City:</td>                        <td>                            <asp:TextBox ID="TextBox_city" runat="server"></asp:TextBox>                            <asp:RequiredFieldValidator                            id="RequiredFieldValidator2"                            runat="server"                            ControlToValidate="TextBox_city"                            Display="Static"                            ErrorMessage="Please enter a city." />                        </td>                    </tr>                                        <tr>                        <td>Agent:</td>                        <td>                            <asp:TextBox ID="TextBox_agent" runat="server"></asp:TextBox>                            <asp:RequiredFieldValidator                            id="RequiredFieldValidator3"                            runat="server"                            ControlToValidate="TextBox_agent"                            Display="Static"                            ErrorMessage="Please enter a agent." />                        </td>                    </tr>                                        <tr>                        <td>Phone:</td>                        <td>                            <asp:TextBox ID="TextBox_phone" runat="server"></asp:TextBox>                            <asp:RequiredFieldValidator                            id="RequiredFieldValidator4"                            runat="server"                            ControlToValidate="TextBox_phone"                            Display="Static"                            ErrorMessage="Please enter a phone No." />                        </td>                    </tr>                                        <tr>                        <td>Equipment:</td>                        <td>                            <asp:TextBox ID="TextBox_equip" runat="server"></asp:TextBox>                            <asp:RequiredFieldValidator                            id="RequiredFieldValidator5"                            runat="server"                            ControlToValidate="TextBox_equip"                            Display="Static"                            ErrorMessage="Please enter a equipment." />                        </td>                    </tr>                                        <tr>                        <td></td>                        <td>                            <asp:Button ID="Button2" runat="server" Text="Add" OnClick="addEntry"                             BackColor="#FFFBFF"                             BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"                            Font-Names="Verdana" Font-Size="1.0em" ForeColor="#284775"/>                          </td>                    </tr>                </table>
And in the code i just called: SqlDataSource1.Insert(); Then the page gives me the exception like:
Cannot insert the value NULL into column 'STATE', table 'WebsiteData.dbo.ServiceAgents'; column does not allow nulls. INSERT fails. The statement has been terminated.
But i actually input all the required text in the textbox.... Any idea guys?
Thanks

View 2 Replies View Related

SqlDataSource And Insert Command

Nov 20, 2007

I get the following error, when I try to create a company. Any help would be appreciated. Error Code: Cannot insert the value NULL into column 'CompanyName', table 'Telemetry.dbo.Company'; column does not allow nulls. INSERT fails.The statement has been terminated. Here is my aspx file: <table>     <tr ><td style="width:110px"><b>Company Name:</b></td><td ><asp:TextBox ID="CompanyName" Text="" runat="server" width="250px"/></td></tr>    <tr><td ><b>Phone Number:</b></td><td ><asp:TextBox runat="server" ID="CompanyPhone" Text=""  Width="250px"/></td></tr>                        <tr><td ><b>Company E-mail:</b></td><td ><asp:TextBox runat="server" ID="CompanyEmail" Text="" Width="250px"/></td></tr>                        <tr><td ><b>Street Address:</b></td><td><asp:TextBox runat="server" ID="AddressStreet" Text="" Width="250px"/></td></tr>            <tr><td ><b>City :</b></td><td><asp:TextBox runat="server" ID="AddressCity" Text="" Width="200px"/></td></tr>            <tr><td ><b>State/Province:</b></td><td><asp:TextBox runat="server" ID="AddressState" Text="" Width="150px"/></td></tr>            <tr><td ><b>Zip Code:</b></td><td><asp:TextBox runat="server" ID="AddressZip" Text="" Width="150px"/></td></tr>            <tr></tr>                </table>          <asp:Button ID="Submit" runat="server" OnClick="Submitinfo" />     <br />    <asp:SqlDataSource ID="sqlCreateCompany" runat="server" ConnectionString="<%$ ConnectionStrings:SqlServer1 %>"        InsertCommand="INSERT INTO Company(CompanyName, CompanyPhone, CompanyEmail, AddressStreet, AddressCity, AddressState, AddressZip) VALUES (@CompanyName, @CompanyPhone, @CompanyEmail, @AddressStreet, @AddressCity, @AddressState, @AddressZip)"        SelectCommand="SELECT [CompanyID], [CompanyName], [CompanyPhone], [CompanyEmail], [AddressStreet], [AddressZip], [AddressState], [AddressCity] FROM [Company]"  >                <InsertParameters>            <asp:FormParameter Name="CompanyName"  FormField="CompanyName"/>            <asp:FormParameter Name="CompanyPhone" FormField="companyPhone" />            <asp:FormParameter Name="CompanyEmail" FormField="CompanyEmail" />           <asp:FormParameter Name="AddressStreet" FormField="AddressStreet" />                       <asp:FormParameter Name="AddressCity" FormField="AddressCity" />            <asp:FormParameter Name="AddressState" FormField="AddressState" />            <asp:FormParameter Name="AddressZip" FormField="AddressZip" />                    </InsertParameters>            </asp:SqlDataSource> .....Behind the code.............. protected void Submitinfo(object sender, EventArgs e)    {        //TextBox t = (TextBox)FormView1.FindControl("CompanyName");        sqlCreateCompany.Insert();    }  .........Database Company Table Design..............CompanyID    int    UncheckedCompanyName    varchar(100)    UncheckedCompanyPhone    varchar(50)    CheckedCompanyEmail    varchar(100)    CheckedAddressStreet    varchar(100)    CheckedAddressCity    varchar(50)    CheckedAddressState    varchar(2)    CheckedAddressZip    varchar(5)    CheckedCompanyLogo    varchar(100)    Checked  

View 2 Replies View Related

SQL INSERT Command From One Table To Another?

Dec 19, 2007

i have a question if anyone can help please..
i have need to create a database table as a go-between, where users will upload info.  then on a backend page admin will make some data settings, then transfer it to the proper, permanent tables.(all this to avoid expecting users themselves to upload files to proper places).
can i use a basic SQL INSERT statement to move from one table directly to another without the use of parameters? for example,
INSERT INTO tblOne (fieldOne,fieldTwo,fieldThree) VALUES (tblTwo(fieldOne,fieldTwo,fieldThree)
if so, what is the syntax? and if not, then what would i have to do, just use some go between variables or objects? perhaps a DataSet to pull the values, then use same DataSet to load the values into new table, then a simple DELETE statement on the intermediate table?
i mean, i can work my way through this, but if anybody has gone through a similar situation i sure could use any pointers or tips to keep it as clean and simple as possible.
thanks all!

View 3 Replies View Related

Insert Command Not Working, Help Please

Mar 21, 2008

Hey people im just wondering if someone could help me out with this Insert query im doing from one of the learn asp videos. I have a table called EquipmentBooking and it contains the following fields
 <teachingSession, int,> <staff, char(5),> <equipment, varchar(15),> <bookedOn, datetime,> <bookedFor, datetime,> now im doing the following Insert statement in Visual Studio 2005 when the submit button is clicked but all I get is the catched error exception and I just can working out why. Can someone help? heres the code im using
Dim WebTimetableDataSource As New SqlDataSource()WebTimetableDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("WebTimetableConnectionString").ToString()
WebTimetableDataSource.InsertCommandType = SqlDataSourceCommandType.Text
WebTimetableDataSource.InsertCommand = "INSERT INTO EquipmentBooking (teachingSession, staff, equipment, bookedOn, bookedFor) VALUES (@TeachingDropDown, @StaffDropDown, @EquipmentDropDown, @DateTimeStamp, @DateTextBox)"WebTimetableDataSource.InsertParameters.Add("TeachingDropDown", TeachingDropDown.Text)
WebTimetableDataSource.InsertParameters.Add("StaffDropDown", StaffDropDown.Text)WebTimetableDataSource.InsertParameters.Add("EquipmentDropDown", EquipmentDropDown.Text)
WebTimetableDataSource.InsertParameters.Add("DateTimeStamp", DateTime.Now())WebTimetableDataSource.InsertParameters.Add("DateTextBox", DateTime.Now())
Dim rowsAffected As Integer = 0
Try
rowsAffected = WebTimetableDataSource.Insert()Catch ex As Exception
Server.Transfer("problem.aspx")
Finally
WebTimetableDataSource = Nothing
End Try
If rowsAffected <> 1 ThenServer.Transfer("confirmation.aspx")
End If

View 5 Replies View Related

Insert Command Not Working....

Jan 24, 2004

This is a real head ache. Nothing I do to add a record to my SQL2k Database wil work.

I'm logged into it as "sa".

I've Tried Stored Procedures:

Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As New SqlCommand("AddLender", myConnection)
' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure

Dim parameterUserName As New SqlParameter("@UserName", SqlDbType.NVarChar, 100)
parameterUserName.Value = userName
myCommand.Parameters.Add(parameterUserName)

Dim parameterName As New SqlParameter("@Name", SqlDbType.NVarChar, 100)
parameterName.Value = name
myCommand.Parameters.Add(parameterName)

Dim parameterCompany As New SqlParameter("@Company", SqlDbType.NVarChar, 100)
parameterCompany.Value = Company
myCommand.Parameters.Add(parameterCompany)

Dim parameterEmail As New SqlParameter("@Email", SqlDbType.NVarChar, 100)
parameterEmail.Value = email
myCommand.Parameters.Add(parameterEmail)

Dim parameterContact As New SqlParameter("@Contact", SqlDbType.NVarChar, 100)
parameterContact.Value = contact
myCommand.Parameters.Add(parameterContact)

Dim parameterPhone As New SqlParameter("@Phone", SqlDbType.NVarChar, 100)
parameterPhone.Value = Phone
myCommand.Parameters.Add(parameterPhone)

Dim parameterFax As New SqlParameter("@Fax", SqlDbType.NVarChar, 100)
parameterFax.Value = Fax
myCommand.Parameters.Add(parameterFax)

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

Return CInt(parameterItemID.Value)

The Sproc.......



CREATE PROCEDURE AddLender
(
@Username nvarchar(100),
@ModuleID int,
@Email nvarchar(100),
@Name nvarchar(100),
@Rep nvarchar(250),
@Phone nvarchar(250),
@Fax nvarchar(250),
@City nvarchar (100),
@State nvarchar(100),
@ItemID int OUTPUT
)
AS
INSERT INTO Lenders
(
Email,
Name,
Rep,
Phone,
Fax,
CIty,
State
)
VALUES
(
@Email,
@Name,
@Rep,
@Phone,
@Fax,
@City,
@state

)
SELECT
@ItemID = @@Identity

GO




I get no Errors... I've run SQLProfiller and I don;t even see it run...

I also tried the method..


Dim strSql As String
Dim objDataSet As New DataSet()
Dim objConnection As OleDbConnection
Dim objAdapter As OleDbDataAdapter

strSql = "Select ItemId,ModuleId,Name,Rep, Email,Phone,Fax,City,State,Address From Portal_Lenders;"

objConnection = New OleDbConnection(ConfigurationSettings.AppSettings("ConnectionStringOledb"))
objAdapter = New OleDbDataAdapter(strSql, objConnection)

objAdapter.Fill(objDataSet, "Lenders")

Dim objtable As DataTable
Dim objNewRow As DataRow


objtable = objDataSet.Tables("Lenders")

objNewRow = objtable.NewRow()
objNewRow("ModuleId") = moduleId
objNewRow("Name") = NameField.Text
objNewRow("Rep") = RepField.Text
objNewRow("Email") = EmailField.Text
objNewRow("Phone") = PhoneField.Text
objNewRow("Fax") = FaxField.Text
objNewRow("City") = CityField.Text
objNewRow("State") = Statefield.Text
objNewRow("Address") = StreetAddress.Text

objtable.Rows.Add(objNewRow)


Still no Error or activity in the Pofiller.

Please Help...

View 2 Replies View Related

Insert Command In SQL Form

Jan 12, 2006

Good morning,
I don't know where is the problem here, but I can not add a new record in the SQL database related. Can you take a look at the code please and let me know what's wrong? When I press the button to insert the data is automatically reloads without any input in the database.
Thanks in advance,Alejandro.
<%@ Page Language="VB" Debug="true" %>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>Untitled Page</title>
</head>
<body>
<form id="form1" method="post" runat="server">
<asp:SqlDataSource id="ds1" runat="server"
ConnectionString="server=62.149.153.13;database=MSSql13067;uid=MSSql13067;pwd=d7f15bd2"
SelectCommand="SELECT [usuario], [emailes] FROM [usuarioyemail]"
InsertCommand="INSERT INTO [usuarioyemail] ([usuario], [emailes]) VALUES (@usuariocontrol, @emailcontrol)"
>
<InsertParameters>
<asp:ControlParameter Name="usuariocontrol" ControlID="usuario" Type="Char" PropertyName="Text" />
<asp:ControlParameter Name="emailcontrol" ControlID="email" Type="Char" PropertyName="Text" />
</InsertParameters>
</asp:SqlDataSource>
<asp:TextBox ID="usuario" runat="server"></asp:TextBox>
<asp:TextBox ID="email" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Button" />
</form>
</body>
</html>

View 3 Replies View Related

Insert-Select Command

May 21, 2001

I need to move various records from a production database to an archive database. The fields in the two databases are identical but the archive database has an additional "Transfer Date" field. Can anyone recommend an easy way to create an SQL statement to move the record from production to archive? I've tried

INSERT INTO ARCHIVE (SELECT * FROM PRODUCTION WHERE ACCOUNTNO='XYZ')

but I get an error saying the columns among the two tables do not match (obviously because of the "Transfer Date" column). Is there a way to use a similar SQL statement that will populate the "Transfer Date" column w/ today's date?

Thanks in advance for your help.

View 2 Replies View Related







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