Visual Studio .NET - Code and run a query when prog is running

Hmm look pretty straightforward to me, you can retreive what the user has selected and add an event right?

So, lets say that we got this primary key you need stored in a string and were in a method

string primaryKey = 1234;

SqlConnection myConnection = new SqlConnection(connectinString);
string myCommand =

" INSERT INTO [COURSES PLAYED]
SELECT   COURSEName, H1PAR, H2PAR, H3PAR, H4PAR, H5PAR, H6PAR, H7PAR, H8PAR, H9PAR, H10PAR, H11PAR, H12PAR, H13PAR, H14PAR, H15PAR,
    H16PAR, H17PAR, H18PAR, H1SC, H2SC, H3SC, H4SC, H5SC, H6SC, H7SC, H8SC, H9SC, H10SC, H11SC, H12SC, H13SC, H14SC, H15SC, H16SC,
    H17SC, H18SC, H1TEE, H2TEE, H3TEE, H4TEE, H5TEE, H6TEE, H7TEE, H8TEE, H9TEE, H10TEE, H11TEE, H12TEE, H13TEE, H14TEE, H15TEE,
    H16TEE, H17TEE, H18TEE, H1FWAY, H2FWAY, H3FWAY, H4FWAY, H5FWAY, H6FWAY, H7FWAY, H8FWAY, H9FWAY, H10FWAY, H11FWAY,
    H12FWAY, H13FWAY, H14FWAY, H15FWAY, H16FWAY, H17FWAY, H18FWAY, BUNK1, BUNK2, BUNK3, BUNK4, BUNK5, BUNK6, BUNK7, BUNK8,
    BUNK9, BUNK10, BUNK11, BUNK12, BUNK13, BUNK14, BUNK15, BUNK16, BUNK17, BUNK18, PUTT1, PUTT2, PUTT3, PUTT4, PUTT5, PUTT6, PUTT7,
    PUTT8, PUTT9, PUTT10, PUTT11, PUTT12, PUTT13, PUTT14, PUTT15, PUTT16, PUTT17, PUTT18, GR1, GR2, GR3, GR4, GR5, GR6, GR7, GR8, GR9,
    GR10, GR11, GR12, GR13, GR14, GR15, GR16, GR17, GR18, BUNK1OUT, BUNK2OUT, BUNK3OUT, BUNK4OUT, BUNK5OUT, BUNK6OUT, BUNK7OUT,
    BUNK8OUT, BUNK9OUT, BUNK10OUT, BUNK11OUT, BUNK12OUT, BUNK13OUT, BUNK14OUT, BUNK15OUT, BUNK16OUT, BUNK17OUT, BUNK18OUT,
   H1YDS, H2YDS, H3YDS, H4YDS, H5YDS, H6YDS, H7YDS, H8YDS, H9YDS, H10YDS, H11YDS, H12YDS, H13YDS, H14YDS, H15YDS, H16YDS,
    H17YDS, H18YDS
FROM   COURSE WHERE (PriKey3 =" + primaryKeye  + ")";

//Now that we got this request built,

SqlCommand cmd = new SqlCommand(myCommand, connection);
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();

 Hi Mikael

 The primary key is Prikey3
 I have put the code you supplied in but get a lot of wavy lines under parts of the code and have no Idea why.
 I cant seem to do the simple things you suggest

Thanks

David

Alright, whats the language of your program? Is it windows app or web app? Have you added the event to retreive the primarykey number on the user interface? What is your database server, is it sql? What you added the correct import statement? Whats the framwork version?

In fact, post the code around  my part of code where you added it.

The debugger most tell you something informations about the lines that are not correct, what is it?

 Hi Mikael
 
 The program is in visual studio 2008 and using Xaml design and winforms
 Data-base is an access data-base
  I have a small version of my program I would like to post to any one that can help me to solve this one problem
 but dont know if thats allowed it has me completely beat and I am going round in circles.
 My e-mail is aldrichdave2465@live.co.uk

 Thanks

 David
 

Ahh , the code I supplied you was for SqlServer, I assume the database is on access 2007?

 Yes Mikael

Access 2007

 Thanks

 David

This is adapted for access 2007, be sure to include import statements of System.Data and System.Data.OleDB;

string primaryKey = 1234;


OleDbConnection myConnection = new OleDbConnection(connectionString);
string myCommand =

" INSERT INTO [COURSES PLAYED]
SELECT   COURSEName, H1PAR, H2PAR, H3PAR, H4PAR, H5PAR, H6PAR, H7PAR, H8PAR, H9PAR, H10PAR, H11PAR, H12PAR, H13PAR, H14PAR, H15PAR, 
    H16PAR, H17PAR, H18PAR, H1SC, H2SC, H3SC, H4SC, H5SC, H6SC, H7SC, H8SC, H9SC, H10SC, H11SC, H12SC, H13SC, H14SC, H15SC, H16SC, 
    H17SC, H18SC, H1TEE, H2TEE, H3TEE, H4TEE, H5TEE, H6TEE, H7TEE, H8TEE, H9TEE, H10TEE, H11TEE, H12TEE, H13TEE, H14TEE, H15TEE, 
    H16TEE, H17TEE, H18TEE, H1FWAY, H2FWAY, H3FWAY, H4FWAY, H5FWAY, H6FWAY, H7FWAY, H8FWAY, H9FWAY, H10FWAY, H11FWAY, 
    H12FWAY, H13FWAY, H14FWAY, H15FWAY, H16FWAY, H17FWAY, H18FWAY, BUNK1, BUNK2, BUNK3, BUNK4, BUNK5, BUNK6, BUNK7, BUNK8,
    BUNK9, BUNK10, BUNK11, BUNK12, BUNK13, BUNK14, BUNK15, BUNK16, BUNK17, BUNK18, PUTT1, PUTT2, PUTT3, PUTT4, PUTT5, PUTT6, PUTT7, 
    PUTT8, PUTT9, PUTT10, PUTT11, PUTT12, PUTT13, PUTT14, PUTT15, PUTT16, PUTT17, PUTT18, GR1, GR2, GR3, GR4, GR5, GR6, GR7, GR8, GR9, 
    GR10, GR11, GR12, GR13, GR14, GR15, GR16, GR17, GR18, BUNK1OUT, BUNK2OUT, BUNK3OUT, BUNK4OUT, BUNK5OUT, BUNK6OUT, BUNK7OUT, 
    BUNK8OUT, BUNK9OUT, BUNK10OUT, BUNK11OUT, BUNK12OUT, BUNK13OUT, BUNK14OUT, BUNK15OUT, BUNK16OUT, BUNK17OUT, BUNK18OUT,
   H1YDS, H2YDS, H3YDS, H4YDS, H5YDS, H6YDS, H7YDS, H8YDS, H9YDS, H10YDS, H11YDS, H12YDS, H13YDS, H14YDS, H15YDS, H16YDS, 
    H17YDS, H18YDS
