Re: ado, ms sql server 'text' columns - Database Discussion Boards

I'm using the following command to select all the columns from a table

SELECT * FROM table

When the command executes it's not returning the columns in the same order they were added to the table. For example if I created the table with column1, column2, column3, column4, they're being returned as column1, column4, column2, column3 (for example). Is this normal? Is the best way around it to simply specify the columns in the order I want them in the query? Thanks. I'm using MSDE.

- Aaron

monrobot13 wrote:
Is the best way around it to simply specify the columns in the order I want them in the query?

You should always specify the columns you want. Mainly because it means that if the data model changes then any existing queries will still return the data in the form that you are expecting. (Assuming that no columns are deleted / moved to different tables etc.)

IIRC specifying the columns in a select is faster at execution time.

"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)

Sounds like a good idea. Thanks for the information.

- Aaron

Where do i begin. I am very new to all of this. I have a class using visual studio.net where i have to creat a single record display that shows data one record at a time, on a form. I have to use ADO and SQL. I have the SQL it is:
Sort by Name

SELECT Customer.CustomerId, Customer.LastName, Customer.FirstName
FROM Customer
ORDER BY Customer.LastName, Customer.FirstName;

Non Zero Balance

SELECT Customer.LastName, Customer.FirstName, Customer.Balance
FROM Customer
WHERE (((Customer.Balance)>0))
ORDER BY Customer.LastName, Customer.FirstName;

Select by State

SELECT Customer.CustomerId, Customer.LastName, Customer.FirstName,
Customer.State
FROM Customer
WHERE (((Customer.State)=[@WhatState]))
ORDER BY Customer.LastName, Customer.FirstName DESC;

Sum Balance

SELECT DISTINCTROW Sum(Customer.Balance) AS SumBalance
FROM Customer;

Insert customer

