|
"Select Count(*) as Records from MYtable"
if Records == 0, then insert, else update.
Actually, I am baffeled that yo want to update records without knowing which record or how many records.
Since Update statements are intended for changing the values of some fields in some specific existing records, it doesn't usually make much sense to blindly update everything (all records), which is what your query does since it has no 'where' clause to selecect specific records to update. Your query won't generate an error if there are no records, it will just gracefully return a rows effected variable containing 0.
If there are 1 million rows, it will change them all if it can, again without returning an error.
If there is a question as toi whether or not a specific record exists, one would normalkly do something like:
strQuery = "If NOT Exists(select x from MyTable where x = 23) Inset into MyTable (x,y) values(23,'this record was missing') ELSE update Mytable set y ='the record was there' where x = 23"
Myconnection.Execute(strQuery....
An even better practice is to make queries like this into stored procedures with appropriate parameters, and execute the sp, passing ony the parameters needed (23 in this example)
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
Well Rob. After looking at my post, I realized that and UPDATE is not what I wnated to do. This was before I read your last post. I realized that I wanted to do an insert.
I guess my question pertained to a thought I had. Here it is, when I create a new table, all fields are blank. Let say that there should be records in this table but they are not in there yet. (This is based on the fact that we do a lot of conversion from C-ISAM databases into SQL database) Should I always be checking to see if there are records in the table before I do an update? That was the question. Because if there where no records to update then it would error out.
I guess in my try, catch I could do a try update and if error catch exception and do insert. But that is assuming that it errored based on the idea that there where no records to update. Bad form. So hence my post, is there a way to determine when to do an update or an insert.
In my case I found out that i should always be doing an insert.
Thanks for the help
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
Is there a way to capture the sql server messages? I have some reporting software that only works if rows are returned and I have some Sp that I want to run from this software that return no rows just server messages. I can turn off the check for rows in the software (just an if statement) but can’t figure out how to capture server messages.
Thanks for any help
Ronald Hahn, CNT - Computer Engineering Technologist
New Technologies Analyst
HahnTech Affiliated With Code Constructors
Edmonton, Alberta, Canada
Email: rhahn82@telus.net
|
|
|
|
|
http://www.588188.com/netbook/sqlserver2000/adosql/adoprg03_59pr.htm
|
|
|
|
|
I have a VB.net standalone application. I want a method of merging a table from the database application with a master database. Replication is not an option.
I have tried dumping the table from db1 into a dataset (dataset1), and then the same table from db2 into a dataset (dataset2). Will using the dataset merge method combine the data to enable me to update the master.
IE.
dataset1.Merge(dataset2)
dataset1.acceptchanges
dataAdapter1.Update(dataset1, "Table1")
It seems that dataset1 gets dataset2's data appended, but the datasource does not update.
Please advise a method to do this.
|
|
|
|
|
The datasource is not getting updated becasue you called dataset1.AcceptChanges
When AcceptChanges is called all the rows in the dataset are marked as unmodified so when the data adapter assesses which rows to update, insert and delete it finds all rows are unmodified and thus thinks it does not need save them.
|
|
|
|
|
OK,
Rem'd this line out. To enable some debugging I load the page with 4 data grids. the first data grid with 1st dataset, second with 2nd dataset, third with the merge results and a fourth with any changes to 1st dataset if any.
Here's my code:
'Merge data
dstMBR.Merge(dstMBR1)
'Get any changes encountered in original dataset
dstMBR2 = dstMBR1.GetChanges
'dstMBR.acceptchanges
'Bind merged dataset to datagrid
dgrd2.DataSource = dstMBR1
dgrd2.DataBind()
'Bind changes(if any)to datagrid
dgrd3.DataSource = dstMBR2
dgrd3.DataBind()
'Update data source
dadMBR.Update(dstMBR, "users")
'More checks for modified data
If not dstMBR.GetChanges(DataRowState.Modified) Is Nothing Then
lblStatus.Text = "Modified"
End If
If not dstMBR.GetChanges(DataRowState.Added) Is Nothing Then
lblStatus.Text = "Add"
End If
If not dstMBR.GetChanges(DataRowState.Deleted) Is Nothing Then
lblStatus.Text = "Del"
End If
End Sub
You'll notice that acceptchanges is remarked out
|
|
|
|
|
From reading the MS Help, it looks like the rows of the merged datasets retain their DataRowState from before the merge, so that the "new" datarows would not show as "Added" they probably are showing "Unchanged" since you haven't changed anything in the rows. Then when the Update is done, nothing is changed in the datasource because all the rows in the merged dataset are "Unchanged."
|
|
|
|
|
Anybody know of a work around?
|
|
|
|
|
Copying the changes/additions from the second dataset into the first dataset and then updating the data source.
Fill datasets
if dataset2 only has additions to dataset1
...for each row
.....read row in dataset2
.....create new row in dataset1
.....fill new row with row from dataset2
Update datasource
if dataset2 contains changes to dataset1
...for each row
.....compare row in dataset1 with row in dataset2
.....If there are changes
.......make the changes in dataset1
Update datasource
|
|
|
|
|
Thanks,
Seem to have the additions working, but problems with comparing rows. What is the correct syntax for comparing datarows. Do I have to compare the value of each column in the row, or can I compare the row as a whole.
|
|
|
|
|
I'm just learning all this myself and I would have to compare each column in the rows. Maybe someone else knows of a compare method. A new thread with that question as the topic might get more notice, either here or under the language you are using.
|
|
|
|
|
Hi,
I created a database (sql) and added tables and stored procedures to it using the .NET server explorer.
Now I want to use the database created in a real server (thus move it from my development PC to a real web server).
What is the way to do it? Do I need to create a database project to do that?
Why does the .NET have databse projects if all can be done in the server explorer?
If anyone knows any article about the subject please let me know.
Thanks.
avivhal
|
|
|
|
|
Use DTS or simply sp_detach_db, copy the mdf & ldf files to the new server, and sp_attach_db.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Hi all,
I'm really frustrated...
I used VS.NET's wizard to create an OdbcConnection object. The object uses a system DSN I created, that points to an MS-Access 2003 database. In the database I have a table called Table1, with some fields. One of the fields is the primary key (autonumber). Now I want to create an OdbcDataAdapter object that selects/inserts/deletes/updates Table1 (a table in my db).
I use the data adapter wizard, select all my rows and click "next". The SELECT command is created, but the others are not. The wizard shows me the error in the subj for those commands.
If I switch to OleDbConnection instead of OdbcConnection, everything's ok.
I saw some articles about this bug - something about the db name containing chars like '.'(dot) so how can I avoid it? The file name is "db1.mdb". I can't remove the dot!
thanks.
/=/=/=/= Sagmam =\=\=\=\
|
|
|
|
|
Hi to All !
Can any one pls briefly explain me how to update data using dataset?
I mean i need to create dataset manually thru code, fill the dataset, update an existing record using sqldataadapter's update method.
Thanks for the valuable help
|
|
|
|
|
There is actually a number of steps to doing it manually. You need to make the connection, make the dataAdaptor, make and fill the dataSet. You might check out a a good ADO.NET book and look under untyped datasets.
|
|
|
|
|
I have a Microsoft Access 2000.
How can I set the AllowZeroLength value for a field using an SQL statment?
Thanks
|
|
|
|
|
In my opinion, I think the check box of Allow Null is the place similar to AllowZeroLength in ms access.
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Hi
I have a local computer(its name is SUN) with sqlserver2000 , IIS and VS.Net.
I have a web app name it WebApplication1 and a database name it Northwind.
When i run this webapplication it runs this portion of code:
System.Data.SqlClient.SqlConnection this.sqlConnection1;
private void Page_Load(object sender, System.EventArgs e)
{
this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
this.sqlConnection1.ConnectionString = "workstation"+
"id=SUN;packet size=4096;integrated security=SSPI;"+
"initial catalog=Northwind;persist security info=False";
this.sqlConnection1.Open();
this.sqlConnection1.Close();
}
but i see this error:
Login failed for user 'SUN\ASPNET'.
In the IIS i cleared Anonymous access check and added user SUN\ASPNET to Northwind database in Enterprise Manager but error didn't fix;
In the same windows app this error doesn't exist.
What should i do plz?
|
|
|
|
|
Dear Programmers,
I have a small problem with sql stored procedure.Does sql storeprocedure
can return more than one recordset (for example say 2 recordsets).
If so can you pls explain me how to get those 2 recordsets through VB
I have already tried the same with single recordset.
|
|
|
|
|
|
Thanks for the solution
one more thing is that i want to get those recordsets through VB 6.0
Please help me
|
|
|
|
|
Use the RecordSet's NextRecordset() method
|
|
|
|
|
Dear Visual Basic & Access Experts
In my application program, I have a Access (version xp) form that calls a update event handler. In this handler I need to know if the user has clicked on the "Close" button of the form. What VBasic codes do we use to check what action triggers off the the chain of events that lead up to the update event & onwards? In particular I am interested to to check if the triggering event is the clicking of the "Close" button of the form; if so I will then write some code to handle this particular case. I tried to handle this via Close event handler but since it comes after the "Update" event, it does not work.
Please help...Cheers! Michael
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
