SQL Query question

[CycloWizard]

I'm trying to rig up a database that can store numbers and memo data types. The database is Access 2003 and I'm writing the aspx code in C#. I have a working version where all variables are stored simply as text, but I would like to expand it for greater utility. I haven't been able to track down the syntax to change a string or integer to the appropriate type for database storage.

Since I'm sure that didn't make any sense, here is an example:

I have a post ID number (integer, stored as number), post author name (string, stored as text), post content (string, stored as memo).

If anyone can point me in the right direction, it would be much appreciated. I've read the W3schools SQL tutorial but it didn't really mention this.

I'll throw in an extra :beer: for anyone who can tell me how to have a very long string in C# (>255 characters) that is analagous to the memo database type. All of these things are probably simple for anyone with relevant experience, but I'm trying to teach myself, so any help is greatly appreciated.

 

[MrChad]

As a basic reference, here's a list of Access data types and their SQL Server equivalents (most .NET documentation refers to SQL Server data types, not Access):

Table 4. Comparing Access and SQL Server data types
Jet (Access)..SQL Server
Text................char, nchar, varchar, nvarchar
Memo..............text, ntext
Byte................tinyint
Integer............smallint
Long Integer....integer
Single.............real
Double............float
Replication ID..uniqueidentifier
Decimal...........decimal
Date/Time.......smalldatetime, datetime, timestamp
Currency.........smallmoney, money
AutoNumber.....int + identity property
Yes/No.............bit
OLE Object.......image
Hyperlink.........(no equivalent)
(no equivalent).binary, varbinary

Beyond that you really don't need to worry about converting code data types into SQL data types. ADO.NET handles everything pretty seamlessly. Here is a good page with some sample code for you to start with.

 

[CycloWizard]

:beer:

Thank you sir.

 

[oog]

you asked about having a string with > 255 characters in C#. C# will not have trouble with that string with the basic .NET string data type.

 

[CycloWizard]

I'm having problems inserting things into any field that is not 'text', and I'm just not sure why. I guess really what I'm asking is whether or not all the arguments for an SQL insert command need to be strings. If I want to insert a boolean as a yes/no, do I need to treat it differently? Can an integer be sent to a number field as an integer or does it need to be converted to a string?

 

[MrChad]

Originally posted by: CycloWizard
I'm having problems inserting things into any field that is not 'text', and I'm just not sure why. I guess really what I'm asking is whether or not all the arguments for an SQL insert command need to be strings. If I want to insert a boolean as a yes/no, do I need to treat it differently? Can an integer be sent to a number field as an integer or does it need to be converted to a string?

Can you post some code so we can see what you're doing?

 

[CycloWizard]

Originally posted by: MrChad
Can you post some code so we can see what you're doing?

I guess... as long as you promise not to laugh.

string insertCommand = "INSERT INTO Items Values ('" + PostID +"','" + PostTitle.Text + "','" + Convert.ToString(DateTime.Now) + "','" + PostText.Text + "','" + IncludePoll.Checked + "','" + PollText.Text + "','" + TextBox1.Text + "')";
myCommand = new OleDbCommand(insertCommand,myConnection);
myCommand.ExecuteNonQuery();

Basically, PostID is an integer and I want to store it in a number field. PostText.Text is the content string, which I want to store in a memo field. IncludePoll is a check box determining whether or not a poll exists, and I would like to store it as a yes/no. The rest are strings stored as text. If I simply convert all database fields to text and send it all strings, it works, but that won't give me the end result I'm looking for.

Thanks!

Oh, and the error I'm getting is "Data type mismatch in criteria expression" on myCommand.ExecuteNonQuery().

 

[Wizkid]

Do not put quotes around non-string data in your SQL statement.

INSERT INTO Items Values ('" + PostID +"','" + PostTitle.Text + "','" +

should be

INSERT INTO Items Values (" + PostID +",'" + PostTitle.Text + "','" +

I'm not sure of the syntax for the dates... someone else should be able to help you...

 

[CycloWizard]

Originally posted by: Wizkid
Do not put quotes around non-string data in your SQL statement.

INSERT INTO Items Values ('" + PostID +"','" + PostTitle.Text + "','" +

should be

INSERT INTO Items Values (" + PostID +",'" + PostTitle.Text + "','" +

I'm not sure of the syntax for the dates... someone else should be able to help you...

BAH! That looks like it did it. Thanks for the help everyone. :beer::beer::beer::beer: