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

hello friends I have two tables with column A and column B. I want to update column B of table9 with column B of table1 . I am trying this query in ms access from Vb.net. It pastes the column below the existing data. How do I write a update query that will replace the existing column? update table1 t1 LEFT JOIN table9 T9 on t1.a = t9.a SET t9.b = t1.b Thanks

hello friends I have a table with three columns col1,col2 and col3. how do I add rows based on column1? col1|col2|col3| A | 2 | 4 | B | 6 | 8 | A | 10 |12| C | 14 |16| C | 3 | 2 | Output col1 col2 col3 ----|-------|-------| A | 12 | 16| B | 6 | 8| C | 17 |18| 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 you show me any sample code or query . I have tried to query the system tables such as MSysAccessObjects MSysAccessXML MSysACEs MSysObjects MSysQueries public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button2_Click(object sender, EventArgs e) { } private static int getTotal(string connectionString) { int total = 0; using (var connection = new OleDbConnection("")) { connection.Open(); var command = new OleDbCommand("SELECT TABLE_NAME FROM MSysAccessObjects.COLUMNS WHERE COLUMN_NAME = 'Total';" , connection); foreach (var table in from IDataRecord row in command.ExecuteReader() select new { name = row.GetValue(row.GetOrdinal("TABLE_NAME")) } ) { command.CommandText = String.Format("SELECT SUM(Total) AS Total FROM {0}", table.name); var tableTotal = command.ExecuteScalar(); total = total + (tableTotal == DBNull.Value ? 0 : (int)command.ExecuteScalar()); } connection.Close(); } return total; } I have found a function similer to my problem but I dont know if it will work. stackoverflow.com/.../how-sum-values-in-dynamic-table-columns- function sumQty(tableID) { var total = 0; var table = document.getElementById(tableID); var rowCount = table.rows.length; for (var i = 0; i < rowCount; i++) { var row = table.rows[i]; var colCount = row.cells.length; for (var j = 0; j < cellCount; j++) { var node = row.cells[j].childNodes[0]; if (node.name == "qty[]") { total += parseInt(node.value); } } } return total;} I also tried the the query as a single statement CmdStr1 = "SELECT 'SELECT ISNULL(SUM('+COLUMN_NAME + '),0) AS Tot FROM ' + TABLE_NAME FROM MSysObjects .COLUMNS WHERE COLUMN_NAME = 'Total'"

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