how to use insert query at actual code programing

Question

Im running this query at my SQL and it is good.

INSERT INTO db.tbl (col1, col2, col3) values ('1.68','2014/01/01 05:00:00', Date_add(col2, interval col1 day_second));

My code is not working but the computation at last column is wrong

cmd = New Odbc.OdbcCommand("INSERT INTO room.save(exacttime, depletion_time, time_inputed) values ('" & lblexact.Text & "','" & Trim(TextBox6.Text) & "',' Date_add( exacttime, interval depletion_time day_second)')", con)           
cmd.ExecuteNonQuery()

Why is there a random `'` at the end? – Psychemaster Sep 08 '14 at 06:23 — Psychemaster, Sep 08 '14 at 06:23

score 1 · Answer 1 · edited May 23 '17 at 11:49

1

Always use parameterized/prepared SQL queries. Here's how your code should look like.

Using con As New Odbc.OdbcConnection("...")
    Using cmd As New Odbc.OdbcCommand()

        cmd.Connection = con
        cmd.CommandText = <!--
            INSERT INTO db.tbl (
                col1, 
                col2, 
                col3
            ) VALUES (
                @p1,
                @p2,
                @p3
            );
        -->.Value

        Dim value1 As Double = Double.Parse(labeldatetime.Text)
        Dim value2 As Date = Date.Parse(textboxtime.Text)
        Dim value3 As Date = value2.AddSeconds(value1)

        cmd.Parameters.AddWithValue("@p1", value1)
        cmd.Parameters.AddWithValue("@p2", value2)
        cmd.Parameters.AddWithValue("@p3", value3)

    End Using
End Using

Note that the you don't need a XComment, I just used it to make it more readable.

edited May 23 '17 at 11:49

Community

1
1

answered Sep 08 '14 at 05:57

Bjørn-Roger Kringsjå

9,849
6
36
64

it still eror exception – user3795723 Sep 08 '14 at 06:11
1

@user3795723 `it still eror exception` tells us... well, **nothing**. You need to provide a full error description. Also, do as I say, use parameterized / prepared SQL queries. – Bjørn-Roger Kringsjå Sep 08 '14 at 06:17
yes already tried that but theres still error, im using timestamp at my database. You have an error in your SQL Sytax ODBC Exception – user3795723 Sep 08 '14 at 06:21
please see my real data in my rewrite question. – user3795723 Sep 08 '14 at 06:22
@user3795723 So `col1` is double, `col2` and `col3` are timestamps? – Bjørn-Roger Kringsjå Sep 08 '14 at 06:32
yup!, do i need to change? or what? – user3795723 Sep 08 '14 at 06:33
im not using col1,2,3. I rewrite to my real data = depletion_time, exacttime, timeinputed... depletion_time is varchar, exacttime and timeinputed are timestamp – user3795723 Sep 08 '14 at 06:33
@user3795723 You need to edit your question so that it includes the following: 1) The sql table schema. 2) The data type of your input fields. Names like `TextBox6` and `lblexact` doesn't reveal this. – Bjørn-Roger Kringsjå Sep 08 '14 at 06:42
depletion_time -> Double,. exacttime -> Timestamp,. timeinputed -> Timestamp,. room -> Schema,. save -> table,. I already rewrite my schema and data type. Take another look at my question, I already put my real data's. – user3795723 Sep 08 '14 at 06:49
@user3795723 You should change the data type of the columns to `DateTime`. You might want to see this SO link: [datetime vs timestamp?](http://stackoverflow.com/questions/409286/datetime-vs-timestamp). – Bjørn-Roger Kringsjå Sep 08 '14 at 06:57
I already change the data type to datetime but still not inserting to my database – user3795723 Sep 08 '14 at 07:01
Do I need to rewrite my code? or what. – user3795723 Sep 08 '14 at 07:03
@user3795723 What's the error message? – Bjørn-Roger Kringsjå Sep 08 '14 at 07:06
My code is now working its all inserting but the problem is when I insert exacttime give me default value 000-00-00 00:00:00 not computing my depletion_time and time_inputed – user3795723 Sep 08 '14 at 07:24

score 0 · Accepted Answer · answered Sep 08 '14 at 08:57

0

PS: I am not familiar with vb.net, so not sure if this is the right way to do (So, no downvotes pls!!),. But as per sql, there are some unwanted single quotes that needs to be removed.

cmd = New Odbc.OdbcCommand("INSERT INTO room.save(exacttime, depletion_time, time_inputed) values ('" & lblexact.Text & "','" & Trim(TextBox6.Text) & "',
Date_add( exacttime, interval depletion_time day_second))", con)   
^                                                        ^
|-----------------Single Quotes Removed------------------|

answered Sep 08 '14 at 08:57

ngrashia

9,869
5
43
58

do i need to use time stamp again?\ – user3795723 Sep 08 '14 at 09:00
Youre correct but one last question, could you give 1 more column for every result minus 1 hour. thanks – user3795723 Sep 08 '14 at 09:12
Seriously I have no idea of .net. Since I found extra single quotes in sql query, I posted as answer. So, I am unable to understand what is results minus 1 hour.. – ngrashia Sep 08 '14 at 09:32
If this helped you solve your current problem, you can accept as answer. Probably you can ask as a separate question which .net experts would be able to help you with. – ngrashia Sep 08 '14 at 09:32
sir, can you help me again with my format insert I just add 1 column and 1 data and it is running in my SQL please last.... cmd = New Odbc.OdbcCommand("INSERT INTO room.save( exacttime, depletion_time, expected_dip_time, expected_dip_time2) values ('" & lblexact.Text & "','" & Trim(TextBox6.Text) & "',Date_add( exacttime, interval depletion_time day_second)) " & ",DATE_SUB(Date_add( exacttime, interval depletion_time day_second), interval 1 hour)", con) – user3795723 Sep 09 '14 at 07:22
Try this: cmd = New Odbc.OdbcCommand("INSERT INTO room.save( exacttime, depletion_time, expected_dip_time, expected_dip_time2) values ('" & lblexact.Text & "','" & Trim(TextBox6.Text) & "',Date_add( exacttime, interval depletion_time day_second) ,DATE_SUB(Date_add( exacttime, interval depletion_time day_second), interval 1 hour)) ", con) – ngrashia Sep 09 '14 at 07:26

how to use insert query at actual code programing

2 Answers2