Help! INSERT Replication not working on Subscriber FOR ONE TABLE ONLY

Everything is working great except ONE table is not replicating INSERTS from
the subscriber back to the publisher. UPDATES *are* being replicating, and
INSERTS *are* being replicated for every other table! I can't figure out
what's going on.
When I first setup the subscription/publication, the table did have a "int
identity" field. This field has now been removed but INSERTS are still not
being replicated.
Can someone please help!
Thanks!
-Ryan
Ben,
what type of replication are you using. Assuming it is transactional, have a
look at the article properties for the problematic table, and the commands
tab. Check that the replace Insert command isn't set to 'NONE'.
HTH,
Paul Ibison
|||Ben,
what type of replication are you using. Assuming it is transactional, have a
look at the article properties for the problematic table, and the commands
tab. Check that the replace Insert command isn't set to 'NONE'.
HTH,
Paul Ibison
|||Paul,
I'm using merge replication. What is the "commands tab"? I can't find this
in the articles properties page.
I ended up getting this thing to work by dropping the publication and
recreating it, then reinitializing my subscriber. But I'd still like to
konw why it wasn't working before. Did it have something to do with the INT
IDENTITY field? Why didn't it work after I deleted this field?
(btw, this is Ryan, my last message was sent from a coworker's computer)
Thanks, Ryan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eVpz8BbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Ben,
> what type of replication are you using. Assuming it is transactional, have
a
> look at the article properties for the problematic table, and the commands
> tab. Check that the replace Insert command isn't set to 'NONE'.
> HTH,
> Paul Ibison
>
|||Paul,
I'm using merge replication. What is the "commands tab"? I can't find this
in the articles properties page.
I ended up getting this thing to work by dropping the publication and
recreating it, then reinitializing my subscriber. But I'd still like to
konw why it wasn't working before. Did it have something to do with the INT
IDENTITY field? Why didn't it work after I deleted this field?
(btw, this is Ryan, my last message was sent from a coworker's computer)
Thanks, Ryan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eVpz8BbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Ben,
> what type of replication are you using. Assuming it is transactional, have
a
> look at the article properties for the problematic table, and the commands
> tab. Check that the replace Insert command isn't set to 'NONE'.
> HTH,
> Paul Ibison
>
|||Ryan,
I don't know what was wrong previously. As you are using merge, I would have
checked that the merge trigger was firing and that the record in
msmerge_contents was being inserted. Then there are dummy updates and the
use of profiler to track what's happening. Anyway, if this crops up again
please report back and we can investigate it then.
Regards,
Paul Ibison
|||Ryan,
I don't know what was wrong previously. As you are using merge, I would have
checked that the merge trigger was firing and that the record in
msmerge_contents was being inserted. Then there are dummy updates and the
use of profiler to track what's happening. Anyway, if this crops up again
please report back and we can investigate it then.
Regards,
Paul Ibison

HELP! insert record

I'm doing a program by using Java to insert record into SQL file. it's a registration program. but i'm not familiar with SQL, i got an error msg "An attempt was made to insert a null value into a column that does not accept nulls. " and i cant solve it. my SQL file contains

connect 'jdbc:rmi://localhost:1099/jdbc:cloudscape:customers;create=true'
;

drop table custProducts
;
drop table products
;
drop table custinfor
;

create table custinfor (
userName varchar (20) NOT NULL,
firstName varchar (20) NOT NULL,
lastName varchar (30) NOT NULL,
email varchar (30) NOT NULL,
password varchar (20) NOT NULL,
verify varchar (20) NOT NULL,
constraint pk_custinfor primary key (userName)
)
;
create table products (
productID int DEFAULT AUTOINCREMENT,
productName varchar (100) NOT NULL,
description varchar (100) NOT NULL,
price real NOT NULL,
imageFile varchar (50) NOT NULL,
constraint pk_products primary key (productID)
)
;
create table custProducts (
userName varchar (20) NOT NULL,
productID int NOT NULL,
constraint fk_custProducts_1 foreign key (userName)
references custinfor (userName),
constraint fk_custProducts_2 foreign key (productID)
references products (productID)
)
;
insert into custinfor (userName,firstName,lastName,email,password,verify ) values ('jSmith','John','Smith','sjohn@.hotmail.com','jsmi th123','jsmith123')
;
insert into custinfor (userName,firstName,lastName,email,password,verify ) values ('jess88','Jess','Ling','jess88@.yahoo.com','jess45 6','jess456')
;

insert into products (productName,description,price,imageFile) values ('Desktop-1','Intel Pentium 4 Processor 1.8Gb,256MB RAM',2998.00,'pentium4 processor.jpg')
;
insert into products (productName,description,price,imageFile) values ('Desktop-2','Intel Celeron Processor,256MB RAM',2260.00,'pentium4 processor.jpg')
;

insert into custProducts (userName,productID) values ('jSmith',1)
;
insert into custProducts (userName,productID) values ('jess88',2)
;

can someone tell me what's the mistake that i made?Hi:

Look... your problem is easy.
Please, try to execute one insert at the time and then you can figure which sentence has the problem.
One of them is tryng to insert a null value in a null column.|||At first glance, everything in your SQL looks good. I notice that you probably should handle the users' password verification in the UI instead of the DB, but that's a minor nit-pick.

Which statement gives you the error?|||thanks moku and avarta. i cancelled the some of the NOT NULL and it works. thanks.

Help! Insert a record into SQL database with ASP.net and C#

I'm trying to make a website that people can upload file to the server, then the webpage will automatically insert the username and the file name into the database. I have thought about this for couple days but still got nothing. can some one help me?

can some one post a very simple aspx file that can insert a record to the sql database with C#? Not the code that generated by ASP.net 2.0!

I want to see how to access the database manually.

Thank you.

A quick example:

protected void Button1_Click(object sender, EventArgs e)
{
string connectionString = @."Data Source=Confute;Initial Catalog=AdventureWorks;Integrated Security=SSPI";

using (SqlConnection connection = new SqlConnection(connectionString))
{

SqlDataSource sds = new SqlDataSource(connectionString, "select * from Orders (where EmployeeID in (@.list))");

SqlCommand cmd = new SqlCommand("insert into t1 (name) select @.name", connection);

//add text of txtBox_Name as input parameter for the insert command
cmd.Parameters.Add("name", txtBox_Name.Text);

connection.Open();
cmd.ExecuteNonQuery();
}

}

|||It is really help! Thank you very much

Help! How to insert multiple rows into Database??

I keep getting this error but it will only insert the 1st row into my database table

The variable name '@.CustId' has already been declared. Variable names must be unique within a query batch or stored procedure.

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Dim drow As GridViewRow

For Each drow In GridView1.Rows

Dim textBoxText As String = CType(drow.FindControl("Label2"), Label).Text

SqlDataSource2.InsertParameters.Add("CustId", TypeCode.String, Profile.UserName)

SqlDataSource2.InsertParameters.Add("OrderDate", TypeCode.DateTime, DateTime.Now.ToString)

SqlDataSource2.InsertParameters.Add("Total", TypeCode.Double, TotalUnitPrice)

SqlDataSource2.InsertParameters.Add("Quantity", TypeCode.Int32, textBoxText)

SqlDataSource2.Insert()

Next

Response.Redirect("checkout.aspx")

End Sub

since you are operating in a loop, you should clear your parameters collection on each pass through

Dim drowAs GridViewRowFor Each drowIn GridView1.RowsDim textBoxTextAs String =CType(drow.FindControl("Label2"), Label).Text SqlDataSource2.InsertParameters.Clear()'<-- Clear the params first SqlDataSource2.InsertParameters.Add("CustId", TypeCode.String, Profile.UserName) SqlDataSource2.InsertParameters.Add("OrderDate", TypeCode.DateTime, DateTime.Now.ToString) SqlDataSource2.InsertParameters.Add("Total", TypeCode.Double, TotalUnitPrice) SqlDataSource2.InsertParameters.Add("Quantity", TypeCode.Int32, textBoxText) SqlDataSource2.Insert()Next

|||

Try this after your insert:

SqlDataSources2.InsertParameters.Clear();

There is another way to do this. Declare your parameters first, then assign the InsertParameters' default values for each InsertParameter after the insert.

SqlDataSource2.InsertParameters("Parameter1").DefaultValue=valueofParameter1

...

|||Omg .. thanks a lot ! It works... Haha ...

Help! Cannot Insert Null

Hello Everyone,
This is my first time here.
I have been trying to turn off the "null" property for my id column.
And then I put yes for identity.
when i go to save it i get the error message,
"Cannot insert the value NULL into column 'ID'"
Can someone please help me out with this, its probably something simple, but i just cant figure it out.

Thanks,
PJIdentity values are create automatically by the server. They cannot be NULL.|||for some reason, the property in the ID column showed as "null"
but it should NOT be
when i uncheck the box for null and set the identity for yes, i get that error.
i have a username/password thing on the website, and when someone registers, it DOES insert all the information from the form, but under the ID column, instead, where it USED to give it a number, it says <null>.
i can go in manually and enter a number, and then it works, but i need the ID to be automatically added.|||A check in the "Allow Nulls" column indicates that the field will accept Null values, not that it will exclude them.

You cannot set the Identity property on a field that allows Nulls. If you try, it will automatically set the field to disallow null values.|||I happened to figure this out
What happened:
There were already null "id's" created/inserted into the table
you have to delete those that were created by null, and THEN you can uncheck the "allow nulls" and set the identity to yes
My whole problem was trying to NOT allow nulls, but once nulls are created in the table, you cannot uncheck the box

Thank you so much for your help though.

-PJ

Help! Bad insert to a table which I can't delete now.

I made a mistake in my insert statement, the insert to destination table is
the same as the Source from table. Oops! Now I can't truncate, delete this
table at all. Any way to get rid of it and start again?
Thanks, Alpha
insert into
[TisSuite].[dbo].tblSource_info(extTID,RelatedEmployment,RelatedAutoAccident,RelatedOtherAccident,RelatedNotAccident,
ReleasePatientInfo,PlaceOfService,TypeOfService, EMG,COB)
--We default to not releasing patient info
select exttid,0,0,0,1,0,1,41,'','' from [TisSuite].[dbo].tblPCS order by
exttidOops, sorry. I was able to delete the table after all.
"Alpha" wrote:
> I made a mistake in my insert statement, the insert to destination table is
> the same as the Source from table. Oops! Now I can't truncate, delete this
> table at all. Any way to get rid of it and start again?
> Thanks, Alpha
> insert into
> [TisSuite].[dbo].tblSource_info(extTID,RelatedEmployment,RelatedAutoAccident,RelatedOtherAccident,RelatedNotAccident,
> ReleasePatientInfo,PlaceOfService,TypeOfService, EMG,COB)
> --We default to not releasing patient info
> select exttid,0,0,0,1,0,1,41,'','' from [TisSuite].[dbo].tblPCS order by
> exttid
>

Help! Bad insert to a table which I can't delete now.

I made a mistake in my insert statement, the insert to destination table is
the same as the Source from table. Oops! Now I can't truncate, delete this
table at all. Any way to get rid of it and start again?
Thanks, Alpha
insert into
[TisSuite].[dbo]. tblSource_info(extTID,RelatedEmployment,
RelatedAuto
Accident,RelatedOtherAccident,RelatedNot
Accident,
ReleasePatientInfo,PlaceOfService,TypeOf
Service, EMG,COB)
--We default to not releasing patient info
select exttid,0,0,0,1,0,1,41,'','' from [TisSuite].[dbo].tblPCS orde
r by
exttidOops, sorry. I was able to delete the table after all.
"Alpha" wrote:

> I made a mistake in my insert statement, the insert to destination table i
s
> the same as the Source from table. Oops! Now I can't truncate, delete th
is
> table at all. Any way to get rid of it and start again?
> Thanks, Alpha
> insert into
> [TisSuite].[dbo]. tblSource_info(extTID,RelatedEmployment,
RelatedAu
toAccident,RelatedOtherAccident,RelatedN
otAccident,
> ReleasePatientInfo,PlaceOfService,TypeO
fService, EMG,COB)
> --We default to not releasing patient info
> select exttid,0,0,0,1,0,1,41,'','' from [TisSuite].[dbo].tblPCS o
rder by
> exttid
>

Help! Bad insert to a table which I can't delete now.

I made a mistake in my insert statement, the insert to destination table is
the same as the Source from table. Oops! Now I can't truncate, delete this
table at all. Any way to get rid of it and start again?
Thanks, Alpha
insert into
[TisSuite].[dbo].tblSource_info(extTID,RelatedEmployment,RelatedAu toAccident,RelatedOtherAccident,RelatedNotAccident ,
ReleasePatientInfo,PlaceOfService,TypeOfService, EMG,COB)
--We default to not releasing patient info
select exttid,0,0,0,1,0,1,41,'','' from [TisSuite].[dbo].tblPCS order by
exttid
Oops, sorry. I was able to delete the table after all.
"Alpha" wrote:

> I made a mistake in my insert statement, the insert to destination table is
> the same as the Source from table. Oops! Now I can't truncate, delete this
> table at all. Any way to get rid of it and start again?
> Thanks, Alpha
> insert into
> [TisSuite].[dbo].tblSource_info(extTID,RelatedEmployment,RelatedAu toAccident,RelatedOtherAccident,RelatedNotAccident ,
> ReleasePatientInfo,PlaceOfService,TypeOfService, EMG,COB)
> --We default to not releasing patient info
> select exttid,0,0,0,1,0,1,41,'','' from [TisSuite].[dbo].tblPCS order by
> exttid
>

Help with weird error

I am working with Access frontends and a SQL Server 2000 backend.
One user gets the following error when trying to insert directly into
a table through access or through a form into that same table: "String
or binary data would be truncated". It doesn't matter into which type
of column the data is entered. Int, datetime, varchar all produce the
same error.
This user has access to the db this table is stored in and can write
to other tables in that db with no problem. In fact, none of the other
users with the same permission settings to this db has this problem.
They can insert into or update this same table just fine.
This is also pc independent for this user. It does seem to be a matter
of account setting though, because none of the other users has this
problem.
What could be wrong? Does anybody have any ideas? As far as I know
this user has the exact same permission setting as others. What else
could I look into to solve this mystery?
Thanks for your help.> One user gets the following error when trying to insert directly into
> a table through access or through a form into that same table: "String
> or binary data would be truncated".
This sounds like a string is attempting to be inserted but it is too long to
fit in the column. This is not a "weird" error, in fact it is quite common.
I highly doubt it has anything to do with this user or his/her permissions,
unless there is a trigger on the table and the failure is occurring because
his domain username is too long to fit into the auditing table.|||I fixed the problem. As it turned out, for this particular user the
username is saved with its domain name. The table field to which this
user info is saved wasn't large enough to hold both the username and
domain name.
Thanks for your reply, Aaron. As you predicted, the username was too
long to be stored.
On Mar 11, 9:33 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > One user gets the following error when trying to insert directly into
> > a table through access or through a form into that same table: "String
> > or binary data would be truncated".
> This sounds like a string is attempting to be inserted but it is too long to
> fit in the column. This is not a "weird" error, in fact it is quite common.
> I highly doubt it has anything to do with this user or his/her permissions,
> unless there is a trigger on the table and the failure is occurring because
> his domain username is too long to fit into the auditing table.

Help with weird error

I am working with Access frontends and a SQL Server 2000 backend.
One user gets the following error when trying to insert directly into
a table through access or through a form into that same table: "String
or binary data would be truncated". It doesn't matter into which type
of column the data is entered. Int, datetime, varchar all produce the
same error.
This user has access to the db this table is stored in and can write
to other tables in that db with no problem. In fact, none of the other
users with the same permission settings to this db has this problem.
They can insert into or update this same table just fine.
This is also pc independent for this user. It does seem to be a matter
of account setting though, because none of the other users has this
problem.
What could be wrong? Does anybody have any ideas? As far as I know
this user has the exact same permission setting as others. What else
could I look into to solve this mystery?
Thanks for your help.
> One user gets the following error when trying to insert directly into
> a table through access or through a form into that same table: "String
> or binary data would be truncated".
This sounds like a string is attempting to be inserted but it is too long to
fit in the column. This is not a "weird" error, in fact it is quite common.
I highly doubt it has anything to do with this user or his/her permissions,
unless there is a trigger on the table and the failure is occurring because
his domain username is too long to fit into the auditing table.
|||I fixed the problem. As it turned out, for this particular user the
username is saved with its domain name. The table field to which this
user info is saved wasn't large enough to hold both the username and
domain name.
Thanks for your reply, Aaron. As you predicted, the username was too
long to be stored.
On Mar 11, 9:33 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> This sounds like a string is attempting to be inserted but it is too long to
> fit in the column. This is not a "weird" error, in fact it is quite common.
> I highly doubt it has anything to do with this user or his/her permissions,
> unless there is a trigger on the table and the failure is occurring because
> his domain username is too long to fit into the auditing table.