C# Discussion Boards - CodeProject

30-May-09 4:09

What I'm trying to do is insert a new record into a SQL Server 2008 database file.

So far the samples I have fount don't work. Does any one know how to do this. Below is one of that samples I did fine.

string UserConnection = Properties.Settings.Default.UsersConnectionString;
SqlConnection MyConnection = new SqlConnection(UserConnection);

MyConnection.Open();

String MyString = @"INSERT INTO UserData (Name, telnetaddr) VALUES('addr.name', 'addr.telnetaddr')";
SqlCommand MyCmd = new SqlCommand(MyString, MyConnection);

MyCmd.ExecuteScalar();
MyConnection.Close();
RMDoor.WriteLn("New Entry Added");

}
catch(SqlException e)
{
RMDoor.WriteLn(e.Message.ToString());
}

Mbah Dhaim30-May-09 5:17

Mbah Dhaim

30-May-09 5:17

bigjoe11a wrote:
MyCmd.ExecuteScalar();

Replace with MyCmd.ExecuteNonQuery();

hope it helps

dhaim
ing ngarso sung tulodho, ing madyo mangun karso, tut wuri handayani. "Ki Hajar Dewantoro"
in the front line gave a lead, in the middle line build goodwill, in the behind give power support

bigjoe11a30-May-09 5:35

30-May-09 5:35

Thanks how ever I did try that too. and for some reason. It just not inserting. Its not giving me any errors.

Henry Minute30-May-09 7:48

30-May-09 7:48

The problem is caused by String MyString = @"INSERT INTO UserData (Name, telnetaddr) VALUES('addr.name', 'addr.telnetaddr')";, in particular the VALUES clause. By enclosing addr.name etc in the quotes they are treated as just another part of the string. Their values are not fetched and used, as you intended.

What you need is a parameterized SQLcommand take a look at the example here[^] and try to adapt it for your data.

Please post back if you get stuck.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

bigjoe11a30-May-09 8:17

30-May-09 8:17

Well I'm stuck. Is there a sample related to what I'm doing. This isn't going to work.

bigjoe11a30-May-09 8:33

30-May-09 8:33

I didn't see the extra commands at the top. This is an update. I need an insert

Henry Minute30-May-09 8:59

30-May-09 8:59

OK.

For now, try breaking the string up like this:

String MyString = @"INSERT INTO UserData (Name, telnetaddr) VALUES('" + addr.name.ToString().Trim() + "', '" + addr.telnetaddr.ToString().Trim() + "')";

Leave out the .ToString() if addr.name etc. is sure to return a string. I would leave the .Trim() there if it were me.
For the future, it might bring benefits to learn about Parameterized Queries, they make things a lot easier in many cases.

Good luck. Smile | :)

bigjoe11a30-May-09 9:08

30-May-09 9:08

well I'm getting some errors on some of the lines. So I can't compile yet.
I do know that some of of it is missing. My code is below

string UserConnection = Properties.Settings.Default.UsersConnectionString;
SqlConnection MyConnection = new SqlConnection(UserConnection);

String MyString = @"INSERT INTO UserData (Name, telnetaddr) VALUES('" + addr.name.ToString().TRIM() + "', '" + addr.telnetaddr.ToString().Trim() + "')";

SqlCommand command = new SqlCommand(MyString, MyConnection);
//command.Parameters.Add("@ID", SqlDbType.Int);
//command.Parameters["@ID"].Value = ID;
command.Parameters.Add("@Name", SqlDbType.Text);
command.Parameters["@Name"].Value = addr.name;
command.Parameters.Add("@telnetaddr", SqlDbType.Text);
command.Parameters["@telnetaddr"].Value = addr.telnetaddr;

// Use AddWithValue to assign Demographics.
// SQL Server will implicitly convert strings into XML.
command.Parameters.AddWithValue(commandText, );

try
{
MyConnection.Open();
Int32 rowsAffected = command.ExecuteNonQuery();
Console.WriteLine("RowsAffected: {0}", rowsAffected);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}

Error 1 Argument missing C:\Users\Joe\Documents\Visual Studio 2008\Projects\MyDoor\Program.cs 245 66 MyDoor

Error 2 'string' does not contain a definition for 'TRIM' and no extension method 'TRIM' accepting a first argument of type 'string' could be found (are you missing a using directive or an assembly reference?) C:\Users\Joe\Documents\Visual Studio 2008\Projects\MyDoor\Program.cs 233 114 MyDoor

Error 3 The name 'commandText' does not exist in the current context C:\Users\Joe\Documents\Visual Studio 2008\Projects\MyDoor\Program.cs 245 53 MyDoor

