|
Please can anyone help with:-
1 How to structure the strSelect statement.
2 How to instruct the cn, da, cb, tb etc to go and get on with it.
private void btnSaveAllChangesMadeToAllLogsSoFar_Click(object sender, EventArgs e)
{
string strSelect = "UPDATE INTO dbo.tblQLs WHERE colErrByUserID = '" +
LoginForm.gb_strUserID + "' ";
SqlConnection cn = new SqlConnection(LoginForm.gb_strConnection);
SqlDataAdapter daTblQLs = new SqlDataAdapter(strSelect, LoginForm.gb_strConnection);
SqlCommandBuilder cb = new SqlCommandBuilder(daTblQLs);
cb.GetUpdateCommand();
daTblQLs.Update(gl_Dataset.ds, "tblQLs");
cn.Close();
}
Thank you for your help.
Mark
|
|
|
|
|
Luc Pattyn
Local announcement (Antwerp region): Lange Wapper? Neen!
|
|
|
|
|
Mark-123456789 wrote: SqlConnection cn = new SqlConnection(LoginForm.gb_strConnection);
So you created your connection. Now you have to establish your connection so that the database is open.
(that was a hint) try cn. and see what pops up. Maybe related to your cn.Close() command.
Mark-123456789 wrote: cb.GetUpdateCommand();
think things through: You created an update command with your strSelect. You created something that consumes that string (whether it is correct or not) and you now are calling GetUpdateCommand(). Why did you do this? Your sql was your update command. You also do not need the data adapter.
So if you use Google then you will do several searches as follows:
"SQL Server UPDATE command"
"Updating SQL Server using C#"
So what you need to do really is this:
1) create a valid Update SQL command {UPDATE tbl ... values()}
2) create and active your connection
3) Create a command using your connection and sql statement
4) Execute the command
Your SQL statement (that needs to be Executed) is considered to be NonScalar as a hint of what to do to your command.
|
|
|
|
|
Thanks for that, I have tried to use this info give and now have this...
Which is giving me a "The variable name '@colNotes' has already been declared. Variable names must be unique within a query batch or stored procedure." ERROR.
And still is not updating my Database. Also The changes i want are in a Dataset, with this new code how does it know what the changes are as there is no referance to gl_dataset.
private void btnSaveAllChangesMadeToAllLogsSoFar_Click(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection(LoginForm.gb_strConnection);
cn.Open();
string strCmdTxt = "UPDATE tblQLs SET colNotes = @colNotes " +
"WHERE [colErrByUserID] = '1'"; //" + LoginForm.gb_strUserID + "' ";
SqlDataAdapter da = new SqlDataAdapter(strCmdTxt, cn);
SqlCommand sqlCmd = new SqlCommand(strCmdTxt, cn);
sqlCmd.Parameters.Add("@colNotes", SqlDbType.NChar, 500, "colNotes");
SqlParameter parm = new SqlParameter();
parm = sqlCmd.Parameters.Add("@colNotes", SqlDbType.NChar, 500, "colNotes");
parm.SourceVersion = DataRowVersion.Original;
sqlCmd.ExecuteNonQuery();
cn.Close();
}
|
|
|
|
|
Looking better.
You still don't need the DataAdapter.
"The variable name '@colNotes' has already been declared" -- You're adding the parameter twice; don't. What do you intend to do with the parm variable anyway?
|
|
|
|
|
Thank you you help, below is the working code.
I am sure there will be smart ways to do this, but I am just happy to get it working.
The next problem (just incase you can help) is, is in another bit of code I use Textbox.text instead of the dr[col..], however the string is too long for the field and i get an error. even when i use substring(0,10). How can I auto sence the max field width and truncate it.
also (as i have you here) i would like to apply some formating to a cell on a DataViewGrid ie date dd/mm/yyyy.
private void btnSaveAllChangesMadeToAllLogsSoFar_Click(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection(LoginForm.gb_strConnection);
cn.Open();
DataTable dt = new DataTable();
dt = gl_Dataset.ds.Tables["tblQls"];
string strCommand = "";
foreach (DataRow dr in dt.Rows)
{
strCommand = "UPDATE tblQLs SET " +
"colClosedDate = '" + dr["colClosedDate"].ToString() + "', " +
"colAppealReason = '" + dr["colAppealReason"].ToString() + "', " +
"colAppealUpheld = '" + dr["colAppealUpheld"].ToString() + "', " +
"colFinalActionTaken = '" + dr["colFinalActionTaken"].ToString() + "', " +
"colNotes = '" + dr["colNotes"].ToString() + "' " +
"WHERE [colErrByUserID] = '" + dr["colErrByUserID"].ToString() + "' " +
"AND [colQLID] = '" + dr["colQLID"].ToString() + "' ";
SqlCommand cmd = new SqlCommand(strCommand);
cmd.Connection = cn;
cmd.ExecuteNonQuery();
}
cn.Close();
}
|
|
|
|
|
 PLEASE use a parameterized query:
