Date Format

[vuhua]

Friend,

I have a question about date conversion. I'm coding a SQL INSERT query in VB.NET platform. The code work just fine and the date conversion work just fine also. I use MS Access database to store the data. I setup the "Dates" field in MS Access as Date/Time with short date format. I notice that whenever I insert the data into the database, the date field is listed as "12/30/1899" and not the date that I wanted (ex...12/30/2005). Also, when I search for the date (ex..12/30/005), the search come back with a result. I'm not sure what might cause this problem. Please help, my code is listed below.

OleDataAdapter.InsertCommand.CommandText = _
"INSERT INTO Expense(Amount, Dates, Debit, Credit, " & _
"ExpenseType, PaymentMethod, Summary) " & _
"VALUES(" & Val(txtAmount.Text) & " , " & _
"" & CType(txtDate.Text, DateTime) & " , " & _
"'" & radDebit.Checked & "' , '" & radCredit.Checked & "' , " & _
"'" & cboExpType.SelectedItem & "' , " & _
"'" & cboPayMethod.SelectedItem & "' , " & _
"'" & txtSummary.Text & "')"

 

[Tyron Harford]

Heya,

Try using the ToString method - eg:

CType(txtDate.Text, Date).ToString("yyyy-MM-dd HH:mm:ss")

Cheers,
Ty

 

[vuhua]

I try that but when I added the date, using your format, I check my MS access table and the date field listed some date that is invalid. The date is listed as "6,24,1904", and some field is listed as "12/30/11899". What's going on here. Help me please

 

[cjard]

ugghhhhh.. what a nasty insert statement.. you really should try using perpared statements, they look like this:

INSERT INTO tblTable(field1,field2) VALUES(?, ?)

make an sql command object from that then you can say:

command1.Parameters.Clear
command1.Parameters.Add(command1.CreateParameter(0, date, input))

etc.. and basically you have a sql where the data members are taken up by ? marks and you can change the contents just by saying:

command1.Parameters(0).Value = datetimepicker1.Value

its so much easier that shooing everything into a string, sending it to the db and having the db parse it all back out again.

Look up "vb.net access parameterized query" or something on google. Come back if you still have problems, but bear in mind that access is very lame and there isnt much reason to use it - MSDE/SQLSErver Express is out, and free, better than access etc and it supports named parameters in its queries..

in SQLS you might say:

INSERT INTO tblTable(f1, f2) VALUES(@theName, @theDate)

and then

command1.Parameters("theName").Value = "cjard"
command1.Parameters("theDate").Value = datetimepicker1.Value
 
commad1.ExecuteNonQuery()

 

[vis781]

OleDataAdapter.InsertCommand.CommandText = _
"INSERT INTO Expense(Amount, Dates, Debit, Credit, " & _
"ExpenseType, PaymentMethod, Summary) " & _
"VALUES(" & Val(txtAmount.Text) & " , " & _
"" & CType(txtDate.Text, DateTime) & " , " & _
"'" & radDebit.Checked & "' , '" & radCredit.Checked & "' , " & _
"'" & cboExpType.SelectedItem & "' , " & _
"'" & cboPayMethod.SelectedItem & "' , " & _
"'" & txtSummary.Text & "')"

As Cjard said parameterised queries are far more readable an in the spirit of good coding. Here's a revised example of your code....

Dim OleCommand as new OleDbCommand(""INSERT INTO Expense(Amount, Dates, Debit, Credit, ExpenseType, 
PaymentMethod, Summary) VALUES (?, ?, ?, ?, ?, ?, ?)", yourconnection)
 
OleCommand.Parameters.Add("@Amount",OleDbType.Integer,0,"Amount").
Value = Convert.ToInt32(txtAmount.Text)
OleCommand.Parameters.Add("@Dates",OleDbType.Date,0,"Dates").Value
 = CType(txtdate.txt,DateTime)
OleCommand.Parameters.Add("@Debit",OleDbType.Boolean,0,"Debit").Value
 = radDebit.Checked
OleCommand.Parameters.Add("@Credit",OleDbType.Boolean,0,"Credit").Value 
= radCredit.Checked
OleCommand.Parameters.Add("@ExpenseType,OleDbType.VarWChar,0,"ExpenseType").Value
= Convert.ToString(cboExpType.SelectedItem)
OleCommand.Parameters.Add("@PaymentMethod",OleDbType.VarWChar,0,"PaymentMethod).Value
 = Convert.ToString(cboPayMethod.SelectedItem)
OleCommand.Parameters.Add("@Summary",OleDbType.VarWChar,0,"Summary").Value
= txtSummary.Text
 
OleDbDataadapter.InsertCommand = olecommand

I've Had to assume a lot, things like the OleDbDataType, but i think i have them right based on what you've called your controls. Also The Field Size i have set to 0 all through, you will have to set them to what the field size is in your Database.

 

[vuhua]

Thanks,

I got the code to be working fine with no error. However, I have one stupid question. This is my first time working with parameter, therefore, I'm not familiar with "Connection String" yet. In the past, I use the connection by choose the "OleDbDataAdapater" from the toolbox and then create a connection that way. In this case, I have to code the connection myself. Is there any example of that. I'd try Google it but find mostly junk only. Your help is appreciated.

Thanks,

Vu

NOTES: I got an error: ExecuteNonQuery failed, your connection string has not been initialized. I use Vis781 code example.

 

[cjard]

the Connection object represents a connection to a database. you msut establish a connection and pass the connection to any Command objects before you can run queries.

There is nothing stopping you from using a wizard, or graphical configuration for this:

Right click in the Toolbox window, in the Data panel, and click CHoose Items. Wait while the computer enumerates all the known controls. in the window that appears, type OLE into the filter and you can see e.g. OleDBConnection etc - tick them on and they will appear in the Toolbox ready for dropping on a form

As for your conenction strings, you can either go to www.connectionstrings.com (i think) or build it yourself with this easy way:

make a file on the desktop called a.udl
open it, and you see a very familiar DB conenction window open up.
set all the parameters and test the connection etc
save/close/say OK whatever to get the window to go away
then open your a.udl file and inside is a connectionstring to connect to the same source as you just specified

 

[ashishnaicker]

connection strings

you can use this statement to create an instance of a connection:

Dim conn As New System.Data.OleDb.OleDbConnection("File Name=C:\conn.udl")


somewhere in your code you can have this procedure to create the udl file.

Private Sub createConnection()
Dim fs As New System.IO.FileStream("C:\conn.udl", IO.FileMode.Create, IO.FileAccess.ReadWrite)
fs.Close()
Dim ps As System.Diagnostics.Process = System.Diagnostics.Process.Start("C:\conn.udl")
ps.WaitForExit()
End Sub

Hope this is useful...