Basic SQL Insert command

[Greyfleck]

I have spent the past couple of hours 'googling' this command and to a man they all give examples of the Insert statement using literals.

I have a simple table:

Horseid  int iscontrol
Mdate smalldatetime
MCourse varchar(50)
Mtime smalldatetime
MHorse varchar(50)
MChecked bit

I have 4 textboxes / 1 checkbox (unbound) on my form

txtDate
txtCourse
txtTime
txtHorse
chkChecked

My table is HorseMaster
I am using VB 2008 express & SQL server express 2008
I have the connections but am struggling with the insert statement.

Any help will be gratefully received ...

 

[simian101]

This is probably more a question for the vb forum. But...

Basically you build a dynamic sql statement then execute it against the connection.

Here is a basic example.

strSQL1 = "INSERT INTO SVC" & strCompany & ".POSPMTIX VALUES('" & strTransnum & "','" & strCardtype & _
"','" & cardstatus & "','" & line1 & "'" strSQL1 = strSQL1 & ",'" & line2 & "','" & line3 & "','" & pin1 & "','"
strSQL1 = strSQL1 & strTranDate & "','" & strTranTime & "')"

Hope that helps.

Simi

 

[simian101]

That should have been on 3 lines...

strSQL1 = "INSERT INTO SVC" & strCompany & ".POSPMTIX VALUES('" & strTransnum & "','" & strCardtype & _
"','" & cardstatus & "','" & line1 & "'"

strSQL1 = strSQL1 & ",'" & line2 & "','" & line3 & "','" & pin1 & "','"

strSQL1 = strSQL1 & strTranDate & "','" & strTranTime & "')"

ps It always helps to debug.print your string to check your syntax.

Simi

 

[Greyfleck]

Thanks for your response.
Could you possibly explain .POSPMTIX.
Jon

 

[simian101]

Sorry I pulled a as400 example....

Which is libary.table and in this case is libaray is dynamic. SVCtable.POSPMTIX.

Libary = SVCtable
Tablename = POSPMTIX

For Sql Server you will probably use

[Database].dbo.[TableName]

Simi

 

[jbenson001]

DO NOT use dynamic SQL, there are very few times when it is necessary. Write a stored procedure, with parameters for each value you want to pass in. Then in your VB code, just call the stored procedure with the parameters.

 

[bborissov]

Use PARAMETERS!!!!!
Do not build the whole string.
If you build the whole string you are opened to SQL Injections.
How to do it? I don't know. Every front end has its own specifics. But the general way is:

strSQL1 = "INSERT INTO SVC VALUES(?,?,?,?,?)"

Where ? is placeholder for the parameter (every front end may have its own placeholder char .

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[jbenson001]

in .NET use the SQL command object and the SQLParamater object.

 

[markros]

Please review this whole thread

http://forums.asp.net/t/1254125.aspx

It has several good ideas about how to prevent SQL injection attacks.

 

[markros]

I meant

http://forums.asp.net/p/777624/2973524.aspx

instead