Insert data from a Dataset into a SQL Database
Hi guys my name is Ralph, i am kinda new to this so please bear with
me.
I have written a .net web service which accepts a stream of data
see relevent source code).
Public Function PutBondInfo(ByVal data As String) As DataSet
Try
Dim zh As New ZipHelper
Dim sz As String
Dim cn As SqlConnection
Dim da As SqlDataAdapter
Dim ds As New DataSet
Dim xSet As New DataSet
Dim sSql As String
Dim mySerializer As XmlSerializer = New
XmlSerializer(ds.GetType())
cn = New
SqlConnection("server=HLSQL;uid=test;pwd=;database=test")
cn.Open()
da = New SqlDataAdapter("SELECT * FROM BondStatus", cn)
'da.TableMappings.Add("BondStatus", "NewDataSet")
sz = zh.Decompress(data)
zh = Nothing
da.FillSchema(ds, SchemaType.Mapped, "BondStatus")
'da.MissingSchemaAction = MissingSchemaAction.AddWithKey
da.Fill(ds, "BondStatus")
Dim st As New MemoryStream(Encoding.UTF8.GetBytes(sz))
' Create the object from the xml file
xSet = CType(mySerializer.Deserialize(st), DataSet)
xSet.Tables(0).TableName = ds.Tables(0).TableName
ds.Merge(xSet, False, System.Data.MissingSchemaAction.Add)
'ds.AcceptChanges()
Dim SqlCB As SqlCommandBuilder = New SqlCommandBuilder(da)
da.Update(ds, "BondStatus")
st.Close()
da.Dispose()
SqlCB.Dispose()
cn.Close()
Return ds
Catch ex As Exception
ex.Message().ToString()
End Try
End Function
This data is compressed.I decompress the data and receive the xml
which is then
deserialized into a dataset. this part works great no problems. My
problem is then taking that dataset and pushing the data into a table
on my SQL server. The elements in the xml and the field names of my
SQL table 'BondStatus'
are exactly the same. The table names are different the xml has
'Table' as its table name and so on. As you can see from the code I
have tried table mappings, assigns, merges etc with no luck.
Ladies and gents if anyone has a clue as to what I am doing and can
help me, it would be appreciated.
Thanks in advance
Ralph Tag: Binding Data with TextBox Tag: 105211
Stored Procedure
Hi,
I've 2 stored procedures in Sql Server 2000 by name QA_User_Insert and
QA_Insert_Candidate.
Am using Oledb connection to connect to Sql server 2000. and i wrote
following code in ASP.Net
Dim MyConn As OleDbConnection = New OleDbConnection("uid=sa;Intial
Catelog=Quiz;data source=server;pwd=thanks;provider=SQLOLEDB.1")
Dim cmd As New OleDbCommand
cmd.Connection = MyConn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "QA_User_Insert"
\\ Here i created paramentes and added to parameters collection of cmd
object. \\
cmd.ExecuteNonQuery()
then after running the application am getting Error
"Can Not Found Stored Procedure QA_User_Insert"
I 've checked sql server 2000 and i found that there is QA_User_Insert
stored Procedure.
why am getting this error ? plz can anybody tell me solution for this
problem.
Thanx in advance
Jyothi Tag: Binding Data with TextBox Tag: 105205
sqlexception error in system.data.sqlclient.dll
Hi everyone, i'm a newbie to VB.NET programming and i need help here. I'm
current doing an application for pocket pc and i get this sqlexception error
when i try to retrieve stuff from the database. My guess is that the error
lies with the connection string. I've tried serveral different connection
string but i still get the error.
Try
cn = New System.Data.SqlClient.SqlConnection("DATA
SOURCE=L311C007; Initial Catalog=HEALTHCARE; Integrated Security=SSPI;")
cn.Open()
cmd.CommandText = "SELECT * FROM PERSON"
cmd.Connection = cn
Catch ex As System.Data.SqlClient.SqlException
result = "There was an SqlException.." & vbCrLf &
"Details..." & vbCrLf & ex.ToString()
MsgBox(result)
End Try
The connection strings that i have tried are:
User id=sa;password=;database=HEALTHCARE;server=L311C007
Data Source=L311C007,14433;Initial Catalog=HealthCare;User id=sa;password=; Tag: Binding Data with TextBox Tag: 105204
AdoHelper Dependency Problem
Hi
I'm using the GotDotNet.ApplicationBlocks.Data.AdoHelper class in one of my
projects, and it's working fine on my development machine. the problem
however arises when deploying the project to another machine. I get a
FileNotFoundException in the type initializer of AdoHelper. It claims it
can't find a dependency, although inspection of the AdoHelper source code
seems to show that it doesn't depend on anything other than the .Net
Framework. The specific line of code which fails is this:
Additionally the stack trace shows that the error occurs within a
System.Reflection class.
AdoHelper adoHelper =
AdoHelper.CreateHelper("GotDotNet.ApplicationBlocks.Data","GotDotNet.ApplicationBlocks.Data.SqlServer");
Interestingly it seems to work fine if I create the SqlServer helper
directly.
Anyone got any thoughts?
Cheers
Andrew Tag: Binding Data with TextBox Tag: 105194
Cannot Delete Rows in Database
Hi all,
I am using the following to remove a row from a DataSet:
myDataSet.Tables["Table1"].Rows.RemoveAt(indexSelected);
followed by;
myDataAdapter.Update(myDataSet);
After this call, looking in the database, the row is not deleted. But
debugging it I can see it remove the row.
Also note that the following insert does insert into the database:
myDataSet.Tables["Table1"].Rows.InsertAt(newRow, index);
Strange how InsertAt works but not RemoveAt.
Does anyone knows what the problem is????
Thanks for any help,
Scott Tag: Binding Data with TextBox Tag: 105193
This sqlTransaction has completed; it is no longer usable
This issue is really frustating because it happens randomnly.
This is the sample layout of my code.
try
{
SQLConnection.Open();
trans = SQLConnection.BeginTransaction();
//Insert the data into tables using datasets
trans.Commit();
}
Catch (SqlException ex)
{
Console.WriteLine(ex.Message);
trans.Rollback();
}
Randomnly I get the error "This sqlTransaction has completed; it is no
longer usable". This error occurs when it tried to execute
"trans.Commit();"
Anyone has any suggestions?
Thanks Tag: Binding Data with TextBox Tag: 105192
I want to execute a simple UPDATE command!
I am pretty new to .NET, so can someone tell me why this doesn't work?
It does not throw any exception but it doesn't update the record in the
table either. All variables are populated correctly and the record
DOES exist!
Thanks in advance!
SqlConnection loConnection = new SqlConnection(<connection string
here>);
SqlCommand loCommand = loConnection.CreateCommand();
loCommand.CommandText = "UPDATE NewUser SET Name = '" + lsName + "'
WHERE NewUserID = " + liNewUserID;
loConnection.Open();
SqlDataReader loDataReader = loCommand.ExecuteReader();
loDataReader.Read();
loDataReader.Close();
loConnection.Close(); Tag: Binding Data with TextBox Tag: 105189
Is it bad to use transactions for read operations (keeps conn. ope
Hi I am trying to find out if it is bad, and how bad, to start a database
transaction on a set of read operations. By starting a transaction I can
keep open the database connection...but is there significant overhead for
transaction?
Basically I am populating a set of objects and I may hit the database 5 to
10 times on any given set of parent/child objects.
I cannot use stored procedures.
What do you think? Tag: Binding Data with TextBox Tag: 105187
updating a decimal
Hallo,
could you help with this problem?
Updating an integer, as tring ok, but a decimal ??
Update tblprice Set article = ' " & this & " ' WHERE ...
what signs do I have to put?
Thx for helping me
Hyphessobrycon Tag: Binding Data with TextBox Tag: 105186
Switch Columns And Rows
Hi,
I wander if it's possible to retrieve a recordset but switch rows and
columns
I've a table with the fields
ar_id, cr_id, ar_st
the table is filled with the folowing records
[1], [01], 0
[1], [02], 1
[1], [03], 1
[2], [02], 0
[2], [03], 1
I want to transform this somehow into
[1], [01], 0, [02], 1, [03], 1
[2], [02], 0, [03], 1
Is this possible via sql or ado.net?
Kind Regards Tag: Binding Data with TextBox Tag: 105184
Reading a dataset into XML
I have a table in my SQL Server that I am loading into a datatable. I am
then saving the dataset that this table is a member of (it's the only
table in the dataset) to an XML file so that I can use it offline in
another part of my program. The code I use for this is:
ds.WriteXML("c:\clients.xml") 'DS is my dataset object
This generates an XML file like the following (the entire XML file is
included in this post at the end of the message)
<?xml version="1.0" standalone="yes"?>
<ClientData>
<Clients>
<c_recordid>1</c_recordid>
<c_name>NOL</c_name>
</Clients>
<Clients>
<c_recordid>2</c_recordid>
<c_name>TCG</c_name>
</Clients>
.more clients.
</ClientData>
In another part of my program, which is designed to use when you're
offline and can't connect to the SQL server, I want to reload this
information back into a datatable. I do so using the following code:
ds = New DataSet("ClientData")
ds.ReadXml("c:\clients.xml")
dt = ds.Tables("Clients")
Next, I try to refer to a specific row in the Clients data table in the
following manner:
Dim rows as datarow() = dt.select("c_recordid=" + clientid.tostring)
'ClientID is an integer being passed to the subroutine
Try
Dim row as datarow = rows(0)
Catch ex as Exception
Msgbox ex.tostring
End Try
Because c_recordid is the unique key for the table, I know that only one
row will be returned - hence I can logically refer to row(0). However,
when I call the subroutine setting the ClientID variable to 2,3.10, or
11, I get the following exception when referring trying to access
rows(0):
System.IndexOutOfRangeException: Index was outside the bounds of the
array.
This does not happen when I set ClientID = 1,4,5,6,7,8, or 9. I know all
of these are valid because I've looked at the XML file in it's entirety.
I've checked for strange characters that might be messing it up and
haven't found anything - all in all, I thought it would be an easy
process, but the only thing that is consistent is the ClientID's I can't
access.
Can anyone direct me to what I'm doing wrong, or what might be causing
this thing to blow up? I'd appreciate any help. One last thing. I HAVE
written a little subroutine to loop through each row in the datatable.
Dim r as datarow
For each r in dt.rows
Msgbox r(0) 'Msgbox's the clientid column (1-11)
Next r
This works fine. I see all 11 clients when I loop through them, but
cannot use the .select method to consistently get a particular one.
Thanks,
Chet
Here is the XML file in it's entirety:
<?xml version="1.0" standalone="yes"?>
<ClientData>
<Clients>
<c_recordid>1</c_recordid>
<c_name>NOL</c_name>
</Clients>
<Clients>
<c_recordid>2</c_recordid>
<c_name>TCG</c_name>
</Clients>
<Clients>
<c_recordid>3</c_recordid>
<c_name>CHN</c_name>
</Clients>
<Clients>
<c_recordid>4</c_recordid>
<c_name>CNT</c_name>
</Clients>
<Clients>
<c_recordid>5</c_recordid>
<c_name>CityNet</c_name>
</Clients>
<Clients>
<c_recordid>6</c_recordid>
<c_name>IFN</c_name>
</Clients>
<Clients>
<c_recordid>7</c_recordid>
<c_name>IU</c_name>
</Clients>
<Clients>
<c_recordid>8</c_recordid>
<c_name>Lilly</c_name>
</Clients>
<Clients>
<c_recordid>9</c_recordid>
<c_name>Plainfield</c_name>
</Clients>
<Clients>
<c_recordid>10</c_recordid>
<c_name>St. Vincent</c_name>
</Clients>
<Clients>
<c_recordid>11</c_recordid>
<c_name>IFW</c_name>
</Clients>
</ClientData> Tag: Binding Data with TextBox Tag: 105180
Values being formatted differently when opening spreadsheet using ado.net
I have a some-what simple app for importing data from excel to our database.
The app is written in C# and uses ado.net.
When I open the xls file, it displays in a grid to show the values.
The problem is values are formatted differently depending on the machine.
I have 2 machines. One the first one (excel installed) I open the xls file
and all values display as decimal even though in excel there displayed as
percents. This is fine and how I want it.
On the second machine (excel not installed) the values display as percents.
What can cause the difference in formatting?
Thanks,
Joe Tag: Binding Data with TextBox Tag: 105179
How to display different data
Here is what I'm trying to do, I have a database table with columns:
ColA ColB ColC ColD ColE
1 AAA BBB xxx yyy x
2 111 xxx 222 yyy y
...
I want to display in a web form ColA, ColB if the ColE of the row I'm
getting having value "x", display ColA, ColC if ColE="y".
So if user searches for row 1:
ColA AAA
ColB BBB
if user searches for row 2
ColA 111
ColC 222
ColD yyy
What is the best way to make it work? Do I need to have different data
controls and display one of them depending on the data returned (my real
problem will consist of quite a few cases)? (Is there a way of displaying the
data at the run time?) This just seems to be someting that is easy
accomplished in ASP but I don't know how to do it in aspx. Give me some
example code will really help.
TIA. Tag: Binding Data with TextBox Tag: 105175
Concurrency Violation Help
I have a dataset that contains 2 datatables. When I change a record and try
the update command it gives me this error...
Concurrency violation: the UpdateCommand affected 0 records. at
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping
tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
Here is the code:
SqlConnection con = new SqlConnection(strCon) ;
try
{
con.Open() ;
daAllSigns = new SqlDataAdapter(sqlAllSigns,con) ;
SqlCommandBuilder cb = new SqlCommandBuilder(daAllSigns) ;
daAllSigns.Fill(dsGrids,"AllSigns") ;
daQueSigns = new SqlDataAdapter(QueSigns,con) ;
daQueSigns.Fill(dsGrids,"QueSigns") ;
// Change record
dsGrids.Tables["AllSigns"].Rows[3]["Sign_Brand"] = "TEST BRAND" ;
dsGrids.Tables["AllSigns"].Rows[3]["Sign_SignDesc"] = "TEST DESC" ;
daAllSigns.Update(dsGrids.Tables["AllSigns"]) ;
catch(Exception exc)
{
Debug.WriteLine(exc.Message + " " + exc.StackTrace) ;
}
Does anyone know why this error could be coming up, the code seems pretty
straight forward to me.... Tag: Binding Data with TextBox Tag: 105167
getting return values from stored procedures
I need to get a return value from the procedure that follows. For some
reason I am not able to asign a value to @returnval and get it back to
the C# code executing it. The procedure does not return any errors. Here
is the code:
ALTER PROCEDURE gradeTest2
@empid int, @testid int
AS
set nocount on
declare @returnval int
set @returnval=1
declare @correct int
declare reader cursor fast_forward for
select answercorrect from questionsanswered where (empid=@empid) and
(testid=@testid)
open reader
fetch next from reader into @correct
while @@Fetch_Status=0
begin
if @correct=0
set @returnval=0
fetch next from reader into @correct
end
close reader
deallocate reader
begin
if @returnval=1
if not exists (select testid from testresults where (empid=@empid)
and (testid=@testid))
insert into testresults (testid,empid,results,testdate)
values (@testid,@empid,@returnval,getdate())
else
update testresults set results=@returnval,testdate=getdate()
where (empid=@empid) and (testid=@testid)
if @returnval=1
delete from questionsanswered where (empid=@empid) and
(testid=@testid)
return @returnval
end
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it! Tag: Binding Data with TextBox Tag: 105164
SQLClient Exception - Retrying
Upon executing a stored procedure using the Data Access application block's
SQLHelper class, I would like to continually retry to execute it every 1
second up to 3 times if an exception occurs. For example, if for some reason
the database server is down, a timeout occurs, etc. I would like to
automatically retry a certain number of times before giving up. What is the
best way to accomplish this? Below is what I envision (pseudo-code):
Dim Retries As Integer = 0
Dim
Try
'// execute database command here
SQLHelper.ExecuteNonQuery(...)
Catch ex AS SQLException
If Retries <= 3 Then
Sleep(1000)
SQLHelper.ExecuteNonQuery(...)
End If
End Try
The problem I have with the code above is that I have duplicate code
(SQLHelper.ExecuteNonQuery(...)). The other problem I have, is what happens
when an exception occurs in the Catch block?
Any help would be greatly appreciated.
Thanks,
Shawn Tag: Binding Data with TextBox Tag: 105161
too much for stored procedure execution
Hi,
I'm trying to write efficient C# code to execute a stored procedure.
The stored procedure takes in 5 arguments. I'm doing a foreach loop to write
a lot, and I mean a lot of data to the DB.
Is the following the most efficient and best way to write tons and tons of
data into the DB using a stored procedure that required 5 arguments?
Remember the code below is called numerous times.
foreach (MyClass obj in CollectionOfObjects)
{
try
{
SqlCommand cmd = new SqlCommand("MyStoredProcedure",sqlcon);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param;
param = cmd.Parameters.Add("@first_name", SqlDbType.VarChar);
param.Value = obj.first_name;
param = cmd.Parameters.Add("@last_name", SqlDbType.VarChar);
param.Value = obj.last_name;
param = cmd.Parameters.Add("@a", SqlDbType.Int);
param.Value = obj.a;
param = cmd.Parameters.Add("@b", SqlDbType.Int);
param.Value = obj.b;
param = cmd.Parameters.Add("@c", SqlDbType.Int);
param.Value = obj.c;
cmd.ExecuteNonQuery();
}
catch {}
}
Thanks Tag: Binding Data with TextBox Tag: 105159
Patterns And Practices
[Copied from microsoft.public.dotnet.languages.csharp, no answer up to now
and probably was wrong group anyways]
I downloaded the patterns and practices enterprise library and found that it
is much different for the Data library that I am used to (SqlHelper)..It's
not just an update but a total rewrite. Anywho, figured out how to get it
to work which wasn't too hard. What I haven't found and am having trouble
with is updating and inserting into a database. In the SqlHelper library,
there were methods such as ExecuteScalarTypedParams and such which allowed
all parameters to be inside a dataset and you just passed the dataset. This
was great because all of our data access routines returned a dataset. We
would just modify the data and pass this same dataset/table/row back to the
SqlHelper method and it would get updated using our stored procedures in Sql
Server.
Now, afaik, there are no methods which take a dataset for parameters. I
have to manually created the parameters, one by one and then add them to the
collection. Is there a better way, maybe they do exist but I haven't seen
them? Anywho, I could always write a method in my applications that would
take a dataset and build the parameters based on column name, column data
type, and compare vs the stored procedures, but if it already exists, I
don't want to reinvent the wheel.
Thanks in advance,
Mythran Tag: Binding Data with TextBox Tag: 105152
Unknown Query Engine Error
Forgive me if this is the wrong forum for this question, but I'm not really
sure where I should ask it.
I am using Crystal Reports to produce a report against a SQLServer database.
Up until 2 weeks ago, the report worked fine. No event was noticed that
might explain why the report stopped working, but now running it produces an
error "Unknown Query Engine Error".
The datasource is a stored procedure joined to a view and 2 tables. I don't
know the EXACT connection string that Crystal Reports is generating, but it
would be you should be able to get the idea from this:
SELECT vwCurrentFacilityInfo.FacilityName, tblItem.DecalNumber,
tblPerson.LastName
FROM dbo.spPropertyTagLetterItems() LEFT OUTER JOIN
dbo.tblItem ON dbo.spPropertyTagLetterItems.ItemID =
dbo.tblItem.ItemID LEFT OUTER JOIN
dbo.vwCurrentFacilityInfo ON
dbo.spPropertyTagLetterItems.ParentID =
dbo.vwCurrentFacilityInfo.FDEPFacilityID LEFT OUTER JOIN
dbo.tblPerson ON
dbo.vwCurrentFacilityInfo.SiteManagerID = dbo.tblPerson.PersonnelID
As I said, this worked until 2 weeks ago, and STILL works if I try generate
the report against the a copy of the database residing on a different server
((local) on my development machine).
Any ideas?
Thanks,
Pat Tag: Binding Data with TextBox Tag: 105151
OleDbDataAdapter.Fill Error ("The provider could not determine the Object value) ADODB.Recordset
Hi,
I am trying to fill a DataSet using an ADODB.recordset:
OleDBDataAdapter.Fill(DataSet, Recordset, "Table1")
This works fine in most part but on other occasions I get the error
below:
"The provider could not determine the Object value. For example, the
row was just created, the default for the Object column was not
available, and the consumer had not yet set a new Object value."
This seems to be a data issue, as when the recordset is empty then no
error occurs but the the fields are correct in the DataSet.
What does this error mean and are there any ways around it?
Help is appreciated,
Leroy. Tag: Binding Data with TextBox Tag: 105143
Using apostrophe in DataTable.Select()
Hi,
I'm using the dataTable.Select() method in my code.
This is my code:
dft.Tables[0].Select("fieldName='" + @fieldName + "'") ;
This works fine until there is an apostrophe in the
fieldName variable - then i get error message:
"Syntax error: Missing operand after ...".
How can i use queries with apostrophe inside?
Thanks. Tag: Binding Data with TextBox Tag: 105132
Big Problem
Hi,
Hi testing the Visual Studio 2005 Beta 2, i'm developed one application and
save this project, when close the visual studio and reopen in another time
with the application i produce this error
OBJECT REFERENCE NOT SET TO AN INSTANCE OF AND OBJECT LINE 0 COLUMN 0
And not is possible open the project
Thanks in advance
Euclides Tag: Binding Data with TextBox Tag: 105130
single large vs multiple small SP calls - speed + efficiency
I have a collection of objects (eg Forms) , each of which contains another
collection (eg Controls). When I come to fill the Control collections at
startup, I fill each one in turn using a Form.GetControls(FormID) call. This
puts the call where I think it is best placed (In the Form class), but am
unsure about the efficiency of it. Would I be better off making one "global"
call, retuning all the Controls and then filling each of the Form.Controls
collections.
I know I could probably test it somehow, but does anyone know what the best
way to do it would be?
Ideas welcome.
Thanks Tag: Binding Data with TextBox Tag: 105129
Exception raised by the DataAdapter Configuration Wizard
Hi,
For a few days now, I've been unable to complete the Sql/OleDataAdpater =
conf wizard on VS .NET 2003 Architect. The message is : "the object =
reference is not defined on an object instance". Exception is raised by =
function =
Microsoft.VSDesigner.Data.VS.VsConnectionManager.CheckConnection(Int32 =
index, ProviderNameInfo)...
Any hint ?
Oriane Tag: Binding Data with TextBox Tag: 105123
DataSet with multiple relations to
Hi!
I'm using a typed dataset where I have a common address table which is
related to several other tables in the dataset, see below:
Table Incident:
- PK
- FK_incidentAddress - relation to Address.PK
- FK_destinationAddress - relation to Address.PK
...
Table Person:
- PK
- FK_homeAddress - relation to Address.PK
...
Common table Address
- PK
- Street
- City
....
If I use one Address entity in the dataset, I guess that I'll get problems
as one row in Address can not satisfy all the relations specified. If I
instead create multiple Addresses which I give different names, and the
relate each of them to their parent, I get ConstraintExceptions when trying
to enable constraints in the dataset after merging the child rows. It seems
like it is the rename of a Address table that creates my problem.
Any tips?
Brgds
Jonas Tag: Binding Data with TextBox Tag: 105122
Access Security Issue
I get the following error message when I attempt to called query which
resides in my Access DB (Stored procedure), "Operation must use an updateable
query". I looked at the KB article which addresses this error and it
indicates that I need to change the security on the folder and the file which
contains my access Db. I went into the folder and and the file and change
the "IUSR" account to "Full access". But this did not resolve the issue.
When I look at the file permissions of the directory, I see the "Deny"
greyed out but checked for "Special Permissions" and "Write". the "Special
Permissions" is not checked for "Allow" and it is checked for "Special
Permissions". Is this the way it is suppose to be?
Any ideas as to what else I should look at so that I can do an update on the
Access DB?
Thanks in advance for your assistance!!! Tag: Binding Data with TextBox Tag: 105113
constraint message box
Hi,
I have a dataset containing constraints. (not null, and size
constraints on table columns).
When the constraint is violated, a message box appears showing the
constraint violation.
Is there any way to catch these constraint violations WITHOUT doing one
of the following:
1) disabling the constraints and manually checking - because what is
the point of that? If I wanted manual checks I wouldn't bother with
fillschema or specifying the constraints at all.
2) installing a thread exception handler and parsing the exception
message, because different database languages and collation orders will
break it, not to mention foreign windows versions (A requirement in our
case).
Is there any way of handling these constraints, I am even happy to
inherit and override the dataset.
What method in the dataset actually pops up this rediculous message
without providing a developer hook to customise or handle the error?
Surely the clever boys at Microsoft did not just write code to pop up a
message without thinking that we might have other plans for the error
message.
Regards
Craig Tag: Binding Data with TextBox Tag: 105111
OleDb Namespace
I have one web project in which I have OleDb Namespce listed with
intellisence and in other projects my variables have to be declared using the
entire namespace (i.e. System.Data.OleDb.OleDbConnection).
All projects have sytem.data.dll reference.
I just can't figure out what I have done to make the OleDb namespace readily
available in the one project? I'd like to do it for all. Tag: Binding Data with TextBox Tag: 105104
dr.Read()
Hello Everyone:
I have a feeling this is very basic, and something I am just overlooking.
The dr.Read() ALWAYS is being translated to false in the code below. I print
out the sSQL statement using the watch window, and paste that into Query
Analyzer: and I confirm the data does indeed exist. What am I missing?
Why, regardless of the data, does my dr.Read() alway equal false?
-----------------------------------------------------------------------------------------------
CODE SNIPPE
-----------------------------------------------------------------------------------------------
sSQL = "SELECT i_OrderID FROM tOrder WHERE sPOrder = " + sPONumber;
SqlConnection oConn = new SqlConnection(connStr());
SqlCommand oCommand = new SqlCommand(sSQL, oConn);
oConn.Open();
SqlDataReader dr = oCommand.ExecuteReader();
if(dr.Read())
{
...Code Removed
}
else
{
...Code Removed
} Tag: Binding Data with TextBox Tag: 105091
DataSet object not releasing memory
Hi,
I'm encountering a strange phenomenon whereby a DataSet object is not
releasing its memory when it's being disposed and/or set to Nothing.
It is part of a Windows service written in VB.NET which fetches data out of
a mySQL database, interrogates each row individually and takes various
actions accordingly. It can fetch upwards of 300,000 rows, each of which
vary between 1k and 2k in size, making the resulting DataSet object almost
500Mb in size. This in itself is not much of a problem, except that when the
service has finished working with the DataSet, it does not release the
memory it has been using.
I'm using it in what appears to me at least to be a fairly standard way
(code at the end of this post).
What do I have to do to free up the memory allocated to the DataSet object?
I've even tried running System.GC.Collect(), though that expectedly made no
difference.
Any assistance gratefully received.
Mark Rae
Option Explicit On
Option Strict On
Imports CoreLab.MySql
Imports System.Collections
Imports System.Data
Imports System.Xml
Public Function Import(pstrMySQLConnectString As String, pstrSQL As String)
As Boolean
Dim objMySQL As New CMySQLCoreLab(pstrMySQLConnectString)
Dim objMySQLDS As DataSet
objMySQLDS = objMySQL.GetDataSet(pstrSQL)
For Each objRow As DataRow in objMySQLDS.Tables(0).Rows
'
' do the processing
'
Next
objMySQLDS.Dispose
objMySQLDS = Nothing
objMySQL.Dispose
objMySQL = Nothing
End Function Tag: Binding Data with TextBox Tag: 105090
Changes in a DataGrid don't always take
Why is it when a new row is added in the datagrid and the user clicks a save
button that GetChanges() returns null unless they move to another record
first? Tag: Binding Data with TextBox Tag: 105088
MS Access DB structure
I need to find out the structure of MS Access database; tables, queries and
so on. How can I do that?
Direction or sample code would work.
Thanks to everyone in advance. Tag: Binding Data with TextBox Tag: 105083
AutoCommit - Oracle.
If you call a stored procedure which is an update procedure, is it
autocommitted by default?
Is the same true for an Oracle Stored procedure?
If you call an Oracle Stored procedure with the defaults for the connection,
and the stored procedure has a rollback, I am getting an error which
indicates that I can not do a rollback on a distributed transaction. How can
I call a stored Oracle Procedure and be able to issue a rollback within the
stored procedure without getting and error in ADO.Net when I do this? Tag: Binding Data with TextBox Tag: 105076
Cant connect to local sql server 2000 from VB.NET running WinXP Pro SP2
Hi
I just installed SQL Server 2000 to run locally on a WinXP Pro SP2 PC.
When I run the following VB.NET code:
Protected Const SQL_CONNECTION_STRING As String = _
"Server=localhost;" & _
"DataBase=Northwind;" & _
"Integrated Security=SSPI"
Dim connectionString As String = SQL_CONNECTION_STRING
Dim cnnNW As New SqlConnection(connectionString)
I can't connect to sql server.
This code works fine on a Windows 2000 Pro SP2 pc.
Thanks.
Dee Tag: Binding Data with TextBox Tag: 105075
Remote connection to access database
Is it possible to connect remotely to access database? If is, how? Any
idea is appricited. Tag: Binding Data with TextBox Tag: 105044
Retrieving data from dataset with multiple tables
Hi!
Having problems with retrieving data from MS Access through a dataset
with multiple tables.
It works fine when I'm only using two tables in the dataset, but when
I want to access data through a second table (three tables in all) it
doesn't work!
This is the error message I get: "An unhandled exception of type
'System.Data.OleDb.OleDbException' occurred in system.data.dll
Please help
Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------
http://www.usenet.com Tag: Binding Data with TextBox Tag: 105040
renaming a access table in ado .net
hi,
i need to rename a table in a access database, within ado .net.
is that doable with a ExecuteNonQuery sql command applied to a
OleDbConnection, if so what
would the command be, i can't find any documentation for it?
is it done thru a SchemaTable? what then would be code. i tried
SchemaTable.Rows(RowCount)!TABLE_NAME = "tblNewName", but it is read only.
thanks for any help
ray Tag: Binding Data with TextBox Tag: 105038
Auto Number Seed ?
How can I reset or change the value of autonumber in MS Access ? Doesn't it
have the function like NEXTVAL or CURVAL as in Oracle ? Or is autonumber
field more rigid ? Or is it possible to make sequence in Access so I can
manually do it ? Tag: Binding Data with TextBox Tag: 105032
Enterprise Library, DataSets, and Windows Forms binding.
Okay so I'm using the Data Access application block in the Enterprise
Library. I'm using it to return a DataSet from an SQL Server (well, MSDE)
stored procedure. So that's an untyped DataSet. I have no access to the
DataAdapter used to retrieve the DataSet (the Enterprise Library classes
don't expose it AFAIK), so I can't create any TableMappings. I want to bind
the DataSet to Windows Forms controls, but I'm getting the old 'cannot
create child list' errors because there's no schema attached to the DataSet.
I can manually name tables and columns in the DataSet via the Tables
collection, and then bind. This is messy though, so:
1) Is that the only way to do it?
2) If I retrieve the DataSet, then use WriteXMLSchema, can I subsequently
use that saved schema against the returned DataSet?
3) This must be a very common thing that people need to do - why is is such
a pain in ADO.NET? Tag: Binding Data with TextBox Tag: 105031
Default value of a Table in Sql server To default value in DataSet scheme
hello
I wanted to ask how to get the default value defined in the DataBase,
to be defined as the default value in the DS scheme?
and if there isn't a way, how else can i do that?
thanks. Tag: Binding Data with TextBox Tag: 105030
Oracle and ASP.NET
I have a Solution with 3 projects:
1- Data Access: A library with all class to acces to the DataBase
2- A Windows Forms Project that uses the library 1 to view data from the
Data Base.
3- A ASP.NET project like the windows project.
The problem is that the project 2 works but not he 3.
In the ASP.NET project show this error:
ORA-12154: TNS:no se ha podido resolver el nombre de servicio
It can't be a problem with the Connection String neither the TNSNames
because the windows project works.
The Oracle directory allow Execute, Read and list to the users ASPNET, IWAM
and IUSR
Sorry for my English and many thanks Tag: Binding Data with TextBox Tag: 105023
Using Interfaces
I am still struggling to understand the usage of Interfaces in an
attempt to make a provider independent data access layer. My question
is this...if I create a connection from an interface that is specfic
to a data prvoider, will the remaining objects created from interfaces
and based on the connection object automatically be of the same
provider type?
For example, does this work....
(Note: strConnect and strSql set elsewhere based on the
desired dbType and action)
Dim conn As IDbConnection
Dim cmd As IDbCommand
Dim da as IDbDataAdapter
dim ds as DataSet
Select Case dbType
Case "SQL"
conn = New SQLCommand(strConnect)
Case "OleDB"
conn = New OleDBCommand(strconnect)
End Select
cmd = New IDbCommand(strSql, conn)
da = New IDbDataAdapter(cmd)
da.Fill(ds, "SomeTable")
TIA,
John Tag: Binding Data with TextBox Tag: 105018
sp returns wrong value with VB.net function
I don't know what's happening here. I can run the sp in query analyzer and
(using PRINT) see that it returns a positive record number (ie., user is
authenticated). But when I run this function on that sp it returns -1. I
used EM's SQL profiler and can see that it's returning -1, there, too. But
the same inputs in query analyzer returns 2, a passing authenticate. How can
I see what's being sent to SQL server? I have SQL debugger turned on, but
...
Function DBAuthenticate( strUsername As String, strPassword As String ) As
Integer
Dim conMyData As SqlConnection
Dim cmdSelect As SqlCommand
Dim parmReturnValue As SqlParameter
Dim intResult As Integer
conMyData = New SqlConnection("workstation id=W2000;packet
size=4096;integrated security=SSPI;data source=W2000;" & _
"persist security info=False;initial catalog=trythis")
cmdSelect = New SqlCommand( "DBAuthenticate", conMyData )
cmdSelect.CommandType = CommandType.StoredProcedure
parmReturnValue = cmdSelect.Parameters.Add( "RETURN_VALUE",
SqlDbType.Int )
parmReturnValue.Direction = ParameterDirection.ReturnValue
cmdSelect.Parameters.Add( "@username", strUsername )
cmdSelect.Parameters.Add( "@password", strPassword )
conMyData.Open()
cmdSelect.ExecuteNonQuery()
intResult = cmdSelect.Parameters( "RETURN_VALUE" ).Value
conMyData.Close()
If intResult < 0 Then
If intResult = -1 Then
lblMessage.Text = "Username Not Registered!"
Else
lblMessage.Text = "Invalid Password!"
End If
End If
Return intResult
End Function
This code is from Sam's ASP.Net Unleashed 2nd edition. I've had to tweak a
few other things, but this looks like it should work.
TIA,
Patrick
psully at eatel dot net
ps: here's the sp
CREATE PROCEDURE DBAuthenticate
(
@username Varchar( 100 ),
@password Varchar( 100 )
)
As
DECLARE @ID INT
DECLARE @actualPassword Varchar( 100 )
SELECT
@ID = IdentityCol,
@actualPassword = u_password
FROM UserList
WHERE u_username = @username
IF @ID IS NOT NULL
IF @password = @actualPassword
RETURN @ID
ELSE
RETURN - 2
ELSE
RETURN - 1
GO Tag: Binding Data with TextBox Tag: 105017
sp does not return right value but query analyzer does
sorry if this is a repsot, but I can't see my 1st post. Lost my connection
in the middle of posting
I don't know what's happening here. I can run the sp in query analyzer and
(using PRINT) see that it returns a positive record number. But when I run
this function on that sp it returns -1. I used EM's SQL profiler and can see
that it's returning -1, there, too. But the same inputs in query analyzer
returns 2, a passing authenticate. How can I see what's being sent to SQL
server? I have SQL debugger turned on, but ...
Function DBAuthenticate( strUsername As String, strPassword As String ) As
Integer
Dim conMyData As SqlConnection
Dim cmdSelect As SqlCommand
Dim parmReturnValue As SqlParameter
Dim intResult As Integer
conMyData = New SqlConnection("workstation id=W2000;packet
size=4096;integrated security=SSPI;data source=W2000;" & _
"persist security info=False;initial catalog=trythis")
cmdSelect = New SqlCommand( "DBAuthenticate", conMyData )
cmdSelect.CommandType = CommandType.StoredProcedure
parmReturnValue = cmdSelect.Parameters.Add( "RETURN_VALUE",
SqlDbType.Int )
parmReturnValue.Direction = ParameterDirection.ReturnValue
cmdSelect.Parameters.Add( "@username", strUsername )
cmdSelect.Parameters.Add( "@password", strPassword )
conMyData.Open()
cmdSelect.ExecuteNonQuery()
intResult = cmdSelect.Parameters( "RETURN_VALUE" ).Value
conMyData.Close()
If intResult < 0 Then
If intResult = -1 Then
lblMessage.Text = "Username Not Registered!"
Else
lblMessage.Text = "Invalid Password!"
End If
End If
Return intResult
End Function
This code is from Sam's ASP.Net Unleashed 2nd edition. I've had to tweak a
few other things, but this looks like it should work.
TIA,
Patrick
psully at eatel dot net
ps: here's the sp
CREATE PROCEDURE DBAuthenticate
(
@username Varchar( 100 ),
@password Varchar( 100 )
)
As
DECLARE @ID INT
DECLARE @actualPassword Varchar( 100 )
SELECT
@ID = IdentityCol,
@actualPassword = u_password
FROM UserList
WHERE u_username = @username
IF @ID IS NOT NULL
IF @password = @actualPassword
RETURN @ID
ELSE
RETURN - 2
ELSE
RETURN - 1
GO Tag: Binding Data with TextBox Tag: 105015
What's the best way to insert new records only
Hi,
Every morning a .NET application downloads a file with cumulative data which
needs to be appended to SQL Server table. This program needs to identify
records that have not been previously inserted (there's a unique identifier
field) and only insert those. Also I must reuse our class that does updates,
it basically can update any table by using SqlDataAdapter .update method. So
I have to rule out bulk inserts, DTS, etc...
I think I have little choice but to load all records first to a "temp" table
and then append only new records (where not exists) to a "real" table. Can
anyone think of a better solution?
Thank you. Tag: Binding Data with TextBox Tag: 105003
Need to suppress DataColumnCollection Events during construction of typed DataSet
We have a wide typed dataset and we profiled our app that constructs it
many 1000s of times and it turns out that the constructor is the most
expensive part of our app. We dug in deeper and it seems the
constructor spends most of its time in the datacolumncollection and
especially in the events that fire as columns are added. I need some
ideas on how to construct the typed dataset faster. Either suppress the
event or some other trick.
Thanks
Costas Tag: Binding Data with TextBox Tag: 104990
ResultSet size (in KB)
In my DB layer, I have a monitoring tool. This tool logs every access
to the database, and writes out the SQL and the time it takes to get
the results.
I want to add another important figure: the size of the result. It's
obvious that an answer of one single integer is completely different
from a dataset of thousends of rows with several columns.
So far, I only found the "RowSize" attribute of OracleCommand, which
is not enough for my need.
Any idead how to obtain this data ?
Posted Via Usenet.com Premium Usenet Newsgroup Services
----------------------------------------------------------
** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
----------------------------------------------------------
http://www.usenet.com Tag: Binding Data with TextBox Tag: 104988
problem updating Access database with values from formatted cell
Hello,
I have a DataGridView with a textbox column which is bound at design time to
a DateTime field from a database.
Also at design time I set the Format value of this CellStyle like "yyyy", so
that I only get the year displayed.
How can I write only the year in the textbox and push a newly
generated DateTime variable back to database? (like DateTime dt = new
DateTime(int.Parse(e.Value.ToString()), 12, 12);). Everything that I tried
resulted in an exception in PushFormattedValue (at the bottom).
Currently the code that handles cell parsing is:
if (dataGridView1.Columns[e.ColumnIndex].Name == "DateYear")
{
DateTime dt = new DateTime(int.Parse(e.Value.ToString()), 12, 12);
string s_Date = dt.ToShortDateString();
dataGridView1.CurrentCell.Style.Format = null;
e.Value = s_Date;
MessageBox.Show(e.Value.ToString()); (here it shows a normal string
like 12/12/2005 which if it is inserted like this it doesn't generate any
error!)
e.ParsingApplied = true;
}
even if this executes ok I still get the data error to fire... Any ideas? Tag: Binding Data with TextBox Tag: 104983
connection error, user data wrong
I installed the Community Starter kit earlier today and fooled around with
it. And I also reinstalled the dot net framework sdk. I already had MSDE
installed as w2000\netsdk, but have it disabled, set for manual startup. I
have sql server 2000 installed as default (local) and always on.
Now I am trying to get an aspx page to check website login and/or
registration info through SQL2k stored proceedures, and get a failed login
for "user w2000\netsdk", but my login is supposed to be integrated security,
user = "". This is a little confusing, I am not having problems with the
asp.net login page, just the connection to SQL server to CHECK the saved
user names/passwords in database "trythis."
I've never seen this problem before. I uninstalled Community Starter and
deleted the w200\netsdk login on sql server, but it still tries to use it.
Here is my login string:
connData = New SqlConnection("workstation id=W2000;packet
size=4096;integrated security=SSPI;data source=W2000;" & _
"persist security info=False;initial catalog=trythis")
TIA
Patrick
psully at eatel dot net Tag: Binding Data with TextBox Tag: 104978
I want add a new record using a text box,
but when I click the new button it should clear the text of textbox and
textbox should be binded to datasource.
pls help!