Question Show data according to SQL statement

[mistyfy]

Hi all,
i want to show my data in datagridview according to the SQL select stament with some condition.

Eg: in a table of stock will be classify with softwar and hardware, when a user login from hardware department then the datagridview will on display those stock under hardware, instead of show both software and hardware stock.

here is the code so far what i had but is not working:

 Dim conn As New SqlClient.SqlConnection
        With conn
            .ConnectionString = "SERVER =   ; DATABASE =  ; UID = root; PASSWORD =  "  
            .Open()
        End With

        Dim cmd As New SqlClient.SqlCommand
        With cmd
            If Form_FPCOSTM003S.Label_Machine_Code.Text = "BAH1" Or Form_FPCOSTM003S.Label_Machine_Code.Text = "SG1" Then
                 .CommandText = "SELECT * FROM Machine_Material_Data WHERE Mac_Mcode = '" & Form_FPCOSTM003S.Label_Machine_Code.Text.Trim & "' AND Mac_MatTypeCode = 'GLUE' ORDER BY Mac_MCode"
                  .Connection = conn
             Else
                .CommandText = "SELECT * FROM Machine_Material_Data WHERE Mac_Mcode = '" & Form_FPCOSTM003S.Label_Machine_Code.Text & "' ORDER BY Mac_MCode"
            End If

        End With

Any idea??

thanks in advance!

 

[daniel02_0403]

You need to refill your dataset with your new data after assigning your command text ...

to do this .. you need a command,
a data adapter and assign your command as the select command of the data adapter,
execute the data adapter's select command and fill your dataset with data from you data adapter ...

of course you need to open your connection before executing the data adapter's select command....

 

[mistyfy]

Can i have the sample coding of refil the dataset? How to fill the dataset to the data table ?

 

[cjard]

Please read the DW2 link in my signature, section Creating a Simple Data App

 

[daniel02_0403]

Dim conn As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
Dim da As New SqlClient.SqlDataAdapter
Dim ds As New DataSet

conn.ConnectionString = "<your connection string>"
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = "<your sql statement here>"
da.SelectCommand = cmd
ds.Tables.Add("sample")
ds.Tables("sample").Clear()
conn.Open()
da.SelectCommand.ExecuteNonQuery()
da.Fill(ds.Tables("sample"))
conn.Close()

Me.DataGridView1.DataSource = ds.Tables("sample")

 

[mistyfy]

Thanks alot!!! I can get the result from the DGV

Thanks!

 

[melissa_77]

Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\dbcoba.mdb")
Dim cmd As New OleDb.OleDbCommand
Dim da As New OleDb.OleDbDataAdapter
Dim a As String
Dim ds As New DataSet

a = "INSERT INTO MHS VALUES ('" & TextBox2.Text & "')"
cmd = New OleDb.OleDbCommand(a, conn)
da.SelectCommand = cmd
conn.Open()
da.SelectCommand.ExecuteNonQuery()
ds.Tables.Clear()
ds.Tables.Add("MHS")
ds.Tables("MHS").Clear()
da.Fill(ds.Tables("MHS"))


conn.Close()
Me.DataGridView1.DataSource = ds.Tables("MHS")

why it doesn't work???
when the sqlcomment is SELECT * FROM the datagridview show the result.
but if the sqlcomment is INSERT INTO the datagridview don't show.

 

[daniel02_0403]

it seems that you are redeclaring you cmd


Dim cmd As New OleDb.OleDbCommand -> declaration
.
.
.
cmd = New OleDb.OleDbCommand(a, conn) -> you are redclaring cmd


all you must do is just

cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = a

 

[cjard]

why it doesn't work???
when the sqlcomment is SELECT * FROM the datagridview show the result.
but if the sqlcomment is INSERT INTO the datagridview don't show.

It's an insert query? What would you like it to show?

 

[cjard]

it seems that you are redeclaring you cmd

I know youre trying to be helpful, but do please at least attempt to answer the question that is asked.. unless you specifically state "This is not an answer to your problem but, as a quick codeing style suggestion..." or something similar

Your post reads like changing a few assignments around will fix the problem, when the issue is actually that the OP is attempting to use an INSERT query to Fill() a datagrid.. Something of an oxymoron to use an UPLOAD op to achieve a DOWNLOAD goal

 

[daniel02_0403]

I know youre trying to be helpful, but do please at least attempt to answer the question that is asked.. unless you specifically state "This is not an answer to your problem but, as a quick codeing style suggestion..." or something similar

Your post reads like changing a few assignments around will fix the problem, when the issue is actually that the OP is attempting to use an INSERT query to Fill() a datagrid.. Something of an oxymoron to use an UPLOAD op to achieve a DOWNLOAD goal

thanks cjard ... i'm sorry .. it's my bad

i didn't notice the question on the last line of its post ..

i was quite in a hurry when i posted my reply ....

what i thought was that its query was not working at all .....

thanks

 

[cjard]

Maybe you need more sleep