|
Use DBCC CHECKIDENT (tablename, RESEED, 9999). This would reset the next identity to 10000 on the table in question.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Use DatabaseName
DBCC CHECKIDENT (tablename, RESEED, 0)
OutPut : reset the column identity next value start to 1
Mitesh Darji
|
|
|
|
|
Hi there,
Well it seems I have stumbled upon a rather basic problem and I just cant seem to work it out. I have written a simple update query as follows:
<br />
private readonly string ACCESS_CUSTOMER_UPDATE = "UPDATE Customer SET Customer.Name = @Name,Customer.Age = @Age WHERE Customer.ID = @ID";<br />
I have used the approach to execute the query in the method listed below. For the life of me I cant seem to work out why the update wont be taking place. I have executed the query listed above in Access without any problems but when encapsulated in the method below it just wont update.
I'd appreciate if someone could point out the mistake I have overlooked.
Thanks
<br />
private string PARAM_ID = "@ID";<br />
private string PARAM_NAME = "@Name";<br />
private string PARAM_AGE = "@Age";<br />
<br />
private string connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Temp\\temp.mdb";<br />
<br />
private OleDbConnection connection = new OleDbConnection(connectionstring);<br />
<br />
public void UpdateCustomer(CustomerInfo customer)<br />
{<br />
OleDbParameter[] parameters = new OleDbParameter[]{<br />
new OleDbParameter(PARAM_ID,OleDbType.Integer),<br />
new OleDbParameter(PARAM_NAME,OleDbType.VarChar),<br />
new OleDbParameter(PARAM_AGE,OleDbType.Integer)};<br />
<br />
parameters[0].Value = customer.ID;<br />
parameters[1].Value = customer.Name;<br />
parameters[2].Value = customer.Age;<br />
<br />
OleDbCommand command = new OleDbCommand();<br />
command.Connection = this.connection;<br />
command.Connection.Open();<br />
command.CommandText = "UPDATE Customer SET Customer.Name = @Name,Customer.Age = @Age WHERE Customer.ID = @ID";<br />
<br />
for (int i = 0; i < parameters.Length; i++)<br />
{<br />
OleDbParameter parm = (OleDbParameter)parameters[i];<br />
command.Parameters.Add(parm);<br />
}<br />
<br />
try<br />
{<br />
int x = command.ExecuteNonQuery();<br />
Debug.WriteLine(x.ToString());<br />
}<br />
catch (Exception e)<br />
{<br />
throw new Exception(e.Message, e.InnerException);<br />
}<br />
finally<br />
{<br />
command.Connection.Close();<br />
command.Dispose();<br />
}<br />
}<br />
edit:
Added the connection string.
|
|
|
|
|
Can you also write the error which you are getting?
Arun Singh
Noida.
|
|
|
|
|
I think that you're going to find that the order of the parameters going into your oledbcommand for access is important...
|
|
|
|
|
I am getting no error. The output of ExecuteNonQuery is an "int" type. The value of this int variable indicates how many rows have been modified. In my case its zero when infact it should be one. I have one row of data in the table that needs to be updated so it should return 1 not 0.
I can verify that order of parameters matches what is in the table.
Any other suggestions I can try out ?
Edit:
Running the code like this works fine:
<br />
public void UpdateCustomer(CustomerInfo customer)<br />
{<br />
string query = "UPDATE CUSTOMER SET Name = " + "'" + customer.Name + "'," + "Age = " + customer.Age + " WHERE ID = " + customer.ID;<br />
OleDbCommand command = new OleDbCommand();<br />
command.CommandText = query;<br />
command.Connection = this.connection;<br />
command.Connection.Open();<br />
try<br />
{<br />
int x = command.ExecuteNonQuery();<br />
Debug.WriteLine(x.ToString());<br />
}<br />
catch (Exception ex)<br />
{<br />
throw new Exception(ex.Message, ex.InnerException);<br />
}<br />
finally<br />
{<br />
command.Connection.Close();<br />
command.Dispose();<br />
}<br />
}<br />
|
|
|
|
|
UPDATE Customer SET Customer.Name = @Name,Customer.Age = @Age WHERE Customer.ID = @ID
private string PARAM_ID = "@ID";
private string PARAM_NAME = "@Name";
private string PARAM_AGE = "@Age";
What i think you'll find is happening here is that the sql statement is being executed and is trying to set name = id, age = name where id = age. Awkward problem.
|
|
|
|
|
Your problem is with the order of your parameters. System.Data.Oledb does not work like sqlclient (or Access internally) The parameter names are irrelevant, only order matters.
change your parameters to:
parameters[0].Value = customer.Name;
parameters[1].Value = customer.Age;
parameters[2].Value = customer.ID;
So that they match the order of occurrence in the query.
|
|
|
|
|
Thanks for the tip, I'll have to makesure I am more observant next time 
|
|
|
|
|
i have two tables .They are related by primary key,foreign key constrain.If i want to delete few rows in primary table without deleting corresponding rows in foreign key table . How will i do it.
|
|
|
|
|
hey whenever u delete a row it is copied somewhere exactly i dont know but we can retrive it back do some R and D u can do it easily
its me sid
|
|
|
|
|
How does this relate to the question being asked? The question was about the primary/foreign key contraint that exists between the 2 tables, not about retrieving the deleted rows from the primary key table.
Paul Marfleet
|
|
|
|
|
Try and give your posts more meaningful titles. 'Delete' is too vague. Read the forum guidelines.
Why would you want to do this if you have defined constraints between the 2 tables? One of the reasons for defining such a constraint is to prevent orphaned records remaining in the foreign key table when a primary key record is deleted. If you don't want such a constraint to exist, remove it.
Paul Marfleet
|
|
|
|
|
ritu4321 wrote: If i want to delete few rows in primary table without deleting corresponding rows in foreign key table . How will i do it.
Don't define any constraints between the two tables
|
|
|
|
|
i want to keep the constrain as it is but want to delete the row how will i do it.
|
|
|
|
|
ritu4321 wrote: i want to keep the constrain as it is but want to delete the row how will i do it.
Then what will be use of the constraint ? Why you need to do this ? Once the delete cascade is given, it will remove the foreign key values too.
|
|
|
|
|
|
Do you understand the purpose of such a constraint? It is there to enforce referential integrity between 2 tables, so values in the foreign key field in the related table must exist in the primary key field in the main table. If you don't want to enforce referential integrity, delete the constraint. It doesn't serve any purpose otherwise.
Paul Marfleet
|
|
|
|
|
Once values should the child table's contain i.e. when the parent key value is removed then what value should be there in child key.
can you give more clarity with some examples
Regards
KP
|
|
|
|
|
hi i know that storing a image in a database is not a correct option but requirement is like that so if anyone is having any idea please let me know
i know few thing abt access that the datatype must be ole object and i know how to get back the image so storing is only the problem
i hope i am clear
thank u
sindhu tiwari
its me sid
|
|
|
|
|
sindhutiwari wrote: so storing is only the problem
What about googling[^] it ?
|
|
|
|
|
thank's for the help but i need a code in c# not the complete one even the glimpse of the code will be sufficient i am confused weather to convert the photo in a byte stream or directly saving the object if u have any idea let me know
sindhu tiwari
its me sid
|
|
|
|
|
sindhutiwari wrote: am confused weather to convert the photo in a byte stream or directly saving the object
You need a byte stream. But I think storing images in access database may remove the meta data for the image.
|
|
|
|
|
Please don't cross post.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
HI : The Given Code is to store the specifications of the controls which are added to a form dynamically ,the database used is access ,here global is a class , lsave & i are variables
there is no syntax error at complile time but runtime a error is coming i.e Syntax error in INSERT INTO statement . The database is absolutely fine with correct syntax...
clue's please........................................
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = WindowsApplication1.Properties.Settings.Default.printsoftwareConnectionString;
connection.Open();
//loop for saving the positions of the labels
OleDbDataAdapter oda = new OleDbDataAdapter("select * from controlspecifications",connection);
OleDbCommandBuilder cmb1 = new OleDbCommandBuilder(oda);
DataSet ds1 = new DataSet("controlspecifications");
oda.Fill(ds1, "controlspecifications");
DataRow dr = ds1.Tables["controlspecifications"].NewRow();
for (lsave = 0; lsave <= i; lsave++)
{
dr[0] = global.projectname + "adsfsd";
dr[1] = lab[lsave].Name;
dr[2] = Convert.ToInt32(lab[lsave].Left);
dr[3] = Convert.ToInt32(lab[lsave].Top);
dr[4] = Convert.ToInt32(lab[lsave].Width);
dr[5] = Convert.ToInt32(lab[lsave].Height);
dr[6] = lab[lsave].Text;
dr[7] = lab[lsave].Font.FontFamily;
dr[8] = Convert.ToInt32(lab[lsave].Font.Size);
dr[9] = lab[lsave].ForeColor.ToString();
dr[10] = lab[lsave].BackColor.ToString();
if (lab[lsave].Font.Bold == true)
{
dr[11] = "bold";
}
else if (lab[lsave].Font.Italic == true)
{
dr[11] = "italic";
}
else if (lab[lsave].Font.Underline == true)
{
dr[11] = "underline";
}
else
{
dr[11] = "strikeout";
}
dr[12] = "dasfsad";
ds1.Tables["controlspecifications"].Rows.Add(dr);
oda.Update(ds1, "controlspecifications");
oda.Fill(ds1, "controlspecifications");
sindhu tiwari
its me sid
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
