litMsg is a "literal", a Server Control from the toolbox/Web Forms Tab.
When you first posted the thread you wrote
"And in Page_Load.." and "Response.Write"
so I thought your application must be an ASP. NET application, since in a Windows Forms Application the event would be "Form... Load", and there normally is no "Resonse.Write" in a Windows Forms application.
Sorry, I do not understand what you mean with "Importing some API"
The RT must be able to instantiate a class named "CrystalReport1", else the line Dim objRpt As New CrystalReport1 cannot work.
Normally you would create such a class by adding a crystal report named "CrystalReport1.rpt" into your project. For VS 2003:
After this there is a report (=class) CrystalReport1 in your project that can be instantiated.
Maybe it is because there is no report (=class) "CrystalReport1" in your project.
Could it be that the file does not only contain PL/SQL? Comments in PL/SQL are
/* comment
comment */or -- comment Calling PL/SQL from VB .NET may basically be performed by executing the PL/SQL text like:
Dim sSQL As String
Try
'create and use a function
sSQL = "CREATE OR REPLACE FUNCTION MyMultiply( " & vbLf & _
"ifactor1 IN NUMBER, " & vbLf & _
"ifactor2 IN NUMBER " & vbLf & _
") RETURN NUMBER IS" & vbLf & _
" iResult NUMBER;" & vbLf & _
"BEGIN" & vbLf & _
" iResult:=iFactor1*iFactor2;" & vbLf & _
" RETURN iResult;" & vbLf & _
"END;"
Dim aCmd As New OracleCommand(sSQL, OracleConnection1)
aCmd.CommandType = CommandType.Text
OracleConnection1.Open()
aCmd.ExecuteNonQuery()
'
aCmd.CommandText = "SELECT MyMultiply(5, 7) FROM dual"
MessageBox.Show(aCmd.ExecuteScalar().ToString)
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
OracleConnection1.Close()
End Trybut there are traps for example using multiple PL/SQL statements in one call to "Execute...".
Please check if the dbms supports grouping for the datatype that is used for the fingerprint column.
If it does not and there is a key in the table to identify each record, ýou may use two loops, as shown below. This solution has a very long runtime if there are many records in the table.
Private Sub Command2_Click()
Dim aConn As ADODB.Connection
Set aConn = New ADODB.Connection
aConn.ConnectionString = "DSN=VPE;"
aConn.Open
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim sDups As String
Dim sSQL1 As String
Dim sSQL2 As String
Dim iCnt As Integer
sDups = ""
iCnt = 0
sSQL1 = "SELECT keycol, fprintcol FROM table1 ORDER BY keycol"
Set rs1 = New ADODB.Recordset
Call rs1.Open(sSQL1, aConn)
Do While Not rs1.EOF
sSQL2 = "SELECT keycol, fprintcol FROM table1 WHERE keycol <> '" & rs1!keycol & "'"
Set rs2 = New ADODB.Recordset
Call rs2.Open(sSQL2, aConn)
Do While Not rs2.EOF
'comparing, for example:
If rs2!fprintcol = rs1!fprintcol Then
'do not remember the same key multiple times
If InStr(sDups, "|" & Trim(rs1!keycol)) = 0 Then
sDups = sDups & "|" & Trim(rs1!keycol)
iCnt = iCnt + 1
End If
End If
rs2.MoveNext
Loop
rs1.MoveNext
Loop
MsgBox Replace(sDups, "|", vbLf)
End SubIn most cases you would use a dataset object to keep a copy of database data inside your program, then insert, change and delete the rows in the dataset and, when done, you would use dataadapters to write the changes from the dataset to the database. But this is too much to be solved in a forum thread. There are books available, for example David Sceppa, Programming ADO.NET.
For directly inserting and deleting data into a database (using a literal on the form to show feedback):
Insert:
Private Sub btnInsertDirect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles btnInsertDirect.Click
Dim iRet As Integer
Dim iNextId As Integer
Dim sConnectionString As String = "server=(local);database=contacts;user=ASPNET"
Dim conn As New SqlConnection(sConnectionString)
Dim sSQL As String
sSQL = "INSERT INTO contacts (contactid, firstname, lastname)" & _
" VALUES (@contactid, @firstname, @lastname)"
Dim cmd As New SqlCommand(sSQL, conn)
iNextId = 123
cmd.Parameters.Add(New SqlParameter("@contactid", 123))
cmd.Parameters.Add(New SqlParameter("@firstname", txtFirstName.Text))
cmd.Parameters.Add(New SqlParameter("@lastname", txtLastName.Text))
conn.Open()
Try
iRet = cmd.ExecuteNonQuery()
litMsg.Text = String.Format("Inserted {0} records", iRet)
Catch ex As System.Exception
litMsg.Text = String.Format("Error: {0}", ex.ToString)
Finally
conn.Close()
End Try
End SubDelete:
Private Sub btnDeleteDirect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteDirect.Click
Dim iRet As Integer
Dim sConnectionString As String = "server=(local);database=contacts;user=ASPNET"
Dim conn As New SqlConnection(sConnectionString)
Dim sSQL As String
sSQL = "DELETE FROM contacts WHERE lastname = '" & txtLastName.Text & "'"
Dim cmd As New SqlCommand(sSQL, conn)
conn.Open()
Try
iRet = cmd.ExecuteNonQuery()
litMsg.Text = String.Format("Deleted {0} records", iRet)
Catch ex …I did not analyze the algorithm. Concerning the types that are used:
If a program needs to perform many append operations on a string, using a stringbuilder object may be faster than using a string object.
See
http://msdn.microsoft.com/en-us/library/system.text.stringbuilder(VS.80).aspx
I solved the problem meanwhile. I replaced
txtMessage.value = sMessage; /* raises Page Error */by
window.Form1.txtMessage.value = sMessage; /* works */I enabled "detect scripterrors" in the browser. The message is:
'txtMessage' is not defined.
I moved the <script> element behind the <body> element. But this page error is still raised.
The following script function "btnRunScript_onclick" works as long as it does not access the HTML control "txtMessage". If it does, "Page Error" is shown in the statusbar of Internet Explorer (6). Knows anybody why? Thank you.
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="GuessingGame.aspx.vb" Inherits="Ch07Le04.GuessingGame"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>GuessingGame</title>
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
<script id="clientEventHandlersJS" language="javascript">
function btnRunScript_onclick() {
sMessage = "ABC";
/* document.write("<h2>ABC</h2>"); */ /* works */
txtMessage.value = sMessage; /* raises Page Error */
}
</script>
</HEAD>
<body language="javascript">
<form id="Form1" method="post" runat="server">
<h2>The Guessing Game - Responding to Events with Script Procedures</h2>
<hr>
<P>
<INPUT id="btnRunScript" type="button" value="Run Script" language="javascript" onclick="return btnRunScript_onclick()">
</P>
<P><INPUT id="txtMessage" type="text" value="ABC" name="txtMessage"></P>
</form>
</body>
</HTML>You could use a SQL statement with a GROUP BY ... HAVING clause.
Group the records by the columns that are relevant for "equality". Use HAVING COUNT(*) > 1 to return only those groups that have more than one member = more than one row with the same values in the relevant columns.
Private Sub Command1_Click()
Dim aConn As ADODB.Connection
Set aConn = New ADODB.Connection
aConn.ConnectionString = "DSN=XYZ;"
aConn.Open
Dim rs As ADODB.Recordset
Dim sSQL As String
sSQL = "SELECT col1, col2 FROM t1 " & _
"GROUP BY col1, col2 " & _
"HAVING COUNT(*) > 1"
Set rs = New ADODB.Recordset
Call rs.Open(sSQL, aConn)
Do While Not rs.EOF
MsgBox "More than one record with columnvalues Col1:" & rs(0) & " Col2:" & rs(1)
rs.MoveNext
Loop
rs.Close
End Subone problem is the comma before the WHERE.
Replace
& "CS = @NewCS, " _
& "WHERE fldUser = @OldfldUser " _by
& "CS = @NewCS " _
& "WHERE fldUser = @OldfldUser " _If more syntax errors please post back
Did you check rows.count before and after adding the row?
console.Writeline( "Rows.Count {0}", DsMembers.Members.Rows.Count )Do you assign the result of the find to a datarow?
Dim rowFound As DataRow = DsMembers.Members.FindByTDL(scannedMember.TDL)Think it is an ASP.NET application. I made some changes, marked with XXX. On my system it works. Maybe the changes help you to get your program to work. Databaseprogramming is a wide area.
Imports System
Imports System.data
Imports System.data.sqlclient
'XXX used a standard dataset since I do not have yours Imports dataset1
Public Class WebForm1
Inherits System.Web.UI.Page
'XXX con needs to be in scope when the Insert-Button Click event is handled
Dim con As SqlConnection
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'XXX Dim con As SqlConnection
Dim cmd As SqlCommand
Dim adp As SqlDataAdapter 'XXX use SQLDataAdapter Dim adp As sqladapter
Dim data As DataSet
'XXX I do not have your database, please change connect string as required.
'XXX If you use the SQLClient dataprovider, you do not need the provider keyword.
'XXX instantiated the con object
'con.ConnectionString = "Provider=SQLOLEDB;initialcatalog=preethi;database=ACER;integrated security=true"
con = New SqlConnection
con.ConnectionString = "database=.;initial catalog=contacts;user=ASPNET;"
cmd = New SqlCommand 'XXX instantiated the command object
'XXX connection is assigned below
'cmd.Connection = con
'XXX cmd=new sqlcommand("select * from emp"),con
cmd = New SqlCommand("select * from contacts", con) 'XXX
adp = New SqlDataAdapter(cmd)
data = New DataSet 'instatiated dataset
adp.Fill(data)
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
Dim sSQL As String = "INSERT INTO contacts( contactid, firstname, lastname) VALUES (20, 'User20', 'User20Firstname')"
Dim cmd As SqlCommand = New SqlCommand(sSQL, con)
con.Open() 'XXX connection must be opened before command is executed
cmd.ExecuteNonQuery()
con.Close() 'XXX …The following code replaces the CR+LF that separates the lines in a multiline textbox by a text that will hopefully never be entered by any user.
I tested this with german culture settings.
Dim sLine As String
sLine = Replace(TextBox1.Text, vbCr & vbLf, ":AxAxA:")
Dim writer As New StreamWriter("temp.txt", False)
writer.WriteLine(sLine)
writer.Close()
Dim reader As New StreamReader("temp.txt")
sLine = reader.ReadLine
reader.Close()
TextBox2.Text = Replace(sLine, ":AxAxA:", vbCr & vbLf)Maybe its possible but I never read code that used a stringreader to feed the DataRowCollections.Add Method. Typically a DataRow generated by the tables NewRow method or an array of objects is used as parameter.
If you read the datafields from a textfile and have other datatypes than "string" in the DataTable, you will need to convert these fields to the requested datatypes.
The example table has 2 String and 1 integer column, I used ";" as separator for the fields that make up one row.
Sample Datafile:
Row1Col1;Row1Col2;1003
Row2Col1;Row2Col2;2003
Row3Col1;Row3Col2;3003Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim tbl As New DataTable("mytable")
tbl.Columns.Add("col1", GetType(String))
tbl.Columns.Add("col2", GetType(String))
tbl.Columns.Add("col3", GetType(Integer))
Dim sFilename As String = "C:\TEMP\Dani\DataAccess\somedata.txt"
Dim myStream As System.IO.StreamReader = New System.IO.StreamReader(sFilename)
Dim line As String
Dim aRow As DataRow
Do
line = myStream.ReadLine()
If line Is Nothing Then
Exit Do
End If
Dim sAry As String() = Split(line, ";") ' separate the fields of the current row
aRow = tbl.NewRow 'get a DataRow that has the required structure
aRow(0) = sAry(0)
aRow(1) = sAry(1)
aRow(2) = CInt(sAry(2))
tbl.Rows.Add(aRow)
Loop
myStream.Close()
For Each aRow In tbl.Rows
Console.WriteLine("{0} {1} {2}", aRow(0), aRow(1), aRow(2))
Next
End SubBut if the DA has the commands, why do you call the Command Builder?
In order to check why the DA cannot save DS's changes to the DB, I would try to find out
- which of the tables in the dataset actually have changes
- if the DA after calling the command builder has all needed commands, or any errors caused by the command builder.
For example:
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Try
' DEBUG: catch errors of command builder
'Dim XCmd As New OleDb.OleDbCommandBuilder(DA)
' check state of tables and dataadapter
Try
Dim XCmd As New OleDb.OleDbCommandBuilder(DA)
For Each aTbl As System.Data.DataTable In DS.Tables
Dim aChgTbl As DataTable = aTbl.GetChanges
If aChgTbl Is Nothing Then
Console.WriteLine("Table: {0} Changed: nothing", aTbl.TableName)
Else
Console.WriteLine("Table: {0} Changed: {1} Rows", aTbl.TableName, aChgTbl.Rows.Count)
End If
Next
Console.WriteLine("Insert: {0}", DA.InsertCommand.CommandText)
Console.WriteLine("Update: {0}", DA.UpdateCommand.CommandText)
Console.WriteLine("Delete: {0}", DA.DeleteCommand.CommandText)
Catch ex As Exception
Console.WriteLine("{0}", ex.ToString)
End Try
' END DEBUG
'DS.AcceptChanges()
DS.GetChanges()
DA.Update(DS)
Console.WriteLine(DA.UpdateCommand.CommandText)
MessageBox.Show("Saved", "ESP MESSAGES", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)
Catch
MessageBox.Show("Please check basic information has been typed, " & Chr(13) & "Partner, FORModel, JITModel, PionnerModel, and PlantCode")
'DS.RejectChanges()
End Try
End SubAfter instantiation of the command builder: do the Commandtext properties of DA.UpdateCommand, DA.InsertCommand and DA.DeleteCommand contain the required SQL commands?
P.S.: DS.GetChanges produces a copy that contains the changed rows. The code does not use this subset but the original dataset. But unless AcceptChanges is called, it should also work with the original dataset.
There seems to be some logic in the access DB engine to recognize that 31 cannot be the number of a month. But not enough to recognize that Feb 31th is very unusual.
maybe VBA documentation is helpful:
http://msdn.microsoft.com/en-us/library/aa212163(office.11).aspx
http://msdn.microsoft.com/en-us/library/aa172286(office.11).aspx
When using SQL and # # to write dates to Access, MS recommends to use USA dateformat.
Do you want to
1) step throgh all rows of Table1, and show the row when there is no row with the same path AND filename in Table 2
2) and then do the same vice versa?
If yes, and DBMS is MS SQL Server or Oracle I would use a union:
/* paste this in MS Query Analyzer, use || instead of + for oracle */
DROP TABLE t1
DROP TABLE t2
CREATE TABLE t1(
sPath VARCHAR(20),
sFilename VARCHAR(10)
)
CREATE TABLE t2(
sPath VARCHAR(20),
sFilename VARCHAR(10)
)
INSERT INTO t1 VALUES ( 'P1', 'F1' )
INSERT INTO t1 VALUES ( 'P2', 'F2' )
INSERT INTO t2 VALUES ( 'P1', 'F1' )
INSERT INTO t2 VALUES ( 'P3', 'F3' )
SELECT sPath + sFilename FROM t1 WHERE spath + sfilename NOT IN
(SELECT spath + sfilename FROM t2)
UNION ALL
SELECT sPath + sFilename FROM t2 WHERE spath + sfilename NOT IN
(SELECT spath + sfilename FROM t1)
------------------------------
P2F2
P3F3
(2 rows affected)My name is Joachim, I live in Stuttgart, a town in a basin sourrounded by hills in the south of germany. I was born 1958 and work as a software developer since 1990. As a hobby pianist I am lucky to play in local salsa combos. I have a very nice wife who after 20 years still loves to hear our concerts...