SqlCommand cmd = cn.CreateCommand ( "UPDATE tblQLs SET colClosedDate=@colClosedDate ... " ) ;
(Create and add parameters...) (It shouldn't require ToString())
foreach ( ... )
{
(Set parameter values...)
cmd.ExecuteNonQuery() ;
}
Mark-123456789 wrote: too long for the field and i get an error
I'd have to see it.
Mark-123456789 wrote: How can I auto sence the max field width and truncate it.
You'd have to access the schema, which isn't very difficult.
Mark-123456789 wrote: DataViewGrid ie date dd/mm/yyyy
I don't use DataViewGrids so I don't know off hand.
|
|
|
|
|
You wish is my command.
{
SqlConnection cn = new SqlConnection(LoginForm.gb_strConnection);
cn.Open();
// this bit is me trying a work-around-----------------------------
char[] test = new char[15];
txbColPolicyRef.Text = txbColPolicyRef.Text.Trim().ToUpper();
int intLen = txbColPolicyRef.Text.Length;
if (intLen > 15) intLen = 15;
test = txbColPolicyRef.Text.Trim().ToUpper().Substring(0, intLen ).ToCharArray();
// ------------------------------------------------------------
string strCommand = "INSERT INTO tblQLs (colQLID, colCreatedDate, colClosedDate, colErrDate, " +
"colPolicyRef, colCreatedByUserID, colErrDescription, colErrByUserID, " +
"colAppealReason, colAppealUpheld, colFinalActionTaken, colNotes, colUserTeam) " +
"VALUES (" +
"'16', " +
"'x', " +
"'" + test + "', " +
"'" + @txbColCreatedByUserID.Text.Trim().ToUpper() + "', " +
"'" + @cbColErrDescription.Text.Trim().ToUpper() + "', " +
"'" + @txbColErrByUserID.Text.Trim().ToUpper() + "', " +
"'x'," +
"'" + @txbColErrDate.Text.Trim() + "', " +
"'x','x','x','x', " +
"'" + @txbColUserTeam.Text.Trim().ToUpper() + "') ";
SqlCommand cmd = new SqlCommand(strCommand);
cmd.Connection = cn;
cmd.ExecuteNonQuery();
cn.Close();
}
|
|
|
|
|
Maybe check this[^] out.
Particularly my rendition of ExecuteNonQuery and ListColumns.
|
|
|
|
|
My friends i need help about this issue can anyone help me 
|
|
|
|
|
You can use Char.IsDigit to see if a character is a digit or other character.
Why is common sense not common?
Never argue with an idiot. They will drag you down to their level where they are an expert.
Sometimes it takes a lot of work to be lazy
Individuality is fine, as long as we do it together - F. Burns
Help humanity, join the CodeProject grid computing team here
|
|
|
|
|
thanks for your help problem solved
|
|
|
|
|
Hi,
you can use the TryParse() method of int, uint, long, ulong depending on the range you want to support; and some overloads will let you choose options, e.g. allowing thousand separators.
Warning: TryParse() will always fail on an empty TextBox.
similar for reals (float, double), dates, etc.
everything in a TextBox is a string.
Luc Pattyn
Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.
Local announcement (Antwerp region): Lange Wapper? Neen!
|
|
|
|
|
thanks alot problem solved
|
|
|
|
|
|
|
I am tryng to serialize the IP's Server Addresses in my Lan using objects IPHostEntry for getting better the communications with them, later I need that the machines which are going to connect with such Server Serialize and Deserialize the Server's List, but when I try to serialize, both in binary or XML, I receive an error telling me that such object IPHostEntry is not marked as Serializable.
Any suggestion for avoiding this problem
Note: I Create a Dictionary<string, iphostentry=""> containing the name and IP data of every Server and such is the object which I want to Serialize, if the key and value are both strings there is no problem, but when I change to Type IPHOstEntry it does not work.
Regards
|
|
|
|
|
|
0) UPDATE INTO -- nope
1) colErrByUserID = '" + LoginForm.gb_strUserID + "' "; -- very bad idea
|
|
|
|
|
Thanks for that! 
Very useful, not!
So what you are really saying is you don't know...
|
|
|
|
|
No, I'm just not going to spoon-feed you. You'll find very few people here who will.
|
|
|
|
|
Thats right you, keep telling yourself that!
|
|
|
|
|
|
I am having an XMl like below
<Details>
<det id=1 name="abc">
</Details>
Now i want to take the above xml as input and to repeat the node
like below using C#
<Details>
<det id=1 name="abc"/>
<det id=2 name="abc"/>
<det id=3 name="abc"/>
</Details>
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
