Add rows from my flexgrid to a table

J

JFB

Hi All,
I have a edit flexgrid, after to fill with different rows manually.
How can I insert the rows of my flexgrid to my table (sql server) using
ado.net?
Any example?
I can insert one row with the code below but when I use a for loop to my
flexgrid... it doesn't work.
Also do you know any advance book for ado.net that you can recomend?
Tks in advance
JFB

Dim conn As New SqlConnection(connectionString)
'Dim insertSQL As String = "INSERT INTO
Orders(customerID,houseID,orderDate,description,employeeID,invoiceStatus)val
ues(@customerID,@houseID,@orderDate,@description,@employeeID,@invoiceStatus)
"

Dim cmd As New SqlCommand(insertSQL, conn)

Dim i As Integer

Dim Param As SqlParameter

For i = 1 To ordersFG.Rows.Count - 2

'Add the input parameters

Param = cmd.Parameters.Add("@customerID", SqlDbType.Int, 4)

Param.Value = ordersFG(i, 1)

Param = cmd.Parameters.Add("@houseID", SqlDbType.Int, 4)

Param.Value = ordersFG(i, 2)

Param = cmd.Parameters.Add("@orderDate", SqlDbType.SmallDateTime, 4)

Param.Value = ordersFG(i, 3)

Param = cmd.Parameters.Add("@employeeID", SqlDbType.Int, 4)

Param.Value = ordersFG(i, 4)

Param = cmd.Parameters.Add("@description", SqlDbType.VarChar, 255)

Param.Value = ordersFG(i, 5)

Param = cmd.Parameters.Add("@invoiceStatus", SqlDbType.Bit, 1)

Param.Value = 0

Next

Try

'Execute command

conn.Open()

Dim rows As Integer = cmd.ExecuteNonQuery

'Clear the flexgrid data

Dim rowCount

rowCount = ordersFG.Rows.Count

ordersFG.Rows.Count = ordersFG.Rows.Fixed

MsgBox("Data is already SAVE")

Catch ex As Exception

MsgBox(ex.ToString)

Return

Finally

conn.Close()

End Try
 
C

Cor

Hi JFB,

That people are using the Flexgrid without a data connection is something I
can understand.
(Althoug not necessary)

But using the Flexgrid instead of the datagrid with a database is for me not
understandable.

Helping you with that is in my idea bringing you in the wrong direction to
the solution

Just my thougth,

Cor
 
J

JFB

Hi Cor,
Tks for you reply...
mmmm.... why?
My flexgrid is for edith (combo box with dataset, datetimepicker...) and
validate the data before to insert to my table.
It doesn't makes sence?
I dont want to pull any data, that's why I'm not using datagrid, I want to
insert data to my table.
am I wrong? Can you explain more to me?
JFB
 
C

Cor

Hi JFB,

I do not know which flexgrid you are using.

But a datagrid you can bind to a datatable, dataset, dataview
(a datatable is easy to made in your program)

You can bind it (using the datasource) the only extra thing is to make
something to delete rows, inserting is a part of it.

When you bind it to a readed (filled) dataset than all changes are
registrated and you can do a simple dataadapter update of the datasettable
to write it back to the dataset.

It has a lot posibilities for styles (datagridstyles and
datagridcolumnstyles)

But see this introduction.

http://msdn.microsoft.com/library/d...tml/vbconintroductiontothedatagridcontrol.asp

I hope this gives some idea's?

Cor
 
J

JFB

Tks for reply and help Cor,
Now we are talking... I think I got the picture but not the code.
How can insert the rows to the dataset?

JFB
'Fill dataSet with orders table (I dont want to do it because I dont want to
retrieve any data)

cmdSQL.CommandText = "Select * from orders"

hAdapter.SelectCommand = cmdSQL

hAdapter.Fill(hSet, "orders")

'Here is the problem

hSet.Tables(0).Rows(myFlexgrid. ??? ).insert ????

'To update the adapter

hAdapter.update(hSet)
 
C

Cor

Hi JFB,

Asuming that you can want to process the items from the flexgrid I do this
in psuedo because I do not know the flexgrid.

To fill the flexgrid (I hope this is posible)
\\\as far as the flexgrid pseudo the rest maybe typos but further probably
correct
dim i as integer
dim y as integer
for i = 0 to hset.tables(0).rows.count-1
for y = 0 to hset.tables(0).columns.count - 1
flexgrid.row(i).item(y) = hset.tables(0).rows(i).(y)
next
next
///
If you do not use a part of the for loop with the y you also can do
\\\
flexgrid.row(i).itemx = hset.tables(0).rows(i).item("itemx")
///
How to update the dataset from the flexgrid I think that that you should
find out first yourself because that is just how you use it. But you have to
do that with every event in the flexgrid.

And keep in mind not to use the "remove" method from the dataset.

I hope this helps?

Cor


"> Tks for reply and help Cor,
 
J

JFB

Cor I forgot to tell you that this flexgrid has the same properties as
datagrid.
How do you update the dataset with you datagrid?
Tks again
JFB
 
C

Cor

Hi JFB

Basicly, just bind it and than you do not have to do that for each loop to
fill it either, something like
\\\
dg.datasource=hset.tables(0)
///
And then
\\\
DirectCast(BindingContext(hset.Tables(0)), CurrencyManager).EndCurrentEdit()
///
Before
\\\
hAdapter.update(hSet)
///
The last can nicer have for that a look to haschanges and getchanges.

I hope this helps?

Cor
 
J

JFB

FYI... I found the solution over the weekend reading the SQL Server Magazine
InstantDoc ID 40791-Listing2.
This solution in not like you suggest as one line code but it works for
me now.
Part of the problem with you suggestion is that I dont have the
invoiceStatus column in my flexgrid and I have to setup this value as zero.
Tks again
JFB

Dim conn As New SqlConnection(connectionString)

Dim insertSQL As String = "INSERT INTO
Orders(customerID,houseID,orderDate,description,employeeID,invoiceStatus)val
ues(@customerID,@houseID,@orderDate,@description,@employeeID,@invoiceStatus)
"

Dim cmd As New SqlCommand(insertSQL, conn)

'Declaring all the input parameters

Dim parmCustomerID = New SqlParameter("@customerID", SqlDbType.Int, 4)

parmCustomerID.direction = ParameterDirection.Input

Dim parmHouseID = New SqlParameter("@houseID", SqlDbType.Int, 4)

parmHouseID.direction = ParameterDirection.Input

Dim parmOrderDate = New SqlParameter("@orderDate", SqlDbType.SmallDateTime,
4)

parmHouseID.direction = ParameterDirection.Input

Dim parmEmployeeID = New SqlParameter("@employeeID", SqlDbType.Int, 4)

parmEmployeeID.direction = ParameterDirection.Input

Dim parmDescription = New SqlParameter("@description", SqlDbType.VarChar,
255)

parmDescription.direction = ParameterDirection.Input

Dim parmInvoiceStatus = New SqlParameter("@invoiceStatus", SqlDbType.Bit, 1)

parmInvoiceStatus.direction = ParameterDirection.Input

'Add the parameter objects to the cmd parameter collection

cmd.Parameters.Add(parmCustomerID)

cmd.Parameters.Add(parmHouseID)

cmd.Parameters.Add(parmOrderDate)

cmd.Parameters.Add(parmEmployeeID)

cmd.Parameters.Add(parmDescription)

cmd.Parameters.Add(parminvoicestatus)

Try

conn.Open()

cmd.Prepare()

Dim i As Integer

For i = 1 To ordersFG.Rows.Count - 2

'Add the values to the parameters

parmCustomerID.value = ordersFG(i, 1)

parmHouseID.Value = ordersFG(i, 2)

parmOrderDate.Value = ordersFG(i, 3)

parmEmployeeID.Value = ordersFG(i, 4)

parmDescription.Value = ordersFG(i, 5)

parmInvoiceStatus.Value = 0

cmd.ExecuteNonQuery()

Next

Catch ex As Exception

MsgBox(ex.ToString)

Return

Finally

conn.Close()

End Try
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

get value fro a table 2
ComboBox and msflexgrid 1
Getting Return Value From Stored Proccedure (Part 2) 2
click event handler executed twice 7
Error: Argument 'Prompt' cannot be converted to type 'String' 1
Error when trying to run a stored procedure 1
insert with stored procedure - no errors, but no insert 6
Insert/Update tables in a database with relational data from an XML file 1

Top