I am simply trying to use SQLCommand in .net to insert a record into a SQL Server 2005 table. There are 2 fields being pulled from a user input for that could have no values selected. In this case I want to insert a null value into the record. I get an error that Null is no longer supported and that I should use System.DBNull, but that can't be used as an expression.
How do I do this?
Thank you in advance.
Is that possible to restrict inserting the record if record already exist in the table.
Scenario: query should be
We are inserting a bulk information of data, it should not insert the row if it already exist in the table. excluding that it should insert the other rows.
Hello,
I have got a database with a datetime field. I insert a date into the database from a textbox. All is fine if someone enters a date. If someone enters nothing I want a null to be inserted. How do I do this?
Grant
how do you write an insert into statement and keep “<NULL>” in the null cells?
i was trying something like this BUT when you try to write an IS NULL statement it doesnt work.
ISNULL(dbo.TRUNK02_LastVersion_PayableClaims_01.MO D1, NULL) AS Mod1,
Is there a way, I can insert NULL value to "DT_Date" type Row Column using Script Component Transformation of Data flow?
View 8 Replies View RelatedI want to insert a new record into my db in asp.net. Im programming in vb and using an sql server database. i know this is a begginers question but that's exactly what i am. Thanks in advance
View 1 Replies View RelatedHi,
I am trying to insert null values into sql server from my access from. I am using sql statement. But it says 'Syntex error in Insert statement'. When i remove null values it works fine? How can I insert null values into a table?
Any help will be highly appreciated.
Folks, this isn't exactly a 'Getting Started' question, but I couldn't find a more appropriate Application Development forum.
I'm porting an open source PHP application (http://sourceforge.net/projects/gallery) to use SQL Server as a backend. One of Gallery's unit test scripts tests the ability to insert a string containing a NULL character ( ). It's OK if the string is truncated during insertion, just so long as everything before the is there.
The string being inserted looks like:
$testString = "The NULL character should be escaped !";
(Note the between "escaped " and " !")
The error that the Gallery test script is getting is:
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'The NULL character should be escaped '.] in EXECUTE("INSERT INTO g2_PluginParameterMap (g_pluginType, g_pluginId, g_itemId, g_parameterName, g_parameterValue) VALUES ('module','unitTestModule',1,'test19476','The NULL character should be escaped !')")
It looks like SQL Server is complaining about the syntax. I've written a much simpler test script in the hopes of reproducing the problem, but I don't know if what I'm now hitting is the same problem or a different one.
My simple script is:
<?php
$localhost = exec("hostname");
$database = "gallery2";
$uid = "g2user";
$pwd = "g2pwd";
$connectString = "Host=PROVIDER=MSDASQL;DRIVER={SQL Server};";
$connectString .= "SERVER=$localhost\sqlexpress;";
$connectString .= "DATABASE=$database;";
$connectString .= "UID=$uid;PWD=$pwd";
$sqlTableDrop = "drop table ljmtemp";
$sqlTableCreate = "create table ljmtemp (col1 nvarchar(100))";
$sqlTableQuery = "select len(col1) from ljmtemp";
// Connect to the db
$db = new COM("ADODB.Connection") or die("Cannot start ADO");
$db->open($connectString);
// Drop & recreate the table
$db->Execute ($sqlTableDrop);
$db->Execute ($sqlTableCreate);
// Insert the test data
//$testString = "The NULL character should be escaped !";
$testString = "This is a test string.";
$res = $db->Execute("insert into ljmtemp (col1) values ('$testString')");
if (!$res) die ("INSERT failed");
// Disconnect from the db
$db->Close();
?>
And it results in:
C:MyServer>php testMsSqlInsertNull.php
PHP Fatal error: Uncaught exception 'com_exception' with message 'Source: Microsoft OLE DB Provider for ODBC Drivers
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark after the character string 'The NULL character should be escaped '.' in C:MyServer estMsSqlInsertNull.php:27
Stack trace:
#0 C:MyServer estMsSqlInsertNull.php(27): com->Execute('insert into ljm...')
#1 {main}
thrown in C:MyServer estMsSqlInsertNull.php on line 27
I'm not sure if this is the same problem as Gallery is reporting or another one.
It looks like somebody is treating the as a string terminator, but when i double the backslash the literal ' ' is inserted into the database.
How can I get SQL Server to insert at least everything up to the ?
Hopefully once I have my simple script working I'll be able to figure out what's wrong in the Gallery test.
Thanks.
hey everyone,I have created two tables in an sql server database. I have a check box in the table 1, and when it is checked, I want to insert that particular record into table 2. (By the way I am using a datagrid) The problem is when I click the check on the particular record I want inserted, and click update, the system copies the record twice instead of once. When I look into table 2, I end up having two of the same records. Can anyone help me.
View 4 Replies View RelatedHi All, i am new to programming, in my application i want to insert a record in sql server database using Ado.net for that i used SqlConnection cn=new SqlConnection(ConfigurationManager.ConnectionStrings["constring"].ConnectionString); SqlCommand cmd; protected void btnInsert_Click(object sender, EventArgs e) { try { cmd = new SqlCommand("Insert into DeptInfo(deptid,deptname)values(" + TextBox1.Text + ",'" + TextBox2.Text + "')", cn); SqlDataAdapter da = new SqlDataAdapter(cmd); cn.Open(); cmd.ExecuteNonQuery(); cn.Close(); TextBox1.Text = ""; TextBox2.Text = ""; } catch(Exception ex) { }} But my requirement is when ever the Insert command is successfull it has to display some alert message(using java script) saying "RECORD INSERTED SUCCESSFULLY" if Record insertion fails it should display some alert message "INSERTING NEW RECORD FAILED" . Any help will be greatly appreciated Thanks,Vision.
View 15 Replies View Relatedi am running a query from one of my asp.net pages. I then want the results which will be around 20 - 30 records to be inserted to a new table. How do I do this?
I get the records from my query but how would i insert more than one record to a new table?
thanks
Is it possible to have sql server automatically record date and time (in a designated field)when a record is created in the db? This may seam basic but it has caused me a lot of grief.
View 8 Replies View RelatedI have a datetime field in a database which I am programmatically inserting values into. The field can be null or not null. However, I am having problems inserting NULL as part of my SQLCommand.
The value is pulled from a text box (linked to a calendar extender) and when I select a value it is being inserted fine. If I just leave the box blank though, I want the field to be set to NULL. I have tried adding in ,DBNULL.Value, as part of my VALUES(…) string, but this throws an exception. I Have tried just inserting ‘’ but that also throws an exception (“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value�), so I don’t know how I can insert this value when the field is blank?
Can anyone shed some light please? Thanks
Hi,
We are using an SQL Server database and seem to be having some problems with inserting null values into numeric fields. The field is set to accept nulls, but when we try and write a record into the database and that field is null, the application craps out on us. Are there any issues that we should be aware of when inserting null values into numeric fields? What might the problem be? Thanks.
i have attached XML and XSD file
i bulkload xml file into 2 tables .ManifestID is the Relationship between 2 tables
CREATE TABLE [dbo].[BIOS_DataLoader_LDR_Manifest_Detail] (
[manifest_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[order_num] [bigint] NULL ,
[track_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[box_id] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[box_type] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[scan_time] [datetime] NULL ,
[scac_code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[BIOS_DataLoader_LDR_Manifest_Header] (
[trailer_id] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[trailer_close_date] [datetime] NULL ,
[manifest_id] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[manifest_qty] [int] NULL ,
[origin_facility] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[destination_facility] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
When the first TrackCodeid is NULL
all remaining NULL TrackCodeid is entered as null in the database table
but if the first/prev trackcodeID is not NULL then following null trackCODEID is populated with the prev trackcodeID and not as null in database
<TrackCodeId>ABCDEFG</TrackCodeId>
All Null TrackCOdeID is populated as ABCDEFG
if we remove ABCDEFG and then bulkload all null values are populated
if a null trackCODEID is to be inserted the prev trackCODEID must be null and must not contain any value
hi
how can we insert null into a small datetime field (from client application VB)
What is the keyboard shortcut for entering a <NULL> into a database field from within the Enterprise Manager? I've done it before, but can't remember it for the life of me! Also, is there a query that I can use to insert <NULL> in place of ''?
View 5 Replies View RelatedHi,
I have a flat file whose data looks like this:
"Opening Balance", Acct1234, 1001.01
"Closing Balance", Acct1234, 1001.01
In my script component for "Opening Balance", I set my output columns like this:
Row.AccountNumber = CDate(rowValues(1)
Row.OpeningBalance = CDec(rowValues(2))
Row.ClosingBalance = Nothing
Because I don't have a value for ClosingBalance in this row, I set ClosingBalance = Nothing.
And for "Closing Balance", I set my output columns like this, because there's no value for OpeningBalance.
Row.AccountNumber = CDate(rowValues(1)
Row.OpeningBalance = Nothing
Row.ClosingBalance = Nothing
Then, in my column mappings I map OpeningBalance = OpeningBalance and ClosingBalance = ClosingBalance.
The idea being that if there's no value, then it should be set to NULL. However, in my table, I see that instead of NULLs, there's 0.00000. Which is not what I want to see.
Any ideas why this is happening?
Thanks
I can't seem to get this work. I'm using SQL2005
I want to check if a record exists before entering it. I just can't figure out how to check it before hand.
Thanks in advance. Protected Sub BTNCreateProdIDandName_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BTNCreateProdIDandName.Click
' Define data objects
Dim conn As SqlConnection
Dim comm As SqlCommand
' Reads the connection string from Web.config
Dim connectionString As String = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString
' Initialize connection
conn = New SqlConnection(connectionString)
' Check to see if the record exists
If
comm = New SqlCommand("EXISTS (SELECT (BuilderID, OptionNO FROM optionlist WHERE (BuilderID = @BuilderID) AND (OptionNO = @OptionNO)", conn)
Then
'if the record is exists display this message.
LBerror.Text = "This item already exists in your Option List."
Else
'If the record does not exist, add it. FYI - This part works fine by itself.
comm = New SqlCommand("INSERT INTO [OptionList] ([BuilderID], [OptionNO], [OptionName]) VALUES (@BuilderID, @OptionNO, @OptionName)", conn)
comm.Parameters.Add("@BuilderID", System.Data.SqlDbType.Int)
comm.Parameters("@BuilderID").Value = LBBuilderID.Text
comm.Parameters.Add("@OptionNO", System.Data.SqlDbType.NVarChar)
comm.Parameters("@OptionNO").Value = DDLProdID.SelectedItem.Value
comm.Parameters.Add("@OptionName", System.Data.SqlDbType.NVarChar)
comm.Parameters("@OptionName").Value = DDLProdname.SelectedItem.Value
LBerror.Text = DDLProdname.SelectedItem.Value & " was added to your Option List."
Try
'open connection
conn.Open()
'execute
comm.ExecuteNonQuery()
Catch
'Display error message
LBerror.Text = "There was an error adding this Option. Please try again."
Finally
'close connection
conn.Close()
End Try
End If
End Sub
I am trying to insert a record into a sql server table from a textbox in a form but am getting this error
The name "textbox1value" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
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: The name "textbox1value" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.Source Error:
Line 33: dim command1 as sqlcommand=new sqlcommand(q1,connection1)
Line 34: command1.connection.open()
Line 35: command1.executenonquery()
Line 36: command1.connection.close()
Line 37: end sub This code is right below. The sub called a() is the one with the problem.
<html> <head> <title> </title> <script language="vb" runat="server"> sub page_load(sender as object, e as eventargs) if not page.ispostback then binddata1() end if end sub sub binddata1() dim connection1 as new sqlconnection("server=111.111.11.11;uid=aa;pwd=bb;database=cc") const q as string="select dxid,code from tbltest where date_del is null order by code" dim command1 as new sqlcommand(q,connection1) dim dataadapter1 as new sqldataadapter() dataadapter1.selectcommand=command1 dim dataset1 as new dataset() dataadapter1.fill(dataset1) grid1.datasource=dataset1 grid1.databind() end sub
sub a(sender as object,e as eventargs)' response.write(textbox1.text) dim textbox1value as string textbox1value=textbox1.text dim connection1 as sqlconnection=new sqlconnection("server=111.111.11.11;uid=aa;pwd=bb;database=cc") const q1 as string="insert into tbltest(code,descriptor) values (textbox1value,'test')" dim command1 as sqlcommand=new sqlcommand(q1,connection1) command1.connection.open()
command1.executenonquery()
command1.connection.close() end sub </script> </head> <body> <form runat="server"> <asp:textbox id="textbox1" runat="server" /> <asp:button id="button1" text="click" onclick="a" runat="server" /> <asp:datagrid id="grid1" runat="server" /> </form> </body></html>
What am I doing wrong?
How can I get the insert query to put the value of textbox1.text into the column called code?
Sorry if I am not explaining things clearly. This is like a foreign language to me.
Hi,
Can someone please tell me the best practices for checking the primary key field before inserting a record into my database?
As an example I have created an asp.net page using VB with an SQL server database. The web page will just insert two fields into a table (Name & Surname into the Names table). The primary key or the Names table is "Name". When I click the Submit button I would like to check to ensure there is not a duplicate primary key. If there is return a user friendly message i.e. A record already exisits, if there no duplicate, add the record.
I guess I could use try, catch within the .APSX page or would a stored procedure be better?
Thanks
Brett
Is there a way to test the integrity of a record which is about to be inserted in a database before doing so?
The goal is to prevent all kinds of runtime errors that will occur after the insertion, if the record breaks any of the database rules.
The solution should apply to a VBScript used inside a DTS package.
SELECTIndustry,
100.0 * SUM(CASE when ceoischairman = 'yes' then 1 else 0 end) / COUNT(DISTINCT CompID) AS [YesPercent],
100.0 * SUM(CASE when ceoischairman = 'no' then 1 else 0 end) / COUNT(DISTINCT CompID) AS [NoPercent]
FROMTCompanies
GROUP BYIndustry
ORDER BYIndustry
This code above is working as I need it but I need to insert some additional functionality. Thanks
I need to add something like this:
IF YesPercent > NoPercent
UPDATE tableX SET CEOIsChairman='Yes' WHERE Industry='<the industry value being evaluated>'
Else If NoPercent > YesPercent
UPDATE tableX SET CEOIsChairman='No' WHERE Industry='<the industry value being evaluated>'
Else
UPDATE tableX SET CEOIsChairman='Equal' WHERE Industry='<the industry value being evaluated>'
End
Hi All,
I'm using Microsoft SQL Server Management Studio with SQL Server 2005 (SP2).
I have not had much experience with t-SQL and iterative logic implementation through SQL, therefore I think my problem is fairly basic for most of the pros here.
Here it goes...
I have two tables at hand, TestTab1 and TestTab2.
TestTab1 contains two attributes (columns) namely account_name (nvarchar(50)) and entry_count (int). For all rows in TestTab1, account_name is unique, i.e. for each account_name there is only 1 row in TestTab1. This is my source table.
TestTab2 contains one attribute namely account_name (nvarchar(50)). This table is empty and is my destination table.
I need to insert X entries (rows) for each account_name into TestTab2 where X is the int value in entry_count againt each account_name in TestTab1.
In other words, I need to insert account_name into TestTab2 as many times as the number in entry_count indicates.
I tried reading through the documentation but the help was not friendly enough for me to understand how to implement this.
Looking forward to the pro support.
Thanks,
Hey everybody!
How do i insert a carriage return at the end of an record that's being sent to a flat file? Currently, I get one long string, and would like for SSIS to put carriage returns at the end of each line.
Any ideas?
Thanks!
Jim Work
I've just noticed some strange behavior that seems like a bug to me.
It's much easier to follow an example of it that to outright explain it, so here goes.
I have a table defined with a NOT NULL constraint on a column and a default clause:
-- DROP TABLE TestTable
CREATE TABLE TestTable ( TestField0 varchar(10), TestField1 varchar(10) NOT NULL DEFAULT ('a') )
I have a view defined on the table, in this example case, the view just mirrors the table one to one:
-- DROP VIEW TestView
CREATE VIEW TestView as SELECT TestField0, TestField1 FROM TestTable
So far so good, if I run this statement, it works as I would expect and inserts the value and the default goes into the other field:
INSERT INTO TestView (TestField0) SELECT 'test'
Now... If I add an INSTEAD OF trigger to the view, and have it perform the insert for me, I get an error with the same insert stmt:
-- DROP TRIGGER TestTrigger
CREATE TRIGGER TestTrigger ON TestView
INSTEAD OF INSERT AS
BEGIN
INSERT INTO TestTable (TestField0, TestField1)
SELECT TestField0, COALESCE(TestField1, 'X')
FROM inserted
END
Notice the trigger will ensure that a null value cannot be inserted into TestField1. If I run this insert stmt though I get an error:
INSERT INTO TestView (TestField0) SELECT 'test'
Server: Msg 233, Level 16, State 2, Line 1
The column 'TestField1' in table 'TestView' cannot be null.
Am I missing something or is this a bug?
Thanks
I have written up a grid consisting of properties and units.The way it works is we have properties, and within properties there are units. They are two seperate tables.Some properties do not have any units so in the unit reference (UN_UREF) column for those records which do not have units and are NULL I would like to insert the text 'NO UNIT'.Please see below for my SQL for the grid which works fine.
create or replace view VWC_PROPMKUNIT_TEST AS ( SELECT
PR_SNAM, PR_NAME, PR_ADD1, PR_ADD2, PR_ADD3, PR_ADD4, PR_ADD5, PR_ADD6,
PR_POST, PR_OWN, UN_UREF, UN_NAME, UN_GFA
FROM PROP
LEFT JOIN UNIT
ON PR_SNAM=UN_BREF);
All I need to do now is insert 'NO UNIT' within the Unit Reference column where it is NULL.
I get the following error when my insert has a DBNull value for a column of type varbinary(MAX). "Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query." In my opinion, the .NET SqlDataAdapter is attempting to convert the null value to a nvarchar. Is it possible to insert a null value in varbinary(max)? I didn't have this problem with the image datatype. Is this a bug or is there a work around to this problem?
Any help would be appreciated.
hii I have to insert some data into a table from the webpage. For that I need to know the last occurred value in the primary key and increment this by one and then insert the new record into the table. I am working with SQL Server 2005. I am coding in VB and ASP.NET 2.0. How will I go about this?? Is there some easy way for me to knw the last value of the primary key n then insert the record. It would be great if I get the idea more clear with the help of some code... Thanks
View 2 Replies View RelatedIm trying to add a new rcord to my db on a button click usign the following code
'data adapter
Dim dAdapt1 As New SqlClient.SqlDataAdapter
'create a command object
Dim objCommand As New SqlClient.SqlCommand
'command builder
Dim builderT As SqlClient.SqlCommandBuilder
'connection string
Dim cnStr As String = "Data Source=ELEARN-FRM-BETA;Initial Catalog=StudentPlayGround;Integrated Security=True"
'dataset
Dim dsT As DataSet
Private Sub connect()
'connection
objCommand.Connection = New SqlClient.SqlConnection(cnStr)
'associating the builder with the data adapter
builderT = New SqlClient.SqlCommandBuilder(dAdapt1)
'opening the connection
objCommand.Connection.Open()
'query string
Dim query As String = "SELECT * from StudentPlayground..Employees"
'setting the select command
dAdapt1.SelectCommand = New SqlClient.SqlCommand(query, objCommand.Connection)
'dataset
dsT = New DataSet("Trainee Listings")
dAdapt1.Fill(dsT, "Employees")
End Sub
Private Sub BindData()
connect()
DataBind()
End Sub
Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click
Dim empID As Integer = CType(FindControl("TextBox8"), TextBox).Text
BindData()
Dim firstName As String = CType(FindControl("TextBox1"), TextBox).Text
BindData()
Dim lastName As String = CType(FindControl("TextBox2"), TextBox).Text
BindData()
Dim location As String = CType(FindControl("TextBox3"), TextBox).Text
BindData()
Dim termDate As Date = CType(FindControl("TextBox4"), TextBox).Text
BindData()
Dim hireDate As Date = CType(FindControl("TextBox7"), TextBox).Text
BindData()
Dim dept As String = CType(FindControl("TextBox5"), TextBox).Text
BindData()
Dim super As String = CType(FindControl("TextBox6"), TextBox).Text
BindData()
Dim newRow As DataRow = dsT.Tables("Employees").NewRow
newRow.BeginEdit()
newRow.Item(0) = empID
newRow.Item(1) = firstName
newRow.Item(2) = lastName
newRow.Item(3) = location
newRow.Item(4) = hireDate
newRow.Item(5) = termDate
newRow.Item(6) = dept
newRow.Item(7) = super
newRow.EndEdit()
'do the update
Dim insertStr As String = "INSERT INTO Employees" + _
"(EmployeeID,FirstName,LatName,Location,HireDate,TerminationDate,Supervisor)" + _
"VALUES (empID,firstName,lastName,location,hireDate,termDate,dept,super)"
Dim insertCmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(insertStr, objCommand.Connection)
dAdapt1.InsertCommand() = insertCmd
dAdapt1.Update(dsT, "Employees")
'Dim insertCmd As new SqlClient.SqlCommand = (builderT.GetInsertCommand()).ToString())
'dAdapt1.InsertCommand = New SqlClient.SqlCommand(insertCmd.ToString(), objCommand.Connection)
BindData()
objCommand.Connection.Close()
objCommand.Connection.Dispose()
End Sub
im not sure wats going wrong because the record is not being added. Please help!!
Hi,As I wrote my message the solution came to me, so I thought I wouldpost anyway for others to see in case it was useful:Here is some sample DDL for this question:CREATE TABLE Source (my_value INT NOT NULL )GOINSERT INTO Source VALUES (1)INSERT INTO Source VALUES (2)INSERT INTO Source VALUES (3)GOCREATE TABLE Destination (value_type VARCHAR(10) NOT NULL,value INT )GOI would like to fill the destination with a row for the COUNT, SUM,MIN, and MAX. My own problem is of course much more complex than this,but this is the basic stumbling block for me now. So, the rows that Iwould expect to see in Destination are:value_type value---------- -----COUNT 3SUM 6MIN 1MAX 3The solution that I came up with was to add a Value_Types table:CREATE TABLE Value_Types (value_type VARCHAR(10) NOT NULL )GOINSERT INTO Value_Types VALUES ('COUNT')INSERT INTO Value_Types VALUES ('SUM')INSERT INTO Value_Types VALUES ('MAX')INSERT INTO Value_Types VALUES ('MIN')GONow the SQL is pretty simple:SELECT V.value_type,CASE V.value_typeWHEN 'COUNT' THEN COUNT(*)WHEN 'SUM' THEN SUM(S.my_value)WHEN 'MAX' THEN MAX(S.my_value)WHEN 'MIN' THEN MIN(S.my_value)ENDFROM Source SINNER JOIN Value_Types V ON 1=1-Tom.P.S. - I know that I did not include primary or foreign keys in my DDL.I'll leave it as an excercise to the reader to figure those out. Ithink the code adequately explains the concept.
View 3 Replies View RelatedHello, I'm trying to accomplish 3 things with one stored procedure.I'm trying to search for a record in table X, use the outcome of thatsearch to insert another record in table Y and then exec another storedprocedure and use the outcome of that stored procedure to update therecord in table Y.I have this stored procedure (stA)CREATE PROCEDURE procstA (@SSNum varchar(9) = NULL)ASSET NOCOUNT ONSELECT OType, Status, SSN, FName, LNameFROM CustomersWHERE (OType = 'D') AND (Status = 'Completed') AND (SSN = @SSNum)GO.Then, I need to create a new record in another table (Y) using the SSN,FName and Lname fields from this stored procedure.After doing so, I need to run the second stored procedure (stB) Here itis:CREATE PROCEDURE procstB( @SSNum varchar(9) = NULL)ASSET NOCOUNT ON-- select the recordSELECT OrderID, OrderDate, SSNFROM OrdersGROUP BY OrderID, OrderDate, SSNHAVING (ProductType = 'VVSS') AND (MIN(SSN) = @SSNum)GO.After running this, I need to update the record I created a moment agoin table Y with the OrderDate and OrderID from the second storedprocedure.Do you guys think that it can be done within a single stored procedure?Like for example, at the end of store procedure A creating an insertstatement for the new record, and then placing something like execprocstB 'SSN value'? to run stored procedure B and then having aupdate statement to update that new record?Thanks for all your help.
View 1 Replies View Related
A user inadvertantly deleted a record in a table that maintains an identity seed. I attempted to insert the record using SET IDENTITY_INSERT ON, but forgot that this table is an article in merge replication. I am attempting the insert on the publisher and I get the following message:
Msg 548, Level 16, State 2, Line 2
The insert failed. It conflicted with an identity range check constraint in database 'cad', replicated table 'dbo.emmain', column 'empl_id'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.
The statement has been terminated.
How can I re-insert this record??Thanks in advance for any help