Silverlight / WPF - Still not a clue how to cal and run a insert command text query

Asked By David on 22-Jan-12 04:20 PM
Hi All
I have asked many times and I am still no closer to making the insert command work.
I am using an access 2007 data base and wpf window with a Datagrid view to select the Prikey I want to insert a complete row of data from COURSE - Table into COURSES_PLAYED table  using the tableadapter insert command query.
What code do I need to put in before and after the query to make it run.
Should I be using Add new item button or creating a new one.?
Am I using the wrong type of form.?

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 David
[)ia6l0 iii replied to David on 22-Jan-12 09:21 PM
It is a loaded Q.  

What do you have currently on your WPF form?
And how do you connect to your access database?
What is the control in your form that holds the value of primarky key 3? (the one that is used in your where clause)

This is how I would do it:

a. Create a model which is exact replica of the row that you are trying to insert to. 
b. Add a new button on XAML - called "Insert Row"
c. Add input fields on the XAML that can fill the model , and thus create a row. 
d. Access the model, transalate the model to xml serialized value ,and send it to the database. 
e. In my database, I would parse the XML and build the row that has to be inserted. 

Hope this helps
David replied to [)ia6l0 iii on 23-Jan-12 04:44 PM
Hi

On the WPF form I have a DatagridView which brings up all the selections of courses to play

Connection to Data base I think is like below

Dim connectionString As String = ConfigurationManager.ConnectionStrings("WpfDataEntry.MySettings.VB_DAVE_1__mainConnectionString").ConnectionString

Dim ds As New DataSet()

Try

' Connect to the database and run the query.

Dim connection As New SqlConnection(connectionString)

Dim adapter As New SqlDataAdapter(queryString, connection)

' Fill the DataSet.

adapter.Fill(ds)

The Primary key called "Prikey3" is from the Data base (access 2007)
The query in the Dataset runs ok and inserts a selected row into the table when in edit mode but I cant connect and run when programme is running dont know how to call the query and run it.

Thanks
David


[)ia6l0 iii replied to David on 23-Jan-12 07:39 PM
I still can't understand. 

You have already passed the query that has to be executed in the SQLDataAdapter constructor.
Dim adapter As New SqlDataAdapter(queryString, connection)

And when you call the fill method, that query executes and fills the dataset.
adapter.Fill(ds)
I think what you mean is that , you need to run an insert query on the same table. Do the following:

Create an Insert query
string insertQuery = @"Insert into tablename(col1, col2, col3) values(@val1,@val2,@val3)";
SqlCommand insertCommand= new SqlCommand(insertQuery , conn);
insertCommand.Parameters.Add("@val1",SqlDbType.NVarChar, 10,"giveaname");
insertCommand.Parameters.Add("@val2",SqlDbType.NVarChar, 10,"giveaname");
insertCommand.Parameters.Add("@val3",SqlDbType.NVarChar, 10,"giveaname");
dataupdater.InsertCommand = insertCommand;
dataupdater.Update(dataset, "name");

Let me know if I misunderstood you.
David replied to [)ia6l0 iii on 25-Jan-12 04:20 PM

Thanks for the reply but I am still not sure how to call and run the query.
Can you show me the order I should have the code etc after the ,

Private

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

This is where I need to be able to run the query from the Tableadapter and do the following but I have no idea what is required and in what order.

Insert into COURSESPlayed "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 =" + ""
What I want to be able to do is select the Primarykey No from Datagridview and insert the selected line from table COURSE into table COURSESPlayed
Any help would be super Thanks

David