FROM   COURSE WHERE (PriKey3 =" + primaryKey  + ")";

//Now that we got this request built, 

 Hi Milael

where should I put this code is it the one below? or make a new command button I am not sure

Private Sub BindingNavigatorAddNewItem_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorAddNewItem.Click

Thanks

David

You should make a new command button, your code is in visual basic by the way, the code I provided was C#. Heres the adapted code for vb:

string primaryKey = //'Retrieve the primary key here !


Dim OleDbConnection myConnection = new OleDbConnection(connectionString)
string myCommand =" INSERT INTO [COURSES PLAYED]" // All the insert statement in one single line or aggregate using &_


//Now that we got this request built, 

 Hi Mikael
 I have made a new command button and entered the copy code in but I am stuck with a load of faults which are in Bold

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

string primaryKey = "Prikey3"

'Retrieve the primary key here !

Dim OleDbConnection myConnection = new OleDbConnection(connectionString)

string myCommand = " INSERT INTO [COURSES PLAYED]" SELECT COURSEName, H1PAR, H2PAR, H3PAR, H4PAR, H5PAR, H6PAR, H7PAR, H8PAR, H9PAR, H10PAR, H11PAR, H12PAR, H13PAR, H14PAR, H15PAR, H16PAR, H17PAR, H18PAR, H1SC, H2SC, H3SC, H4SC, H5SC, H6SC, H7SC, H8SC, H9SC, H10SC, H11SC, H12SC, H13SC, H14SC, H15SC, H16SC,H17SC, H18SC, H1TEE, H2TEE, H3TEE, H4TEE, H5TEE, H6TEE, H7TEE, H8TEE, H9TEE, H10TEE, H11TEE, H12TEE, H13TEE, H14TEE, H15TEE, H16TEE, H17TEE, H18TEE, H1FWAY, H2FWAY, H3FWAY, H4FWAY, H5FWAY, H6FWAY, H7FWAY, H8FWAY, H9FWAY, H10FWAY, H11FWAY, H12FWAY, H13FWAY, H14FWAY, H15FWAY, H16FWAY, H17FWAY, H18FWAY, BUNK1, BUNK2, BUNK3, BUNK4, BUNK5, BUNK6, BUNK7, BUNK8, BUNK9, BUNK10, BUNK11, BUNK12, BUNK13, BUNK14, BUNK15, BUNK16, BUNK17, BUNK18, PUTT1, PUTT2, PUTT3, PUTT4, PUTT5, PUTT6, PUTT7, PUTT8, PUTT9, PUTT10, PUTT11, PUTT12, PUTT13, PUTT14, PUTT15, PUTT16, PUTT17, PUTT18, GR1, GR2, GR3, GR4, GR5, GR6, GR7, GR8, GR9, GR10, GR11, GR12, GR13, GR14, GR15, GR16, GR17, GR18, BUNK1OUT, BUNK2OUT, BUNK3OUT, BUNK4OUT, BUNK5OUT, BUNK6OUT, BUNK7OUT, BUNK8OUT, BUNK9OUT, BUNK10OUT, BUNK11OUT, BUNK12OUT, BUNK13OUT, BUNK14OUT, BUNK15OUT, BUNK16OUT, BUNK17OUT, BUNK18OUT, H1YDS, H2YDS, H3YDS, H4YDS, H5YDS, H6YDS, H7YDS, H8YDS, H9YDS, H10YDS, H11YDS, H12YDS, H13YDS, H14YDS, H15YDS, H16YDS, H17YDS, H18YDS FROM COURSE WHERE (Primarykey =" + Prikey3 + ")";

'//Now that we got this request built,

Dim OleDbCommand cmd = new OleDbCommand()

cmd.CommandText = myCommand

cmd.Connection = myConnection

connection.Open()

cmd.ExecuteNonQuery()

connection.Close()

End Sub

All the insert  names are in one row but what does (All the insert statement in one single line or aggregate using &_) mean

Thanks

David

When you change line in a string you need to aggregate the string using the characters & _ in VB

string s = " this is an example" & _
"omg I changed line " &_
"then end"

Sorry, I did not convert the string:

Dim primaryKey As String = "retreive primary key"

Same thing for the command:

Dim myCommand As String = "......"

As for the new keyword in bold , you need to declare it that way in vb.net

Dim oleDbConnection myConnection As new oleDbConnection(connectionString)

Now , I think your good

Hi Mikael

Sorry for long delay of reply

I have tried all ways but just cant make it work I think I need help by somebody looking at the programme and what I am trying to do with it.
I would post a cd with it on to anyone willing to help and come to some arrangement

Thanks

David