Re: sql query - C# Discussion Boards

Why do people keep asking this ? How about a program that accepts speech input, and then takes requirements delivered in this manner, and spits out a working exe ?

Or was there some limit in scope of your request that you expected us to work out by voodoo or something ?

Think about what your skillset is, think about what you'd like to learn, and then come up with a project that fits. Or at least, give us something to go with.

Christian Graus - Microsoft MVP - C++

"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

http://www.dotnetspider.com/projects/[^]

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

Control and monitoring system for a nuclear power plant

Flight control system for the Airbus A380

Satellite navigation system to compete with the U.S. GPS

Launch control system for the Space Shuttle

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Port .net to OpenVMS... please? Cool | :cool:

-- modified at 14:43 Friday 24th August, 2007

Oh, and IIS too.

Hi,

I have an SQL query
tring sql = "UPDATE employees SET Fullname = '" + textBox1.Text + "' Where EmployeeID = '" + employeeID + "'";

I am using Where EmployeeID = '" + employeeID + "'"; because I want the query to be based on what the user has selected in a seperato classes combobox. The above how, ever doesn't work. Nothing changes. Do you know if the query is somewhat incorrect?

Thank you Sigh | :sigh:

First of all, your code is still insecure, I guess this is for a class then ?

Second, you should try to give your controls real names. What is textbox1 ? When you have 6 on the screen, how will you remember which one is textbox5 ?

Third, your id is a number as I recall, but you have it in quotes, which means you're passing it as a string. Why is it a string in the database ?

The best thing to do here, is to step through the debugger, and see what the SQL string is, then pass it through to your database manually and see how it responds. It will give you meaningful information if it can't process the SQL.

And, depending on what the rest of your code looks like, it's possible the SQL is not being called, but if it is, it should return to you the number of rows affected, you could check that and see if it's > 0.

Christian Graus - Microsoft MVP - C++

"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

but I am concatenating C# with SQL so don't you use quotes so it knows what is C# code? EG. The SQL turns red and the C# remains black. If I don't use quotes it will be wrong. I have also used this for my insert statements which work perfectly. The ID is an int in the database. It has seemed that for my insert statements it only works when I use.Tostring which to me is strange.

Also the only reason I had to change everything to public was because I am using inheritance. I ahve decided against that now.
I am not on contracted work, I am a permanent worker who has been given the opportunity to learn C#.

falles01 wrote:
Also the only reason I had to change everything to public was because I am using inheritance. I ahve decided against that now.

The two do not follow. Protected gives you things that are private, but visible in derived classes.

falles01 wrote:
but I am concatenating C# with SQL so don't you use quotes so it knows what is C# code?

What I mean is, your final SQL will look like this:

where id ='323'

or something

The id should not be in quotes if it's a number, unless Access is different in that regard. I'm talking about the ' in your generated string, not the quotes around the static strings.

falles01 wrote:
It has seemed that for my insert statements it only works when I use.Tostring which to me is strange.

You may have to use ToString to get a string when yuo're building the SQL, which is a string. Howver, if you concatenate a string and an int, the int should go tostring by itself.,

falles01 wrote:
I am not on contracted work, I am a permanent worker who has been given the opportunity to learn C#.

I just read back on your older thread, so I get your situation now. Did you try getting the SQL out of the debugger and pasting it into Access ? Also, what does your data access code look like ?

Ideally, you'd have all your data access via stored procs, and seperated into a dll. For now, I'd recommend you create a class whose sole job is to manage any database calls, and put any data access in there. Make it static, and just call the methods to run the SQL, so in this instance, the method would take two params ( the name and the id ). you can then add methods to 'sanitise' your input against SQL injection attacks, but even if yuo don't, it makes sense to centralise your SQL, not least so you can reuse it if need be.

Christian Graus - Microsoft MVP - C++

"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

falles01 wrote:
but I am concatenating C# with SQL so don't you use quotes so it knows what is C# code?

No. The SQL String you built using this code is:

UPDATE employees SET Fullname = 'someTextBoxValue' Where EmployeeID = 'someIdValue'

Because of the single quotes around the IDValue, you've told SQL to treat it as a string. So, is this value a string in your table or are you telling SQL to find a string value in a numeric column?

You really need to translate this code into an SQL parameterized query. That way, you don't make the mistake of quoting numbers or haveing a user enter a quote in a textbox, then having that screw up your concatentated string, thereby making an invalid SQL statement.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Well, apart from the SQL Injection vulnerabilities... try using SQL Parameters:

conn = new SqlConnection("Server=yourServer; DataBase=yourDatabase; /*stick the rest of your connection info in here*/);

SqlCommand cmd = new SqlCommand("SET Fullname = @Fullanme WHERE EmployeeID = @EmployeeID;", conn);

SqlParameter param1 = new SqlParameter();
param1.ParameterName = "@Fullname";
param.Value = textBox1.Text;

cmd.Paramaters.Add(param1);

SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@employeeID";
param2.Value = employeeComboBox.Value;

cmd.Paramaters.Add(param2);

conn.Open();
conn.Execute.....

"It was the day before today.... I remember it like it was yesterday."

-Moleman

Please read SQL Injection attacks and tips on how to prevent them.[^]

-- Always write code as if the maintenance programmer were an axe murderer who knows where you live.

Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ...
* Reading: SQL Bits

My website

*grin* I've told her that three times now. I suspect she's completely overwhelmed by being given a task that she was not hired for, so I'm recommending she create a data layer that she can easily work through to fix the injection stuff later, if she chooses not to do it now.

Christian Graus - Microsoft MVP - C++

"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

Okay okay..I will read up on injection attacks. I just wanted to get this working first as I only have a few days to cmplete this before they may possibly ask me to go back to my previous job.
I am hoping they will give me an extension because I would obviously want to make it as safe as possible. at the moment I am only using dummy data in the database.

Thanks;P

If you need to impress by a deadline, I'd say get features in there, and add comments about your intention to move to a better data layer, so that you're covered if someone looks at it later, but you focus on progress that will impress and get you your extension.

This is no way to write software, but that's not your fault, obviously.

Christian Graus - Microsoft MVP - C++

"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

My sql update query now works correctly. I asked for more help around me and worked out some things for myself.
I found that if I put the code for the save button at the bottom of the page it works exactly the same way.
Strange.
Big Grin | :-D

This query is really good in one way. It can clean the database in just one shot. Mad | :mad:

P.S.: I actually indicated about the vulnerability of SQL Injection that this query is bearing on it. Smile | :)

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

Thanks for adding that, there was a danger that she missed the three people who told her already...

Christian Graus - Microsoft MVP - C++

"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

from where i can get
office add-on for Visual Studio.Net 2003 ?

Is Visual Studio Tools for Office[^] what you're looking for?

Tech, life, family, faith: Give me a visit.
I'm currently blogging about: Roman Catholic Relevance?
The apostle Paul, modernly speaking: Epistles of Paul

Judah Himango

Hi,

How can I modify the text displayed in a webbrowser control, e.g. highlight it, or make it bold? I know I can access it using the documenttext property but how can i modify it?

Also, what are the licence terms for webbrowser control 2.0? I mean if i build an application using it, can i redistribute it or would I have to inform MS?

Thanks,
Ahmad

Look into the Document property of the WebBrowser control which returns a HTMLDocument[^].

All the controls provided by the .NET framework are free for you to use. You could code from your favorite text editor and use the command line c-sharp compiler and never have to buy a license to anything other than the operating system your using to compile on.

Hi all

I have a sub that writes to a textbox in the code. I want to change it so it just returns as a string so i can use it over again for other stuff in the program

the current code is this

private void Readlog(string path)<br />
        {<br />
            StreamReader SR;<br />
            string S;<br />
            SR = File.OpenText(path);<br />
            S = SR.ReadLine();<br />
            while (S != null)<br />
            {<br />
                if (S != null)<br />
                {<br />
                   //this is sloppy but i cant have a trailing /r/n so this is the only                <br />
                    //way i can think to get rid of it                    <br />
                   S = S + "\r\n";<br />
                }<br />
                textBox1.AppendText(S);<br />
                S = SR.ReadLine();<br />
            }<br />
            SR.Close();<br />
        }

what i want to do is change it to something like

private string Readlog(string path)<br />
        {<br />
            string temp;<br />
            StreamReader SR;<br />
            string S ;<br />
            SR = File.OpenText(path);<br />
            S = SR.ReadLine();<br />
            temp =s<br />
            while (S != null)<br />
            {<br />
                if (S != null)<br />
                {<br />
                    S = S + "\r\n";<br />
                }<br />
                temp = temp +S;<br />
                S = SR.ReadLine();<br />
            }<br />
            SR.Close();<br />
            return temp;<br />
        }

The problem is that it always returns a null or just one line of text and i know its probably somthing right in front of me but I can't seem to figure out what it is

the idea is that i can use it in something like textbox1.text = readlog(c:\test)

or

<br />
Foreach something in something<br />
{<br />
writelog(merge.txt,readlog(something.filename);<br />
}<br />
<br />

Thanks for pointing me in the right direction

C# is case sensative on variable names, so your use of s and S were a problem. Also having an IF statement to check for null inside your while statement that is calready checking for null makes no sense. But I guess you want to make sure that it's not null.

Here's my cleanup of your code. I also ignored empty strings, but if you want to keep those, then change the while comparison to be while(temp != null)

private string Readlog(string path)
{
	string returnValue = string.Empty;

	using (StreamReader sr = File.OpenText(path))
	{
		string temp = sr.ReadLine();

		while (!string.IsNullOrEmpty(temp))
		{
			returnValue += temp + "\r\n";
			temp = sr.ReadLine();
		}
				
		sr.Close();
	}

	return returnValue;			
}

-- modified at 17:44 Thursday 23rd August, 2007

BLAH!!!!

I cannot believe I totally missed the string concat inside a loop. Someone please pass me a gun now.

FIXED version

private string Readlog(string path)
{
	StringBuilder sb = new StringBuilder();

	using (StreamReader sr = File.OpenText(path))
	{
		string temp = sr.ReadLine();

		while (!string.IsNullOrEmpty(temp))
		{
			sb.AppendLine(temp);
			temp = sr.ReadLine();
		}
				
		sr.Close();
	}

	return sb.ToString();			
}

Thanks for all your help

I really appreciate the string.isnullorempty(temp) In are application it doesn't matter because we are joining together .csv files

but if you were to use it for say a text file or something if there was a empty space it would stop at the dump out of the loop.

again I really appreciate it thanks