INSERT INTO Customer ( LastName, FirstName, Balance )
VALUES ([@newlast], [@newfirst], [@newbalance

I also have my form code which is

Public Class Form1
Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form Designer.
InitializeComponent()

'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents MainMenu1 As System.Windows.Forms.MainMenu
Friend WithEvents MenuItem1 As System.Windows.Forms.MenuItem
Friend WithEvents MenuItem2 As System.Windows.Forms.MenuItem
Friend WithEvents MenuItem3 As System.Windows.Forms.MenuItem
Friend WithEvents MenuItem4 As System.Windows.Forms.MenuItem
<system.diagnostics.debuggerstepthrough()> Private Sub InitializeComponent()
Me.MainMenu1 = New System.Windows.Forms.MainMenu
Me.MenuItem1 = New System.Windows.Forms.MenuItem
Me.MenuItem2 = New System.Windows.Forms.MenuItem
Me.MenuItem3 = New System.Windows.Forms.MenuItem
Me.MenuItem4 = New System.Windows.Forms.MenuItem
'
'MainMenu1
'
Me.MainMenu1.MenuItems.AddRange(New System.Windows.Forms.MenuItem() {Me.MenuItem1, Me.MenuItem2, Me.MenuItem3, Me.MenuItem4})
'
'MenuItem1
'
Me.MenuItem1.Index = 0
Me.MenuItem1.Text = "Exit"
'
'MenuItem2
'
Me.MenuItem2.Index = 1
Me.MenuItem2.Text = "Customers"
'
'MenuItem3
'
Me.MenuItem3.Index = 2
Me.MenuItem3.Text = "MasterDetail"
'
'MenuItem4
'
Me.MenuItem4.Index = 3
Me.MenuItem4.Text = "StateQuery"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(292, 206)
Me.Menu = Me.MainMenu1
Me.Name = "Form1"
Me.Text = "Form1"

End Sub

#End Region

Private Sub MenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem1.Click

End Sub

Private Sub MenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem2.Click

End Sub

Private Sub MenuItem3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem3.Click

End Sub

Private Sub MenuItem4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem4.Click

End Sub
Private Sub Exit_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem1.Click

' Exit the Application
Application.Exit()

End Sub
Private Sub MenuItem2_Click_(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem2.Click

Dim myform As New Customers
myform.ShowDialog()

End Sub
Private Sub MenuItem3_Click_(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem3.Click
Dim myForm As New MasterDetail
myForm.ShowDialog()

End Sub
Private Sub MenuItem4_Click_(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem34.Click
Dim myForm As New StateQuery
myForm.ShowDialog()

End Sub
End Class

The file i am getting the information for the table to display the records is customer.mdb. From what i gather I think i have to change the button event to be something like me.bindingtxt(customer.mdb,"customer"). Is that right? I won't post my next question until i get a response, this one's long enough!
-ErinSue

I'm not sure exactly what problem you are having.

Have you connected to your database yet?

Have a read of http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskcreatingconnectiontoaccessdatabase.asp[^]

The other articles in the section should give you a clearer idea of how to proceed to bind your dataset to controls.

Alternatively, you could use the DataForm Wizard to generate an example form and study the code to get a better understanding.

Michael

But you know when the truth is told,
That you can get what you want or you can just get old,
Your're going to kick off before you even get halfway through.
When will you realise... Vienna waits for you? - "The Stranger," Billy Joel

i'm stuck
im trying to save a text file to sql server(in a 'text' column ); have no problem with unicode text, but with non-unicode strings after saving to the sql server i read garbage from the columns

here's my code

_bstr_t bstr_text = adoStream->ReadText(adoStream->Size);
// dumping bstr_text shows the text here in bstr_text is ok
recSet->Fields->GetItem(item)->Value = bstr_text;
// debugging down to Field20::PutValue shows that correct data is being put in the field

If your source data is 8-bit character-oriented, you should use a text column and ensure that the client's thread locale is the same as the locale used by the column.

If your source data is 16-bit character-oriented, use an ntext column. You should also use ntext if your client's locale will be different from the server's.

If you're just interested in storing and retrieving a file with no conversions at all, use an image column to treat it as binary. Obviously you can't do full-text search on an image column.

When you assign a char string to a _bstr_t, the conversion to Unicode is performed using the thread's default code page. When the opposite process occurs to store the data in the database, SQL Server uses the configured locale's code page. If the two settings don't match, the conversions may not be a round-trip.

Stability. What an interesting concept. -- Chris Maunder

thank you, but i know all of this already.
i think i found the problem: it turns out that whenever i call _bstr_t bstr_text = adoStream->ReadText(adoStream->Size);
no matter if the file is unicode or not, the data is saved to the wide char member of _bstr_t

so some hacking is needed to get correct results with C char* data

Yes, this is because ADO is an Automation interface designed for access from Visual Basic, and hence uses BSTRs throughout. It isn't really designed for use with C++.

You can access char-based data directly, without intervening conversions to and from Unicode, using the OLE DB interfaces. Visual C++ supplies the OLE DB Consumer Templates, which can simplify the task somewhat.

If you need to get char-oriented data back out of a BSTR, see WideCharToMultiByte.

Stability. What an interesting concept. -- Chris Maunder

thank you

WideCharToMultiByte is exactly what i used

I’m having a problem connecting my application to an MySQL server at an web host, have no problem on my own system.

The host doesn’t seem to know what’s wrong.

I’m using .Net Framework 1.1 and System.Data.ODBC and when connecting to the server get this error.

ERROR [S1000] [TCX][MyODBC]Can't connect to MySQL server on 'localhost' (10061)

ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr)

I have tried different connections strings, OleDB, SqlClient. But it just doesn’t seem to work.

If you have any ideas or know where I can find some information about this, I would be very happy since I can’t find any.

You must use System.Data.ODBC. SqlClient is only for Microsoft SQL Server databases.

First, try the MySQL client directly (if you can). This will isolate whether there actually is a MySQL server running.

Error number 10061 looks like a Windows Sockets error code, where it means WSAECONNREFUSED. Typically this means there wasn't a server listening on that port.

Stability. What an interesting concept. -- Chris Maunder

I am using System.Data.ODBC.
There is an MySQL server running since I have edit the MySQL database thru phpMyAdmin.
Im not declaring any port in my connectionstring.
Thx for trying.

As indicated by the error message, you are trying to connect to a server on your own machine i.e. localhost. Recheck your connection string. See http://www.connectionstrings.com.

do any of u have an idea if there is anything similar to the Hierarchical Recordset of ADO in ADO.NET

See the DataView and DataRelation documentation in MSDN.

What a piece of work is man, how noble in reason, how infinite in faculties, in form and moving how express and admirable . . . and yet to me, what is this quintessence of dust? -- Hamlet, Act II, Scene ii.

Can anyone explain me how to convert a date with the format YYYYWWD (where WW is weeknumber an D is day (1 for Monday, 2 for Tuesday, 3 for Wednesday, 4 for Thursday, 5 for Friday) to YYYYMMDD.

An obvious way would be to find out which day number January 1 of the given year was, then add WW lots of seven days. SQL Server's date manipulation functions are capable.

Stability. What an interesting concept. -- Chris Maunder

I want to do a simple select * of all the fields I have in a table, but there is a text field in it and if I do "SELECT * FROM table" almost all pertinant information scrolls out of the screen and buffer (I'm using MSDE with OSQL) because the text field is so long. Is there a way to limit this without having to specify every field I want to select? Thanks.

- Aaron

I'm trying to create a stored procedure which can insert data into a number of different tables.
But the SQL parser always gives error 137 must declare the variable '@whichtable'
What am I doing wrong? Help please! Confused | :confused:

<br />
CREATE PROCEDURE  dbo.new_zone<br />
@whichtable varchar(20),<br />
@num int,<br />
@name varchar(20)<br />
AS INSERT @whichtable (n,nme) VALUES (@num,@name)<br />
GO<br />

chris fearnley wrote:
INSERT @whichtable (n,nme) VALUES (@num,@name)

I don't think you can substiture a variable for a table name - it messes with SQL Server's ability to pre-compile stored procedures then use the compiled version.

What you need to do is create something like:

IF @whichtable = 'TableA'
    INSERT TableA (n,nme) VALUES (@num,@name)
ELSE IF @whichtable = 'TableB'
    INSERT TableB (n,nme) VALUES (@num,@name)
ELSE IF @whichtable = 'TableC'
    INSERT TableC (n,nme) VALUES (@num,@name)

There is another method whereby you can create a string containing the relevant SQL and get it to parse and execute the string, but I don't recall off the top of my head how to do that. Also, if performance is important you don't want stored procedures parsing SQL midflight.

Does this help?

"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)

Thanks Colin,
What you say makes complete sense - but that gives me a problem!
I can't use your suggested if...else if since the table names are generated programmatically (they are unknown when the app starts up) and there could be 300+ of them! Looks like I have a major restructure on hand Sigh | :sigh:

if I really need to change the T-SQL commands into stored procs (which I've been advised to do for speed and security).

chris fearnley wrote:
table names are generated programmatically

How often are new tables created? Just, a project I've been working on recently has about 24 tables with exactly the same structure that are put together with a view. (The reason is that each year so much data is generated that is makes sense to split it up that way and operate through the view - and since most queries only relate to one particular year it is much faster than if the records were all in the one big table.) So each year we have a script that is run that creates the new tables and updates the relevant views to include the new table.

Could that work for you? (You have to be careful about the range of data in each table so that it is easy to set up constraints to get the records in the correct table when inserting - For instance in the example I mentioned above each table represents one year, so there is a field for year and all the records in a table contain the same year value - but when you look at the data through the view it appears as one continuous set of records)

"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)

Most of the tables are created soon after the app starts up, as external hardware devices are 'discovered' via serial ports and network ports. The table names are based on the serial numbers of these devices.
Your suggestion may be useful to us in the long run (see below!)
In the short term I've solved the immediate problem (I think) simply by creating a new stored proc for each table as it is created. The procs are working now anyway.
If your SQL experience covers security issues, and you know what "21CFRpart11" is, I could do with your help on a paid basis, urgently.
Thanks for help so far anyway Smile | :)

.

chris fearnley wrote:
The procs are working now anyway.

Excellent.

I have actually thought of another possible solution. It may be possible to have a combined primary key on your tables, the extra field for the primary key would be the based on the serial number you mention above. If this is too much repetition of data (I'm guessing the serial numbers are quite long) you can set up a lookup table that contains all the relevant serial numbers and use its primary key in the main (original) table.

For example:

   +----+--------------+
   | PK | SerialId     |
   |    | SerialNumber |
   +----+--------------+
             |
             |
            /|\
+--------+----------------+
| PK, FK | SerialId       | 
| PK     | OriginalId     |
|        | OriginalField1 |
|        | OriginalField2 |
+--------+----------------+

With this schema your stored procedures would only need to add an extra item to the where clause to get the same effect has lots of tables with identical schema.

chris fearnley wrote:
If your SQL experience covers security issues, and you know what "21CFRpart11" is, I could do with your help on a paid basis

Unfortunately my knowledge of Security is about average for a developer (in other words: quite limited), although I am due to go on some courses later this year. However, this doesn't help your current situation. Thanks for the offer anyway.

"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)