Jump to content

rony_78

Members
  • Posts

    26
  • Joined

  • Last visited

rony_78's Achievements

Newbie

Newbie (1/7)

0

Reputation

  1. 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
  2. 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
  3. 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"})
  4. Thank you for the link
  5. 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"
  6. 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
  7. 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
  8. I could not get any of them to work. I trying to build SQL statement at run time without hard coding table names.
  9. 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'"
  10. 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 .
  11. 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.
  12. 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
  13. 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
  14. 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 .
×
×
  • Create New...