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

Asked By David on 29-Aug-11 04:57 PM
Hi All I am still stuck and getting desperate  I have been trying to finish this prog for 20 months

Below is the Dataset Tableadaptor query which if you click execute the query it works perfect when in Query Builder but what I am trying to do is have the option on a datagridview page to select the (Prikey3) number and run the  query with my program running this I am unable to do because I dont know how to set the code up
I dont know if I am trying to do it the wrong way.

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 = ?)

Thanks again I appreciate your help

David
Mikael Gonella replied to David on 08-Sep-11 04:16 PM
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();
David replied to Mikael Gonella on 08-Sep-11 04:25 PM
 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
Mikael Gonella replied to David on 08-Sep-11 04:35 PM
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?

David replied to Mikael Gonella on 08-Sep-11 04:45 PM
 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
 
Mikael Gonella replied to David on 08-Sep-11 04:55 PM
Ahh , the code I supplied you was for SqlServer, I assume the database is on access 2007?
David replied to Mikael Gonella on 08-Sep-11 05:38 PM
 Yes Mikael

Access 2007

 Thanks

 David
Mikael Gonella replied to David on 11-Sep-11 12:53 PM

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, 
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = myCommand;
cmd.Connection = myConnection
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
David replied to Mikael Gonella on 11-Sep-11 03:21 PM
 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

Mikael Gonella replied to David on 11-Sep-11 09:17 PM
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, 
Dim OleDbCommand cmd = new OleDbCommand()
cmd.CommandText = myCommand
cmd.Connection = myConnection
connection.Open()
cmd.ExecuteNonQuery()
connection.Close()
David replied to Mikael Gonella on 12-Sep-11 04:30 PM
 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

Mikael Gonella replied to David on 14-Sep-11 10:09 AM
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

David replied to Mikael Gonella on 07-Nov-11 04:35 PM
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