rony_78

Could somebody tell me how to update table with datagridview with blank cell in it?
I have created a data entry form with datagridview with two columns in designer. I want to leave some cells of a column blank and save blank cells as zero in table. If there are no blank cells I can save datagridview content into table

Dim thisConnection As New SqlConnection()
Dim nonqueryCommand As SqlCommand = thisConnection.CreateCommand()

   Try
           ' Open Connection
           thisConnection.Open()
           Console.WriteLine("Connection Opened")

           ' Create INSERT statement with named parameters
           nonqueryCommand.CommandText = _
              "INSERT  INTO myTable (Col1, Col2) VALUES (@Col1, @Col2)"
' Add Parameters to Command Parameters collection
           nonqueryCommand.Parameters.Add("@Col1", SqlDbType.VarChar, 50)
           nonqueryCommand.Parameters.Add("@Col2", SqlDbType.VarChar, 50)

           ' Prepare command for repeated execution
           nonqueryCommand.Prepare()

           ' Data to be inserted
           For Each row As DataGridViewRow In DataGridView1.Rows
             If Not row.IsNewRow Then
                 nonqueryCommand.Parameters("@Col1").Value = row.Cells(0).Value.ToString
                 nonqueryCommand.Parameters("@Col2").Value = row.Cells(1).Value.ToString
             End If
           Next

           nonqueryCommand.ExecuteNonQuery()

       Catch ex As SqlException
           ' Display error
           Console.WriteLine("Error: " & ex.ToString())
       Finally
           ' Close Connection
           thisConnection.Close()
           Console.WriteLine("Connection Closed")

       End Try 

I don’t know if this is correct way to check for empty cell in order to save into table. I get an error when I place the code between try and Catch ex As SqlException

An OleDbParameter with ParameterName '@Col1' is not contained by this OleDbParameterCollection

If row.Cells(0).Value.ToString IsNot Nothing Then
   nonqueryCommand.Parameters("@Col1").Value = row.Cells(0).Value.ToString()
else
   nonqueryCommand.Parameters("@Col1").Value = "0"
end if

Dim thisConnection As New OleDbConnection("")
        Dim nonqueryCommand As OleDbCommand = thisConnection.CreateCommand()
       
        Try

            ' Open Connection
            thisConnection.Open()
            Console.WriteLine("Connection Opened")
            nonqueryCommand.CommandText = _
               "INSERT  INTO Table1 (Col1, Col2) VALUES (@Col1, @Col2)"
            ' Data to be inserted 
            For Each row As DataGridViewRow In DataGridView1.Rows
                If row.Cells(0).Value.ToString IsNot Nothing Then
                    nonqueryCommand.Parameters("@Col1").Value = row.Cells(0).Value.ToString()
                Else
                    nonqueryCommand.Parameters("@Col1").Value = "0"
                End If

                If row.Cells(1).Value.ToString IsNot Nothing Then
                    nonqueryCommand.Parameters("@Col1").Value = row.Cells(1).Value.ToString()
                Else
                    nonqueryCommand.Parameters("@Col1").Value = "0"
                End If
            Next

            nonqueryCommand.ExecuteNonQuery()

        Catch ex As OleDbException
            ' Display error
            Console.WriteLine("Error: " & ex.ToString())
        Finally
            ' Close Connection
            thisConnection.Close()
            Console.WriteLine("Connection Closed")

        End Try

I have a datagridview with two combo box columns bound to two columns in the same table such as col1,col2 . I want combo box2 to display adjacent row data when I select an item in combo box1.Is there any way to do it? Any bit of info would be helpful Thanks

Is it possible to fill a Combo Box with names of tables in ms access with space Or replace underscores in the table names with whitespace ? I have wrapped the table name with bracket .this display system.data.datarows in combobox .Me.combobox1.DisplayMember = "[TABLE_NAME]" Me.combobox1.ValueMember = "[TABLE_NAME]"Me.combobox1.DataSource = Me.con.GetSchema("[TABLES]", New String() {Nothing, Nothing, Nothing, "TABLE"})

Thank you for the link

I am trying to filter a table between two dates in ms access from vb.net . the query below does Not work when data type is date/ time . When I change data type to text, it gives me sum of the columns. Is there anyway to fix it?

Thanks

