|
I got it figured out. Since I was using the DataAdapter,it seems that in order to "post back" to the dataset you have created, the table in the database needs to have a Primary Key. I guess because since when you re using the dataAapter, the resultant dataset (in memory) needs to somehow map back to the original table and a primary key is needed. I didn't need to use a DataAdapter but I was trying to do it using the new cool stuff in DotNet.
Here is a complete sample. (I palgerized some of this fro the help...)
Notice you will need to return a connectin. I did this with my sconneciton function...
**********UPDATING AN EXISTIN ROW WITH THE DATA ADAPTER - SQLSERVER**********
(I needed a primary key.)
''This works!!!!
Public Sub UpdateExistingRow()
Dim myConnection As New SqlConnection(sConnection)
Dim mySqlDataAdapter As SqlDataAdapter
mySqlDataAdapter = New SqlDataAdapter("SELECT * FROM TestTable1", sConnection)
Dim myDataSet As DataSet = New DataSet
Dim myDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(mySqlDataAdapter)
mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
mySqlDataAdapter.Fill(myDataSet, "TestTable1")
Dim table As DataTable
table = myDataSet.Tables(0)
Dim myDatarow As DataRow
myDatarow = table.Rows(0)
myDatarow.Item(1) = "Wednesday"
mySqlDataAdapter.Update(myDataSet, "TestTable1")
End Sub
|
|
|
|
|
Hi,
I try to call a SQL function which is generated my Id, but the problem is that the return value return 0. Also to be able to execute the function, I have to remove the commandtype definition of my code.
----------------------------------------------------------------
Code where my command line is build
////////////////////////////////////
<br />
public void ExecuteStoredProcedure(string pStoredProcedureName, <br />
object[] pReturnValue)<br />
{<br />
if(dbConnection.IsConnected())<br />
{<br />
bool outputValue = false;<br />
command.Parameters.Clear();<br />
try<br />
{ <br />
foreach(IDbDataParameter parameter in parameterCollection)<br />
{<br />
if(parameter.Direction != ParameterDirection.Input)<br />
outputValue = true;<br />
command.Parameters.Add(parameter); <br />
}<br />
command.CommandText = dbConnection.FormatStoredProcedureCall<br />
(pStoredProcedureName, <br />
command.Parameters);<br />
<br />
if(outputValue)<br />
{<br />
IDataReader reader = command.ExecuteReader();<br />
reader.Close(); <br />
<br />
int count = 0;<br />
<br />
foreach(IDbDataParameter parameter in command.Parameters)<br />
{<br />
Console.WriteLine("Parameter value:" + parameter.Value.ToString());<br />
<br />
if(parameter.Direction != ParameterDirection.Input)<br />
{<br />
pReturnValue[count] = parameter.Value;<br />
count++;<br />
}<br />
} <br />
}<br />
else<br />
command.ExecuteNonQuery();<br />
}<br />
catch(Exception e)<br />
{<br />
throw new DbException(ErrorMessage.INVALID_METHOD);<br />
} <br />
parameterCollection.Clear();<br />
}<br />
else<br />
throw new DbException(ErrorMessage.NOT_CONNECTED);<br />
}<br />
Code when I call my function who generate my Id
///////////////////////////////////////////////
public static string NewId(ICommand pCommand, int pModuleId)<br />
{<br />
string sqlStatement = "NewRecordId";<br />
object[] id = new object[1]; <br />
try<br />
{ <br />
pCommand.DefineParameter("pReturnValue", <br />
DbType.String, <br />
null, <br />
ParameterDirection.ReturnValue, <br />
30);<br />
pCommand.DefineParameter("pCurrentDate", <br />
DbType.DateTime, <br />
DateTime.Now);<br />
pCommand.DefineParameter("pNewId", <br />
DbType.String, <br />
Guid.NewGuid().ToString());<br />
pCommand.ExecuteStoredProcedure(sqlStatement, id); <br />
<br />
if (id[0].ToString().Length != 30)<br />
throw new DatabaseLayerException("Genearated Id doesn't have 30 digits.");<br />
}<br />
catch(Exception e)<br />
{<br />
throw new DatabaseLayerException("LCMS5000", e.Message);<br />
}<br />
<br />
return(pModuleId.ToString() + id[0].ToString());<br />
}
help will be appreciated.
Stephan Martin
|
|
|
|
|
Hi,
I installed SQL Server in INDIA and developed a ASP.Net Application.
In my application, there is one page 'Page1' with one report generated by Repeater.
I need list of all employees Joined after 01/11/2005. The date format is in Indian Standared.I used the following Query.
SELECT *FROM Employees WHERE DateOfJoin >= CONVERT(datetime,'01/11/2005',102)
Here the DateOfJoin field is also from DateTime datatype.
I am getting expected results here (INDIA).
I hosted the same thing in Austrialian Server. I am not getting expected results. I am getting results of Earliar employees also.
What's wrong in it ?
How to solve this problem.
Please let me know as earilar possible.
Your best friend,
Murthy.
|
|
|
|
|
Insead of hard-coding the format of the date, try using a parameterized query, that way the environment will sort out the formatting of the DATETIME variable.
CREATE PROC SelectEmployeesAfterGivenDate
@DATE DATETIME
AS
SELECT * FROM Employees WHERE DateOfJoin >= @Date
|
|
|
|
|
J4amieC wrote: using a parameterized query
Also helps stop sql injection attacks 
PJC
|
|
|
|
|
I used similar to like that. Instead of creating Procedure, I used as in the following way.
Dim cnn as new SqlConnection(...)
Dim cmd as new SqlCommand("SELECT *FROM Employees WHERE DateOfJoin >= @JoinDate, cnn)
Dim dr as SqlDataReader
cmd.Parameters.Add(New SqlParameter("@JoinDate", toDate))
where toDate is '01/11/2005' IST
dr = cmd.ExecuteReader
Still not getting. Is it still wrong ?
Please revert back.
Your best friend,
Murthy.
|
|
|
|
|
Hello,
I've got a problem. The Task is to create a database for a special kind of report.
For the further description I call the template report and a data acquisition for a report survey.
Every report has got sections which can occurs many times in a survey and every survey has to be hold in different version. A report can consist of many hundred or thousand datafields and a survey can be even larger, because it can occurs many times.
A version of a survey may only have one new entry, then all fields except the one entered would be NULL. An operator can validate every new entry, so that a hold a validated version of the survey consisting of all validated fields from the different versions (where every field can only be validated in one version. if a field is validated in another version, it overwrites the validate state in the former validate fieldversion).
When I have twenty versions I want to be able to read the latest version (with all the previous data, where this version is NULL) or a previous version (where the fields which are NULL are filled with previous version data) or the validated version (consisting of perhaps all versions).
The next challenge is that a report can change every week. Perhaps there will be twenty new fields and e.g. twelve will be obsolete. A old survey may be updated to a new report version but it must be possible to get all obsolete data, when watching it with a old report version.
From these reports I generate fix tables in another database fill with only the importet values by the latest validated version. So in my database only a history will be stored and - if you want to look up a survey - a survey-report can be created.
The data in the fields can have different types. Some are text, others are int or double or bit, but fix for a special field.
Now I don't want to make tables for every report (there will be many reports) and every version of a report, because I would have a table for every repeatable part and every of these tables can have hundreds of columns. In the worst case only one or two fields will be fields in a survey-version and the report-version won't be compatible when I drop or add some fields for a new version.
So I thought about a dynamic database. I can store every report-version and survey-version in a small set of tables. I would split the different value-types in different tables, so that the value can be found in different tables.
I could generate stored procedures on this, because the tables won't change. As only few people would work with it and the database is strictly seperated from the main database, I think I could live with the time a query will take longer and I will save lot of design time for every change.
But I don't know if I forgot something. Is it a bad idea? I talked to some database designers in our company. No one happy about such a solution (I ain't, too), but no one knows a better handable solution. But perhaps some of the board users know?
Thanks,
Ingo
------------------------------
A bug in a Microsoft Product? No! It's not a bug it's an undocumented feature!
|
|
|
|
|
...
I think I am going to side with your database designers. 
Maybe just pass your user a copy of InfoPath?
|
|
|
|
|
DarkElv wrote: I think I am going to side with your database designers.
 Yes, I'm too.
DarkElv wrote: Maybe just pass your user a copy of InfoPath?
InfoPath is not powerful enough. I got just the first part of the specification, but it seems not possible to integrate all functions. And InfoPath is to slow. There is to much data and after inserting a few replications of a repeatable sections it takes more than 20 seconds for an insertion.
I worked out a first part of the report and it's not possible to handle without taking a look at the other required functions.
Thanks,
Ingo
------------------------------
A bug in a Microsoft Product? No! It's not a bug it's an undocumented feature!
|
|
|
|
|
hi all,
i am completely new to working with data in .net.
i am using c#.
can anyone point me to a good tutorial to start with?
regards,
rnv
|
|
|
|
|
rnvrnv wrote: can anyone point me to a good tutorial to start with?
There are many high quality articles here on Code Project that can help you get started
PJC
|
|
|
|
|
Hey, anyone tested by how much does a Profiler trace slow down SQL Server?
|
|
|
|
|
I can't give you numbers, but I've seen it bring a SQL server down when it was close to capacity, so don't profile when the system is close to failure. Also, only use it for relative ranking between batches, not for any absolute numbers.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
Hi, how do you configure SQL "Server Log" (NOT transaction log/ldf) to recycle every now and then? Say, everytime when "Server Log" size hit 50MB?
Thanks.
|
|
|
|
|
what is oj, it is used with from clause while joining tables
please explain fully
thank u
|
|
|
|
|
dev dhoundiyal wrote: what is oj
Well, my understanding was that OJ is an abbrebiation of Orange Juice.
dev dhoundiyal wrote: it is used with from clause while joining tables
Then I'm guessing it refers to the first letter each of OUTER JOIN. I have never heard it being refered to as OJ before. I suggest you don't use that as few people will understand you.
dev dhoundiyal wrote: please explain fully
There isn't space in a forum post to "explain fully" entire chapters of books are devoted to the subject.
A brief explanation is that an outer join takes all the rows on the left side and all the rows on the right side and joins them together, matching those that it can, leaving nulls in the columns where a match cannot be found.
Consider the following two tables:
A
key data
1 a
2 b
B
key data
1 d
3 e
A statement like this:
SELECT A.key AS aKey, A.data AS aData, B.key AS bKey, B.data AS bData
FROM A
OUTER JOIN B ON a.key = b.key Will produce a result like this:
aKey aData bKey bData
1 a 1 d
2 b null null
null null 3 e
By comparison, an INNER JOIN would have returned one row, the only row that matched (i.e. The row with key 1); a LEFT OUTER JOIN would have return two rows (all the rows in A [the left side] and any matching rows in B); a RIGHT OUTER JOIN would have returned two rows again (all the rows in B [the right side] and any matching rows in A)
Does this help?
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
That seems a pretty damn good explaination of an OJ to me 
|
|
|
|
|
I'm trying to make a form or perhaps simply use Web Developer 2005 or SQL Server 2005 management to populate a database with pictures. So simply I want upload images to an sql database, if I make a form I would like to use asp.net.
Option 1
If I make the form using asp.net, i have no idea what to use to put them in that db. I have use db before with access to put text in the db, that's it.
Option 2
In Web Developer and SQL server I can run a query to pass the data into the db, but I don't know how to write sql besides the basic select, update, delete and insert into with basics one word string.
Can anybody give me a clue, link or code to do this...thanks
|
|
|
|
|
See here[^].
NOTE: Posting the same question in multiple forums is considered bad manners.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
I want to create an application where is one Access database.
In this database there is table called customers.
I've made Crystal Report called CrystalReport1. When I start the program
it asks me for user name and password, server name and database name.
How can I create a report from password protected database in C#?
|
|
|
|
|
string cnnString = "Server=localhost;Port=3306;Database=tibia;Uid=client;Pwd=password";<br />
<br />
MySqlConnection cnx = new MySqlConnection(cnnString);<br />
<br />
string cmdText = "SELECT * FROM queue";<br />
MySqlCommand cmd = new MySqlCommand(cmdText, cnx);<br />
<br />
cnx.Open();<br />
<br />
MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(cmdText, cnx);<br />
string szInsert = "INSERT INTO queue VALUES ('testing')";<br />
myDataAdapter.InsertCommand = new MySqlCommand(szInsert, cnx);<br />
<br />
cnx.Close();
I don't recieve an error and my database is not being updated. I can recieve data using close to the same code, but I can not send data to the database. See the problem? Thanks for the help!
|
|
|
|
|
Aren't you missing some kind of query execution method from the MySQLDataAdapter class? Your code you've supplied looks good as far as getting ready to execute the query. Try something like ExecuteReader() or whatever MySQLDataAdapter uses to execute the query specified in the InsertCommand attribute.
I hope this helps,
Paul
|
|
|
|
|
Wow, I can not believe I missed that.
Thanks a ton man.
|
|
|
|
|
Glad to be of help 
PJC
|
|
|
|
|
Is there a function in MS SQL server to get the total size of the current database (or a single table) in KBs?
It's possible to get this information through Enterprise Manager but I would like to get it through C# code.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
