|
Hi,
I try to update Sql server DB with update from dataset but fails on the DataAdapter.Update with insert exception. here's my code :
<br />
<br />
try<br />
{<br />
<br />
sSQLSel = @"SELECT * From MidnightReadings";<br />
sSQLUpd = @"UPDATE MidnightReadings SET MR_Date = @MR_Date, Coal_Feeder_Tot = @Coal_Feeder_To" +<br />
"t, Coal_Silo_Level_Prct = @Coal_Silo_Level_Prct, Coal_Barn_Inv_Prct = @Coal_Barn" +<br />
"_Inv_Prct, Limestone_Feed_Total = @Limestone_Feed_Total, LS_Silo_Level_Prct = @L" +<br />
"S_Silo_Level_Prct, Ammonia_Totalizer = @Ammonia_Totalizer, Prop_to_Burn_Totalize" +<br />
"r = @Prop_to_Burn_Totalizer, TwentyFourHr_SO2_Reduct = @TwentyFourHr_SO2_Reduct," +<br />
" Limestone_Received = @Limestone_Received, Ammonia_Received = @Ammonia_Received," +<br />
" Prop_Received = @Prop_Received, Est_hours_Dry_Oper_Hours = @Est_hours_Dry_Oper_" +<br />
"Hours, Est_hours_Dry_Oper_Mins = @Est_hours_Dry_Oper_Mins, LAB_Sulfur_Content_Pr" +<br />
"ct = @LAB_Sulfur_Content_Prct, LAB_High_Heat_Val = @LAB_High_Heat_Val, CEMS_SO2_" +<br />
"Daily_Avg = @CEMS_SO2_Daily_Avg, SO2_Daily = @SO2_Daily, Raw_Water_Pump_Meter = " +<br />
"@Raw_Water_Pump_Meter, Reservoir_Level = @Reservoir_Level, Comments = @Comments," +<br />
" Date_Rec_Added = @Date_Rec_Added WHERE (MR_Date = @MR_Date)";<br />
<br />
<br />
SqlConnection myCon = new SqlConnection(sCon1);<br />
<br />
SqlDataAdapter da = new SqlDataAdapter(sSQLSel, myCon);<br />
<br />
da.UpdateCommand = new SqlCommand(sSQLUpd, myCon);<br />
<br />
<br />
buildMNUpdParams(da.UpdateCommand);<br />
da.Update(dsDiff,"MidnightReadings"); <- Fails here !<br />
dsDiff.AcceptChanges();<br />
myCon.Close();<br />
<br />
return true;<br />
<br />
public static void buildMNUpdParams(SqlCommand workCommand)<br />
{<br />
<br />
<br />
SqlParameter workParam = null;<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@MR_Date", SqlDbType.DateTime, 8));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "MR_Date";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@Coal_Feeder_Tot", SqlDbType.Decimal, 9));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "Coal_Feeder_Tot";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@Coal_Silo_Level_Prct", SqlDbType.Decimal, 9));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "Coal_Silo_Level_Prct";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@Coal_Barn_Inv_Prct", SqlDbType.Decimal, 9));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "Coal_Barn_Inv_Prct";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@Limestone_Feed_Total", SqlDbType.Decimal, 9));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "Limestone_Feed_Total";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@LS_Silo_Level_Prct", SqlDbType.Decimal, 9));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "LS_Silo_Level_Prct";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@Ammonia_Totalizer", SqlDbType.Decimal, 9));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "Ammonia_Totalizer";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@Prop_to_Burn_Totalizer", SqlDbType.Decimal, 9));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "Prop_to_Burn_Totalizer";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@TwentyFourHr_SO2_Reduct", SqlDbType.Decimal, 9));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "TwentyFourHr_SO2_Reduct";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@Limestone_Received", SqlDbType.Decimal, 9));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "Limestone_Received";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@Ammonia_Received", SqlDbType.Decimal, 9));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "Ammonia_Received";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@Prop_Received", SqlDbType.Decimal, 9));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "Prop_Received";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@Est_hours_Dry_Oper_Hours", SqlDbType.Int, 4));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "Est_hours_Dry_Oper_Hours";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@Est_hours_Dry_Oper_Mins", SqlDbType.Int, 4));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "Est_hours_Dry_Oper_Mins";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@LAB_Sulfur_Content_Prct", SqlDbType.Decimal, 9));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "LAB_Sulfur_Content_Prct";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@LAB_High_Heat_Val", SqlDbType.Decimal, 9));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "LAB_High_Heat_Val";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@CEMS_SO2_Daily_Avg", SqlDbType.Int, 4));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "CEMS_SO2_Daily_Avg";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@SO2_Daily", SqlDbType.Decimal, 9));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "SO2_Daily";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@Raw_Water_Pump_Meter", SqlDbType.Decimal, 9));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "Raw_Water_Pump_Meter";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@Reservoir_Level", SqlDbType.Decimal, 9));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "Reservoir_Level";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@Comments", SqlDbType.VarChar, 200));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "Comments";<br />
workParam = workCommand.Parameters.Add(new SqlParameter("@Date_Rec_Added", SqlDbType.DateTime, 8));<br />
workParam.Direction = ParameterDirection.Input;<br />
workParam.SourceColumn = "Date_Rec_Added";<br />
workParam = workCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@MR_ID", System.Data.SqlDbType.BigInt, 8, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "MR_ID", System.Data.DataRowVersion.Original, null));<br />
}<br />
<br />
Any ideas ?
Thanks,
JJ
|
|
|
|
|
Could you post the exception? That's a lot of code to look through with out any sort of clue as to where to start looking.
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
My Blog
|
|
|
|
|
I am getting an InvalidOperationException with a message of "Insert". That's it, pretty useless message. Only hints that it didn't like my Statement for update command I guess. I am using a diffgram dataset to update database.
JJ
|
|
|
|
|
If your DiffGram includes inserts, updates, and deletes, all the related properties on a DataAdapter must be assigned. So, if a row was updated and all you supplied was the InsertCommand, then you would see such an operation because the DataAdapter doesn't know how to update the data store.
Microsoft MVP, Visual C#
My Articles
|
|
|
|
|
HI Heath,
That's what I thought too. So in the method that I use to update db. I assigned the Insert command to DataAdapter along with the Update command to DataAdapter. Now it runs but when I execute the DataAdapter update method. I get a new row in database instead of actually updating same row. When I update the datarow with values how do I tell the diffram dataset to go to update mode as well?
Here I find the datarow I want to update in dataset:
<br />
nRow2 = dsDiff.Tables["MidnightReadings"].Select(sRecFind);<br />
It finds it in dataset with no problem
Then I update datarow:
<br />
nRow2[0]["Coal_Feeder_Tot"] = tbCoalFeederTot.Text;<br />
nRow2[0]["Coal_Silo_Level_Prct"] = tbCoalSiloPrct.Text;<br />
nRow2[0]["Coal_Barn_Inv_Prct"] = tbCoalBarnInvPrct.Text;<br />
nRow2[0]["Limestone_Feed_Total"] = tbLimeFeedTot.Text;<br />
I checked the dataset and changes are there but here is the status of row:
<br />
<MidnightReadings diffgr:id="MidnightReadings2" msdata:rowOrder="1" diffgr:hasChanges="inserted"><br />
<MR_Date>2004-06-10T00:00:00.0000000-04:00</MR_Date><br />
<Coal_Feeder_Tot>8789</Coal_Feeder_Tot><br />
<Coal_Silo_Level_Prct>95.5</Coal_Silo_Level_Prct><br />
<Coal_Barn_Inv_Prct>75.8</Coal_Barn_Inv_Prct><br />
<Limestone_Feed_Total>0</Limestone_Feed_Total><br />
<LS_Silo_Level_Prct>0</LS_Silo_Level_Prct><br />
...<br />
This dataset already exists in db but keeps inserting new records.
What am I missing?
Thanks,
JJ
|
|
|
|
|
Make sure the row information contains a primary key so that the UpdateCommand can effectively use a WHERE clause so that the right row is updated.
Microsoft MVP, Visual C#
My Articles
|
|
|
|
|
Hi Heath,
Ok really frustrated with trying to create a diffgram dataset.
I assume that I should first create a DataAdapter then build SqlCommand,InsertCommand, UpdateCommand for Adapter, Correct?
Next build parameters for each Commmand, Correct? After that I can then
Fill dataadapter, correct? Then I write my diffgram out to a file.
Does this sound like the correct approach to this?
After I do this I then take dataset and do inserts, updates etc. to
dataset. After done with this process I go back create a new dataadapter with whats mentioned above and call DataAdapter.Update.
What do you think? I did away with the webservice and have the create diffgram and database updates in a separate class.
Thanks,
JJ
|
|
|
|
|
To call Fill, you only need the SelectCommand set. When you call DataAdapter.Update, however, you may need all four set depending on what changes have been made. After calling Update, though, there will be no changes because the DataAdapter then calls AcceptChanges on your DataSet or DataTable (whichever you passed in).
So, you really only need to write out a DiffGram (if that's really necessary) after you make changes, otherwise those won't be any differences.
Microsoft MVP, Visual C#
My Articles
|
|
|
|
|
See I need the diffgram because this is a pda app that is only sometimes connected to DB. So I need a way to record data to pda then update the database when a connection can be made. So I thought the diffgram would be the easiest way to accomplish this.
JJ
|
|
|
|
|
I was wondering if it was possible to import a comma delimited text file into a access database using c#? If someone could help me out that would be great.
Thanks.
|
|
|
|
|
Of course it's possible. Create an OleDbConnection to an MDB database file, then create an OleDbCommand from that with something like INSERT INTO MyTable VALUES(?, ?, ?, ...) for the CommandText. Then create an OleDbParameter for each ?.
Open the comma-delimited text file and split the strings on , (if you have other commas in the text, you'll need to do a little better job parsing taking quotes into account). For each line you read in and split, set the values of the OleDbParameters you created above and call OleDbCommand.ExecuteNonQuery (after opening your connection for the batch, of course). When you're done, close the connection.
Read about the OleDbConnection, OleDbCommand, and OleDbParameter in the .NET Framework SDK. If you have specific question, please don't hesitate to ask.
Microsoft MVP, Visual C#
My Articles
|
|
|
|
|
How can I get the text in a DataGridTextBoxColumn to word wrap by default? The text just extends off the end of my DataGrid on a single line, even if I set the word wrap property of the Column's TextBox to true. If I select a TextBox for editing, then it wraps, but only while selected.
E. Simmons
|
|
|
|
|
The TextBox is only displayed when editing. When the text is displayed, it is simply drawn in the bounds and is clipped.
In order to word-wrap when displaying the text, you must extend DataGridTextBoxColumn and override Paint (the one with the most parameters, sine that's the one that's ultimately called) and GetMinimumHeight. When painting the string, make sure that StringFormatFlags.NoWrap is not set (negating the StringFormat.FormatFlags property with a bitwise AND), and set/unset anything else you want/don't want, including for other properties of the StringFormat.
The trick is that you have to use the Graphics object passed to Paint to call MeasureString, so that you can adjust the return value of GetMinimumHeight (since only enough for one line of text is displayed by default), but nothing that can't be done.
Microsoft MVP, Visual C#
My Articles
|
|
|
|
|
Ahhhh, that works nicely. Thank you.
E. Simmons
|
|
|
|
|
The function in unmanaged DLL get char* as parametar. When I try to use that function with (out) string parametar, program breaks. How to use that function?
|
|
|
|
|
A char* is a string - they're both already pointers. So, don't use out or ref with a string unless you see an unmanaged declaration like char** or LPCSTR* or something.
BTW, if the string is actually a TCHAR*, TCHAR[], or LP(C)TSTR, then make sure you specify CharSet.Auto. If the declaration is a char* or LP(C)STR, be sure to specify CharSet.Ansi since strings are Unicode in .NET natively. Both the DllImportAttribute and StructLayoutAttribute define a CharSet field you should set in the attribute in such a case.
Microsoft MVP, Visual C#
My Articles
|
|
|
|
|
If I pass only string as parametar program breaks with error message: "Object reference is not set to an instance of an object". Pass parremetar is char* type in dll.
|
|
|
|
|
And is the string set to something? Trust me, a char* is a string - in any language.
If the string is supposed to be an output parameter, do not use the out keyword but instead use the OutAttribute. This tells the marshaler that the parameter is an output parameter and will be assigned when the function returns.
Microsoft MVP, Visual C#
My Articles
|
|
|
|
|
If I understand you well, this is a solution for problem:
[DllImport("Kernel32.dll", CharSet=CharSet.Ansi)]
public static extern bool GetComputerName([Out,MarshalAs(UnmanagedType.LPTStr)] string f1, out int a2);
.
.
.
int v = 0;
string a = "";
if (GetComputerName(a, out v))
{
MessageBox.Show(a+" " + v);
}
But, this will not work???
|
|
|
|
|
You have that all wrong. The character set should be CharSet.Auto (since, like I said before, the string is LPTSTR), and the second parameter is documented as [in, out], which means you should use ref because you MUST pass the size of the string buffer, which is probably why you're getting the error (not to mention the wrong character set when using Windows NT (4.0, 2000, XP, 2003). The property signature is:
[DllImport("user32.dll", CharSet=CharSet.Auto)]
public static extern bool GetComputerName(string name, ref uint size);
uint size = 16;
string name = new string('\0', (int)size);
GetComputerName(name, ref size);You know what, though? You should browse the class library documentation and learn what's there. This P/Invocation is not necessary. Just use the Environment.MachineName property.
Microsoft MVP, Visual C#
My Articles
|
|
|
|
|
Why can't you remove it? How are you trying to remove it? Please be more specific or we really can't help you with your problem.
Microsoft MVP, Visual C#
My Articles
|
|
|
|
|
its because i have no idea how to remove it!
i tried...
i have a xml document called Data!
string id = listBoxSpil.SelectedItem.ToString().Substring(0,2);
//the value of id is 1
XmlNode remove = Data.SelectSingleNode("//food[@id='" + id + "']")
Data.RemoveChild(remove);
But it doesn't work 
so if you can tell me a way to remove it please help me!
|
|
|
|
|
First of all, make sure the id string is correct. When you call RemoveChild, you must call it on the XmlNode.ParentNode, because the XmlNode will most likely not be a child of the document (which only has one child - the root node, a.k.a. document element.
So, change Data.RemoveChild(remove) to remove.ParentNode.RemoveChild(remove) and it should work.
Microsoft MVP, Visual C#
My Articles
|
|
|
|
|
heyY thanks man... it work now!!! 
if there is anything i can do... just say it!
|
|
|
|
|
... But don't call me Sherly. I have a VS.NET 2002 project that uses a 1.1 Framework dll and it is giving me the StrongNameIdentityPermission error because it is looking at the MachineConfig for the 1.0 Framework. How do I get my project to the look at the 1.1 Framework Machine.Config?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
