|
Also install MDAC 2.7 on client machine.
It will definatly work.
Pankaj Kulkarni
|
|
|
|
|
It'll work so long as he built the connection string properly...
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
I'm new to SQL, so forgive my ignorance.
I have a number of related tables. Of these there are 3 which are of concern to my problem: Invoices, InvoiceItems and Products. There is a 1:M r/ship between Invoices as well as between Products and Invoice items. The Invoice items table has InvoiceItemID, InvoiceID, ProductID, Quantity and Price.
I would like to add 3 InvoiceItems as records in my InvoiceItems table and i would like to have them related to one Invoice (the last Invoice in my case) in the invoices table, that is, also the field in the InvoiceItems called InvoiceID for these 3 records should have the same InvoiceID. All the Primary Keys are set to AutoNumber.
These are the the statements i used:
INSERT INTO InvoiceItems ( InvoiceID, ProductID, Quantity, Price )
VALUES (4, 8, 1, 0);
INSERT INTO InvoiceItems ( InvoiceID, ProductID, Quantity, Price )
VALUES (4, 9, 4, 0);
INSERT INTO InvoiceItems ( InvoiceID, ProductID, Quantity, Price )
VALUES (4, 10, 2, 0);
I would like to do this using some sort of INSERT SQL statements, but it's just not working. I can INSERT one InvoiceItem, but when i try to INSERT the next one i get some long error saying something about "type conversion failure","key violations", "lock violations" and "validation rule violations"
Does anyone know why this is happening and how to correct it?
Any help would be much appreciated.
|
|
|
|
|
I think you have not entered the master data
ProductID Name
8 ABC
InvoiceID Invoice Name
4 ZXZ
InvoiceID ProductID Qty Price
4 8 1 0 ----> ok
4 9 4 0 ----> ProductID is not existing
4 10 2 0 ----> ProductID is not existing
I think ! This is the problem
|
|
|
|
|
Thanks ... you were right
...i can't believe it was something so small
|
|
|
|
|
Hi;
I am using SQLXML 4.0 for loading bulk load data. I'm having a problem with connectionstring, when I use sqlclient i.e. sqlconnection it gives me error and does not connect to the database. When I use the connection string providing SQLOLEDB provider it works.
private static SqlConnection getConnection()
{
SqlConnectionStringBuilder conStrBuilder = new SqlConnectionStringBuilder();
conStrBuilder.DataSource = "abc";
conStrBuilder.InitialCatalog = "abc";
conStrBuilder.UserID = "abc";
conStrBuilder.Password = "abc";
return new SqlConnection(conStrBuilder.ConnectionString);
}
private void ExecuteBulkLoad()
{
SqlConnection conn = getConnection();
conn.Open();
SQLXMLBulkLoad4 objXBL = new SQLXMLBulkLoad4();
try
{
objXBL.ConnectionString = conn.ConnectionString; // it does not work
objXBL.BulkLoad = true;
objXBL.ErrorLogFile = @"C:\SQLXML3Books.errlog";
objXBL.KeepIdentity = false;
objXBL.Execute(@"C:\booksmapping.xml", @"C:\Books.xml");
}
catch
{
throw;
}
finally
{
// close of the writer and the stream objects.
objXBL = null;
}
}
But if I use objXBL.ConnectionString = "provider=SQLOLEDB.1;data source=abc;database=abc;uid=abc;pwd=abc";
It worked fine.
Any help?
Thanks
M. Essa Mughal
Toronto, CANADA
|
|
|
|
|
Hi;
I've just started playing with SQLXML 4.0, here is the code which I am using to load the XML into database.
SQLXMLBulkLoad4Class bulkLoad = new SQLXMLBulkLoad4Class();
bulkLoad.ConnectionString = connString;
bulkLoad.BulkLoad = true;
bulkLoad.KeepIdentity = false;
bulkLoad.XMLFragment = true;
bulkLoad.Execute("C:\books.xsd", "C:\books.xml");
My question is that, the mapping file i.e. xsd or booksmapping.xml is compulsory to pass or we can have that information into xml data file? We want to avoid two files to maintain.
Thanks
M. Essa Mughal
Toronto, CANADA
Email: essamughal@yahoo.com
|
|
|
|
|
Hello everyone,
I have been working with VBA code in MS Access 2003. I have a Command button which sends an email and then update the column of a table. Here is what I have...
<br />
DoCmd.SendObject, , acForMatTXT, strWho, , , strSubject, strText, -1<br />
strSQL = "UPDATE [Action Item Table] SET ysnActionAssigned = -1 " & _<br />
"Where [Action Item Number] = " Me.Action_Item_Number & ";"<br />
On Error GoTo Err_Execute<br />
CurrentDB.Execute strSQL<br />
on Error GoTo 0<br />
<br />
Err_Execute:<br />
' Here I display error message in a message box<br />
I am getting an error of "Type mismatch". Can someone tell me what is going wrong.
Thank you.
Bashar Amin
Sr. Software Engineer
|
|
|
|
|
Which line is triggering the Type Mismatch?
|
|
|
|
|
CurrentDB.Execute strSQL
When executing the SQL command.
Bashar Amin
Sr. Software Engineer
|
|
|
|
|
Are you explicitly using Dim to create the variable strSQL as a string?
|
|
|
|
|
Yes.
It is like a local variable in the same function.
Bashar Amin
Sr. Software Engineer
|
|
|
|
|
Try adding a Debug.Print line to output your constructed sql UPDATE statement to the immediate window. You can check to make sure the form values are being incorporated the way you think they are. You can also then copy and paste the actual statement from the immediate window and execute it through the SQL view of an access query. This is a good way to test whether or not there is a problem with the UPDATE statement itself.
|
|
|
|
|
Mike,
Thank you for you valuable suggestion. Debug.print is a good way to troubleshoot software bug. I am still getting the "Type mismatch" error but it is updating the table. So I comment out the error check part for now.
Bashar Amin
Sr. Software Engineer
|
|
|
|
|
Hi Bashar. Wow - that's interesting. So the UPDATE command is successfully executing? AND you are getting a Type Mismatch error on the same line?
Just to see what happens, try changing the line
CurrentDB.Execute strSQL to
dim iResult as integer
iResult = CurrentDB.Execute(strSQL)
|
|
|
|
|
I would guess it is this
codeamin wrote: [Action Item Number] = " Me.Action_Item_Number & "
that is causing your problem. in particular the = " Me.Action_Item_Number & "
You are missing the first &. As far as the query is concerned you are trying to enter the literal string value 'Me.Action_Item_Number &' into a numeric column.
Jon
|
|
|
|
|
Jon,
Good thinking. Here is what I have my code now:
<br />
Dim num as Integer<br />
num = Me.Item_Number<br />
strSQL = "UPDATE [Action Item Table] SET [Action Item Table].ysnActionAssigned = -1 " & _<br />
"WHERE [Item Number] = " & num & ";"<br />
CurrentDB.Execute strSQL<br />
I was still getting a "Type Mismatch" error if I check for error. I comment out the error check as it was updating the [Action Item Table].
Thank's for your help.
Bashar Amin
Sr. Software Engineer
|
|
|
|
|
Hi,
I've a running .NET 2.0 app that connects to DB locally (thru ODBC),
using an entry in connectionStrings section in web.config.
Now I want to connect to a second DB, but adding just
another entry in web.config (see below).
(replaced angle brackets by [])
[connectionStrings]
[add name="My1stDbConn" connectionString="Dsn=FirstDB" providerName="System.Data.Odbc" /]
[add name="My2ndDbConn" connectionString="Dsn=SecondDB" providerName="System.Data.Odbc"/]
[/connectionStrings]
How do I tell my code I wanna use the second entry, instead of first one?
Thanks in advance.
Eugenio
-- modified at 7:12 Thursday 9th February, 2006
|
|
|
|
|
Hi Eugenio. Consider what your connection code looks like for the first connection - you must be identifying it by its name "My1stDbConn" at some point. You would do the same thing to create your second connection - just refer to its name "My2ndDbConn" when pulling connection string values from web.config.
|
|
|
|
|
Hi Everybody,
I wrote code deleting data in Excel file by using OLEDB.
But ADO.NET show error
"Delete data in a linked table is not supported by this ISAM"
Now, I want to delete data in Excel file.
Please help me!!!!
Thanks you
Tran Thai Nam
|
|
|
|
|
To my knowledge, there is no way to delete rows in an Excel spreadsheet through OLEDB. You may need to use interop calls directly to the Excel COM objects to accomplish this.
|
|
|
|
|
I think so. Then I change Delete Command to Update Command (fill null value) in ASP.NET.
UPDATE [SHEET1$] SET F2='1' WHERE F1='1'
But I recieved the error "Operation must use an updateable query". I don't know how to fix this bug.
Please help me!
Thanks
Tran Thai Nam
-- modified at 20:02 Thursday 9th February, 2006
|
|
|
|
|
|
I choose my table key is int type and use identity. I don't know HOw many record it have to corrupt my database? Please help me. If that not good please tell me a better way. Thanks
|
|
|
|
|
I don't know what you mean by "when will my database corrupt"? Do you mean how many rows can you insert before the key has to wrap around? If you are using an int type you have a maximum of 4294967296 unique values.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
