|
I keep getting an exception when I call the DataAdapter Update method.
I have been trying to figure out what is causing the null reference
exception for this code for what seems like forever:
private void DoInserts(OdbcDataAdapter odbcDataAdapter, string
tableName)
{
DataTable dataTableChanged =
dsTapes.Tables[tableName].GetChanges(DataRowState.Added);
if ((dataTableChanged != null) &&
(dataTableChanged.Rows.Count > 0))
{
// Open the connection if its not already open.
if (odbcConnection.State !=
System.Data.ConnectionState.Open)
{
odbcConnection.Open();
}
//Create a new transaction.
odbcDataAdapter.InsertCommand.Transaction =
odbcConnection.BeginTransaction();
try
{
//Submit the changes.
odbcDataAdapter.Update(dsTapes,
dataTableChanged.TableName.ToString());
//Commit the changes and close the connection.
odbcDataAdapter.InsertCommand.Transaction.Commit();
}
catch (Exception ex)
{
odbcDataAdapter.InsertCommand.Transaction.Rollback();
throw (ex);
}
}
}
The exception information has not been helpful. All it says is: "Object
reference not set to an instance of an object."
I don't know if this will help, but the Insert command looks like this:
tapeLogInsert = odbcConnection.CreateCommand();
tapeLogInsert.CommandText =
"INSERT INTO " + tapeLogODBCName.Trim()
+ " (NBR, Backup_Name, BakDate, ScrDate, Location, "
+ "Tape_No, Tape_Set, Usage, Comment, UseDate) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
tapeLogInsert.Parameters.Add(new OdbcParameter("NBR",
OdbcType.Int));
tapeLogInsert.Parameters["NBR"].SourceColumn = "nbr";
tapeLogInsert.Parameters.Add("Backup_Name", OdbcType.Char, 17,
"backup_name");
tapeLogInsert.Parameters.Add(new OdbcParameter("BakDate",
OdbcType.DateTime));
tapeLogInsert.Parameters["BakDate"].SourceColumn = "bakdate";
tapeLogInsert.Parameters.Add(new OdbcParameter("ScrDate",
OdbcType.DateTime));
tapeLogInsert.Parameters["ScrDate"].SourceColumn = "scrdate";
tapeLogInsert.Parameters.Add("Location", OdbcType.Char, 1,
"location");
tapeLogInsert.Parameters.Add(new OdbcParameter("Tape_No",
OdbcType.SmallInt));
tapeLogInsert.Parameters["Tape_No"].SourceColumn = "tape_no";
tapeLogInsert.Parameters.Add(new OdbcParameter("Tape_Set",
OdbcType.SmallInt));
tapeLogInsert.Parameters["Tape_Set"].SourceColumn = "tape_set";
tapeLogInsert.Parameters.Add(new OdbcParameter("Usage",
OdbcType.SmallInt));
tapeLogInsert.Parameters["Usage"].SourceColumn = "usage";
tapeLogInsert.Parameters.Add("Comment", OdbcType.VarChar, 50,
"comment");
tapeLogInsert.Parameters.Add(new OdbcParameter("UseDate",
OdbcType.DateTime));
tapeLogInsert.Parameters["UseDate"].SourceColumn = "usedate";
tapeLogAdapter.InsertCommand = tapeLogInsert;
Does any one have any idea what is going on here or how to go about
finding out? Any suggestions would be appreciated: I don't even know
how to figure out which reference is null!
|
|
|
|
|
When using c#, I've found I have to re-reference the commands when using CreateCommand.
Try:
odbcCommand tapeLogInsert = odbcConnection.CreateCommand();
instead of:
tapeLogInsert = odbcConnection.CreateCommand();
even if you lead with:
odbcCommand tapeLogInsert = new odbcCommand();
tapeLogInsert = odbcConnection.CreateCommand();
Hope that helps
RABB17
|
|
|
|
|
I am tring to create a table in a mdb file, but the maximum number of columns i can add is 40. When i try to add the same number (or move) of columns from the access interface, there is no problem.
Can you tell me if it is something i overlooked.
The connection string to the mdb file is : Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<filepath>
I am using C#, on .NET Framework 1.1 and the connection class is OleDbConnection
|
|
|
|
|
So, what is the error you get? What is the SQL you are using?
|
|
|
|
|
I'm using the next sequence of SQL:
CREATE TABLE Table_1 ( ID char(50) PRIMARY KEY);
After the creation of the table i'm using a for cycle to add as many columns in the table as necesary. (max. 200)
The query run in the for cycle is: ALTER TABLE Table_1 ADD column_i char(50); - where "i" is the counter
I get the following error when i add more then 40 columns:
System.Data.OleDb.OleDbException: Record is too large.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
|
|
|
|
|
You are exceeding a published limit for Microsoft Access:
Access XP allows a maximum of 2000 characters per record in a Table.
Access 2003 allows 4000, but depending on your connection string,
you may be using the access xp engine version even thought the application version
you have is 2003, which would explain why you can interactively create
up to 80 fields of Char(50) data. See this[^] for a list of the limitations.
BTW, a table with 200 50 char fields does not look like a very good design...
|
|
|
|
|
I know is not very good, but it was only a test. The type of the fields will be changed when i start codding seriously.
10x for the link.
|
|
|
|
|
Hi,
recently I needed a tool to run SQL scripts against a Microsoft Access database. I found OleDbCmd on Sourceforge.net (http://sourceforge.net/projects/oledbcmd/[^] which did the trick.
However it had a number of shortcomings, so I extended it somewhat and have released my updates through sourceforge.net as version 1.0. These usage notes should explain the extent of the updated application:
----------------------------------------------------------------------------------------------------------------
Purpose:
This program allows you to execute SQL commands against an OleDB Database
- eg a Microsoft Access database, an Excel spreadsheet, etc. This can be a
powerful adjunct to LGX Report (see www.freereporting.com) as it allows
database creation to be scripted rather than manually creating a database
with Microsoft Access. Another useful free tool to load data into your
database is Microsoft's LogParser 2.2 - search their site for the latest
download location for this tool.
Usage:
OleDBCmd [/help] [/ver] [/connect=<oledb connect="" string=""> | /file=filename] ...
[/create] [/script=<sql script="" filename=""> | /cmd="sql command" ...
[/quiet] [/CSV] /nohead] [/starComments]
Parameters:
/help Print the usage details and exit.
/ver Print the version and license details.
/connect Specifies the OleDB connection string for the database, eg
'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=test.mdb'
/file Specifies a file name, the provider will be set to
Microsoft.Jet.OLEDB.4.0. Both .mdb and .xls files are
handled.
/create The program will attempt to create the database.
/script Specifies a script file for the SQL commands to be
read from. Note, commands may span multiple lines and
must be terminated with a semi-colon. Remark lines must
start with -- following any leading whitespace.
/cmd Specifies a single SQL command to execute.
Note, if neither the /script nor /cmd arguments are given
commands are read from the console - comment processing
is not performed.
/quiet Suppresses command echo and status messages. Only SQL
output is written to the console.
/CSV SQL results are output in CSV format rather than the default
TSV format.
/nohead Excludes the header row from the results.
/starComments Allows embedded and multi-line comments using /* ... */ format.
----------------------------------------------------------------------------------------------------------------
As noted above, I found this useful in conjuction with the free web reporting tool - LGX Report (http://www.freereporting.com/[^]) as I can then design databases using Enterprise Architect from http://www.sparxsystems.com.au/[^] and generate the script to create the database and run it with OleDbCmd.
Hope this is useful to others out there looking to leverage free and low-cost tools to build solutions quickly.
Regards,
Ian.
Ian Hogan - Consultant
THINKronicity Pty Ltd
http://THINKronicity.com.au/[^]
|
|
|
|
|
i want to make an select statment like that
select * from Table_name Where ID=2 and ID=4 and ID=5 while the Id in not the primery key
amarni
|
|
|
|
|
|
sorry it is not primery key
but i need this
imagine that i have three table
x has X_ID(Primery) and Name,..... atrribute
y has Y_ID(Primery) and desc,..... atrribute
the relation between them is many to many
so that i creat a table between them named xy that conatain both IDs X_ID and y_ID so that i want to make select statment like that select * from x Where Y_ID=2 and Y_ID=4 and Y_ID=5
i hope you understatnd it now
Amarni
|
|
|
|
|
Could you please post exactly what you want to arrive ?
|
|
|
|
|
I think you just want a join like this:
SELECT DISTINCT x.*
FROM x
INNER JOIN xy
ON x.x_id = xy.x_id
WHERE xy.y_id in (2,4,5)
|
|
|
|
|
Hi there!
Anyone having any idea that how can i configure SQL MAIL on my machine as i am working on a LOCAL MACHINE on which i have installed SQL Server 2000 and VS .NET 2003 as i am working on ASP.NET. No server access i am having. My machine is acting as the localhost. I have tried configuring SQL MAIL on my machine but failed. And I have searched on net SQL Server uses two services to handle mail : MSSQLServer-provide SQL MAIL feature and SQLServerAgent(SQLAgentMail)...
I need to use Trigger to send an email notificaltion to reciepents for which i have written a trigger but on Updating the table it shows me the following error...
Server: Msg 17985, Level 16, State 1, Line 0
xp_sendmail: Procedure expects parameter @user, which was not supplied.
(1 row(s) affected)
Any sort of help will be appriciated.
Thanks a Lot in advance...
Bhawna
bhawna
|
|
|
|
|
Have you read this: clickety[^]
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
My issue is the update of a dataBase table with a dataSet table as a parameter using C# and SQL Server 2005.
Below is the function I used in my code ---> verry simple.
I noticed that in line :updates = da.Update(tmpDS, tableName) ,updates variable is 0 ,so no update possible.
Perhaps this is not the way to do the update.
IF you can help me with some hints ,
Thanks in advance!
public DataSet dsUpdateData(DataSet ds, string tableName, string ConnectionString)
{
DataSet tmpDS = new DataSet();
try
{
SqlDataAdapter da = new SqlDataAdapter(SQLQuery + tableName, ConnectionString);
int updates = da.Fill(tmpDS,tableName);
foreach (DataRow dataRow in ds.Tables[tableName].Rows)
{
tmpDS.Tables[tableName].ImportRow(dataRow);
tmpDS.Tables[tableName].AcceptChanges();
}
updates = da.Update(tmpDS, tableName);
return tmpDS;
}
catch (Exception ex)
{
Debug.WriteLine(ex.ToString());
}
}
-- modified at 14:43 Tuesday 18th July, 2006
|
|
|
|
|
Check your table names. It appears you are importing rows into tableName1 but trying to update tableName
|
|
|
|
|
Yes you where right on that,but it was just a typo error tableName1.
Even so, it is still not working.
I modified the original message.
Thank you anyway!
|
|
|
|
|
Hi
in some applications i have seen that DataGrids have a Control which is the number of the DataGrid's Row and it has next( > ), previous, last and ...
whats the name of that control?
thanks
|
|
|
|
|
hamidreza_buddy wrote: whats the name of that control?
BindingNavigator or something like that?
|
|
|
|
|
Hi,
There is a requirement to insert data into Bulk like 20,000 - 30,000 rows into database (sql server 2005).
Description:
I have to get one number from text file,bifercate that number and from 1st 5 digit i have to find out ID from sql server 2005 table and then go for second number of that number ,find out ID from sql server 2005 table,this process happens for 4 times for that one number and i have to do same process for 20000 rows.
Once i get all those IDs into variables into vb.net from sql server 2005,i have to insert those into one physical table and than one package will run that insert those records into another table.
Here main problem i am feeling is speed issue,to process 20000 rows for getting ids and inserting back into sql server takes somuch time.
So can some body help me out which way to go to insert records into database using sql server 2005?
thanks
montu3377
|
|
|
|
|
You can Bulk insert (using BCP or managed SqlBulkCopy) the text file contents to a table and use a stored proc to do read from that table and insert/update into the target table.
|
|
|
|
|
Hi everyone,
I'm hoping someone can help me with some sql statements. I have a temp table that contains 30 dates that a student has missed in the last year. I also have a holiday table of when training was not available. I want to find out if there are 6 consecutive days missed excluding weekends and holidays (from the holiday table). I know this is some nasty looping statement but I can't get my brain around it.
I would like do this in a stored proc but I could use C# if necessary.
Thanks, Jessica
|
|
|
|
|
Why don't you give it a try, post your code, and we will help you where you are struggling. What you have provided here sounds too much like a school assignment. People aren't likely to do your assignment for you - but they will help you figure out the solution, provided you put forth an effort.
That's just my suggestion.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Nasty requirements - here is a first attempt using a cursor. I think I would change the EndDate calculation to a function so that you could remove the cursor.
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @cnt INTEGER
DECLARE cur CURSOR FOR
SELECT
AbsentDate
FROM
#tmpTable
OPEN cur
FETCH NEXT FROM cur INTO @StartDate
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cnt = 0
SET @EndDate = @StartDate
WHILE @cnt < 6
BEGIN
SET @cnt = @cnt + 1
SET @EndDate = DATEADD(d,1,@EndDate)
WHILE (DATEPART(dw,@EndDate) IN (1,7)) OR
(EXISTS(SELECT * FROM Holidays
WHERE HolidayDate = @EndDate))
BEGIN
SET @EndDate = DATEADD(d,1,@EndDate)
END
END
IF ((SELECT COUNT(*)
FROM #tmpTable
WHERE AbsentDate BETWEEN @StartDate AND @EndDate) > 5)
BEGIN
-- Save Date
-- or do other desired processing
END
FETCH NEXT FROM cur INTO @StartDate
END
CLOSE cur
DEALLOCATE cur
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
