|
Hi, I need to do a lookup in my DTS package and the lookup will most probably return more than one value:
Dim names()<br />
names = DTSLookups("name_matching").Execute(DTSSource("param1"))
The DTS Lookup "name_matching" is as follows:
SELECT supplier_no<br />
FROM supplier_table<br />
WHERE (supplier_name LIKE ? + '%')
Is it possible for me to do this? Returning multiple values from a DTS lookup? I know lookups can return value consisting of multiple columns (http://www.sqljunkies.com/How%20To/C093CD3A-8695-4BD7-95C5-1B14E7C4D303.scuk[^]), but multiple rows... I'm not so sure.
Norman Fung
|
|
|
|
|
Insert data from a .txt file into a table, using the osql tool?
Assume I have table TableA with 3 columns named Column1, Column2 and Column3 , and I have a text file 1.txt with the values -seperated by tabs.
Thanks,
Yaakov
|
|
|
|
|
Why do you want to use osql . You can import directly through Import Data with the Data Source your text file.
<italic>Work hard, Work effectively and a bit of luck is the key to success.
|
|
|
|
|
I dont have the Import Data tool.
I use Sql Express 2005 .
|
|
|
|
|
I can't guarantee all of this code because I pulled it out of one of my programs and then edited it down to what I thought you could use, but I did not test it.
Private Sub loadDatagrid()
boolBeenToGatagrid = True
AddRecords()
strImportfolder = aPath
'this is the folder in which the file resides
strFilename = "selectdata.csv"
'this is the csv file to be imported
strConnstr = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + strImportfolder + ";Extensions=asc,csv,tab,txt;Persist Security Info=False;"
Conn = New System.Data.Odbc.OdbcConnection(strConnstr)
da = New System.data.Odbc.OdbcDataAdapter("select * from [" + strFilename + "]", Conn)
Try
ds2.Clear()
da.Fill(ds2, "TableName")
Catch esc As Exception
System.Windows.Forms.MessageBox.Show(esc.Message, "")
End Try
With DataGrid1
.SetDataBinding(ds2, "TableName")
' Set DataGrid Background Color
.GridLineColor = System.Drawing.Color.Black
.BackgroundColor = System.Drawing.Color.Lavender
' Set DataGrid Caption Background Color
.CaptionBackColor = System.Drawing.Color.SlateBlue
' Set DataGrid Caption Foreground Color
.CaptionForeColor = System.Drawing.Color.White 'LemonChiffon
' Set DataGrid Parent Rows Background Color
.ParentRowsBackColor = System.Drawing.Color.Lavender
' Set DataGrid Parent Rows Foreground Color
.ParentRowsForeColor = System.Drawing.Color.SlateBlue
' Set DataGrid Caption Text
.CaptionText = "Study List"
' Clear DataGrid Table Styles
.TableStyles.Clear()
End With
' Set data grid Table Style
Dim tblCrrncMngr As System.Windows.Forms.CurrencyManager = CType(BindingContext(ds2.Tables.Item("TableName")), System.Windows.Forms.CurrencyManager)
Dim TblStyle As New System.Windows.Forms.DataGridTableStyle
With TblStyle
.MappingName = "TableName"
''''''.MappingName = "Studies"
.BackColor = System.Drawing.Color.White
.ForeColor = System.Drawing.Color.Black 'DarkSlateBlue
.GridLineColor = System.Drawing.Color.Black 'MediumSlateBlue
.HeaderBackColor = System.Drawing.Color.Lavender
.HeaderForeColor = System.Drawing.Color.DarkBlue 'MediumSlateBlue
.AlternatingBackColor = System.Drawing.Color.LightGray
.RowHeaderWidth = 10
' Set column styles
With .GridColumnStyles
' Set datagrid ColumnStyle for ID field
.Add(New DataGridPictureColumn)
With .Item(0)
.MappingName = "Wav"
.HeaderText = ""
.Width = 20
.NullText = String.Empty
End With
End With
End With
' Add TableStyle
DataGrid1.TableStyles.Add(TblStyle)
Conn.Close()
ds2.Dispose()
''''''data_table.Dispose()
da.Dispose()
end sub
There is another way to do this without using the QSQL.
|
|
|
|
|
Look into the BCP command line utility.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Hi,
I successfully exported one table from SQL server to an access file using
INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','DataSource="C:\TEST\test.mdb"')..............
But when I tried to export to a access file located on a different machine where I (Everyone)am having full rights it is giving following error:
Server: Msg 7399, Level 16, State 1, Line 1<br />
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. <br />
[OLE/DB provider returned message: Could not find installable ISAM.]<br />
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Any one please help me to sort out this issue.
Thanks in advance
Jay Krishna
|
|
|
|
|
Can anyone tell me How to determine whether MSDE or SQLSERVER edition is installed on my machine.
I want to know if this can be determined from Registry values??
Please In my application I need to determine this from registery only..
Pls help me??
RuchirDhar Dwivedi
Software Engineer
Windowmaker Software Pvt.Ltd.
Baroda, India.
|
|
|
|
|
SUBSTRING(CAST(Round(100.00 * (a.attempts - Sum(a.accepts)) - Round(100.00 * (b.attempts - Sum(b.accepts)) / Round(100.00 * (a.attempts - Sum(a.accepts AS CHAR),1,4) AS SUMMARY
The above is not complete. Could someone help?
Thanks
|
|
|
|
|
|
Hmmm
okay
4 colums with int values
I want a percent difference
A.Attempts A.Accepts B.Attempts B.Accepts
10000 9000 6500 4500
10000 - 9000 = 1000
Then
6500 - 4500 = 2000
Then difference and yes it may be negative
1000 - 2000 = -1000
Then
10000 - 9000 = 1000 / -1000 = -1
SUBSTRING(CAST(Round(100.00 * (a.attempts - Sum(a.accepts)) - Round(100.00 * (b.attempts - Sum(b.accepts)) / Round(100.00 * (a.attempts - Sum(a.accepts AS CHAR),1,4) AS SUMMARY
|
|
|
|
|
I don't see where your SQL matches the example you've given. Where is the SUM() coming from, what are you doing with the SUBSTRING.
From the example you gave this would appear to be the correct code:
SELECT cast((A.Attempts-A.Accepts) as float) / cast(((A.Attempts-A.Accepts) - (B.Attempts-B.Accepts)) as float)
FROM ...
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Sorry but what I was attempting to do with the substring function is return 4 characters in lentgh .
The code you provided works and thank you
|
|
|
|
|
Hi,
I am facing a problem while inserting a datarow with data in to a new dataTable.
can anybody plz help me how to add a row with data to a new data table.
regards,
Vinu
|
|
|
|
|
It would be very helpful , to show us whats the error you get ?
Some code would be helpful too..
Bahadir Cambel
|
|
|
|
|
Hi,
I'm having a runtime error when I execute the BeginTrans() _Connection method on a MSAccess database.
Can someone tell me if there is any condition which can cause the error on this method?
I don't believe that the problem would be the ADO version, because I have another application that uses this method on a MSAccess database without any problem, but any way, I am working with ADO 2.7.
Thanks in advance,
Vinicius
|
|
|
|
|
|
Yes it is.
I found, somewhere in MSDN library, that if there is a recordset opened, the BeginTrans doesn't work. In my case, I have a recordset opened. I tryed to do the operation while the recordset was closed and that worked. The problem is that I need the recordset to be opened.
Vinicius
|
|
|
|
|
I am needing to copy data from an MSSQL table to a dbaseIV table and would ideally like to do this with an SQL trigger on the source table.
Can someone please advise if it is possible to do this and if so provide some clues on how to go about this? I have established an ODBC connection to the dbase table folder but I cannot resolve how to address this from Query Analyser.
Many thanks,
Kerry
Kerry Johnson
Business Systems Consultant
|
|
|
|
|
Hi.
I'm trying to connect to a remote SQL serer express through TCP/IP.
I entered the <IP address\instance name> in the server name field. I tried also without the instance name.
I tried with integrated security as well as authentication. Also, I tried with the port address, and I get the error message "TCP Provider: Connection closed forcibly by the remote computer".
P.S. the protocol is enabled on noth the local and remote sides.
Can anyone explain me please how to establish a connection?
Thanks,
Yaakov
|
|
|
|
|
|
I'm trying to connect from the VS IDE.
I own both the server and the client, and I see no problem on both.
Can you reffer me to a relevant artical?
Thanks,
Yaakov
|
|
|
|
|
Hi.
I want to change a column in a table from BIGINT to DECIMAL or NUMERIC.
But i wonder, what is the performance loss when doing sorts,filters, arithmetic operations (like multiply and adds) on these data types (dec and numerics).
and, btw, what is the diffrence between 'NUMERIC' and 'DECIMAL'?
thanks
|
|
|
|
|
Menny Even Danan wrote:
what is the diffrence between 'NUMERIC' and 'DECIMAL'?
Reading the documentation indicates that they are synonyms for each other.
Menny Even Danan wrote:
But i wonder, what is the performance loss when doing sorts,filters, arithmetic operations
Normally the main killer in performance is to do with volume of data being processed(*) so I'd say that on a large dataset any performance hit is negligable. So, I'd suggest that you are careful about the size of the data. This table from the books on-line will help:
+-----------+---------------+
| Precision | Storage bytes |
+-----------+---------------+
| 1 - 9 | 5 |
| 10-19 | 9 |
| 20-28 | 13 |
| 29-38 | 17 |
+-----------+---------------+
[EDIT] Missed a bit: By comparison a BIGINT is 8 bytes in length but is constrained to a scale of 0 (nothing after the decimal point) and has a maximum value of (2^63)-1 while a NUMERIC/DECIMAL has a potentially much greater range [/EDIT]
* not necessarlily the same as volume of data returned
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hi All,
I am wondering if anybody know sql statement of how to import all the text from the text file into Access database field?
I have a text file, a.txt and afield called TextBody in the table.
TextBody is a memo size.
I was inserting like following
<br />
aTextfile.open("c:\\a.txt",ios::in);<br />
if(aTextfile)<br />
{ <br />
while(!aTextfile.eof())<br />
{<br />
aTextfile.getline(data,1024,'\n');<br />
temp.Format("%s",data);<br />
<br />
qstr.Format("UPDATE %s Set TextBody = TextBody & '%s' ",tablename,temp);<br />
rs.m_pDatabase->ExecuteSQL(qstr);<br />
}<br />
<br />
aTextfile.close();<br />
}<br />
But it takes so long to import all the text from the a.txt. Is there any better way?
Thanks
|
|
|
|