What I should have ask if some one could type out the full code So I know I get every thing right.

Henry Minute30-May-09 9:14

30-May-09 9:14

Error 2 is my fault I put it in uppercase, it should be Trim(). Blush | :O

Have a try with that change while I look at the others.

Henry Minute30-May-09 9:22

30-May-09 9:22

None of this is needed now that you are no longer trying Parameterized stuff.

//command.Parameters.Add("@ID", SqlDbType.Int);
//command.Parameters["@ID"].Value = ID;
command.Parameters.Add("@Name", SqlDbType.Text);
command.Parameters["@Name"].Value = addr.name;
command.Parameters.Add("@telnetaddr", SqlDbType.Text);
command.Parameters["@telnetaddr"].Value = addr.telnetaddr;

// Use AddWithValue to assign Demographics.
// SQL Server will implicitly convert strings into XML.
command.Parameters.AddWithValue(commandText, );

either delete those lines or preferably just comment them out until you have the thing working.

Error 3 is caused by Error 2, so should be alright now.

The stuff about MyDoor, I cannot help with, although I remember seeing it in one of your previous posts.

bigjoe11a30-May-09 9:59

30-May-09 9:59

Well this is what why code likes like now. I'm just going to add the rest of the code. I didn't think it would help. Here it is. Don't worry about the RMDoor. Its just an add on library that I added.
////////////////////////////////////

public static void add()
{
RMDoor.ClrScr();

Person addr = new Person();

RMDoor.Write("Enter Name : ");
addr.name = RMDoor.ReadLn();

RMDoor.Write("Address : ");
addr.telnetaddr = RMDoor.ReadLn();

RMDoor.ClrScr();

RMDoor.WriteLn("BBS Name : " + addr.name);
RMDoor.WriteLn("Telnet Address : " + addr.telnetaddr);
RMDoor.WriteLn();

RMDoor.Write("Is this what you want Y/N");
string Q = RMDoor.ReadLn();
Q = Q.ToUpper();
savelog("User has just added an entry");
if (Q == "Y")
{

string UserConnection = Properties.Settings.Default.UsersConnectionString;
SqlConnection MyConnection = new SqlConnection(UserConnection);

String MyString = @"INSERT INTO UserData (Name, telnetaddr) VALUES('" + addr.name + "', '" + addr.telnetaddr + "')";
SqlCommand MyCmd = new SqlCommand(MyString, MyConnection);

MyConnection.Open();
try
{
MyCmd.ExecuteNonQuery();
Int32 rowsAffected = MyCmd.ExecuteNonQuery();
RMDoor.WriteLn("RowsAffected: " + rowsAffected);
MyConnection.Close();
}
catch (SqlException ex)
{
RMDoor.WriteLn(ex.Message);
}

}
else
{
RMDoor.WriteLn("Save Aborted");

}

RMDoor.WriteLn("Press Enter to Continue");
RMDoor.WriteLn();

}

I hope this helps. It just doesn't work.

Henry Minute30-May-09 10:43

30-May-09 10:43

Apart from the fact that you execute the command twice:

MyCmd.ExecuteNonQuery();
Int32 rowsAffected = MyCmd.ExecuteNonQuery();

I could not see anything wrong with the code, as it is. So I copied and pasted it into a project. To get it to compile on my machine I changed all your calls to RMDoor.whatever to Console.whatever and commented out the savelog line. I created a new database, added a table called UserData with two columns Name and telnetaddr both nvarchar(50).

In the project I created a new DataSource ConsoleUsersTestDataSource from the database above, and ran the project.

Because the command executes twice, it added two new records, with identical data.

Just so that you can see that my code is essentially identical to yours, here it is:

class Program
{
    static void Main(string[] args)
    {
        add();
    }

    public static void add()
    {
        Console.Clear();

        Person addr = new Person();

        Console.Write("Enter Name : ");
        addr.name = Console.ReadLine();


        Console.Write("Address : ");
        addr.telnetaddr = Console.ReadLine();

        Console.Clear();

        Console.WriteLine("BBS Name : " + addr.name);
        Console.WriteLine("Telnet Address : " + addr.telnetaddr);
        Console.WriteLine();

        Console.Write("Is this what you want Y/N");
        string Q = Console.ReadLine();
        Q = Q.ToUpper();
        //savelog("User has just added an entry");
        if (Q == "Y")
        {
            string UserConnection = Properties.Settings.Default.ConsoleUsersTestConnectionString;
            SqlConnection MyConnection = new SqlConnection(UserConnection);

            String MyString = @"INSERT INTO UserData (Name, telnetaddr) VALUES('" + addr.name + "', '" + addr.telnetaddr + "')";
            SqlCommand MyCmd = new SqlCommand(MyString, MyConnection);

            MyConnection.Open();
            try
            {
                MyCmd.ExecuteNonQuery();
                Int32 rowsAffected = MyCmd.ExecuteNonQuery();
                Console.WriteLine("RowsAffected: " + rowsAffected);
                MyConnection.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }


        }
        else
        {
            Console.WriteLine("Save Aborted");

        }

        Console.WriteLine("Press Enter to Continue");
        Console.WriteLine();

    }
}

