How to save Datatable content into Sql database Table

[Leodumbi]

gents

please help on the following. I'm creating an small application where I search from the databse table tems and add the result line to a datarow of my datatable "dt", I the set the datagridviewe datatsource to this same dt.

My question is: How can I save this dt content into another table of the same database.
I'm not ussing stored procedures and dont understand much of parameters. can you please give suggestions/Ideas?

thank ypu very much

CODE

Dim cs As New SqlConnection("Data Source=myserver;Initial Catalog=mydatabase;Integrated Security=True")
        
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet("Chosed")
        Dim cmd As New SqlCommand
        'Dim dr As DataRow = (ds.Tables("Chosed")).NewRow

        cs.Open()


        cmd = cs.CreateCommand
        cmd.CommandText = "SELECT DESCRITION, QTTY  from ITEMS WHERE CODE = '" & Trim(txtITEMSearch.Text) & "'"
        da.SelectCommand = cmd

        da.Fill(ds, "Chosed")

        linha = dt.NewRow
        linha("DESCRITION") = ds.Tables("Chosed").Rows(0).Item("DESCRITION").ToString
        linha("QTTY  ") = 1 
        
        dgSales.DataSource = dt

/CODE

Leo

[M.Waqas Aslam]

hello !
please can you tell me , you want to save data from grid , or from your datatable.

[Leodumbi]

Hi!

Thanks for your replay.
I would like to save from either one of them. I dont really have a preference since I've never done it before. so, I would appreciate anything that alowed me to pass the data to the database.

Thanks
Leo

[M.Waqas Aslam]

ok now please use this code to insert your data into db , using grid ,
for example we have two columns , fname , and lname , in grid , and we want to insert them ,

sub myInsert(byval fname as string , byval lname as string)
'in above i declear two variables fname , lname 
Try
dim con as new sqlconnection("you connection string")
dim cmd as new sqlcommand 
con.open()
cmd.connection= con
cmd.commandtext="insert into table (fname,lname) values (@fname,@lname)"
cmd.Parameters.AddWithValue("@fname", fname)
cmd.Parameters.AddWithValue("@lname", lname)
cmd.ExecuteNonQuery()
Con.close()
Catch ex As Exception
MsgBox(Err.Description)
End Try
end sub
'after this sub , you can just call it any where in your form , now after making this sub at top of your form where we declare global variables ,use this code to call it 
dim i as integer
try
for i = 0 to datagrid.rows.count-1 
myInsert(datagrid.item(0,i).tostring() , datagrid.item(1,i).tostring())
'note , you can also give the column name of your grid in place of column index , like this datagrid.item("fname",i).tostring()
next
Catch ex As Exception
MsgBox(Err.Description)
End Try

by using above code you can insert your records in your db .hope this will solve your prob , if yes them please mark this thread solved and vote me up :)

Best Regards

[Leodumbi]

Hi waqasaslammmeo

it definitely helped me a lot and I'll mark it complete but I'just one last question.
when I tried to apply to my project it gave an error because the QUANTITY field is integer. It said something about being truncated. can you give me guidance?

Thanks
Leo

[M.Waqas Aslam]

yes , at the top we declare variables , byval fname as string , here you can change string to integer , and also in your for loop where you are getting values from the grid , use val(datagrid.item(0,i).tostring()) , it will work perfectly , but if you have prob with that please post your full code , so that i can better ans you.

Regards

[Leodumbi]

Hi waqasaslammmeo

I appreciated the help you gave on about save grid content into db. however I got one more doubt and hope you'd help.

I keep getting an error saying "Parametized query (@Name nvarchar(400)... Expects @Name, wich was not sopllied.
but before that I get a stranhe error:

invalid column name 'Name'
invalid column name 'Age'
invalid column name 'phone'

please help
Thnks
Leo