|
|
I've tried the "select TABLE_NAME from INFORMATION_SCHEMA.TABLES" and tried the sysobjects route too, but the DSN I am using (ODBC) reports that no such table exists within the given database. The DB itself is a MySQL database. I don't want to use any non-standard queries because the code must work across multiple datasources.
Then I went the catalog route and tried:
SQLTables(hstmt3, NULL, 0, NULL, 0, "Orders", SQL_NTS, NULL, 0);
and I get a compile error about param 6 ("Orders") being of the incorrect type - even though I copied and pasted it directly from MSDN.
What should I do to get a listing of the tables in a given ODBC DSN?
|
|
|
|
|
Hey,
Are there any predefined functions in .NET to encode user input for SQL statement compatablity (ie. change "'" to "\'", etc)?
So: "Sam's cat died." would become "Sam\'s cat died."
Thanks,
Adam
-- Adam
"If you can't beat your computer in chess, try kickboxing"
|
|
|
|
|
You should avoid encoding user input into your SQL queries. This is a major security hole:
SELECT MyColumn FROM MyTable WHERE MyOtherColumn={1}
can become
SELECT MyColumn FROM MyTable WHERE MyOtherColumn=0; DELETE FROM MyTable
A better solution is to use stored procedures and pass user input as their parameters.
Alexandre Kojevnikov
MCAD charter member
Leuven, Belgium
|
|
|
|
|
If you are using SqlCommand's or DataAdpaters, you can use parameters for your values and it will automatically encode them for you. When you specify you DataAdpater query you can put:
SELECT au_id, au_lname, au_fname
from authors
where au_fname = @au_fname
Then in your command:
myCmd.Parameters["@au_fname"].value = "'any'''input";
And all should be well.
Another approach is to simply to a string replace on your input replacing all single quotes with two single quotes.
Rocky Moore <><
|
|
|
|
|
I have a form that queries my database and returns some values on the forms textboxes. On my laptop it returns for instance a value of 82 but it i load it on another machine the value of 82 shows as 82.000000 in that users textbox. what gives?
Chris
|
|
|
|
|
I am writing an ATL component to do some data access using ATO. I want to pass a _RecordsetPtr pointer or a _ConnectionPtr to a ATL function. I use code like this:
<br />
<br />
STDMETHODIMP CMyAtlObject::ReadData(LPDISPATCH pAdoRecordset, BOOL *pbSuccess)<br />
{<br />
_RecordsetPtr pRecordsetPtr;<br />
<br />
if (FAILED(pAdoRecordset->QueryInterface(IID_IDispatch, (void**) &pRecordsetPtr)))<br />
{<br />
return S_FALSE;<br />
}<br />
<br />
<br />
<br />
<br />
return S_OK;<br />
}<br />
<br />
But this doesn't work. The recordset pointer gets closed during the function call. If I pass an _ConnectionPtr as LPDISPATCH, the pointer becomes invalid.
So how can I pass valid ADO _RecordsetPtr's and _ConnectionPtr's to an ATL Object?
|
|
|
|
|
Hi,
I am trying to query some data from a SQL Server 2000 database, then change the connection and batch update that data into another (but identical in schema) SQL Server 2000 database (some records are different, some new), but nothing happens.
I'm sure it's related to the fact that no data has changed in the ADO recordset means it doesn't think a BatchUpdate is worth it.
Is there a way to make it go back to the database anyway?
Thanks,
Simon
|
|
|
|
|
Can anyone tell me how to connect to fox database (dbf file) from C#? I tried to use odbcDataAdapter but I could not access data in my dbf file.
Maybe i did something wrong or is there any other normal way how to retrieve data from dbf file?
thanx a lot...
|
|
|
|
|
Here's how we do it using an OleDB provider for VFP:
<br />
using System.Data.OleDB;<br />
<br />
private void FillDataSet()<br />
{<br />
<ul><br />
OleDbConnection foxConn = new OleDbConnection(<br />
"Provider=VFPOLEDB.1;Data Source=C:\\DataDirectory");<br />
OleDbCommand foxCommand = new OleDbCommand();<br />
OleDbDataAdapter foxDA = new OleDbDataAdapter();<br />
DataSet foxData = new DataSet();<br />
<br />
foxCommand.Connection = foxConn;<br />
foxCommand.CommandType = CommandType.Text;<br />
foxCommand.CommandText = "SELECT * FROM myTable";<br />
<br />
foxDA.SelectCommand = foxCommand;<br />
foxDA.Fill(foxData, "MyTable");<br />
</ul><br />
}<br />
|
|
|
|
|
Hello, I've run into something strange, I have the following SQL parameter (c#):
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@_rate", System.Data.SqlDbType.Float));
This is for a simple stored procedure that updates a float value in a SQL server 2000 (MSDE) table.
The following line of code:
cmd.Parameters["@_rate"].Value=1.33F;
Results in 1.33000004291534 being stored in the record.
It's obviously a binary rounding issue, but I can't seem to pin it down, I honestly don't think it should be doing that.
Is it my parameter definition or...?
------------
Tomorrow is the same day as today was yesterday.
|
|
|
|
|
There's no accurate way to store 1.33F in binary, just as there's no accurate way to write 1/3 in decimal. What you're seeing is an approximation of the value 1.33F being stored.
If you need to return 1.33 instead of 1.33000004291534, modify your precision so that you have 2 digits after your decimal separator.
|
|
|
|
|
If you want two digits of accuracy in your database, define the column as decimal(n,2), where n is the number of significant digits. For example, if you want three digits before the decimal point and two after, use decimal(5,2).
Then use the SqlDbType.Decimal type to define your parameter.
|
|
|
|
|
I use a datagrid populated with a dataview source, for looking up and selecting data for the parent form. after grid header click which sorts the underlying data source, i cannot select the value i see in the grid. before clicking the grid header it is ok. i know the grid's currentrow property is
no longer valid, but cannot solive this. Any one please help ! Thanks!
|
|
|
|
|
I am writing a vb.net windows applications
I have two related tables that I wish to join on a key field and then use the results to fill a bound control such as a listbox or combo box.
ssql = "select distinct(ntlogin), full_name, userid from dcr_USER_ROLES, EMPLOYEE " & _
" where USERID = NTLOGIN order by FULL_NAME"
' Establish a connection to the sql server
OleDbConnection1.Open()
Dim MyAdapter As New OleDbDataAdapter(New OleDbCommand(ssql, OleDbConnection1))
Dim MyDataset As New DataSet()
MyAdapter.Fill(MyDataset)
where do I go from here to fill the control with the selected full_name or do I need to take adifferent approach?
Thanks so much!
Milancie
|
|
|
|
|
You can use DataView object to refer to one table, or if it is a strong typed dataset you can iterate through the specified table.
Best regards,
Alexandru Savescu
P.S. Interested in art? Visit this!
|
|
|
|
|
I repost it because i'm afriad people will miss my "reply". I make it more clear about my problem here.
Thanks to Ray Cassick for his reply.
But i still have questions. I think i need to give you more information of my current problem.
HERE IS MY ORIGINAL DATABASE DESIGN:
For normal product, i have to store these attributes:
<code>- Prod_id
- Prod_title
- Prod_ISBN
- Prod_publisher
- Prod_cost
- Prod_retailprice
- Prod_description
- Category_id
- Prod_max (Max On Hand)
- Prod_min (Min On Hand)
- Shelf_id
- Status_id</code>
For consignment product, i have to store these attributes:
<code>- Cons_Prod_id
- Cons_Prod_description
- Cons_cost
- Cons_retailprice
- Cons_member_id [FK] (for the table that stores the info of the person who consign this item)</code>
For Invoice, i have:
<code>- Invoice_number
- Prod_id [FK]
- Cons_Prod_id [FK]
- Qty</code>
I think in the invoice table, it's not making sense to have both Prod_id and Cons_Prod_id in the table. Because if a product has Prod_id, it won't have Cons_Prod_id or vice versa. In this case, there will be wasting memory space.
So I decided to modify it and use inherit relationship in order to use ONE Prod_id ONLY in the invoice table.
However, if i use your suggestion, it just solves the problem on how to distinguish the normal and consignment product.
I still need a table to store those extra information such as Cons_member_id because some information won't exist in both products. (e.g. Consignment table does not need Prod_max and Prod_min, but need cons_member_id)
Then i stuck here now...
I very appreciate your help to solve my problem!!
Thanks alot!!!!! 
|
|
|
|
|
I have a problem: I receive a "Microsoft Cursor Engine error '80004005'
Data provider or other service returned an E_FAIL status." when querying my Access database. The query works as some records are returned. I can also eliminate the error by doing one of two things: I can remove an "order by" clause in the SQL statement so the results are not sorted and the error goes away. I can also eliminate the error by changing the data format of one of the fields in the database from Memo to text, (I am not sorting on that field). I have coded the cursor as "aduseclient" "adopenstatic" and know that it is not a permissions issue or failure to create the temp files on the client side. Everything I have seen on the large text field issues and "order by" clause says making it client side cursor should solve any issues. Any suggestions??
|
|
|
|
|
I have a form that uses a data adapter to query some data and populate some textboxes on the form. I then have a button that I hand coded to update the datasource and uses the textboxes as inputs for my parameters. My problem is that after i do the initial update it wont let me do another on if the data is changed without closing the form.
Here is the exception that is thrown:
Exception Text **************
System.Data.SqlClient.SqlException: The variable name '@weight' has already been declared. Variable names must be unique within a query batch or stored procedure.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
Is there a way to reset this variable or reset the connection?
|
|
|
|
|
Do Sqlcommand1.Parameters.clear() before running through the code that adds the parameters to the SqlCommand.Parameters collection, otherwise you are trying to add the same parameter name to the collection multiple times. It is ok to call clear on an empty parameters collection...
Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard
|
|
|
|
|
Thanks Rob that worked perfectly. I really appreciate it.
|
|
|
|
|
Could you possibly supply some code? How did you generate the data adapter?
To reset a connection you can call Close() on the connection and then Open() again. There seems to be a problem though, you should not get that message in a normal data adapter call. What is the select and update strings for that data adapter?
Rocky Moore <><
|
|
|
|
|
1. With the statement below I was able to retrieved records using the @jv parameter.
declare @jv varchar(100)
set @jv = 'abh3'
SELECT jv,day, interval/4, sm, aiu, sum(att), ans,usg, acblk
FROM c204
WHERE [day] between '7-21-2003' and '7-22-2003'
and jv = COALESCE(@jv,jv)
GROUP BY jv,day,interval/4,sm,aiu,ans,usg,acblk
ORDER BY jv,day,interval/4,sm,aiu,ans,usg,acblk
GO
2. What is the solution if you have two value in one parameter like the example below, Does anyone know the answer. Please HELP I'm using it in my stored procdure.
declare @jv varchar(100)
set @jv = 'abh3','hal3'
SELECT jv,day, interval/4, sm, aiu, sum(att), ans,usg, acblk
FROM c204
WHERE [day] between '7-21-2003' and '7-22-2003'
and jv = COALESCE(@jv,jv)
GROUP BY jv,day,interval/4,sm,aiu,ans,usg,acblk
ORDER BY jv,day,interval/4,sm,aiu,ans,usg,acblk
GO
Dabuskol
|
|
|
|
|
dabuskol wrote:
set @jv = 'abh3','hal3'
Not quite sure what you are doing here. SQL Server does not support arrays so it does not seem possible you will have two values in one parameter. You can break them up manually and then build the query and execute it dynamically.
Rocky Moore <><
|
|
|
|
|
Hi There
Looks like you will have to revert to Dynamic sql. Build up the statement as required and then excecute.
This is normally a bit slower than straight sql but should be able to do the trick.
Regards
Peet Schultz
USE [PUBS]
DECLARE @LAST_NAME NVARCHAR(100)
DECLARE @SQL NVARCHAR(1000)
SET @LAST_NAME = '(''White'',''Green'')'
SET @SQL = 'SELECT * FROM AUTHORS WHERE [AU_LNAME] IN' + @LAST_NAME
EXEC (@SQL)
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