I can only suggest that you try cleaning your solution (Build | Clean Solution, just in case you don't know) and then rebuild it.

If it doesn't work then, I can only assume that there is something wrong with your database; or you have duplicate databases and that you are updating one of them and checking the other for changes, which obviously won't be there; or something really weird like that.

Apart from deleting the extra ExecuteNonQuery() line I am at a loss.

bigjoe11a30-May-09 11:31

30-May-09 11:31

Thanks. That's works now. It turned out to be the table name was wrong. That's why.
It Shows as UserData and it should be UaersData.

What I did was I went back and check my lines again making sure they were all spelled right and so. I changed so many times. I forgot to change it back.
I can't under stand why it didn't throw an error.

Ok, now. Now how about a search. How can I setup a search. Using the same code or is that not the same.

string sname = "Tom Cat";

So how would I search for this name and if it finds it, it displays the record. If not it throws an error.

Henry Minute30-May-09 11:52

30-May-09 11:52

For that you will need to use a SELECT Command, which would look something like:

SELECT * FROM dbo.UaersData WHERE Name = @userName

******************************** Note **************************
This is only approximate. and it uses a parameter (the @userName)

However instead of that you can do as you have done for the INSERT and break the string into bits, a bit like:

string commandString = "SELECT * FROM dbo.UaersData WHERE Name = '" + theVariableHoldingTheNameFromTheConsole + "'";

In order to use it efficiently however you really should use the Prameterized methodology.

In the mean time take a look at this[^], scroll down till you reach the Executing SQL Statements that Return Rows Using a Command Object section. This is very similar to the method you have been using. You will then need to research the SqlDataReader object to find out how to get the data from the reader

As this is really basic stuff, I think you will be better off if you do some reading in the MSDN Documentation, and by googling.
If you have any further questions, I would suggest that you start a new thread, as I will be away from my PC for a few hours.

bigjoe11a30-May-09 12:03

Updating changings back to DB from DataGridView

30-May-09 12:03

Well I wanted to tell you too. That when I added the new record. It added it. But is didn't save it to the database. When I added the record. And I when to view all the records. The 3rd and new record was there. How ever when I closed the application and
look in the database explorer. to view the records. The 3rd record was missing. Looks like it never got added to the database file.

And all so I have been doing searches for all this at google and I go throw 100's of pages and still can't find what I'm looking for.

Thats why I desided to post.

WinSolution30-May-09 3:11

WinSolution

30-May-09 3:11

Dear All

I want to updated the changes made in DataGridView back to SQL Table in an easiest way while i am working with 3-Tire approach (GUI, BusinessLogic, DataAccess)

I have search a lot on Google but i am not able to find a good,simple and easy solution.

Thanks for helping.

Re: Updating changings back to DB from DataGridView

Henry Minute30-May-09 4:12

Re: Updating changings back to DB from DataGridView

30-May-09 4:12

Try looking at this[^], it might help.

I Believe In GOD30-May-09 10:08

I Believe In GOD

30-May-09 10:08

Hi , if you want to save datagrid back to SQL database then there is a very simple rules :

- every table must have PK ( Exception will rise if don't )

ds = new DataSet("tb");

clsConnect.SqlDa = new SqlDataAdapter("SELECT * FROM tb1 WHERE ID = 2", clsConnect.SqlCon);

clsConnect.SqlCmdBuild = new SqlCommandBuilder(clsConnect.SqlDa);

clsConnect.SqlDa.InsertCommand = clsConnect.SqlCmdBuild.GetInsertCommand();
clsConnect.SqlDa.DeleteCommand = clsConnect.SqlCmdBuild.GetDeleteCommand();
clsConnect.SqlDa.UpdateCommand = clsConnect.SqlCmdBuild.GetUpdateCommand();

clsConnect.SqlDa.Fill(ds , "tb");
dataGridView1.DataSource = ds.Tables[0];
clsConnect.SqlDa.Update(ds.Tables[0]);
ds.Tables[0].AcceptChanges();

I know nothing , I know nothing ...

Re: Updating changings back to DB from DataGridView

Henry Minute30-May-09 10:51