select * into Table3 from (SELECT 'Table6' AS [Table], SUM(a) - SUM( AS Result FROM table6 where date_ BETWEEN " & FromDate & " AND " & ToDate & " UNION ALL SELECT 'Table7', SUM(a) - SUM(  FROM table7 where date_ BETWEEN " & FromDate & " AND " & ToDate & ")a"

I get no runtime error

 update table1 t1 LEFT JOIN table9 T9 on t1.a = t9.a SET t9.b = t1.b 

Thanks

I am running the query in ms access from vb.net, can someone tell me why it does not work

select col1, SUM(col2) as col2, SUM(col3) as col3  from table1  group by col1

Thanks

I could not get any of them to work. I trying to build SQL statement at run time without hard coding table names.

Can somene help me with this I'm having difficulty figuring out how to sum column values in dynamic tables. I am using create table statement passing text box input to the query with a column name 'total'. Using another insert query i am saving tables name in another table called table_list. I am looking for a way to get the sum of column 'total' of all created tables .

SELECT "Table1" AS Table, SUM(a) - SUM( AS Result FROM table1UNIONSELECT "Table2", SUM(a) - SUM( FROM table2UNIONSELECT "Table3", SUM(a) - SUM( FROM table3

It gives me this output table | result-------|-------- I would like to know how do I update the below table with "result" column from output table?name | table | result------|-------|--------I have tried to save the output table in a new table then reference the new table to update the other table. I get this Error.

I'm running this query on an MDB file from vb.Net, ;select * into NewTablefrom ( SELECT 'Table1' AS [Table], SUM(a) - SUM( AS Result FROM table1 union all SELECT 'Table2' AS [Table], SUM(a) - SUM( AS Result FROM table2) The inner SELECT works fine but when wrapped in the outer SELECT, I get the error "An action query cannot be used as a row source."Please tell me how can I fix this

I have the following sql query in vb.net and ms access , how do I create a table from the query result ?

SELECT 'Table1' AS [Table], SUM(a) - SUM( AS Result FROM table1  

I have tried but it does not work

create table tble10 as SELECT 'Table1' AS [Table], SUM(a) - SUM( AS Result FROM table1 

any help would be appreciated

I have a table with column1 and column2. How do i write a query to search in column1 for last two matching words and write '1' and '2' in column3. All datas in col1 contains last two word either ab or cd. Help me to get started .

can someone tell me how do I open selected table in another datagridview ? Please help me to solve it.thanks

Dim con As SqlCeConnection = New SqlCeConnection("Data Source=|DataDirectory|\Northwind.sdf;Password=***;Persist Security Info=True")		Dim Query2 As String = " Select * from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'TABLE'"	   Dim DA As SqlCeDataAdapter, Ds As New DataSet, Dtb As New System.Data.DataTable	   con.Open()	   DA = New SqlCeDataAdapter(Query2, con)	   DA.Fill(Ds)	   con.Close()	   Dtb = Ds.Tables(0)	   DataGridView1.DataSource = Dtb

I want to create a table from an existing table with copying values from the existing table , I am using vb.net and ms accessI am getting syntax error in create table statement , can someone help me to solve it

Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\dtb.accdb  ")Dim CmdStr As StringCmdStr = "CREATE TABLE table1 AS (SELECT col1,col2,col3 FROM mytable)"con.Open()Dim cmd As OleDbCommand = New OleDbCommand(CmdStr, con)cmd.ExecuteNonQuery()con.Close()MsgBox("Done")

I am trying to learn how to add an expression column in datagridview but its not giving me correct out putcan someone show me how to do it?

	 Dim dt As New DataTable		dt.Columns.Add("col1")		dt.Columns.Add("col2")		dt.Columns.Add("col3")		dt.Columns.Add("col4")		dt.Columns.Add("total")		dt.Rows.Add("")		dt.Rows.Add("")		dt.Rows.Add("")		dt.Rows.Add("")		Me.DataGridView1.DataSource = dt		dt.Columns("total").Expression = "[col2]+[col3]"

hi friendsI am having difficulty to insert two rows in database table from datagridview. I can insert one row , I would like to know how do i run nonqueryCommand.Parameters inside For Each loop twice to insert two row. please help me

Dim thisConnection As New OleDbConnection("Provider=Microsoft.ACE.… Source=C:\Dtb.accdb")'Create Command objectDim nonqueryCommand As OleDbCommand = thisConnection.CreateCommand()Try' Open ConnectionthisConnection.Open()Console.WriteLine("Connection Opened")' Create INSERT statement with named parametersnonqueryCommand.CommandText = _"INSERT INTO myTable (Col1, Col2) VALUES (@Col1, @Col2)"' Add Parameters to Command Parameters collectionnonqueryCommand.Parameters.Add("@Col1"… OleDbType.VarChar, 50)nonqueryCommand.Parameters.Add("@Col2"… OleDbType.VarChar, 50)' Prepare command for repeated executionnonqueryCommand.Prepare()' Data to be insertedFor Each row As DataGridViewRow In DataGridView1.RowsIf Not row.IsNewRow ThennonqueryCommand.Parameters("@Col1").Va… = row.Cells(0).Value.ToStringnonqueryCommand.Parameters("@Col2").Va… = row.Cells(1).Value.ToStringEnd IfNextnonqueryCommand.ExecuteNonQuery()Catch ex As OleDbException' Display errorConsole.WriteLine("Error: " & ex.ToString())Finally' Close ConnectionthisConnection.Close()Console.WriteLine("Connection Closed")End Try

Hi friendsI am trying to learn how do i calculates between columns such as col1*col2=col3 col4-col3=col5 when i add columns and rows in datagridview like this .im not familiar with terminologies yet, i have added columns with rows in in datagridview and put it in form load event..

DataGridView1.ColumnCount = 5DataGridView1.Columns(0).Name = "col1"DataGridView1.Columns(1).Name = "col2"Dim row As String() = New String() {}DataGridView1.Rows.Add(row)row = New String() {}

can someone provide me a link or a sample code please

i am having difficulty populating combobox with sql server compact 3.5 tables.i am encountering the following error "NotSuppoertedException was unhandled "in this line Error - Me.ComboBox1.DataSource = Me.con.GetSchema("TABLES")can someome help me on how do i use the sql query instead of GetSchema method.

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  

Private con As New SqlCeConnection("Provider=;Data Source= C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples\Northwind.sdf;Persist Security Info=False")  Private adapter As New SqlCeDataAdapter(String.Empty, Me.con)  Private data As DataTablePrivate Sub Form1_Load(ByVal sender As Object, _		   ByVal e As EventArgs) Handles MyBase.Load	con.Open()	Me.ComboBox1.DisplayMember = "TABLE_NAME"	Me.ComboBox1.ValueMember = "TABLE_NAME"	Me.ComboBox1.DataSource = Me.con.GetSchema("TABLES")	con.Close()  End Sub  Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, _					  ByVal e As EventArgs) Handles ComboBox1.SelectedIndexChanged	If Me.ComboBox1.SelectedItem IsNot Nothing Then	  Me.data = New DataTable	  Me.adapter.SelectCommand.CommandText = String.Format("SELECT * FROM [{0}]", Me.ComboBox1.SelectedValue)	  Me.adapter.Fill(Data)	  Me.DataGridView1.DataSource = Nothing	  Me.DataGridView1.Columns.Clear()	  Me.DataGridView1.DataSource = Me.data  	End If  End Sub

It's really helpful

Thanks -

I am new to vb.net .I would like to know how do i insert text box value into combobox selected table ?i can load tables names in combobox but how do i insert into combobox selected table?Thanks in advance

Private Sub Form1_Load(ByVal sender As Object, _					   ByVal e As EventArgs) Handles MyBase.Load		con.Open()		Me.ComboBox1.DisplayMember = "TABLE_NAME"		Me.ComboBox1.ValueMember = "TABLE_NAME"		Me.ComboBox1.DataSource = Me.con.GetSchema("TABLES", New String() {Nothing, Nothing, Nothing, "TABLE"})		con.Close()	End SubPrivate Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click		Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\testdb2.accdb")	  Dim CmdStr As String = "insert into table1 (a,b,c) values ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "')"		con.Open()		Dim cmd As OleDbCommand = New OleDbCommand(CmdStr, con)		cmd.ExecuteNonQuery()		con.Close()		MsgBox("Done")	End Sub

can someone please tell me how can i join two table . table1col A colBtable2colC colDHow do i join like this col A colB colC colD